统计活跃用户核心是定义“活跃”和“时间窗口”，再用SQL聚合计算；关键在理清业务逻辑，如DAU、WAU、MAU及留存率的差异化实现与口径对齐。
统计活跃用户核心是定义“活跃”和“时间窗口”，再用SQL聚合计算。关键不在复杂语法，而在理清业务逻辑：比如“近7天登录过的用户”和“连续3天登录的用户”，写法完全不同。
明确活跃标准和时间范围
先和产品、运营对齐口径，避免技术实现和业务需求脱节。常见定义包括：
- 单日活跃（DAU）：某一天有行为（如登录、下单、点击）的去重用户数
- 周活跃（WAU）：最近7天内至少活跃1天的用户数
- 月活跃（MAU）：最近30天内至少活跃1天的用户数
- 留存用户：在某日新增后，在后续第N天再次活跃的用户（需分层分析）
基础活跃用户统计（以DAU为例）
假设日志表 user_behavior 含字段：user_id、event_time（datetime）、event_type（如'login'、'click'）：
SELECT COUNT(DISTINCT user_id) AS dau FROM user_behavior WHERE DATE(event_time) = '2024-06-15' AND event_type = 'login';
注意点：
- 用 COUNT(DISTINCT user_id) 防止同一用户多次行为重复计数
- 日期过滤建议用 DATE(event_time) 或 event_time >= '2024-06-15' AND event_time ，后者能走索引
- 务必加行为类型条件，避免把埋点错误或测试数据计入
滚动窗口活跃（如WAU/MAU）
统计“截至今天，过去7天活跃过的用户总数”，用日期范围动态筛选：
SELECT COUNT(DISTINCT user_id) AS wau FROM user_behavior WHERE event_time >= DATE_SUB(CURDATE(), INTERVAL 6 DAY) AND event_time
说明：
- DATE_SUB(CURDATE(), INTERVAL 6 DAY) 表示7天窗口的起始日（含），例如今天6月15日 → 起始为6月9日
- 结束条件用 确保包含今日0点到23:59:59
- 不同数据库函数略有差异：PostgreSQL用
CURRENT_DATE - INTERVAL '6 days'，ClickHouse用today() - 6
留存分析（次日/7日留存）
需要两步：先找出某日新增用户，再查他们在后续日期是否回归。可用自连接或窗口函数：
WITH first_login AS ( SELECT user_id, MIN(DATE(event_time)) AS first_date FROM user_behavior WHERE event_type = 'login' GROUP BY user_id ), retention AS ( SELECT f.first_date, COUNT(DISTINCT b.user_id) AS retained_cnt FROM first_login f LEFT JOIN user_behavior b ON f.user_id = b.user_id AND DATE(b.event_time) = DATE_ADD(f.first_date, INTERVAL 1 DAY) AND b.event_type = 'login' GROUP BY f.first_date ) SELECT first_date, COALESCE(retained_cnt, 0) / COUNT(*) AS retention_rate FROM first_login f LEFT JOIN retention r ON f.first_date = r.first_date GROUP BY first_date;
要点：
- 用 MIN(DATE(event_time)) 定义“首次活跃日”，作为留存基准日
- LEFT JOIN + 条件匹配目标日（如次日），保留无回归记录的用户（计为0）
- 分母是当日新增用户数，分子是次日仍活跃的用户数，比值即留存率
