Oracle SQL聚合查询：计算用户创建日期当日累计活跃用户数

现有存储用户状态历史与创建记录的表myTable，字段定义如下：

• ID：记录唯一标识，自增属性，数值越大代表记录生成时间越晚
• USERID：用户唯一ID
• STATUS：用户状态，有效值包含active（活跃）、inactive（非活跃）等
• USER_CREATED：用户创建日期，格式为YYYY-MM-DD
• TIMESTAMP：当前状态记录生成的时间戳

统计日期区间2020-02-01 ~ 2020-02-03内每日的3个核心指标：

1. 当日新创建用户数：USER_CREATED等于当日的去重USERID总数
2. 当日创建用户中当前仍活跃数：USER_CREATED等于当日，且用户最新状态为active的去重USERID总数
3. 当日全量活跃用户数：所有用户在当日及之前生成的最新状态记录中，STATUS为active的去重USERID总数

以下为标准SQL语法实现，兼容支持窗口函数的主流数据库（MySQL8.0+、PostgreSQL、Hive、Spark SQL等）：

WITH 
-- 计算每个用户的最新状态，用于支撑前两个指标统计
user_latest_status AS (
    SELECT 
        USERID,
        STATUS AS latest_status,
        USER_CREATED
    FROM (
        SELECT 
            USERID,
            STATUS,
            USER_CREATED,
            ROW_NUMBER() OVER(PARTITION BY USERID ORDER BY ID DESC) AS rn
        FROM myTable
    ) t
    WHERE rn = 1
),
-- 生成目标统计日期序列，避免无新用户的日期在结果中丢失
target_dates AS (
    SELECT '2020-02-01' AS stat_date
    UNION ALL SELECT '2020-02-02'
    UNION ALL SELECT '2020-02-03'
    -- 若数据库支持日期生成函数可动态生成，比如PostgreSQL写法：
    -- SELECT generate_series('2020-02-01'::date, '2020-02-03'::date, '1 day')::text AS stat_date
),
-- 预计算每个统计日期下所有用户的最新状态，用于支撑第三个指标统计
user_daily_status AS (
    SELECT 
        d.stat_date,
        s.USERID,
        s.STATUS,
        ROW_NUMBER() OVER(PARTITION BY d.stat_date, s.USERID ORDER BY s.ID DESC) AS rn
    FROM target_dates d
    LEFT JOIN myTable s
        ON DATE(s.TIMESTAMP) <= d.stat_date
)
-- 关联汇总三个指标结果
SELECT 
    td.stat_date,
    COUNT(DISTINCT uls.USERID) AS new_user_cnt,
    COUNT(DISTINCT CASE WHEN uls.latest_status = 'active' THEN uls.USERID END) AS new_user_active_cnt,
    COUNT(DISTINCT CASE WHEN uds.STATUS = 'active' THEN uds.USERID END) AS total_daily_active_cnt
FROM target_dates td
LEFT JOIN user_latest_status uls
    ON uls.USER_CREATED = td.stat_date
LEFT JOIN user_daily_status uds
    ON uds.stat_date = td.stat_date AND uds.rn = 1
GROUP BY td.stat_date
ORDER BY td.stat_date;

• 第一个CTE通过按USERID分组取最大ID的方式，拿到每个用户的最新状态，直接满足前两个指标的统计需求
• 第三个CTE将统计日期和所有用户状态记录关联，过滤出每个用户在统计日期之前的所有状态后，再取最大ID对应的记录，即可得到用户在该统计日的最新有效状态，聚合后得到当日全量活跃用户数

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