Prosty sposób na obliczenie mediany za pomocą MySQL


207

Jaki jest najprostszy (i mam nadzieję, że nie za wolny) sposób obliczenia mediany za pomocą MySQL? Kiedyś szukałem AVG(x)średniej, ale trudno mi znaleźć prosty sposób obliczenia mediany. Na razie zwracam wszystkie wiersze do PHP, robię sortowanie, a następnie wybieram środkowy wiersz, ale na pewno musi być jakiś prosty sposób na wykonanie tego w jednym zapytaniu MySQL.

Przykładowe dane:

id | val
--------
 1    4
 2    7
 3    2
 4    2
 5    9
 6    8
 7    3

Sortowanie według valdaje 2 2 3 4 7 8 9, więc mediana powinna być 4, w porównaniu z SELECT AVG(val)którym == 5.


71
czy jestem jedynym mdłym z powodu faktu, że MySQL nie ma funkcji do obliczenia mediany? Śmieszny.
Monica Heddneck

3
MariaDB od wersji 10.3 ma jedną, patrz mariadb.com/kb/en/library/median
berturion

Odpowiedzi:


224

W MariaDB / MySQL:

SELECT AVG(dd.val) as median_val
FROM (
SELECT d.val, @rownum:=@rownum+1 as `row_number`, @total_rows:=@rownum
  FROM data d, (SELECT @rownum:=0) r
  WHERE d.val is NOT NULL
  -- put some where clause here
  ORDER BY d.val
) as dd
WHERE dd.row_number IN ( FLOOR((@total_rows+1)/2), FLOOR((@total_rows+2)/2) );

Steve Cohen wskazuje, że po pierwszym przejściu @rownum będzie zawierać całkowitą liczbę wierszy. Można to wykorzystać do ustalenia mediany, więc nie jest potrzebne drugie przejście lub łączenie.

Również AVG(dd.val)i dd.row_number IN(...)służy do prawidłowego wytworzenia medianę gdy istnieje liczba nawet zapisów. Rozumowanie:

SELECT FLOOR((3+1)/2),FLOOR((3+2)/2); -- when total_rows is 3, avg rows 2 and 2
SELECT FLOOR((4+1)/2),FLOOR((4+2)/2); -- when total_rows is 4, avg rows 2 and 3

Wreszcie, MariaDB 10.3.3+ zawiera funkcję MEDIAN


4
jakikolwiek sposób, aby wyświetlić wartości grupy? jak: miejsce / mediana dla tego miejsca ... jak wybierz miejsce, median_value z tabeli ... jakikolwiek sposób? dzięki
saulob

2
@rowNum będzie miał „łączną liczbę” na końcu wykonania. Możesz więc użyć tego, jeśli chcesz uniknąć konieczności ponownego liczenia wszystkich (co było moim przypadkiem, ponieważ moje zapytanie nie było takie proste)
Ahmed-Anas,

Logika posiadania jednego wyrażenia: (floor ((total_rows + 1) / 2), floor ((total_rows + 2) / 2)) oblicza wiersze potrzebne do mediany jest niesamowita! Nie jestem pewien, jak o tym pomyślałeś, ale jest genialny. Część, której nie przestrzegam, to (SELECT @rownum: = 0) r - do czego to służy?
Shanemeister

zmień pierwszy WHERE 1na WHERE d.val IS NOT NULLtak, aby wykluczył NULLwiersze, aby zachować tę metodę zgodną z natywnąAVG
chiliNUT

1
Moja wartość pochodzi od złączenia dwóch tabel, więc musiałem dodać kolejne podzapytanie, aby upewnić się, że kolejność wierszy jest poprawna po złączeniu! Struktura była w pewnym sensieselect avg(value) from (select value, row_number from (select a - b as value from a_table join b_table order by value))
Daniel Buckmaster

62

Właśnie znalazłem inną odpowiedź online w komentarzach :

Dla median w prawie każdym SQL:

SELECT x.val from data x, data y
GROUP BY x.val
HAVING SUM(SIGN(1-SIGN(y.val-x.val))) = (COUNT(*)+1)/2

Upewnij się, że kolumny są dobrze zindeksowane, a indeks służy do filtrowania i sortowania. Zweryfikuj za pomocą planów wyjaśniania

select count(*) from table --find the number of rows

Obliczyć „środkowy” numer wiersza. Może użyć:median_row = floor(count / 2) .

Następnie wybierz go z listy:

select val from table order by val asc limit median_row,1

