python - 按列分组以在 Postgresql 中获取数组结果

Question

我有一个名为 moviegenre 的表，它看起来像:

moviegenre:
- movie (FK movie.id)
- genre (FK genre.id)

我有一个查询(ORM 生成的)，它返回所有 movie.imdb 和 genre.id 的 genre.id' s 与给定的 movie.imdb_id 相同。

SELECT "movie"."imdb_id", 
       "moviegenre"."genre_id" 
FROM   "moviegenre" 
       INNER JOIN "movie" 
               ON ( "moviegenre"."movie_id" = "movie"."id" ) 
WHERE  ( "movie"."imdb_id" IN (SELECT U0."imdb_id" 
                               FROM   "movie" U0 
                                      INNER JOIN "moviegenre" U1 
                                              ON ( U0."id" = U1."movie_id" ) 
                               WHERE  ( U0."last_ingested_on" IS NOT NULL 
                                        AND NOT ( U0."imdb_id" IN 
                                                  ( 'tt0169547' ) ) 
                                        AND NOT ( U0."imdb_id" IN 
                                                  ( 'tt0169547' ) ) 
                                        AND U1."genre_id" IN ( 2, 10 ) )) 
         AND "moviegenre"."genre_id" IN ( 2, 10 ) ) 

问题是我会得到以下格式的结果:

[
  ('imdbid22`, 'genreid1'),
  ('imdbid22`, 'genreid2'),
  ('imdbid44`, 'genreid1'),
  ('imdbid55`, 'genreid8'),
]

在查询本身中是否有一种方法可以将所有类型 ID 分组到 movie.imdb_id 下的列表中？我想在查询中进行分组。
目前在我的 Web 应用程序代码 (Python) 中执行此操作，当返回 50k+ 行时速度非常慢。

[
  ('imdbid22`, ['genreid1', 'genreid2']),
  ('imdbid44`, 'genreid1'),
  ('imdbid55`, 'genreid8'),
]

提前致谢!

编辑:

这是针对当前结果运行的 python 代码

结果列表 = []

for item in movies_and_genres:
    genres_in_common = len(set([
        i['genre__id'] for i in movies_and_genres
        if i['movie__imdb_id'] == item['movie__imdb_id']
    ]))
    imdb_id = item['movie__imdb_id']

    if genres_in_common >= min_in_comon:
        result_item = {
            'movie.imdb_id': imdb_id,
            'count': genres_in_common
        }
        if result_item not in results_list:
            results_list.append(result_item)

return results_list

Answer 1

select m.imdb_id, array_agg(g.genre_id) as genre_id
from
    moviegenre g
    inner join 
    movie m on g.movie_id = m.id
where 
    m.last_ingested_on is not null 
    and not m.imdb_id in ('tt0169547')  
    and not m.imdb_id in ('tt0169547')
    and g.genre_id in (2, 10) 
group by m.imdb_id

array_agg 将创建一个包含特定 imdb_id 的所有 genre_ids 的数组:

http://www.postgresql.org/docs/current/interactive/functions-aggregate.html#FUNCTIONS-AGGREGATE-TABLE