กรณีศึกษา Stored Procedures

การเขียนฟังก์ชันและ Stored Procedures ใน SQL Server

Meghan Kwartler

IT Consultant

โจทย์ธุรกิจแท็กซี่

บริษัท Private Equity ในยุโรปกำลังมองหาโอกาสลงทุนในธุรกิจขนส่งของสหรัฐฯ
ค่าโดยสารเฉลี่ยต่อระยะทาง, จำนวนเที่ยว และ เวลาเดินทางรวม ของแต่ละ Borough ใน NYC ในแต่ละวันของสัปดาห์เป็นเท่าไร?
ควรกำหนด จุดรับผู้โดยสาร ใดภายใน Borough สำหรับแต่ละ กะ ของคนขับ?

ภาพรถแท็กซี่สีเหลือง

วิวัฒนาการของ 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

ถึงตาคุณแล้ว!

การเขียนฟังก์ชันและ Stored Procedures ใน SQL Server