直接用 SUM(AVG()) 会报错，因为 SQL 不允许聚合函数嵌套；正确做法是用子查询先按用户求总消费，外层再求平均，如 SELECT AVG(total_spent) FROM (SELECT user_id, SUM(amount) AS total_spent FROM orders GROUP BY user_id) AS t。
为什么直接用 SUM(AVG()) 会报错？
SQL 不允许在聚合函数内部直接嵌套另一个聚合函数（比如 AVG(SUM()) 或 SUM(AVG())），这是语法硬限制。生命周期价值（LTV）常需「先算每个用户的总消费，再求这些总消费的平均值」，本质是两层聚合，必须拆成子查询实现。
常见错误现象：ERROR: aggregate function calls cannot be nested（PostgreSQL）、Invalid use of group function（MySQL）。
- 不能写：
SELECT AVG(SUM(amount)) FROM orders GROUP BY user_id - 正确路径：外层查平均，内层按用户聚合出总消费
- 注意：子查询必须有别名（哪怕只是
AS t），否则多数数据库报错
标准写法：子查询先算单用户总消费，外层取平均
这是最通用、兼容性最好的方案，适用于 MySQL、PostgreSQL、SQL Server、ClickHouse 等主流引擎。
SELECT AVG(total_spent) AS ltv FROM ( SELECT user_id, SUM(amount) AS total_spent FROM orders WHERE order_status = 'completed' GROUP BY user_id ) AS user_totals;
关键点：
-
WHERE条件要放在子查询内，避免外层过滤导致AVG()计算范围错误 - 如果用户可能无订单，且你想包含 0 值（比如注册未下单用户），需改用
LEFT JOIN users ON ...并配合COALESCE(SUM(), 0) - MySQL 8.0+ 和 PostgreSQL 支持 CTE，可读性更好：
WITH user_totals AS (SELECT ...) SELECT AVG(total_spent) FROM user_totals
带时间窗口的 LTV：必须小心 GROUP BY 和日期过滤顺序
计算「过去 12 个月新注册用户的 LTV」时，容易把时间条件放错位置，导致结果偏高（混入老用户）或偏低（漏掉跨期用户）。
正确逻辑：先筛选「注册时间在指定范围内」的用户，再关联其所有有效订单（不限时间），最后聚合。
SELECT AVG(totals.total_spent) AS ltv_12m_cohort
FROM (
SELECT u.user_id, COALESCE(SUM(o.amount), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id AND o.order_status = 'completed'
WHERE u.registered_at >= '2023-01-01'
AND u.registered_at < '2024-01-01'
GROUP BY u.user_id
) AS totals;
- 注册时间过滤（
WHERE u.registered_at...）必须在外层JOIN之前完成，确保只选目标队列 - 订单时间不应额外限制（除非业务明确只要「注册后 12 个月内」的订单），否则变成「留存率 × ARPU」而非真正 LTV
-
COALESCE(SUM(), 0)防止新用户无订单时该行被SUM()排除，影响分母
性能陷阱：大表上 GROUP BY user_id 可能慢，怎么优化？
当 orders 表超千万行，子查询中 GROUP BY user_id 会触发全表扫描和临时表排序，响应明显变长。
- 强制走索引：确保
(user_id, order_status)有联合索引（order_status用于WHERE过滤） - 如果只需近似值，可采样：
FROM orders TABLESAMPLE SYSTEM(5)（PostgreSQL）或FROM orders WHERE RAND() （MySQL） - 更稳的方案是预计算：用物化视图（PostgreSQL）或汇总表（每天跑
INSERT INTO user_daily_sums ... GROUP BY user_id），查 LTV 时直接AVG()聚合汇总表
真正麻烦的不是语法，而是想清楚「LTV 定义是否含沉默用户」「订单状态如何判定有效」「时间窗口以注册日还是首购日为锚点」——这些业务逻辑一旦定错，SQL 写得再对也没用。
