Postgres filtered WITH query

Question

I currently have the following query:

WITH instances AS (
    SELECT b.ldtc as date, a.fk_item_id, b.movement, a.quantity, 
        CASE WHEN b.movement = 'Inbound' THEN a.quantity ELSE -a.quantity END as absquantity
    FROM inventory_resupplylogiteminstance a
    INNER JOIN inventory_resupplylog b ON b.uid = a.fk_resupply_log_id
)

SELECT a.name, 
    SUM(CASE WHEN b.date < ('2018-10-10'::date) THEN b.absquantity END) as starting_balance,

    SUM(CASE WHEN b.date > ('2018-10-10'::date) AND b.date < ('2018-10-12'::date) AND b.movement = 'aa' THEN b.absquantity END) as aa,
    SUM(CASE WHEN b.date > ('2018-10-10'::date) AND b.date < ('2018-10-12'::date) AND b.movement = 'bb' THEN b.absquantity END) as bb,
    SUM(CASE WHEN b.date > ('2018-10-10'::date) AND b.date < ('2018-10-12'::date) AND b.movement = 'cc' THEN b.absquantity END) as cc,
    SUM(CASE WHEN b.date > ('2018-10-10'::date) AND b.date < ('2018-10-12'::date) AND b.movement = 'dd' THEN b.absquantity END) as dd,

    SUM(CASE WHEN b.date < ('2018-10-12'::date) THEN b.absquantity END) AS ending_balance




FROM inventory_item a
LEFT JOIN instances b ON b.fk_item_id = a.uid

GROUP BY a.uid, a.name
ORDER BY a.name

As you can see, the 2nd-5th SUM lines are redundant in that they're querying for rows where b.date is between 2018-10-10 and 2018-10-12. Is there any way to rewrite my query so as to only have to write b.date > ('2018-10-10'::date) AND b.date < ('2018-10-12'::date) once and still be able to SELECT starting_balance and ending_balance on the same row?

score 2 · Accepted Answer · 2018-10-17 14:17:10Z

You can check the range in the CTE and create a flag that indicates if the row is in the indicated range.

Then you can use that flag in the final SELECT. Switching to a FILTER () expression also makes it more readable:

WITH instances AS (
  SELECT b.ldtc as date, 
         a.fk_item_id, 
         b.movement, 
         a.quantity, 
         CASE 
           WHEN b.movement = 'Inbound' THEN a.quantity 
           ELSE -a.quantity 
         END as absquantity,
         -- the column in_range returns either true or false
         (b.ldtc > ('2018-10-10'::date) AND b.date < ('2018-10-12'::date)) as in_range
  FROM inventory_resupplylogiteminstance a
    INNER JOIN inventory_resupplylog b ON b.uid = a.fk_resupply_log_id
)
SELECT a.name, 
       SUM(b.absquantity) filter (where b.date < '2018-10-10'::date) as starting_balance,
       SUM(b.absquantity) filter (where in_range and b.movement = 'aa') as aa,
       SUM(b.absquantity) filter (where in_range and b.movement = 'bb') as bb,
       SUM(b.absquantity) filter (where in_range and b.movement = 'cc') as cc,
       SUM(b.absquantity) filter (where in_range and b.movement = 'dd') as dd,
       SUM(b.absquantity) filter (where b.date < '2018-10-12'::date) AS ending_balance
FROM inventory_item a
  LEFT JOIN instances b ON b.fk_item_id = a.uid
GROUP BY a.uid, a.name
ORDER BY a.name;

If you don't want to repeat the date for the starting and ending balance either, you can put the range you want to test for into the CTE, then use Postgres' range functions in the final query:

WITH instances AS (
  SELECT b.ldtc as date, 
         a.fk_item_id, 
         b.movement, 
         a.quantity, 
         CASE 
           WHEN b.movement = 'Inbound' THEN a.quantity 
           ELSE -a.quantity 
         END as absquantity,
         daterange('2018-10-10'::date, '2018-10-12'::date, '()') as check_range
  FROM inventory_resupplylogiteminstance a
    INNER JOIN inventory_resupplylog b ON b.uid = a.fk_resupply_log_id
)
SELECT a.name, 
    SUM(b.absquantity) filter (where b.date < lower(check_range)) as starting_balance,
    SUM(b.absquantity) filter (where b.date <@ b.check_range and b.movement = 'aa') as aa,
    SUM(b.absquantity) filter (where b.date <@ b.check_range and b.movement = 'bb') as bb,
    SUM(b.absquantity) filter (where b.date <@ b.check_range and b.movement = 'cc') as cc,
    SUM(b.absquantity) filter (where b.date <@ b.check_range and b.movement = 'dd') as dd,
    SUM(b.absquantity) filter (where b.date < upper(b.check_range))) AS ending_balance
FROM inventory_item a
  LEFT JOIN instances b ON b.fk_item_id = a.uid
GROUP BY a.uid, a.name
ORDER BY a.name;

daterange('2018-10-10'::date, '2018-10-12'::date, '()') creates a date range where the two dates are excluded.

The <@ operator tests if a date falls into the given range.

So the expression b.date <@ b.check_range is equivalent to b.date > '2018-10-10'::date AND b.date < '2018-10-12'::date (because the range was defined excluding the edges)

Wow, thanks @a_horse_with_no_name, this is so clean! Definitely way better than what I came up with. Question though, I noticed you replaced SUM(CASE WHEN b.date < ('2018-10-10'::date) THEN b.absquantity END) as starting_balance with SUM(b.absquantity) filter (where b.date < lower(check_range)) as starting_balance I realize they do the same thing but I want to know if the latter is more efficient or simply just something you prefer in terms of readability

Collectives™ on Stack Overflow

Postgres filtered WITH query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related