Funkcja mediany SQL Server

Mediana statystyczna lub krótka odnosi się do wartości, która oddziela zestaw wartości na pół. Możesz pomyśleć o medianie wartości środkowej w zestawie sortowanych wartości w kolejności rosnącej lub malejącej.

Mediana zazwyczaj wskazuje na największą lub najmniejszą wartość, w zależności od odniesionego zestawu. Na przykład w zestawie z wartościami:

100,200,300,400,500 600,700 800 900

Wartość mediany w powyższym zestawie wynosi 500. Stąd 500 to czwarta co do wielkości wartość w pierwszym zestawie i czwarta najmniejsza w drugim zestawie.

W tym artykule dowie się, jak obliczyć kolumnę na serwerze SQL. Należy pamiętać, że nie ma konkretnej funkcji do wykonywania mediany statystycznej na serwerze SQL.

Podstawy

Zacznijmy od podstaw i zrozumiemy, jak obliczyć medianę dla zestawu wartości.

W statystykach, aby obliczyć medianę zbioru, zaczynamy od zorganizowania wartości w kolejności rosnącej lub malejącej. Po uporządkowaniu danych logicznie określamy wartość środkową.

Jeśli zestaw zawiera nieparzystą liczbę wartości, rozważamy wartość środkową medianę określonego zestawu.

Jeśli jednak zestaw zawiera parzystą liczbę wartości, określamy dwie środkowe wartości w zestawie, dodajemy je i dzielimy przez 2.

Możemy wyrazić formułę obliczania mediany danego zestawu jako:

ŹRÓDŁO: Wikipedia.

Oblicz medianę na serwerze SQL

Dowiedzmy się, jak obliczyć medianę na serwerze SQL. Zacznijmy od skonfigurowania informacji demo, jak pokazano w poniższych zapytaniach:

Utwórz bazę danych:

Utwórz medianę bazy danych;

Użyj bazy danych

Użyj mediany;

Utwórz tabelę z kolumnami, jak pokazano:

Użyj mediany;
Utwórz tabelę próbki_data (
Id int int tożsamość kluczowa (1,1) nie zerowa,
Varchar Product (50),
cena,
ilość int
);
Wstaw do próbki_data (produkt, cena, ilość)
Wartości („regulowane krzesło”, 380.40, 1),
(„Windproof parasol”, 26.77, 3),
(„Amazon Echo Dot”, 39.99, 5),
(„Air Purifier”, 99.99, 6),
(„4K Security Camera”, 109.85, 4),
(„Fitness Tracker”, 67.49, 10),
(„Rękawiczki ekranu dotykowe”, 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);

Po uzyskaniu danych przykładowych możemy obliczyć medianę dostarczonych danych.

Metoda 1 - ranking SQL i CTE

Pierwszą metodą, którą możemy użyć do obliczenia mediany wartości, jest funkcja rang i wspólne wyrażenia tabeli. Ta metoda działa nawet w starszych wersjach SQL Server.

Działa to poprzez grupowanie zestawu w 50 procent najwyższych i 50 procent najniższych wartości.

Możemy użyć tego, jak pokazano na przykładowym zapytaniu poniżej:

WYBIERAĆ
(
(Wybierz Max (cenę) z
(Wybierz najwyższą cenę 50 procent z zamówienia Sample_Data według ceny) jako Bottomhalf)
+
(Wybierz min (cena) z
(Wybierz najwyższą cenę 50 procent z zamówienia próbki_data według ceny desc) jako tophalf)
) / 2 jako mediana

Powstała wartość to:

mediana
---------------------
109.85
(1 rząd dotknięty)

Metoda 2 - percentile_cont

Jak wspomniano, w momencie pisania tego artykułu nie ma środkowej funkcji na serwerze SQL. Możemy jednak użyć funkcji percentile_cont, aby osiągnąć tę samą funkcjonalność.

Funkcja zwraca wartość uszeregowaną w określonym procencie dla zdefiniowanego zestawu wartości. Stąd, jeśli ustawimy wartość procentową na 0.5, funkcja zwróci wartość mediany.

Rozważ przykładowe zapytanie poniżej:

Wybierz produkt, cenę, percentile_cont (0.5)
wewnątrz grupy (zamówienie według ceny)
Over (partycja według produktu) jako mediana
Z zamówienia próbki_data według produktu DESC;

Zapytanie zwraca dane wyjściowe jako:

Dowiedz się więcej o Perpresle_Cont w dokumentacji.

Zamknięcie

W tym artykule omówiono medianę statystyczną i różne sposoby obliczenia mediany kolumny na serwerze SQL.