google-bigquery - BigQuery从查询中创建重复的记录字段

Question

是否可以在BigQuery中创建重复的记录列？例如，对于以下数据:

| a | b | c |
-------------
| 1 | 5 | 2 |
-------------
| 1 | 3 | 1 |
-------------
| 2 | 2 | 1 |

以下可能吗？

Select a, NEST(b, c) as d from *table* group by a

产生以下结果

| a | d.b | d.c |
-----------------
| 1 |  5  |  2  |
-----------------
|   |  3  |  1  |
-----------------
| 2 |  2  |  1  |

Answer 1

解决ozt_code仅嵌套一个字段的限制的一种方法是使用BigQuery User-Defined Functions。它们非常强大，但仍然需要注意一些Limits和Limitations。从我的角度来看，最重要的是-它们相当有资格成为昂贵的High-Compute queries

Complex queries can consume extraordinarily large computing resources relative to the number of bytes processed. Typically, such queries contain a very large number of JOIN or CROSS JOIN clauses or complex User-defined Functions.

因此，以下是questino中的“模仿” NEST(b，c)示例:

SELECT a, d.b, d.c FROM 
JS((      // input table
  SELECT a, NEST(CONCAT(STRING(b), ',', STRING(c))) AS d
  FROM (
    SELECT * FROM 
    (SELECT 1 AS a, 5 AS b, 2 AS c),
    (SELECT 1 AS a, 3 AS b, 1 AS c),
    (SELECT 2 AS a, 2 AS b, 1 AS c)
  ) GROUP BY a),
  a, d,     // input columns
  "[{'name': 'a', 'type': 'INTEGER'},    // output schema
    {'name': 'd', 'type': 'RECORD',
     'mode': 'REPEATED',
     'fields': [
       {'name': 'b', 'type': 'STRING'},
       {'name': 'c', 'type': 'STRING'}
     ]    
    }
  ]",
  "function(row, emit){    // function 
    var c = [];
    for (var i = 0; i < row.d.length; i++) {
      x = row.d[i].toString().split(',');
      t = {b:x[0], c:x[1]}
      c.push(t);
    };
    emit({a: row.a, d: c});  
  }"
)

这是相对简单的。我希望您能够通过它并获得一个想法

还记得:

No matter how you create record with nested/repeated fields - BigQuery automatically flattens query results, so visible results won't contain repeated fields. So you should use it as a subselect that produces intermediate results for immediate use by the same query.

作为仅供引用，您可以通过在下面的查询中运行来证明自己仅返回两条记录(在展平时不返回三条记录)

SELECT COUNT(1) AS rows FROM (
  <above query here>
) 

另一个重要的注意事项:
众所周知 NEST()与 NEST()输出不兼容，并且主要用于子查询中的中间结果。
相比之下，上述解决方案可以轻松地直接保存到表中(未经检查的展平结果)