使用UNPIVOT将代码从SQL重写为Redshift

使用UNPIVOT将代码从SQL重写为Redshift

考虑一下这种

UNNEST(ARRAY(...))

方法，该方法是@Stew从RedShift最初派生的PostgreSQL方言中借用的。具体来说，首先调用

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 BYmd.contactid   ) s

随机数据演示

---

但是，请重新考虑枢轴，然后再取消枢轴。只需有条件地创建指标列

CASE

，然后进行汇总：

SELECt sub.contactid, sub.Description, MAX(sub."VALUE") As SeatChargeFROM       (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 subGROUP BY sub.contactid, sub.Description

注意：将来，请确保合并 描述 值的查找表，您可以将这些ID加入这些表中，并避免冗长的

CASE

逻辑计算甚至是long

IN

子句。