MYSQL窗口函数

最新推荐文章于 2023-02-11 11:37:29 发布

野生java研究僧

最新推荐文章于 2023-02-11 11:37:29 发布

阅读量446

点赞数 1

分类专栏： mysql 文章标签： mysql 数据库 java

本文链接：https://blog.csdn.net/m0_46188681/article/details/128813164

版权

mysql 专栏收录该内容

8 篇文章 1 订阅

订阅专栏

MYSQL窗口函数

1.行与列的转换
- 1.1 行转列
- 1.2 列转行
2.窗口函数

1.行与列的转换

1.1 行转列

题目：

-- 原表：
id   name   subject score
 1	 张三	语文	78
 2	 张三	数学	88
 3	 张三	英语	98
 4	 李四	语文	89
 5	 李四	数学	76
 6	 李四	英语	90
 7	 王五	语文	99
 8	 王五	数学	66
 9	 王五	英语	91
-- 结果表：
name  	chinese  mathematics  english
张三   	78   		88    		98
李四   	89   		76   		90
王五   	99   		66   		91

建表SQL

 CREATE TABLE `t_scoe` (
  `id` int(10) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `subject` varchar(20) DEFAULT NULL,
  `score` float DEFAULT NULL,
  PRIMARY KEY (`id`)
) 

insert into t_scoe (id,name, subject, score) values (1,'张三', 'chinese', 78);
insert into t_scoe (id,name, subject, score) values (2,'张三', 'mathematics', 88);
insert into t_scoe (id,name, subject, score) values (3,'张三', 'english', 98);
insert into t_scoe (id,name, subject, score) values (4,'李四', 'chinese', 89);
insert into t_scoe (id,name, subject, score) values (5,'李四', 'mathematics', 76);
insert into t_scoe (id,name, subject, score) values (6,'李四', 'english', 90);
insert into t_scoe (id,name, subject, score) values (7,'王五', 'chinese', 99);
insert into t_scoe (id,name, subject, score) values (8,'王五', 'mathematics', 66);
insert into t_scoe (id,name, subject, score) values (9,'王五', 'english', 91);

SELECT * from t_scoe

方式1：同样使用case then 也能实现

SELECT 
	NAME,
	SUM( chinese ) AS chinese,
	SUM( english ) AS english,
	SUM( mathematics ) AS mathematics 
FROM
	( SELECT NAME,
	IF( SUBJECT = 'chinese', score, 0 ) AS chinese,
	IF( SUBJECT = 'mathematics', score, 0 ) AS english,
	IF( SUBJECT = 'english', score, 0 ) AS mathematics 
	FROM t_scoe ) t_temp 
GROUP BY NAME

方式2(优化)：同样使用case then 也能实现

SELECT 
	name,
	SUM(IF(subject = 'chinese',score,0) ) AS chinese,
	SUM(IF(subject = 'mathematics',score,0) ) AS english,
	SUM(IF(subject = 'english',score,0) )AS mathematics
 FROM t_scoe  GROUP BY name

公式：

select 分组列，
       聚合函数(case 转换列 when 转换列值1 then 数据列 else ... end) as 列名1,
	   聚合函数(case 转换列 when 转换列值2 then 数据列 else ... end) as 列名2,
	   聚合函数(case 转换列 when 转换列值3 then 数据列 else ... end) as 列名3
	   ...
from 表名
group by 分组列;

select 分组列，
       聚合函数(case when 转换列=转换列值1 then 数据列 else ... end) as 列名1,
	   聚合函数(case when 转换列=转换列值2 then 数据列 else ... end) as 列名2,
	   聚合函数(case when 转换列=转换列值3 then 数据列 else ... end) as 列名3
	   ...
from 表名
group by 分组列;

注意点：判断函数返回null和0都可以，但是需要选择对，对应的聚合函数，如果是使用cunt的话，返回的是0也会被统计到，因为只要不是null，count就会+1，如果使用sum，那么null和0都可以，因为null不参与计算

1.2 列转行

题目：

-- 原表：
name  chinese  mathematics  englinsh 
张三    78   		88    	  98
李四    89   		76    	  90
王五    99   		66   	  91 

