BigQuery如何统计列内以逗号分隔的所有唯一值的数量

核心实现逻辑 #

先将每个单元格内逗号分隔的多值拆分为独立行，再对拆分后的值进行去重统计即可，实现代码如下：

1. 统计全局唯一值的总数量 #

SELECT COUNT(DISTINCT TRIM(value)) AS unique_value_total
FROM `你的表完整路径`,
UNNEST(SPLIT(待统计的列名, ',')) AS value

2. 输出每个唯一值及对应出现频次（匹配期望结果格式） #

SELECT
  TRIM(value) AS unique_value,
  COUNT(*) AS occurrence_times
FROM `你的表完整路径`,
UNNEST(SPLIT(待统计的列名, ',')) AS value
GROUP BY unique_value
ORDER BY occurrence_times DESC

函数说明 #

• SPLIT(列名, ',')：将目标列的字符串按逗号分割为数组格式
• UNNEST(数组)：将数组展开为多行，每行对应数组中的一个元素
• TRIM(value)：可选工具函数，用于清理值前后可能存在的多余空格，避免同一值因空格差异被判定为不同
• COUNT(DISTINCT 值)：对展开后的值去重后统计总数量
• 统计频次时通过GROUP BY对拆分后的值分组聚合即可

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