纽约市行政区统计 SP
现在是检验您在本课程中所学的时候了。请编写一个存储过程，解决出租车出行业务案例的第一个目标。为每个纽约市行政区和每个工作日计算 AvgFarePerKM、RideCount 和 TotalRideMin。与相关方讨论后，您应当忽略 TripDistance 为零的记录。
本练习是课程的一部分
SQL Server 中的函数与存储过程编写
练习说明
- 按上车的工作日与
Borough进行选择并分组。 - 使用
dbo.ConvertDollar()和dbo.ConvertMiletoKM()计算AvgFarePerKM，并采用兑欧元汇率.88。 - 将
AvgFarePerKM显示为德语地区的货币格式，将RideCount和TotalRideMin显示为德语地区的数字格式。 - 省略
TripDistance为0的记录。
交互式实操练习
通过完成这段示例代码来试试这个练习。
CREATE OR ALTER PROCEDURE dbo.cuspBoroughRideStats
AS
BEGIN
SELECT
-- Calculate the pickup weekday
___(weekday, PickupDate) AS 'Weekday',
-- Select the Borough
Zone.Borough AS 'PickupBorough',
-- Display AvgFarePerKM as German currency
___(AVG(dbo.___(TotalAmount, .88)/dbo.___(TripDistance)), '_', 'de-de') AS '___',
-- Display RideCount in the German format
___(COUNT(ID), '_', 'de-de') AS '___',
-- Display TotalRideMin in the German format
___(SUM(DATEDIFF(SECOND, PickupDate, DropOffDate))/60, '_', 'de-de') AS '___'
FROM YellowTripData
INNER JOIN TaxiZoneLookup AS Zone
ON PULocationID = Zone.LocationID
-- Only include records where TripDistance is greater than 0
___ ___ > ___
-- Group by pickup weekday and Borough
GROUP BY DATENAME(WEEKDAY, PickupDate), Zone.Borough
ORDER BY CASE WHEN DATENAME(WEEKDAY, PickupDate) = 'Monday' THEN 1
WHEN DATENAME(WEEKDAY, PickupDate) = 'Tuesday' THEN 2
WHEN DATENAME(WEEKDAY, PickupDate) = 'Wednesday' THEN 3
WHEN DATENAME(WEEKDAY, PickupDate) = 'Thursday' THEN 4
WHEN DATENAME(WEEKDAY, PickupDate) = 'Friday' THEN 5
WHEN DATENAME(WEEKDAY, PickupDate) = 'Saturday' THEN 6
WHEN DATENAME(WEEKDAY, PickupDate) = 'Sunday' THEN 7 END,
SUM(DATEDIFF(SECOND, PickupDate, DropOffDate))/60
DESC
END;