使用UNPIVOT将代码从SQL重写为Redshift [英] Rewrite code with UNPIVOT from sql to redshift

查看：71
本文介绍了使用UNPIVOT将代码从SQL重写为Redshift的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有需要转换为redshift的SQL Server脚本.

我转换了一部分

这是我遇到问题的脚本的一部分.

    SELECT  a.*,
        b.*

FROM    (

SELECT  u.ContactId,
        u.Description,
        CONVERT(Float,u.SeatCharge) AS SeatCharge
        --CAST(u.SeatCharge AS numeric (18,4)) AS SeatCharge,
        --CONVERT(INT, CASE WHEN IsNumeric(CONVERT(VARCHAR(12), u.SeatCharge)) = 1 then CONVERT(VARCHAR(12), u.SeatCharge) else 0 End)

FROM(

SELECT  md.contactid,
                CONVERT(float, MAX(CASE WHEN md.fieldid= 7172 THEN CONVERT(float,Value) ELSE 0 END)) AS PM_Core,
                CONVERT(float, MAX(CASE WHEN md.fieldid= 7182 THEN CONVERT(float,Value) ELSE 0 END)) AS PM_RCM,
                CONVERT(float, MAX(CASE WHEN md.fieldid= 7192 THEN CONVERT(float,Value) ELSE 0 END)) AS PM_Advanced_RCM,
                CONVERT(float, MAX(CASE WHEN md.fieldid= 7183 THEN CONVERT(float,Value) ELSE 0 END)) AS PM_Payroll,
                CONVERT(float, MAX(CASE WHEN md.fieldid= 7184 THEN CONVERT(float,Value) ELSE 0 END)) AS PM_DXM,
                CONVERT(float, MAX(CASE WHEN md.fieldid= 7185 THEN CONVERT(float,Value) ELSE 0 END)) AS PM_Messaging,
                CONVERT(float, MAX(CASE WHEN md.fieldid= 7186 THEN CONVERT(float,Value) ELSE 0 END)) AS PM_Tasks,
                CONVERT(float, MAX(CASE WHEN md.fieldid= 7173 THEN CONVERT(float,Value) ELSE 0 END)) AS Clinical_Core,
                CONVERT(float, MAX(CASE WHEN md.fieldid= 7187 THEN CONVERT(float,Value) ELSE 0 END)) AS Clinical_Data_Collection,
                CONVERT(float, MAX(CASE WHEN md.fieldid= 7189 THEN CONVERT(float,Value) ELSE 0 END)) AS Clinical_Auditing_Tools,
                CONVERT(float, MAX(CASE WHEN md.fieldid= 7190 THEN CONVERT(float,Value) ELSE 0 END)) AS Clinical_Group_Sessions,
                CONVERT(float, MAX(CASE WHEN md.fieldid= 7191 THEN CONVERT(float,Value) ELSE 0 END)) AS Clinical_ABC_Data,
                CONVERT(float, MAX(CASE WHEN md.fieldid= 7211 THEN CONVERT(float,Value) ELSE 0 END)) AS Clinical_AGA,
                CONVERT(float, MAX(CASE WHEN md.fieldid= 4495 THEN CONVERT(float,Value) ELSE 0 END)) AS LMS_Core,
                CONVERT(float, MAX(CASE WHEN md.fieldid= 7175 THEN CONVERT(float,Value) ELSE 0 END)) AS HRIS_PTO,
                CONVERT(float, MAX(CASE WHEN md.fieldid= 8106 THEN CONVERT(float,Value) ELSE 0 END)) AS LMS_Course_Groups,
                CONVERT(float, MAX(CASE WHEN md.fieldid= 8286 THEN CONVERT(float,Value) ELSE 0 END)) AS PM_Reach_Me,
                CONVERT(float, MAX(CASE WHEN md.fieldid= 8999 THEN CONVERT(float,Value) ELSE 0 END)) AS PM_Redshift,
                CONVERT(float, MAX(CASE WHEN md.fieldid= 9155 THEN CONVERT(float,Value) ELSE 0 END)) AS HRIS_Benefits,
                CONVERT(float, MAX(CASE WHEN md.fieldid= 9156 THEN CONVERT(float,Value) ELSE 0 END)) AS HRIS_Assets
        FROM    public.contact_meta md
        WHERE   md.fieldid IN (4495,7172,7182,7192,7183,7184,7185,7186,7173,7187,7189,7190,7191,7175,7211,7212,8106,8286,8999,9155,9156)
                --AND md.ContactId = 75337
        GROUP BY
                md.contactid
                )s

--UNPIVOT
--(
--SeatCharge
--FOR Description IN (
  --      PM_Core,
    --    PM_RCM,
      --  PM_Advanced_RCM,
       -- PM_Payroll,
       -- PM_DXM,
       -- PM_Messaging,
       -- PM_Tasks,

        --HRIS_Assets)
--)u



WHERE           u.SeatCharge <> ''

        )a

