我有一个包含数千行的表格，我想计算其中一个名为 "round" 的字段的90百分位数。
例如，选择处于第90百分位的“round”值。
我没有看到在MySQL中直接完成此计算的简单方法。
能否提供一些关于如何开始这种计算的建议？
谢谢！
9个回答
25
首先，假设您有一张带值列的表格。 您想要获取具有第95个百分位值的行。换句话说，您正在寻找一个比所有值的95%更大的值。
这里是一个简单的答案：
这里是一个简单的答案：
SELECT * FROM
(SELECT t.*, @row_num :=@row_num + 1 AS row_num FROM YOUR_TABLE t,
(SELECT @row_num:=0) counter ORDER BY YOUR_VALUE_COLUMN)
temp WHERE temp.row_num = ROUND (.95* @row_num);
- Abbas Hosseini
2
这是一个非常简洁和吸引人的答案。然而，对于高百分位数来说，订购整个数据集可能不是最优的选择。我们只需获取最大值的(1-百分位数)即可获得百分位数，并且MySQL可以在仅需要少量值（部分排序）时优化ORDER。为此，您可以使用ORDER LIMIT解决方案。 - Rualark
这是一个非常有用的解决方案，但在计数之前我需要对我的数据进行分组。例如，假设有5个不同的日期，每天有20行数据；我需要五个不同的计数器，而不是从1到20的计数器：第一个计数器从1到4表示第一天，第二个计数器从1到6表示第二天，以此类推，直到20条记录和5天。这种情况是否可行？谢谢！ - Javier Blanco
7
比较解决方案：
在我的服务器上，获取130万行中99%分位数所需的秒数：
- LIMIT x,y（使用索引且无where语句）：
0.01秒 - LIMIT x,y（无where语句）：
0.7秒 - LIMIT x,y（有where语句）：
2.3秒 - 全表扫描（无where语句）：
1.6秒 - 全表扫描（有where语句）：
5.7秒
对于使用LIMIT x,y处理大表格的最快解决方案：
- 获取值的数量：
SELECT COUNT(*) AS cnt FROM t - 获取第n个值，其中
n = (cnt - 1) * (1 - 0.95)：SELECT k FROM t ORDER BY k DESC LIMIT n,1
由于mysql不支持在LIMIT子句中指定变量（除了存储过程），因此此解决方案需要两个查询（可以通过存储过程进行优化）。通常，附加查询开销非常低。
如果将索引添加到k列并且不使用复杂的where子句（例如1百万行的表格中只需0.01秒，因为不需要排序），则可以进一步优化此解决方案。
PHP中的实现示例（可以计算列和表达式的分位数）：
function get_percentile($table, $where, $expr, $percentile) {
if ($where) $subq = "WHERE $where";
else $subq = "";
$r = query("SELECT COUNT(*) AS cnt FROM $table $subq");
$w = mysql_fetch_assoc($r);
$num = abs(round(($w['cnt'] - 1) * (100 - $percentile) / 100.0));
$q = "SELECT ($expr) AS prcres FROM $table $subq ORDER BY ($expr) DESC LIMIT $num,1";
$r = query($q);
if (!mysql_num_rows($r)) return null;
$w = mysql_fetch_assoc($r);
return $w['prcres'];
}
// Usage example
$time = get_percentile(
"state", // table
"service='Time' AND cnt>0 AND total>0", // some filter
"total/cnt", // expression to evaluate
80); // percentile
- Rualark
4
SQL标准支持百分位数反分布函数
这将计算每行的
这仅适用于支持窗口函数的MySQL 8+。
PERCENTILE_DISC和PERCENTILE_CONT，以精确地完成此任务。至少在Oracle、PostgreSQL、SQL Server和Teradata中提供了实现。不幸的是，MySQL中没有这个功能。但是，您可以按照以下步骤在MySQL 8中模拟PERCENTILE_DISC：链接。SELECT DISTINCT first_value(my_column) OVER (
ORDER BY CASE WHEN p <= 0.9 THEN p END DESC /* NULLS LAST */
) x,
FROM (
SELECT
my_column,
percent_rank() OVER (ORDER BY my_column) p,
FROM my_table
) t;
这将计算每行的
PERCENT_RANK，给定您的my_column排序，并找到百分位数小于或等于0.9的最后一行。这仅适用于支持窗口函数的MySQL 8+。
- Lukas Eder
2
我曾经尝试解决这个问题很长一段时间，然后我找到了下面的答案。真是太棒了！即使对于大型表格（我使用它的表格包含约500万条记录），速度也相当快，只需要几秒钟。
SELECT
CAST(SUBSTRING_INDEX(SUBSTRING_INDEX( GROUP_CONCAT(field_name ORDER BY
field_name SEPARATOR ','), ',', 95/100 * COUNT(*) + 1), ',', -1) AS DECIMAL)
AS 95th Per
FROM table_name;
你可以想象，只需将table_name和field_name替换为您的表名和列名即可。
如需更多信息，请查看Roland Bouman的原始帖子。
- tony_pets
1
http://www.artfulsoftware.com/infotree/queries.php#68
SELECT
a.film_id ,
ROUND( 100.0 * ( SELECT COUNT(*) FROM film AS b WHERE b.length <= a.length ) / total.cnt, 1 )
AS percentile
FROM film a
CROSS JOIN (
SELECT COUNT(*) AS cnt
FROM film
) AS total
ORDER BY percentile DESC;
这对于非常大的表格可能会很慢
- exussum
5
这并不是回答问题 - 它检索电影长度的自上而下的百分位排名。相反，我要求计算单个值，即在值的第90个百分位数中的“round”字段值。 - Daniel C
使用
where percentile between 89 and 91。 - exussum当我运行这个程序时，出现了“未知列b.length”的错误。你有这个演示的源表数据链接吗？ - Daniel C
b.length和a.length是具有百分位数的列。如果你在寻找名为
round的字段，那么可以使用a.round和b.round。 - exussum我非常喜欢那个解决方案，尽管正如你所说，它真的很慢。 - David Robertson
1
根据Tony_Pets的回答，但是我注意到在类似的问题上必须稍微更改计算，例如90百分位数 - 使用“90/100 * COUNT(*) + 0.5”而不是“90/100 * COUNT(*) + 1”。有时它会跳过百分位点后面两个值，而不是选择下一个更高的值作为百分位数。也许是mysql中整数舍入方式的问题。
即：
.... SUBSTRING_INDEX(SUBSTRING_INDEX( GROUP_CONCAT(fieldValue ORDER BY fieldValue SEPARATOR ','), ',', 90/100 * COUNT(*) + 0.5), ',', -1) as 90thPercentile ....
即：
.... SUBSTRING_INDEX(SUBSTRING_INDEX( GROUP_CONCAT(fieldValue ORDER BY fieldValue SEPARATOR ','), ',', 90/100 * COUNT(*) + 0.5), ',', -1) as 90thPercentile ....
- MetalRules
1
百分位数最常见的定义是指一个数值，该数值下方所包含的分数比例占据了总体的一定百分比。例如，你在一次考试中得到了90分中的67分。但是，除非你知道自己所处的百分位数，否则这个分数没有实际意义。如果你知道自己的分数处于95分位数，那么就意味着你在参加考试的人中表现比95%的人都要好。
此解决方案同样适用于旧版本MySQL 5.7。
SELECT *, @row_num as numRows, 100 - (row_num * 100/(@row_num + 1)) as percentile
FROM (
select *, @row_num := @row_num + 1 AS row_num
from (
SELECT t.subject, pt.score, p.name
FROM test t, person_test pt, person p, (
SELECT @row_num := 0
) counter
where t.id=pt.test_id
and p.id=pt.person_id
ORDER BY score desc
) temp
) temp2
-- optional: filter on a minimal percentile (uncomment below)
-- having percentile >= 80
- Jan
1
在MySQL 8中，你可以使用
ntile窗口函数：SELECT SomeTable.ID, SomeTable.Round
FROM SomeTable
JOIN (
SELECT SomeTable, (NTILE(100) OVER w) AS Percentile
FROM SomeTable
WINDOW w AS (ORDER BY Round)
) AS SomeTablePercentile ON SomeTable.ID = SomeTablePercentile.ID
WHERE Percentile = 90
LIMIT 1
https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_ntile
- Michael Hagar
1
在内部选择中有一个小错误，应该是
SomeTable.ID, (NTILE...。 - Antonín Hoskovec0
在MySQL 8中可行的另一种解决方案：生成您的数据的直方图：
ANALYZE TABLE my_table UPDATE HISTOGRAM ON my_column WITH 100 BUCKETS;
然后只需从information_schema.column_statistics中选择第95条记录：
SELECT v,c FROM information_schema.column_statistics, JSON_TABLE(histogram->'$.buckets',
'$[*]' COLUMNS(v VARCHAR(60) PATH '$[0]', c double PATH '$[1]')) hist
WHERE column_name='my_column' LIMIT 95,1
然后就完成了！你仍然需要决定是取百分位数的下限还是上限，或者可能取平均值 - 但这现在只是一个小任务。最重要的是 - 一旦直方图对象建立起来，这非常快速。
此解决方案的来源：lefred's博客。
- Michal J Figurski
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接