Estudo de caso: UDFs

Escrevendo Funções e Procedures no SQL Server

Meghan Kwartler

IT Consultant

Problema de negócio: corridas de táxi

Empresa europeia de private equity buscando oportunidade no setor de Transporte nos EUA.
Qual é a tarifa média por distância, número de corridas e tempo total de corrida por distrito de NYC em cada dia da semana?

Foto de táxi amarelo

Problema de negócio: corridas de táxi

Empresa europeia de private equity buscando oportunidade no setor de Transporte nos EUA.
Qual é a tarifa média por distância, número de corridas e tempo total de corrida por distrito de NYC em cada dia da semana?
Quais locais de embarque em cada distrito devem ser programados para cada turno de motorista?

Foto de táxi amarelo

UDFs de conversão

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

Iterar

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;

E os turnos?

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;

Testar turnos

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

Sua vez!

Escrevendo Funções e Procedures no SQL Server