如何在Snowflake中利用动态值 [英] How to pivot on dynamic values in Snowflake

问题描述

我想基于可以包含动态"值(并非总是事先知道)的字段来旋转表.

I want to pivot a table based on a field which can contain "dynamic" values (not always known beforehand).

我可以通过对值进行硬编码(这是不希望的)来使其工作:

I can make it work by hard coding the values (which is undesirable):

SELECT *
FROM my_table
  pivot(SUM(amount) FOR type_id IN (1,2,3,4,5,20,50,83,141,...);

但是我无法使用查询来动态提供值:

But I can't make it work using a query to provide the values dynamically:

SELECT *
FROM my_table
  pivot(SUM(amount) FOR type_id IN (SELECT id FROM types);
---
090150 (22000): Single-row subquery returns more than one row. 

SELECT *
FROM my_table
  pivot(SUM(amount) FOR type_id IN (SELECT ARRAY_AGG(id) FROM types);
---
001038 (22023): SQL compilation error:                                          
Can not convert parameter 'my_table.type_id' of type [NUMBER(38,0)] into expected type [ARRAY]

有没有办法做到这一点?

Is there a way to accomplish this?

推荐答案

我认为在本机SQL中是不可能的，但是我写了

I don't think it's possible in native SQL, but I wrote an article and published some code showing how my team does this by generating the query from Python.

您可以直接调用Python脚本，并传递类似于Excel为数据透视表提供的选项的参数:

You can call the Python script directly, passing arguments similar to the options Excel gives you for pivot tables:

python generate_pivot_query.py                  \
    --dbtype snowflake --database mydb          \
    --host myhost.url --port 5432               \
    --user me --password myp4ssw0rd             \
    --base-columns customer_id                  \
    --pivot-columns category                    \
    --exclude-columns order_id                  \
    --aggfunction-mappings amount=sum           \
    myschema orders

或者，如果您是Airflow，则可以使用 CreatePivotTableOperator 直接创建任务.

Or, if you're Airflow, you can use a CreatePivotTableOperator to create tasks directly.

这篇关于如何在Snowflake中利用动态值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！