-- 结果表：
name  	subject  	score
张三    chinese       78
张三    mathematics   88
张三    englinsh 	    98
李四    chinese     	89
李四    mathematics   76
李四    englinsh      90
王五    chinese	    99
王五	  mathematics	66
王五	  englinsh	    91

建表SQL

CREATE TABLE `report_card` (
  `name` varchar(20) DEFAULT NULL,
  `chinese` float DEFAULT NULL,
  `english` float DEFAULT NULL,
  `mathematics` float DEFAULT NULL
) 
insert into report_card (name, chinese, mathematics, english) values ('张三', 78, 88, 98);
insert into report_card (name, chinese, mathematics, english) values ('李四', 89, 76, 90);
insert into report_card (name, chinese, mathematics, english) values ('王五', 99, 66, 91);

SELECT * from report_card

解题：

SELECT name,chinese AS chinese  FROM  report_card  UNION ALL
SELECT name,mathematics AS mathematics FROM  report_card  UNION ALL
SELECT name,english AS  english FROM  report_card  
ORDER BY  name

公式：

SELECT 非转换列, '转换列1' AS 新转换列名, 转换列1 AS 新数据列名 FROM 表名
UNION ALL
SELECT 非转换列, '转换列2' AS 新转换列名, 转换列2 AS 新数据列名 FROM 表名
UNION ALL
SELECT 非转换列, '转换列3' AS 新转换列名, 转换列3 AS 新数据列名 FROM 表名
ORDER BY ...;

2.窗口函数

MYSQL8.0官方文档：https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html

名字	描述
`CUME_DIST()`	累计分布值
`DENSE_RANK()`	分区内当前行的排名，无间隙
`FIRST_VALUE()`	窗口框架第一行的参数值
`LAG()`	分区内滞后当前行的参数值
`LAST_VALUE()`	窗口框架最后一行的参数值
`LEAD()`	分区内当前行前导行的参数值
`NTH_VALUE()`	窗口框架第 N 行的参数值
`NTILE()`	分区内当前行的桶号。
`PERCENT_RANK()`	百分比排名值
`RANK()`	分区中当前行的排名，带有间隙
`ROW_NUMBER()`	其分区中的当前行数

2.1 什么是窗口函数

2.2基本含义

窗口限定一个范围，它可以理解为满足某些条件的记录集合，窗口函数也就是在窗口范围内执行的函数。

基本语法

窗口函数有over关键字，指定函数执行的范围，可分为三部分：分组子句(partition by)，排序子句(order by)，窗口子句(rows)

<函数名> over (partition by <分组的列> order by <排序的列> rows between <起始行> and <终止行>)

注意Mysql8开始才支持窗口函数

2.3演示表格

cid（班级id)	sname（学生姓名）	score（分数）
001	张三	78
001	李四	82
002	小明	90
001	王五	67
002	小红	85
002	小刚	62

2.4演示脚本

CREATE TABLE score (
       cid varchar(4),
       sname varchar(4),
       score int
);

insert into score (cid, sname, score) values ('001', '张三', 78);
insert into score (cid, sname, score) values ('001', '李四', 82);
insert into score (cid, sname, score) values ('002', '小明', 90);
insert into score (cid, sname, score) values ('001', '王五', 67);
insert into score (cid, sname, score) values ('002', '小红', 85);
insert into score (cid, sname, score) values ('002', '小刚', 62);

案例：求出各个班级的总分

SELECT 
*, 
SUM(score) OVER(PARTITION BY cid ORDER BY score  ROWS BETWEEN unbounded preceding AND unbounded following) AS sum_score 
FROM  score

2.5分组子句(partition by)

不分组可以写成partition by null或者直接不写

后面可以跟多个列，如 partition by cid, sname

注意 partition by与group by的区别

1）前者不会压缩行数但是后者会

2）后者只能选取分组的列和聚合的列

也就是说group by 后生成的结果集与原表的行数和列数都不同

2.6排序子句(order by)

不排序可以写成order by null 或者直接不写

asc或不写表示升序，desc表示降序

后面可以跟多个列，如 order by cid, sname

2.7窗口子句(rows)

窗口子句的描述

起始行: N preceding/unbounded preceding
当前行: current row
终止行: N following/unbounded following

举例：

rows between unbounded preceding and current row 从之前所有的行到当前行

rows between 2 preceding and current row 从前面两行到当前行

