SQL Server medianfunksjon

Den statistiske medianen, eller kort, refererer til en verdi som skiller et sett med verdier i to. Du kan tenke på medianen av mellomverdien i et sett med sorterte verdier i stigende eller synkende rekkefølge.

Medianen indikerer typisk den største eller minste verdien, avhengig av settet som er referert til. For eksempel i et sett med verdier:

100.200.300.400.500.600.700.800.900

Medianverdien i ovennevnte sett er 500. Derfor er 500 den fjerde største verdien i det første settet og det fjerde minste i det andre settet.

Denne artikkelen vil lære hvordan du beregner en kolonne i SQL Server. Husk at det ikke er noen spesifikk funksjon å utføre en statistisk median i SQL Server.

Det grunnleggende

La oss starte med det grunnleggende og forstå hvordan vi kan beregne medianen for et sett med verdier.

I statistikk, for å beregne medianen til et sett, starter vi med å ordne verdiene i enten en stigende eller synkende rekkefølge. Når vi har sortert dataene logisk, bestemmer vi mellomverdien.

Hvis settet inneholder et rart antall verdier, vurderer vi mellomverdien medianen til det spesifikke settet.

Imidlertid, hvis settet omfatter et jevnt antall verdier, bestemmer vi de to mellomverdiene i settet, legger dem til og deler med 2.

Vi kan uttrykke formelen for å beregne medianen til et gitt sett som:

KILDE: Wikipedia.

Beregn median i SQL Server

La oss lære å beregne medianen i SQL Server. La oss starte med å sette opp demoinformasjon som vist i spørsmålene nedenfor:

Opprett database:

Opprette databasemedian;

Bruk databasen

Bruk median;

Lag en tabell med kolonner som vist:

Bruk median;
Lag tabellprøve_data (
id int primær nøkkelidentitet (1,1) ikke null,
Produkt varchar (50),
pris penger,
Mengde int
);
Sett inn i eksempler_data (produkt, pris, mengde)
Verdier ('justerbar stol', 380.40, 1),
('Vindtett paraply', 26.77, 3),
('Amazon Echo Dot', 39.99, 5),
('Air Purifier', 99.99, 6),
('4K sikkerhetskamera', 109.85, 4),
('Fitness Tracker', 67.49, 10),
('Berøringsskjermhansker', 12.99, 8),
('Apple AirPods Pro', 329.99, 5),
('Sony WH-1000XM4', 320.99, 5),
('MacBook Air', 999.99, 10),
('Dell XPS 13', 1170.00, 6);

Når vi har prøvedataene, kan vi beregne medianen til de oppgitte dataene.

Metode 1 - SQL -rangering og CTE

Den første metoden vi kan bruke for å beregne medianen av verdier er rangfunksjonen og vanlige tabelluttrykk. Denne metoden fungerer selv i eldre versjoner av SQL Server.

Dette fungerer ved å gruppere settet i 50 prosent høyeste og 50 prosent laveste verdier.

Vi kan bruke dette som vist i eksempelforespørselen nedenfor:

PLUKKE UT
(
(Velg maks (pris) fra
(Velg topp 50 prosent pris fra Sample_Data -bestillingen etter pris) som BottomHalf)
+
(Velg min (pris) fra
(Velg topp 50 prosent pris fra Sample_Data -bestillingen etter pris DESC) som tophalf)
) / 2 som median

Den resulterende verdien er som:

median
---------------------
109.85
(1 rad berørt)

Metode 2 - Percentile_cont

Som nevnt, i skrivende stund er det ingen medianfunksjon i SQL Server. Vi kan imidlertid bruke Perscentile_Cont -funksjonen for å oppnå den samme funksjonaliteten.

Funksjonen returnerer verdien rangert til en spesifikk prosent for et definert sett med verdier. Derfor, hvis vi setter prosentverdien til 0.5, vil funksjonen returnere en median verdi.

Tenk på eksemplet på spørringen nedenfor:

Velg produkt, pris, persentil_cont (0.5)
innen gruppe (bestilling etter pris)
Over (partisjon etter produkt) som median
Fra prøve_data bestilling etter produkt desc;

Spørringen returnerer utdataene som:

Lær mer om persentil_cont i dokumentasjonen.

Lukking

Denne artikkelen diskuterer den statistiske medianen og forskjellige måter å beregne en kolonnes median i SQL Server.