在Presto中基于前一行值计算新增用户与累计用户

核心逻辑实现（递归CTE方案） #

假设你已有生成每日用户列表的查询，我们通过递归CTE来实现依赖前一行的累计计算，适配表中旧行被删除的场景：

WITH daily_user_lists AS (
    -- 替换为你已有的生成每日用户列表的SQL
    SELECT 
        date AS record_date,
        ARRAY_DISTINCT(users_today) AS users_today  -- 先去重，避免重复用户干扰计算
    FROM your_source_table
    ORDER BY record_date ASC
),
RECURSIVE cumulative_stats AS (
    -- 初始化：处理最早的记录（无前置行）
    SELECT 
        record_date,
        users_today,
        users_today AS new_users,
        users_today AS all_users_to_date
    FROM daily_user_lists
    WHERE record_date = (SELECT MIN(record_date) FROM daily_user_lists)
    
    UNION ALL
    
    -- 递归处理后续记录，依赖前一行的累计用户数据
    SELECT 
        d.record_date,
        d.users_today,
        -- 当日新增 = 当日用户 - 前一行累计用户（差集）
        ARRAY_EXCEPT(d.users_today, cs.all_users_to_date) AS new_users,
        -- 当前累计 = 前一行累计 + 当日新增（并集）
        ARRAY_UNION(cs.all_users_to_date, ARRAY_EXCEPT(d.users_today, cs.all_users_to_date)) AS all_users_to_date
    FROM daily_user_lists d
    JOIN cumulative_stats cs 
        ON d.record_date = (SELECT MIN(record_date) FROM daily_user_lists WHERE record_date > cs.record_date)
)
-- 输出最终统计结果
SELECT 
    record_date,
    users_today,
    new_users,
    all_users_to_date
FROM cumulative_stats
ORDER BY record_date ASC;

关键细节说明 #

• 递归CTE的作用：因为表中旧行可能被删除，无法依赖全量历史，递归逻辑会按日期顺序逐行计算，每一行仅依赖表中存在的前一行数据。
• 集合操作适配：如果你的用户集合使用SET类型而非数组，只需将ARRAY_EXCEPT替换为SET_DIFFERENCE，ARRAY_UNION替换为SET_UNION即可，SET类型自动去重，性能更优。
• 去重处理：用ARRAY_DISTINCT确保当日用户列表无重复值，避免差集计算出现错误。

边界场景处理 #

• 当表中只有一行数据时，直接将当日用户全部视为新增，累计用户等于当日用户。
• 当表中存在日期间隔时，递归逻辑会跳过间隔日期，仅处理实际存在的记录，适配旧行被删除的场景。

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