Étude de cas : UDF

Écrire des fonctions et des procédures stockées dans SQL Server

Meghan Kwartler

IT Consultant

Problème métier : courses de taxi

Société européenne de capital-investissement cherchant une opportunité dans le transport aux États‑Unis.
Quel est le tarif moyen par distance, le nombre de courses et la durée totale des courses pour chaque arrondissement de NYC chaque jour de la semaine ?

photo d'un taxi jaune

Problème métier : courses de taxi

Société européenne de capital-investissement cherchant une opportunité dans le transport aux États‑Unis.
Quel est le tarif moyen par distance, le nombre de courses et la durée totale des courses pour chaque arrondissement de NYC chaque jour de la semaine ?
Quels lieux de prise en charge dans l'arrondissement planifier pour chaque poste de conducteur ?

photo d'un taxi jaune

UDF de conversion

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       |
+--------------+---------------+---------+---------+

Itérer

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;

Et les postes ?

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;

Tester les postes

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     |
| ...  | ...   |
+------+-------|

À vous de jouer !

Écrire des fonctions et des procédures stockées dans SQL Server