将SPLIT键/值对分配到Google BigQuery中的列 [英] SPLIT key-value-pairs to Columns in Google BigQuery
问题描述
我对Google BigQuery还是陌生的,肯定很挣扎.
I am quite new to Google BigQuery and definitely struggling.
我的表具有以下内容:
+----------+----------------------------------------+
| order_id | line_items |
+----------+----------------------------------------+
| 123 | id:1|qy:1|sum:1.00;id:2|qy:6|sum:4.50; |
+----------+----------------------------------------+
| 456 | id:1|qy:3|sum:3.00;id:3|qy:4|sum:3.20; |
+----------+----------------------------------------+
我需要看起来像这样:
+----------+----+----+------+
| order_id | id | qy | sum |
+----------+----+----+------+
| 123 | 1 | 1 | 1.00 |
| 123 | 2 | 6 | 4.50 |
| 456 | 1 | 3 | 3.00 |
| 456 | 3 | 4 | 3.20 |
+----------+----+----+------+
我在line_items中拥有的键值对的数量是任意的(并且有不止三个,但是我需要提取这三个).
The amount of key value pairs I have in line_items is arbitrary (and there are much more than those 3, but I would need to extract those three).
我能够使以下UNNEST和SPLIT查询正常工作,但不幸的是,我仍然具有这些键值对...
I was able to get the following UNNEST and SPLIT query working, but unfortunately I still have these key-value pairs...
此
SELECT
order_id,
line_items
FROM
`myTable`,
UNNEST(SPLIT(line_items,"|")) line_items
带我来这里
+----------+------------+
| order_id | line_items |
+----------+------------+
| 123 | id:1 |
| 123 | qy:1 |
| 123 | sum:1.00 |
| 123 | id:2 |
| 123 | qy:6 |
| 123 | sum:4.50; |
| 456 | id:1 |
| 456 | qy:3 |
| 456 | sum:3.00 |
| 456 | id:3 |
| 456 | qy:4 |
| 456 | sum:3.20 |
+----------+------------+
因此,我仍然不是很能干,如何将这些键提取到列标题,并将值提取到列内容.
So I am still not really able, how to extract these keys to column headlines and the value to the column content.
如果有人指出我正确的方向,我将不胜感激.
I would highly appreciate if someone pointed me in the right direction.
已经非常感谢!
推荐答案
下面是BigQuery标准SQL
Below is for BigQuery Standard SQL
#standardSQL
select order_id,
( select split(kv, ':')[offset(1)] from x.kvs kv where split(kv, ':')[offset(0)] = 'id') id,
( select split(kv, ':')[offset(1)] from x.kvs kv where split(kv, ':')[offset(0)] = 'qy') qy,
( select split(kv, ':')[offset(1)] from x.kvs kv where split(kv, ':')[offset(0)] = 'sum') sum
from `project.dataset.table`,
unnest(split(trim(line_items, ';'), ';')) items,
unnest([struct(split(items,'|') as kvs)]) x
-- order by order_id
如果要应用于您的问题的样本数据-输出为
If to apply to sample data from your question - output is
下面的变化也可能有用
#standardSQL
select order_id,
(select value from z.y where key = 'id') id,
(select value from z.y where key = 'qy') qy,
(select value from z.y where key = 'sum') sum
from `project.dataset.table`,
unnest(split(trim(line_items, ';'), ';')) items,
unnest([struct(split(items,'|') as kvs)]) x,
unnest([struct(array(
select as struct
split(kv, ':')[offset(0)] as key,
split(kv, ':')[offset(1)] value
from x.kvs kv
) as y)]) z
-- order by order_id
这篇关于将SPLIT键/值对分配到Google BigQuery中的列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!