Nghiên cứu tình huống: EDA & bù thiếu

Viết Hàm và Thủ Tục Lưu Trữ trong SQL Server

Meghan Kwartler

IT Consultant

Bài toán kinh doanh taxi

Quỹ PE châu Âu tìm cơ hội đầu tư vào Vận tải Mỹ.
Trung bình giá vé/km, số chuyến & tổng thời gian chuyến cho từng quận NYC theo từng ngày trong tuần?
Nên lên lịch các điểm đón nào trong quận cho từng ca tài xế?

Ảnh taxi vàng

EDA thiết yếu

Dữ liệu giao dịch phân tán có thể chứa kịch bản bất khả do lỗi hiệu chuẩn thu thập
- Ngày ở tương lai
- Ngày kết thúc trước ngày bắt đầu

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

Bù thiếu dữ liệu

Lỗi chia cho 0 khi tính Giá vé TB/Quãng đường
EDA phát hiện hàng trăm bản ghi TaxiRide có Quãng đường = 0
Cách bù thiếu để xử lý
- Trung bình
- Hot Deck
- Loại bỏ

Bù thiếu bằng trung bình

Thay giá trị thiếu bằng trung bình
Không làm đổi giá trị trung bình
Tăng tương quan với cột khác

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;

Bù thiếu Hot Deck

Giá trị thiếu được gán ngẫu nhiên
Mệnh đề TABLESAMPLE trong 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        |
+-----------+-------------|

Đến lượt bạn!

Viết Hàm và Thủ Tục Lưu Trữ trong SQL Server