I have a table which looks like this:

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:

Is there any good way to do it with PostgresSQL?

CodePudding user response：

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