BigQuery使用UNNEST展开数组后如何按歌曲统计收听用户数量？

实现方案 #

你可以直接调整查询语句，把UNNEST放到FROM子句中做关联展开，就能得到每行对应单首歌曲+用户ID的结构，查询语句如下：

SELECT
  user_id,
  JSON_EXTRACT_SCALAR(x, '$.Name') AS Name
FROM data,
UNNEST(JSON_EXTRACT_ARRAY(json_col, '$.info.music')) AS x

这里的逗号是BigQuery中CROSS JOIN UNNEST的简写，会自动将数组中的每一个元素和原表对应的user_id做关联，输出你需要的平级表格结构。

统计每首歌曲的收听用户数 #

不需要单独生成中间表，你可以直接一步完成统计：

SELECT
  JSON_EXTRACT_SCALAR(x, '$.Name') AS Name,
  COUNT(DISTINCT user_id) AS listen_user_count
FROM data,
UNNEST(JSON_EXTRACT_ARRAY(json_col, '$.info.music')) AS x
GROUP BY Name
ORDER BY listen_user_count DESC

基于现有查询结果的统计方法 #

如果你已经生成了当前user_id+歌曲名字段为数组的结果表（假设表名为current_result，数组字段名为NAME），也可以直接对该表的数组做展开统计：

SELECT
  song_name,
  COUNT(DISTINCT user_id) AS listen_user_count
FROM current_result,
UNNEST(NAME) AS song_name
GROUP BY song_name

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