如何通过单条SQL查询对CTE的每行执行指定查询？

开发者阿泽

2026-7-20

问题描述

我有一个用generate_series生成的CTE（命名为series），它会返回当前日期往前一年的每月最后一天的时间戳，示例结果如下：

2022-05-31 00:00:00.000000
2022-06-30 00:00:00.000000
2022-07-31 00:00:00.000000
2022-08-31 00:00:00.000000
2022-09-30 00:00:00.000000
2022-10-31 00:00:00.000000
2022-11-30 00:00:00.000000
2022-12-31 00:00:00.000000
2023-01-31 00:00:00.000000
2023-02-28 00:00:00.000000
2023-03-31 00:00:00.000000
2023-04-30 00:00:00.000000
2023-05-31 00:00:00.000000

该CTE的SQL代码为：

with series as (
    select (d + '1 month'::interval - '1 day'::interval )::timestamp date
    from generate_series(date_trunc('month', now()) - interval '1 year', now(), '1 month'::interval) d
)

现在我需要针对series中的每一行日期，执行如下多表联合查询，计算对应日期的amount_per_month总和：

-- 针对series CTE的每一行series_row：
SELECT amount_per_month FROM application_contract_subscription WHERE
                 subscription_date <= series_row.date AND (
                     expiry_date IS NULL
                     OR (expiry_date IS NOT NULL AND (commitment_period = 'monthly' AND subscription_date BETWEEN series_row.date - INTERVAL '30 day' AND series_row.date) OR (commitment_period = 'annually' AND subscription_date BETWEEN series_row.date - INTERVAL '1 year' AND series_row.date))
                 )
         AND tenant_id = 'd7842881-31c9-4974-bc87-7a621ba440a5'
         UNION ALL
         SELECT amount_per_month FROM application_contract_service WHERE
                 beginning_date <= series_row.date AND (
                     amortization_end_date IS NULL
                     OR (amortization_end_date >= series_row.date - INTERVAL '30 day')
                 )
         AND tenant_id = 'd7842881-31c9-4974-bc87-7a621ba440a5'
         UNION ALL
         SELECT amount_per_month FROM application_contract_licence WHERE
                 subscription_date <= series_row.date AND (
                     (amortization_end_date IS NULL AND subscription_date BETWEEN series_row.date - INTERVAL '30 day' AND series_row.date)
                     OR (amortization_end_date >= series_row.date - INTERVAL '30 day')
                 )
         AND tenant_id = 'd7842881-31c9-4974-bc87-7a621ba440a5'

预期结果格式如下：

2022-05-31 00:00:00.000000 => 10000
2022-06-30 00:00:00.000000 => 500
2022-07-31 00:00:00.000000 => 0
2022-08-31 00:00:00.000000 => 1200
2022-09-30 00:00:00.000000 => 1400
2022-10-31 00:00:00.000000 => ...
2022-11-30 00:00:00.000000 => ...
2022-12-31 00:00:00.000000 => ...
2023-01-31 00:00:00.000000 => ...
2023-02-28 00:00:00.000000 => ...
2023-03-31 00:00:00.000000 => ...
2023-04-30 00:00:00.000000 => ...
2023-05-31 00:00:00.000000 => ...

请问能否仅通过一条SQL查询实现该需求？

解决方案

完全可以通过一条SQL实现需求，核心思路是将series CTE与三个业务表的联合查询结果关联，按日期分组求和即可。

具体SQL代码如下：

WITH series AS (
    SELECT (d + '1 month'::interval - '1 day'::interval)::timestamp AS date
    FROM generate_series(date_trunc('month', now()) - interval '1 year', now(), '1 month'::interval) d
)
SELECT 
    s.date,
    COALESCE(SUM(amounts.amount_per_month), 0) AS total_amount
FROM series s
LEFT JOIN (
    -- 合并三个业务表的数据，统一字段（缺失字段用NULL填充）
    SELECT amount_per_month, tenant_id, subscription_date, expiry_date, commitment_period, NULL::timestamp AS beginning_date, NULL::timestamp AS amortization_end_date
    FROM application_contract_subscription
    UNION ALL
    SELECT amount_per_month, tenant_id, NULL::timestamp AS subscription_date, NULL::timestamp AS expiry_date, NULL::text AS commitment_period, beginning_date, amortization_end_date
    FROM application_contract_service
    UNION ALL
    SELECT amount_per_month, tenant_id, subscription_date, NULL::timestamp AS expiry_date, NULL::text AS commitment_period, NULL::timestamp AS beginning_date, amortization_end_date
    FROM application_contract_licence
) amounts ON amounts.tenant_id = 'd7842881-31c9-4974-bc87-7a621ba440a5'
    -- 实现原查询的所有过滤逻辑
    AND (
        -- application_contract_subscription 的条件
        (amounts.subscription_date IS NOT NULL AND amounts.subscription_date <= s.date AND (
            amounts.expiry_date IS NULL
            OR (amounts.expiry_date IS NOT NULL AND (
                amounts.commitment_period = 'monthly' AND amounts.subscription_date BETWEEN s.date - INTERVAL '30 day' AND s.date
                OR amounts.commitment_period = 'annually' AND amounts.subscription_date BETWEEN s.date - INTERVAL '1 year' AND s.date
            ))
        ))
        OR
        -- application_contract_service 的条件
        (amounts.beginning_date IS NOT NULL AND amounts.beginning_date <= s.date AND (
            amounts.amortization_end_date IS NULL
            OR amounts.amortization_end_date >= s.date - INTERVAL '30 day'
        ))
        OR
        -- application_contract_licence 的条件
        (amounts.subscription_date IS NOT NULL AND amounts.subscription_date <= s.date AND (
            (amounts.amortization_end_date IS NULL AND amounts.subscription_date BETWEEN s.date - INTERVAL '30 day' AND s.date)
            OR amounts.amortization_end_date >= s.date - INTERVAL '30 day'
        ))
    )
GROUP BY s.date
ORDER BY s.date;

代码说明：

保留日期序列CTE：原series CTE保持不变，用于生成目标日期范围的每月最后一天。
合并业务表数据：通过UNION ALL将三个业务表的数据合并为一个临时数据集，统一字段结构，缺失字段用NULL填充。
关联与过滤：使用LEFT JOIN确保每个日期都能被保留（即使无匹配数据，总和会被转为0），同时在ON子句中实现原查询的所有过滤规则。
分组求和：按日期分组，用COALESCE将空值转为0，得到每个日期的amount_per_month总和。

如果想要和预期格式完全一致的输出（用=>连接日期与总和），可以调整SELECT语句为：

SELECT CONCAT(s.date::text, ' => ', COALESCE(SUM(amounts.amount_per_month), 0)) AS result

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

如何通过单条SQL查询对CTE的每行执行指定查询？

代码说明： #

代码说明：