Redshift中如何计算近30天唯一用户的累计统计值？

你的原查询错误原因在于：SUM(COUNT(DISTINCT user_id)) OVER (...)是将每天的去重用户数累加，而非统计整个30天窗口内的唯一用户。比如用户a在多个日期出现，会被重复计入不同日期的统计结果，最终求和时重复计算，导致结果偏大。

通用解决方案（适用于大多数SQL数据库） #

核心思路是先对用户每日访问记录去重，再针对每个日期统计其往前30天内的所有唯一用户数量：

WITH daily_unique AS (
    -- 去重：每个用户每天仅保留一条记录
    SELECT DISTINCT date, user_id 
    FROM mytable
),
all_dates AS (
    -- 获取表中所有不重复的日期
    SELECT DISTINCT date 
    FROM mytable
)
SELECT 
    ad.date,
    COUNT(DISTINCT du.user_id) AS distinct_users
FROM all_dates ad
LEFT JOIN daily_unique du
    -- 关联当前日期及往前29天的记录（包含当前日共30天）
    ON STR_TO_DATE(du.date, '%m/%d/%y') BETWEEN DATE_SUB(STR_TO_DATE(ad.date, '%m/%d/%y'), INTERVAL 29 DAY) 
                                            AND STR_TO_DATE(ad.date, '%m/%d/%y')
GROUP BY ad.date
ORDER BY STR_TO_DATE(ad.date, '%m/%d/%y') DESC;

针对支持窗口内COUNT(DISTINCT)的数据库（如PostgreSQL、BigQuery） #

可以简化为窗口函数写法，效率更高：

WITH daily_unique AS (
    SELECT DISTINCT 
        TO_DATE(date, 'MM/DD/YY') AS date,
        user_id 
    FROM mytable
)
SELECT 
    TO_CHAR(date, 'MM/DD/YY') AS date,
    COUNT(DISTINCT user_id) OVER (
        ORDER BY date
        RANGE BETWEEN INTERVAL '29 days' PRECEDING AND CURRENT ROW
    ) AS distinct_users
FROM daily_unique
GROUP BY date
ORDER BY date DESC;

关键说明 #

1. 日期格式转换：由于你的日期是MM/DD/YY字符串格式，必须转换为数据库可识别的日期类型（如STR_TO_DATE、TO_DATE），否则日期排序和区间计算会出错。
2. 30天区间定义：使用DATE_SUB(..., INTERVAL 29 DAY)是为了包含当前日期在内的30天（比如01/31/23往前29天是01/02/23，区间01/02/23到01/31/23共30天）。如果需要严格的“过去30天（不含当日）+当日”，这个逻辑可以根据需求调整。

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