To powinno zwrócić ci jeden wiersz z tylko pożądaną wartością.

Jakub


6
@rob możesz pomóc w edycji? A może powinienem po prostu pokłonić się rozwiązaniu Velcrow? (nie jestem pewien, jak odłożyć się na inne rozwiązanie) Dzięki, Jacob
TheJacobTaylor

1
Zauważ, że wykonuje „łączenie krzyżowe”, co jest bardzo wolne w przypadku dużych tabel.
Rick James

1
Ta odpowiedź nic nie zwraca dla parzystej liczby wierszy.
kuttumiah

Ta odpowiedź w ogóle nie działa w przypadku niektórych zestawów danych, np. Trywialny zestaw danych o wartościach 0,1, 0,1, 0,1, 2 - zadziała, jeśli wszystkie wartości będą różne, ale zadziała tylko wtedy, gdy wartości
Kem Mason

32

Zauważyłem, że zaakceptowane rozwiązanie nie działa w mojej instalacji MySQL, zwracając pusty zestaw, ale to zapytanie działało dla mnie we wszystkich sytuacjach, w których testowałem to:

SELECT x.val from data x, data y
GROUP BY x.val
HAVING SUM(SIGN(1-SIGN(y.val-x.val)))/COUNT(*) > .5
LIMIT 1

1
absolutnie poprawne, działa idealnie i bardzo szybko na moich indeksowanych tabelach
Rob

2
wydaje się, że jest to najszybsze rozwiązanie dla mysql spośród wszystkich odpowiedzi tutaj, 200 ms przy zaledwie milionie rekordów w tabeli
Rob

3
@FrankConijn: Wybiera dwa razy z jednej tabeli. Nazwa tabeli to datai jest używana z dwiema nazwami xoraz y.
Brian

3
tylko mówię, że utknąłem mój mysqld z tym dokładnym zapytaniem na stole z
33k

1
To zapytanie zwraca złą odpowiedź dla parzystej liczby wierszy.
kuttumiah

26

Niestety, ani odpowiedzi TheJacobTaylor, ani velcrow nie zwracają dokładnych wyników dla bieżących wersji MySQL.

Odpowiedź na rzep z góry jest bliska, ale nie oblicza się poprawnie dla zestawów wyników o parzystej liczbie wierszy. Mediany są zdefiniowane jako 1) środkowa liczba w zestawach nieparzystych lub 2) średnia z dwóch liczb środkowych w zestawach parzystych.

Oto rozwiązanie zapinane na rzep, które obsługuje zestawy liczb nieparzystych i parzystych:

SELECT AVG(middle_values) AS 'median' FROM (
  SELECT t1.median_column AS 'middle_values' FROM
    (
      SELECT @row:=@row+1 as `row`, x.median_column
      FROM median_table AS x, (SELECT @row:=0) AS r
      WHERE 1
      -- put some where clause here
      ORDER BY x.median_column
    ) AS t1,
    (
      SELECT COUNT(*) as 'count'
      FROM median_table x
      WHERE 1
      -- put same where clause here
    ) AS t2
    -- the following condition will return 1 record for odd number sets, or 2 records for even number sets.
    WHERE t1.row >= t2.count/2 and t1.row <= ((t2.count/2) +1)) AS t3;

Aby z tego skorzystać, wykonaj 3 proste kroki:

  1. Zamień „median_table” (2 wystąpienia) w powyższym kodzie na nazwę swojej tabeli
  2. Zamień „median_column” (3 wystąpienia) na nazwę kolumny, dla której chcesz znaleźć medianę
  3. Jeśli masz warunek GDZIE, zamień „GDZIE 1” (2 wystąpienia) na warunek where

A co robisz dla mediany wartości ciągów?
Rick James

12

Proponuję szybszy sposób.

Uzyskaj liczbę wierszy:

SELECT CEIL(COUNT(*)/2) FROM data;

Następnie weź środkową wartość w posortowane podzapytanie:

SELECT max(val) FROM (SELECT val FROM data ORDER BY val limit @middlevalue) x;

Przetestowałem to za pomocą zestawu danych losowych liczb 5x10e6, a mediana znajdzie się w ciągu 10 sekund.


3
Dlaczego nie: WYBIERZ val z danych ORDER BY val limit @middlevalue, 1
Bryan

1
Jak wciągnąć zmienne wyjście pierwszego bloku kodu do drugiego bloku kodu?
Wyjazd

3
Skąd pochodzi @middlevalue?
Trip

