我在雪花資料庫中有一個帶有嵌套陣列的表列。我想以雪花 SQL 中顯示的方式將嵌套陣列轉換為列。
表名：SENSOR_DATA
RX 列的資料型別為 VARIANT。嵌套陣列并不總是 3，如下所示。在某些情況下，有 20,000 個嵌套陣列，而在其他情況下則沒有。
| ID | RX |
|----|-----------------------------|
| 1 |[[0, 15], [1, 50], [2, 34.2]]|
| 2 |[[0, 20], [1, 25]] |
| 3 |[[0, 100], [1, 42], [2, 76]] |
我想從上表中實作這樣的目標：
| ID |Col0 | Col1| Col2|
|----|-----|-----|-----|
| 1 | 15 | 50 | 34.2|
| 2 | 20 | 25 | NULL|
| 3 | 100 | 42 | 76 |
uj5u.com熱心網友回復：
使用 [] 訪問陣列元素：
SELECT ID, RX[0][1] AS col1, RX[1][1] AS col1, RX[2][1] AS col2
FROM SENSOR_DATA;
uj5u.com熱心網友回復：
不完全符合您的要求，但這很接近
with sensor_data as (
select column1 id, parse_json(column2) rx
from values (1, '[[0, 15], [1, 50], [2, 34.2]]')
,(2, '[[0, 20], [1, 25]]')
,(3, '[[0, 100], [1, 42], [2, 76]]')
as vals
),
flat as (
select id, val.value[1] arrvalue
from sensor_data,
lateral flatten(input => sensor_data.rx, outer => true) val
)
select
id
,listagg(arrvalue, ',') rx_list
from flat
group by id
order by id
;
ID RX_LIST
1 15,50,34.2
2 20,25
3 100,42,76
uj5u.com熱心網友回復：
讓我們重新創建該表：
create table sensor_data as
select column1 id, parse_json(column2) rx
from values (1, '[[0, 15], [1, 50], [2, 34.2]]')
,(2, '[[0, 20], [1, 25]]')
,(3, '[[0, 100], [1, 42], [2, 76]]');
然后獲取您擁有的不同列鍵：
select distinct r.value[0] from sensor_data, table(flatten(input=>rx)) r;
| R.VALUE[0] |
|---|
| 0 |
| 1 |
| 2 |
鑒于我們希望這是動態的，下一步就是使用靜態 SQL 檢查它給我們的正確答案：
select id
,max(iff(r.value[0] = 0, r.value[1], null)) as col_0
,max(iff(r.value[0] = 1, r.value[1], null)) as col_1
,max(iff(r.value[0] = 2, r.value[1], null)) as col_2
from sensor_data, table(flatten(input=>rx)) r
group by 1
order by 1;
| ID | COL_0 | COL_1 | COL_2 |
|---|---|---|---|
| 1 | 15 | 50 | 34.2 |
| 2 | 20 | 25 | 無效的 |
| 3 | 100 | 42 | 76 |
現在撰寫動態執行此操作：
declare
sql string;
c1 cursor for select distinct r.value[0] as key from sensor_data, table(flatten(input=>rx)) r;
begin
sql := 'select id ';
for record in c1 do
sql := sql || ',max(iff(r.value[0] = '|| record.key::text ||', r.value[1], null)) as col_' || record.key::text;
end for;
sql := sql || ' from sensor_data, table(flatten(input=>rx)) r group by 1 order by 1';
return sql;
end;
這給了我們：
選擇 id ,max(iff(r.value[0] = 0, r.value 1 , null)) 作為 col_0,max(iff(r.value[0] = 1, r.value 1 , null)) 作為 col_1 ,max(iff(r.value[0] = 2, r.value 1 , null)) as col_2 from sensor_data, table(flatten(input=>rx)) r group by 1 order by 1
運行時會給我們預期的結果。
所以現在我們要運行它：
declare
sql string;
res resultset;
c1 cursor for select distinct r.value[0] as key from sensor_data, table(flatten(input=>rx)) r;
begin
sql := 'select id ';
for record in c1 do
sql := sql || ',max(iff(r.value[0] = '|| record.key::text ||', r.value[1], null)) as col_' || record.key::text;
end for;
sql := sql || ' from sensor_data, table(flatten(input=>rx)) r group by 1 order by 1';
res := (execute immediate :sql);
return table (res);
end;
給出：
| ID | COL_0 | COL_1 | COL_2 |
|---|---|---|---|
| 1 | 15 | 50 | 34.2 |
| 2 | 20 | 25 | 無效的 |
| 3 | 100 | 42 | 76 |
基于手冊這些部分的代碼：
使用回圈
使用結果集
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/492348.html
上一篇：將每個重復行匹配到不同的記錄