案例研究：EDA 与插补

SQL Server 中的函数与存储过程编写

Meghan Kwartler

IT Consultant

出租车业务问题

欧盟私募股权公司寻求投资美国交通行业。
每个工作日各纽约行政区的"单位距离平均车费"、"行程数"与"总行程时长"是多少？
各行政区内应将哪些"上车地点"分配到各司机"班次"？

黄色出租车照片

基础 EDA

由于采集校准问题，分布式交易数据集中可能出现不可能的场景
- 未来日期
- 结束日期早于开始日期

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

数据插补

计算平均车费/行程距离时出现被零除错误
EDA 发现数百条行程距离为 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;

热卡插补（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 中的函数与存储过程编写