mysql百分位,在MySQL中有效分配百分位/等级

I have a couple of very large tables (over 400,000 rows) that look like the following:

+---------+--------+---------------+

| ID | M1 | M1_Percentile |

+---------+--------+---------------+

| 3684514 | 3.2997 | NULL |

| 3684515 | 3.0476 | NULL |

| 3684516 | 2.6499 | NULL |

| 3684517 | 0.3585 | NULL |

| 3684518 | 1.6919 | NULL |

| 3684519 | 2.8515 | NULL |

| 3684520 | 4.0728 | NULL |

| 3684521 | 4.0224 | NULL |

| 3684522 | 5.8207 | NULL |

| 3684523 | 6.8291 | NULL |

+---------+--------+---------------+...about 400,000 more

I need to assign each row in the M1_Percentile column a value that represents "the percent of rows with M1 values equal or lower to the current row's M1 value"

In other words, I need:

I implemented this sucessfully, but it is FAR FAR too slow. If anyone could create a more efficient version of the following code, I would really appreciate it!

UPDATE myTable AS X JOIN (

SELECT

s1.ID, COUNT(s2.ID)/ (SELECT COUNT(*) FROM myTable) * 100 AS percentile

FROM

myTable s1 JOIN myTable s2 on (s2.M1 <= s1.M1)

GROUP BY s1.ID

ORDER BY s1.ID) AS Z

ON (X.ID = Z.ID)

SET X.M1_Percentile = Z.percentile;

This is the (correct but slow) result from the above query if the number of rows is limited to the ones you see (10 rows):

+---------+--------+---------------+

| ID | M1 | M1_Percentile |

+---------+--------+---------------+

| 3684514 | 3.2997 | 60 |

| 3684515 | 3.0476 | 50 |

| 3684516 | 2.6499 | 30 |

| 3684517 | 0.3585 | 10 |

| 3684518 | 1.6919 | 20 |

| 3684519 | 2.8515 | 40 |

| 3684520 | 4.0728 | 80 |

| 3684521 | 4.0224 | 70 |

| 3684522 | 5.8207 | 90 |

| 3684523 | 6.8291 | 100 |

+---------+--------+---------------+

Producing the same results for the entire 400,000 rows takes magnitudes longer.

解决方案

I cannot test this, but you could try something like:

update table t

set mi_percentile = (

select count(*)

from table t1

where M1 < t.M1 / (

select count(*)

from table));

UPDATE:

update test t

set m1_pc = (

(select count(*) from test t1 where t1.M1 < t.M1) * 100 /

( select count(*) from test));

This works in Oracle (the only database I have available). I do remember getting that error in MySQL. It is very annoying.