时间戳

首页 > 解决方案 > 有没有办法将一组列转换为相同的 NUMBER(p,s) 类型，以便它们可以在 Snowflake SQL 中成为 UNPIVOT？

snowflake-cloud-data-platform - 有没有办法将一组列转换为相同的 NUMBER(p,s) 类型，以便它们可以在 Snowflake SQL 中成为 UNPIVOT？

问题描述

我有一个表格，几个数字列都有不同的NUMBER(p,s)类型。该表是使用CREATE TABLE xx as (select date, SUM(x), SUM(y) from xxx GROUP BY date). 似乎雪花决定了NUMBER(precision, scale)存储每个结果列所需的最小值。这导致每列的类型不同。

现在我想 UNPIVOT 那些列，Snowflake 会抱怨SQL compilation error: The type of column 'xxxxx' conflicts with the type of other columns in the UNPIVOT list

我创建了这个最小的表来举例说明这个问题：

create or replace temporary table temp1(id number, sales number(10,0), n_orders number(20,0)) as (
    select * from (values
           (1, 1, 2 )
           ,(2, 3, 4)
           ,(3, 5, 6)
    ) 
); -- imagine that temp1 was created via a select AGG1, AGG2 FROM XXX GROUP BY YYY

describe table temp1;
-- 
name    type    kind    null?   default primary key unique key  check   expression  comment
ID  NUMBER(38,0)    COLUMN  Y       N   N           
SALES   NUMBER(10,0)    COLUMN  Y       N   N           
N_ORDERS    NUMBER(20,0)    COLUMN  Y       N   N           

select * 
from temp1 UNPIVOT(measure_value for measure_name in (sales, n_orders)); -- won't work because SALES is NUMBER(10,0) and N_ORDERS is NUMBER(20,0)

现在我的解决方法是TO_NUMBER(x, 38,0) as x像这样显式地转换每一列：

with t1 as (
  select 
    id
    ,TO_NUMBER(sales,38,0) as sales
    ,TO_NUMBER(n_orders, 38,0) as n_orders
  from temp1
)
select * from t1 UNPIVOT(measure_value for measure_name in (sales, n_orders));

这不是最佳的，因为我正在使用的实际表中有很多列。

我不想重新创建表（聚合需要很长时间来计算）所以我有什么选择？

是否有任何其他语法可用于批量转换列列表？

标签： snowflake-cloud-data-platform

解决方案

您最好的选择是修改已经创建的表（无需重新运行代价高昂的聚合），如下所示：

alter table temp1 modify
  sales set data type number(38,0)
  ,n_orders set data type number(38,0)
;

这种方式有两个好处：

  1. 您避免为每列输入两次列名： column_name set data type number(38,)而不是TO_NUMBER(column_name, 38,0) as column_name
  2. 它只运行一次，而不必在每个 UNPIVOT 查询之前作为 CTE 运行。

推荐阅读