シフト別のピックアップ地点
ビジネスケースの2つ目の目的に取り組みます。NYCの各Borough内で、ピックアップ地点とシフトごとのAvgFarePerKM、RideCount、TotalRideMinはいくつでしょうか。
この演習はコースの一部です
SQL Server における関数とストアドプロシージャの作成
演習の手順
- 入力パラメーターとして
@Borough nvarchar(30)を受け取り、Boroughの値が一致するレコードに限定する、cuspPickupZoneShiftStatsという名前のストアドプロシージャを作成します。 PickupDateのhourをdbo.GetShiftNumber()関数に渡して'Shift'を算出します。DATEPART関数を使って、PickupDateのhour部分のみを取得します。PickupDateの曜日、シフト、Zoneでグループ化します。PickupDateの曜日（Mondayを先頭）、シフト、TotalRideMinの順に並べ替えます。
実践的なインタラクティブ演習
このサンプルコードを完成させて、この演習に挑戦してみましょう。
-- Create the stored procedure
CREATE PROCEDURE dbo.cuspPickupZoneShiftStats
-- Specify @Borough parameter
@Borough nvarchar(30)
AS
BEGIN
SELECT
DATENAME(WEEKDAY, PickupDate) as 'Weekday',
-- Calculate the shift number
___.___(___(___, ___)) as 'Shift',
Zone.Zone as 'Zone',
FORMAT(AVG(dbo.ConvertDollar(TotalAmount, .77)/dbo.ConvertMiletoKM(TripDistance)), 'c', 'de-de') AS 'AvgFarePerKM',
FORMAT(COUNT (ID),'n', 'de-de') as 'RideCount',
FORMAT(SUM(DATEDIFF(SECOND, PickupDate, DropOffDate))/60, 'n', 'de-de') as 'TotalRideMin'
FROM YellowTripData
INNER JOIN TaxiZoneLookup as Zone on PULocationID = Zone.LocationID
WHERE
dbo.ConvertMiletoKM(TripDistance) > 0 AND
Zone.Borough = @Borough
GROUP BY
DATENAME(WEEKDAY, PickupDate),
-- Group by shift
___.___(___(___, ___)),
Zone.Zone
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,
-- Order by shift
___.___(___(___, ___)),
SUM(DATEDIFF(SECOND, PickupDate, DropOffDate))/60 DESC
END;