Étude de cas : EDA et imputation

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

Meghan Kwartler

IT Consultant

Problème métier : courses en taxi

Société européenne de capital-investissement cherchant une opportunité aux États‑Unis dans le transport.
Quelle est la course moyenne par distance, le nombre de courses et le temps total de course pour chaque borough de NYC, chaque jour de la semaine ?
Quelles zones de prise en charge du borough planifier pour chaque quart de conducteur ?

Photo d'un taxi jaune

EDA essentielle

Des jeux de données transactionnels distribués peuvent contenir des cas impossibles à cause de problèmes de calibration à la collecte
- Dates dans le futur
- Dates de fin avant les dates de début

SELECT * 
FROM CapitalBikeShare 
WHERE 
  StartDate > GetDate() 
  OR EndDate > GetDate() 
  OR StartDate > EndDate

Imputation des données

Division par zéro lors du calcul de Avg Fare/TripDistance
L'EDA révèle des centaines d'enregistrements TaxiRide avec Trip Distance = 0
Méthodes d'imputation pour corriger
- Moyenne
- Hot Deck
- Omission

Imputation par la moyenne

Remplacer la valeur manquante par la moyenne
Ne modifie pas la moyenne
Augmente les corrélations avec d'autres colonnes

CREATE PROCEDURE dbo.ImputeDurMean 
AS
BEGIN
DECLARE @AvgTripDuration AS float

SELECT @AvgTripDuration = AVG(Duration) 
FROM CapitalBikeShare
WHERE Duration > 0

UPDATE CapitalBikeShare
SET Duration =  @AvgTripDuration
WHERE Duration = 0
END;

Imputation Hot Deck

Valeur manquante remplacée par une valeur choisie aléatoirement
Clause TABLESAMPLE de la clause FROM

CREATE FUNCTION dbo.GetDurHotDeck()
RETURNS decimal (18,4)
AS BEGIN
RETURN (SELECT TOP 1 Duration
FROM CapitalBikeShare
TABLESAMPLE (1000 rows)
WHERE Duration >0)
END

SELECT 
  StartDate, 
  "TripDuration" = CASE WHEN Duration > 0 THEN Duration 
                        ELSE dbo.GetDurHotDeck() END 
FROM CapitalBikeShare;

SELECT 
  DATENAME(weekday, StartDate) AS DayofWeek, 
  AVG(Duration) AS 'AvgDuration' 
FROM CapitalBikeShare 
WHERE Duration > 0 
GROUP BY DATENAME(weekday, StartDate) 
ORDER BY AVG(Duration) desc

+-----------+-------------+
| DayofWeek | AvgDuration |
|-----------+-------------|
| Saturday  | 1476        |
| Sunday    | 1391        |
| Monday    |  979        |
| Friday    |  957        |
| Thursday  |  956        |
| Wednesday |  807        |
| Tuesday   |  763        |
+-----------+-------------|

À vous de jouer !

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