Existe-t-il un moyen en SQL d'attribuer à une ligne la valeur suivante qui est supérieure à elle-même ?

Question

Existe-t-il un moyen de calculer une requête où une ligne dans une nouvelle colonne prend la prochaine valeur qui est supérieure à elle-même dans une colonne différente. J'ai essayé d'utiliser une méthode de rolling max, mais je me suis heurté à un mur car cela implique de spécifier un nombre spécifique de lignes à comparer comme ceci :

select t.*,
       max(val) over (order by date rows between 2 preceding and current row) max3
from t;

Où nous devons spécifier que le maximum sera calculé entre la ligne actuelle et 2 lignes précédentes.

Cependant, ce que je voudrais faire est quelque chose comme ceci :

---------------------------------------
val        |   id    |   newcol       |
2          |  ABC    |   4            |
2          |  ABC    |   4            |
2          |  ABC    |   4            |
4          |  ABC    |   10           |
4          |  ABC    |   10           |
10         |  ABC    |   9999         |
10         |  ABC    |   9999         |
3          |  EFG    |   5            |
5          |  EFG    |   9            |
9          |  EFG    |   9999         |
2          |  HIJ    |   9999         |
7          |  KLM    |   11           |
11         |  KLM    |   12           |
12         |  KLM    |   9999         |

Où je partitionne par l'id et la nouvelle col est assignée à la prochaine valeur dans la col val qui est supérieure à sa propre valeur (indépendamment du nombre de lignes en avant) et assigne un grand nombre arbitraire (9999) si c'est déjà la valeur la plus élevée dans sa partition.

Jusqu'à présent, j'ai trouvé ceci :

SELECT val, 
id,
CASE WHEN val = MAX(val) OVER(PARTITION BY id) THEN 9999 
ELSE MAX(val) OVER(partition by id ORDER BY val ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 
END AS newcol
FROM table
ORDER BY ID;

Cependant, cela ne fait qu'attribuer la valeur maximale de la partition à toute valeur qui n'est pas le maximum, comme ceci :

---------------------------------------
val        |   id    |   newcol       |
2          |  ABC    |   10           |
2          |  ABC    |   10           |
2          |  ABC    |   10           |
4          |  ABC    |   10           |
4          |  ABC    |   10           |
10         |  ABC    |   9999         |
10         |  ABC    |   9999         |
3          |  EFG    |   9            |
5          |  EFG    |   9            |
9          |  EFG    |   9999         |
2          |  HIJ    |   9999         |
7          |  KLM    |   11           |
11         |  KLM    |   11           |
12         |  KLM    |   9999         |

Toute aide serait très appréciée.

Answer 1

Vous avez besoin d'une clause d'intervalle afin d'examiner des valeurs distinctes. Ordonnez par valeur et obtenez la valeur minimale de toutes les valeurs suivantes (supérieures).

coalesce(min(val) over (partition by id
                        order by val
                        range between 1 following and unbounded following),
         9999) as newcol

Answer 2

Dans SQL Server, je pourrais :

with T as (
select *
from (values
 ('2022-01-01',2,'ABC',4)
,('2022-01-02',2,'ABC',4)
,('2022-01-03',2,'ABC',4)
,('2022-01-04',4,'ABC',10)
,('2022-01-05',4,'ABC',10)
,('2022-01-06',10,'ABC',9999)
,('2022-01-07',10,'ABC',9999)
,('2022-01-08',3,'EFG',5)
,('2022-01-09',5,'EFG',9)
,('2022-01-10',9,'EFG',9999)
,('2022-01-11',2,'HIJ',9999)
,('2022-01-12',7,'KLM',11)
,('2022-01-13',11,'KLM',12)
,('2022-01-14',12,'KLM',9999)
) T (Date, val, id, Expected)
),
 TDR as (
select
  t.*
  , DR=DENSE_RANK() over (partition by id order by val asc)
from t
)
select 
TDRT.*,
coalesce(TDRN.val,9999) as NextHigherVal
from 
    TDR as TDRT
    left join
    (select id, DR, val
     from TDR
     group by id, dr, val
     ) as TDRN
    on TDRT.id=TDRN.id
        and TDRT.DR+1=TDRN.DR

Ce n'est en aucun cas la seule solution.