Google BigQuery 中的多级数据透视 [英] Multi-level pivot in Google BigQuery

问题描述

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

Is it possible to do the following pivot in one query using BigQuery, or would I need to break it up into multiple queries?

这是原始数据:

这里是数据透视表:

有没有办法在 BigQuery 中构建任意嵌套的数据透视表?还是每个级别都需要自己的 SQL 查询?

Is there a way to construct an arbitrarily-nested Pivot Table in BigQuery? Or does each level need its own SQL Query?

注意，在上面，对 6 个列组合(CA-M、CA-F、FR-M、FR-F、US-M、US-F)，但为了一般情况，我们假设可能有数百个国家/地区，因此手动将每个组合编写为不同的 CASE 语句是不切实际的.

Note, in the above, it would be simple to do a CASE WHEN statement for each of the 6 column combinations (CA-M, CA-F, FR-M, FR-F, US-M, US-F), but for the sake of the general case, let's suppose there may be hundreds of countries so it's not practical to manually write in each combination as a different CASE statement.

推荐答案

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

Below is most optimal in my mind option

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

Step 1 - prepare query based on your data

#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 步结果的文本并将其作为查询运行.
结果会如你所愿(见下面的例子)

Step 2 - take text of result of Step 1 and run it as a query.
Result will be as you expect (see example below)

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   

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

I think these two steps are generic enough to extend to real use-case

当然，您可以在 Web UI 中手动运行这两个步骤，也可以在 客户 您选择的

Of course, You can run those two steps manually in Web UI or you can script them in client of your choice

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

Below is dummy data to test with

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'
)

这篇关于Google BigQuery 中的多级数据透视的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！