所有功能都在工作，而不是UNPIVOT功能.

我知道redshift没有UNPIVOT功能.

所以现在我需要正确地重写它?

感谢您的帮助.

解决方案

请考虑从这种方法中借用的UNNEST(ARRAY(...))方法从RedShift最初衍生的PostgreSQL方言中炖制<.具体来说，首先在列的字符串文字名称数组上调用UNNEST，然后在实际列的数组上再次调用UNNEST，以在单独的行中返回指标和值.

SELECT  s.ContactId,
        UNNEST(ARRAY('PM_Core', 'PM_RCM', 'PM_Advanced_RCM', 'PM_Payroll', 'PM_DXM', 
                     'PM_Messaging', 'PM_Tasks', 'Clinical_Core', 'Clinical_Data_Collection',  
                     'Clinical_Auditing_Tools', 'Clinical_Group_Sessions', 'Clinical_Group_Sessions', 
                     'Clinical_ABC_Data', 'Clinical_AGA', 'LMS_Core', 'HRIS_PTO', 'LMS_Course_Groups',
                     'PM_Reach_Me', 'PM_Redshift', 'HRIS_Benefits', 'HRIS_Assets')) AS Description,
        UNNEST(ARRAY(PM_Core, PM_RCM, PM_Advanced_RCM, PM_Payroll, PM_DXM, 
                     PM_Messaging, PM_Tasks, Clinical_Core, Clinical_Data_Collection,  
                     Clinical_Auditing_Tools, Clinical_Group_Sessions, Clinical_Group_Sessions, 
                     Clinical_ABC_Data, Clinical_AGA, LMS_Core, HRIS_PTO, LMS_Course_Groups,
                     PM_Reach_Me, PM_Redshift, HRIS_Benefits, HRIS_Assets)) AS SeatCharge    
