横向平整两列,雪花中无重复

snowflake-datawarehouse

1728 观看

1回复

1191 作者的声誉

我有一个查询,该查询按两个变量进行分组以得到另一个变量。为了维护我的表结构以进行以后的计算,我将listagg()的其他两个变量保存到查询的下一阶段。但是,当我尝试对listagg()列进行两个以后的展平时,我的数据会重复很多次。

示例:my_table

   id   |     list1       | code|   list2  | total
--------|-----------------|-----|----------|---
2434166 | 735,768,769,746 | 124 | 21,2,1,6 | 30


select
id,
list1_table.value::int as list1_val,
code,
list2.value::int as list2_val,
total

from my_table
lateral flatten(input=>split(list1, ',')) list1_table,
lateral flatten(input=>split(list2, ',')) list2_table

结果:

   id   |     list1       | code|   list2  | total
--------|-----------------|-----|----------|---
2434166 |      768        | 124 |     2    | 30
2434166 |      735        | 124 |     2    | 30
2434166 |      746        | 124 |     2    | 30
2434166 |      769        | 124 |     2    | 30
2434166 |      768        | 124 |     21   | 30
2434166 |      735        | 124 |     21   | 30
2434166 |      746        | 124 |     21   | 30
2434166 |      769        | 124 |     21   | 30
2434166 |      768        | 124 |     6    | 30
2434166 |      735        | 124 |     6    | 30
2434166 |      746        | 124 |     6    | 30
2434166 |      769        | 124 |     6    | 30
2434166 |      768        | 124 |     1    | 30
2434166 |      735        | 124 |     1    | 30
2434166 |      746        | 124 |     1    | 30
2434166 |      769        | 124 |     1    | 30

我了解发生了什么,但我只是想知道如何获得理想的结果:

   id   |     list1       | code|   list2  | total
--------|-----------------|-----|----------|---
2434166 |      768        | 124 |     2    | 30
2434166 |      735        | 124 |     21   | 30
2434166 |      746        | 124 |     6    | 30
2434166 |      769        | 124 |     1    | 30
作者: moku 的来源 发布者: 2016 年 4 月 22 日

回应 (1)


4

2500 作者的声誉

决定

如您所知,您需要4条记录。有两种方法,两种方法都利用index产生的列flatten,该列表示输入中产生值的位置(请参见Flatten文档

使用2展平和索引选择

第一种方法是获取查询结果,并添加这些索引列,这是一个示例:

select id,
list1_table.value::int as list1_val, list1_table.index as list1_index, code,
list2_table.value::int as list2_val, list2_table.index as list2_index, total
from my_table,
lateral flatten(input=>split(list1, ',')) list1_table,
lateral flatten(input=>split(list2, ',')) list2_table;
---------+-----------+-------------+------+-----------+-------------+-------+
   ID    | LIST1_VAL | LIST1_INDEX | CODE | LIST2_VAL | LIST2_INDEX | TOTAL |
---------+-----------+-------------+------+-----------+-------------+-------+
 2434166 | 735       | 0           | 124  | 21        | 0           | 30    |
 2434166 | 735       | 0           | 124  | 2         | 1           | 30    |
 2434166 | 735       | 0           | 124  | 1         | 2           | 30    |
 2434166 | 735       | 0           | 124  | 6         | 3           | 30    |
 2434166 | 768       | 1           | 124  | 21        | 0           | 30    |
 2434166 | 768       | 1           | 124  | 2         | 1           | 30    |
 2434166 | 768       | 1           | 124  | 1         | 2           | 30    |
 2434166 | 768       | 1           | 124  | 6         | 3           | 30    |
 2434166 | 769       | 2           | 124  | 21        | 0           | 30    |
 2434166 | 769       | 2           | 124  | 2         | 1           | 30    |
 2434166 | 769       | 2           | 124  | 1         | 2           | 30    |
 2434166 | 769       | 2           | 124  | 6         | 3           | 30    |
 2434166 | 746       | 3           | 124  | 21        | 0           | 30    |
 2434166 | 746       | 3           | 124  | 2         | 1           | 30    |
 2434166 | 746       | 3           | 124  | 1         | 2           | 30    |
 2434166 | 746       | 3           | 124  | 6         | 3           | 30    |
---------+-----------+-------------+------+-----------+-------------+-------+

如您所见,您感兴趣的行是具有相同索引的行。

因此,通过在横向连接发生之后选择这些行来获得结果:

select id,
list1_table.value::int as list1_val, code,
list2_table.value::int as list2_val, total
from my_table,
lateral flatten(input=>split(list1, ',')) list1_table,
lateral flatten(input=>split(list2, ',')) list2_table 
where list1_table.index = list2_table.index;
---------+-----------+------+-----------+-------+
   ID    | LIST1_VAL | CODE | LIST2_VAL | TOTAL |
---------+-----------+------+-----------+-------+
 2434166 | 735       | 124  | 21        | 30    |
 2434166 | 768       | 124  | 2         | 30    |
 2434166 | 769       | 124  | 1         | 30    |
 2434166 | 746       | 124  | 6         | 30    |
---------+-----------+------+-----------+-------+

使用1展平+按索引查找

一种更简单,更有效,更灵活的方法(如果您有多个这样的数组(例如,与数组索引相关但不一对一的关系,则很有用))仅在一个数组上展平,然后使用产生的元素的索引查找其他数组中的值。

这是一个例子:

select id, list1_table.value::int as list1_val, code, 
split(list2,',')[list1_table.index]::int as list2_val,  -- array lookup here 
total
from my_table, lateral flatten(input=>split(list1, ',')) list1_table;
---------+-----------+------+-----------+-------+
   ID    | LIST1_VAL | CODE | LIST2_VAL | TOTAL |
---------+-----------+------+-----------+-------+
 2434166 | 735       | 124  | 21        | 30    |
 2434166 | 768       | 124  | 2         | 30    |
 2434166 | 769       | 124  | 1         | 30    |
 2434166 | 746       | 124  | 6         | 30    |
---------+-----------+------+-----------+-------+

了解我们如何简单地使用展平时生成的索引list1来从中查找值list2

作者: Marcin Zukowski 发布者: 22.04.2016 11:07
来自类别的问题 :
32x32