Cara mudah untuk mengira median dengan MySQL

Question

Apakah cara paling mudah (mudah-mudahan tidak terlalu perlahan) untuk mengira median menggunakan MySQL? Saya menggunakan AVG(x) untuk mencari min, tetapi saya menghadapi masalah mencari cara mudah untuk mengira median. Sekarang, saya mengembalikan semua baris ke PHP, menyusun, dan kemudian memilih baris tengah, tetapi mesti ada beberapa cara mudah untuk melakukan ini dalam satu pertanyaan MySQL.

Contoh data:

id |
--------
 1 4
 2 7
 3 2
 4 2
 5 9
 6 8
 7 3
Isih val memberikan 2 2 3 4 7 8 9, jadi median hendaklah 4, Dan  ;PILIH AVG(val) == 5. 

Answer 1

Saya baru jumpa jawapan lain dalam talian dalam ulasan:

Pastikan lajur anda diindeks dengan baik dan indeks digunakan untuk menapis dan mengisih. Sahkan dengan menerangkan pelan.

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

Kira nombor baris "median". Mungkin gunakan: median_row = Floor(count / 2).

Kemudian pilihnya daripada senarai:

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

Ini sepatutnya mengembalikan baris dengan nilai yang anda mahukan.

Answer 2

Dalam 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 menegaskan bahawa selepas hantaran pertama, @rownum akan mengandungi jumlah bilangan baris. Ini boleh digunakan untuk menentukan median, jadi tiada hantaran kedua atau gabungan diperlukan.

Selain itu, AVG(dd.val) 和 dd.row_number IN(...) digunakan untuk menjana median dengan betul apabila terdapat bilangan rekod genap. Alasan:

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

Akhir sekali, MariaDB 10.3.3+ termasuk fungsi MEDIAN