如何在BigQuery中对两列进行Pivot并获取聚合数据

原始数据表包含event_date、country_code、platform、user_id字段，示例数据如下：

需求是按日期聚合统计，具体要求：

• 国家维度：仅统计UK、FR、ES，以及非UK的情况
• 平台维度：区分ios和android
• 单独统计各平台的总安装量（不含国家组合）

期望输出表结构：

已尝试使用PIVOT语句，但只能获取部分国家组合的统计结果，不知道如何处理non-uk场景和仅按平台统计的需求：

SELECT * FROM
(
  SELECT 
    event_date,
    platform,
    country_code
  FROM my_table
)
PIVOT
(
  COUNT(*) AS count
  FOR LOWER(country_code) IN ('uk', 'fr', 'ie')
)
ORDER BY event_date DESC;

可以使用条件聚合来实现所有需求，这种方式比单纯的PIVOT更灵活，能同时覆盖平台总统计、指定国家以及非UK的场景：

SELECT
  event_date,
  -- 各平台总安装量
  COUNT(CASE WHEN platform = 'ios' THEN user_id END) AS count_ios,
  COUNT(CASE WHEN platform = 'android' THEN user_id END) AS count_android,
  
  -- iOS各国家/地区统计
  COUNT(CASE WHEN platform = 'ios' AND country_code != 'UK' THEN user_id END) AS count_ios_non_uk,
  COUNT(CASE WHEN platform = 'ios' AND country_code = 'UK' THEN user_id END) AS count_ios_uk,
  COUNT(CASE WHEN platform = 'ios' AND country_code = 'FR' THEN user_id END) AS count_ios_fr,
  COUNT(CASE WHEN platform = 'ios' AND country_code = 'ES' THEN user_id END) AS count_ios_es,
  
  -- Android各国家/地区统计
  COUNT(CASE WHEN platform = 'android' AND country_code != 'UK' THEN user_id END) AS count_android_non_uk,
  COUNT(CASE WHEN platform = 'android' AND country_code = 'UK' THEN user_id END) AS count_android_uk,
  COUNT(CASE WHEN platform = 'android' AND country_code = 'FR' THEN user_id END) AS count_android_fr,
  COUNT(CASE WHEN platform = 'android' AND country_code = 'ES' THEN user_id END) AS count_android_es
FROM my_table
GROUP BY event_date
ORDER BY event_date DESC;

说明： #

1. 平台总统计：通过CASE筛选对应平台，COUNT会忽略NULL值，不符合条件的CASE返回NULL，不会被计入统计。
2. 非UK场景：在CASE中加入country_code != 'UK'的条件，统计所有非英国的用户。
3. 指定国家统计：直接匹配country_code为UK、FR、ES的情况，精准统计对应区域的用户量。
4. 最终按event_date分组，确保每天的统计结果独立。

如果坚持使用PIVOT，需要先在子查询中构造平台+国家的组合标识，同时单独保留平台总标识，但条件聚合的写法更直观易维护，能快速覆盖所有需求场景。

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