直接JOIN+GROUP BY易致SUM/COUNT错误，因一对多关联引发数据行膨胀；应改用子查询/CTE预聚合或窗口函数分层计算，避免源头重复。
为什么直接 JOIN + GROUP BY 容易算错 SUM/COUNT
多表关联后聚合出错，根本不是函数写错了，而是数据在 JOIN 阶段就重复了。比如 orders LEFT JOIN order_items（1 订单 → N 明细），再 LEFT JOIN payments（1 订单 → M 支付记录），那一条订单可能变成 1×N×M 行——SUM(amount) 就被放大了 N×M 倍。
常见错误现象：SUM(payments.amount) 比实际收款总额高几倍；COUNT(DISTINCT orders.id) 正常，但 SUM(order_items.price) 异常偏大。
- 检查执行计划里每个
JOIN的预估行数，若某步 “rows” 突然跳增 10 倍以上，大概率就是膨胀源 - 确认每张被 JOIN 的表是否和主表存在“一对多”关系；若是，优先不直连，改用子查询或 CTE 预聚合
- 避免在
GROUP BY后才靠DISTINCT或HAVING补救——它们不解决重复根源，只掩盖问题
用窗口函数替代部分 JOIN 的典型场景
当你需要“每个订单的最新支付时间 + 该订单所有明细总金额”，传统写法是三表 JOIN 后 GROUP BY order_id，但容易因支付记录 × 明细记录双重膨胀。更稳的方式是：用窗口函数在明细层或支付层各自计算，再通过主键对齐。
示例（PostgreSQL/MySQL 8.0+）：
SELECT o.id, o.created_at, FIRST_VALUE(p.paid_at) OVER (PARTITION BY p.order_id ORDER BY p.paid_at DESC) AS latest_payment, SUM(oi.price) OVER (PARTITION BY oi.order_id) AS total_item_amount FROM orders o LEFT JOIN LATERAL ( SELECT order_id, paid_at FROM payments WHERE order_id = o.id ORDER BY paid_at DESC LIMIT 1 ) p ON true LEFT JOIN order_items oi ON oi.order_id = o.id;
关键点：
-
FIRST_VALUE() OVER (PARTITION BY ...)和SUM() OVER (PARTITION BY ...)在不同粒度上独立计算，不互相干扰 -
LATERAL子查询确保每条订单只取一条支付记录，从源头掐断膨胀 - 窗口函数必须写在
SELECT中，不能出现在WHERE或ON条件里——否则报错invalid reference to FROM-clause entry
窗口函数和聚合函数不能混用在同一级 SELECT
这是硬性语法限制，不是性能建议。像 SELECT order_id, SUM(amount), AVG(amount) OVER (PARTITION BY status) 这种写法，在绝大多数数据库（包括 MySQL 8.0、PostgreSQL、SQL Server）中会直接报错。
错误信息通常是：Window function is not allowed in this context 或 mixing of GROUP functions and window functions is not allowed。
- 正确做法是分两层：外层用窗口函数，内层用聚合；或都用窗口函数（如用
SUM() OVER替代SUM()） - 如果必须同时有全局聚合和分组窗口值，用 CTE 拆开：
WITH agg AS (SELECT order_id, SUM(amount) AS total FROM ... GROUP BY order_id), win AS (SELECT *, AVG(total) OVER (PARTITION BY region) FROM agg) SELECT * FROM win; - 注意：MySQL 8.0 不支持命名
WINDOW子句，所以多个窗口函数共用同一PARTITION BY / ORDER BY时，务必保证字段名、顺序、表达式完全一致，否则无法复用排序缓冲区
ORDER BY 中带函数时窗口性能骤降
写 ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY DATE(created_at)) 看似合理，但如果 created_at 没建索引，或者 DATE() 表达式未被覆盖，每次窗口计算都会触发全字段计算 + 排序，比 ORDER BY created_at 慢 5–10 倍。
- 先确认排序字段是否有索引；若必须用函数，考虑建函数索引（PostgreSQL）或生成列索引（MySQL 5.7+）
- 显式指定
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，避免默认RANGE框架在日期/状态码等高频重复值上反复扫描 - 当业务允许“物理顺序排名”（如按插入顺序取首单），就别用
RANGE——它不光慢，还容易让COUNT() OVER ()失去前缀和优化机会
窗口函数不是银弹，它解决不了主表和维度表之间本就不对齐的业务语义问题。比如用户表 JOIN 地址表再窗口取最新地址，若地址表里有脏数据（同一用户多条“最新”记录），窗口结果依然不可信——这时候得先清洗或加唯一约束，而不是调参。
