Existe-t-il un moyen d'accéder à la valeur de la "ligne précédente" dans une instruction SELECT ?

Question

Je dois calculer la différence d'une colonne entre deux lignes d'un tableau. Existe-t-il un moyen de le faire directement en SQL ? J'utilise Microsoft SQL Server 2008.

Je cherche quelque chose comme ça :

SELECT value - (previous.value) FROM table

Imaginons que la variable "previous" fasse référence à la dernière ligne sélectionnée. Bien sûr, avec une telle sélection, je me retrouverai avec n-1 lignes sélectionnées dans un tableau de n lignes, mais ce n'est pas un problème, c'est même exactement ce dont j'ai besoin.

Est-ce possible d'une manière ou d'une autre ?

Answer 1

select t2.col from (
select col,MAX(ID) id from 
(
select ROW_NUMBER() over(PARTITION by col order by col) id ,col from testtab t1) as t1
group by col) as t2

Answer 2

La réponse sélectionnée ne fonctionnera que s'il n'y a pas de trous dans la séquence. Toutefois, si vous utilisez un identifiant autogénéré, il est probable qu'il y ait des lacunes dans la séquence en raison d'insertions qui ont été annulées.

Cette méthode devrait fonctionner si vous avez des lacunes

declare @temp (value int, primaryKey int, tempid int identity)
insert value, primarykey from mytable order by  primarykey

select t1.value - t2.value from @temp  t1
join @temp  t2 
on t1.tempid = t2.tempid - 1

Answer 3

Une autre façon de faire référence à la ligne précédente dans une requête SQL est d'utiliser une balise expression récursive de la table commune (CTE) :

CREATE TABLE t (counter INTEGER);

INSERT INTO t VALUES (1),(2),(3),(4),(5);

WITH cte(counter, previous, difference) AS (
  -- Anchor query
  SELECT MIN(counter), 0, MIN(counter)
  FROM t
  UNION ALL
  -- Recursive query
  SELECT t.counter, cte.counter, t.counter - cte.counter
  FROM t JOIN cte ON cte.counter = t.counter - 1
)
SELECT counter, previous, difference
FROM cte
ORDER BY counter;

Résultat :

compteur

précédent

différence

1

0

1

2

1

1

3

2

1

4

3

1

5

4

1

La requête d'ancrage génère la première ligne de l'expression de la table commune cte où il fixe cte.counter à la colonne t.counter dans la première ligne du tableau t , cte.previous à 0, et cte.difference à la première ligne de t.counter .

La requête récursive joint chaque ligne de l'expression du tableau commun cte à la ligne précédente du tableau t . Dans la requête récursive, cte.counter se réfère à t.counter dans chaque ligne du tableau t , cte.previous se réfère à cte.counter dans la ligne précédente de cte y t.counter - cte.counter fait référence à la différence entre ces deux colonnes.

Notez qu'un ETC récursif est plus souple que les fonctions LAG et LEAD, car une ligne peut faire référence à n'importe quel résultat arbitraire d'une ligne précédente. (A fonction ou processus récursif est un processus dont l'entrée est la sortie de l'itération précédente de ce processus, à l'exception de la première entrée qui est une constante).

J'ai testé cette requête à SQLite en ligne .

Answer 4

Vous pouvez utiliser la fonction suivante pour obtenir la valeur de la ligne actuelle et la valeur de la ligne précédente :

SELECT value,
min(value) over (order by id rows between 1 preceding and 1 
preceding) as value_prev
FROM table

Il vous suffit ensuite de sélectionner value - value_prev à partir de là, sélectionnez et obtenez votre réponse