核心思路是用窗口函数分时段统计活跃次数后计算下降率：先用CTE按统一规则（如登录/订单）聚合两期数据，再用NULLIF防除零，结合COALESCE处理空值，确保时间段对齐、时区一致、索引有效。
用窗口函数计算用户活跃度下降率
核心思路是先分别统计两个时间段的用户活跃次数，再用 LAG() 或 ROW_NUMBER() 排序后做差值比。直接用子查询嵌套容易写错关联逻辑，且 MySQL 8.0+、PostgreSQL、SQL Server 都支持窗口函数，更稳妥。
常见错误是把两个时间段的统计结果用 JOIN 连接时没加 ON user_id，或漏掉 COALESCE(active_count, 0) 导致 NULL 参与除法运算报错（如 PostgreSQL 报 division by zero）。
- 时间段必须对齐：比如都取最近 7 天 vs 上一个 7 天，不能一个用
CURRENT_DATE - INTERVAL '7 days'，另一个用DATE_SUB(CURDATE(), INTERVAL 14 DAY)却没统一时区或截断逻辑 - 活跃定义要一致：是登录次数？订单数？页面 PV？建议提前封装为 CTE，避免在内外层重复写
WHERE event_type IN ('login', 'click') - 下降率公式推荐用
(prev_count - curr_count) * 1.0 / NULLIF(prev_count, 0)，NULLIF比CASE WHEN更简洁，也防分母为 0
MySQL 8.0+ 实操示例（含时间分区与空值防护）
假设日志表叫 user_events，字段有 user_id、event_time；想比「2024-05-01 到 2024-05-07」vs「2024-04-24 到 2024-04-30」：
WITH period_counts AS (
SELECT
user_id,
SUM(CASE WHEN event_time >= '2024-04-24' AND event_time < '2024-05-01' THEN 1 ELSE 0 END) AS prev_count,
SUM(CASE WHEN event_time >= '2024-05-01' AND event_time < '2024-05-08' THEN 1 ELSE 0 END) AS curr_count
FROM user_events
WHERE event_time >= '2024-04-24' AND event_time < '2024-05-08'
GROUP BY user_id
)
SELECT user_id,
prev_count,
curr_count,
ROUND((prev_count - curr_count) * 1.0 / NULLIF(prev_count, 0), 4) AS drop_rate
FROM period_counts
WHERE prev_count > 0
ORDER BY drop_rate DESC
LIMIT 10;注意：WHERE 提前过滤总时间范围，避免全表扫描；prev_count > 0 排除上期没活跃的用户（否则下降率无意义）；ROUND(..., 4) 防止浮点误差干扰排序。
PostgreSQL 中用 generate_series 做动态时间段对比
如果需要频繁切换对比周期（比如每周自动跑），硬编码日期不灵活。PostgreSQL 可用 generate_series 动态生成时间边界：
PostgreSQL 18.4 官方 Ubuntu 安装包现已发布，这是目前最新的稳定版本。推荐通过官方 APT 仓库安装：先执行 sudo apt update 更新索引，再运行 sudo apt install postgresql-18 即可完成部署。新版本引入了异步 I/O 子系统，在顺序扫描与 VACUUM 场景下性能提升显著，同时支持 UUID v7 原生生成函数与虚拟生成列。
常见坑是 generate_series('2024-05-01'::date, '2024-05-07'::date, '1 day'::interval) 返回的是 timestamp，和 event_time 类型不一致导致索引失效。应统一转为 date：
- 用
(event_time::date)而非event_time做分组，确保走 date 字段索引 - CTE 中用
SELECT MAX(event_time)::date - 7 AS start_prev算相对时间，比写死字符串更可靠 - 若
user_events表极大，务必确认user_id和event_time有联合索引，例如CREATE INDEX idx_user_time ON user_events(user_id, event_time);
为什么不用 UNION ALL + PIVOT？
有人尝试先 UNION ALL 两段数据，再用 PIVOT（SQL Server）或条件聚合转成宽表——逻辑可行，但实际执行计划往往更重：每个子查询都要独立扫描原表，而 CTE + 单次扫描 + 条件聚合只需一次 I/O。
另一个隐蔽问题是时区。比如应用写入用 UTC，但查询时用 CONVERT_TZ(event_time, '+00:00', '+08:00') 再判断日期，会导致同一条记录在两个时间段里被重复/遗漏计数。最稳做法是在写入时就存标准 date 字段（如 event_date DATE GENERATED ALWAYS AS (event_time::date) STORED），查的时候直接用它。
下降率本身不解决归因，但它是触发人工排查的信号——比如 top 3 下降用户集中在某 SDK 版本或某渠道安装包，这时候再关联设备、版本、来源字段深挖，才真正有用。
