cte中窗口函数结果不能直接用于group by，需前置计算分组键；group by必须包含select中所有非聚合字段；多层cte应避免中间聚合，保持原子性，最终聚合留至外层。
CTE里不能直接用窗口函数做分组依据
很多人写完 WITH cte AS (SELECT ..., ROW_NUMBER() OVER (...) AS rn)，紧接着在外部 GROUP BY rn，结果报错或逻辑错乱——因为 rn 是窗口计算结果，在 GROUP BY 阶段不可见（除非再套一层子查询）。CTE 本身只是命名结果集，不改变执行顺序。
正确做法是：把需要分组的逻辑前置到 CTE 内部完成，或者用 CTE 先规整数据结构，再在外层聚合。比如按“连续登录天数分组”，得先用 LAG() 或 ROW_NUMBER() 算出差值，再用该差值作为分组键：
WITH login_diff AS (
SELECT user_id, login_date,
DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY) AS grp_key
FROM logins
)
SELECT user_id, COUNT(*) AS streak_len, MIN(login_date), MAX(login_date)
FROM login_diff
GROUP BY user_id, grp_key;
GROUP BY 和聚合字段必须严格匹配SELECT中的非聚合列
MySQL 5.7+ 默认开启 ONLY_FULL_GROUP_BY，一旦 SELECT 里出现没出现在 GROUP BY 中的非聚合字段（比如 SELECT user_id, name, COUNT(*) 但只 GROUP BY user_id），就会报错：Expression #2 of SELECT list is not in GROUP BY clause。
常见应对方式不是关 SQL mode，而是明确意图：
- 如果
name确实和user_id一一对应（如维表关联后），就加进GROUP BY - 如果只需要任一值，改用
MAX(name)或ANY_VALUE(name)（MySQL 5.7+ 支持） - 如果想保留原始行粒度再聚合，得用 CTE 先聚合，再 JOIN 回原表
多层嵌套CTE时，别在中间层做无意义的聚合
有人习惯把每一步都 GROUP BY 一下，比如 CTE1 汇总订单金额，CTE2 再按用户聚合 CTE1 结果——这看似清晰，实则可能丢失明细维度，且增加中间结果体积。
更高效的做法是：让 CTE 尽量保持原子性（只做数据筛选、关联、衍生字段），把最终聚合留到最外层。尤其当 CTE 后续还要参与 JOIN 或条件过滤时，提前聚合会切断关联路径。
例如：要统计“每个城市中订单金额 Top 3 的用户”，不要在 CTE 里先按城市聚合用户金额，而应：
WITH ranked AS (
SELECT city, user_id, SUM(amount) AS total_amt,
ROW_NUMBER() OVER (PARTITION BY city ORDER BY SUM(amount) DESC) AS rn
FROM orders o JOIN users u ON o.user_id = u.id
GROUP BY city, user_id
)
SELECT city, user_id, total_amt
FROM ranked
WHERE rn
<h3>CTE + 聚合性能容易被索引忽略</h3>
<p>CTE 不是物化视图，大多数数据库（PostgreSQL、MySQL 8.0+）默认不缓存其结果；如果 CTE 被多次引用，可能重复执行。而聚合操作本身又容易绕过索引——比如 <code>GROUP BY DATE(created_at)</code> 会让 <code>created_at</code> 上的索引失效。</p>
<p>优化要点：</p>
- 确认 CTE 是否真被复用：若只用一次，直接写子查询反而更轻量
- 对 GROUP BY 字段建函数索引（如 PostgreSQL 的
CREATE INDEX idx_orders_date ON orders ((DATE(created_at)))） - 避免在 GROUP BY 中使用表达式或类型转换，优先用已存在的字段或生成列
复杂分组真正难的不是语法，而是搞清“分组键到底该基于原始数据算，还是基于上一步聚合结果推”。一旦键的语义模糊，后面所有聚合都会漂移。
