pivot - BigQuery 透视数据行列

Question

这个问题在这里已经有了答案:





How to Pivot table in BigQuery

(7 个回答)


去年关闭。




我目前在 BigQuery 中处理数据，然后导出到 Excel 中以制作最终的数据透视表，并希望能够使用 BigQuery 中的 PIVOT 选项创建相同的数据。

我在大查询中的数据集看起来像

Transaction_Month || ConsumerId || CUST_createdMonth
01/01/2015        || 1          || 01/01/2015
01/01/2015        || 1          || 01/01/2015
01/02/2015        || 1          || 01/01/2015
01/01/2015        || 2          || 01/01/2015
01/02/2015        || 3          || 01/02/2015
01/02/2015        || 4          || 01/02/2015
01/02/2015        || 5          || 01/02/2015
01/03/2015        || 5          || 01/02/2015
01/03/2015        || 6          || 01/03/2015
01/04/2015        || 6          || 01/03/2015
01/06/2015        || 6          || 01/03/2015
01/03/2015        || 7          || 01/03/2015
01/04/2015        || 8          || 01/04/2015
01/05/2015        || 8          || 01/04/2015
01/04/2015        || 9          || 01/04/2015

它本质上是一个附加了客户信息的订单表。

当我将此数据放入 excel 时，我将其添加到数据透视表中，将 CUST_createdMonth 添加为行，将 Transaction_Month 添加为列，该值是 ConsumerID 的不同计数

输出如下所示


BigQuery 中是否可以进行这种支点？

Answer 1

在 BigQuery 中没有很好的方法可以做到这一点，但是您可以按照以下想法进行操作

Step 1

运行以下查询

SELECT 'SELECT CUST_createdMonth, ' + 
   GROUP_CONCAT_UNQUOTED(
      'EXACT_COUNT_DISTINCT(IF(Transaction_Month = "' + Transaction_Month + '", ConsumerId, NULL)) as [m_' + REPLACE(Transaction_Month, '/', '_') + ']'
   ) 
   + ' FROM yourTable GROUP BY CUST_createdMonth ORDER BY CUST_createdMonth'
FROM (
  SELECT Transaction_Month 
  FROM yourTable
  GROUP BY Transaction_Month
  ORDER BY Transaction_Month
) 

结果 - 您将获得如下字符串(为了便于阅读，其格式如下)

SELECT
  CUST_createdMonth,
  EXACT_COUNT_DISTINCT(IF(Transaction_Month = "01/01/2015", ConsumerId, NULL)) AS [m_01_01_2015],
  EXACT_COUNT_DISTINCT(IF(Transaction_Month = "01/02/2015", ConsumerId, NULL)) AS [m_01_02_2015],
  EXACT_COUNT_DISTINCT(IF(Transaction_Month = "01/03/2015", ConsumerId, NULL)) AS [m_01_03_2015],
  EXACT_COUNT_DISTINCT(IF(Transaction_Month = "01/04/2015", ConsumerId, NULL)) AS [m_01_04_2015],
  EXACT_COUNT_DISTINCT(IF(Transaction_Month = "01/05/2015", ConsumerId, NULL)) AS [m_01_05_2015],
  EXACT_COUNT_DISTINCT(IF(Transaction_Month = "01/06/2015", ConsumerId, NULL)) AS [m_01_06_2015]
  FROM yourTable 
GROUP BY
  CUST_createdMonth
ORDER BY
  CUST_createdMonth

Step 2

只需在组合查询上方运行

结果将如下所示

CUST_createdMonth   m_01_01_2015    m_01_02_2015    m_01_03_2015    m_01_04_2015    m_01_05_2015    m_01_06_2015     
01/01/2015          2               1               0               0               0               0    
01/02/2015          0               3               1               0               0               0    
01/03/2015          0               0               2               1               0               1    
01/04/2015          0               0               0               2               1               0   

Note

如果您有几个月的时间来处理太多的手动工作，则第 1 步会很有帮助。
在这种情况下 - 第 1 步可帮助您生成查询

You can see more about pivoting in my other posts.

How to scale Pivoting in BigQuery?
请注意 - 每个表有 10K 列的限制 - 因此您只能使用 10K 个组织。
您还可以在下面查看简化示例(如果上面的示例过于复杂/冗长):
How to transpose rows to columns with large amount of the data in BigQuery/SQL?
How to create dummy variable columns for thousands of categories in Google BigQuery?
Pivot Repeated fields in BigQuery