参考
https://mode.com/sql-tutorial/sql-window-functions
https://dev.mysql.com/doc/refman/8.0/en/window-functions-frames.html
https://www.geeksforgeeks.org/window-functions-in-sql/
https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html
什么是窗口函数
来自 PostgreSQL 文档的定义:
窗口函数在与当前行相关的一组行上执行计算,可以使用聚合函数进行的计算类型相媲美。但是,与常规的聚合功能不同,使用窗口函数不会导致行聚合为单个输出行(每一行都会进行保留)。
关键点:
- 窗口
- 聚合
- 每一行都会有结果
表准备
注意:mysql 是从 8.0 开始支持窗口函数的
为了演示方便,创建一张销量表并插入演示数据:
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for sales
-- ----------------------------
DROP TABLE IF EXISTS `sales`;
CREATE TABLE `sales` (
`id` int UNSIGNED NOT NULL AUTO_INCREMENT,
`year` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
`country` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
`product` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
`profit` int NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 14 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of sales
-- ----------------------------
INSERT INTO `sales` VALUES (1, '2000', 'Finland', 'Computer', 1500);
INSERT INTO `sales` VALUES (2, '2000', 'Finland', 'Phone', 2000);
INSERT INTO `sales` VALUES (3, '2000', 'Finland', 'Phone', 10);
INSERT INTO `sales` VALUES (4, '2000', 'India', 'Calculator', 75);
INSERT INTO `sales` VALUES (5, '2000', 'India', 'Calculator', 75);
INSERT INTO `sales` VALUES (6, '2000', 'India', 'Computer', 1200);
INSERT INTO `sales` VALUES (7, '2000', 'USA', 'Calculator', 75);
INSERT INTO `sales` VALUES (8, '2000', 'USA', 'Computer', 1500);
INSERT INTO `sales` VALUES (9, '2001', 'USA', 'Calculator', 50);
INSERT INTO `sales` VALUES (10, '2001', 'USA', 'Computer', 1500);
INSERT INTO `sales` VALUES (11, '2001', 'USA', 'Computer', 1200);
INSERT INTO `sales` VALUES (12, '2001', 'USA', 'TV', 150);
INSERT INTO `sales` VALUES (13, '2001', 'USA', 'TV', 100);
SET FOREIGN_KEY_CHECKS = 1;
语法
SELECT coulmn_name1,
window_function(cloumn_name2)
OVER([PARTITION BY column_name1] [ORDER BY column_name3] [frame_units(ROWS|RANGE) frame_extent()]) AS new_column
FROM table_name;
frame_extent:
{frame_start | frame_between}
frame_between:
BETWEEN frame_start AND frame_end
frame_start, frame_end: {
CURRENT ROW
| UNBOUNDED PRECEDING
| UNBOUNDED FOLLOWING
| expr PRECEDING
| expr FOLLOWING
}
例子:
ORDER BY X ASC RANGE BETWEEN 10 PRECEDING AND 15 FOLLOWING
ORDER BY X ASC ROWS 10 PRECEDING
ORDER BY X ASC ROWS 10 FOLLOWING
根据语法写出来的例子:
-- 当前行和当前行的前一行的利润和
SELECT
id
,year
,country
,product
,profit
,SUM(profit) over(PARTITION BY year order by id rows 1 PRECEDING) rolling_sum_profit
FROM sales
;
-- 当前行的前一行、当前行、当前行的后一行的利润和
SELECT
id
,year
,country
,product
,profit
,SUM(profit) over(PARTITION BY year order by id RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) rolling_sum_profit
FROM sales
;
窗口函数
聚合型窗口函数
AVG()
BIT_AND()
BIT_OR()
BIT_XOR()
COUNT()
JSON_ARRAYAGG()
JSON_OBJECTAGG()
MAX()
MIN()
STDDEV_POP(), STDDEV(), STD()
STDDEV_SAMP()
SUM()
VAR_POP(), VARIANCE()
VAR_SAMP()
非聚合型窗口函数
CUME_DIST()
DENSE_RANK()
FIRST_VALUE()
LAG()
LAST_VALUE()
LEAD()
NTH_VALUE()
NTILE()
PERCENT_RANK()
RANK()
ROW_NUMBER()
常用窗口函数例子
聚合型
avg、sum、count、max、min
-- 使用 count 统计每一行记录所在年份的年销量
SELECT
*
,COUNT(1) over(PARTITION by year) as year_sales_cnt
FROM sales
;
-- 使用 sum 统计每一行记录所在年份的年利润
SELECT
*
,sum(profit) over(PARTITION by year) as year_profit_sum
FROM sales
;
非聚合型
lead、lag
-- 求每一行记录的下一行记录的 profit 列的值(如果没有下一行,就填充 NULL)
SELECT
id
,year
,country
,product
,profit
,LEAD(profit, 1, NULL) OVER (PARTITION BY year, country ORDER BY product) AS next_profit
FROM
sales;
-- 求每一行记录的上一行记录的 profit 列的值(如果没有下一行,就填充 NULL)
SELECT
id
,year
,country
,product
,profit
,LAG(profit, 1, NULL) OVER (PARTITION BY year, country ORDER BY product) AS next_profit
FROM
sales;
row_number、rank、dense_rank
面试常问三者的区别
-- row_number 就是起行号
-- rank 会跳排名,是稀疏的
-- dense_rank 不会跳排名,是稠密的
SELECT
id
,year
,profit
,ROW_NUMBER() OVER (PARTITION BY year order by profit) AS row_nm
,RANK() over (PARTITION BY year order by profit) as rk
,DENSE_RANK() over (PARTITION BY year order by profit) as dense_rk
FROM
sales;
first_value、last_value、nth_value
-- 分别获取当前窗口的第 1、2、3、4 行记录的 profit 列的值
SELECT
id
,year
,profit
,FIRST_VALUE(profit) OVER (PARTITION BY year ORDER BY profit ROWS UNBOUNDED PRECEDING) AS 'first'
,LAST_VALUE(profit) OVER (PARTITION BY year ORDER BY profit ROWS UNBOUNDED PRECEDING) AS 'last'
,NTH_VALUE(profit, 2) OVER (PARTITION BY year ORDER BY profit ROWS UNBOUNDED PRECEDING) AS 'second'
,NTH_VALUE(profit, 4) OVER (PARTITION BY year ORDER BY profit ROWS UNBOUNDED PRECEDING) AS 'fourth'
FROM sales
;
-- 使用窗口别名简化后的
SELECT
id
,year
,profit
,FIRST_VALUE(profit) OVER w AS 'first'
,LAST_VALUE(profit) OVER w AS 'last'
,NTH_VALUE(profit, 2) OVER w AS 'second'
,NTH_VALUE(profit, 4) OVER w AS 'fourth'
FROM sales
WINDOW w AS (PARTITION BY year ORDER BY profit ROWS UNBOUNDED PRECEDING);
窗口别名
SELECT
id
,year
,country
,product
,profit
,SUM(profit) over w as rolling_sum_profit
FROM sales
WINDOW w as (PARTITION BY year order by id RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING)
;