sql - 获取最流行的值转化为数组类型

Question

我有这样一张表:

               Table "public.items_tags_array"
 Column  |       Type        |           Modifiers           
---------+-------------------+-------------------------------
 item_id | character varying | not null
 tags    | text[]            | not null default '{}'::text[]
Foreign-key constraints:
    "items_tags_array_item_id_fkey" FOREIGN KEY (item_id) REFERENCES items(item_id)

tags字段包含数组格式的多个元素:

   item_id    |       tags       
--------------+------------------
 8eb6a7d9558c | {test,home,sun}
 3779a64d1bf9 | {test2,home,fam}

如何构建查询以获取最流行的标签(单个值)？

Answer 1

使用unnest():

select tag, count(tag)
from items_tags_array
cross join unnest(tags) as tag
group by 1
order by 2 desc