如何在SQL中实现10天间隔的DATE_TRUNC日期截断分组统计

原生DATE_TRUNC仅支持预定义时间单位（年/月/周/日等），无法直接实现10天粒度截断，可通过锚点偏移计算的方式手动划分固定步长的时间桶，完全满足“不从每月1号起始”的要求。

核心计算逻辑 #

• 选定固定锚点日期：所有10天桶都从该锚点开始向后顺延划分，不受自然月/自然周限制，可根据业务需求任意指定锚点（比如统计周期起始日、业务节点日期均可）
• 计算每条支付记录日期与锚点的天数差，除以10向下取整，得到所属桶序号
• 桶起始日 = 锚点 + 桶序号 * 10天
• 桶结束日 = 桶起始日 + 9天（保证每个桶恰好覆盖10天）

PostgreSQL 实现（适配原有语法环境） #

以下示例使用锚点2020-11-03，计算出的2020-11-20对应桶为2020-11-13 to 2020-11-22，和给出的示例输出对齐：

WITH base_params AS (
    -- 可直接修改此处锚点日期，调整桶的起始边界
    SELECT DATE '2020-11-03' AS anchor_date
),
payment_buckets AS (
    SELECT
        client_id,
        -- 若金额字段已为数值，可直接替换为 payments.sum
        REPLACE(payments.sum, '$', '')::INT AS pay_amount,
        payments.date,
        anchor_date + (FLOOR((payments.date - anchor_date) / 10)) * INTERVAL '10 days' AS interval_start,
        (anchor_date + (FLOOR((payments.date - anchor_date) / 10)) * INTERVAL '10 days') + INTERVAL '9 days' AS interval_end
    FROM payments, base_params
    -- 沿用原有时间范围过滤条件
    WHERE payments.date BETWEEN '2020-11-01' AND '2020-12-31'
)
SELECT
    client_id,
    -- 若无需保留$符号，可直接去掉拼接
    SUM(pay_amount) || '$' AS sum,
    TO_CHAR(interval_start, 'YYYY-MM-DD') || ' to ' || TO_CHAR(interval_end, 'YYYY-MM-DD') AS Interval_of_payments
FROM payment_buckets
GROUP BY client_id, interval_start, interval_end
ORDER BY interval_start, client_id;

适配说明 #

• 若需要调整桶的起始规则，仅需修改base_params中的anchor_date值即可，例如希望2020-12-01作为新桶起始，将锚点改为DATE '2020-12-01'
• 同一客户端多条记录落在同一10天桶内时，会自动合并求和，符合聚合要求
• 该逻辑和DATE_TRUNC('week')的底层实现一致：原生周截断也是以周一为固定锚点、按7天步长划分桶，此处仅将步长替换为自定义的10天。

MySQL 8.0+ 适配版本 #

如果使用MySQL环境，可参考以下语法实现相同逻辑：

WITH base_params AS (
    SELECT DATE('2020-11-03') AS anchor_date
),
payment_buckets AS (
    SELECT
        client_id,
        CAST(REPLACE(payments.sum, '$', '') AS UNSIGNED) AS pay_amount,
        payments.date,
        DATE_ADD(anchor_date, INTERVAL FLOOR(DATEDIFF(payments.date, anchor_date)/10)*10 DAY) AS interval_start,
        DATE_ADD(DATE_ADD(anchor_date, INTERVAL FLOOR(DATEDIFF(payments.date, anchor_date)/10)*10 DAY), INTERVAL 9 DAY) AS interval_end
    FROM payments, base_params
    WHERE payments.date BETWEEN '2020-11-01' AND '2020-12-31'
)
SELECT
    client_id,
    CONCAT(SUM(pay_amount), '$') AS sum,
    CONCAT(DATE_FORMAT(interval_start, '%Y-%m-%d'), ' to ', DATE_FORMAT(interval_end, '%Y-%m-%d')) AS Interval_of_payments
FROM payment_buckets
GROUP BY client_id, interval_start, interval_end
ORDER BY interval_start, client_id;

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