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

Utiliser le décalage fonction :

SELECT value - lag(value) OVER (ORDER BY Id) FROM table

Les séquences utilisées pour les identifiants peuvent sauter des valeurs, de sorte que l'identifiant Id-1 ne fonctionne pas toujours.

Answer 2

SQL n'a pas de notion d'ordre intégrée, vous devez donc ordonner par une colonne pour que cela ait un sens. Quelque chose comme ceci :

select t1.value - t2.value from table t1, table t2 
where t1.primaryKey = t2.primaryKey - 1

Si vous savez comment ordonner les choses, mais pas comment obtenir la valeur précédente à partir de la valeur actuelle (par exemple, vous voulez ordonner par ordre alphabétique), je ne connais pas de moyen de le faire dans le langage SQL standard, mais la plupart des implémentations SQL disposent d'extensions pour le faire.

Voici une méthode pour le serveur SQL qui fonctionne si vous pouvez ordonner les lignes de manière à ce que chacune soit distincte :

select  rank() OVER (ORDER BY id) as 'Rank', value into temp1 from t

select t1.value - t2.value from temp1 t1, temp1 t2 
where t1.Rank = t2.Rank - 1

drop table temp1

Si vous avez besoin de départager des personnes, vous pouvez ajouter autant de colonnes que nécessaire à l'ORDER BY.

Answer 3

Oracle, PostgreSQL, SQL Server et bien d'autres moteurs de SGBDR disposent de fonctions analytiques appelées LAG y LEAD qui font précisément cela.

Dans SQL Server antérieur à 2012, vous devez procéder comme suit :

SELECT  value - (
        SELECT  TOP 1 value
        FROM    mytable m2
        WHERE   m2.col1 < m1.col1 OR (m2.col1 = m1.col1 AND m2.pk < m1.pk)
        ORDER BY 
                col1, pk
        )
FROM mytable m1
ORDER BY
      col1, pk

donde COL1 est la colonne par laquelle vous commandez.

Disposer d'un index sur (COL1, PK) améliorera grandement cette requête.

Answer 4

WITH CTE AS (
  SELECT
    rownum = ROW_NUMBER() OVER (ORDER BY columns_to_order_by),
    value
  FROM table
)
SELECT
  curr.value - prev.value
FROM CTE cur
INNER JOIN CTE prev on prev.rownum = cur.rownum - 1

Answer 5

JOINDRE LEFT la table à elle-même, avec la condition de jointure élaborée de sorte que la ligne correspondant à la version jointe de la table soit une ligne précédente, pour votre définition particulière de "précédente".

Mise à jour : j'ai d'abord pensé que vous vouliez conserver toutes les lignes, avec des NULL pour la condition où il n'y avait pas de ligne précédente. A la relecture, vous voulez juste que ces lignes soient éliminées, donc vous devriez faire une jointure interne plutôt qu'une jointure gauche.

---

Mise à jour :

Les versions plus récentes de Sql Server disposent également des fonctions LAG et LEAD Windowing qui peuvent être utilisées à cette fin.