如何在BigQuery中将多行汇总为一行？

小编典典

如何在BigQuery中将多行汇总为一行？

sql

假设您有一个非规范化的架构，其中包含多行，如下所示：

   uuid    |    property    |    value   
------------------------------------------
  abc      |   first_name   |  John
  abc      |   last_name    |  Connor
  abc      |   age          |  26
...

所有行都具有相同的属性集，不一定要排序。 如何创建一个表格，例如使用BigQuery （即没有客户端）：

表user_properties：

   uuid    |    first_name  |    last_name   |    age
 --------------------------------------------------------
  abc      |   John         |    Connor      |    26

在传统的SQL中，为此目的使用了“ STUFF”关键字。

如果我至少 可以得到由uuid 排序 的结果 会更容易，这样客户端就不需要加载整个表（4GB）进行排序了-
可以通过顺序扫描具有相同uuid的行来合并每个实体。但是，这样的查询：

SELECT * FROM user_properties ORDER BY uuid;

超出BigQuery中的可用资源（使用allowLargeResults禁止ORDER
BY）。除非我订阅高端计算机，否则似乎几乎无法在BigQuery中对大表（4GB）进行排序。有任何想法吗？

阅读 236

2021-04-28

共1个答案

小编典典

SELECT 
  uuid,
  MAX(IF(property = 'first_name', value, NULL)) AS first_name,
  MAX(IF(property = 'last_name', value, NULL)) AS last_name,
  MAX(IF(property = 'age', value, NULL)) AS age
FROM user_properties
GROUP BY uuid

另一种选择-不涉及GROUP

SELECT uuid, first_name, last_name, age  
FROM (
  SELECT 
    uuid,
    LEAD(value, 1) OVER(PARTITION BY uuid ORDER BY property) AS first_name,
    LEAD(value, 2) OVER(PARTITION BY uuid ORDER BY property) AS last_name,
    value AS age,
    property = 'age' AS anchor
  FROM user_properties
)
HAVING anchor

2021-04-28