有效分配MYSQL中的百分位/等级 [英] Efficient assignment of percentile/rank in MYSQL

问题描述

我有几个非常大的表(超过40万行)，如下所示:

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

我需要为M1_Percentile列中的每一行分配一个值，该值表示具有等于或低于当前行的M1值的M1值的行的百分比"

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"

换句话说，我需要:

我成功地实现了这一点，但是FAR FAR太慢了.如果有人可以创建以下代码的更有效的版本，我将不胜感激！

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;

如果行数限制为您看到的行数(10行)，则这是上述查询的结果(正确但缓慢):

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

在整个40万行中产生相同的结果需要花费更长的时间.

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 test t
set m1_pc = (
    (select count(*) from test t1 where t1.M1 < t.M1) * 100 /
    ( select count(*) from test));

这在Oracle(我唯一可用的数据库)中有效.我确实记得在MySQL中遇到该错误.真烦人.

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

这篇关于有效分配MYSQL中的百分位/等级的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！