时间戳

首页 > 解决方案 > Sql：使用动态列列表取消透视

sql - Sql：使用动态列列表取消透视

问题描述

我希望在 sql 中做一些反透视，将键值转换为 ID 列表。表格中的列是动态的，每周都会更改（甚至可能每天更改）。哪里可能有更多的关键列和值的可能性。

DATABASE.PUBLIC.IMPORT_TABLE

+-----------+--------+--------+--------+
| SOURCE_ID | KEY1   | KEY2   | [KEY3] |
+-----------+--------+--------+--------+
| 0001      | TRUE   | FALSE  | [TBD]  |
| 0002      | TRUE   | FALSE  | [TBD]  |
| 0003      | TRUE   | TRUE   | [TBD]  |
| 0004      | TRUE   | TRUE   | [TBD]  |
| 0005      | FALSE  | TRUE   | [TBD]  |
+-----------+--------+--------+--------+

为了管理可能的组合，有一个表来管理键值对，并给它们一个 ID。此表用于管理我感兴趣的列。有时如果添加了其他列而不在此表中，则将其排除在外。

DATABASE.PUBLIC.KEY_VALUE_TABLE

+-----------+--------+--------+
| KEY       | VALUE  | KV_ID  |
+-----------+--------+--------+
| KEY1      | TRUE   | AAA    |
| KEY1      | FALSE  | BBB    |
| KEY2      | TRUE   | CCC    |
| KEY2      | FALSE  | DDD    |
| [KEY3]    | [TRUE] | [EEE]  |
+-----------+--------+--------+

使用以下查询。我能够实现我正在寻找的 unpivot。但是它没有考虑 KEY_VALUE_TABLE 的动态变化的变化

CREATE OR REPLACE TABLE DATABASE.PUBLIC.FINAL_TABLE AS
SELECT SOURCE_ID
      ,LISTAGG(DISTINCT b.KV_ID, ',') within group (order by b.KV_ID desc) AS KV_ID
FROM (
    SELECT DISTINCT SOURCE_ID, KEY, VALUE from DATABASE.PUBLIC.IMPORT_TABLE
    UNPIVOT(VALUE for KEY in (KEY1,KEY2))
) a
LEFT JOIN DATABASE.PUBLIC.KEY_VALUE_TABLE b
ON (a.KEY=b.KEY AND a.VALUE=b.VALUE)
GROUP BY 1,2

结果如下所示 DATABASE.PUBLIC.FINAL_TABLE

+-----------+--------+--------+
| SOURCE_ID | KV_ID           |
+-----------+--------+--------+
| 0001      | AAA,DDD         |
| 0002      | AAA,DDD         |
| 0003      | AAA,CCC         |
| 0004      | AAA,CCC         |
| 0005      | BBB,CCC         |
+-----------+--------+--------+

有没有办法让UNPIVOT(VALUE for KEY in (KEY1,KEY2))

到类似的东西UNPIVOT(VALUE for KEY in (SELECT DISTINCT KEY FROM KEY_VALUE_TABLE))。注意我已经尝试过后者，但它不起作用。SQLSELECT在 UNPIVOT中使用时出现问题

我也尝试过使用UNPIVOT(VALUE for KEY in ($KEY_LIST)) Where my variable is SET KEY_LIST = '[KEY1,KEY2]' => SET KEY_LIST = (SELECT listagg(KEY, ',') as my_strings FROM DATABASE.PUBLIC.KEY_VALUE_TABLE) SQL 对变量的大小有限制。

任何意见，将不胜感激。我的下一步是创建存储过程或函数。其中包含外部变量。通过javascript或类似的东西。

标签： sqlsnowflake-cloud-data-platform

解决方案

推荐阅读