rows between current row and unbounded following 从当前行到之后所有的行

rows between current row and 1following 从当前行到后面一行

注意：

排序子句后面缺少窗口子句，窗口规范默认是 rows between unbounded preceding and current row

排序子句和窗口子句都缺失，窗口规范默认是 rows between unbounded preceding and unbounded following

2.8总体流程

1）通过partition by 和 order by 子句确定大窗口（定义出上界unbounded preceding和下界unbounded following）

2）通过row 子句针对每一行数据确定小窗口（滑动窗口）

3）对每行的小窗口内的数据执行函数并生成新的列

2.9 函数分类

排序类：

演示SQL

CREATE TABLE `t_emp` (
  `id` bigint(19) NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  `group_name` varchar(11) DEFAULT NULL,
  `salary` bigint(19) DEFAULT NULL,
  PRIMARY KEY (`id`)
) 

INSERT INTO `t_emp`(`id`, `name`, `group_name`, `salary`) VALUES (1, '张三', '开发部', 10000);
INSERT INTO `t_emp`(`id`, `name`, `group_name`, `salary`) VALUES (2, '李四', '开发部', 12000);
INSERT INTO `t_emp`(`id`, `name`, `group_name`, `salary`) VALUES (3, '王五', '开发部', 13000);
INSERT INTO `t_emp`(`id`, `name`, `group_name`, `salary`) VALUES (4, '小明', '运维部', 20000);
INSERT INTO `t_emp`(`id`, `name`, `group_name`, `salary`) VALUES (5, '小胡', '运维部', 10000);
INSERT INTO `t_emp`(`id`, `name`, `group_name`, `salary`) VALUES (6, '小王', '运维部', 12000);
INSERT INTO `t_emp`(`id`, `name`, `group_name`, `salary`) VALUES (7, '小李', '运维部', 13000);
INSERT INTO `t_emp`(`id`, `name`, `group_name`, `salary`) VALUES (8, '小张', '测试部', 17000);
INSERT INTO `t_emp`(`id`, `name`, `group_name`, `salary`) VALUES (9, '小刘', '测试部', 13000);
INSERT INTO `t_emp`(`id`, `name`, `group_name`, `salary`) VALUES (10, '山西', '测试部', 14000);
INSERT INTO `t_emp`(`id`, `name`, `group_name`, `salary`) VALUES (11, '小花', '测试部', 15000);
INSERT INTO `t_emp`(`id`, `name`, `group_name`, `salary`) VALUES (12, '小华', '美工部', 30000);
INSERT INTO `t_emp`(`id`, `name`, `group_name`, `salary`) VALUES (13, '杰克', '美工部', 25000);
INSERT INTO `t_emp`(`id`, `name`, `group_name`, `salary`) VALUES (14, '爱丽丝', '美工部', 26000);
INSERT INTO `t_emp`(`id`, `name`, `group_name`, `salary`) VALUES (15, '斯维尔', '美工部', 40000);
INSERT INTO `t_emp`(`id`, `name`, `group_name`, `salary`) VALUES (16, '杰克逊', '人事部', 15000);
INSERT INTO `t_emp`(`id`, `name`, `group_name`, `salary`) VALUES (17, '露西', '人事部', 16000);
INSERT INTO `t_emp`(`id`, `name`, `group_name`, `salary`) VALUES (18, '杰瑞', '人事部', 16000);

RANK() 根据排序字段进行排序，生成一列排序号，如果排名有相同的，排序是可能不连续的

SELECT RANK() OVER( ORDER BY salary DESC ) AS sort_number,  id,name,group_name,salary FROM  t_emp

DENSE_RANK() 根据排序字段进行排序，生成一列排序号，如果排名有相同的，dense_rank函数的排序是连续的

SELECT DENSE_RANK() OVER( ORDER BY salary DESC )  AS sort_number,  id,name,group_name,salary FROM  t_emp

ROW_NUMBER() 根据排序字段进行排序，生成一列排序号，即使对于两行相同的数据，也会根据查询到的顺序进行排名

SELECT ROW_NUMBER() OVER( ORDER BY salary DESC )  AS sort_number,  id,name,group_name,salary FROM  t_emp

聚合类：

