postgresql 对每个CTE行执行查询(generate_series())

5m1hhzi4 于 2023-06-05 发布在 PostgreSQL

关注(0)|答案(1)|浏览(218)

我想知道是否可以对CTE的每一行执行查询：

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
)

它返回：

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行执行以下查询：

-- For each of series CTE rows as 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 => ...

是否可以在1个查询中完成此操作？

postgresql

来源：https://stackoverflow.com/questions/76373754/perform-a-query-on-each-cte-rows-generate-series

1条答案

按热度按时间

dtcbnfnu1#

您似乎想要一个非对等连接来替换大部分WHERE子句。该方法为：
1.必须将包含now()的所有条件移到连接条件中。

now()必须替换为seriesdate。
注意：date是一个类型名称，所以最好不要这样命名一个列。

with series(seriesdate) as (
    select (d + '1 month'::interval - '1 day'::interval )::timestamp
    from generate_series(date_trunc('month', now()) - interval '1 year', now(), '1 month'::interval) d
)
SELECT seriesdate, seriesdateamount_per_month
FROM   application_contract_subscription
JOIN   series 
    ON subscription_date <= seriesdate
    AND (
       expiry_date IS NULL
       OR (expiry_date IS NOT NULL AND (commitment_period = 'monthly' AND subscription_date BETWEEN seriesdate - INTERVAL '30 day' AND seriesdate)
       OR (commitment_period = 'annually' AND subscription_date BETWEEN seriesdate - INTERVAL '1 year' AND seriesdate))
    )
WHERE  tenant_id = 'd7842881-31c9-4974-bc87-7a621ba440a5'

UNION ALL

SELECT seriesdate, amount_per_month
FROM   application_contract_service
JOIN   series 
    ON beginning_date <= seriesdate
    AND (
         amortization_end_date IS NULL
         OR (amortization_end_date >= seriesdate - INTERVAL '30 day')
    )
WHERE  tenant_id = 'd7842881-31c9-4974-bc87-7a621ba440a5'

UNION ALL

SELECT seriesdate, amount_per_month
JOIN   series 
    ON subscription_date <= now()
    AND (
         (amortization_end_date IS NULL AND subscription_date BETWEEN seriesdate - INTERVAL '30 day' AND seriesdate)
         OR (amortization_end_date >= seriesdate - INTERVAL '30 day')
    )
WHERE  tenant_id = 'd7842881-31c9-4974-bc87-7a621ba440a5'

赞(0）回复(0）举报 2023-06-05

我来回答

postgresql 对每个CTE行执行查询(generate_series())

1条答案

相关问题

热门标签

最新问答