计算MySQL中的百分位排名

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

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

在您的号码上创建一个索引。 总计=选择计数( ; less_equal=选择计数( )其中,value>indexed_number;

百分比应该是:小于等于/总计或(总计-小于等于)/总计

确保它们都使用您创建的索引。如果不是,调整它们直到它们是。解释查询的右边列应该有“使用索引”。在select count(*)的情况下,应该使用innodb的index和myisam的const。Myisam将随时知道这个值,而不必计算它。

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

这有帮助吗?

雅各伯

这里有一个不需要加入的不同方法。在我的例子中(一个有15000+行的表),它在3秒钟内运行。(连接方法需要更长的数量级)。

在样本中,假设 测量 是计算百分比排名的列,并且 身份证件 只是行标识符(不需要):

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

这种方法归功于Shlomi Noach。他在这里详细地写到:

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

我在MySQL中测试过这个,它工作得很好;不知道Oracle、SQLServer等。

要做到这一点并不容易。看见 http://rpbouman.blogspot.com/2008/07/calculating-nth-percentile-in-mysql.html

如果要将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";
        }
    }
}

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;

MySQL8最终引入了窗口函数,其中 PERCENT_RANK() 你正在寻找的功能。所以,只需写:

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

你的问题提到“百分位数”,这是一个稍微不同的事情。为了完整,有 PERCENTILE_DISC 和 PERCENTILE_CONT SQL标准和一些RBDMS(Oracle、PostgreSQL、SQL Server、Teradata)中的反向分布函数,但MySQL中没有。使用mysql 8和window函数, you can emulate PERCENTILE_DISC , however, again using the PERCENT_RANK and FIRST_VALUE window functions .

为了获得排名,我想说你需要(左)外部加入表格本身,比如:

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,所以语法可能不正确。另外,对于你想要达到的目标,不同的人可能没有正确的行为。但这是一般的想法。
然后,要获得实际的百分位排名,您需要首先获得变量中的值的数量(或根据您想要采用的约定的不同值),然后使用上面给出的实际排名计算百分位排名。

假设我们有一个销售表,比如:

用户标识

然后,下面的查询将给出每个用户的百分比:

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版本中没有任何函数,因此看起来很简单。

不确定op的“百分位排名”是什么意思,但要获得一组值的给定百分位,请参见 http://rpbouman.blogspot.com/2008/07/calculating-nth-percentile-in-mysql.html SQL计算可以很容易地更改以生成另一个或多个百分点。

一个注意事项:我必须稍微改变计算方法,例如90%的数值——“90/100*计数(*)+0.5”,而不是“90/100*计数(*)+1”。有时它跳过了排序列表中超过百分位点的两个值,而不是为百分位选择下一个更高的值。也许整数舍入在MySQL中的工作方式。

IE:

…substring_index(substring_index(group_concat(fieldvalue order by fieldvalue separator),'),',',90/100*count(*)+ 零点五 ),',',-1)作为第90个百分点…