在窗口函数必知必会Ⅰ中我们已经介绍了窗口函数的核心价值聚合 保留明细、语法结构、窗口帧规则以及事件流法。这些是理解窗口函数的内功。本文将进入招式层面系统讲解常用的窗口函数分为三大类排序函数ROW_NUMBER / RANK / DENSE_RANK / NTILE / PERCENT_RANK偏移函数LAG / LEAD / FIRST_VALUE / LAST_VALUE聚合窗口函数SUM / COUNT / AVG / MAX / MIN每个函数都配有简例帮助快速理解用法和典型场景。第一章 排序函数排序函数用于给数据打排名是最常用的窗口函数之一。1.1 ROW_NUMBER —— 强行编号语法ROW_NUMBER()OVER(PARTITIONBY分区字段ORDERBY排序字段)特点每行分配一个唯一的连续整数即使值相同也不会并列。简例SELECTname,score,ROW_NUMBER()OVER(ORDERBYscoreDESC)ASrnFROMt_score;namescorern张三951李四902王五903赵六854李四和王五都是 90 分但ROW_NUMBER强行给了一个先后顺序2 和 3不会并列。1.2 RANK —— 并列跳号语法RANK()OVER(PARTITIONBY分区字段ORDERBY排序字段)特点相同值并列排名后续排名跳过已占的名次。简例SELECTname,score,RANK()OVER(ORDERBYscoreDESC)ASrkFROMt_score;namescorerk张三951李四902王五902赵六854李四和王五并列第 2下一个排名直接跳到第 4没有第 3。1.3 DENSE_RANK —— 并列不跳号语法DENSE_RANK()OVER(PARTITIONBY分区字段ORDERBY排序字段)特点相同值并列排名后续排名不跳号。简例SELECTname,score,DENSE_RANK()OVER(ORDERBYscoreDESC)ASdrkFROMt_score;namescoredrk张三951李四902王五902赵六853李四和王五并列第 2下一个排名是第 3不跳号。1.4 三者对比总结用同一份数据张三95、李四90、王五90、赵六85对比namescoreROW_NUMBERRANKDENSE_RANK张三95111李四90222王五90322赵六85443函数并列跳号适用场景ROW_NUMBER不并列—需要唯一排名如取 Top-N 去重RANK并列跳号竞赛排名如第1名、第2名并列、第4名DENSE_RANK并列不跳号需要连续排名如一等奖、二等奖并列、三等奖如何选择要唯一编号 →ROW_NUMBER要并列且允许跳号 →RANK要并列但不跳号 →DENSE_RANK1.5 NTILE —— 分桶语法NTILE(N)OVER(PARTITIONBY分区字段ORDERBY排序字段)特点将分区内的数据尽量均匀地分成 N 个桶组返回每行所在的桶编号。简例SELECTname,score,NTILE(3)OVER(ORDERBYscoreDESC)ASbucketFROMt_score;namescorebucket张三951李四901王五902赵六8534 个人分 3 个桶第1桶2人第2桶1人第3桶1人。尽量均匀分配。典型场景将用户按消费金额分为高/中/低三档将数据按百分比分段处理分配规则如果不能整除前面的桶多分 1 个例如 10 个人分 3 组4, 3, 31.6 PERCENT_RANK —— 百分位排名语法PERCENT_RANK()OVER(PARTITIONBY分区字段ORDERBY排序字段)特点返回当前行在分区中的百分位排名值为[0, 1]之间的小数。计算公式PERCENT_RANK (当前行的RANK - 1) / (分区总行数 - 1)简例SELECTname,score,PERCENT_RANK()OVER(ORDERBYscoreDESC)ASpct_rankFROMt_score;namescorepct_rank张三950.0李四900.333…王五900.333…赵六851.0计算过程张三RANK1(1-1)/(4-1) 0.0李四/王五RANK2(2-1)/(4-1) 0.333赵六RANK4(4-1)/(4-1) 1.0典型场景判断某个值处于什么百分位如你的薪资超过了 80% 的人异常检测百分位接近 0 或 1 的可能是异常值第二章 偏移函数偏移函数用于跨行取值——获取当前行的前一行或后一行的数据。2.1 LAG —— 取前 N 行的值语法LAG(字段名,N,默认值)OVER(PARTITIONBY分区字段ORDERBY排序字段)参数说明字段名要取的字段N往前偏移几行默认 1默认值超出边界时的返回值默认 NULL简例SELECTdate,amount,LAG(amount)OVER(ORDERBYdate)ASprev_amount,amount-LAG(amount)OVER(ORDERBYdate)ASdiffFROMt_daily_sales;dateamountprev_amountdiff2024-01-01100NULLNULL2024-01-022001001002024-01-033002001002024-01-04400300100第一行没有上一行所以prev_amount为 NULL。典型场景环比计算本月 vs 上月登录间隔分析本次登录 vs 上次登录用户留存次日是否回访2.2 LEAD —— 取后 N 行的值语法LEAD(字段名,N,默认值)OVER(PARTITIONBY分区字段ORDERBY排序字段)参数含义与 LAG 相同只是方向相反——向后取值。简例SELECTdate,amount,LEAD(amount)OVER(ORDERBYdate)ASnext_amountFROMt_daily_sales;dateamountnext_amount2024-01-011002002024-01-022003002024-01-033004002024-01-04400NULL最后一行没有下一行所以next_amount为 NULL。典型场景预测/趋势看下一个时间点的值会话间隔下次购买距本次多久2.3 FIRST_VALUE —— 取分组内第一个值语法FIRST_VALUE(字段名)OVER(PARTITIONBY分区字段ORDERBY排序字段)特点返回分区内排序后的第一个值。简例SELECTdept,name,salary,FIRST_VALUE(name)OVER(PARTITIONBYdeptORDERBYsalaryDESC)AStop_employeeFROMt_employee;deptnamesalarytop_employee技术部张三25000张三技术部李四20000张三技术部王五18000张三市场部赵六22000赵六市场部孙七15000赵六每个部门都能看到薪资最高的员工是谁。典型场景查看每个分组中的最大/最小/首条记录拼接组内最优字段到每一行2.4 LAST_VALUE —— 取分组内最后一个值语法LAST_VALUE(字段名)OVER(PARTITIONBY分区字段ORDERBY排序字段ROWSBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING)注意LAST_VALUE必须显式指定窗口帧ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING否则默认窗口帧只到当前行取到的不是真正的最后一个值。简例SELECTdept,name,salary,LAST_VALUE(name)OVER(PARTITIONBYdeptORDERBYsalaryDESCROWSBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING)ASbottom_employeeFROMt_employee;deptnamesalarybottom_employee技术部张三25000王五技术部李四20000王五技术部王五18000王五市场部赵六22000孙七市场部孙七15000孙七每个部门都能看到薪资最低的员工是谁。踩坑提醒-- ❌ 错误不加窗口帧结果可能不符合预期LAST_VALUE(name)OVER(PARTITIONBYdeptORDERBYsalaryDESC)-- ✅ 正确显式指定全分区窗口帧LAST_VALUE(name)OVER(PARTITIONBYdeptORDERBYsalaryDESCROWSBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING)2.5 偏移函数对比函数方向边界返回典型场景LAG向前上方NULL环比、间隔分析、留存LEAD向后下方NULL趋势预测、下次购买FIRST_VALUE分组内第一个始终有值组内最优/首条LAST_VALUE分组内最后一个需显式窗口帧组内最差/末条第三章 聚合窗口函数将常见的聚合函数SUM / COUNT / AVG / MAX / MIN放在窗口中执行实现聚合 保留明细。3.1 三种聚合模式聚合窗口函数的核心在于窗口帧的不同决定了聚合的范围模式写法聚合范围效果全分区聚合SUM(col) OVER (PARTITION BY col)整个分区每行都看到相同的汇总值累计聚合SUM(col) OVER (ORDER BY col)从第一行到当前行逐行累加滑动窗口SUM(col) OVER (ORDER BY col ROWS BETWEEN N PRECEDING AND N FOLLOWING)当前行 ± N 行移动求和这三种模式的区别完全来自窗口帧的不同回顾教程一2.2.3 节的默认窗口帧规则。3.2 全分区聚合语法聚合函数(字段)OVER(PARTITIONBY分区字段)不加 ORDER BY窗口帧默认为整个分区。简例SELECTname,dept,salary,SUM(salary)OVER(PARTITIONBYdept)ASdept_total,salary/SUM(salary)OVER(PARTITIONBYdept)ASsalary_ratioFROMt_employee;namedeptsalarydept_totalsalary_ratio张三技术部25000630000.397李四技术部20000630000.317王五技术部18000630000.286赵六市场部22000370000.595孙七市场部15000370000.405dept_total每行都一样整个部门的总薪资可以用来算每个人的薪资占比。典型场景计算占比部分 / 整体计算每个人的贡献度与整体平均值对比3.3 累计聚合语法聚合函数(字段)OVER(ORDERBY排序字段)有 ORDER BY窗口帧默认为从第一行到当前行。简例SELECTdate,amount,SUM(amount)OVER(ORDERBYdate)AScumulative_sumFROMt_daily_sales;dateamountcumulative_sum2024-01-011001002024-01-022003002024-01-033006002024-01-044001000每一行的cumulative_sum都是从第一天到当天的累计总和。典型场景累计销售额 / 累计用户数累计占比用于 ABC 分析、帕累托图商品等级划分累计销售额占比 × 103.4 滑动窗口聚合语法聚合函数(字段)OVER(ORDERBY排序字段ROWSBETWEENNPRECEDINGANDNFOLLOWING)需要显式指定窗口帧。简例3日移动平均SELECTdate,amount,AVG(amount)OVER(ORDERBYdateROWSBETWEEN1PRECEDINGAND1FOLLOWING)ASmoving_avgFROMt_daily_sales;dateamountmoving_avg2024-01-01100150.02024-01-02200200.02024-01-03300300.02024-01-04400350.02024-01-05500450.0第1行的移动平均 (100200)/2 150前面没有数据第2行的移动平均 (100200300)/3 200第3行的移动平均 (200300400)/3 300典型场景N日移动平均平滑趋势线滑动窗口内的最大/最小值短期波动分析3.5 各聚合函数的窗口用法对比函数全分区累计滑动窗口典型场景SUM总额、占比累计求和滑动求和销售额分析COUNT总数、计数累计计数滑动计数用户增长分析AVG整体均值累计均值移动平均趋势平滑MAX全局最大值截至目前的最大值窗口内最大值峰值检测MIN全局最小值截至目前的最小值窗口内最小值谷值检测附录窗口函数速查表函数用途语法模板ROW_NUMBER唯一行号ROW_NUMBER() OVER (PARTITION BY col ORDER BY col)RANK并列跳号排名RANK() OVER (PARTITION BY col ORDER BY col)DENSE_RANK并列不跳号排名DENSE_RANK() OVER (PARTITION BY col ORDER BY col)NTILE(N)分桶NTILE(N) OVER (PARTITION BY col ORDER BY col)PERCENT_RANK百分位排名PERCENT_RANK() OVER (PARTITION BY col ORDER BY col)LAG(col, N)取前第 N 行LAG(col, N) OVER (PARTITION BY col ORDER BY col)LEAD(col, N)取后第 N 行LEAD(col, N) OVER (PARTITION BY col ORDER BY col)FIRST_VALUE(col)分组内第一个值FIRST_VALUE(col) OVER (PARTITION BY col ORDER BY col)LAST_VALUE(col)分组内最后一个值LAST_VALUE(col) OVER (... ROWS BETWEEN ... AND UNBOUNDED FOLLOWING)SUM(col)窗口内求和SUM(col) OVER (PARTITION BY col [ORDER BY col])COUNT(col)窗口内计数COUNT(col) OVER (PARTITION BY col [ORDER BY col])AVG(col)窗口内平均AVG(col) OVER (PARTITION BY col [ORDER BY col])MAX(col)窗口内最大值MAX(col) OVER (PARTITION BY col [ORDER BY col])MIN(col)窗口内最小值MIN(col) OVER (PARTITION BY col [ORDER BY col])写在最后本文系统讲解了 14 个常用窗口函数的语法和用法。限于篇幅和笔者水平部分函数的边界行为和高级用法可能未完全覆盖示例也相对简化。如有疏漏或表述不当之处欢迎指正交流。学完内功教程一和招式本文接下来就是实战。推荐继续阅读《窗口函数教程三面试考察重点》通过 5 道面试题将所学知识串联起来并附有面试官视角的考核要点与应对策略帮组读者从会写窗口进阶到能用窗口并应对面试做到胸中有数。
