PostgreSQL统计客户创建后36个月分区间累计付款额

你之前用36段SQL UNION拼接的写法会重复扫描两张表36次，数据量上来后性能会非常差，下面的写法只需要扫描表各1次，就能一次性返回1-36个月的所有统计结果。

实现思路 #

• 先用CTE提前筛选出符合创建时间范围、委托方条件的客户，缩小计算范围
• 关联付款表时，提前计算每笔有效付款距离客户创建日的月份间隔，过滤掉36个月外、创建日前的无效付款数据
• 用generate_series生成1-36的月份节点，不需要手写重复UNION逻辑
• 通过条件聚合直接计算每个节点的累计付款额，避免重复关联

可直接运行的SQL #

WITH target_customers AS (
    SELECT
        id,
        client_name,
        date_created
    FROM customers
    WHERE
        date_created >= '2018-06-01'
        AND date_created <= '2018-12-31'
        AND client_name ILIKE '%Boden%' -- 不需要筛选特定委托方就删掉这行
),
valid_payments AS (
    SELECT
        tc.client_name,
        p.amount,
        -- 计算付款时间距离客户创建日的整月差
        (EXTRACT(YEAR FROM AGE(p.date_payment, tc.date_created)) * 12
        + EXTRACT(MONTH FROM AGE(p.date_payment, tc.date_created)))::INT AS month_gap
    FROM target_customers tc
    LEFT JOIN payments p ON tc.id = p.id
    WHERE
        p.date_payment >= tc.date_created -- 排除创建前的异常付款
        AND p.date_payment < tc.date_created + INTERVAL '36 months' -- 只保留36个月内的付款减少计算量
),
intervals AS (
    SELECT generate_series(1, 36) AS interval_num
)
SELECT
    i.interval_num AS "interval",
    tc.client_name,
    COALESCE(SUM(vp.amount) FILTER (WHERE vp.month_gap < i.interval_num), 0) AS "sum"
FROM intervals i
-- 交叉关联确保每个委托方都有完整1-36行记录，无付款也返回0
CROSS JOIN (SELECT DISTINCT client_name FROM target_customers) tc
LEFT JOIN valid_payments vp ON tc.client_name = vp.client_name
GROUP BY i.interval_num, tc.client_name
ORDER BY tc.client_name, i.interval_num;

注意事项 #

• 返回结果和预期格式完全一致，包含interval、client_name、sum三个字段，覆盖1到36所有节点
• 累计逻辑和原UNION写法一致：第N个月节点的金额是创建日到创建后N个月整之前的所有付款总和，如果业务需要包含节点当天的付款，把vp.month_gap < i.interval_num改成vp.month_gap <= i.interval_num即可
• 性能远高于UNION拼接：提前过滤无效数据，全流程仅对两张表做一次扫描，没有重复计算
• 如果需要统计所有委托方的回款数据，直接删掉target_customers里的client_name ILIKE '%Boden%'条件即可

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