PostgreSQL 统计数组中的元素个数

需求

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

表字段

使用\d+ post查看。

z-blog=# \d+ post
                                                               数据表 "public.post"
      栏位      |           类型           | 校对规则 |  可空的  |       预设        |   存储   | 统计目标 |                 描述
----------------+--------------------------+----------+----------+-------------------+----------+----------+---------------------------------------
 id             | integer                  |          | not null |                   | plain    |          | 文章id
 topics         | text[]                   |          |          |                   | extended |          | 标签数组
索引：
    "post_pkey" PRIMARY KEY, btree (id)
    "gin_index_topics" gin (topics)
访问方法 heap

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;