Étude de cas : EDA et imputation

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

Meghan Kwartler

IT Consultant

Problème d'affaires : trajets en taxi

Société européenne de capital-investissement cherchant une occasion d'investissement dans le transport aux É.-U.
Quelle est la tarification moyenne par distance, le nombre de courses et la durée totale pour chaque arrondissement de NYC, par jour de la semaine ?
Quels lieux de prise en charge dans l'arrondissement planifier pour chaque quart de travail de conducteur ?

Photo d'un taxi jaune

EDA essentielle

Des ensembles de données transactionnels distribués peuvent contenir des cas impossibles à cause de problèmes d'étalonnage à 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

Erreur de division par zéro en calculant Avg Fare/TripDistance
L'EDA révèle des centaines d'enregistrements TaxiRide avec Trip Distance = 0
Méthodes d'imputation pour résoudre
- 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 au hasard
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