NYC 行政區統計 SP
現在是把你在本課學到的內容派上用場的時候了。請撰寫一個預存程序，解決計程車營運個案的第一個目標。為每個紐約市行政區與每個星期幾計算 AvgFarePerKM、RideCount 與 TotalRideMin。在與利害關係人討論後，你需要忽略 TripDistance 為 0 的紀錄。
本練習屬於課程
在 SQL Server 中撰寫函式與預存程序
練習說明
- 以上車的星期幾與
Borough進行 SELECT 與 GROUP BY。 - 使用
dbo.ConvertDollar()與dbo.ConvertMiletoKM()計算AvgFarePerKM，並套用兌換成歐元的 0.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;