Postgres：如何按聚合類型限制查詢結果的數量

考慮下表定義

CREATE TABLE associations (
   id SERIAL PRIMARY KEY,
   resource_id integer,
   target_id integer,
   relation_id integer
);

它用於通過關係指定資源與目標的關聯：

296 10  49  4 <--
297 10  50  4 <--
298 10  51  4 <--
299 10  52  4
300 10  53  4
301 10  54  4
302 10  55  4
303 10  56  5 <--
304 10  57  5 <--
305 10  58  7
306 10  59  7
307 10  60  7
308 10  61  7
309 10  62  7
310 10  64  8 <--
316 11  80  8 <--
320 11  81  8 <--
321 11  82  8 <--
322 11  83  8

• 資源 10 與目標相關聯$$ 49, 50, 51, 52, 53, 54, 55 $$按關係 4
• 資源 10 也與目標相關聯$$ 56, 57 $$按關係 5

如何查詢關聯表以獲取特定關係的資源目標（例如 4、5 和 8），同時限制每個關係返回的目標數量？

例如，如果我們將關係 4、5 和 8 上的目標數量限制為 3，我們應該得到以下結果：

10 | 49, 50, 51 | 4
10 |     56, 57 | 5
10 |         64 | 8
11 | 80, 81, 82 | 8

謝謝你。

WITH 
cte AS ( SELECT resource_id, 
               target_id,
               relation_id, 
               ROW_NUMBER() OVER ( PARTITION BY resource_id, relation_id 
                                   ORDER BY id ) rn
        FROM associations
        WHERE relation_id IN (4, 5, 8) )
SELECT resource_id, STRING_AGG(target_id::TEXT, ','::TEXT), relation_id
FROM cte
WHERE rn <= 3
GROUP BY resource_id, relation_id

小提琴

引用自：https://dba.stackexchange.com/questions/256738