@Bryan - Zgadzam się z tobą, to ma dla mnie znacznie więcej sensu. Czy kiedykolwiek znalazłeś powód, aby tego nie robić?
Shane N

5
To nie działa, ponieważ zmienna nie może być użyta w klauzuli limitu.
codepk

8

Komentarz do tej strony w dokumentacji MySQL ma następującą sugestię:

-- (mostly) High Performance scaling MEDIAN function per group
-- Median defined in http://en.wikipedia.org/wiki/Median
--
-- by Peter Hlavac
-- 06.11.2008
--
-- Example Table:

DROP table if exists table_median;
CREATE TABLE table_median (id INTEGER(11),val INTEGER(11));
COMMIT;


INSERT INTO table_median (id, val) VALUES
(1, 7), (1, 4), (1, 5), (1, 1), (1, 8), (1, 3), (1, 6),
(2, 4),
(3, 5), (3, 2),
(4, 5), (4, 12), (4, 1), (4, 7);



-- Calculating the MEDIAN
SELECT @a := 0;
SELECT
id,
AVG(val) AS MEDIAN
FROM (
SELECT
id,
val
FROM (
SELECT
-- Create an index n for every id
@a := (@a + 1) mod o.c AS shifted_n,
IF(@a mod o.c=0, o.c, @a) AS n,
o.id,
o.val,
-- the number of elements for every id
o.c
FROM (
SELECT
t_o.id,
val,
c
FROM
table_median t_o INNER JOIN
(SELECT
id,
COUNT(1) AS c
FROM
table_median
GROUP BY
id
) t2
ON (t2.id = t_o.id)
ORDER BY
t_o.id,val
) o
) a
WHERE
IF(
-- if there is an even number of elements
-- take the lower and the upper median
-- and use AVG(lower,upper)
c MOD 2 = 0,
n = c DIV 2 OR n = (c DIV 2)+1,

-- if its an odd number of elements
-- take the first if its only one element
-- or take the one in the middle
IF(
c = 1,
n = 1,
n = c DIV 2 + 1
)
)
) a
GROUP BY
id;

-- Explanation:
-- The Statement creates a helper table like
--
-- n id val count
-- ----------------
-- 1, 1, 1, 7
-- 2, 1, 3, 7
-- 3, 1, 4, 7
-- 4, 1, 5, 7
-- 5, 1, 6, 7
-- 6, 1, 7, 7
-- 7, 1, 8, 7
--
-- 1, 2, 4, 1

-- 1, 3, 2, 2
-- 2, 3, 5, 2
--
-- 1, 4, 1, 4
-- 2, 4, 5, 4
-- 3, 4, 7, 4
-- 4, 4, 12, 4


-- from there we can select the n-th element on the position: count div 2 + 1 

IMHO, ten jest zdecydowanie najlepszy w sytuacjach, w których potrzebujesz mediany ze skomplikowanych podzbiorów (potrzebowałem obliczyć oddzielne mediany dużej liczby podzbiorów danych)
mblackwell8

Działa dobrze dla mnie. 5.6.14 Serwer społeczności MySQL. Tabela z rekordami 11 mln (około 20 Gb na dysku), ma dwa indeksy inne niż podstawowe (identyfikator_domeny, cena). W tabeli (po filtracji) mamy 500 000 rekordów do obliczenia mediany. W rezultacie mamy 30 000 rekordów (model_id, median_price). Czas trwania zapytania wynosi 1,5–2 sekundy. Szybkość jest dla mnie szybka.
Mikl


6

Większość powyższych rozwiązań działa tylko dla jednego pola tabeli, może być konieczne uzyskanie mediany (50 percentyla) dla wielu pól w zapytaniu.

Używam tego:

SELECT CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(
 GROUP_CONCAT(field_name ORDER BY field_name SEPARATOR ','),
  ',', 50/100 * COUNT(*) + 1), ',', -1) AS DECIMAL) AS `Median`
FROM table_name;

Możesz zamienić „50” w powyższym przykładzie na dowolny percentyl, jest bardzo wydajny.

Upewnij się, że masz wystarczającą ilość pamięci dla GROUP_CONCAT, możesz to zmienić za pomocą:

SET group_concat_max_len = 10485760; #10MB max length

Więcej informacji: http://web.performancerasta.com/metrics-tips-calculating-95th-99th-or-any-percentile-with-single-mysql-query/


Należy pamiętać: dla parzystej liczby wartości przyjmuje ona wyższą z dwóch średnich wartości. W przypadku liczby kursowej wartości przyjmuje następną wyższą wartość po medianie.
giordano

