Prosty sposób na obliczenie mediany za pomocą MySQL

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.

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

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

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

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

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.

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 

Zainstaluj i użyj tych funkcji statystycznych mysql: http://www.xarg.org/2012/07/statystyczny-functions-in-mysql/

Następnie obliczyć medianę jest łatwe:

SELECT median(val) FROM data;

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/

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 );

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 ;

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

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

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;

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);

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

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));

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.

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.

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);
...

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;

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

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;

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

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));

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ć

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.

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

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

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

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 :-)