基于PostgreSQL(Redshift)按日期统计Y日内交易超X次的活跃用户

要在PostgreSQL（Redshift）中按日期分组统计每个日期起Y日内拥有超过X笔唯一交易的客户数量，可通过以下步骤实现：

核心思路 #

1. 生成连续的统计日期序列：避免因原订单表日期不连续导致结果缺失日期
2. 计算每个用户在指定时间窗口内的唯一交易数：基于每个统计日期，向后取Y天的订单数据，统计用户的去重交易ID数量
3. 筛选并统计符合条件的用户：对每个统计日期，计数交易数超过X的用户数量

完整SQL代码 #

-- 定义参数：替换X为交易数阈值，Y为统计天数
WITH date_range AS (
    -- 生成从订单最早日期到最晚日期的连续日期（Redshift兼容写法）
    WITH RECURSIVE dates AS (
        SELECT (SELECT MIN(date) FROM orders) AS stat_date
        UNION ALL
        SELECT stat_date + 1 AS stat_date
        FROM dates
        WHERE stat_date < (SELECT MAX(date) FROM orders)
    )
    SELECT stat_date FROM dates
),
user_transaction_counts AS (
    -- 计算每个统计日期下，用户在Y日内的唯一交易数
    SELECT
        dr.stat_date,
        o.user_id,
        COUNT(DISTINCT o.transaction_id) AS unique_transactions
    FROM date_range dr
    LEFT JOIN orders o
        ON o.date >= dr.stat_date
        AND o.date < dr.stat_date + Y  -- 向后取Y天（含统计日当天）
    GROUP BY dr.stat_date, o.user_id
)
-- 统计每个日期符合条件的活跃客户数
SELECT
    stat_date AS date,
    COUNT(user_id) AS active_customers
FROM user_transaction_counts
WHERE unique_transactions > X  -- 筛选交易数超过X的用户
GROUP BY stat_date
ORDER BY stat_date;

关键细节说明 #

• 连续日期生成：使用递归CTE生成连续日期，兼容Redshift所有版本（若集群支持generate_series，也可替换为更简洁的写法）
• 唯一交易统计：通过COUNT(DISTINCT transaction_id)确保同一交易ID的多笔记录仅算1次，符合需求中的"唯一交易"定义
• 时间窗口控制：o.date >= dr.stat_date AND o.date < dr.stat_date + Y确保统计的是从当前日期起（含当天）的Y天内订单，例如Y=7时，统计窗口为stat_date到stat_date+6（共7天）
• 参数替换：将代码中的X替换为实际的交易数阈值（如3），Y替换为统计天数（如7）即可适配不同需求

示例适配 #

针对你提供的示例数据：

• 若X=1、Y=1，统计2022-03-01时，用户003的唯一交易数为1（同一transaction_id的两笔记录去重后），会被计入活跃客户
• 最终输出将按连续日期展示每个日期的符合条件客户数，与预期输出格式一致

内容的提问来源于stack exchange，提问作者Mikael Björkqvist