我正在尝试用case/then语句创建一个标志列,我不想按带有估计\u交货\u结束\u日期和最后\u交货\u日期的数据分组。
--(CASE WHEN ol. ESTIMATED_DELIVERY_END_DATE >= s.LAST_DELIVERED_DATE THEN "Delivery on time" ELSE "Cargo to Delivery" END) As Metrik
EXTRACT(MONTH FROM CAST(DATE(s.LAST_DELIVERED_DATE) AS DATE)) As Month,
s.SHIPMENT_CITY_NAME AS City ,
cp.CARGO_PROVIDER_CODE,
st.STOCK_TYPE_DESC,
s.WHO_PAYS,
ROUND(AVG(abs((date_diff(CAST(DATE(s.CARGO_SHIPMENT_DATE) AS DATE),CAST(DATE(s.LAST_DELIVERED_DATE) AS DATE),DAY)))*24 +
abs((TIME_DIFF(CAST(s.CARGO_SHIPMENT_DATE AS TIME),CAST(s.LAST_DELIVERED_DATE AS TIME),MINUTE)))/60)) AS Deger,
COUNT(DISTINCT(CASE WHEN s.SHIPMENT_TYPE_SK=1002 THEN s.SHIPMENT_ID END)) AS Inbound_Delivery,
COUNT(DISTINCT(CASE WHEN s.SHIPMENT_TYPE_SK=1001 THEN s.SHIPMENT_ID END)) AS Outbound_Delivery,
(COUNT(DISTINCT(CASE WHEN s.SHIPMENT_TYPE_SK=1002 THEN s.SHIPMENT_ID END))+
COUNT(DISTINCT(CASE WHEN s.SHIPMENT_TYPE_SK=1001 THEN s.SHIPMENT_ID END))) As Total_Delivery,
Sum(DISTINCT(CASE WHEN N.SCORE IN (1,2,3,4,5,6) AND N.QUESTION_SK IN (1,1003,2002,5002) THEN N.SCORE END)) AS Detractors,
Sum(DISTINCT(CASE WHEN N.SCORE IN (9,10) AND N.QUESTION_SK IN (1,1003,2002,5002) THEN N.SCORE END)) AS Promoters,
COUNT(DISTINCT(CASE WHEN N.SCORE IN (1,2,3,4,5,6,7,8,9,10) THEN N.SHIPMENT_NUMBER END)) AS Total_Respondents,
SAFE_DIVIDE(Sum(DISTINCT(CASE WHEN N.SCORE IN (9,10) AND N.QUESTION_SK IN (1,1003,2002,5002) THEN N.SCORE END))-
(Sum(DISTINCT(CASE WHEN N.SCORE IN (1,2,3,4,5,6) AND N.QUESTION_SK IN (1,1003,2002,5002) THEN N.SCORE END))),COUNT(DISTINCT(CASE WHEN N.SCORE IN (1,2,3,4,5,6,7,8,9,10) THEN N.SHIPMENT_NUMBER END))) As NPS_SCORE,
--(CASE WHEN ol. ESTIMATED_DELIVERY_END_DATE >= s.LAST_DELIVERED_DATE THEN "Delivery on time" ELSE "Cargo to Delivery" END) As Metrik
FROM `dsm-data.pbl_trendyol.SHIPMENT` s
LEFT JOIN `dsm-data.pbl_trendyol.cargo_provider` cp ON
s.SHIPMENT_CARGO_PROVIDER_SK=cp.CARGO_PROVIDER_SK
LEFT JOIN `dsm-data.dim_charon.DIM_STOCK_TYPE` st ON s.SHIPMENT_STOCK_TYPE_SK=st.STOCK_TYPE_SK
LEFT JOIN `dsm-data.pbl_trendyol.NPS` n ON s.SHIPMENT_ID=n.SHIPMENT_NUMBER
LEFT JOIN `dsm-data.dim_marketplace.DIM_ORDER_LINE` ol ON s.SHIPMENT_ID=ol.SHIPMENT_NUMBER
WHERE LAST_DELIVERED_DATE is not null AND CARGO_SHIPMENT_DATE IS NOT NULL
Group by cp.CARGO_PROVIDER_CODE,st.STOCK_TYPE_DESC, Year, Month,s.SHIPMENT_CITY_NAME,s.WHO_PAYS;```
