如何统计PostgreSQL数组字段中各元素的出现次数？

要搞定这个需求，核心思路就是先把数组拆成单独的行，再做分组统计。PostgreSQL自带的unnest()函数正好能帮我们把数组展开，配合常规的分组聚合就能得到你要的结果。

完整查询语句 #

SELECT
    unnest(ct) AS name,
    COUNT(*) AS value
FROM public.cts
GROUP BY name
ORDER BY value DESC;

查询结果验证 #

执行上面的SQL后，会直接输出你期望的格式：

name  | value
-------+-------
ct286 |     3
ct281 |     3
ct277 |     1

步骤拆解说明 #

1. unnest(ct)：把ct字段的数组拆分成独立行，比如原表中id=31的{ct286,ct277,ct281}会被拆成3条单独的记录，每条对应一个数组元素。
2. GROUP BY name：按拆分后的元素名称分组，把相同元素的记录归到一起。
3. COUNT(*) AS value：统计每组的记录数，也就是对应元素的出现次数。
4. ORDER BY value DESC：可选步骤，按次数从多到少排序，让结果更直观。

扩展：限定日期范围统计（可选） #

如果需要只统计某段时间内的元素次数，加个WHERE条件就行，比如统计2015年1月24日至25日的数据：

SELECT
    unnest(ct) AS name,
    COUNT(*) AS value
FROM public.cts
WHERE day BETWEEN '2015-01-24' AND '2015-01-25'
GROUP BY name
ORDER BY value DESC;

内容的提问来源于stack exchange，提问作者aseques