-- 计算出每组的总工资
SELECT *, SUM(salary) OVER (PARTITION BY group_name)  AS group_sum_salary ,
--  计算出每个组的累计总工资
SUM(salary) OVER (PARTITION BY group_name ORDER BY id )  AS group_salary_accumulative ,
-- 按照id进行排序，然后对排序完的salary进行累加处理
SUM(salary) OVER (ORDER BY id )  AS accumulative_salar ,
-- 对 salary 进行求和
SUM(salary) OVER ()  AS salar_sum  FROM t_emp

跨行类

lag/lead 函数参数1:比较的列参数2: 偏移量参数3:找不到的默认值 (lag()用于访问前面行，lead()用于访问后面行)

--  查询同一部门比薪资比自己高2名的人的薪资
SELECT   id,name,group_name,salary ,lead(salary, 2) OVER( ORDER BY group_name   ) temp_salary  FROM  t_emp

2.10 组内分组TOP-N

SELECT * FROM
    (
        SELECT *, ROW_NUMBER() over (PARTITION BY group_name ORDER BY  salary DESC) AS rn FROM t_emp
    ) AS tmp
WHERE rn <= 2;

2.11 查询连续登录人数

SQL:

CREATE TABLE `t_login` (
  `user_id` varchar(2) DEFAULT NULL,
  `login_date` date DEFAULT NULL
) 
INSERT INTO `t_login`(`user_id`, `login_date`) VALUES ('A', '2022-09-03');
INSERT INTO `t_login`(`user_id`, `login_date`) VALUES ('A', '2022-09-04');
INSERT INTO `t_login`(`user_id`, `login_date`) VALUES ('A', '2022-08-31');
INSERT INTO `t_login`(`user_id`, `login_date`) VALUES ('B', '2021-11-25');
INSERT INTO `t_login`(`user_id`, `login_date`) VALUES ('B', '2021-12-31');
INSERT INTO `t_login`(`user_id`, `login_date`) VALUES ('C', '2022-01-01');
INSERT INTO `t_login`(`user_id`, `login_date`) VALUES ('C', '2022-04-04');
INSERT INTO `t_login`(`user_id`, `login_date`) VALUES ('C', '2022-09-03');
INSERT INTO `t_login`(`user_id`, `login_date`) VALUES ('C', '2022-09-05');
INSERT INTO `t_login`(`user_id`, `login_date`) VALUES ('C', '2022-09-04');
INSERT INTO `t_login`(`user_id`, `login_date`) VALUES ('A', '2022-09-03');
INSERT INTO `t_login`(`user_id`, `login_date`) VALUES ('D', '2022-10-20');
INSERT INTO `t_login`(`user_id`, `login_date`) VALUES ('D', '2022-10-21');
INSERT INTO `t_login`(`user_id`, `login_date`) VALUES ('A', '2022-10-03');
INSERT INTO `t_login`(`user_id`, `login_date`) VALUES ('D', '2022-10-22');
INSERT INTO `t_login`(`user_id`, `login_date`) VALUES ('D', '2022-10-23');
INSERT INTO `t_login`(`user_id`, `login_date`) VALUES ('B', '2022-01-04');
INSERT INTO `t_login`(`user_id`, `login_date`) VALUES ('B', '2022-01-05');
INSERT INTO `t_login`(`user_id`, `login_date`) VALUES ('B', '2022-11-16');
INSERT INTO `t_login`(`user_id`, `login_date`) VALUES ('B', '2022-11-17');
INSERT INTO `t_login`(`user_id`, `login_date`) VALUES ('A', '2022-09-05');

--先查询出原本的临时表，然后按照日期进行排序，计算出当前行日期和后一行日期差值，计算出当前行日期和后2行日期差值，生成临时表然后再去连接原表
WITH t1 AS (
   SELECT DISTINCT user_id ,login_date FROM t_login
),
t2 AS (
    SELECT *, DATEDIFF(login_date, lag(login_date, 1) over (PARTITION BY user_id ORDER BY login_date)) AS diff,
           DATEDIFF(login_date, lag(login_date, 2) over (PARTITION BY user_id ORDER BY login_date)) AS diff2 FROM t1
)
SELECT DISTINCT user_id FROM t2 WHERE diff = 1 AND diff2 = 2 GROUP BY  user_id;