Redshift中滚动90天内满足3次交互的ID去重计数问题

你的推测完全正确——原查询仅统计了当日有交互行为的ID，遗漏了那些过去90天内交互次数≥3次但当日无交互的ID，同时也会缺失没有任何交互记录的日期数据。以下是针对Amazon Redshift的解决方案：

核心思路 #

1. 生成完整的日期序列：确保覆盖需要统计的所有日期（从表中最早交互日期到最晚交互日期），避免遗漏无交互的日期。
2. 对每个日期，计算其过去90天窗口内所有ID的交互次数，筛选出次数≥3的高活跃ID。
3. 按日期统计去重的高活跃ID数量。

最终SQL查询 #

方法1：使用generate_series生成日期序列 #

WITH date_range AS (
    -- 生成所有需要统计的日期（从表中最早到最晚交互日期）
    SELECT date_trunc('day', dd)::date AS date_day
    FROM generate_series(
        (SELECT MIN(dateinteracted)::date FROM interactions),
        (SELECT MAX(dateinteracted)::date FROM interactions),
        '1 day'::interval
    ) dd
),
id_rolling_stats AS (
    -- 对每个日期，统计过去90天内每个ID的交互次数
    SELECT
        dr.date_day,
        i.id,
        COUNT(i.id) AS interaction_count
    FROM date_range dr
    LEFT JOIN interactions i
        ON i.dateinteracted::date BETWEEN dr.date_day - 90 AND dr.date_day - 1
    GROUP BY dr.date_day, i.id
    -- 筛选出90天内交互≥3次的ID
    HAVING COUNT(i.id) >= 3
)
-- 按日期统计去重的高活跃ID数量
SELECT
    date_day AS "Date",
    COUNT(DISTINCT id) AS "DistinctCount"
FROM id_rolling_stats
GROUP BY date_day
ORDER BY date_day DESC;

方法2：递归CTE生成日期序列（兼容Redshift旧版本） #

如果你的Redshift版本对generate_series支持有限，可使用递归CTE生成日期范围：

WITH RECURSIVE date_range AS (
    -- 起始日期：表中最早的交互日期
    SELECT (SELECT MIN(dateinteracted)::date FROM interactions) AS date_day
    UNION ALL
    -- 递归生成后续日期，直到表中最晚交互日期
    SELECT date_day + 1
    FROM date_range
    WHERE date_day < (SELECT MAX(dateinteracted)::date FROM interactions)
),
id_rolling_stats AS (
    SELECT
        dr.date_day,
        i.id,
        COUNT(i.id) AS interaction_count
    FROM date_range dr
    LEFT JOIN interactions i
        ON i.dateinteracted::date BETWEEN dr.date_day - 90 AND dr.date_day - 1
    GROUP BY dr.date_day, i.id
    HAVING COUNT(i.id) >= 3
)
SELECT
    date_day AS "Date",
    COUNT(DISTINCT id) AS "DistinctCount"
FROM id_rolling_stats
GROUP BY date_day
ORDER BY date_day DESC;

关键改进点 #

• 完整日期覆盖：通过date_rangeCTE生成所有需要统计的日期，确保即使当天无交互记录，也能输出该日期的统计结果。
• 全窗口ID统计：使用左连接关联每个日期的90天窗口内所有交互记录，统计所有符合条件的ID，而非仅当日有交互的ID。
• 性能优化：通过提前按日期和ID分组统计，避免重复计算每个ID的交互次数。

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