FROM
   (SELECT md.contactid,
           CONVERT(float, MAX(CASE WHEN md.fieldid= 7172 THEN CONVERT(float,Value) ELSE 0 END)) AS PM_Core,
           CONVERT(float, MAX(CASE WHEN md.fieldid= 7182 THEN CONVERT(float,Value) ELSE 0 END)) AS PM_RCM,
           CONVERT(float, MAX(CASE WHEN md.fieldid= 7192 THEN CONVERT(float,Value) ELSE 0 END)) AS PM_Advanced_RCM,
           CONVERT(float, MAX(CASE WHEN md.fieldid= 7183 THEN CONVERT(float,Value) ELSE 0 END)) AS PM_Payroll,
           CONVERT(float, MAX(CASE WHEN md.fieldid= 7184 THEN CONVERT(float,Value) ELSE 0 END)) AS PM_DXM,
           CONVERT(float, MAX(CASE WHEN md.fieldid= 7185 THEN CONVERT(float,Value) ELSE 0 END)) AS PM_Messaging,
           CONVERT(float, MAX(CASE WHEN md.fieldid= 7186 THEN CONVERT(float,Value) ELSE 0 END)) AS PM_Tasks,
           CONVERT(float, MAX(CASE WHEN md.fieldid= 7173 THEN CONVERT(float,Value) ELSE 0 END)) AS Clinical_Core,
           CONVERT(float, MAX(CASE WHEN md.fieldid= 7187 THEN CONVERT(float,Value) ELSE 0 END)) AS Clinical_Data_Collection,
           CONVERT(float, MAX(CASE WHEN md.fieldid= 7189 THEN CONVERT(float,Value) ELSE 0 END)) AS Clinical_Auditing_Tools,
           CONVERT(float, MAX(CASE WHEN md.fieldid= 7190 THEN CONVERT(float,Value) ELSE 0 END)) AS Clinical_Group_Sessions,
           CONVERT(float, MAX(CASE WHEN md.fieldid= 7191 THEN CONVERT(float,Value) ELSE 0 END)) AS Clinical_ABC_Data,
           CONVERT(float, MAX(CASE WHEN md.fieldid= 7211 THEN CONVERT(float,Value) ELSE 0 END)) AS Clinical_AGA,
           CONVERT(float, MAX(CASE WHEN md.fieldid= 4495 THEN CONVERT(float,Value) ELSE 0 END)) AS LMS_Core,
           CONVERT(float, MAX(CASE WHEN md.fieldid= 7175 THEN CONVERT(float,Value) ELSE 0 END)) AS HRIS_PTO,
           CONVERT(float, MAX(CASE WHEN md.fieldid= 8106 THEN CONVERT(float,Value) ELSE 0 END)) AS LMS_Course_Groups,
           CONVERT(float, MAX(CASE WHEN md.fieldid= 8286 THEN CONVERT(float,Value) ELSE 0 END)) AS PM_Reach_Me,
           CONVERT(float, MAX(CASE WHEN md.fieldid= 8999 THEN CONVERT(float,Value) ELSE 0 END)) AS PM_Redshift,
           CONVERT(float, MAX(CASE WHEN md.fieldid= 9155 THEN CONVERT(float,Value) ELSE 0 END)) AS HRIS_Benefits,
           CONVERT(float, MAX(CASE WHEN md.fieldid= 9156 THEN CONVERT(float,Value) ELSE 0 END)) AS HRIS_Assets
   FROM    public.contact_meta md
   WHERE   md.fieldid IN (4495,7172,7182,7192,7183,7184,7185,7186,7173,7187,7189,7190,7191,7175,7211,7212,8106,8286,8999,9155,9156)
   --AND   md.ContactId = 75337
   GROUP BY
           md.contactid
   ) s

随机数据演示

但是，请重新考虑枢轴，然后再取消枢轴.只需使用CASE有条件地创建指标列，然后进行聚合:

SELECT sub.contactid, sub.Description, MAX(sub."VALUE") As SeatCharge
FROM
       (SELECT md.contactid,
               CASE md.fieldid
                    WHEN 7172 THEN 'PM_Core'
                    WHEN 7182 THEN 'PM_RCM'
                    WHEN 7192 THEN 'PM_Advanced_RCM'
                    WHEN 7183 THEN 'PM_Payroll'
                    WHEN 7184 THEN 'PM_DXM'
                    WHEN 7185 THEN 'PM_Messaging'
                    WHEN 7186 THEN 'PM_Tasks'
                    WHEN 7173 THEN 'Clinical_Core'
                    WHEN 7187 THEN 'Clinical_Data_Collection'
                    WHEN 7189 THEN 'Clinical_Auditing_Tools'
                    WHEN 7190 THEN 'Clinical_Group_Sessions'
                    WHEN 7191 THEN 'Clinical_ABC_Data'
                    WHEN 7211 THEN 'Clinical_AGA'
                    WHEN 4495 THEN 'LMS_Core'
                    WHEN 7175 THEN 'HRIS_PTO'
                    WHEN 8106 THEN 'LMS_Course_Groups'
                    WHEN 8286 THEN 'PM_Reach_Me'
                    WHEN 8999 THEN 'PM_Redshift'
                    WHEN 9155 THEN 'HRIS_Benefits'
                    WHEN 9156 THEN 'HRIS_Assets'
                    ELSE NULL
               END AS Description,
               CONVERT(float, Value) AS VALUE
       FROM    public.contact_meta md
       WHERE   md.fieldid IN (4495, 7172, 7182, 7192, 7183, 7184,7185, 7186, 7173, 7187, 7189,
                              7190, 7191, 7175, 7211, 7212,8106, 8286, 8999, 9155, 9156)
       --AND   md.ContactId = 75337
       ) AS sub
GROUP BY sub.contactid, sub.Description

