Mediaan berekenen in SQL Server
Er is geen MEDIAN()-functie in SQL Server. Het dichtst in de buurt komt PERCENTILE_CONT(), die de waarde op het n-de percentiel in een gegevensset vindt.
We willen uitzoeken hoeveel de mediaan afwijkt van het gemiddelde per incidenttype in onze incident-rollup. Dat doen we door de AVG()-functie uit de vorige oefening te vergelijken met PERCENTILE_CONT(). Dit zijn vensterfuncties; we behandelen ze uitgebreider in hoofdstuk 4. Voor nu is het belangrijk om te weten dat PERCENTILE_CONT() een parameter heeft: het percentiel (een decimaal tussen 0 en 1). Het percentiel moet binnen een geordende groep staan in de WITHIN GROUP-clausule en OVER een bepaald bereik als je de data moet partitioneren. In de WITHIN GROUP-sectie moeten we sorteren op de kolom waarvan we het 50e percentiel willen bepalen.
Deze oefening maakt deel uit van de cursus
Tijdreeksanalyse in SQL Server
Oefeninstructies
- Vul de ontbrekende waarde in voor
PERCENTILE_CONT(). - Sorteer binnen de
WITHIN GROUP()-clausule op aantal incidenten aflopend. - Partitioneer in de
OVER()-clausule opIncidentType(de daadwerkelijke tekstwaarde, niet de ID).
Praktische interactieve oefening
Probeer deze oefening eens door deze voorbeeldcode in te vullen.
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;