sql - postgresql 选择查询

Question

create temp table tmp_apps (
  id integer
);

create temp table tmp_pos (
     tmp_apps_id integer,
     position integer
);

insert into tmp_apps
select 1 id union
select 2 id 
;

insert into tmp_pos (tmp_apps_id, position)
select 1 tmp_apps_id, 1 as position union all
select 1 tmp_apps_id, 1 as position union all
select 1 tmp_apps_id, 2 as position union all
select 1 tmp_apps_id, 3 as position union all
select 1 tmp_apps_id, 3 as position union all
select 2 tmp_apps_id, 1 as position
;
/*
Expected result:
tmp_apps_id tmp_pos_position
1           1,2
2           1
*/

如何为每个 tmp_apps.id 获取前 2 个逗号分隔的、不同的 tmp_pos.position
有可能吗？

Answer 1

WITH x AS (
    SELECT tmp_apps_id
         , position
         , row_number() OVER (PARTITION BY tmp_apps_id ORDER BY position) AS rn
    FROM   tmp_pos
    GROUP  BY 1, 2
    ORDER  BY 1, 2
    )
SELECT tmp_apps_id, string_agg(position::text, ', ')
FROM   x
WHERE  rn < 3
GROUP  BY 1;

这恰好很像@araqnid 发布的比我快一点的解决方案。
CTE或子查询，这只是在这种情况下执行相同操作的两种方法。

我的版本在一个重要方面有所不同:
通过使用 GROUP BY 而不是 DISTINCT 要获得不同的值，您可以应用 window function row_number() (解决方案的关键要素)在同一查询级别，不需要另一个子查询(或 CTE)。

原因是聚合(GROUP BY)在窗口函数之前应用，而DISTINCT在之后应用。在很多情况下DISTINCT和 GROUP BY提供同样好的解决方案。在这种情况下，如果您知道的话，可以很好地利用细微差别。我希望这会快很多。