我試圖將包含由逗號分隔的字符串的列拆分為行（簡單部分），但還將第二列除以逗號分隔的字符串中的項目數。
輸入 -
+--------------------+----+
|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
嘗試這個
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
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句