0

I have a database table with:

id | date       | position | name
--------------------------------------
1  | 2016-06-29 | 9        | Ben Smith
2  | 2016-06-29 | 1        | Ben Smith
3  | 2016-06-29 | 5        | Ben Smith
4  | 2016-06-29 | 6        | Ben Smith
5  | 2016-06-30 | 2        | Ben Smith
6  | 2016-06-30 | 2        | Tom Brown
7  | 2016-06-29 | 4        | Tom Brown
8  | 2016-06-30 | 2        | Tom Brown
9  | 2016-06-30 | 1        | Tom Brown

How can I query the table efficiently so that I can get a new column using array_agg().

I have already tried the following query however its incredibly slow and also wrong as it doesn't group the previous_positions by the name column:

SELECT 
j.*,
(SELECT array_agg(id) FROM jockeys j2 WHERE j2.id < j.id)
FROM jockeys j

I expect the table output to look like this

id | date       | position | name      | previous_positions
----------------------------------------------------------
1  | 2016-06-29 | 9        | Ben Smith | {}
2  | 2016-06-29 | 1        | Ben Smith | {9}
3  | 2016-06-29 | 5        | Ben Smith | {9,1}
4  | 2016-06-29 | 6        | Ben Smith | {9,1,5}
5  | 2016-06-30 | 2        | Ben Smith | {9,1,5,6}
6  | 2016-06-30 | 2        | Tom Brown | {}
7  | 2016-06-29 | 4        | Tom Brown | {2}
8  | 2016-06-30 | 2        | Tom Brown | {2,4}
9  | 2016-06-30 | 1        | Tom Brown | {2,4,2}
Luke Byrne
  • 109
  • 7

1 Answers1

0

You may use the WINDOW clause for array_agg

SELECT 
j.* ,  array_agg(position) over w as previous_positions
FROM jockeys j
WINDOW w as
(  partition by name ORDER BY id rows between 
     unbounded preceding and 1 preceding
     )

DEMO

Kaushik Nayak
  • 30,772
  • 5
  • 32
  • 45
  • How would I also get the median of the previous_positions? For all, last_20, last_50 etc. I have been trying it on the created arrays but its really slow, was wondering whether it would be easier to calc these at the same time as the grouping? – Luke Byrne May 07 '19 at 03:07
  • @LukeByrne : Please ask a new question. – Kaushik Nayak May 07 '19 at 03:08
  • @KaushikNakyak will do – Luke Byrne May 07 '19 at 03:09
  • @KaushikNakyak Posted update [here](https://stackoverflow.com/questions/56015072/query-table-with-array-agg-median-of-all-previous-positions-last-10-last-50-e) – Luke Byrne May 07 '19 at 03:20
  • @KaushikNakyak I have new question [here](https://stackoverflow.com/questions/57354543/query-table-with-sum-of-all-previous-positions-excluding-current-position) if you are able to help? – Luke Byrne Aug 05 '19 at 08:08