案例研析：EDA 與插補

在 SQL Server 中撰寫函式與預存程序

Meghan Kwartler

IT Consultant

計程車營運問題

歐盟私募股權公司尋找美國運輸業的投資機會。
每週各天，NYC 各行政區的「每距離平均車資」、「乘車次數」與「總乘車時間」是多少？
各行政區內哪些「上車地點」應安排到各駕駛「班次」？

黃色計程車照片

必要的 EDA

分散式交易資料集可能因蒐集校準問題而出現不可能情境
- 日期在未來
- 結束時間早於開始時間

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

資料插補

計算 Avg Fare/TripDistance 時發生除以 0 錯誤
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）

將遺漏值設為隨機抽取的值
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 中撰寫函式與預存程序