PostgreSQL查询聚合历史行：统计每日累计及近期活跃用户

嘿，这个需求我太熟了！做用户行为分析经常要算这俩指标，我给你一步步拆解，用SQL就能轻松实现～

你的用户活动表结构与数据 #

需求拆解 #

• 累计唯一用户：第N天的数值 = 从第0天到第N天所有有活动的去重用户总数
• 近期用户：第N天的数值 = 第N-1天或第N天有活动的去重用户总数

通用SQL解决方案（支持窗口函数的数据库：PostgreSQL、MySQL 8.0+、SQL Server等） #

先通过CTE把每天的去重用户提出来（避免同一用户一天多次活动重复计算），再用窗口函数分别计算两个指标：

WITH daily_unique_users AS (
    -- 先获取每天的去重用户列表
    SELECT DISTINCT
        day,
        username
    FROM user_activity
)
SELECT 
    day,
    -- 累计唯一用户：从第一天到当前天的所有去重用户数
    COUNT(DISTINCT username) OVER (
        ORDER BY day 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_unique_users,
    -- 近期用户：当前天和前一天的去重用户数
    COUNT(DISTINCT username) OVER (
        ORDER BY day 
        ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
    ) AS recent_users
FROM daily_unique_users
GROUP BY day
ORDER BY day;

执行结果 #

运行上面的SQL后，你会得到如下结果：

兼容旧版本数据库（比如MySQL 5.x，不支持窗口函数中的DISTINCT） #

如果你的数据库不支持窗口函数里的DISTINCT，可以用子查询的方式实现：

SELECT 
    days.day,
    -- 计算累计唯一用户
    (SELECT COUNT(DISTINCT username) 
     FROM user_activity 
     WHERE day <= days.day) AS cumulative_unique_users,
    -- 计算近期用户
    (SELECT COUNT(DISTINCT username) 
     FROM user_activity 
     WHERE day IN (days.day - 1, days.day)) AS recent_users
FROM (
    -- 先获取所有存在活动的日期
    SELECT DISTINCT day 
    FROM user_activity
) AS days
ORDER BY days.day;

这个写法虽然性能比窗口函数稍差，但兼容性更好，结果和上面完全一致。

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