在 MySQL 中计算百分位数排名

Question

我在 MySQL 中有一个非常大的测量数据表，我需要计算每个值的百分位数排名。 Oracle 似乎有一个名为percent_rank 的函数，但我找不到MySQL 的类似函数。 当然，我可以在 Python 中对其进行暴力破解，无论如何我都会使用它来填充表，但我怀疑这会非常低效，因为一个样本可能有 200.000 个观察值。

Answer 1

没有简单的方法可以做到这一点。 请参阅http://rpbouman.blogspot.com/2008 /07/calculate-nth-percentile-in-mysql.html

Answer 2

这是一个相对丑陋的答案，说出来我感到内疚。 也就是说，它可能会帮助您解决问题。

确定百分比的一种方法是计算所有行的数量，并计算大于您提供的数量的行数。 您可以计算大于或小于，并根据需要取倒数。

对您的号码创建索引。
总计 = 选择计数();
less_equal = 选择计数()，其中值 > 索引号；

百分比类似于： less_equal / Total 或 (total - less_equal)/total

确保它们都使用您创建的索引。 如果不是，请调整它们直到它们为止。 解释查询应该在右侧列中包含“使用索引”。 在 select count(*) 的情况下，InnoDB 应该使用索引，MyISAM 应该使用 const 之类的东西。 MyISAM 随时都会知道这个值，而无需计算它。

如果您需要将百分比存储在数据库中，则可以使用上面的设置来提高性能，然后使用第二个查询作为内部选择来计算每行的值。 第一个查询的值可以设置为常量。

这有帮助吗？

雅各布

Answer 3

MySQL 8终于引入了窗口函数，其中，PERCENT_RANK() 您正在寻找的函数。 所以，只需写：

SELECT col, percent_rank() OVER (ORDER BY col)
FROM t
ORDER BY col

你的问题提到了“百分位数”，这是一个略有不同的东西。 为了完整起见，SQL 标准和某些 RBDMS（Oracle、PostgreSQL、SQL Server、Teradata）中有 PERCENTILE_DISC 和 PERCENTILE_CONT 逆分布函数，但在 MySQL 中没有。 使用 MySQL 8 和窗口函数， 您可以再次使用PERCENT_RANK 和FIRST_VALUE 窗口函数来模拟PERCENTILE_DISC。

Answer 4

如果您将 SQL 与 PHP 等过程语言相结合，则可以执行以下操作。 此示例将机场的超额航班阻塞时间分解为百分位数。 在 MySQL 中将 LIMIT x,y 子句与 ORDER BY 结合使用。 不是很漂亮，但可以完成工作（抱歉格式问题）：

$startDt = "2011-01-01";
$endDt = "2011-02-28";
$arrPort= 'JFK';

$strSQL = "SELECT COUNT(*) as TotFlights FROM FIDS where depdt >= '$startDt' And depdt <= '$endDt' and ArrPort='$arrPort'";
if (!($queryResult = mysql_query($strSQL, $con)) ) {
    echo $strSQL . " FAILED\n"; echo mysql_error();
    exit(0);
}
$totFlights=0;
while($fltRow=mysql_fetch_array($queryResult)) {
    echo "Total Flights into " . $arrPort . " = " . $fltRow['TotFlights'];
    $totFlights = $fltRow['TotFlights'];

    /* 1906 flights. Percentile 90 = int(0.9 * 1906). */
    for ($x = 1; $x<=10; $x++) {
        $pctlPosn = $totFlights - intval( ($x/10) * $totFlights);
        echo "PCTL POSN for " . $x * 10 . " IS " . $pctlPosn . "\t";
        $pctlSQL = "SELECT  (ablk-sblk) as ExcessBlk from FIDS where ArrPort='" . $arrPort . "' order by ExcessBlk DESC limit " . $pctlPosn . ",1;";
        if (!($query2Result = mysql_query($pctlSQL, $con)) ) {
            echo $pctlSQL  . " FAILED\n";
            echo mysql_error();
            exit(0);
        }
        while ($pctlRow = mysql_fetch_array($query2Result)) {
            echo "Excess Block is :" . $pctlRow['ExcessBlk'] . "\n";
        }
    }
}

Answer 5

为了获得排名，我想说您需要将表本身（左）外部连接，例如：

select t1.name, t1.value, count(distinct isnull(t2.value,0))  
from table t1  
left join table t2  
on t1.value>t2.value  
group by t1.name, t1.value 

对于每一行，您将计算同一个表中有多少（如果有）行具有较低的值。

请注意，我更熟悉 sqlserver，因此语法可能不正确。 此外，独特的行为可能不适合您想要实现的目标。 但这是总体想法。
然后，要获得真正的百分位数排名，您需要首先获取变量中的值数量（或不同的值，具体取决于您想要采用的约定），并使用上面给出的真实排名计算百分位数排名。

Answer 6

假设我们有一个像这样的销售表：

user_id,units

那么下面的查询将给出每个用户的百分位数：

select a.user_id,a.units,
(sum(case when a.units >= b.units then 1 else 0 end )*100)/count(1) percentile
from sales a join sales b ;

请注意，这将用于交叉连接，因此会导致 O(n2) 复杂性，因此可以被视为未优化的解决方案，但考虑到我们这样做似乎很简单mysql版本没有任何功能。

Answer 7

这是一种不需要联接的不同方法。 在我的例子中（一个包含 15,000 多行的表），它的运行时间约为 3 秒。 （JOIN 方法花费的时间要长一个数量级）。

在示例中，假设 measure 是您要计算百分比排名的列，id 只是行标识符（不是必需的）：

SELECT
    id,
    @prev := @curr as prev,
    @curr := measure as curr,
    @rank := IF(@prev > @curr, @rank+@ties, @rank) AS rank,
    @ties := IF(@prev = @curr, @ties+1, 1) AS ties,
    (1-@rank/@total) as percentrank
FROM
    mytable,
    (SELECT
        @curr := null,
        @prev := null,
        @rank := 0,
        @ties := 1,
        @total := count(*) from mytable where measure is not null
    ) b
WHERE
    measure is not null
ORDER BY
    measure DESC

此方法的功劳去什洛米·诺奇。 他在这里详细介绍了这一点：

http://code.openark.org/blog/ mysql/sql-ranking-without-self-join

我已经在 MySQL 中对此进行了测试，效果很好； 不知道Oracle、SQLServer等。

Answer 8

SELECT 
    c.id, c.score, ROUND(((@rank - rank) / @rank) * 100, 2) AS percentile_rank
FROM
    (SELECT 
    *,
        @prev:=@curr,
        @curr:=a.score,
        @rank:=IF(@prev = @curr, @rank, @rank + 1) AS rank
    FROM
        (SELECT id, score FROM mytable) AS a,
        (SELECT @curr:= null, @prev:= null, @rank:= 0) AS b
ORDER BY score DESC) AS c;