Блог

Мне нужно создать СВОДНУЮ таблицу из исходного кода, подобную этой таблице

 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
 

Нужен такой свод, где кол-во продукта — это кол-во по последней дате

 UserID  A   B
11      400 200
22      200 100
 

Моя попытка 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
 

И результат

 UserID  A   B
11     600  500
22     1000 300
 

Я имею в виду, что я получаю результат по максимальному количеству, а не по максимальной дате!
Что мне нужно добавить, чтобы получить результаты к максимальной (последней) дате??

В Postgres нет «первых» и «последних» функций агрегирования. Один из способов сделать это (без подзапроса) использует массивы:

 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;
 

Другой метод использует select distinct с 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;
 

Однако с соответствующими индексами это distinct on может быть самый быстрый подход:

 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;
 

В частности, для этого может использоваться индекс on userid, product, date desc) . Улучшение производительности будет наиболее заметным, если для данного пользователя будет много дат.

Вы можете использовать DENSE_RANK() функцию window для фильтрации по последней дате для каждого продукта и идентификатора пользователя перед применением условной агрегации, например

 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

предполагается, что все значения даты различны (для дат не происходит привязки)