Google BigQuery 中的多级数据透视

是否可以使用 BigQuery 在一个查询中执行以下数据透视，或者我需要将其分解为多个查询？

这是原始数据：

这是数据透视表：

有没有办法在 BigQuery 中构造任意嵌套的数据透视表？或者每个级别都需要自己的 SQL 查询吗？

请注意，在上面，做一个很简单CASE WHEN6 列组合（CA-M、CA-F、FR-M、FR-F、US-M、US-F）中的每一个的语句，但为了一般情况，我们假设可能有数百个国家，因此在每个组合中手动写入不同的内容是不切实际的CASE陈述。

以下是我心目中最佳的选择

第 1 步 - 根据您的数据准备查询

#standardSQL
WITH country_sex_list AS (
  SELECT Country, Sex 
  FROM yourTable 
  GROUP BY Country, Sex
),
permutations AS (
SELECT 
  STRING_AGG(CONCAT("SUM(CASE WHEN (Country, Sex) = ('", Country, "', '", Sex, "') THEN Income END) AS ", Country, "_", Sex), ',' ORDER BY Country, Sex) AS text
FROM country_sex_list 
)
SELECT 
  CONCAT(
    "SELECT company, ", text, ", SUM(Income) AS Total FROM yourTable GROUP BY Company UNION ALL ",
    "SELECT 'Total' as company, ", text, ", SUM(Income) AS Total FROM yourTable"
  ) AS query
FROM permutations  

步骤 2 - 获取步骤 1 结果的文本并将其作为查询运行。
结果将如您所料（参见下面的示例）

company   CA_M    FR_F    FR_M    US_F    US_M    Total  
Acme      null  40,000    null    null  40,000   80,000  
Bravo   50,000    null    null  30,000    null   80,000  
Delta     null    null  40,000    null    null   40,000  
Total   50,000  40,000  40,000  30,000  40,000  200,000   

我认为这两个步骤足够通用，可以扩展到实际用例

当然，您可以在 Web UI 中手动运行这两个步骤，也可以将它们编写为脚本client你的选择

下面是用于测试的虚拟数据

WITH yourTable AS (
  SELECT 'M' AS Sex, 'US' AS Country, 40000 AS Income, 'Acme' AS Company UNION ALL
  SELECT 'M', 'CA', 50000, 'Bravo' UNION ALL
  SELECT 'F', 'US', 30000, 'Bravo' UNION ALL
  SELECT 'F', 'FR', 40000, 'Acme' UNION ALL
  SELECT 'M', 'FR', 40000, 'Delta'
)