6

Mam poniższy kod, który znalazłem na HackerRank i jest dość prosty i działa w każdym przypadku.

SELECT M.MEDIAN_COL FROM MEDIAN_TABLE M WHERE  
  (SELECT COUNT(MEDIAN_COL) FROM MEDIAN_TABLE WHERE MEDIAN_COL < M.MEDIAN_COL ) = 
  (SELECT COUNT(MEDIAN_COL) FROM MEDIAN_TABLE WHERE MEDIAN_COL > M.MEDIAN_COL );

2
Uważam, że to działa tylko z tabelą, która ma liczbę wpisów, jest nieparzysta. W przypadku parzystej liczby wpisów może to stanowić problem.
Y. Chang

4

Opierając się na odpowiedzi na rzep, dla tych z was, którzy muszą zrobić medianę z czegoś, co jest pogrupowane według innego parametru:

WYBIERZ pole grp_ , t1 . val FROM  ( SELECT grp_field , @ rownum : = IF (@ s = grp_field , @ rownum + 1 , 0 ) AS , @ s : = IF (@ s = grp_field , @ s , grp_field ) AS sec , d . val
   FROM data d , (
         row_number
       SELECTNumer wiersza @ rownum : = 0 , @ s : = 0 ) r
   ORDER BY grp_field , d BY grp_field
 ) jako t2
 ON t1 . grp_field = t2 . grp_field
 GDZIE t1 .   . val
 ) jako t1 DOŁĄCZ ( wybierz pole grp , policz (*) jako total_rows
   Z danych d
   GRUPA  
     = piętro ( total_rows / 2 ) +1 ;


3

Możesz użyć funkcji zdefiniowanej przez użytkownika, która znajduje się tutaj .


3
Wygląda to najbardziej przydatne, ale nie chcę, aby zainstalować oprogramowanie niestabilny alfa, które mogą spowodować awarię mysql na moim serwerze produkcyjnym :(
davr

6
Więc przestudiuj ich źródła pod kątem interesującej ich funkcji, popraw je lub zmodyfikuj w razie potrzeby i zainstaluj „własną” stabilną i inną niż alfa wersję, gdy już ją stworzysz - jak to jest gorsze niż podobnie poprawianie mniej sprawdzonych sugestii kodu dostajesz się na SO? -)
Alex Martelli,

3

Dba o nieparzystą liczbę wartości - w takim przypadku podaje średnią z dwóch wartości pośrodku.

SELECT AVG(val) FROM
  ( SELECT x.id, x.val from data x, data y
      GROUP BY x.id, x.val
      HAVING SUM(SIGN(1-SIGN(IF(y.val-x.val=0 AND x.id != y.id, SIGN(x.id-y.id), y.val-x.val)))) IN (ROUND((COUNT(*))/2), ROUND((COUNT(*)+1)/2))
  ) sq

2

Mój kod, wydajny bez tabel i dodatkowych zmiennych:

SELECT
((SUBSTRING_INDEX(SUBSTRING_INDEX(group_concat(val order by val), ',', floor(1+((count(val)-1) / 2))), ',', -1))
+
(SUBSTRING_INDEX(SUBSTRING_INDEX(group_concat(val order by val), ',', ceiling(1+((count(val)-1) / 2))), ',', -1)))/2
as median
FROM table;

3
Nie powiedzie się to w przypadku znacznej ilości danych, ponieważ GROUP_CONCATjest ograniczone do 1023 znaków, nawet jeśli jest używane w innej funkcji takiej jak ta.
Rob Van Dam

2

Opcjonalnie możesz to zrobić w procedurze składowanej:

DROP PROCEDURE IF EXISTS median;
DELIMITER //
CREATE PROCEDURE median (table_name VARCHAR(255), column_name VARCHAR(255), where_clause VARCHAR(255))
BEGIN
  -- Set default parameters
  IF where_clause IS NULL OR where_clause = '' THEN
    SET where_clause = 1;
  END IF;

  -- Prepare statement
  SET @sql = CONCAT(
    "SELECT AVG(middle_values) AS 'median' FROM (
      SELECT t1.", column_name, " AS 'middle_values' FROM
        (
          SELECT @row:=@row+1 as `row`, x.", column_name, "
          FROM ", table_name," AS x, (SELECT @row:=0) AS r
          WHERE ", where_clause, " ORDER BY x.", column_name, "
        ) AS t1,
        (
          SELECT COUNT(*) as 'count'
          FROM ", table_name, " x
          WHERE ", where_clause, "
        ) AS t2
        -- the following condition will return 1 record for odd number sets, or 2 records for even number sets.
        WHERE t1.row >= t2.count/2
          AND t1.row <= ((t2.count/2)+1)) AS t3
    ");

  -- Execute statement
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
END//
DELIMITER ;


-- Sample usage:
-- median(table_name, column_name, where_condition);
CALL median('products', 'price', NULL);

Dzięki za to! Użytkownik powinien pamiętać, że brakujące wartości (NULL) są uważane za wartości. aby uniknąć tego problemu, dodaj „x NIE JEST NULL, gdy warunek.
giordano

1
@giordano W której linii kodu x IS NOT NULLnależy dodać?
Przemysław Remin

1
@PrzemyslawRemin Przepraszam, nie byłem jasny w moim oświadczeniu i zdałem sobie sprawę, że SP rozważa już przypadek brakujących wartości. SP powinny być nazywane w ten sposób: CALL median("table","x","x IS NOT NULL").
giordano

2

Moje rozwiązanie przedstawione poniżej działa tylko w jednym zapytaniu bez tworzenia tabeli, zmiennej, a nawet pod-zapytania. Ponadto pozwala uzyskać medianę dla każdej grupy w zapytaniach grupowych (właśnie tego potrzebowałem!):

SELECT `columnA`, 
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(`columnB` ORDER BY `columnB`), ',', CEILING((COUNT(`columnB`)/2))), ',', -1) medianOfColumnB
FROM `tableC`
-- some where clause if you want
GROUP BY `columnA`;

