PostgreSQL对数组元素聚合基本方法示例


    目录
  • 前言
  • 表 jounal 结构示例
  • sql 语句

    前言
  • 本文对 PostgreSQL 14.x 适用
  • 实现的方式是用 unnest 将

    表 jounal 结构示例
    
id      author
 2    [张三, 李四, 王五]
 2    [张三, 李四]
 3    [张三]

    聚合结果
    
    item  cnt
    张三    3
    李四    2
    王五    1
    sql 语句
    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
相关文章！