SQL如何通过窗口函数简化年度报表逻辑

用year() + over()可高效实现年度汇总，需先提取年份再分区计算同比和排名，避免全局排序错误；注意数据库兼容性及分区键必须包含客户id和年份。

sql如何通过窗口函数简化年度报表逻辑_sql开发技巧

用 `YEAR()` + `OVER()` 替代子查询拼年度汇总

年度报表最常卡在“每个客户每年的销售额、同比、排名”这类需求上。传统写法是多层子查询或临时表，可读性差、改起来像拆炸弹。窗口函数能直接在单次扫描中完成分组内计算，关键是得把时间维度对齐到年粒度。

常见错误是直接对 order_date 用 OVER (PARTITION BY customer_id)，结果发现同比算乱了——因为没按年切片。必须先提取年份，再作为分区依据：

SELECT
  customer_id,
  YEAR(order_date) AS y,
  SUM(amount) AS yearly_amount,
  LAG(SUM(amount), 1) OVER (PARTITION BY customer_id ORDER BY YEAR(order_date)) AS prev_year_amount
FROM orders
GROUP BY customer_id, YEAR(order_date);

YEAR(order_date) 必须出现在 GROUP BY 和 ORDER BY 中，否则窗口排序无意义
MySQL 8.0+、PostgreSQL、SQL Server 都支持；但 SQLite 不支持窗口函数，别踩坑
如果日期字段含时分秒，YEAR() 没问题；但若用 DATE_FORMAT(order_date, '%Y')（MySQL）或 EXTRACT(YEAR FROM order_date)（PostgreSQL），语法要对应数据库

避免 `ROW_NUMBER()` 在年度内错排客户TOP3

“每家客户每年销售额最高的3个产品”这种需求，容易误用全局 ROW_NUMBER() OVER (ORDER BY amount DESC)，结果排出来的是全量TOP3，不是“每年各算一遍”。核心是分区键必须包含年份和客户ID。

典型错误写法：ROW_NUMBER() OVER (ORDER BY amount DESC) —— 完全没分区，顺序随机且不可控。

正确做法：

SELECT *
FROM (
  SELECT
    customer_id,
    product_id,
    YEAR(order_date) AS y,
    SUM(amount) AS total_amount,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id, YEAR(order_date)
      ORDER BY SUM(amount) DESC
    ) AS rn
  FROM orders
  GROUP BY customer_id, product_id, YEAR(order_date)
) t
WHERE rn

注意 PARTITION BY 里必须同时有 customer_id 和 YEAR(order_date)，少一个就不是“年度内”
聚合（SUM(amount)）必须在窗口函数外做，否则会报错：窗口函数不能直接嵌套聚合
如果要并列不跳号，换用 RANK()；但年度TOP3通常要严格取3条，ROW_NUMBER() 更稳妥

`AVG() OVER()` 算年度均值时，NULL 和空组怎么处理

报表里常要加一列“当年平均订单金额”，看着简单，但实际数据里可能某客户某年没下单，导致该行 amount 为 NULL；或者用了 LEFT JOIN 引入空记录，AVG() 会自动忽略 NULL，但用户可能期望显示 0 或 “-”。

更隐蔽的问题是：如果只写 AVG(amount) OVER (PARTITION BY YEAR(order_date))，它算的是“所有订单的年度均值”，不是“每个客户的年度均值”——语义错位。

明确目标：是“每个客户每年的平均单笔金额”，那就必须 PARTITION BY customer_id, YEAR(order_date)
想把 NULL 当 0 参与计算？用 COALESCE(amount, 0) 包一层，但注意这会拉低均值，需业务确认是否合理
若某客户某年无数据，该行根本不会出现在结果集里（因没匹配到订单），这时候需要先用 GENERATE_SERIES（PostgreSQL）或数字表补年份，不能只靠窗口函数解决

性能陷阱：`ORDER BY` 在窗口里不是白写的

很多人以为 OVER (PARTITION BY x) 就够了，其实只要写了 ORDER BY（比如做累计求和、同比），数据库就得排序。年度报表数据量一大，ORDER BY YEAR(order_date) 看似简单，但如果基表没在 order_date 上建索引，就会触发文件排序，慢得明显。