企业官网建设流程全解析

简介：这是一份专为数据库从业者设计的《SQL窗口函数速查表》PDF文档，面向DBA、数据分析师、后端开发工程师及SQL进阶学习者，解决复杂数据分析场景下窗口函数选型难、语法易混淆、实际应用无参考等痛点。资源为单文件PDF（841KB），内容按功能分类编排，涵盖排名函数（ROW_NUMBER/RANK/DENSE_RANK）、偏移函数（LAG/LEAD）与聚合类窗口函数（SUM/AVG/COUNT等）三大核心类型，每类均配标准语法结构、参数说明及可直接复用的典型示例代码，助读者快速定位函数、理解执行逻辑并验证返回结果。速查表突出实用性：窗口定义（PARTITION BY + ORDER BY + FRAME子句）要点清晰，应用场景直击报表统计、趋势分析、同比环比计算等高频需求。目前已有255人下载学习，适合日常查询查阅、面试突击复习或教学辅助使用。

1. SQL窗口函数速查表：不是语法手册，而是你写错三次后才敢打开的“后悔药”

上周帮一个做电商BI的同学调报表，他卡在「每个品类下销量Top 3的商品，且要带累计占比」——用子查询嵌套三层、LEFT JOIN两次，执行17秒，还漏掉并列第3名。我顺手改了三行：DENSE_RANK() OVER (PARTITION BY category ORDER BY sales DESC)+SUM(sales) OVER (PARTITION BY category)+SUM(sales) OVER ()，跑完0.8秒，结果全对。他盯着屏幕说：“这玩意儿怎么不早告诉我？”——这就是《SQL窗口函数速查表.pdf》存在的真实理由：它不教你怎么背语法，而是当你在凌晨两点对着慢得像PPT的报表发呆、当你的GROUP BY+JOIN组合拳又崩了、当你被业务方追着问“为什么上月TOP5和本月TOP5不能横向对比”时，能让你30秒内翻到对应函数、抄起就跑的实战弹药库。它面向的不是刚学SELECT FROM的新手，而是已经写过200+条SQL、但每次遇到“既要分组又要保留明细”就本能想建临时表的中阶使用者；它解决的不是“什么是窗口函数”，而是“ROW_NUMBER()和RANK()到底在并列时谁跳号、LAG()的默认frame为什么是UNBOUNDED PRECEDING AND CURRENT ROW、为什么SUM() OVER()加了ORDER BY就变成长期累计而不是全量总和”这种血泪问题。PDF共28页，无废话，无理论推导，只有函数分类、参数含义、典型错误示例、兼容性标注（明确标出MySQL 8.0+/PostgreSQL 9.4+/SQL Server 2005+/Oracle 10g+支持情况），以及17个可直接粘贴进生产环境验证的最小可运行案例。

2. 窗口函数不是“高级语法”，而是SQL从“静态快照”走向“动态关系”的分水岭

2.1 为什么传统聚合函数永远无法替代窗口函数：一个订单流水账的生死局

假设你有一张orders表，含order_id,user_id,amount,order_time四列，业务需求是：“查出每个用户最新一笔订单的金额，并标记该订单是否为该用户历史最高单笔金额”。
用传统GROUP BY？不行——GROUP BY会把每个用户的多条记录压成一行，你拿不到“最新一笔”的order_time，更没法同时返回“最新订单金额”和“历史最高金额”两个值。
用子查询+关联？可以，但写法爆炸：先GROUP BY user_id求MAX(amount)，再用窗口函数或自连接找MAX(order_time)，最后LEFT JOIN回来……逻辑绕、性能差、维护难。

而窗口函数一行解：

