mysql 有效位数_在MYSQL中有效分配百分位数/等级

我有几个非常大的表(超过400,000行)，如下所示：

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

| 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我需要为M1_Percentile列中的每行分配一个值，该值表示“M1值等于或低于当前行的M1值的行的百分比”

换句话说，我需要：

我成功地实现了这一点，但它太慢了。如果任何人都可以创建更高效​​的以下代码版本，我将非常感激！

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;如果行数限于您看到的行数(10行)，则这是上述查询的(正确但缓慢的)结果：

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

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

+---------+--------+---------------+为整个400,000行产生相同的结果需要更长的时间。