การคำนวณค่ามัธยฐานใน SQL Server
SQL Server ไม่มีฟังก์ชัน MEDIAN() โดยตรง แต่มี PERCENTILE_CONT() ซึ่งใกล้เคียงที่สุด โดยฟังก์ชันนี้จะหาค่า ณ เปอร์เซ็นไทล์ที่ n ในชุดข้อมูล
เราต้องการดูว่าค่ามัธยฐาน (Median) ต่างจากค่าเฉลี่ย (Mean) มากน้อยเพียงใดในแต่ละประเภทเหตุการณ์ในชุดข้อมูล Incident Rollup โดยเปรียบเทียบฟังก์ชัน AVG() จากแบบฝึกหัดก่อนหน้ากับ PERCENTILE_CONT() ฟังก์ชันทั้งสองนี้เป็น Window Function ซึ่งจะอธิบายรายละเอียดเพิ่มเติมในบทที่ 4 สำหรับตอนนี้ให้รู้ว่า PERCENTILE_CONT() รับพารามิเตอร์เป็นค่าเปอร์เซ็นไทล์ (ทศนิยมในช่วง 0. ถึง 1.) ค่าเปอร์เซ็นไทล์นี้ต้องอยู่ภายในกลุ่มที่เรียงลำดับแล้วในส่วน WITHIN GROUP และต้องกำหนดขอบเขตในส่วน OVER หากต้องการแบ่งพาร์ติชันข้อมูล ในส่วน WITHIN GROUP ต้องเรียงลำดับตามคอลัมน์ที่ต้องการหาค่าเปอร์เซ็นไทล์ที่ 50
แบบฝึกหัดนี้เป็นส่วนหนึ่งของหลักสูตร
การวิเคราะห์อนุกรมเวลาใน SQL Server
คำแนะนำการฝึกหัด
- ใส่ค่าที่หายไปในฟังก์ชัน
PERCENTILE_CONT() - ในส่วน
WITHIN GROUP()ให้เรียงลำดับตามจำนวนเหตุการณ์จากมากไปน้อย - ในส่วน
OVER()ให้ Partition ตามIncidentType(ค่าข้อความจริง ไม่ใช่ ID)
แบบฝึกหัดเชิงโต้ตอบแบบลงมือทำ
ลองทำแบบฝึกหัดนี้โดยเติมโค้ดตัวอย่างนี้ให้สมบูรณ์
SELECT DISTINCT
it.IncidentType,
AVG(CAST(ir.NumberOfIncidents AS DECIMAL(4,2)))
OVER(PARTITION BY it.IncidentType) AS MeanNumberOfIncidents,
--- Fill in the missing value
PERCENTILE_CONT(___)
-- Inside our group, order by number of incidents DESC
WITHIN GROUP (ORDER BY ir.___ DESC)
-- Do this for each IncidentType value
OVER (PARTITION BY it.___) AS MedianNumberOfIncidents,
COUNT(1) OVER (PARTITION BY it.IncidentType) AS NumberOfRows
FROM dbo.IncidentRollup ir
INNER JOIN dbo.IncidentType it
ON ir.IncidentTypeID = it.IncidentTypeID
INNER JOIN dbo.Calendar c
ON ir.IncidentDate = c.Date
WHERE
c.CalendarQuarter = 2
AND c.CalendarYear = 2020;