mysql开窗函数
求他例子参考网址https://www.cnblogs.com/thxj/p/12727589.html
知识点总结
sum(...A...) over(partition by ...B... order by ...C... rows between ...D1... and ...D2...)
avg(...A...) over(partition by ...B... order by ...C... rows between ...D1... and ...D2.….)
A:需要被加工的字段名称
B:分组的字段名称
C:排序的字段名称
D:计算的行数范围
rows between 2 preceding and current row #取当前行和前面两行
rows between unbounded preceding and current row #包括本行和之前所有的行
rows between current row and unbounded following #包括本行和之后所有的行
rows between 3 preceding and current row #包括本行和前面三行
rows between 3 preceding and 1 fo1lowing #从前面三行和下面一行，总共五行
#当order by后面缺少窗口从句条件，窗口规范默认是rows betiween unbounded preceding and currentrow.
#当order by和窗口从句都缺失，窗口规范默认是rows between unbounded preceding and unboundedfo77owing
窗口函数应用
专有窗口函数:rank()、dense_rank()、row_number()、ntile(n)
notile(n)说明:
在这个语法中n是一个字面正整数。桶号的范围是1到n。
在PARTITION BY从返回的结果集划分FROM子句为分区到的NTILE()函数被应用。
ORDER BY子句指定将NTILE()值分配给分区中的行的顺序。
请注意，如果分区行的数量不能被整除n，则NTILE()函数将生成两个大小的组，差异为1。较大的组总是以ORDER BY子句指定的顺序位于较小的组之前。
另一方面，如果分区行的总数可被整除n，则行将在组之间平均分配。
聚合类窗口函数:sum()、count()、avg()、max()、min()
窗口函数和普通场景下的聚合函数也很容易混淆，二者区别如下:
1.聚合函数是将多条记录聚合为一条;窗口函数是每条记录都会执行，有几条记录执行完还是几条。
2.分组(partition by):窗口按照字段进行分组，窗口函数在不同的分组上分别执行。
3.排序(order by):按照哪些字段进行排序，窗口函数将按照排序后的记录顺序进行编号，可以和partition子句配合使用，也可以单独使用。如果没有partition子句，数据范围则是整个表的数据行。
4.窗口(rows):就是进行函数分析时要处理的数据范围，属于当前分区的一个子集，通常用来作为滑动窗口使用。比如要根据每个订单动态计算包括本订单和按时间顺序前后两个订单的移动平均支付金额，则可以设置rows子句来创建滑动窗口(rows)。
使用案例
/*
Navicat Premium Data Transfer
Source Server : mysql8-3308
Source Server Type : MySQL
Source Server Version : 80024
Source Host : localhost:3308
Source Schema : usertrage
Target Server Type : MySQL
Target Server Version : 80024
File Encoding : 65001
Date: 24/03/2022 15:06:33
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for usertrage
-- ----------------------------
DROP TABLE IF EXISTS `usertrage`;
CREATE TABLE `usertrage` (
`username` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '用户名',
`piece` int NULL DEFAULT NULL COMMENT '购买数量',
`price` double NULL DEFAULT NULL COMMENT '价格',
`payamount` double NULL DEFAULT NULL COMMENT '支付金额',
`goodscategory` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品品类',
`paytime` date NULL DEFAULT NULL COMMENT '支付日期'
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of usertrage
-- ----------------------------
INSERT INTO `usertrage` VALUES ('B', 1, 10, 10, 'a', '2022-01-01');
INSERT INTO `usertrage` VALUES ('B', 2, 11, 22, 'b', '2022-02-01');
INSERT INTO `usertrage` VALUES ('C', 3, 12, 36, 'c', '2022-03-01');
INSERT INTO `usertrage` VALUES ('D', 4, 13, 42, 'd', '2022-04-01');
INSERT INTO `usertrage` VALUES ('E', 5, 14, 70, 'a', '2022-05-01');
INSERT INTO `usertrage` VALUES ('F', 6, 15, 90, 'b', '2022-06-01');
INSERT INTO `usertrage` VALUES ('G', 7, 16, 112, 'c', '2022-07-01');
INSERT INTO `usertrage` VALUES ('H', 8, 17, 136, 'd', '2022-08-01');
INSERT INTO `usertrage` VALUES ('I', 9, 18, 162, 'a', '2022-09-01');
INSERT INTO `usertrage` VALUES ('J', 10, 19, 190, 'b', '2022-10-01');
INSERT INTO `usertrage` VALUES ('K', 11, 20, 220, 'c', '2022-11-01');
INSERT INTO `usertrage` VALUES ('L', 12, 21, 252, 'd', '2022-12-01');
INSERT INTO `usertrage` VALUES ('A', 1, 2, 2, 'a', '2023-01-01');
INSERT INTO `usertrage` VALUES ('A', 2, 3, 6, 'a', '2023-02-01');
INSERT INTO `usertrage` VALUES ('B', 3, 4, 12, 'a', '2023-03-01');
INSERT INTO `usertrage` VALUES ('C', 4, 5, 20, 'a', '2023-04-01');
INSERT INTO `usertrage` VALUES ('D', 5, 6, 30, 'a', '2023-05-01');
INSERT INTO `usertrage` VALUES ('D', 6, 7, 42, 'b', '2023-06-01');
INSERT INTO `usertrage` VALUES ('A', 7, 8, 56, 'b', '2023-07-01');
INSERT INTO `usertrage` VALUES ('B', 8, 9, 72, 'b', '2023-08-01');
INSERT INTO `usertrage` VALUES ('C', 9, 10, 90, 'c', '2023-09-01');
INSERT INTO `usertrage` VALUES ('D', 10, 11, 110, 'c', '2023-10-01');
INSERT INTO `usertrage` VALUES ('D', 11, 12, 132, 'c', '2023-11-01');
INSERT INTO `usertrage` VALUES ('A', 12, 13, 156, 'c', '2023-12-01');
INSERT INTO `usertrage` VALUES ('A', 1, 10, 10, 'a', '2022-01-01');
INSERT INTO `usertrage` VALUES ('B', 2, 11, 22, 'b', '2022-02-01');
INSERT INTO `usertrage` VALUES ('C', 3, 12, 36, 'c', '2022-03-01');
INSERT INTO `usertrage` VALUES ('D', 4, 13, 42, 'd', '2022-04-01');
INSERT INTO `usertrage` VALUES ('E', 5, 14, 70, 'a', '2022-05-01');
INSERT INTO `usertrage` VALUES ('F', 6, 15, 90, 'b', '2022-06-01');
INSERT INTO `usertrage` VALUES ('G', 7, 16, 112, 'c', '2022-07-01');
INSERT INTO `usertrage` VALUES ('H', 8, 17, 136, 'd', '2022-08-01');
INSERT INTO `usertrage` VALUES ('I', 9, 18, 162, 'a', '2022-09-01');
INSERT INTO `usertrage` VALUES ('J', 10, 19, 190, 'a', '2022-10-01');
INSERT INTO `usertrage` VALUES ('K', 11, 20, 220, 'a', '2022-11-01');
INSERT INTO `usertrage` VALUES ('L', 12, 21, 252, 'a', '2022-12-01');
INSERT INTO `usertrage` VALUES ('A', 1, 2, 2, 'b', '2023-01-01');
INSERT INTO `usertrage` VALUES ('A', 2, 3, 6, 'b', '2023-02-01');
INSERT INTO `usertrage` VALUES ('B', 3, 4, 12, 'c', '2023-03-01');
INSERT INTO `usertrage` VALUES ('C', 4, 5, 20, 'c', '2023-04-01');
INSERT INTO `usertrage` VALUES ('D', 5, 6, 30, 'c', '2023-05-01');
INSERT INTO `usertrage` VALUES ('A', 6, 7, 42, 'd', '2023-06-01');
INSERT INTO `usertrage` VALUES ('D', 7, 8, 56, 'd', '2023-07-01');
INSERT INTO `usertrage` VALUES ('D', 8, 9, 72, 'd', '2023-08-01');
INSERT INTO `usertrage` VALUES ('A', 9, 10, 90, 'd', '2023-09-01');
INSERT INTO `usertrage` VALUES ('B', 10, 11, 110, 'd', '2023-10-01');
INSERT INTO `usertrage` VALUES ('B', 11, 12, 132, 'd', '2023-11-01');
INSERT INTO `usertrage` VALUES ('B', 12, 13, 156, 'd', '2023-12-01');
INSERT INTO `usertrage` VALUES ('A', 10, 10, 100, 'c', '2022-01-01');
INSERT INTO `usertrage` VALUES ('C', 10, 10, 100, 'c', '2022-01-01');
INSERT INTO `usertrage` VALUES ('A', 10, 10, 100, 'c', '2022-01-01');
INSERT INTO `usertrage` VALUES ('A', 10, 10, 100, 'd', '2022-01-01');
INSERT INTO `usertrage` VALUES ('A', 10, 10, 100, 'b', '2022-01-01');
INSERT INTO `usertrage` VALUES ('B', 10, 10, 100, 'a', '2022-01-01');
INSERT INTO `usertrage` VALUES ('B', 10, 10, 100, 'b', '2022-01-01');
INSERT INTO `usertrage` VALUES ('C', 10, 10, 100, 'a', '2022-01-01');
INSERT INTO `usertrage` VALUES ('D', 10, 10, 100, 'a', '2022-01-01');
SET FOREIGN_KEY_CHECKS = 1;
1累计计算函数
累计求和: sum() over()
需求1:查询出2022年每月的支付总额和当年累积支付总额
--查询出2022年每月的支付总额和当年累积支付总额
--1.获取2022年的信息
select * from usertrage where year(paytime)=2022;
--2.获取2022年每个月的支付总额
select month(paytime),sum(payamount) from usertrage where year(paytime)=2022 group by month(paytime);
--3.
select a.mpaytime,a.sumpayamount,sum(a.sumpayamount) over(order by a.mpaytime) from (select month(paytime) mpaytime,sum(payamount) sumpayamount from usertrage where year(paytime)=2022 group by month(paytime)) a;
--需求2:查询出2022和2023年每月的支付总额和当年累积支付总额
--1.获取2022-2023年每个月的支付总额
select year(paytime) ypaytime, month(paytime) mpaytime,sum(payamount) sumpayamount from usertrage where year(paytime) in(2022,2023) group by year(paytime),month(paytime);
--2.
select a.ypaytime, a.mpaytime,a.sumpayamount,sum(a.sumpayamount) over(partition by a.ypaytime order by a.ypaytime,a.mpaytime) from (select year(paytime) ypaytime, month(paytime) mpaytime,sum(payamount) sumpayamount from usertrage where year(paytime) in(2022,2023) group by year(paytime),month(paytime)) a;
--需求3:查询出2022-2023年每个月的近三月移动总支付金额(当前行向前数2)
select a.ypaytime, a.mpaytime,a.sumpayamount,sum(a.sumpayamount) over(partition by a.ypaytime order by a.ypaytime,a.mpaytime rows between 2 preceding and current row) from (select year(paytime) ypaytime, month(paytime) mpaytime,sum(payamount) sumpayamount from usertrage where year(paytime) in(2022,2023) group by year(paytime),month(paytime)) a;
--需求4:查询出2022-2023连续每四个月的最大月总支付金额(当前行向前数3)
select a.ypaytime, a.mpaytime,a.sumpayamount,max(a.sumpayamount) over(order by a.ypaytime,a.mpaytime rows between 3 preceding and current row) from (select year(paytime) ypaytime, month(paytime) mpaytime,sum(payamount) sumpayamount from usertrage where year(paytime) in(2022,2023) group by year(paytime),month(paytime)) a;
--需求5:2022年1月,购买商品品类数的用户排名(根据所购买商品涉及的品类数量给用户进行排名)
--1.查询每个用户对应的品类数(品类去重)
select username,count(distinct(goodscategory)) from usertrage where substr(paytime,1,7)='2022-01' group by username;
--2.最终查询结果如下图
select username,count(distinct(goodscategory)),row_number() over(order by count(distinct(goodscategory)) desc),rank() over(order by count(distinct(goodscategory)) desc),dense_rank() over(order by count(distinct(goodscategory)) desc) from usertrage where substr(paytime,1,7)='2022-01' group by username;
--需求6:查询出将2022年1月的支付用户，按照支付金额分成5组后的结果
--1.查询出2022年1月的支付用户及其对应的支付金额,并按照降序排列
select username,sum(payamount) from usertrage where substr(paytime,1,7)='2022-01' group by username order by sum(payamount) desc;
--2.
select username,sum(payamount),ntile(3) over(order by sum(payamount) desc) from usertrage where substr(paytime,1,7)='2022-01' group by username;
--需求7:查询出2022年支付金额排名前30%的所有用户
--按支付金额排序
select username,sum(payamount),row_number() over(order by sum(payamount) desc) as ranking from usertrage where year(paytime)='2022' group by username;
--去重查询总人数
select count(distinct(username)) from usertrage where year(paytime)='2022';
--总人数的30%,向上取整
select round(count(distinct(username))*0.3) rzo from usertrage where year(paytime)='2022';
--取排名前30%的用户的最小支付金额
select a.zonge from
(select username,sum(payamount) zonge,row_number() over(order by sum(payamount) desc) as ranking from usertrage where year(paytime)='2022' group by username)a,
(select round(count(distinct(username))*0.3) rzo from usertrage where year(paytime)='2022')b where a.ranking =b.rzo
---7.
select c.username,c.zonge from
(select username,sum(payamount) zonge,row_number() over(order by sum(payamount) desc) as ranking from usertrage where year(paytime)='2022' group by username) c,
(select a.zonge from
(select username,sum(payamount) zonge,row_number() over(order by sum(payamount) desc) as ranking from usertrage where year(paytime)='2022' group by username)a,
(select round(count(distinct(username))*0.3) rzo from usertrage where year(paytime)='2022')b where a.ranking =b.rzo) d where c.zonge>=d.zonge
--需求8:查询出每年支付金额排名前30%的所有用户
--按支付金额排序,并按照年份分组
select year(paytime), username,sum(payamount),row_number() over(partition by year(paytime) order by sum(payamount) desc) as ranking from usertrage group by username,year(paytime);
--去重查询每年的总人数
select year(paytime),count(distinct(username)) from usertrage group by year(paytime);
--每年总人数的30%,向上取整
select year(paytime),round(count(distinct(username))*0.3) rzo from usertrage group by year(paytime);
--取每年排名前30%的用户的最小支付金额
select a.nian, a.zonge from
(select year(paytime) nian, username,sum(payamount) zonge,row_number() over(partition by year(paytime) order by sum(payamount) desc) as ranking from usertrage group by username,year(paytime))a,
(select year(paytime) nian,round(count(distinct(username))*0.3) rzo from usertrage group by year(paytime))b where a.ranking =b.rzo and a.nian=b.nian;
---8.
select c.nian,c.username,c.zonge from
(select year(paytime) nian, username,sum(payamount) zonge,row_number() over(partition by year(paytime) order by sum(payamount) desc) as ranking from usertrage group by username,year(paytime)) c,
(select a.nian, a.zonge from
(select year(paytime) nian, username,sum(payamount) zonge,row_number() over(partition by year(paytime) order by sum(payamount) desc) as ranking from usertrage group by username,year(paytime))a,
(select year(paytime) nian,round(count(distinct(username))*0.3) rzo from usertrage group by year(paytime))b where a.ranking =b.rzo and a.nian=b.nian) d where c.zonge>=d.zonge and c.nian=d.nian order by c.nian, c.zonge desc;
--需求10:查询出支付时间间隔超过100天的用户
select username,paytime,lead(paytime) over(partition by username order by paytime) as leadtime from usertrage;
select distinct(a.username),a.paytime,a.leadtime,datediff(a.leadtime,a.paytime) as difftime from
(select username,paytime,lead(paytime) over(partition by username order by paytime) as leadtime from usertrage)a where datediff(a.leadtime,a.paytime)>100;
---说明:Mysql 没有nvl()函数，却有一个类似功能的函数ifnull();Oracle有NVL()函数
select a.username,a.paytime,ifnull(a.leadtime,now()),datediff(a.leadtime,a.paytime) as difftime from
(select username,paytime,lead(paytime) over(partition by username order by paytime) as leadtime from usertrage)a where datediff(a.leadtime,a.paytime)>100;
