跳到主要内容

SQL 聚合函数 (Aggregate) 与窗口函数 (Window)

窗口函数（Window Functions）是 SQL 中一种强大的分析工具，它允许在 不减少行数 的情况下对数据进行 分组计算、排序、排名、累计统计 等操作。与 GROUP BY 不同，窗口函数不会合并行，而是为每一行返回一个计算值。

SQL 聚合函数 (Aggregate) 与窗口函数 (Window)​

1. 窗口函数基本语法​

SELECT
    column1, column2,
    WINDOW_FUNCTION() OVER (
        [PARTITION BY partition_column]  -- 分组（类似GROUP BY）
        [ORDER BY sort_column]          -- 排序（影响排名、累计计算）
        [ROWS|RANGE frame_clause]       -- 定义窗口范围
    ) AS alias_name
FROM table_name;

2. 常用窗口函数分类​

(1) 排名函数​

函数说明示例场景
ROW_NUMBER()为每行分配唯一序号（相同值也递增）分页、去重
RANK()相同值排名相同，后续序号跳跃（如 1,2,2,4）成绩排名
DENSE_RANK()相同值排名相同，后续序号连续（如 1,2,2,3）奖金等级划分
NTILE(n)将数据分成 n 组，返回组编号数据分桶（前10%用户）
示例：员工工资排名​
SELECT
    name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
    RANK() OVER (ORDER BY salary DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

输出：

namesalaryrow_numrankdense_rank
Alice9000111
Bob8000222
Carol8000322
Dave7000443

(2) 聚合函数​

函数说明
SUM()计算窗口内总和
AVG()计算窗口内平均值
COUNT()计算窗口内行数
MAX() / MIN()计算窗口内最大/最小值
示例：计算部门累计工资​
SELECT
    name,
    department,
    salary,
    SUM(salary) OVER (PARTITION BY department ORDER BY salary) AS running_total,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

输出：

namedepartmentsalaryrunning_totaldept_avg
AliceHR500050006000
BobHR7000120006000
CarolIT800080008500
DaveIT9000170008500

(3) 偏移函数​

函数说明
LAG(column, n)返回当前行 前 n 行 的值
LEAD(column, n)返回当前行 后 n 行 的值
FIRST_VALUE()返回窗口内 第一行 的值
LAST_VALUE()返回窗口内 最后一行 的值（需注意范围）
示例：计算月度销售额环比​
SELECT
    month,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY month) AS prev_month,
    revenue - LAG(revenue, 1) OVER (ORDER BY month) AS growth
FROM sales;

输出：

monthrevenueprev_monthgrowth
Jan1000NULLNULL
Feb12001000200
Mar15001200300

(4) 分布函数​

函数说明
PERCENT_RANK()返回行的百分比排名（0~1）
CUME_DIST()返回行的累积分布（0~1）
示例：计算工资分布​
SELECT
    name,
    salary,
    PERCENT_RANK() OVER (ORDER BY salary) AS percentile
FROM employees;

3. 窗口范围（Frame Clause）​

通过 ROWS 或 RANGE 定义窗口的计算范围：

SUM(salary) OVER (
    PARTITION BY department
    ORDER BY hire_date
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING  -- 当前行+前一行+后一行
) AS moving_sum
  • UNBOUNDED PRECEDING：从窗口开始
  • UNBOUNDED FOLLOWING：到窗口结束
  • n PRECEDING / n FOLLOWING：前/后 n 行

4. 实际应用场景​

(1) 计算移动平均（股票分析）​

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

(2) 查找连续登录用户​

WITH login_dates AS (
    SELECT
        user_id,
        login_date,
        LAG(login_date, 1) OVER (PARTITION BY user_id ORDER BY login_date) AS prev_date
    FROM logins
)
SELECT user_id
FROM login_dates
WHERE login_date = prev_date + INTERVAL '1 day'
GROUP BY user_id
HAVING COUNT(*) >= 3;  -- 连续登录3天

(3) 分页查询（替代 LIMIT OFFSET）​

SELECT *
FROM (
    SELECT
        id, name,
        ROW_NUMBER() OVER (ORDER BY id) AS row_num
    FROM users
) AS t
WHERE row_num BETWEEN 11 AND 20;  -- 第2页（每页10条）

5. 性能优化建议​

  1. 减少窗口大小：通过 ROWS 限制范围。
  2. 避免全表排序：确保 ORDER BY 列有索引。
  3. 优先使用 PARTITION BY：减少单次计算的数据量。

