กรณีศึกษา UDFs

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

Meghan Kwartler

IT Consultant

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

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

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

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

UDFs สำหรับการแปลงค่า

CREATE FUNCTION dbo.ConvertMileToMeter (@miles numeric)
RETURNS numeric
AS
BEGIN
RETURN (SELECT @miles * 1609.34)
END

CREATE FUNCTION dbo.ConvertCurrency (@Currency numeric, @ExchangeRate numeric)
RETURNS numeric
AS
BEGIN
RETURN (SELECT @ExchangeRate * @Currency)
END

SELECT TripDistance as 'MileDistance', 
dbo.ConvertMileToMeter (TripDistance) as 'MeterDistance', 
FareAmount as 'FareUSD', 
dbo.ConvertCurrency (FareAmount, '.78') as 'FareGBP' 
FROM dbo.YellowTripData

+--------------+---------------+---------+---------+
| MileDistance | MeterDistance | FareUSD | FareGBP |
|--------------+---------------+---------+---------+
|  1.10        | 1609          |  6      | 6       |
|  0.02        | 0             | 52      | 52      |
|  0.50        | 1609          |  4      | 4       |
|  7.75        | 12875         | 22      | 22      |
|  0.80        | 1609          |  6      | 6       |
|  0.90        | 1609          |  7      | 7       |
|  1.76        | 3219          |  7      | 7       |
|  8.47        | 12875         | 24      | 24      |
|  2.40        | 3219          | 10.50   | 11      |
| 12.60        | 20921         | 60      | 60      |
|  0.90        | 1609          |  7      | 7       |
+--------------+---------------+---------+---------+

ปรับปรุงฟังก์ชัน

ALTER FUNCTION dbo.ConvertMileToMeter (
  @miles numeric (18, 2)
) RETURNS numeric (18, 2) as BEGIN RETURN (
  SELECT @miles * 1609.34
) END;

ALTER FUNCTION dbo.ConvertCurrency (
  @Currency numeric (18, 2), 
  @ExchangeRate numeric(18, 2)
) RETURNS numeric (18, 2) AS BEGIN RETURN (
  SELECT @ExchangeRate * @Currency
) END;

แล้วกะการทำงานล่ะ?

CREATE FUNCTION dbo.GetShift (@Hour int)
RETURNS int
AS
BEGIN
RETURN (CASE 
    WHEN @Hour >= 0 AND @Hour < 9 THEN 1
    WHEN @Hour >= 9 AND @Hour < 18 THEN 2
    WHEN @Hour >= 18 AND @Hour < 24 THEN 3
END)
END;

ทดสอบกะการทำงาน

SELECT 
  DATENAME(hour, PickupDate) AS 'Hour', 
  dbo.GetShift (
    DATENAME(hour, PickupDate)
  ) AS 'Shift' 
FROM YellowTripData 
GROUP BY DATENAME(hour, PickupDate) 
ORDER BY 
  dbo.GetShift (
    DATENAME(hour, PickupDate)
  )

+------+-------+
| Hour | Shift |
|------+-------|
|  3   | 1     |
|  6   | 1     |
|  4   | 1     |
|  2   | 1     |
|  0   | 1     |
|  8   | 1     |
|  5   | 1     |
|  1   | 1     |
|  7   | 1     |
|  9   | 2     |
| 11   | 2     |
| 15   | 2     |
| 14   | 2     |
| ...  | ...   |
+------+-------|

ถึงคิวของคุณแล้ว!

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