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

问题描述

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

以下是原始数据：

这里是枢轴表：

有没有一种方法可以构建一个任意的 - 在BigQuery中嵌套数据透视表？或者每个级别都需要自己的SQL Query？

请注意，在上面，对于6列中的每一列，执行 CASE WHEN 语句都很简单组合（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国家/地区，性别
），
置换AS（
SELECT
STRING_AGG（CONCAT（SUM（CASE WHEN（Country，Sex）='''，Country，''，' ，性别，）收入完）作为，国家，_，性别），'，'按国家，性别划分的文本。
CONCAT（
SELECT company，text，，SUM（Income）AS FROM yourTable GROUP BY Company UNION ALL，
SELECT'Total'as company，text ，SUM（Income）AS FROM yourTable
）AS查询
FROM置换


第2步 - 获取第1步的结果文本并将其作为查询运行。

结果将如您期望的那样（请参阅下面的示例）
$ b $ 公司CA_M FR_F FR_M US_F US_M总计
Acme null 40,000 null null 40,000 80,000
Bravo 50,000 null null 30,000 null 80,000
Delta空null 40,000 null null 40,000
共计50,000 50,000 40,000 30,000 40,000 200,000


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

当然，您可以在Web UI中手动运行这两个步骤，也可以在<一个href =https://cloud.google.com/bigquery/docs/reference/libraries =nofollow noreferrer>您选择的客户端

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

WITH yourTable AS（
SELECT'M' AS性别，'US'AS国家，40000 AS收入，'Acme'AS公司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'
）


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

Here is the raw data:

And here is the Pivot Table:

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

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

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  

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

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屋！