雪花旋转 [英] Snowflake unpivoting

问题描述

我需要转置一个表，其中 column1 是实体的名称，column2 到 column366 是一年中持有美元金额的日期.表格、select语句和输出结果都给出下面 -

I need to transpose a table in which column1 is name of an entity and column2 to column366 are dates in a year that hold a dollar amount. The table, the select statement and the output result are all given below -

问题 - 这种语法要求我创建一个逗号分隔的列列表 - 基本上是 365 个日期 - 并在 select 语句的 IN 子句中使用该列表.

Question - This syntax requires me to create a comma separated list of columns - which are basically 365 dates - and use that list in the IN clause of the select statement.

像这样 -

.....unpivot ("1-1-2020" , "1-2-2020" , "1-3-2020" ......2020 年 12 月 31 日")) 订购 2

.....unpivot (cash for dates in ("1-1-2020" , "1-2-2020" , "1-3-2020"........."12-31-2020")) order by 2

有没有更好的方法呢?像正则表达式一样?我不想以 mm-dd-yyyy 格式输入 365 个日期并为我的麻烦获取心皮隧道

Is there any better way of doing this ? Like with regular expressions ? I don't want to type 365 dates in mm-dd-yyyy format and get carpel tunnel for my trouble

这是表格 - 第一行是列标题，第二行是分隔符.第 3、4、5 行是样本数据.

Here is the table - First line is column header, second line is separator. 3rd, 4th and 5th lines are sample data.

Name                  01-01-2020            01-02-2020          01-03-2020           12-31-2020
---------------------------------------------------------------------------------------------------
Entity1               10.00                 15.75               20.00                100.00
Entity2               11.00                 16.75               20.00                 10.00
Entity3              112.00                166.75               29.00                108.00

我可以使用下面的 select 语句转置它

I can transpose it using the select statement below

从表 1 中选择 *unpivot (("1-1-2020" , "1-2-2020" , "1-3-2020") 日期的现金) 以 2 为单位

select * from Table1 unpivot (cash for dates in ("1-1-2020" , "1-2-2020" , "1-3-2020")) order by 2

获得如下输出 -

    Name-------------------dates-----------------------cash
    --------------------------------------------------------------
    Entity1                01-01-2020                   10.00
    Entity2                01-01-2020                   11.00
    Entity3                01-01-2020                  112.00

...............................
.............................
.........
and so on

推荐答案

有一种更简单的方法可以在没有 PIVOT 的情况下执行此操作.Snowflake 为您提供了一个将整行表示为对象"的函数——键值对的集合.使用该表示，您可以展平每个元素并提取列名(键 == 日期)和内部值(值 == 现金).这是一个可以执行此操作的查询:

There is a simpler way to do this without PIVOT. Snowflake gives you a function to represent an entire row as an "OBJECT" -- a collection of key-value pairs. With that representation, you can FLATTEN each element and extract both the column name (key == date) and the value inside (value == cash). Here is a query that will do it:

with obj as (
   select OBJECT_CONSTRUCT(*) o from Table1
)
select o:NAME::varchar as name, 
       f.key::date as date, 
       f.value::float as cash  
from obj,
     lateral flatten (input => obj.o, mode => 'OBJECT') f
where f.key != 'NAME'
;

这篇关于雪花旋转的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！