count(distinct user_id) 在 kpi 场景下常因忽略业务语义（如过滤测试账号、对齐自然日、定义活跃逻辑）导致结果偏差10%～30%，非语法错误；需结合 where 条件与业务口径精准计算。
为什么 COUNT(DISTINCT user_id) 在 KPI 场景下常出错
不是语法错，是语义错：KPI 往往要求“去重后在指定时间窗口内活跃的用户数”，但直接写 COUNT(DISTINCT user_id) 会忽略业务约束——比如是否要排除测试账号、是否只算付费用户、是否需对齐自然日而非事件时间戳。
- 常见错误现象：
COUNT(DISTINCT user_id)返回值比 BI 工具或上游口径高 10%～30%，通常因为没过滤is_test = 0或没用DATE(event_time)对齐统计日 - 使用场景：DAU、MAU、付费用户数等基础 KPI，必须和产品定义的“活跃”逻辑一致（例如登录 + 页面浏览 ≥2 次才算）
- 参数差异：MySQL 5.7 不支持
COUNT(DISTINCT ...)和GROUP BY混用子查询优化，建议先SELECT DISTINCT再聚合；ClickHouse 则推荐用uniq(user_id)替代，性能高 3～5 倍 - 示例（修正版 DAU）：
SELECT COUNT(DISTINCT user_id) FROM events WHERE DATE(event_time) = '2024-06-01' AND is_test = 0 AND user_id IS NOT NULL
LAG() 和 LEAD() 算留存率时怎么避免漏掉首日用户
留存率本质是“第 N 日还回来的人 / 首日新用户”，但直接用 LAG(user_id, 1) 会把首日用户全丢掉——因为 LAG 只能取前一行，首日没“前一日”可拉。
- 常见错误现象：次日留存率恒为 0，或结果明显偏低，查表发现
LAG返回 NULL 的行被 WHERE 过滤掉了 - 正确做法：先用
MIN(event_date) OVER (PARTITION BY user_id)标出每个用户的首次活跃日，再关联判断“是否在首日后第 N 天再次出现” - 性能影响：窗口函数嵌套两层（先标首日，再算留存）在亿级表上可能慢，建议提前物化
first_active_date到用户维表 - 示例（次日留存）：
WITH first_day AS ( SELECT user_id, MIN(DATE(event_time)) AS first_date FROM events GROUP BY user_id ), active_days AS ( SELECT DISTINCT user_id, DATE(event_time) AS dt FROM events ) SELECT COUNT(DISTINCT d.user_id) * 1.0 / COUNT(DISTINCT f.user_id) AS retention_d1 FROM first_day f LEFT JOIN active_days d ON f.user_id = d.user_id AND d.dt = DATE_ADD(f.first_date, INTERVAL 1 DAY)
分母用 SUM(CASE WHEN ... THEN 1 ELSE 0 END) 还是 COUNT(*)？
取决于 KPI 定义里“分母”是不是天然带条件。比如“支付转化率 = 支付人数 / 加购人数”，加购人数必须是明确发生过加购行为的用户，不能用总 UV 当分母。
- 容易踩的坑：用
COUNT(*)当分母，却忘了 WHERE 已经过滤了部分行为，导致分母变小、指标虚高 - 更安全的做法：分母也用条件聚合，保持逻辑原子性。例如加购人数写成
SUM(CASE WHEN event_type = 'add_to_cart' THEN 1 ELSE 0 END)，哪怕外层没 WHERE - 兼容性注意：Hive SQL 中
COUNT(CASE ...)会把 NULL 当 0 计，而SUM(CASE ...)更直观；Trino 推荐后者，避免隐式类型转换
时间窗口对齐：为什么 event_time >= '2024-06-01' 比 DATE(event_time) = '2024-06-01' 更可靠
因为前者能走索引，后者强制对每行计算函数，大表扫描直接变慢 10 倍以上；更重要的是，DATE() 会丢失时区信息，跨时区服务易出错。
- 真实问题：凌晨 00:03 的订单被算进前一天的 KPI，只因数据库时区设为 UTC，而业务按北京时间统计
- 正确做法：统一用带时区的时间戳字段（如
event_time_utc），并在 WHERE 中用范围比较：event_time_utc >= '2024-06-01 00:00:00+08:00' AND event_time_utc - 如果只有无时区字段，至少用
CAST(event_time AS DATE)替代DATE(event_time)，部分引擎能更好下推
事情说清了就结束。KPI 计算最麻烦的从来不是 SQL 写法，而是搞明白“这个数字到底要回答什么问题”——多问一句产品文档里的定义原文，比调三次 EXPLAIN 更省时间。
