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;

输出：

name	salary	row_num	rank	dense_rank
Alice	9000	1	1	1
Bob	8000	2	2	2
Carol	8000	3	2	2
Dave	7000	4	4	3

---

(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;

输出：

name	department	salary	running_total	dept_avg
Alice	HR	5000	5000	6000
Bob	HR	7000	12000	6000
Carol	IT	8000	8000	8500
Dave	IT	9000	17000	8500

---

(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;

输出：

month	revenue	prev_month	growth
Jan	1000	NULL	NULL
Feb	1200	1000	200
Mar	1500	1200	300

---

(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或子查询
嵌套冲突	窗口函数嵌套	分层计算

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