Athena中如何提取并统计payload字段内所有数组键及其出现次数

实现方案 #

你当前可直接用payload.keyname提取字段值，说明所用数据库支持半结构化数据（结构体/JSON类型）查询，核心实现逻辑是先把每行payload的所有键转为数组，再把数组拆为多行，最后分组统计即可，不同主流数据库的写法如下：

Spark SQL / Databricks 写法 #

SELECT 
  key,
  COUNT(*) AS Count
FROM 
  Database
  LATERAL VIEW EXPLODE(MAP_KEYS(payload)) AS key
-- 此处添加日期过滤条件，例如 WHERE dt >= '2024-01-01' AND dt <= '2024-01-31'
GROUP BY 
  key
ORDER BY 
  Count DESC

BigQuery 写法 #

SELECT 
  key,
  COUNT(*) AS Count
FROM 
  `你的项目名.你的数据集名.Database`,
  UNNEST(JSON_KEYS(payload)) AS key
-- 此处添加日期过滤条件，例如 WHERE date_column BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY 
  key
ORDER BY 
  Count DESC

Snowflake 写法 #

SELECT 
  KEY AS Key,
  COUNT(*) AS Count
FROM 
  Database,
  LATERAL FLATTEN(input => OBJECT_KEYS(payload))
-- 此处添加日期过滤条件，例如 WHERE date_column BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY 
  KEY
ORDER BY 
  Count DESC

提示：你示例期望输出里的game应为笔误，匹配你给出的示例数据统计出来的对应键是gameid，计数为3。

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