Estudo de caso: stored procedures

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 busca oportunidades nos EUA em Transporte.
Qual é a tarifa média por distância, número de corridas e tempo total por bairro de NYC em cada dia da semana?
Quais locais de embarque em cada bairro devem ser programados para cada turno de motoristas?

Foto de táxi amarelo

Evolução de uma 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;

"Último" passo

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

Sua vez!

Escrevendo Funções e Procedures no SQL Server