Działa dzięki inteligentnemu użyciu group_concat i substring_index.

Ale aby pozwolić dużej grupie_konkurencyjnej, należy ustawić wyższą wartość parametru grupa_konkata_maks_len (domyślnie 1024 znaki). Możesz to ustawić w ten sposób (dla bieżącej sesji SQL):

SET SESSION group_concat_max_len = 10000; 
-- up to 4294967295 in 32-bits platform.

Więcej informacji o group_concat_max_len: https://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_group_concat_max_len


2

Kolejny riff na odpowiedź Velcrowa, ale wykorzystuje pojedynczą tabelę pośrednią i wykorzystuje zmienną używaną do numerowania wierszy, aby uzyskać liczbę, zamiast wykonywania dodatkowego zapytania w celu jej obliczenia. Rozpoczyna również zliczanie, tak aby pierwszy rząd był rzędem 0, aby umożliwić po prostu użycie opcji Podłoga i Sufit do wybrania środkowych rzędów.

SELECT Avg(tmp.val) as median_val
    FROM (SELECT inTab.val, @rows := @rows + 1 as rowNum
              FROM data as inTab,  (SELECT @rows := -1) as init
              -- Replace with better where clause or delete
              WHERE 2 > 1
              ORDER BY inTab.val) as tmp
    WHERE tmp.rowNum in (Floor(@rows / 2), Ceil(@rows / 2));

2
SELECT 
    SUBSTRING_INDEX(
        SUBSTRING_INDEX(
            GROUP_CONCAT(field ORDER BY field),
            ',',
            ((
                ROUND(
                    LENGTH(GROUP_CONCAT(field)) - 
                    LENGTH(
                        REPLACE(
                            GROUP_CONCAT(field),
                            ',',
                            ''
                        )
                    )
                ) / 2) + 1
            )),
            ',',
            -1
        )
FROM
    table

Powyższe wydaje się działać dla mnie.


Nie zwraca poprawnej mediany dla parzystej liczby wartości, na przykład mediana {98,102,102,98}jest, 100ale kod podaje 102. Działa dobrze dla liczb nieparzystych.
Nomiluks,

1

Użyłem dwóch zapytań:

  • pierwszy, aby uzyskać liczbę, min, maks i śr
  • drugi (przygotowane oświadczenie) z klauzulami „LIMIT @ count / 2, 1” i „ORDER BY ..” w celu uzyskania wartości mediany

Są one zapakowane w funkcję defn, dzięki czemu wszystkie wartości mogą być zwrócone z jednego wywołania.

Jeśli zakresy są statyczne, a dane nie zmieniają się często, bardziej efektywne może być wstępne obliczanie / przechowywanie tych wartości i używanie zapisanych wartości zamiast zapytania od zera za każdym razem.


1

