如何在BigQuery中将多行汇总为一行? [英] How to aggregate multiple rows into one in BigQuery?

问题描述

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

Suppose you have a de-normalized schema with multiple rows like below:

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

所有行都具有相同的属性集，不一定要排序. 如何使用BigQuery创建表(即没有客户端)

The same set of properties for all rows, not necessarily sorted. How to create a table such as using BigQuery (i.e. no client):

表user_properties:

Table user_properties:

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

在传统的SQL中，为此目的使用了"STUFF"关键字.

In traditional SQL there is the "STUFF" keyword for this purpose.

如果我至少将结果按uuid排序会更容易，这样客户端就不需要加载整个表(4GB)进行排序-可以对每个表进行水合实体，方法是依次扫描具有相同uuid的行.但是，这样的查询:

It would be easier if I could at least get the results ORDERED by uuid so the client would not need to load the whole table (4GB) to sort -- it would be possible to hydrate each entity by scanning sequentially the rows with same uuid. However, a query like this:

SELECT * FROM user_properties ORDER BY uuid; 

超出了BigQuery中的可用资源(使用allowLargeResults禁止ORDER BY).除非我订阅高端计算机，否则似乎几乎无法在BigQuery中对大表(4GB)进行排序.有什么想法吗?

exceeds the available resources in BigQuery (using allowLargeResults forbids ORDER BY). It almost seems like I cannot sort a large table (4GB) in BigQuery unless I subscribe to a high end machine. Any ideas?

推荐答案

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

Another option - no GROUP'ing involved

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

这篇关于如何在BigQuery中将多行汇总为一行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！