我目前有以下代碼
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
問題是 NULLS 正在被洗掉，所以我錯過了一些原始行。知道如何解決這個問題嗎？我已經讀過橫向交叉連接是一種可能的方式，但我無法掌握如何實作它。請幫忙。
uj5u.com熱心網友回復：
UNPIVOT洗掉空值。您可以改為使用CROSS APPLY (VALUESunpivot，這在任何情況下都更加靈活。
with physician_diag as (
SELECT
baseentityid,
eventdate,
locationid,
teamid,
average_muac,
child_age,
child_gender,
physician_diagnosis,
dignosis_data
FROM vr.event_physician_visit epv
CROSS APPLY (VALUES
('well_baby', epv.well_baby),
('severe_pneumonia', epv.severe_pneumonia)
) v(physician_diagnosis, dignosis_data)
),
final as (
select *,
dense_rank() OVER (PARTITION BY baseentityid ORDER BY eventdate) AS rn
from physician_diag
)
.....
我注意到您可能會在取消透視DENSE_RANK 之前放置它，這可能會使其達到索引。在這種情況下，結果應該是相同的。
with ranked as (
select *,
dense_rank() OVER (PARTITION BY epv.baseentityid ORDER BY epv.eventdate) AS rn
from vr.event_physician_visit epv
),
final as (
SELECT
baseentityid,
eventdate,
locationid,
teamid,
average_muac,
child_age,
child_gender,
physician_diagnosis,
dignosis_data
FROM ranked epv
CROSS APPLY (VALUES
('well_baby', epv.well_baby),
('severe_pneumonia', epv.severe_pneumonia)
) v(physician_diagnosis, dignosis_data)
)
.....
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/504871.html