将嵌套阵列转换为雪花

Question

我有一个表列，上面有雪花数据库中的嵌套数组。我想以雪花SQL中所示的方式将嵌套数组转换为列。

表名称：Sensor_data

Rx列是数据类型变体的。如下所示，嵌套阵列不会总是为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  |

Answer 1

让我们重新创建该表：

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	null
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（iff（r.value [0] = 2，r.value，r.value） 1 ，null）表（FLATTEN（输入=＆GT; rx））R组按1顺序为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	NULL
3	100	42	76

基于手册的这些部分的代码：

与loops一起工作

雪花扫描/结果表.html“ rel =“ nofollow noreferrer”>与结果集一起使用

和上述顺序：

create or replace 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]]')
             ,(4, '[[0,20],[30,50], [45, 100]]');

declare
  sql string;
  res resultset;
  c1 cursor for select distinct r.value[0] as key from sensor_data, table(flatten(input=>rx)) r order by key;
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;

giss：

id	col_0	col_0	col_1	col_2	col_3
带有	”	额外	和提及的顺序	的	棘手
“	数据	25	null	null	null
3	100	42	76	null	null
4	20	null	null	50	100

，所以是的，但这是最大的操作：

因此，如果我们返回原始数据

create or replace 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 id
    ,iff(r.value[0] = 0, r.value[1], null) as col_0
    ,iff(r.value[0] = 1, r.value[1], null) as col_1
    ,iff(r.value[0] = 2, r.value[1], null) as col_2
 from sensor_data, table(flatten(input=>rx)) r
 order by 1;  

我们会看到：

ID	col_0	col_1	col_2
1	15	null	null
1	1 null	50	null
null 1	null	null	34.2
2	20	null	null
2	null	2 null 2 null 2 null 25	null
3	100	null	null
3	null 3 null	3 null 42	null
3	null 3 null	null	76，

以便我们可以看到这些值被插入了，现在由ID进行了，现在是ID的组，将要滚动这三个ID，因此对于Col0，我们具有15，null，Null max采用最高的值，而15较高，而null则是null的。这是正在进行的过程。

Answer 2

使用[]访问数组元素：

SELECT ID, RX[0][1] AS col1,  RX[1][1] AS col1, RX[2][1] AS col2
FROM SENSOR_DATA;

Answer 3

您的要求不完全是什么，但这很接近

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