ponieważ potrzebowałem tylko mediany ORAZ percentyla, stworzyłem prostą i dość elastyczną funkcję w oparciu o ustalenia w tym wątku. Wiem, że sam się cieszę, gdy znajdę „gotowe” funkcje, które można łatwo włączyć do moich projektów, dlatego postanowiłem szybko udostępnić:

function mysql_percentile($table, $column, $where, $percentile = 0.5) {

    $sql = "
            SELECT `t1`.`".$column."` as `percentile` FROM (
            SELECT @rownum:=@rownum+1 as `row_number`, `d`.`".$column."`
              FROM `".$table."` `d`,  (SELECT @rownum:=0) `r`
              ".$where."
              ORDER BY `d`.`".$column."`
            ) as `t1`, 
            (
              SELECT count(*) as `total_rows`
              FROM `".$table."` `d`
              ".$where."
            ) as `t2`
            WHERE 1
            AND `t1`.`row_number`=floor(`total_rows` * ".$percentile.")+1;
        ";

    $result = sql($sql, 1);

    if (!empty($result)) {
        return $result['percentile'];       
    } else {
        return 0;
    }

}

Użycie jest bardzo łatwe, przykład z mojego obecnego projektu:

...
$table = DBPRE."zip_".$slug;
$column = 'seconds';
$where = "WHERE `reached` = '1' AND `time` >= '".$start_time."'";

    $reaching['median'] = mysql_percentile($table, $column, $where, 0.5);
    $reaching['percentile25'] = mysql_percentile($table, $column, $where, 0.25);
    $reaching['percentile75'] = mysql_percentile($table, $column, $where, 0.75);
...

1

Oto moja droga. Oczywiście możesz to zrobić w ramach procedury :-)

SET @median_counter = (SELECT FLOOR(COUNT(*)/2) - 1 AS `median_counter` FROM `data`);

SET @median = CONCAT('SELECT `val` FROM `data` ORDER BY `val` LIMIT ', @median_counter, ', 1');

PREPARE median FROM @median;

EXECUTE median;

Możesz uniknąć zmiennej @median_counter, jeśli ją podmienisz:

SET @median = CONCAT( 'SELECT `val` FROM `data` ORDER BY `val` LIMIT ',
                      (SELECT FLOOR(COUNT(*)/2) - 1 AS `median_counter` FROM `data`),
                      ', 1'
                    );

PREPARE median FROM @median;

EXECUTE median;

1

Wydaje się, że ten sposób obejmuje liczenie parzyste i nieparzyste bez podzapytania.

SELECT AVG(t1.x)
FROM table t1, table t2
GROUP BY t1.x
HAVING SUM(SIGN(t1.x - t2.x)) = 0

1

Na podstawie odpowiedzi @ bob uogólnia to zapytanie, aby mieć możliwość zwrócenia wielu median pogrupowanych według niektórych kryteriów.

Pomyśl np. O średniej cenie sprzedaży używanych samochodów na parkingu, pogrupowanej według roku.

SELECT 
    period, 
    AVG(middle_values) AS 'median' 
FROM (
    SELECT t1.sale_price AS 'middle_values', t1.row_num, t1.period, t2.count
    FROM (
        SELECT 
            @last_period:=@period AS 'last_period',
            @period:=DATE_FORMAT(sale_date, '%Y-%m') AS 'period',
            IF (@period<>@last_period, @row:=1, @row:=@row+1) as `row_num`, 
            x.sale_price
          FROM listings AS x, (SELECT @row:=0) AS r
          WHERE 1
            -- where criteria goes here
          ORDER BY DATE_FORMAT(sale_date, '%Y%m'), x.sale_price
        ) AS t1
    LEFT JOIN (  
          SELECT COUNT(*) as 'count', DATE_FORMAT(sale_date, '%Y-%m') AS 'period'
          FROM listings x
          WHERE 1
            -- same where criteria goes here
          GROUP BY DATE_FORMAT(sale_date, '%Y%m')
        ) AS t2
        ON t1.period = t2.period
    ) AS t3
WHERE 
    row_num >= (count/2) 
    AND row_num <= ((count/2) + 1)
GROUP BY t3.period
ORDER BY t3.period;

1