注意:将来，请确保合并一个 Description 值的查找表，您可以将这些ID加入这些表中，并避免冗长的CASE逻辑计算甚至冗长的IN子句./p>

I have SQL server script that needs to be converted to redshift.

I converted part of it

Here is part of the script where I have a problem with.

    SELECT  a.*,
        b.*

FROM    (

SELECT  u.ContactId,
        u.Description,
        CONVERT(Float,u.SeatCharge) AS SeatCharge
        --CAST(u.SeatCharge AS numeric (18,4)) AS SeatCharge,
        --CONVERT(INT, CASE WHEN IsNumeric(CONVERT(VARCHAR(12), u.SeatCharge)) = 1 then CONVERT(VARCHAR(12), u.SeatCharge) else 0 End)

FROM(

SELECT  md.contactid,
                CONVERT(float, MAX(CASE WHEN md.fieldid= 7172 THEN CONVERT(float,Value) ELSE 0 END)) AS PM_Core,
                CONVERT(float, MAX(CASE WHEN md.fieldid= 7182 THEN CONVERT(float,Value) ELSE 0 END)) AS PM_RCM,
                CONVERT(float, MAX(CASE WHEN md.fieldid= 7192 THEN CONVERT(float,Value) ELSE 0 END)) AS PM_Advanced_RCM,
                CONVERT(float, MAX(CASE WHEN md.fieldid= 7183 THEN CONVERT(float,Value) ELSE 0 END)) AS PM_Payroll,
                CONVERT(float, MAX(CASE WHEN md.fieldid= 7184 THEN CONVERT(float,Value) ELSE 0 END)) AS PM_DXM,
                CONVERT(float, MAX(CASE WHEN md.fieldid= 7185 THEN CONVERT(float,Value) ELSE 0 END)) AS PM_Messaging,
                CONVERT(float, MAX(CASE WHEN md.fieldid= 7186 THEN CONVERT(float,Value) ELSE 0 END)) AS PM_Tasks,
                CONVERT(float, MAX(CASE WHEN md.fieldid= 7173 THEN CONVERT(float,Value) ELSE 0 END)) AS Clinical_Core,
                CONVERT(float, MAX(CASE WHEN md.fieldid= 7187 THEN CONVERT(float,Value) ELSE 0 END)) AS Clinical_Data_Collection,
                CONVERT(float, MAX(CASE WHEN md.fieldid= 7189 THEN CONVERT(float,Value) ELSE 0 END)) AS Clinical_Auditing_Tools,
                CONVERT(float, MAX(CASE WHEN md.fieldid= 7190 THEN CONVERT(float,Value) ELSE 0 END)) AS Clinical_Group_Sessions,
                CONVERT(float, MAX(CASE WHEN md.fieldid= 7191 THEN CONVERT(float,Value) ELSE 0 END)) AS Clinical_ABC_Data,
                CONVERT(float, MAX(CASE WHEN md.fieldid= 7211 THEN CONVERT(float,Value) ELSE 0 END)) AS Clinical_AGA,
                CONVERT(float, MAX(CASE WHEN md.fieldid= 4495 THEN CONVERT(float,Value) ELSE 0 END)) AS LMS_Core,
                CONVERT(float, MAX(CASE WHEN md.fieldid= 7175 THEN CONVERT(float,Value) ELSE 0 END)) AS HRIS_PTO,
                CONVERT(float, MAX(CASE WHEN md.fieldid= 8106 THEN CONVERT(float,Value) ELSE 0 END)) AS LMS_Course_Groups,
                CONVERT(float, MAX(CASE WHEN md.fieldid= 8286 THEN CONVERT(float,Value) ELSE 0 END)) AS PM_Reach_Me,
                CONVERT(float, MAX(CASE WHEN md.fieldid= 8999 THEN CONVERT(float,Value) ELSE 0 END)) AS PM_Redshift,
                CONVERT(float, MAX(CASE WHEN md.fieldid= 9155 THEN CONVERT(float,Value) ELSE 0 END)) AS HRIS_Benefits,
                CONVERT(float, MAX(CASE WHEN md.fieldid= 9156 THEN CONVERT(float,Value) ELSE 0 END)) AS HRIS_Assets
        FROM    public.contact_meta md
        WHERE   md.fieldid IN (4495,7172,7182,7192,7183,7184,7185,7186,7173,7187,7189,7190,7191,7175,7211,7212,8106,8286,8999,9155,9156)
                --AND md.ContactId = 75337
        GROUP BY
                md.contactid
                )s

--UNPIVOT
--(
--SeatCharge
--FOR Description IN (
  --      PM_Core,
    --    PM_RCM,
      --  PM_Advanced_RCM,
       -- PM_Payroll,
       -- PM_DXM,
       -- PM_Messaging,
       -- PM_Tasks,

        --HRIS_Assets)
--)u



WHERE           u.SeatCharge <> ''

        )a

