SQL窗口函数速查表

一、排名与分布分析类（核心用：给数据 “排位置、看占比”）

专门用于确定数据在整体 / 分区中的相对位置、排名顺序或分布比例，是最常用的窗口函数类型。

1. ROW_NUMBER()：给分区内每行贴唯一连续序号，同值也不重复（如 “给每天的订单按时间排 1、2、3 号”）。
2. RANK()：按排序值排名，同值同名次，后续名次跳号（如 “成绩并列第 1，下一名直接第 3”）。
3. DENSE_RANK()：按排序值排名，同值同名次，后续名次不跳号（如 “成绩并列第 1，下一名仍第 2”）。
4. PERCENT_RANK()：返回相对百分位排名（公式：(名次 - 1)/(总行数 - 1)），范围 0~1，体现 “比多少数据好 / 差”。
5. CUME_DIST()：返回 “≤当前值的行数 / 总行数”，范围 0~1，体现 “当前值及以下数据的占比”。
6. NTILE(n)：将分区数据均匀分成 n 个 “桶”，返回每行所属桶号（如 “把员工绩效分成 3 组，标 1/2/3 桶”）。

二、相邻数据取值类（核心用：“跨行取数”，比当前行前 / 后 / 特定位置的值）

专门用于获取分区内 “非当前行” 的数据，无需手动关联表，直接跨行提取目标位置的值。

1. LAG(expr [,offset [,default]])：取当前行 “往上数 offset 行” 的 expr 值，无对应行返回 default（如 “取前 1 天的销售额，无则返回 0”）。
2. LEAD(expr [,offset [,default]])：取当前行 “往下数 offset 行” 的 expr 值，无对应行返回 default（如 “取后 1 天的销售额，无则返回 0”）。
3. FIRST_VALUE(expr)：取分区内按排序规则的 “第一行” expr 值（如 “取店铺本月第一天的销售额”）。
4. LAST_VALUE(expr)：取分区内 “到当前行为止” 的最后一行 expr 值（需注意窗口框架，默认到当前行，非全分区）。
5. NTH_VALUE(expr, n)：取分区内排序后的 “第 n 行” expr 值，无第 n 行则返回 NULL（如 “取店铺本月第 2 天的销售额”）。

三、窗口聚合计算类（核心用：“累计 / 整体统计”，在分区内做聚合，不压缩行数）

将普通聚合函数（SUM/AVG 等）改造为窗口模式，不合并行，每行都能显示聚合结果（累计或全分区）。

1. 基础累计 / 局部聚合（支持按顺序累计，默认 “从第一行到当前行”）

• SUM(expr)：计算分区内到当前行的 expr 累计和（如 “按日期累计店铺销售额”）。
• AVG(expr)：计算分区内到当前行的 expr 累计平均值（如 “按日期累计店铺日均销售额”）。
• COUNT(expr)：统计分区内到当前行的非 NULL 行数（如 “按日期累计店铺营业天数”）。
• MIN(expr)：取分区内到当前行的 expr 最小值（如 “按日期记录店铺截至当天的最低销售额”）。
• MAX(expr)：取分区内到当前行的 expr 最大值（如 “按日期记录店铺截至当天的最高销售额”）。

2. 全分区统计（不累计，直接计算整个分区的统计量，每行结果相同）

• STDDEV_POP(expr)：计算分区内 expr 的 “总体标准差”（用全部数据计算，分母为总行数）。
• STDDEV_SAMP(expr)：计算分区内 expr 的 “样本标准差”（用样本数据计算，分母为总行数 - 1）。
• VAR_POP(expr)：计算分区内 expr 的 “总体方差”（总体标准差的平方）。
• VAR_SAMP(expr)：计算分区内 expr 的 “样本方差”（样本标准差的平方）。

四、多变量关联分析类（核心用：“分析两个变量的关系”，如相关性、回归）

专门用于分析分区内两个变量（如 “销售额” 与 “日期”、“广告投入” 与 “销量”）的关联程度或回归规律。

1. COVAR_POP(y, x)：计算 y 和 x 的 “总体协方差”，体现两变量的整体联动方向（正 / 负相关趋势）。

2. COVAR_SAMP(y, x)：计算 y 和 x 的 “样本协方差”，基于样本数据体现两变量的联动方向。

3. CORR(y, x)：计算 y 和 x 的 “皮尔逊相关系数”，范围 - 1~1，直接体现关联强度（1 = 完全正相关，-1 = 完全负相关）。

4. REGR_* 系列

   ：一次线性回归专用函数，如：

   • REGR_SLOPE(y, x)：计算 y 对 x 的回归斜率（如 “销售额随日期变化的每日增长幅度”）。
   • REGR_INTERCEPT(y, x)：计算回归截距（如 “回归公式的初始值”）。
   • REGR_R2(y, x)：计算决定系数 R²，体现回归模型的拟合优度（越接近 1 拟合越好）。
   • REGR_COUNT(y, x)：统计 y 和 x 均非空的行数（回归分析的有效数据量）

五、窗口函数常见错误与避坑指南

窗口函数功能强大但细节复杂，实际使用中容易因理解框架范围、分区逻辑或排序规则导致结果不符合预期。以下是最常见的错误类型及解决方案：

1.LAST_VALUE() 结果不符合预期（最容易踩的坑）

错误表现：

使用 LAST_VALUE() 时，返回的不是分区内最后一行的值，而是当前行或靠前位置的值。

错误原因：

窗口函数默认框架是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（从第一行到当前行），而非全分区。例如：

-- 错误示例：想取全分区最后一行，却返回当前行及之前的最后一行
SELECT 
  shop, date, sales,
  LAST_VALUE(sales) OVER (PARTITION BY shop ORDER BY date) AS last_sales