Często możemy potrzebować obliczyć Medianę nie tylko dla całej tabeli, ale dla agregatów w odniesieniu do naszego ID. Innymi słowy, oblicz medianę dla każdego identyfikatora w naszej tabeli, gdzie każdy identyfikator ma wiele rekordów. (dobra wydajność i działa w wielu SQL + rozwiązuje problem parzystości i szans, więcej na temat wydajności różnych metod Mediana https://sqlperformance.com/2012/08/t-sql-queries/median )

SELECT our_id, AVG(1.0 * our_val) as Median
FROM
( SELECT our_id, our_val, 
  COUNT(*) OVER (PARTITION BY our_id) AS cnt,
  ROW_NUMBER() OVER (PARTITION BY our_id ORDER BY our_val) AS rn
  FROM our_table
) AS x
WHERE rn IN ((cnt + 1)/2, (cnt + 2)/2) GROUP BY our_id;

Mam nadzieję, że to pomoże


To najlepsze rozwiązanie. Jednak w przypadku dużych zestawów danych nastąpi spowolnienie, ponieważ liczy się ponownie dla każdego elementu w każdym zestawie. Aby przyspieszyć, umieść „COUNT (*)” w oddzielnym zapytaniu podrzędnym.
Slava Murygin

1

MySQL obsługuje funkcje okien od wersji 8.0, możesz używać ROW_NUMBERlub DENSE_RANK( NIE używaj, RANKponieważ przypisuje tę samą pozycję do tych samych wartości, jak w rankingu sportowym):

SELECT AVG(t1.val) AS median_val
  FROM (SELECT val, 
               ROW_NUMBER() OVER(ORDER BY val) AS rownum
          FROM data) t1,
       (SELECT COUNT(*) AS num_records FROM data) t2
 WHERE t1.row_num IN
       (FLOOR((t2.num_records + 1) / 2), 
        FLOOR((t2.num_records + 2) / 2));

0

Jeśli MySQL ma ROW_NUMBER, MEDIAN jest (zainspirowany tym zapytaniem SQL Server):

WITH Numbered AS 
(
SELECT *, COUNT(*) OVER () AS Cnt,
    ROW_NUMBER() OVER (ORDER BY val) AS RowNum
FROM yourtable
)
SELECT id, val
FROM Numbered
WHERE RowNum IN ((Cnt+1)/2, (Cnt+2)/2)
;

IN jest używany w przypadku, gdy masz parzystą liczbę wpisów.

Jeśli chcesz znaleźć medianę na grupę, po prostu PARTITION BY grupa w klauzulach OVER.

Obrabować


1
Nie, nie ROW_NUMBER OVER, nie PARTITION BY, nic z tego; to MySql, a nie prawdziwy silnik DB, taki jak PostgreSQL, IBM DB2, MS SQL Server i tak dalej ;-).
Alex Martelli,

0

Po przeczytaniu wszystkich poprzednich nie pasowały one do moich rzeczywistych wymagań, więc wdrożyłem własny, który nie wymaga żadnej procedury ani skomplikowanych instrukcji, po prostu ja GROUP_CONCAT wszystkie wartości z kolumny, które chciałem uzyskać MEDIAN i stosując COUNT DIV BY 2 Wyodrębniam wartość ze środka listy, jak to robi następujące zapytanie:

(POS to nazwa kolumny, dla której chcę uzyskać medianę)

(query) SELECT
SUBSTRING_INDEX ( 
   SUBSTRING_INDEX ( 
       GROUP_CONCAT(pos ORDER BY CAST(pos AS SIGNED INTEGER) desc SEPARATOR ';') 
    , ';', COUNT(*)/2 ) 
, ';', -1 ) AS `pos_med`
FROM table_name
GROUP BY any_criterial

Mam nadzieję, że może to być przydatne dla kogoś w sposób, w jaki wiele innych komentarzy było dla mnie z tej strony.


0

Znając dokładną liczbę wierszy, możesz użyć tego zapytania:

SELECT <value> AS VAL FROM <table> ORDER BY VAL LIMIT 1 OFFSET <half>

Gdzie <half> = ceiling(<size> / 2.0) - 1


0

Mam bazę danych zawierającą około 1 miliarda wierszy, których wymagamy do ustalenia mediany wieku w zestawie. Sortowanie miliarda wierszy jest trudne, ale jeśli agregujesz różne wartości, które można znaleźć (przedziały wiekowe od 0 do 100), możesz posortować TĄ listę i użyć magii arytmetycznej, aby znaleźć dowolny percentyl w następujący sposób:

