计算用户的排名 [英] calculate rank of the user

问题描述

我想使用MySQL计算排名。我有一个名为result的表

  result_id test_id exam_id user_id percentage 
 1 5 6 50 57 
 2 5 6 58 76 
 3 5 6 65 42 
  

我想计算用户的排名根据他的user_id和test_id，像user_id（58）有1等级user_id（50）有2等等

我试过像

 从其中test_id = $ test_id（即5）和user_id = $ user_id（即58）的结果中选择百分比
  / pre> 
 
 
但是它给出76并且不给出等级
 
 
 
 
 
 
 从test_id = $ test_id（即5）的结果中选择百分比
  
但它给了我57,76,42 
 
 
 
有任何方法可以计算用户的排名
解决方案
您可以使用变量来计算表的DenseRank，我假设此排名将重新开始为1每个<$ c>组合的test_id和exam_id ，我将其视为分区字段，对于此查询 我们将使用来确定排名是百分比（按降序）;所以最高百分比的排名为1 
  set @patn =''; 
 set @rank = 1; 
 set @valu =''; 
 set @rept = 1; 
 
 SELECT test_id 
，exam_id 
，user_id 
，percentage 
，DenseRank 
 FROM（
 SELECT test_id 
 ，exam_id 
，user_id 
，percentage 
，@rank：= if（@ patn = grp，if（@ valu = percentage，@rank，@rank + @rept），1）as DenseRank 
，@rept：= if（@ patn = grp，if（@ valu = percentage，@rept，1），1）
，@patn：= grp 
，@valu： = percentage 
 FROM（
 select 
 concat（test_id，exam_id）AS grp 
，test_id 
，exam_id 
，user_id 
，percentage 
从结果
订单by 
 test_id 
，exam_id 
，百分比DESC 
）dta 
）dtb; 
  
 
 
 
 
 
 @patn跟踪对  
 
 
 @rept跟踪值列中的更改
 
 
如果value重复，@rept不会增加
 
 
 @rank是计算的密集排名
 
 
 
 
 
内部查询（dta）必须按以下顺序排序：
 
 
 
 
 
 partition列例如column_id 
 
 
和value 
 
 
 
 
 
下一级外部查询（dtb），
 
 
 
 
 
比较当前行分区与上一行分区 
 
 
如果分区更改，则rank = 1 
 
 
否则比较上一行  c>value 
如果value 更改，将排名加1  
 
否则指定相同排名
 
 
 partition;  cb        ; 
 
 
 
 
  $ <
  
最后：
 
 
 
 
 
选择所需的列
 
 
 
 $ b b 
稍微扩展示例数据是一个结果：
  | TEST_ID | EXAM_ID | USER_ID | PERCENTAGE | DENSERANK | 
 | --------- | --------- | --------- | ------------ |  - --------- 
 | 5 | 6 | 580 | 76 | 1 | 
 | 5 | 6 | 50 | 57 | 2 | 
 | 5 | 6 | 581 | 57 | 2 | 
 | 5 | 6 | 582 | 57 | 2 | 
 | 5 | 6 | 583 | 42 | 3 | 
 | 5 | 6 | 65 | 42 | 3 | 
 
 | 6 | 6 | 580 | 76 | 1 | 
  
其中排名计算应该只增加1（因此术语密集）， 。
 
 
 
 nb：分区指向分区由在dbms系统中使用的具有用于排名的分析函数（Oracle，DB2，SQL Server，PostGres和其他）的 
 
 
 
  请参阅此SQLfiddle演示  
 
I want to calculate rank using MySQL. I have a table called result
result_id    test_id    exam_id    user_id    percentage
   1            5          6          50          57
   2            5          6          58          76 
   3            5          6          65          42
I want to calculate the rank of the user according to his user_id and test_id like user_id(58) has 1 rank user_id(50) has 2 and so on

I tried query like
select percentage  from result where test_id=$test_id(i.e 5) and user_id=$user_id(i.e 58)
but it gives 76 and doesn't give the rank 

I also tried
select percentage from result where test_id=$test_id(i.e 5) 
but it gives me 57,76,42

Is there any way by which I can calculate the rank of the user?
解决方案
You can use variables to calculate a DenseRank for a table, I have assumed this ranking would re-commence at 1 for each combination of test_id and exam_id which I treat as the "partition" fields, and for this query the "value" we will use to determine rank is percentage (in descending order); so the highest percentage gets a rank of 1

like this:
set @patn = '';
set @rank = 1;
set @valu = '';
set @rept = 1;

SELECT    test_id
        , exam_id
        , user_id
        , percentage
        , DenseRank
FROM (
      SELECT  test_id
            , exam_id
            , user_id
            , percentage
            , @rank := if(@patn=grp, if(@valu=percentage, @rank, @rank + @rept),1) as DenseRank
            , @rept := if(@patn=grp, if(@valu=percentage, @rept, 1),1)
            , @patn := grp
            , @valu := percentage     
      FROM  (
             select
                      concat(test_id,exam_id) AS grp
                    , test_id
                    , exam_id
                    , user_id
                    , percentage
              from results 
              order by
                      test_id
                    , exam_id
                    , percentage DESC
            ) dta
     ) dtb;



@patn tracks changes to a "partition"
@rept tracks changes in the "value" column
@rept does not increment if "value" repeats
@rank is the calculated dense rank


The inner query (dta) must be sorted by:


the "partition" columns   e.g. test_id, exam_id
and the "value" column       e.g. percentage


Next level outer query (dtb), 


compare current row "partition" with previous row "partition"
if the "partition" changes, then rank = 1
else compare the previous row "value" with current row "value"
if the "value" changes, increment the rank by 1
else assign the same rank
the current "partition" is stored ; to be considered the previous
"partition" on the next row
the current "value" is stored; to be considered the previous
"value" on the next row


Lastly:


the required columns are chosen


Extending the sample data a little this is a result:
| TEST_ID | EXAM_ID | USER_ID | PERCENTAGE | DENSERANK |
|---------|---------|---------|------------|-----------|
|       5 |       6 |     580 |         76 |         1 |
|       5 |       6 |      50 |         57 |         2 |
|       5 |       6 |     581 |         57 |         2 |
|       5 |       6 |     582 |         57 |         2 |
|       5 |       6 |     583 |         42 |         3 |
|       5 |       6 |      65 |         42 |         3 |

|       6 |       6 |     580 |         76 |         1 |
Where the ranking calculation should only step up by 1 (hence the term "dense") and it recommences at one for the next "partition".

nb: the term partition alludes to partition by used in dbms system that do have analytic functions for ranking (Oracle, DB2, SQL Server, PostGres, others)

See this SQLfiddle demo

                        
这篇关于计算用户的排名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！