Postgres中如何统计包含起始部分周的按周聚合数据

问题原因 #

原查询中，子查询c使用date_trunc('week', conversions.created_at)按默认周（周一为起始）分组，得到的聚合键是每周一的日期（比如2024-12-02、2024-12-09），但你自定义的起始桶是2024-12-04，两者无法匹配，因此左连接后该桶的统计数为0。

解决方案：自定义日期桶匹配统计 #

我们需要直接定义所有目标日期桶，然后将每条记录映射到对应的桶，再按桶统计数量。

方法1：用CASE WHEN映射日期桶

WITH date_buckets AS (
    -- 明确定义所有自定义日期桶的起始和结束日期
    SELECT 
        '2024-12-04'::date AS bucket_start,
        '2024-12-08'::date AS bucket_end
    UNION ALL
    SELECT 
        '2024-12-09'::date,
        '2024-12-15'::date
    UNION ALL
    SELECT 
        '2024-12-16'::date,
        '2024-12-22'::date
    UNION ALL
    SELECT 
        '2024-12-23'::date,
        '2024-12-23'::date
)
SELECT
    db.bucket_start AS series_interval,
    COALESCE(c.conversion_count, 0) AS conversion_count
FROM date_buckets db
LEFT JOIN (
    SELECT
        -- 将每条转换记录映射到对应的自定义桶起始日
        CASE
            WHEN cv.created_at BETWEEN '2024-12-04' AND '2024-12-08 23:59:59.999999' THEN '2024-12-04'::date
            WHEN cv.created_at BETWEEN '2024-12-09' AND '2024-12-15 23:59:59.999999' THEN '2024-12-09'::date
            WHEN cv.created_at BETWEEN '2024-12-16' AND '2024-12-22 23:59:59.999999' THEN '2024-12-16'::date
            WHEN cv.created_at BETWEEN '2024-12-23' AND '2024-12-23 23:59:59.999999' THEN '2024-12-23'::date
        END AS bucket_start,
        COUNT(cv.id) AS conversion_count
    FROM conversions cv
    INNER JOIN commissions cm ON cm.conversion_id = cv.id
    WHERE
        cv.created_at >= '2024-12-02' 
        AND cv.created_at <= '2024-12-23 23:59:59.999999'
        -- 过滤掉自定义桶范围外的记录（比如2024-12-02至12-03）
        AND cv.created_at >= '2024-12-04'
    GROUP BY bucket_start
) c ON c.bucket_start = db.bucket_start
ORDER BY db.bucket_start;

方法2：用JOIN直接匹配日期桶区间

这种方式更灵活，适合桶数量较多的场景：

WITH date_buckets AS (
    SELECT 
        '2024-12-04'::date AS bucket_start,
        '2024-12-08'::date AS bucket_end
    UNION ALL
    SELECT 
        '2024-12-09'::date,
        '2024-12-15'::date
    UNION ALL
    SELECT 
        '2024-12-16'::date,
        '2024-12-22'::date
    UNION ALL
    SELECT 
        '2024-12-23'::date,
        '2024-12-23'::date
)
SELECT
    db.bucket_start AS series_interval,
    COUNT(cv.id) AS conversion_count
FROM date_buckets db
LEFT JOIN conversions cv 
    ON cv.created_at >= db.bucket_start
    -- 用区间计算替代硬写的时间戳，避免出错
    AND cv.created_at <= db.bucket_end + INTERVAL '1 day' - INTERVAL '1 microsecond'
    AND cv.created_at <= '2024-12-23 23:59:59.999999'
LEFT JOIN commissions cm ON cm.conversion_id = cv.id
WHERE
    cv.created_at >= '2024-12-02'
GROUP BY db.bucket_start
ORDER BY db.bucket_start;

说明 #

• 两种方法都先定义了你的目标日期桶，确保统计范围完全符合需求。
• 方法1通过CASE WHEN明确映射每条记录到对应桶，逻辑直观；方法2通过JOIN关联桶区间，扩展性更强。
• 最终结果会包含所有自定义桶，且每个桶的统计数准确对应其日期范围。

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