PostgreSQL 统计数组中的元素个数
PostgreSQL About 1,607 words需求
统计文章表中的标签数组字段中的不同分类个数。
表字段
使用\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;
Views: 7,664 · Posted: 2020-03-26
————        END        ————
Give me a Star, Thanks:)
https://github.com/fendoudebb/LiteNote扫描下方二维码关注公众号和小程序↓↓↓
Loading...