MySQL:按数据有效分组为大小相同的桶

Question

假设我有一个玩家表，每个玩家都有一个分数，现在我想根据他们的分数将所有玩家分成相同大小的级别，所以如果我有 n 个玩家，级别 1 将拥有前 n/10 个得分最高的玩家，级别 2 将拥有下一个 n/10，依此类推。

我想出了一个问题:

UPDATE Players SET Level=? WHERE PlayerID IN (
    SELECT * FROM (
        SELECT PlayerID FROM Players ORDER BY Score DESC, PlayerID ASC LIMIT ?,?
    ) AS T1
);

我运行了 10 次，第一个参数从 1-10 运行，第二个参数是 0，n/10，2*n/10，。 .. 第三个总是 n/10。

这行得通，但需要相当长的时间。为了获得更好的结果，我创建了一个临时表:

CREATE TEMPORARY TABLE TempTable (
    IDX INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ID INT UNSIGNED NOT NULL,
    PRIMARY KEY (IDX)
) ENGINE=MEMORY; 
INSERT INTO TempTable (ID) SELECT PlayerID FROM Players ORDER BY Score DESC, PlayerID ASC;

然后我跑十次:

UPDATE Players SET Level=? WHERE PlayerID IN (
    SELECT * FROM TempTable WHERE IDX BETWEEN ? AND ?        
);

加上合适的参数，最后:

DROP TABLE TempTable;

但是，这运行甚至更慢。那么在 MySQL 中是否有更有效的方法来执行此操作？我找到了 this answer , 但它似乎 NTILE 在 MySQL 中不可用。

注意:Players 在 PlayerID(主键)和 Score 上有一个索引，尽管在 Score< 上没有索引运行 似乎没有太大区别。我还按 PlayerID 排序的原因是，在出现平局时我有明确定义的(一致的)行为。

Answer 1

您可以尝试使用排名功能。这就是我要使用的:

SELECT PlayerID, 
    score,
    @levelLimit,
    @counter := @counter + 1 AS counter,
    @level := IF(@counter % @levelLimit = 0, @level:= @level + 1, @level) as level
FROM Players,

    (SELECT @counter := 0) a,

    (SELECT @levelLimit := round(count(*)/4 -- number of groups you want to end with
                                 , 0)
     FROM Players) b,

    (SELECT @level := 1) c
ORDER BY Score DESC,
         PlayerID ASC
;

更新表格:

UPDATE Players join (
    SELECT PlayerID,
       score,
       @levelLimit, @counter := @counter + 1 AS counter, 
       @level := IF(@counter % @levelLimit = 0, @level:= @level + 1, @level) AS level
FROM Players,

    (SELECT @counter := 0) a,

    (SELECT @levelLimit := round(count(*)/4 -- number of clusters
 , 0)
     FROM Players) b,

    (SELECT @level := 1) c
ORDER BY Score DESC,
         PlayerID ASC
) as a on a.PlayerID = Players.PlayerID
    SET Players.level = a.level

http://sqlfiddle.com/#!9/7f55f9/3