求BigQuery SQL语句：按特殊分隔符拆分元素并统计频次

针对你需要统计以^G（ASCII分组分隔符，对应字符码29）分隔的元素出现次数的需求，可以用以下BigQuery SQL语句实现：

WITH split_data AS (
  SELECT 
    UNNEST(SPLIT(origin_column, CHR(29))) AS element
  FROM 
    `your-project.your-dataset.your-table`
)
SELECT 
  element,
  COUNT(*) AS count
FROM split_data
WHERE element != '' -- 过滤拆分后可能出现的空字符串
GROUP BY element
ORDER BY count DESC, element;

代码说明： #

• CHR(29)：对应^G控制字符，直接用字符码转换能避免输入特殊字符时的异常
• SPLIT(origin_column, CHR(29))：将原始列按指定分隔符拆分为数组
• UNNEST(...)：把数组中的每个元素转为独立行，为后续统计做准备
• 最后通过GROUP BY分组、COUNT(*)统计次数，ORDER BY让结果按次数降序、元素升序排列，和示例输出一致

示例验证： #

输入表数据：

origin_column
a^Gb^Gc  
a^Gc  
a^Gb^Gd

执行SQL后输出：

element | count  
a | 3  
b | 2  
c | 2  
d | 1

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