SQL窗口函数详解

题目描述
面试中经常被问及SQL窗口函数，特别是考察如何用它进行复杂的数据分析与分组计算。题目可能要求你对比普通聚合函数与窗口函数的区别，或写出特定场景下的窗口函数SQL（如计算累计百分比、排名、移动平均等）。掌握窗口函数能显著提升数据查询效率与表达能力。

解题过程

1. 窗口函数的核心概念

• 定义：窗口函数（Window Function）对一组与当前行相关的行执行计算，但不会像普通聚合函数那样将多行合并为一行，而是保留所有原始行。
• 关键区别：
  • 普通聚合函数（如SUM()、AVG()）搭配GROUP BY时，会按分组合并行；
  • 窗口函数通过OVER()子句定义数据窗口，计算结果直接附加到每一行。
• 基本语法：

  函数名(表达式) OVER (
    [PARTITION BY 列]   -- 分组，类似GROUP BY但非合并行
    [ORDER BY 列]       -- 组内排序，影响计算顺序
    [窗口框架]          -- 定义当前行的计算范围（如ROWS BETWEEN...）
  )
  

2. 窗口函数的分类与常用函数

• 排名函数：

  • ROW_NUMBER()：连续唯一排名（相同值按顺序不同排名）。
  • RANK()：相同值排名相同，但后续排名跳过重复位（如1,1,3）。
  • DENSE_RANK()：相同值排名相同，排名连续不跳位（如1,1,2）。
    示例：

  SELECT name, score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
    RANK() OVER (ORDER BY score DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
  FROM students;
  

• 分布函数：

  • PERCENT_RANK()：计算行的百分比排名（公式：(rank-1)/(总行数-1)）。
  • CUME_DIST()：计算行的累积分布（小于等于当前值的行数/总行数）。

• 前后值函数：

  • LAG(列, n)：获取当前行前第n行的值。
  • LEAD(列, n)：获取当前行后第n行的值。
    示例（计算每日销售额环比增长）：

  SELECT sale_date, amount,
    LAG(amount, 1) OVER (ORDER BY sale_date) AS prev_amount,
    (amount - LAG(amount, 1) OVER (ORDER BY sale_date)) / LAG(amount, 1) OVER (ORDER BY sale_date) AS growth_rate
  FROM sales;
  

• 聚合类窗口函数：

  • SUM()/AVG()/COUNT()等结合OVER()，实现累计计算或移动平均。
    示例（计算累计销售额）：

  SELECT sale_date, amount,
    SUM(amount) OVER (ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sum
  FROM sales;
  

3. 窗口框架的详细用法

• 作用：指定函数计算时使用的数据范围，需配合ORDER BY使用。
• 常见框架子句：
  • ROWS BETWEEN ...：按物理行数范围。
    • UNBOUNDED PRECEDING：从分区第一行开始。
    • n PRECEDING：当前行前n行。
    • CURRENT ROW：当前行。
    • n FOLLOWING：当前行后n行。
  • RANGE BETWEEN ...：按数值逻辑范围（如时间间隔）。
• 示例（计算3天移动平均）：

  SELECT date, revenue,
    AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
  FROM daily_sales;
  

4. 综合应用场景
场景：查询每个部门工资排名前3的员工。

SELECT * FROM (
  SELECT name, department, salary,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
  FROM employees
) ranked
WHERE dept_rank <= 3;

• 步骤解析：
  1. 使用PARTITION BY department按部门分组。
  2. 组内按工资降序排序，用DENSE_RANK()计算排名。
  3. 外层查询筛选排名前3的行。

5. 面试常见陷阱与优化建议

• 陷阱：
  • 混淆ROW_NUMBER()与RANK()导致排名逻辑错误。
  • 忽略ORDER BY对窗口框架的影响（无ORDER BY时框架默认为整个分区）。
• 优化：
  • 避免在OVER()中使用复杂子查询，优先用PARTITION BY分区。
  • 对大数据集，通过索引优化PARTITION BY和ORDER BY的列。

通过以上步骤，你不仅能写出正确的窗口函数查询，还能在面试中解释其底层逻辑与适用场景。