postgresql - Postgres - 简化基于日期的选择子查询的重复

Question

Postgres (9.6) 新手。

我有两张 table

sales_account(客户列表)

日记帐(交易行发票付款)

我想创建另一个名为 balances 的表，其中包含基于时间段(例如 7 天、14 天、30 天、60 天或更早)的未结交易摘要。

我已将查询详细编写为子查询(生成时间太长 - 有超过 1500 万条日志记录)，但我正在寻找有关如何简化查询的任何提示。

谢谢。

INSERT INTO balances (sales_account, 
    balance,
    period_1,
    period_2,
    period_3,
    period_4,
    period_5)       

SELECT  sales_account.id,

(SELECT coalesce(sum(journal.outstanding),0)
FROM journal
WHERE journal.sales_account = sales_account.id AND
journal.status='Active'), -- overall balance

(SELECT coalesce(sum(journal.outstanding),0)
FROM journal
WHERE journal.sales_account = sales_account.id AND
journal.status='Active' AND
journal.reversed_by is null AND
journal.original_id is null AND
journal.transaction_date <= CURRENT_DATE - interval '7 days'), -- current

(SELECT coalesce(sum(journal.outstanding),0)
FROM journal 
WHERE journal.sales_account = sales_account.id AND
journal.status='Active' AND
journal.reversed_by is null AND
journal.original_id is null AND
journal.transaction_date <= CURRENT_DATE  - interval '14 days' AND
journal.transaction_date >= CURRENT_DATE - interval '8 days'), -- 8-14 days

(SELECT coalesce(sum(journal.outstanding),0)
FROM journal
WHERE journal.sales_account = sales_account.id AND
journal.status='Active' AND
journal.transaction_date <=CURRENT_DATE  - interval '30 days' AND
journal.transaction_date >= CURRENT_DATE - interval '15 days'), -- 22-30 days

(SELECT coalesce(sum(journal.outstanding),0)
FROM journal
WHERE journal.sales_account = sales_account.id AND
journal.status='Active' AND
journal.transaction_date >= CURRENT_DATE - interval '31 days' AND
journal.transaction_date <=CURRENT_DATE  - interval '60 days'), -- 31-60 days

(SELECT coalesce(sum(journal.outstanding),0) 
FROM journal
WHERE journal.sales_account = sales_account.id AND
journal.status='Active' AND
journal.transaction_date >= CURRENT_DATE - interval '61 days') -- 61+ days

FROM sales_account;

Answer 1

您可以使用 FILTER简化 SUM 的表达式:

INSERT INTO balances (sales_account, 
    balance,
    period_1,
    period_2,
    period_3,
    period_4,
    period_5)     
SELECT 
    COALESCE(sum(journal.outstanding), 0) AS overall,
    COALESCE(sum(journal.outstanding) 
        FILTER (WHERE journal.transaction_date >= CURRENT_DATE - interval '7 days' 
            AND journal.reversed_by IS NULL 
            AND journal.original_id IS NULL), 0) AS period_1,   
    COALESCE(sum(journal.outstanding) 
        FILTER (WHERE journal.transaction_date >= CURRENT_DATE - interval '14 days' 
            AND journal.transaction_date <= CURRENT_DATE  - interval ' 8 days'
            AND journal.reversed_by IS NULL 
            AND journal.original_id IS NULL), 0) AS period_2,
    COALESCE(sum(journal.outstanding) 
        FILTER (WHERE journal.transaction_date >= CURRENT_DATE - interval '30 days' 
            AND journal.transaction_date <= CURRENT_DATE  - interval '22 days'), 0) AS period_3,
    COALESCE(sum(journal.outstanding) 
        FILTER (WHERE journal.transaction_date >= CURRENT_DATE - interval '60 days' 
            AND journal.transaction_date <= CURRENT_DATE  - interval ' 31 days'), 0) AS period_4,
    COALESCE(sum(journal.outstanding) 
        FILTER (WHERE journal.transaction_date >= CURRENT_DATE - interval '61 days'), 0) AS period_5
FROM 
    journal 
    JOIN sales_account ON (journal.sales_account = sales_account.id)
WHERE 
    journal.status='Active';