如何在PostgreSQL中编写SQL按时间区间统计用户首次购买数？

现有一个名为payments的表，包含created_at（时间戳）和user_id（用户ID）字段。需求是：按周（支持自定义时间间隔）聚合统计首次购买用户数——即每个用户仅在其首次购买所在的周被统计，后续重复购买不计入。

示例数据 #

原查询问题 #

我编写了如下查询语句，但会重复统计多次购买的用户（比如示例中user_id=1会被计入第1周和第2周），请问该如何优化？

WITH dates AS 
(
    SELECT *
    FROM generate_series(
      '2022-07-22T15:30:06.687Z'::DATE,
      '2022-11-21T17:04:59.457Z'::DATE,
      '1 week'
    ) date
)
SELECT 
    dates.date::DATE AS date, 
    COALESCE(COUNT(DISTINCT(user_id)), 0) AS registrations
FROM
    dates
LEFT JOIN  
    payment ON created_at::DATE BETWEEN dates.date AND dates.date::date + '1 ${dateUnit}'::INTERVAL
GROUP BY 
    dates.date
ORDER BY 
    dates.date DESC;

优化方案 #

核心思路是：先锁定每个用户的首次购买时间，再将该时间映射到对应的统计周期，最后按周期聚合计数，从根源避免重复统计。

优化后的完整查询 #

WITH user_first_purchase AS (
    -- 获取每个用户的首次购买时间
    SELECT 
        user_id,
        MIN(created_at) AS first_purchase_time
    FROM payments
    GROUP BY user_id
),
dates AS (
    -- 生成统计周期的起始日期序列（支持自定义间隔）
    SELECT 
        generate_series(
            '2022-07-22'::DATE,
            '2022-11-21'::DATE,
            '1 week'::INTERVAL  -- 可替换为'1 month'/'1 day'等自定义间隔
        ) AS period_start
)
-- 匹配首次购买到对应周期并统计
SELECT 
    d.period_start::DATE AS date,
    COALESCE(COUNT(u.user_id), 0) AS registrations
FROM dates d
LEFT JOIN user_first_purchase u
    -- 精准匹配周期范围，避免边界重叠
    ON u.first_purchase_time >= d.period_start
    AND u.first_purchase_time < d.period_start + '1 week'::INTERVAL
GROUP BY d.period_start
ORDER BY d.period_start DESC;

关键优化点 #

1. 锁定首次购买：通过MIN(created_at)分组获取每个用户的最早购买记录，确保每个用户仅对应一条有效数据。
2. 精准周期匹配：用>=和<替代BETWEEN，避免周期边界的重复匹配（比如原逻辑中dates.date + 1 week会包含下一个周期的起始日，<则只统计到当前周期的最后一刻）。
3. 支持自定义间隔：只需修改generate_series和LEFT JOIN中的间隔参数（如替换为'1 month'::INTERVAL），即可切换统计周期。

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