Кейс: EDA та імпутація

Написання функцій і збережених процедур у SQL Server

Meghan Kwartler

IT Consultant

Бізнес-задача для таксі

Приватна інвесткомпанія ЄС шукає можливості в транспорті США.
Які середні витрати на милю, кількість поїздок і загальний час поїздок для кожного боро Нью-Йорка в кожен день тижня?
Які місця посадки в межах боро слід планувати для кожної зміни водія?

Фото жовтого таксі

Базова EDA

Розподілені транзакційні набори даних можуть містити неможливі сценарії через проблеми калібрування збору даних
- Дати в майбутньому
- Кінцеві дати раніше початкових

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

Імпутація даних

Ділення на нуль під час обчислення Avg Fare/TripDistance
EDA виявляє сотні записів TaxiRide з Trip Distance = 0
Методи імпутації даних для розвʼязання
- Середнє
- Hot Deck
- Пропуск

Імпутація середнім

Замінити пропущене значення на середнє
Не змінює середнє значення
Підвищує кореляції з іншими стовпцями

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;

Імпутація Hot Deck

Пропущене значення замінюється випадково вибраним значенням
Оператор TABLESAMPLE у 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        |
+-----------+-------------|

Ваша черга!

Написання функцій і збережених процедур у SQL Server