PostgreSQL统计数组中的元素个数

需求

统计文章表中的标签数组字段中的不同分类个数

unnest

PostgreSQL提供了unnest关键词，将数组拆解为一条条记录（不去重）。

select unnest(topics) as unnest_topic from post;

输出

z-blog=# select unnest(topics) as unnest_topic from post;
 unnest_topic
--------------
 Java
 Linux
 OpenResty
 Linux
 SEO
(5 行记录)

统计分类个数

select count(1) as topic_count, unnest(topics) unnest_topic from post group by unnest_topic order by topic_count desc

输出

z-blog=# select count(1) as topic_count, unnest(topics) unnest_topic from post group by unnest_topic order by topic_count desc;
 topic_count |   unnest_topic
-------------+----------------
          80 | PostgreSQL
          77 | Linux
          73 | OpenResty
          69 | Java
          59 | MySQL
(5 行记录)

去重分类分页查询

分类总数

select count(distinct unnest_topic) as unnest_topic_count from post, unnest(topics) as unnest_topic;

分类分页

select distinct unnest(topics) as unnest_topic from post order by unnest_topic limit 10 offset 30;