如何在 BigQuery 中取消嵌套和透视两列 [英] How to unnest and pivot two columns in BigQuery

问题描述

假设我有一个包含以下信息的 BQ 表

Say I have a BQ table containing the following information

<头>

id	test.name	test.score
1	一	5
	b	7
2	一	8
	c	3

测试嵌套的地方.我如何将测试转入下表?

Where test is nested. How would I pivot test into the following table?

<头>

id	一个	b	c
1	5	7
2	8		3

我无法直接透视测试，因为我在 pivot(test) 收到以下错误消息:Table-valued function not found.以前的问题(1、2) 不处理嵌套列或已过时.

I cannot pivot test directly, as I get the following error message at pivot(test): Table-valued function not found. Previous questions (1, 2) don't deal with nested columns or are outdated.

以下查询似乎是有用的第一步:

The following query looks like a useful first step:

select a.id, t
from `table` as a,
unnest(test) as t

然而，这只是为我提供:

However, this just provides me with:

<头>

id	test.name	test.score
1	一	5
1	b	7
2	一	8
2	c	3

推荐答案

以下是处理您的情况的通用/动态方式

Below is generic/dynamic way to handle your case

EXECUTE IMMEDIATE (
  SELECT """
  SELECT id, """ || 
    STRING_AGG("""MAX(IF(name = '""" || name || """', score, NULL)) AS """ || name, ', ') 
  || """
  FROM `project.dataset.table` t, t.test
  GROUP BY id
  """
  FROM (
    SELECT DISTINCT name
    FROM `project.dataset.table` t, t.test
    ORDER BY name
  )
);  

如果应用于您问题中的样本数据 - 输出为

If to apply to sample data from your question - output is

Row     id      a       b       c    
1       1       5       7       null     
2       2       8       null    3    

这篇关于如何在 BigQuery 中取消嵌套和透视两列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！