如何基于指定表统计各版本每日活跃用户（含无记录日期）

这是个很典型的时间序列统计需求，要覆盖到没有用户活跃的日期，关键得先构建完整的日期范围和所有需要统计的Version列表，再通过关联把实际活跃数据匹配上去。我结合你的表结构给你详细说明：

核心思路 #

1. 生成连续的日期序列：覆盖你需要统计的所有日期（比如从table1最早数据日期到最晚日期），确保无记录的日期也能出现在结果里。
2. 提取所有要统计的Version：不管是table1里的具体版本（v1/v2等）还是table2里的分组版本（A/R），先把所有可能的Version列出来。
3. 笛卡尔积关联日期和Version：得到「每个日期+每个Version」的全量组合，保证每个维度都不遗漏。
4. 左连接实际活跃数据：匹配对应日期和Version的用户记录，统计去重后的活跃用户数，没有数据的会自动显示0。

场景1：统计table1中的具体Version（v1/v2/v3等） #

以MySQL为例，用递归CTE生成日期序列：

WITH date_range AS (
    -- 取table1中最早和最晚的日期，生成连续日期
    SELECT MIN(DATE(server_time)) AS date_val FROM table1
    UNION ALL
    SELECT DATE_ADD(date_val, INTERVAL 1 DAY) FROM date_range
    WHERE date_val < (SELECT MAX(DATE(server_time)) FROM table1)
),
all_versions AS (
    -- 提取table1中所有不重复的Version
    SELECT DISTINCT version FROM table1
)
SELECT 
    dr.date_val AS `日期`,
    av.version AS `版本`,
    -- 统计去重的活跃用户数，无数据则为0
    COUNT(DISTINCT t1.user_id) AS `每日活跃用户数`
FROM date_range dr
-- 笛卡尔积：每个日期都对应所有Version
CROSS JOIN all_versions av
-- 左连接table1，匹配当天该Version的活跃记录（这里假设活跃定义为login事件）
LEFT JOIN table1 t1 
    ON DATE(t1.server_time) = dr.date_val 
    AND t1.version = av.version
    AND t1.event_id = 'login' -- 若活跃是任意事件，可删除此条件
GROUP BY dr.date_val, av.version
ORDER BY dr.date_val, av.version;

场景2：统计table2中的分组Version（A/R） #

如果你的需求是按table2里的分组Version（比如A对应A83机型，R对应R11s/R15机型）统计，只需要多一步关联table2：

WITH date_range AS (
    SELECT MIN(DATE(server_time)) AS date_val FROM table1
    UNION ALL
    SELECT DATE_ADD(date_val, INTERVAL 1 DAY) FROM date_range
    WHERE date_val < (SELECT MAX(DATE(server_time)) FROM table1)
),
all_group_versions AS (
    -- 提取table2中所有不重复的分组Version
    SELECT DISTINCT version FROM table2
)
SELECT 
    dr.date_val AS `日期`,
    agv.version AS `分组版本`,
    COUNT(DISTINCT t1.user_id) AS `每日活跃用户数`
FROM date_range dr
CROSS JOIN all_group_versions agv
-- 先关联table2，拿到分组对应的机型
LEFT JOIN table2 t2 ON agv.version = t2.version
-- 再关联table1，匹配当天该分组机型的活跃用户
LEFT JOIN table1 t1 
    ON DATE(t1.server_time) = dr.date_val 
    AND t1.model = t2.model
    AND t1.event_id = 'login'
GROUP BY dr.date_val, agv.version
ORDER BY dr.date_val, agv.version;

不同数据库的语法差异 #

• PostgreSQL：可以用generate_series直接生成日期序列，更简洁：

  SELECT generate_series(
      (SELECT MIN(DATE(server_time)) FROM table1),
      (SELECT MAX(DATE(server_time)) FROM table1),
      INTERVAL '1 day'
  )::date AS date_val
  

• SQL Server：递归CTE的日期生成用DATEADD，语法和MySQL类似，只是函数名略有不同。
• Oracle：可以用CONNECT BY生成日期序列，或者用递归CTE。

注意事项 #

• 如果需要指定固定日期范围（比如不是table1的时间区间），可以直接替换date_range里的MIN和MAX为固定日期，比如'2018-07-01'和'2018-08-31'。
• 活跃用户的定义可以根据实际调整：比如如果只要有任意事件就算活跃，就去掉AND t1.event_id = 'login'这个条件。

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