查询表与以前的所有位置的ARRAY_AGG，不包括当前位置(Query table with arr

我有一个数据库表：

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

我怎样才能有效地查询表，这样我可以得到一个新的使用列ARRAY_AGG（）。

我已经尝试下面的查询但其令人难以置信的缓慢，也是错误的，因为它没有GROUP BY的名字列previous_positions：

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

我期望表输出看起来像这样

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}

Answer 1:

您可以使用WINDOW的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

文章来源: Query table with array_agg of ALL previous positions, excluding current position