กรณีศึกษา EDA & การแทนค่าที่หายไป

การเขียนฟังก์ชันและ Stored Procedures ใน SQL Server

Meghan Kwartler

IT Consultant

โจทย์ธุรกิจแท็กซี่

บริษัท Private Equity ในยุโรปกำลังมองหาโอกาสลงทุนในธุรกิจขนส่งของสหรัฐฯ
ค่าโดยสารเฉลี่ยต่อระยะทาง, จำนวนเที่ยว และ เวลารวมทั้งหมด ของแต่ละเขตใน NYC ในแต่ละวันของสัปดาห์คือเท่าไร?
ควรกำหนด จุดรับผู้โดยสาร ภายในเขตให้กับ กะ ของคนขับแต่ละกะอย่างไร?

ภาพแท็กซี่สีเหลือง

EDA เบื้องต้น

ชุดข้อมูลธุรกรรมแบบกระจายอาจมีสถานการณ์ที่เป็นไปไม่ได้ เนื่องจากปัญหาการปรับเทียบการเก็บข้อมูล
- วันที่อยู่ในอนาคต
- วันที่สิ้นสุดก่อนวันที่เริ่มต้น

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

การแทนค่าที่หายไป

เกิดข้อผิดพลาดหารด้วยศูนย์เมื่อคำนวณค่าโดยสารเฉลี่ย/ระยะทาง
EDA พบข้อมูลการเดินทาง TaxiRide หลายร้อยรายการที่มี Trip Distance = 0
วิธีการแทนค่าที่หายไปเพื่อแก้ปัญหา
- Mean
- Hot Deck
- Omission

การแทนค่าด้วยค่าเฉลี่ย

แทนค่าที่หายไปด้วยค่าเฉลี่ย
ไม่เปลี่ยนแปลงค่าเฉลี่ยของข้อมูล
เพิ่มความสัมพันธ์กับคอลัมน์อื่น

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

ค่าที่หายไปจะถูกแทนด้วยค่าที่เลือกแบบสุ่ม
ใช้ clause TABLESAMPLE ใน clause 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        |
+-----------+-------------|

ถึงตาคุณแล้ว!

การเขียนฟังก์ชันและ Stored Procedures ใน SQL Server