Postgres - simplify repeat of select subqueries based on dates

Question

Newbie with Postgres (9.6).

I have 2 tables

sales_account (list of customers)

journal (transactions line invoices payments)

I want to create another table called balances which has a summary of outstanding transactions based on periods (like 7 days, 14 days, 30 days, 60 days or older).

I have written the query in long hand as sub-queries (it takes far too long to generate - there are over 15m journal records) but am looking for any hints as to how I can simplify the query.

Thank you.

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;

Michel Milezzi · Accepted Answer · 2017-11-16 13:44:33Z

1

You can use FILTER expression to simplify 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';

edited Nov 16, 2017 at 13:44

answered Nov 16, 2017 at 10:27

Michel Milezzi

11.4k3 gold badges24 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

JustMe Over a year ago

that's definietly more elegant

eurosoll Over a year ago

Thank you both for your quick answers. They both worked a dream. I have marked this one as the answer since it works slightly faster.

Michel Milezzi Over a year ago

Glad to help @eurosoll

eurosoll Over a year ago

Just a quick update for anyone looking at this in the future. Since the code is looking for dates in the past, then the dates need to be the other way round. So the furthest away date first and then the newer date. For example 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,

Michel Milezzi Over a year ago

True. Edited answer to posterity.

JustMe · Accepted Answer · 2017-11-16 09:55:47Z

This can be simplified to one query using case:

   SELECT 
       coalesce(sum(case 
         when journal.transaction_date >= CURRENT_DATE - interval '8 days' AND
              journal.transaction_date <= CURRENT_DATE  - interval '14 days'
         then 
          journal.outstanding else 0
         end),0) days814,
       coalesce(sum(case 
         when journal.transaction_date >= CURRENT_DATE - interval '22 days' AND
              journal.transaction_date <= CURRENT_DATE  - interval '30 days'
         then 
          journal.outstanding else 0
         end),0) days2230,
      coalesce(sum(case 
         when journal.transaction_date >= CURRENT_DATE - interval '31 days' AND
              journal.transaction_date <= CURRENT_DATE  - interval '60 days'
         then 
          journal.outstanding else 0
         end),0) days3160,
      coalesce(sum(case 
         when journal.transaction_date >= CURRENT_DATE - interval '61 days'
         then 
          journal.outstanding else 0
         end),0) days61,
    FROM journal, sales_account
    WHERE journal.sales_account = sales_account.id AND
    journal.status='Active'

Collectives™ on Stack Overflow

Postgres - simplify repeat of select subqueries based on dates

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related