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