CODEWITHSUNDEEP
sqlpostgresqlpivotpivot-table
Galat
Galat

Reputation: 477

PostgreSQL Pivot by Last Date

I need to make a PIVOT table from Source like this table

FactID  UserID  Date    Product QTY
1         11    01/01/2020  A   600
2         11    02/01/2020  A   400
3         11    03/01/2020  B   500
4         11    04/01/2020  B   200
6         22    06/01/2020  A   1000
7         22    07/01/2020  A   200
8         22    08/01/2020  B   300
9         22    09/01/2020  B   100

Need Pivot Like this where Product QTY is QTY by Last Date

UserID  A   B
11      400 200
22      200 100

My try PostgreSQL

Select 
        UserID,
        MAX(CASE WHEN Product='A' THEN 'QTY' END) AS 'A',
        MAX(CASE WHEN Product='B' THEN 'QTY' END) AS 'B'
FROM table
GROUP BY UserID

And Result

UserID  A   B
11     600  500
22     1000 300

I mean I get a result by the maximum QTY and not by the maximum date! What do I need to add to get results by the maximum (last) date ??

Upvotes: 0

Views: 227

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269933

Postgres doesn't have "first" and "last" aggregation functions. One method for doing this (without a subquery) uses arrays:

select userid,
       (array_agg(qty order by date desc) filter (where product = 'A'))[1] as a,
       (array_agg(qty order by date desc) filter (where product = 'B'))[1] as b
from tab
group by userid;

Another method uses select distinct with first_value():

select distinct userid,
       first_value(qty) over (partition by userid order by product = 'A' desc, date desc) as a,
       first_value(qty) over (partition by userid order by product = 'B' desc, date desc) as b
from tab;

With the appropriate indexes, though, distinct on might be the fastest approach:

select userid,
       max(qty) filter (where product = 'A') as a,
       max(qty) filter (where product = 'B') as b
from (select distinct on (userid, product) t.*
      from tab t
      order by userid, product, date desc
     ) t
group by userid;

In particular, this can use an index on userid, product, date desc). The improvement in performance will be most notable if there are many dates for a given user.

Upvotes: 1

Barbaros Özhan
Barbaros Özhan

Reputation: 65288

You can use DENSE_RANK() window function in order to filter by the last date per each product and UserID before applying conditional aggregation such as

SELECT UserID, 
       MAX(CASE WHEN Product='A' THEN QTY END) AS "A",
       MAX(CASE WHEN Product='B' THEN QTY END) AS "B"
  FROM
  (
    SELECT t.*, DENSE_RANK() OVER (PARTITION BY Product,UserID ORDER BY Date DESC) AS rn
      FROM tab t     
  ) q
 WHERE rn = 1 
 GROUP BY UserID

Demo

presuming all date values are distinct(no ties occur for dates)

Upvotes: 1

Related Questions