以 Hot Deck 邏輯填補的工作日
計算每個星期幾的「總車資金額 ÷ 總行駛距離」。如果 TripDistance 為 0，請使用你在本章前面建立的 Hot Deck 插補函式。
本練習屬於課程
在 SQL Server 中撰寫函式與預存程序
練習說明
- 使用
DATENAME()與PickupDate來取得星期幾。 - 使用
AVG()計算每單位TripDistance的TotalAmount，並用CASE判斷當TripDistance大於0時選用其值；否則改用dbo.GetTripDistanceHotDeck()。 - 依
PickupDate的星期幾排序，且使'Monday'排在最前面。
動手互動練習
試著完成這個範例程式碼，體驗一下這個練習。
SELECT
-- Select the pickup day of week
DATENAME(weekday, PickupDate) as DayofWeek,
-- Calculate TotalAmount per TripDistance
CAST(AVG(TotalAmount/
-- Select TripDistance if it's more than 0
CASE WHEN TripDistance > 0 THEN TripDistance
-- Use GetTripDistanceHotDeck()
ELSE dbo.GetTripDistanceHotDeck() END) as decimal(10,2)) as 'AvgFare'
FROM YellowTripData
GROUP BY DATENAME(weekday, PickupDate)
-- Order by the PickupDate day of week
ORDER BY
___ ___ ___(weekday, PickupDate) = '___' THEN 1
___ ___(weekday, PickupDate) = '___' THEN 2
___ ___(weekday, PickupDate) = '___' THEN 3
___ ___(weekday, PickupDate) = '___' THEN 4
___ ___(weekday, PickupDate) = '___' THEN 5
___ ___(weekday, PickupDate) = '___' THEN 6
___ ___(weekday, PickupDate) = '___' THEN 7
END ASC;