ケーススタディ：EDA と補完

SQL Server における関数とストアドプロシージャの作成

Meghan Kwartler

IT Consultant

タクシー配車のビジネス課題

EU のプライベート・エクイティが米国運輸セクターで投資先を探索
曜日×NYC 各区での「距離あたり平均運賃」「乗車数」「総乗車時間」は？
各区内で、各ドライバーの各シフトに割り当てる「乗車地」はどこか？

黄色いタクシーの写真

必須の EDA

分散トランザクションデータは、収集時の較正不良で不可能な値を含むことがある
- 未来日付
- 開始前に終了している日付

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

データ補完

Avg Fare/TripDistance 計算でゼロ除算が発生
EDA で Trip Distance=0 のレコードが多数見つかる
対処の補完法
- 平均値
- ホットデック
- 除外

平均値補完

欠損値を平均で置換
平均値自体は変わらない
他列との相関が高まりやすい

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;

ホットデック補完

欠損値をランダムに選んだ値で置換
FROM 句の TABLESAMPLE 句を使用

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 における関数とストアドプロシージャの作成