雪花脱节 [英] 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" ...."12-31-2020")) 由 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
获得如下所示的输出 -
to get an output like the one below -
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屋!