У меня есть одна таблица (Bigquery), в которой значения разделены ',' в двух столбцах:
id tag_id tag_name
1 1, 2, 4 a, b, d
2 3 c
3 1, 4 a, d
Для каждого tag_id 1:1 совпадает с tag_name. Я хочу разделить значения, разделенные запятыми, и преобразовать их в строки. Идеальный выход выглядит так:
id tag_id tag_name
1 1 a
1 2 b
1 4 d
2 3 c
3 1 a
3 4 d
Мой рабочий ход SQL:
SELECT * EXCEPT(t, tn) REPLACE(t AS tag_id, tn AS tags_name)
FROM `table`,
UNNEST(SPLIT(tag_id, ', ')) t, UNNEST(SPLIT(tags_name, ', ')) tn
Но похоже, что tag_id не совпадает 1:1 с tags_name... Я хотел бы использовать EXCEPT
в запросе выбора, так как есть много других столбцов, которые я не хочу перечислять вручную.
Один из вариантов — сохранить смещение каждого элемента в каждом списке, чтобы мы могли использовать эту информацию для соединения соответствующих строк после того, как они не вложены друг в друга:
select t.id, elt_id, elt_name
from mytable t
cross join unnest( split(t.tag_id, ', ') ) elt_id with offset as rn_id
cross join unnest( split(t.tag_name, ', ') ) elt_name with offset as rn_name
where rn_id = rn_name
В качестве альтернативы вы можете unnest
tag_id
использовать индекс offset
для извлечения tag_name
select id,
elt_id as tag_id ,
split(tag_name, ',')[safe_offset(rn_id)] as part1
from cte t,unnest(split(t.tag_id,',')) elt_id with offset as rn_id