sql - 一列多列上的 array_agg

Question

我目前有一个这样的示例标记表:

id | tag    | user_id | video_id
 1 | cake   |       1 |        1
 2 | cookie |       1 |        2
 3 | crab   |       2 |        2
 4 | carrot |       2 |        2

我想按 video_id 将最近的 10-20 行分组，其他列作为数组:

video_id 1, has tag "cake", user_id 1
video_id 2, has tags "cookie", "cake", "crab", by user_id 1, user_id 2

Answer 1

像这样的东西应该可以工作

SELECT video_id, array_agg(tag) as tags, array_agg(user_id) as user_ids
FROM tags
ORDER BY id
GROUP BY video_id
LIMIT 10