Oracle 递归子查询因子转换

Question

我正在尝试使用此递归 SQL 功能，但无法让它执行我想要的操作，甚至无法关闭。我在一个展开的循环中编写了逻辑，询问是否可以将其转换为单个递归 SQL 查询，而不是我使用的表更新样式。

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 |
+-----+---------+--------+------+

Answer 1

这有点晚了，但我不确定这可以使用递归 CTE 来完成。然而，我确实想出了一个使用 MODEL 子句的解决方案:

WITH SAMPLE (ID,GRP_ID,SCORE,RANK) AS (
SELECT 1,1,100,NULL FROM DUAL UNION
SELECT 2,1,90,NULL FROM DUAL UNION
SELECT 3,1,70,NULL FROM DUAL UNION
SELECT 4,2,95,NULL FROM DUAL UNION
SELECT 5,2,70,NULL FROM DUAL UNION
SELECT 6,2,60,NULL FROM DUAL)
SELECT ID,GRP_ID,SCORE,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(),CV()] END,
ITEM_COUNT[ANY,ANY] = COUNT(*) OVER (),
HAS_RANK[ANY,ANY] = CASE WHEN RANK[CV(),CV()] <> 0 THEN 1 ELSE 0 END --TO SEPARATE RANKED/UNRANKED ITEMS
)
ORDER BY RANK;

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

注意事项:

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

这会根据每次迭代后的分数进行完整的重新排名。因此，如果我们采用您的样本数据，但将第 2 项的初始分数更改为 95 而不是 90:在对第 1 项进行排名并为第 2 项提供 10 分奖励后，它现在的分数为 105。因此我们将其列为第 1并将第 1 项下移到第 2 项。如果这不是所需的行为，则必须进行一些修改。