ケーススタディ：ストアドプロシージャ

SQL Server における関数とストアドプロシージャの作成

Meghan Kwartler

IT Consultant

タクシー乗車のビジネス課題

EUのプライベート・エクイティ企業が、米国運輸セクターで投資先を探索。
各曜日×NYCの各行政区について、距離あたりの平均運賃、乗車数、総乗車時間は？
各行政区内で、運転手の各シフトに割り当てるべき乗車地はどこか？

黄色タクシーの写真

SPの発展

--Query detail level with UDFs
SELECT 
  DATENAME (weekday, PickupDate) as 'Weekday', 
  PickupDate, 
  DropOffDate, 
  TotalAmount, 
  TripDistance, 
  dbo.ConvertDollar(TotalAmount,.88)/ dbo.ConvertMileToKm(TripDistance) as 'EuroFarePerKM', 
  DATEDIFF(SECOND, PickupDate, DropOffDate)/ 60 as 'TotalRideMin' 
FROM YellowTripData 
WHERE TripDistance > 0

SELECT DATENAME(weekday, PickupDate) as 'Weekday', 
     Zone.Borough as 'PickupBorough',
     AVG(dbo.ConvertDollar(TotalAmount, .77)/
     dbo.ConvertMiletoKM(TripDistance)))
     AS 'AvgFarePerKM',
     COUNT (ID) as 'RideCount',
     SUM(DATEDIFF(SECOND, PickupDate, DropOffDate)/60) as 'TotalRideMin'
FROM YellowTripData
INNER JOIN TaxiZoneLookup AS Zone 
ON PULocationID = Zone.LocationID 
WHERE dbo.ConvertMiletoKM(TripDistance) > 0 
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,  
AVG(dbo.ConvertDollar(TotalAmount, .77)/dbo.ConvertMiletoKM(TripDistance)) DESC;

「最後」のステップ

CREATE OR ALTER PROCEDURE dbo.cuspPickupZoneShiftStats
@Borough nvarchar(30)
AS
BEGIN
.....
END

DROP PROCEDURE IF EXISTS dbo.cuspPickupZoneShiftStats
GO
CREATE PROCEDURE dbo.cuspPickupZoneShiftStats
@Borough nvarchar(30)
AS
BEGIN
....
END

あなたの番です！

SQL Server における関数とストアドプロシージャの作成