sql - Snowflake 如何对字段数组运行逆透视查询而不是显式声明每个字段？

Question

我需要使用数组中指定的字段对表进行逆透视。我有以下疑问:

select
    *
FROM
    TEMP_TABLE_NAME T unpivot (
        val_col for name_col in (
            array_of_fields
        )
    );

问题是我们的数据是动态的，我们可能会收到具有 10 个字段或 100 个字段的数据，具体取决于我们需要上传到 Snowflake 的调查。

因此，我没有显式声明需要使用的字段，而是将字段导出到数组中，并且需要在 unpivot 的 in 子句中使用它们。当我们指定一些字段时，查询工作正常。

Answer 1

您可以使用 JavaScript UDTF 获得动态 unpivot:

CREATE OR REPLACE FUNCTION my_unpivot(R OBJECT, A ARRAY)
    RETURNS TABLE (KEY VARCHAR, VALUE VARCHAR)
    LANGUAGE JAVASCRIPT
    AS '
{
    processRow: function f(row, rowWriter, context){
       for (const element of row.A) {
          rowWriter.writeRow({KEY:element, VALUE:row.R[element]});
       }
    }
}';

SELECT empid, dept, x.key month, x.value sales
FROM (
    select *, object_construct(a.*) obj
    from monthly_sales a
), TABLE(my_unpivot(obj, array_construct('JAN', 'FEB', 'MAR', 'APRIL'))) x
;

本例的示例数据:

create or replace table monthly_sales(empid int, dept text, jan int, feb int, mar int, april int);


insert into monthly_sales values
    (1, 'electronics', 100, 200, 300, 100),
    (2, 'clothes', 100, 300, 150, 200),
    (3, 'cars', 200, 400, 100, 50);