我有一列文本[]。如何获取列中所有对象的频率计数?
例子:
col_a
--------
{a, b}
{a}
{b}
{a}
输出应该是:
col_a | count
----------------
a | 3
b | 2
我的查询:
with all_tags as (
select array_agg(c)
from (
select unnest(tags)
from message_tags
) as dt(c)
)
select count(*) from all_tags;
弄清楚了:
-- Collapse all tags into one array
with all_tags as (
select array_agg(c) as arr
from (
select unnest(ner_tags)
from message_tags
) as dt(c)
),
-- Expand single array into a row per tag
row_tags as (
select unnest(arr) as tags from all_tags
)
-- count distinct tags
select tags, count(*) from row_tags group by tags
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)