Obliczanie mediany w SQL Server
W SQL Server nie ma funkcji MEDIAN(). Najbliższym odpowiednikiem jest PERCENTILE_CONT(), która wyznacza wartość odpowiadającą n-temu percentylowi w zbiorze danych.
Chcemy sprawdzić, jak bardzo mediana różni się od średniej w zależności od typu zdarzenia w naszym zbiorze zestawień zdarzeń. W tym celu porównamy funkcję AVG() z poprzedniego ćwiczenia z funkcją PERCENTILE_CONT(). Są to funkcje okna, które omówimy dokładniej w rozdziale 4. Na razie warto wiedzieć, że PERCENTILE_CONT() przyjmuje jeden parametr – percentyl (liczba dziesiętna z zakresu od 0. do 1.). Percentyl musi być określony wewnątrz uporządkowanej grupy w klauzuli WITHIN GROUP, a dane można opcjonalnie podzielić na partycje za pomocą klauzuli OVER. W sekcji WITHIN GROUP należy posortować dane według kolumny, której 50. percentyl chcemy wyznaczyć.
To ćwiczenie jest częścią kursu
Analiza szeregów czasowych w SQL Server
Instrukcje do ćwiczenia
- Uzupełnij brakującą wartość parametru funkcji
PERCENTILE_CONT(). - Wewnątrz klauzuli
WITHIN GROUP()posortuj dane według liczby zdarzeń malejąco. - W klauzuli
OVER()podziel dane według kolumnyIncidentType(rzeczywista wartość tekstowa, nie identyfikator ID).
Interaktywne ćwiczenie praktyczne
Spróbuj tego ćwiczenia, uzupełniając ten przykładowy kod.
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;