PostgreSQL 對陣列元素聚合（qbit）

前言

• 本文對 PostgreSQL 14.x 適用
• 實現的方式是用 unnest 將

正文

• 表 jounal 結構示例

   id      author
   2    [張三, 李四, 王五]
   2    [張三, 李四]
   3    [張三]

• 聚合結果

   item  cnt
  張三    3
  李四    2
  王五    1

• sql 語句寫法一

  select item, count(item) as cnt
  from (select unnest(journal.author) as item from journal) as tmp
  group by item

• sql 語句寫法二

  select item, count(distinct id) as cnt
  from journal, unnest(journal.author) as item
  group by item

• sql 語句寫法三

  select item, count(item) as cnt
  from journal, unnest(journal.author) as item 
  group by item

相關連結

• How to group result by array column in Postgres?
• Group by unique items in an array column and count records that contain each item

本文出自 qbit snap