oracle递归子查询,Oracle递归子查询因子转换

这是一个相当晚,但我不确定这可以使用递归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项并将第10项奖励提供给项目2后,它现在得分为105.因此我们将其排名为第1并将第1项下移至第2项.如果这不是所需的行为,则必须进行一些修改.