All is working, instead of UNPIVOT function.

As I know redshift doesn't have UNPIVOT function.

So now I need to rewrite it correctly?

Thank's for help.

解决方案

Consider the UNNEST(ARRAY(...)) approach, borrowed from this approach by @Stew from the PostgreSQL dialect which RedShift originally derives. Specifically, first call UNNEST on array of string literal names of columns and then again on array of actual columns to return indicator and value in separate rows.

SELECT  s.ContactId,
        UNNEST(ARRAY('PM_Core', 'PM_RCM', 'PM_Advanced_RCM', 'PM_Payroll', 'PM_DXM', 
                     'PM_Messaging', 'PM_Tasks', 'Clinical_Core', 'Clinical_Data_Collection',  
                     'Clinical_Auditing_Tools', 'Clinical_Group_Sessions', 'Clinical_Group_Sessions', 
                     'Clinical_ABC_Data', 'Clinical_AGA', 'LMS_Core', 'HRIS_PTO', 'LMS_Course_Groups',
                     'PM_Reach_Me', 'PM_Redshift', 'HRIS_Benefits', 'HRIS_Assets')) AS Description,
        UNNEST(ARRAY(PM_Core, PM_RCM, PM_Advanced_RCM, PM_Payroll, PM_DXM, 
                     PM_Messaging, PM_Tasks, Clinical_Core, Clinical_Data_Collection,  
                     Clinical_Auditing_Tools, Clinical_Group_Sessions, Clinical_Group_Sessions, 
                     Clinical_ABC_Data, Clinical_AGA, LMS_Core, HRIS_PTO, LMS_Course_Groups,
                     PM_Reach_Me, PM_Redshift, HRIS_Benefits, HRIS_Assets)) AS SeatCharge    
