窗口函数简单讲解

参考

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)
;

练习题

https://www.nowcoder.com/practice/90778f5ab7d64d35a40dc1095ff79065?tpId=199&tqId=1980672&ru=/exam/oj&qru=/ta/sql-quick-study/question-ranking&sourceUrl=%2Fexam%2Foj