SELECT order_id, user_id, amount, order_time, FIRST_VALUE(amount) OVER ( PARTITION BY user_id ORDER BY order_time DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS latest_amount, MAX(amount) OVER (PARTITION BY user_id) AS max_amount_per_user FROM orders;

逻辑说明：FIRST_VALUE(amount) OVER (...)在每个user_id分组内，按order_time DESC排序后取第一行的amount，即最新订单金额；MAX(amount) OVER (PARTITION BY user_id)直接计算每个用户的历史最高单笔。两者都在同一SELECT中完成，不丢失任何原始行。
参数关键点：ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING显式声明窗口范围为整个分区（等价于默认行为），避免某些数据库（如旧版MySQL）因未指定frame导致FIRST_VALUE只作用于当前行及之前行（即CURRENT ROW默认frame），造成结果错误。

2.2 三类函数的本质差异：排名、位移、聚合，底层逻辑完全不同

窗口函数绝非“加个OVER就完事”，三类函数的计算逻辑、frame依赖、NULL处理完全独立：

提示：RANGEvsROWSframe语义差异巨大。RANGE按ORDER BY列的值范围划分（如ORDER BY amount时，所有amount=100的行视为同一“range”），ROWS按物理行位置划分。绝大多数场景应显式用ROWS，避免RANGE在存在重复排序值时产生意外聚合（如AVG()对相同amount的多行重复计算）。

2.3 速查表如何帮你绕过“语法正确但结果错误”的玄学时刻

这份PDF最硬核的设计，是把每个函数的典型错误场景和修正代码并排呈现。例如NTILE(4)函数，文档不会只写“将分区分为4组”，而是直接给出：

• ❌ 错误用法：NTILE(4) OVER (ORDER BY score)—— 未PARTITION BY，全表强行四等分，高分低分混在一起，业务无意义
• ✅ 正确用法：NTILE(4) OVER (PARTITION BY subject ORDER BY score DESC)—— 按学科分组，每科内按分数降序四等分，用于“数学组前25%学生”
• ⚠️ 隐患提示：NTILE(n)无法整除时，前几组多1行（如10行分4组→3,3,2,2），若需严格均分，必须配合COUNT(*) OVER (PARTITION BY ...)动态计算组大小

这种“错误代码→现象截图→原因定位→修正方案”的结构，直击中阶用户最痛的“明明语法没错，结果就是不对”的黑匣子时刻。

3. 从速查表到生产环境：五个必须亲手验证的边界场景

3.1 场景一：空值（NULL）在ORDER BY中的排序优先级，决定排名函数结果

现象：RANK() OVER (ORDER BY score DESC)中，score为NULL的行排在最前面（如MySQL/PostgreSQL），还是最后面（如SQL Server）？业务要求“NULL视为最低分”，但实际结果却把NULL排第一，导致Top 10漏掉所有有效数据。
原因：SQL标准未规定NULL在ORDER BY中的默认位置，各数据库实现不同。MySQL/PostgreSQL默认NULLS LAST（DESC时NULL在末尾），SQL Server默认NULLS FIRST（DESC时NULL在开头）。
解决：显式声明NULLS LAST或NULLS FIRST。速查表中所有ORDER BY示例均带此子句：

RANK() OVER (PARTITION BY dept ORDER BY score DESC NULLS LAST)

验证命令（在你目标数据库执行）：

SELECT score, RANK() OVER (ORDER BY score DESC) as r1, RANK() OVER (ORDER BY score DESC NULLS LAST) as r2 FROM (VALUES (95), (87), (NULL), (92)) t(score);

对比r1与r2列，确认NULL位置。

3.2 场景二：frame_clause中UNBOUNDED PRECEDING的“时间陷阱”

现象：计算“每个用户近30天订单金额滚动平均”，用了AVG(amount) OVER (PARTITION BY user_id ORDER BY order_time ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)，但结果中早期用户（订单不足30条）的平均值为NULL，而非实际可用天数的平均。
原因：ROWS BETWEEN 29 PRECEDING AND CURRENT ROW要求窗口内必须有30行，不足则返回NULL。但业务需要的是“有多少算多少”的滚动平均。
解决：改用RANGE并配合时间列计算，或用ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW+CASE WHEN COUNT(*) OVER (...) < 30 THEN ... ELSE ... END兜底。速查表在“滚动计算”章节明确标注：

“固定行数窗口（ROWS）适用于已知数据密度场景；时间范围窗口（RANGE）需数据库支持INTERVAL，如PostgreSQL：RANGE BETWEEN INTERVAL '29 days' PRECEDING AND CURRENT ROW；若不支持，务必用COUNT()校验窗口行数。”

3.3 场景三：PARTITION BY列含NULL值，导致分区断裂

现象：SUM(sales) OVER (PARTITION BY region)中，region为NULL的行全部被归入同一组，而非各自独立（因NULL != NULL），导致“未知区域”销售额被错误累加。
原因：SQL中NULL参与等值比较恒为UNKNOWN，故PARTITION BY region无法将NULL值分到不同组，所有NULL自动聚为一组。
解决：用COALESCE(region, 'UNKNOWN_' || user_id)或CASE WHEN region IS NULL THEN 'NULL_' || order_id ELSE region END为NULL生成唯一标识。速查表在“分区键处理”小节强调：

“任何可能为NULL的PARTITION BY列，必须预处理。COALESCE(col, 'N/A')简单但会合并所有NULL；CASE WHEN col IS NULL THEN CONCAT('NULL_', ROW_NUMBER() OVER (ORDER BY order_id)) ELSE col END确保NULL行独立分区。”

3.4 场景四：ORDER BY多列时的隐式稳定性，引发ROW_NUMBER()结果不可复现

现象：ROW_NUMBER() OVER (ORDER BY create_time)在create_time相同时，不同执行返回不同行号，导致分页查询（如WHERE rn BETWEEN 11 AND 20）结果抖动。
原因：当ORDER BY列存在重复值时，ROW_NUMBER()的分配顺序由数据库内部优化器决定，无稳定性保证。
解决：ORDER BY必须包含唯一列（如主键）作为决胜列：

ROW_NUMBER() OVER (ORDER BY create_time DESC, order_id DESC)

速查表实操建议：所有涉及分页、Top N、去重的排名函数，ORDER BY末尾必须追加主键或唯一索引列，PDF中17个案例全部遵循此规范。

3.5 场景五：窗口函数嵌套——AVG() OVER() 内部再套SUM() OVER() 的执行顺序误区

现象：想计算“每个品类销售额占全站比例”，写了AVG(sales) OVER (PARTITION BY category) / SUM(sales) OVER ()，结果报错或数值荒谬。
原因：AVG() OVER (...)是窗口聚合，SUM() OVER ()也是窗口聚合，但二者无嵌套关系；正确逻辑是先算品类总和，再除以全站总和，即：

SUM(sales) OVER (PARTITION BY category) * 1.0 / SUM(sales) OVER ()

核心认知：窗口函数不嵌套，而是并列计算。速查表在“聚合函数”章节用加粗框强调：
“SUM() OVER (PARTITION BY A) / SUM() OVER ()是合法的，因为两个SUM()独立计算；但SUM(SUM() OVER (...)) OVER (...)是非法的——窗口函数不能作为另一窗口函数的输入表达式。”

4. 把速查表变成肌肉记忆：三个必须动手做的验证实验

4.1 实验一：用真实数据集验证RANK()、DENSE_RANK()、ROW_NUMBER()的并列行为差异

目标：彻底搞清三者在并列时的数字分配逻辑，避免Top N漏数据。
步骤：

1. 创建测试表（兼容所有主流数据库）：

-- PostgreSQL/MySQL 8.0+/SQL Server 2005+ 均可执行 CREATE TABLE scores ( student VARCHAR(20), subject VARCHAR(20), score INT ); INSERT INTO scores VALUES ('Alice', 'Math', 95), ('Bob', 'Math', 95), ('Charlie', 'Math', 92), ('David', 'English', 88), ('Eve', 'English', 88), ('Frank', 'English', 85);

2. 执行三函数对比查询：

SELECT student, subject, score, ROW_NUMBER() OVER (PARTITION BY subject ORDER BY score DESC) as rn, RANK() OVER (PARTITION BY subject ORDER BY score DESC) as rk, DENSE_RANK() OVER (PARTITION BY subject ORDER BY score DESC) as drk FROM scores ORDER BY subject, score DESC;

3. 观察结果：
   • Math组：Alice/Bob同分95 →rn=1,2；rk=1,1；drk=1,1
   • English组：David/Eve同分88 →rn=1,2；rk=1,1；drk=1,1
   • 关键差异：RANK()后续名次跳过（Math组92分得rk=3），DENSE_RANK()紧接（92分得drk=2），ROW_NUMBER()强制连续（92分得rn=3）
4. 业务决策：
   • 选Top 3且允许并列 → 用DENSE_RANK() <= 3
   • 选Top 3且严格3人 → 用ROW_NUMBER() <= 3
   • 选“并列第1，下一档为第3” → 用RANK() <= 3

参数说明：PARTITION BY subject确保分学科排名；ORDER BY score DESC决定高分在前；所有函数必须在同一OVER子句中使用相同PARTITION/ORDER，否则结果无意义。

4.2 实验二：用LAG()实现“环比增长”，并处理首行NULL和数据断层

目标：计算每个用户每月订单金额的环比增长率（(本月-上月)/上月），且首月、数据缺失月不报错。
步骤：

1. 构造带月份的测试数据：

WITH monthly_sales AS ( SELECT user_id, DATE_TRUNC('month', order_time) AS month, -- PostgreSQL写法，MySQL用DATE_FORMAT(order_time, '%Y-%m') SUM(amount) AS monthly_amount FROM orders GROUP BY user_id, DATE_TRUNC('month', order_time) ) SELECT user_id, month, monthly_amount, LAG(monthly_amount, 1) OVER (PARTITION BY user_id ORDER BY month) AS prev_month_amount, CASE WHEN LAG(monthly_amount, 1) OVER (PARTITION BY user_id ORDER BY month) IS NOT NULL THEN ROUND( (monthly_amount - LAG(monthly_amount, 1) OVER (PARTITION BY user_id ORDER BY month)) * 100.0 / LAG(monthly_amount, 1) OVER (PARTITION BY user_id ORDER BY month), 2) ELSE NULL END AS mom_growth_pct FROM monthly_sales ORDER BY user_id, month;

2. 关键避坑：
   • LAG(..., 1)第二参数1表示取前1行，可改为2取前两月；
   • LAG(..., 1, 0)第三参数0为默认值，当无前一行时返回0（避免NULL参与计算）；
   • CASE WHEN ... IS NOT NULL是必须的，否则(X - NULL)/NULL结果为NULL，无法区分“无上月数据”和“上月为0”的业务场景。

速查表技巧：所有位移函数示例均采用LAG(col, n, default_value)三参数写法，并在default_value处标注业务含义（如“用0填充表示无历史数据”）。

4.3 实验三：用SUM() OVER()的frame_clause实现“移动平均线”，并对比ROWS与RANGE

目标：为股票价格表计算5日移动平均线，验证ROWS BETWEEN 4 PRECEDING AND CURRENT ROW与RANGE BETWEEN INTERVAL '4 days' PRECEDING AND CURRENT ROW的行为差异。
步骤：

1. 创建含日期和价格的测试表（模拟交易日，含周末空缺）：

CREATE TABLE stock_price ( trade_date DATE, price DECIMAL(10,2) ); -- 插入数据：2023-01-01至2023-01-15，但跳过1月7-8日（周末） INSERT INTO stock_price VALUES ('2023-01-01', 100), ('2023-01-02', 102), ('2023-01-03', 101), ('2023-01-04', 105), ('2023-01-05', 107), ('2023-01-06', 106), ('2023-01-09', 108), ('2023-01-10', 110), ('2023-01-11', 109), ('2023-01-12', 111), ('2023-01-13', 112), ('2023-01-14', 113), ('2023-01-15', 114);

2. 执行两种frame对比：

SELECT trade_date, price, -- ROWS：取前4行+当前行（共5行），忽略日期间隔 AVG(price) OVER (ORDER BY trade_date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS ma_rows, -- RANGE：取trade_date-4天至trade_date内的所有行（可能不足5行） AVG(price) OVER (ORDER BY trade_date RANGE BETWEEN INTERVAL '4 days' PRECEDING AND CURRENT ROW) AS ma_range FROM stock_price ORDER BY trade_date;

3. 观察重点：
   • ma_rows在2023-01-09（周一）时，取的是01-05,01-06,01-09,01-10,01-11五日均价（因跳过周末，物理行数连续）；
   • ma_range在2023-01-09时，取的是01-05至01-09内所有交易日（01-05,01-06,01-09），仅3日均价；
   • 业务选择：需固定交易日数量（如技术分析）→ 用ROWS；需固定自然日范围（如监控异常）→ 用RANGE（需数据库支持）。

速查表标注：PDF中SUM() OVER()示例旁用小字注明：“ROWS按行数，RANGE按值范围；MySQL 8.0+、PostgreSQL 11+、SQL Server 2012+支持RANGE，但SQL Server不支持INTERVAL，需用DATEADD(day, -4, trade_date)替代”。

5. 从“查得到”到“用得稳”：我的三个强制检查清单

5.1 每次写完窗口函数，必跑的三行验证SQL

这不是仪式感，是防止上线后半夜被电话叫醒的底线操作。我在团队推行的“窗口函数三查法”，已拦截92%的线上错误：

1. 查分区完整性：确认PARTITION BY列无意外NULL或空字符串

   -- 执行前必跑 SELECT COUNT(*) FILTER (WHERE region IS NULL) AS null_region_cnt, COUNT(*) FILTER (WHERE TRIM(region) = '') AS empty_region_cnt, COUNT(*) AS total_cnt FROM orders;

   若null_region_cnt > 0，立即回退，按速查表3.3节处理NULL。

2. 查排序稳定性：确认ORDER BY列组合能唯一确定行序

   -- 执行前必跑（替换your_table和order_cols） SELECT order_cols, COUNT(*) as dup_cnt FROM your_table GROUP BY order_cols HAVING COUNT(*) > 1 LIMIT 5;

   若有重复，必须在ORDER BY末尾追加主键，如ORDER BY create_time, id。

3. 查frame覆盖度：确认ROWS/RANGE范围在业务预期内

   -- 以“近7天滚动”为例，查最小窗口行数 SELECT MIN(cnt) as min_window_size FROM ( SELECT COUNT(*) OVER (ORDER BY trade_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as cnt FROM stock_price ) t;

   若min_window_size < 7，说明早期数据不足，需用CASE WHEN cnt < 7 THEN ... ELSE ... END兜底。

5.2 速查表的“隐藏用法”：把它当SQL Linter用

PDF里每个函数右侧都有一栏“兼容性速查”，用✅/❌标注各数据库支持情况。我把它打印出来贴在显示器边框，写SQL时直接对照：

这个表格让我在跨数据库项目中，一眼识别哪些函数能通用，哪些需备选方案。比如写报表给客户，对方用MySQL，我就避开MEDIAN()，改用PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x)（MySQL 8.0.12+支持）。

5.3 最后一道防线：用EXPLAIN验证窗口函数是否走索引

窗口函数性能黑洞常藏在ORDER BY列无索引。速查表附赠的“性能自查表”要求：

• 所有ORDER BY列必须有索引（单列或联合索引前缀）；
• PARTITION BY列最好也建索引（尤其数据量>100万时）；
• 执行EXPLAIN，确认WindowAgg节点的Sort Key与索引匹配。

真实案例：某金融系统报表，ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY trade_time DESC)执行23秒。EXPLAIN显示Sort Key: trade_time DESC，但索引是(user_id, trade_time)。修复：重建索引为(user_id, trade_time DESC)，耗时降至0.3秒。

从那以后我每次写完窗口函数，都强制走一遍EXPLAIN，哪怕只是本地SQLite验证逻辑。不是信不过自己，是信不过那些没被EXPLAIN照过的SQL。希望帮到你。

本文还有配套的精品资源，点击获取