总结​

函数类型典型用途关键函数
排名函数排序、分组排名ROW_NUMBER(), RANK()
聚合函数累计计算、移动平均SUM(), AVG()
偏移函数环比分析、相邻行比较LAG(), LEAD()
分布函数百分比排名PERCENT_RANK()

掌握窗口函数可以高效解决复杂分析需求（如排名、趋势分析、累计统计），是 SQL 高级查询的核心技能！

SQL 中可能引发冲突的窗口函数使用场景​

虽然窗口函数通常是相互独立的，但在某些特殊情况下确实会出现冲突或意外行为。以下是几种需要特别注意的情况：

1. ORDER BY 导致的计算范围冲突​

当多个窗口函数使用相同的 PARTITION BY 但不同的 ORDER BY 时，可能导致结果不符合预期。

问题示例：​

SELECT
    date,
    revenue,
    SUM(revenue) OVER (ORDER BY date ROWS 2 PRECEDING) AS moving_sum,
    AVG(revenue) OVER (ORDER BY date DESC ROWS 2 PRECEDING) AS reverse_avg
FROM sales;

冲突点：

  • 两个窗口函数对相同数据使用了相反的排序方向（ASC vs DESC）
  • 导致移动求和与移动平均的计算范围逻辑冲突

2. 窗口范围定义不匹配​

当混合使用 ROWS 和 RANGE 时可能出现边界问题。

问题示例：​

SELECT
    employee_id,
    salary,
    SUM(salary) OVER (ORDER BY salary ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS row_sum,
    SUM(salary) OVER (ORDER BY salary RANGE BETWEEN 100 PRECEDING AND 100 FOLLOWING) AS range_sum
FROM employees;

冲突点：

  • ROWS 按物理行计算
  • RANGE 按值范围计算
  • 相同排序字段但不同计算逻辑会导致结果不一致

3. PARTITION BY 不一致导致数据错位​

当多个窗口函数的分区逻辑不同时。

问题示例：​

SELECT
    department,
    employee_name,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
    RANK() OVER (ORDER BY salary DESC) AS global_rank
FROM employees
ORDER BY department, dept_rank;

潜在问题：

  • 全局排名和部门排名可能显示矛盾结果
  • 如：某员工在部门内排名第1，但全局排名第20

4. 与聚合函数混用导致歧义​

窗口函数与普通聚合函数混合使用时。

问题示例：​

SELECT
    department,
    COUNT(*) OVER () AS total_count,  -- 窗口函数
    COUNT(*) AS dept_count            -- 普通聚合函数
FROM employees
GROUP BY department;

冲突点：

  • 这个查询会直接报错，因为不能混合窗口函数和GROUP BY聚合
  • 修正方法：使用子查询或CTE

5. 嵌套窗口函数​

直接嵌套窗口函数通常不被允许。

无效示例：​

-- 错误的写法（大多数数据库不支持）
SELECT
    SUM(COUNT(*) OVER (PARTITION BY department))
FROM employees;

解决方案：

-- 正确写法：使用子查询
WITH dept_counts AS (
    SELECT
        department,
        COUNT(*) AS cnt
    FROM employees
    GROUP BY department
)
SELECT SUM(cnt) FROM dept_counts;

如何避免冲突？​

  1. 保持窗口定义一致：多个窗口函数尽量使用相同的PARTITION BY和ORDER BY
  2. 显式命名窗口（SQL标准支持）： 
    SELECT
        salesperson,
        sales,
        SUM(sales) OVER win AS running_sum,
        AVG(sales) OVER win AS running_avg
    FROM sales
    WINDOW win AS (PARTITION BY region ORDER BY month);
  3. 测试边界情况：特别是涉及NULL值、重复排序值的情况
  4. 使用EXPLAIN分析：查看执行计划确认计算顺序

总结：冲突场景对照表​

冲突类型示例场景解决方案
排序冲突混合ASC/DESC排序统一排序方向
范围冲突ROWS vs RANGE混用选择一致的窗口范围类型
分区冲突不同粒度分区使用子查询分步计算
聚合冲突窗口函数+GROUP BY改为CTE或子查询
嵌套冲突窗口函数嵌套分层计算

正确使用窗口函数需要明确每个函数的计算范围和上下文环境，通过合理的查询设计和测试可以避免大多数冲突问题。

最后于 更新