解决UNPIVOT移除NULL值导致丢失原始行的技术问题

原始SQL代码 #

with physician_diag as (
   SELECT baseentityid, eventdate,locationid,teamid,average_muac,child_age,child_gender,physician_diagnosis, dignosis_data
   FROM [VITAL_DWH].[vr].[event_physician_visit]   
UNPIVOT  
   (dignosis_data FOR physician_diagnosis  IN   
      (
     well_baby,
severe_pneumonia
)  
) AS unpvt 
),
final as (
-- Final
select *, dense_rank() OVER (PARTITION BY baseentityid ORDER BY eventdate) AS rn from (
select * from physician_diag  
 ) F

问题说明 #

上述SQL使用UNPIVOT做行转列时，会自动过滤掉well_baby或severe_pneumonia字段为NULL的原始行，导致部分数据丢失。可以用CROSS JOIN LATERAL结合VALUES子句替代，实现保留所有包含NULL值的原始行的行转列效果。

修改后的SQL代码（保留NULL行） #

with physician_diag as (
   SELECT 
       baseentityid, 
       eventdate,
       locationid,
       teamid,
       average_muac,
       child_age,
       child_gender,
       physician_diagnosis,
       dignosis_data
   FROM [VITAL_DWH].[vr].[event_physician_visit]
   -- 用CROSS JOIN LATERAL + VALUES替代UNPIVOT，保留NULL值
   CROSS JOIN LATERAL (
       VALUES
           ('well_baby', well_baby),
           ('severe_pneumonia', severe_pneumonia)
   ) AS unpvt(physician_diagnosis, dignosis_data)
),
final as (
-- Final
select *, dense_rank() OVER (PARTITION BY baseentityid ORDER BY eventdate) AS rn 
from physician_diag
)
-- 根据需求添加最终查询逻辑
select * from final;

逻辑说明 #

• CROSS JOIN LATERAL会对每一行原始数据，与VALUES中的每一组值做关联，无论字段是否为NULL，都会生成对应的行。
• VALUES子句的每一项对应原UNPIVOT中的列名和字段值：第一个元素是诊断名称（映射physician_diagnosis），第二个是对应字段的实际值（映射dignosis_data）。
• 这种方式不会过滤NULL值，完美保留所有原始行的信息。

内容的提问来源于stack exchange，提问作者analyst92