结合 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;