Практический пример: разведочный анализ и импутация

Написание функций и хранимых процедур в SQL Server

Meghan Kwartler

IT Consultant

Бизнес-задача: сервис такси

Европейская компания прямых инвестиций ищет возможности в транспортной отрасли США.
Каков средний тариф за километр, число поездок и суммарное время поездок по каждому району Нью-Йорка в разбивке по дням недели?
Какие точки посадки в каждом районе следует включить в расписание смен водителей?

Основы разведочного анализа

Распределённые транзакционные наборы данных могут содержать невозможные значения из-за ошибок калибровки при сборе данных
- Даты в будущем
- Дата окончания раньше даты начала

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

Импутация данных

Деление на ноль при вычислении среднего тарифа / расстояния поездки
Разведочный анализ выявил сотни записей TaxiRide с расстоянием поездки = 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