mysql - 计算 MySQL 中的百分比排名

Question

我有一个包含成本的数据表。对于任何给定的记录，我想知道该记录相对于其他成本的位置。

基本上，我想知道(有多少记录成本较低)/(记录总数)。

到目前为止，我已经想出了:

SELECT (SUM(IF(r.cost > c.cost, 1, 0)) + 1) / COUNT(1) as percent_rank 
FROM record r, 
   (SELECT cost FROM record WHERE cost IS NOT NULL) as c 
WHERE r.id = 10;

在此查询上运行 EXPLAIN 会产生:

+----+-------------+------------+-------+---------------+---------+---------+-------+-------+-------------+
| id | select_type | table      | type  | possible_keys | key     | key_len | ref   | rows  | Extra       |
+----+-------------+------------+-------+---------------+---------+---------+-------+-------+-------------+
|  1 | PRIMARY     | r          | const | PRIMARY       | PRIMARY | 4       | const |     1 |             |
|  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL    | NULL    | NULL  | 21469 |             |
|  2 | DERIVED     | record     | ALL   | NULL          | NULL    | NULL    | NULL  | 21469 | Using where |
+----+-------------+------------+-------+---------------+---------+---------+-------+-------+-------------+

是否有更好的方法来运行此查询？ 21469 是表中的所有记录。我有点担心交叉连接，但此查询将始终使用 id = something 运行，因此它只有 1 * 总行数。

Answer 1

我建议在子查询中获取 r.cost 的值，或者如果可能的话将其放入查询中引用的变量中而不是进行连接，因为这样可以简化您需要的逻辑:

DECLARE @thisCost Cost_Datatype;
SELECT @thisCost = cost FROM record WHERE id = 10;

表示查询现在看起来像:

SELECT (SUM(IF(@thisCost > cost, 1, 0)) + 1) / COUNT(1) as percent_rank 
FROM record 
WHERE cost IS NOT NULL 

移除了连接的复杂性，也可以移除条件，并且引擎允许通过将总行数放入它自己的(子)查询中来将查询作为集合操作来回答:

SELECT (Count(*) / (SELECT Count(*) FROM record)) as [% with lower cost]
FROM record
WHERE cost IS NOT NULL AND @thisCost > cost

这行吗？