时间戳

首页 > 解决方案 > 数组中的 BigQuery 数组到正确的列

google-bigquery - 数组中的 BigQuery 数组到正确的列

问题描述

我正在尝试操纵它；

id | data
1  | [[item1,2,1.99],[item2,1,4.99]]
2  | [[item1,2,1.99]]

进入这个：

id | A     | B | C    |
1  | item1 | 2 | 1.99 |
   | item2 | 1 | 4.99 |
2  | item1 | 2 | 1.99 |
...'

我得到的最接近的是；

id | data |
1  | item1, 2, 1.99 |
   | item2, 1, 4.99 |
2  | item1, 2, 1.99 |

如何将该字符串数组拆分为列？

这是我当前的查询；

SELECT 
   id,
   ARRAY(SELECT * FROM UNNEST(SPLIT(SUBSTR(line_items, 3 , LENGTH(line_items) - 4),'], [')) AS line_items) AS line_items
FROM raw_data.sales

这个查询也是一样的；

SELECT 
   id,
   SPLIT(SUBSTR(line_items, 3 , LENGTH(line_items) - 4),'], [') AS line_items
FROM raw_data.sales

解决方案

SELECT
  ID id,
  ARRAY_AGG(STRUCT(SPLIT(A, ',')[OFFSET(0)] AS A,
  SPLIT(A, ',')[OFFSET(1)] AS B,
  SPLIT(A, ',')[OFFSET(2)] AS C))
FROM
  TABLE t,
  UNNEST(SPLIT(SUBSTR(DATA, 3 , LENGTH(DATA) - 4),'],[')) A
GROUP BY id

标签： google-bigquery

解决方案

以下是 BigQuery 标准 SQL 

#standardSQL
SELECT id, 
  ARRAY(SELECT SPLIT(value, ',')[OFFSET(0)] FROM UNNEST(x.y) value) AS A,
  ARRAY(SELECT SPLIT(value, ',')[OFFSET(1)] FROM UNNEST(x.y) value) AS B,
  ARRAY(SELECT SPLIT(value, ',')[OFFSET(2)] FROM UNNEST(x.y) value) AS C
FROM `project.dataset.my_table`,
UNNEST([STRUCT(SPLIT(SUBSTR(DATA, 3, LENGTH(DATA) - 4),'],[') AS y)]) x

您可以使用您问题中的示例数据进行测试，使用上面的示例数据，如下例所示 

#standardSQL
WITH `project.dataset.my_table` AS (
  SELECT 1 AS ID, '[[item1,2,1.99],[item2,1,4.99]]' AS DATA UNION ALL
  SELECT 2, '[[item1,2,1.99]]'
)
SELECT
  ID id, 
  ARRAY(SELECT SPLIT(value, ',')[OFFSET(0)] FROM UNNEST(x.y) value) AS A,
  ARRAY(SELECT SPLIT(value, ',')[OFFSET(1)] FROM UNNEST(x.y) value) AS B,
  ARRAY(SELECT SPLIT(value, ',')[OFFSET(2)] FROM UNNEST(x.y) value) AS C
FROM `project.dataset.my_table`,
UNNEST([STRUCT(SPLIT(SUBSTR(DATA, 3, LENGTH(DATA) - 4),'],[') AS y)]) x

结果 

Row id  A       B   C    
1   1   item1   2   1.99     
        item2   1   4.99     
2   2   item1   2   1.99

推荐阅读