FROM
   (SELECT md.contactid,
           CONVERT(float, MAX(CASE WHEN md.fieldid= 7172 THEN CONVERT(float,Value) ELSE 0 END)) AS PM_Core,
           CONVERT(float, MAX(CASE WHEN md.fieldid= 7182 THEN CONVERT(float,Value) ELSE 0 END)) AS PM_RCM,
           CONVERT(float, MAX(CASE WHEN md.fieldid= 7192 THEN CONVERT(float,Value) ELSE 0 END)) AS PM_Advanced_RCM,
           CONVERT(float, MAX(CASE WHEN md.fieldid= 7183 THEN CONVERT(float,Value) ELSE 0 END)) AS PM_Payroll,
           CONVERT(float, MAX(CASE WHEN md.fieldid= 7184 THEN CONVERT(float,Value) ELSE 0 END)) AS PM_DXM,
           CONVERT(float, MAX(CASE WHEN md.fieldid= 7185 THEN CONVERT(float,Value) ELSE 0 END)) AS PM_Messaging,
           CONVERT(float, MAX(CASE WHEN md.fieldid= 7186 THEN CONVERT(float,Value) ELSE 0 END)) AS PM_Tasks,
           CONVERT(float, MAX(CASE WHEN md.fieldid= 7173 THEN CONVERT(float,Value) ELSE 0 END)) AS Clinical_Core,
           CONVERT(float, MAX(CASE WHEN md.fieldid= 7187 THEN CONVERT(float,Value) ELSE 0 END)) AS Clinical_Data_Collection,
           CONVERT(float, MAX(CASE WHEN md.fieldid= 7189 THEN CONVERT(float,Value) ELSE 0 END)) AS Clinical_Auditing_Tools,
           CONVERT(float, MAX(CASE WHEN md.fieldid= 7190 THEN CONVERT(float,Value) ELSE 0 END)) AS Clinical_Group_Sessions,
           CONVERT(float, MAX(CASE WHEN md.fieldid= 7191 THEN CONVERT(float,Value) ELSE 0 END)) AS Clinical_ABC_Data,
           CONVERT(float, MAX(CASE WHEN md.fieldid= 7211 THEN CONVERT(float,Value) ELSE 0 END)) AS Clinical_AGA,
           CONVERT(float, MAX(CASE WHEN md.fieldid= 4495 THEN CONVERT(float,Value) ELSE 0 END)) AS LMS_Core,
           CONVERT(float, MAX(CASE WHEN md.fieldid= 7175 THEN CONVERT(float,Value) ELSE 0 END)) AS HRIS_PTO,
           CONVERT(float, MAX(CASE WHEN md.fieldid= 8106 THEN CONVERT(float,Value) ELSE 0 END)) AS LMS_Course_Groups,
           CONVERT(float, MAX(CASE WHEN md.fieldid= 8286 THEN CONVERT(float,Value) ELSE 0 END)) AS PM_Reach_Me,
           CONVERT(float, MAX(CASE WHEN md.fieldid= 8999 THEN CONVERT(float,Value) ELSE 0 END)) AS PM_Redshift,
           CONVERT(float, MAX(CASE WHEN md.fieldid= 9155 THEN CONVERT(float,Value) ELSE 0 END)) AS HRIS_Benefits,
           CONVERT(float, MAX(CASE WHEN md.fieldid= 9156 THEN CONVERT(float,Value) ELSE 0 END)) AS HRIS_Assets
   FROM    public.contact_meta md
   WHERE   md.fieldid IN (4495,7172,7182,7192,7183,7184,7185,7186,7173,7187,7189,7190,7191,7175,7211,7212,8106,8286,8999,9155,9156)
   --AND   md.ContactId = 75337
   GROUP BY
           md.contactid
   ) s

Random Data Demo

However, reconsider pivoting and then unpivoting. Simply create your metric column conditionally with CASE and then aggregate:

SELECT sub.contactid, sub.Description, MAX(sub."VALUE") As SeatCharge
FROM
       (SELECT md.contactid,
               CASE md.fieldid
                    WHEN 7172 THEN 'PM_Core'
                    WHEN 7182 THEN 'PM_RCM'
                    WHEN 7192 THEN 'PM_Advanced_RCM'
                    WHEN 7183 THEN 'PM_Payroll'
                    WHEN 7184 THEN 'PM_DXM'
                    WHEN 7185 THEN 'PM_Messaging'
                    WHEN 7186 THEN 'PM_Tasks'
                    WHEN 7173 THEN 'Clinical_Core'
                    WHEN 7187 THEN 'Clinical_Data_Collection'
                    WHEN 7189 THEN 'Clinical_Auditing_Tools'
                    WHEN 7190 THEN 'Clinical_Group_Sessions'
                    WHEN 7191 THEN 'Clinical_ABC_Data'
                    WHEN 7211 THEN 'Clinical_AGA'
                    WHEN 4495 THEN 'LMS_Core'
                    WHEN 7175 THEN 'HRIS_PTO'
                    WHEN 8106 THEN 'LMS_Course_Groups'
                    WHEN 8286 THEN 'PM_Reach_Me'
                    WHEN 8999 THEN 'PM_Redshift'
                    WHEN 9155 THEN 'HRIS_Benefits'
                    WHEN 9156 THEN 'HRIS_Assets'
                    ELSE NULL
               END AS Description,
               CONVERT(float, Value) AS VALUE
       FROM    public.contact_meta md
       WHERE   md.fieldid IN (4495, 7172, 7182, 7192, 7183, 7184,7185, 7186, 7173, 7187, 7189,
                              7190, 7191, 7175, 7211, 7212,8106, 8286, 8999, 9155, 9156)
       --AND   md.ContactId = 75337
       ) AS sub
GROUP BY sub.contactid, sub.Description

NOTE: In future, be sure to incorporate a lookup table of Description values that you can join these ids to and avoid the long CASE logic calculation and even long IN clause.

这篇关于使用UNPIVOT将代码从SQL重写为Redshift的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