拆分逗号分隔的字符串并使用 Bigquery 将第二列的值划分为单独的行

Question

我试图将包含用逗号分隔的字符串的列拆分为行(简单的部分(，但也要将第二列除以逗号分隔字符串中的项数。

输入-

+--------------------+----+  
|11710, 11830        | 10 |  
+--------------------+----+  
|11711, 11015, 10020 |  9 |  
+--------------------+----+  

预期结果

+------+---+  
|11710 | 5 |  
+------+---+  
|11830 | 5 |  
+------+---+  
|11711 | 3 |  
+------+---+  
|11015 | 3 |    
+------+---+  
|10020 | 3 |  
+------+---+ 

查询：

#standardSQL
WITH `project.dataset.table` AS (
SELECT '11710, 11830' id, 10 hours UNION ALL
SELECT '11711, 11015, 10020', 9
)
SELECT * EXCEPT(uniq_id) REPLACE(uniq_id AS id)
FROM `project.dataset.table`,
UNNEST(SPLIT(id)) uniq_id   

Answer 1

试试这个

WITH data_ AS (
SELECT [11710, 11830] id, 10 hours UNION ALL
SELECT [11711, 11015, 10020], 9
)
select  itm,
cast((hours/array_length(id)) as int64) as div ,
hours from data_,unnest(id) as itm 

Answer 2

基于您的共享脚本，还可以考虑以下方法。

WITH `project.dataset.table` AS (
SELECT '11710, 11830' id, 10 hours UNION ALL
SELECT '11711, 11015, 10020', 9
)

SELECT 
TRIM(VALUE) AS id,
CAST(HOURS/ARRAY_LENGTH(SPLIT(id,',')) AS INT64) AS HOURS
FROM `project.dataset.table`,UNNEST(split(id,',')) AS VALUE