SQL: join last 3 values with matching key to single row

user_16703301 0
Viewed 47

I have a table which looks like this:

id date user value
1 2022-07-13 16:42:33.1233 1 '123'
2 2022-07-13 16:41:33.1233 1 '12345'
3 2022-07-13 16:40:33.1233 1 '12'
4 2022-07-13 16:39:33.1233 1 '12365'
5 2022-07-13 16:37:33.1233 2 '123234'
6 2022-07-13 16:36:33.1233 2 '123'
7 2022-07-13 16:35:33.1233 2 '1234'
8 2022-07-13 16:34:33.1233 2 '3234'
9 2022-07-13 16:33:33.1233 3 '1234'

I want to get a table with each row of this one and it's user's last(by date) 3 values with a single request. Resulting table will look like that:

id date user value last values
1 2022-07-13 16:42:33.1233 1 '123' '12345', '12', '12365'
2 2022-07-13 16:41:33.1233 1 '12345' '12', '12365'
3 2022-07-13 16:40:33.1233 1 '12' '12365'
4 2022-07-13 16:39:33.1233 1 '12365'
5 2022-07-13 16:37:33.1233 2 '123234' '123', '1234', '3234'
... ... ... ... ...

Is there any good way to do it with PostgresSQL?

postgresql sql
1 Answers

Use array_agg as a window function (in ROWS mode) and set the window to 3 PRECEDING and 1 PRECEDING, partitioned by user and ordered by date:

SELECT *, array_agg(value) OVER w
FROM t
WINDOW w AS (PARTITION BY user_id ORDER BY date
             ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING)
ORDER BY id, date;


 id |           date           | user_id | value  |    array_agg     
----+--------------------------+---------+--------+------------------
  1 | 2022-07-13 16:42:33.1233 |       1 | 123    | {12365,12,12345}
  2 | 2022-07-13 16:41:33.1233 |       1 | 12345  | {12365,12}
  3 | 2022-07-13 16:40:33.1233 |       1 | 12     | {12365}
  4 | 2022-07-13 16:39:33.1233 |       1 | 12365  | 
  5 | 2022-07-13 16:37:33.1233 |       2 | 123234 | {3234,1234,123}
  6 | 2022-07-13 16:36:33.1233 |       2 | 123    | {3234,1234}
  7 | 2022-07-13 16:35:33.1233 |       2 | 1234   | {3234}
  8 | 2022-07-13 16:34:33.1233 |       2 | 3234   | 
  9 | 2022-07-13 16:33:33.1233 |       3 | 1234   | 
(9 rows)

Demo: db<>fiddle

user_22753880
Related