使用Bigquery将逗号分隔的字符串拆分为行

Question

我要将包含逗号分隔的字符串的列拆分为行

输入 -

期望输出 -

我尝试使用REGEXP_EXTRACT_ALL但无法得到上面的输出。

- raven

---

你具体尝试了什么？ - Wiktor Stribiżew

Answer 1

以下内容适用于BigQuery标准SQL。

#standardSQL
SELECT * EXCEPT(c) REPLACE(c AS country)
FROM `project.dataset.table`,
UNNEST(SPLIT(country)) c   

如果要应用来自您问题的样本数据，就像下面的示例一样。

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 'asia' region, 'india,china,japan' country, 100 revenue, 0.3 weight UNION ALL
  SELECT 'europe', 'uk,france,germany,italy', 75, 0.25
)
SELECT * EXCEPT(c) REPLACE(c AS country)
FROM `project.dataset.table`,
UNNEST(SPLIT(country)) c   

结果是

Row region  country revenue weight   
1   asia    india   100     0.3  
2   asia    china   100     0.3  
3   asia    japan   100     0.3  
4   europe  uk      75      0.25     
5   europe  france  75      0.25     
6   europe  germany 75      0.25     
7   europe  italy   75      0.25     

- Mikhail Berlyant

Answer 2

WITH alarm_table AS (

   SELECT 'X' AS model, '1,34,60,1000' AS alarm
   union all  
   SELECT 'Y' AS model, '14,32,44,111' AS alarm
   union all  
   SELECT 'Z' AS model, '15,43,55,222' AS alarm
)

SELECT model, TRIM(alarm_time) AS alarm1 FROM alarm_table, UNNEST(SPLIT(alarm, ',')) alarm_time

- BinaryWo