如何按月份统计每日活跃用户数？SQL实现方案求助

核心思路 #

先统计每日的活跃用户数（用户当日有至少一次活动即视为活跃，需用COUNT(DISTINCT user_id)去重避免同一用户重复计数），再将每日数据按月份分组展示，或进一步汇总月度日活统计指标。

假设Activity表结构 #

基于常见业务场景，假设表包含以下核心字段：

• user_id: 用户唯一标识
• activity_date: 用户活动的时间戳或日期字段

基础需求：每个月份下的每日活跃明细 #

以下SQL会输出每个月份对应的每一天的活跃用户数，结果按月份、日期排序：

MySQL/MariaDB 版本

SELECT
  DATE_FORMAT(activity_day, '%Y-%m') AS activity_month,
  activity_day,
  daily_active_users
FROM (
  -- 子查询：统计每日活跃用户数
  SELECT
    DATE(activity_date) AS activity_day,
    COUNT(DISTINCT user_id) AS daily_active_users
  FROM Activity
  GROUP BY DATE(activity_date)
) daily_stats
ORDER BY activity_month, activity_day;

PostgreSQL 版本

SELECT
  TO_CHAR(activity_day, 'YYYY-MM') AS activity_month,
  activity_day,
  daily_active_users
FROM (
  SELECT
    DATE(activity_date) AS activity_day,
    COUNT(DISTINCT user_id) AS daily_active_users
  FROM Activity
  GROUP BY DATE(activity_date)
) daily_stats
ORDER BY activity_month, activity_day;

SQL Server 版本

SELECT
  FORMAT(activity_day, 'yyyy-MM') AS activity_month,
  activity_day,
  daily_active_users
FROM (
  SELECT
    CAST(activity_date AS DATE) AS activity_day,
    COUNT(DISTINCT user_id) AS daily_active_users
  FROM Activity
  GROUP BY CAST(activity_date AS DATE)
) daily_stats
ORDER BY activity_month, activity_day;

扩展需求：月度日活汇总统计 #

如果需要每个月份的日活汇总指标（如日均活跃、最高/最低日活、活跃天数），可以用以下SQL（以MySQL为例）：

SELECT
  DATE_FORMAT(activity_day, '%Y-%m') AS activity_month,
  COUNT(*) AS active_days_in_month,
  ROUND(AVG(daily_active_users), 2) AS avg_daily_active,
  MAX(daily_active_users) AS max_daily_active,
  MIN(daily_active_users) AS min_daily_active
FROM (
  SELECT
    DATE(activity_date) AS activity_day,
    COUNT(DISTINCT user_id) AS daily_active_users
  FROM Activity
  GROUP BY DATE(activity_date)
) daily_stats
GROUP BY DATE_FORMAT(activity_day, '%Y-%m')
ORDER BY activity_month;

内容的提问来源于stack exchange，提问作者hieu pham