大查询-将数组转置为列 [英] Big Query - Transpose arrays into colums

问题描述

我们在Big Query中有一个表格，如下所示.

We have a table in Big Query like below.

输入表:

 Name | Interests
 -----+----------
 Bob  | ["a"]
 Sue  | ["a","b"]
 Joe  | ["b","c"]

我们希望将上表转换为以下格式，使其对BI/可视化友好.

We want to convert the above table to below format to make it BI/Visualisation friendly.

目标/必需表:

 +------------------+
 | Name | a | b | c |
 +------------------+
 | Bob  | 1 | 0 | 0 |
 | Sue  | 1 | 1 | 0 |
 | Joe  | 0 | 1 | 0 |
 +------------------+

注意:兴趣列是数组数据类型.Big Query可能进行这种转换吗?如果是，是否有任何参考查询?

Note: The Interests column is an array datatype. Is this sort of transformation possible in Big Query? If yes, Any reference query?

提前谢谢！

推荐答案

以下内容适用于BigQuery Standard SQL，并使用BQ的脚本功能

Below is for BigQuery Standard SQL and uses scripting features of BQ

#standardSQL
create temp table ttt as (
  select name, interest 
  from `project.dataset.table`, 
  unnest(interests) interest
);

EXECUTE IMMEDIATE (
  SELECT """
  SELECT name, """ || 
    STRING_AGG("""MAX(IF(interest = '""" || interest || """', 1, 0)) AS """ || interest, ', ') 
  || """
  FROM ttt 
  GROUP BY name
  """
  FROM (
    SELECT DISTINCT interest 
    FROM ttt
    ORDER BY interest
  )
);      

如果要应用于您的问题中的示例数据-输出为

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

这篇关于大查询-将数组转置为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！