snowflake-cloud-data-platform - 横向压平两列，雪花中不重复

Question

我有一个查询，它按两个变量分组以获得另一个变量。为了为以后的计算维护我的表结构，我列出了另外两个变量来保存以供查询的下一阶段使用。但是，当我尝试对 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

Answer 1

当您注意到自己时，您需要 4 条记录。有两种方法可以做到，都利用 index专栏由 flatten 制作，表示生成值在输入中的位置(参见 Flatten Documentation )

使用 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 flatten + lookup-by-index

一种更简单、更高效、更灵活的方法(如果您有多个这样的数组或例如数组索引相关但不是 1 对 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 中查找值