with rawData(count_value) as
(
    select p.YEAR_OF_BIRTH
        from dbo.PERSON p
),
overallStats (avg_value, stdev_value, min_value, max_value, total) as
(
  select avg(1.0 * count_value) as avg_value,
    stdev(count_value) as stdev_value,
    min(count_value) as min_value,
    max(count_value) as max_value,
    count(*) as total
  from rawData
),
aggData (count_value, total, accumulated) as
(
  select count_value, 
    count(*) as total, 
        SUM(count(*)) OVER (ORDER BY count_value ROWS UNBOUNDED PRECEDING) as accumulated
  FROM rawData
  group by count_value
)
select o.total as count_value,
  o.min_value,
    o.max_value,
    o.avg_value,
    o.stdev_value,
    MIN(case when d.accumulated >= .50 * o.total then count_value else o.max_value end) as median_value,
    MIN(case when d.accumulated >= .10 * o.total then count_value else o.max_value end) as p10_value,
    MIN(case when d.accumulated >= .25 * o.total then count_value else o.max_value end) as p25_value,
    MIN(case when d.accumulated >= .75 * o.total then count_value else o.max_value end) as p75_value,
    MIN(case when d.accumulated >= .90 * o.total then count_value else o.max_value end) as p90_value
from aggData d
cross apply overallStats o
GROUP BY o.total, o.min_value, o.max_value, o.avg_value, o.stdev_value
;

Ta kwerenda zależy od funkcji okna obsługujących db (w tym ROWS UNBOUNDED PRECEDING), ale jeśli nie masz, łatwo jest połączyć się z aggData CTE ze sobą i agregować wszystkie wcześniejsze sumy w kolumnie „akumulacji”, która jest używana do określenia, które wartość zawiera określony precentyl. Powyższa próbka oblicza p10, p25, p50 (mediana), p75 i p90.

-Chris


0

Zaczerpnięte z: http://mdb-blog.blogspot.com/2015/06/mysql-find-median-nth-element-without.html

Sugerowałbym inny sposób, bez łączenia , ale praca z ciągami

nie sprawdziłem tego z tabelami z dużymi danymi, ale małe / średnie tabele działają dobrze.

Dobrą rzeczą jest to, że działa również przez GROUPING, dzięki czemu może zwrócić medianę dla kilku elementów.

oto kod testu dla tabeli testowej:

DROP TABLE test.test_median
CREATE TABLE test.test_median AS
SELECT 'book' AS grp, 4 AS val UNION ALL
SELECT 'book', 7 UNION ALL
SELECT 'book', 2 UNION ALL
SELECT 'book', 2 UNION ALL
SELECT 'book', 9 UNION ALL
SELECT 'book', 8 UNION ALL
SELECT 'book', 3 UNION ALL

SELECT 'note', 11 UNION ALL

SELECT 'bike', 22 UNION ALL
SELECT 'bike', 26 

oraz kod do znalezienia mediany dla każdej grupy:

SELECT grp,
         SUBSTRING_INDEX( SUBSTRING_INDEX( GROUP_CONCAT(val ORDER BY val), ',', COUNT(*)/2 ), ',', -1) as the_median,
         GROUP_CONCAT(val ORDER BY val) as all_vals_for_debug
FROM test.test_median
GROUP BY grp

Wynik:

grp | the_median| all_vals_for_debug
bike| 22        | 22,26
book| 4         | 2,2,3,4,7,8,9
note| 11        | 11

Czy nie uważasz, że mediana `{22,26}` powinna wynosić 24?
Nomiluks,

0

W niektórych przypadkach mediana jest obliczana w następujący sposób:

„Mediana” to „środkowa” wartość na liście liczb, gdy są one uporządkowane według wartości. W przypadku zestawów parzystych mediana jest średnią z dwóch średnich wartości . Stworzyłem do tego prosty kod:

$midValue = 0;
$rowCount = "SELECT count(*) as count {$from} {$where}";

$even = FALSE;
$offset = 1;
$medianRow = floor($rowCount / 2);
if ($rowCount % 2 == 0 && !empty($medianRow)) {
  $even = TRUE;
  $offset++;
  $medianRow--;
}

$medianValue = "SELECT column as median 
               {$fromClause} {$whereClause} 
               ORDER BY median 
               LIMIT {$medianRow},{$offset}";

$medianValDAO = db_query($medianValue);
while ($medianValDAO->fetch()) {
  if ($even) {
    $midValue = $midValue + $medianValDAO->median;
  }
  else {
    $median = $medianValDAO->median;
  }
}
if ($even) {
  $median = $midValue / 2;
}
return $median;

Zwrócona mediana $ byłaby wymaganym wynikiem :-)

Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.