用YEAR() + OVER()可高效实现年度汇总，需先提取年份再分区计算同比和排名，避免全局排序错误；注意数据库兼容性及分区键必须包含客户ID和年份。
用 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 <= 3;- 注意
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 上建索引，就会触发文件排序，慢得明显。
- 检查执行计划里有没有
Using filesort或Sort步骤 - 在
order_date字段上建索引，比在YEAR(order_date)上建函数索引更通用（MySQL 5.7 不支持函数索引） - 如果只是求年度总和、最大值，不用
ORDER BY，那OVER (PARTITION BY ...)几乎无额外开销
窗口函数不是银弹，它简化逻辑的前提是分区键能命中索引、排序字段有支撑。没索引硬上 ORDER BY，报表跑得比老式子查询还慢。
