Estudo de caso: EDA e imputação

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 oportunidade nos EUA em Transporte.
Qual é a tarifa média por distância, o nº de corridas e o tempo total de corrida por bairro de NYC em cada dia da semana?
Quais locais de embarque no bairro devem ser escalados para cada turno de motorista?

Foto de um táxi amarelo

EDA essencial

Conjuntos de dados transacionais distribuídos podem conter cenários impossíveis por falhas de calibração na coleta
- Datas no futuro
- Data de término antes da data de início

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

Imputação de dados

Erro de divisão por zero ao calcular Tarifa Média/TripDistance
A EDA revela centenas de corridas com Trip Distance = 0
Métodos de imputação para resolver
- Média
- Hot Deck
- Omissão

Imputação pela média

Substituir o valor faltante pela média
Não altera o valor médio
Aumenta correlações com outras colunas

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;

Imputação Hot Deck

Valor faltante vira um valor selecionado aleatoriamente
Cláusula TABLESAMPLE em 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        |
+-----------+-------------|

Sua vez!

Escrevendo Funções e Procedures no SQL Server