Oracle递归子查询因子转换

http://sqlfiddle.com/#!4/b7217/1

有六名球员排名.他们有id,组ID,分数和排名.

初始状态

+----+--------+-------+--------+
| id | grp_id | score |  rank  |
+----+--------+-------+--------+
| 1  |     1  |  100  | (null) |
| 2  |     1  |   90  | (null) |
| 3  |     1  |   70  | (null) |
| 4  |     2  |   95  | (null) |
| 5  |     2  |   70  | (null) |
| 6  |     2  |   60  | (null) |
+----+--------+-------+--------+

我想把初始得分最高的人给他们排名1.然后我将10点奖励积分给每个拥有相同组ID的人的得分.取下一个最高,分配等级2,分配奖励积分等等,直到没有玩家离开.

用户ID打破了关系.

奖励积分会改变排名. id = 4最初看起来是第二位置95,在领先者后面有100但是有10点奖金,id = 2向上移动并取得现场.

最终状态

+-----+---------+--------+------+
| ID  | GRP_ID  | SCORE  | RANK |
+-----+---------+--------+------+
|  1  |      1  |   100  |    1 |
|  2  |      1  |   100  |    2 |
|  4  |      2  |    95  |    3 |
|  3  |      1  |    90  |    4 |
|  5  |      2  |    80  |    5 |
|  6  |      2  |    80  |    6 |
+-----+---------+--------+------+

解决方法

WITH SAMPLE (ID,GRP_ID,SCORE,RANK) AS (
SELECT 1,1,100,NULL FROM DUAL UNION
SELECT 2,90,NULL FROM DUAL UNION
SELECT 3,70,NULL FROM DUAL UNION
SELECT 4,2,95,NULL FROM DUAL UNION
SELECT 5,NULL FROM DUAL UNION
SELECT 6,60,NULL FROM DUAL)
SELECT ID,RANK FROM SAMPLE
MODEL
DIMENSION BY (ID,GRP_ID)
MEASURES (SCORE,0 RANK,0 LAST_RANKED_GRP,0 ITEM_COUNT,0 HAS_RANK)
RULES
ITERATE (1000) UNTIL (ITERATION_NUMBER = ITEM_COUNT[1,1]) --ITERATE ONCE FOR EACH ITEM TO BE RANKED
(
RANK[ANY,ANY] = CASE WHEN SCORE[CV(),CV()] = MAX(SCORE) OVER (PARTITION BY HAS_RANK) THEN RANK() OVER (ORDER BY SCORE DESC,ID) ELSE RANK[CV(),CV()] END,--IF THE CURRENT ITEM SCORE IS EQUAL TO THE MAX SCORE OF UNRANKED,ASSIGN A RANK
LAST_RANKED_GRP[ANY,ANY] = FIRST_VALUE(GRP_ID) OVER (ORDER BY RANK DESC),SCORE[ANY,ANY] = CASE WHEN RANK[CV(),CV()] = 0 AND CV(GRP_ID) = LAST_RANKED_GRP[CV(),CV()] THEN SCORE[CV(),CV()]+10 ELSE SCORE[CV(),ITEM_COUNT[ANY,ANY] = COUNT(*) OVER (),HAS_RANK[ANY,CV()] <> 0 THEN 1 ELSE 0 END --TO SEPARATE RANKED/UNRANKED ITEMS
)
ORDER BY RANK;

它不是很漂亮,我怀疑有更好的方法可以解决这个问题,但它确实提供了预期的输出.

注意事项：

如果行数超过该数量,则必须增加迭代次数.

这基于每次迭代后的分数进行完全重新排名.因此,如果我们获取您的样本数据,但将项目2的初始分数更改为95而不是90：在排名第1项并将第10项奖励提供给项目2后,它现在得分为105.因此我们将其排名为第1并将第1项下移至第2项.如果这不是所需的行为,则必须进行一些修改.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!