FROM t;

解决方案：

显式指定全分区框架 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING：

SELECT 
  shop, date, sales,
  LAST_VALUE(sales) OVER (
    PARTITION BY shop 
    ORDER BY date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS last_sales  -- 正确返回分区内最后一行
FROM t;

2.遗漏 PARTITION BY 导致全局计算

错误表现：

希望按分组（如店铺）单独计算窗口函数，结果却变成了所有数据的全局计算。

错误原因：

忘记添加 PARTITION BY，函数默认对全表数据进行计算。例如：

-- 错误示例：想按店铺计算累计销售额，却计算了所有店铺的总和
SELECT 
  shop, date, sales,
  SUM(sales) OVER (ORDER BY date) AS running_sum  -- 缺少 PARTITION BY shop
FROM t;

解决方案：

明确添加 PARTITION BY 定义分组：

SELECT 
  shop, date, sales,
  SUM(sales) OVER (PARTITION BY shop ORDER BY date) AS running_sum  -- 正确按店铺分组
FROM t;

3.ORDER BY 排序方向错误影响结果

错误表现：

PERCENT_RANK()/CUME_DIST() 等排名函数的值与预期相反（如最大值的百分比反而大）。

错误原因：

排序方向与分析目标不符。例如：

-- 错误示例：想让销售额越高，PERCENT_RANK() 越小，却用了升序
SELECT 
  shop, sales,
  PERCENT_RANK() OVER (ORDER BY sales ASC) AS pct_rnk  -- 升序导致最大值的百分比为1
FROM t;

解决方案：

根据业务目标选择排序方向（通常分析 “头部数据” 用 DESC）：

SELECT 
  shop, sales,
  PERCENT_RANK() OVER (ORDER BY sales DESC) AS pct_rnk  -- 降序让最大值的百分比为0
FROM t;

4.RANK() 与 DENSE_RANK() 混淆导致排名错误

错误表现：

需要连续排名时用了 RANK()（出现跳号），或需要保留名次空位时用了 DENSE_RANK()（连续编号）。

错误示例：

-- 若想统计“第2名”包含并列情况但不跳号，却用了 RANK()
SELECT 
  sales,
  RANK() OVER (ORDER BY sales DESC) AS rnk  -- 若前2名并列，下一名会是3（跳号）
FROM t;

解决方案：

• 需跳号（如比赛排名，允许第 1 名并列后直接第 3 名）→ 用 RANK()
• 需连续（如筛选前 3 名，包含并列且不跳号）→ 用 DENSE_RANK()

SELECT 
  sales,
  DENSE_RANK() OVER (ORDER BY sales DESC) AS dense_rnk  -- 并列第1后，下一名是2（不跳号）
FROM t;

5.窗口聚合函数的 “累计” 与 “全量” 混淆

错误表现：

使用 SUM()/AVG() 时，希望得到全分区的总量，却返回了累计值。

错误原因：

ORDER BY 会触发 “累计计算”（从第一行到当前行），省略 ORDER BY 则返回全分区聚合结果。例如：

-- 错误示例：想获取店铺总销售额，却因加了 ORDER BY 变成累计值
SELECT 
  shop, date, sales,
  SUM(sales) OVER (PARTITION BY shop ORDER BY date) AS total_sales  -- 累计值，非总量
FROM t;

解决方案：

• 需累计值 → 保留 ORDER BY（默认从第一行到当前行）
• 需全分区总量 → 省略 ORDER BY

SELECT 
  shop, date, sales,
  SUM(sales) OVER (PARTITION BY shop) AS total_sales  -- 正确返回店铺总销售额
FROM t;

6.LAG()/LEAD() 越界处理缺失

错误表现：

第一行用 LAG() 或最后一行用 LEAD() 时，返回 NULL 导致后续计算错误。

错误原因：

未指定越界时的默认值，函数默认返回 NULL。例如：

-- 错误示例：第一行的 LAG() 返回 NULL，可能导致计算失败
SELECT 
  shop, date, sales,
  sales - LAG(sales) OVER (PARTITION BY shop ORDER BY date) AS diff  -- 第一行 diff 为 NULL
FROM t;

解决方案：

显式指定 default 参数（如 0 或其他合理值）：

SELECT 
  shop, date, sales,
  sales - LAG(sales, 1, 0) OVER (PARTITION BY shop ORDER BY date) AS diff  -- 越界返回0
FROM t;

7.NTILE(n) 数据分配不均时的误解

错误表现：

期望 NTILE(n) 严格均分数据，实际某些桶的行数多 1（如 7 行数据分 3 桶，结果为 3、2、2）。

错误原因：

NTILE(n) 会优先向前几个桶分配多余的行（总行数不能被 n 整除时），保证分配尽可能均匀而非绝对均分。

解决方案：

• 接受合理偏差（函数设计如此）
• 若需严格均分，可先过滤数据使总行数为 n 的倍数，或自定义逻辑：

-- 替代方案：用 ROW_NUMBER() 手动分配，确保前m个桶多1行
SELECT 
  *,
  (ROW_NUMBER() OVER (ORDER BY sales) - 1) % 3 + 1 AS custom_ntile
FROM t;

避坑总结：3 个核心检查点

1. 框架范围：LAST_VALUE() 必须显式指定全分区框架，其他函数注意默认框架是否符合需求。
2. 分区与排序：是否遗漏 PARTITION BY（导致全局计算），ORDER BY 方向是否与业务目标一致。
3. 边界处理：LAG()/LEAD() 显式指定默认值，RANK() 与 DENSE_RANK() 根据排名规则选择。