การคำนวณหาค่ามัธยฐาน บน SQL ( median )

หน้าแรก PHP MySQL เกร็ดความรู้ การคำนวณหาค่ามัธยฐาน บน SQL ( median )

---

For medians in almost any SQL:

Code

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

Make sure your columns are well indexed and the index is used for filtering and sorting. Verify with the explain plans.

Code

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

Calculate the "median" row number. Maybe use: median_row = floor(count / 2).

Then pick it out of the list:
select val from table order by val asc limit median_row,1
This should return you one row with just the value you want.


refer: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html
http://stackoverflow.com/questions/1291152/simple-way-to-calculate-median-with-mysql

ขึ้นไปด้านบน