掌握SQL窗口函数，轻松处理复杂数据分析

SQL 窗口函数Window Function是一种强大的分析工具能够在不缩减原始数据行数的前提下执行复杂计算。这种函数通过对一组相关数据行称为窗口进行计算并将结果直接附加到每一行记录中。窗口函数的主要特点‌保持原始行数‌与传统的聚合函数如GROUP BY不同窗口函数不会合并行而是为每一行返回一个计算结果。‌基于窗口计算‌窗口函数的计算基于一个“窗口”范围这个窗口可以通过OVER()子句定义。‌支持多种类型函数‌‌1. 排序类函数Ranking Functions‌用于在分组内进行排名是处理“Top N”问题的核心工具。表格函数详细说明使用场景典型SQL模式ROW_NUMBER()为每行分配唯一序号无并列严格递增分页查询、去重取唯一记录ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC)RANK()并列排名相同值同名次后续跳过名次竞赛排名、成绩并列展示RANK() OVER (ORDER BY score DESC)DENSE_RANK()并列排名后续不跳名次保持连续奖项等级划分、梯队分析DENSE_RANK() OVER (PARTITION BY region ORDER BY sales)NTILE(n)将数据均分为n组返回组号客户分层、收入五分位分析NTILE(4) OVER (ORDER BY income)PARTITION BY可实现分组内排名若省略则全表作为一个窗口计算。‌2. 聚合类函数Aggregate Functions as Window Functions‌在保留原始行的基础上实现累计、移动、滑动统计。表格函数详细说明使用场景典型SQL模式SUM()窗口内累计求和月度累计销售额、滚动总额SUM(sales) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)AVG()窗口内平均值移动平均、趋势分析AVG(price) OVER (PARTITION BY product ORDER BY date ROWS 2 PRECEDING)COUNT()统计窗口内行数活跃用户累计计数COUNT(*) OVER (PARTITION BY user_id ORDER BY login_date)MAX()/MIN()获取窗口内极值历史最高/最低对比MAX(temp) OVER (PARTITION BY city ORDER BY day)使用ROWS或RANGE明确窗口范围避免全表扫描性能问题UNBOUNDED PRECEDING表示从分区第一行开始。‌3. 取值类函数Value Access Functions‌用于跨行取值实现环比、同比、趋势预测等分析。表格函数详细说明使用场景典型SQL模式LAG(col, n, default)取当前行前第n行的值与上期对比、环比增长LAG(sales, 1) OVER (ORDER BY month)LEAD(col, n, default)取当前行后第n行的值预测、下期对比LEAD(price, 1) OVER (PARTITION BY stock ORDER BY date)FIRST_VALUE(col)取窗口第一行的值初始值对比、基期分析FIRST_VALUE(salary) OVER (PARTITION BY emp_id ORDER BY year)LAST_VALUE(col)取窗口最后一行的值末期值提取需配合RANGE BETWEEN ...LAST_VALUE(score) OVER (ORDER BY date RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)LAST_VALUE()默认窗口为RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW需显式调整为UNBOUNDED FOLLOWING才能取到最后。‌4. 分布类函数Distribution Functions‌用于分析数据在分组中的相对位置和分布密度。表格函数详细说明使用场景典型SQL模式PERCENT_RANK()计算相对排名0到1公式(rank-1)/(总行数-1)成绩百分位、绩效分布PERCENT_RANK() OVER (ORDER BY score)CUME_DIST()小于等于当前值的比例公式≤当前值的行数 / 总行数客户覆盖率、达标率分析CUME_DIST() OVER (ORDER BY revenue)CUME_DIST()对重复值更敏感适合用于“有多少人低于我”的场景。基本语法结构sqlCopy Code窗口函数 OVER ( [PARTITION BY 分组列] [ORDER BY 排序列] [ROWS/RANGE 窗口框架定义] )PARTITION BY用于将数据划分为不同的组类似于GROUP BY但不减少行数。ORDER BY定义窗口内的排序方式影响窗口函数的计算顺序。ROWS或RANGE定义窗口的范围例如当前行前后几行或某个值范围内的行。注意 [ROWS/RANGE 窗口框架定义] 默认值是**1. 没有指定 ORDER BY‌窗口函数将应用于整个结果集相当于对所有行进行计算。2. 指定了 ORDER BY‌默认的窗口帧是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW这意味着窗口从分区的第一行开始到当前行结束。ROWS‌‌定义‌ROWS基于物理行号来定义窗口范围严格按照行的顺序进行计数。‌特点严格按照行的物理位置来确定窗口。即使排序列的值相同也会被单独计数。适用于需要精确控制行数的场景例如计算最近 N 笔交易或前后 N 天的记录。‌示例ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING表示当前行及其前后各一行共三行。ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW表示从第一行到当前行的所有行。‌RANGE‌‌定义‌RANGE基于排序列的值范围来定义窗口将排序值相同的行视为一个逻辑组。‌特点‌将排序列中值相等的所有行视为一个逻辑单元。适用于处理有重复值的场景例如按日期或金额计算累积值。通常与ORDER BY子句一起使用。‌示例RANGE BETWEEN 10 PRECEDING AND CURRENT ROW表示当前行排序值范围内前 10 个单位的行。RANGE BETWEEN CURRENT ROW AND CURRENT ROW仅包含当前行。‌两者区别总结‌特性ROWSRANGE定义方式基于物理行号基于排序列值范围适用场景精确控制行数处理重复值、逻辑范围重复值处理单独计数合并为一个逻辑组例子本例子是使用mysql8.0版本进行测试初始数据的准备CREATE TABLE business ( name VARCHAR(255), orderdate DATE, cost INT ); ​ INSERT INTO business VALUES (jack, 2017-01-01, 10), (tony, 2017-01-02, 15), (jack, 2017-02-03, 23), (tony, 2017-01-04, 29), (jack, 2017-01-05, 46), (jack, 2017-04-06, 42), (tony, 2017-01-07, 50), (jack, 2017-01-08, 55), (mart, 2017-04-08, 62), (mart, 2017-04-09, 68), (neil, 2017-05-10, 12), (mart, 2017-04-11, 75), (neil, 2017-06-12, 80), (mart, 2017-04-13, 94);相关例子查询在2017年4月份购买过的顾客及总人数SELECT DISTINCT name AS customer_name, COUNT(DISTINCT name) OVER() AS total_customers FROM business WHERE SUBSTRING(orderdate, 1, 7) 2017-04 ORDER BY name;查询顾客的购买明细及月购买总额SELECT name AS customer_name, orderdate AS order_date, cost AS purchase_amount, SUM(cost) OVER(PARTITION BY name, DATE_FORMAT(orderdate, %Y-%m)) AS monthly_total, COUNT(*) OVER(PARTITION BY name, DATE_FORMAT(orderdate, %Y-%m)) AS monthly_purchase_count FROM business ORDER BY name, orderdate;3.求每个顾客的购买明细及起点到当前行的累加 上一行到当前行的累加 当前行到下一行的累加 上一行到下一行的累加 当前行到终点的累加-- 查询每个顾客的购买明细及各种累加统计 SELECT name AS customer_name, orderdate AS order_date, cost AS purchase_amount, -- 起点到当前行的累加 SUM(cost) OVER(PARTITION BY name ORDER BY orderdate ROWS UNBOUNDED PRECEDING) AS cumulative_from_start, -- 上一行到当前行的累加 SUM(cost) OVER(PARTITION BY name ORDER BY orderdate ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS sum_prev_current, -- 当前行到下一行的累加 SUM(cost) OVER(PARTITION BY name ORDER BY orderdate ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS sum_current_next, -- 上一行到下一行的累加 SUM(cost) OVER(PARTITION BY name ORDER BY orderdate ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sum_prev_next, -- 当前行到终点的累加 SUM(cost) OVER(PARTITION BY name ORDER BY orderdate ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS cumulative_to_end FROM business ORDER BY name, orderdate;4.查询每个顾客上次 和 下次 的购买时间-- 查询每个顾客的购买明细及上次和下次购买时间 SELECT name AS customer_name, orderdate AS current_purchase_date, LAG(orderdate) OVER (PARTITION BY name ORDER BY orderdate) AS previous_purchase_date, LEAD(orderdate) OVER (PARTITION BY name ORDER BY orderdate) AS next_purchase_date FROM business ORDER BY name, orderdate;查询前20%时间的订单信息SELECT name AS customer_name, orderdate AS order_date, cost AS purchase_amount FROM ( SELECT name, orderdate, cost, ---使用NTILE(5)窗口函数将所有订单按日期分为5个等份(五分位数) ---选择第一个五分位数(quintile1)的数据即最早20%时间段内的订单 --最终结果按订单日期升序排列展示前20%时间范围内的所有订单详情 NTILE(5) OVER (ORDER BY orderdate) AS quintile FROM business ) AS ranked_orders WHERE quintile 1 ORDER BY orderdate;