LEAD & LAG Functions

Fait partie de la section Les fondamentaux du Journey SQL de Coddy — leçon 61 sur 72.

Les fonctions LEAD et LAG nous permettent d'accéder à la valeur de la ligne actuelle de n étapes en arrière ou de n étapes en avant.

Par exemple, si nous voulons calculer le ratio du chiffre d'affaires d'une entreprise pour la ligne actuelle et un mois auparavant, nous pouvons extraire la valeur du mois précédent :

id	revenue	month
1	532	5
2	492	6
3	393	7
4	723	8

SELECT id, revenue, LAG(revenue, 1) OVER (ORDER BY MONTH) as prev_month_revenue
FROM table1 ORDER BY id

Cela créera le tableau suivant :

id	revenue	prev_month_revenue
1	532
2	492	532
3	393	492
4	723	393

De cette façon, nous pouvons calculer le ratio prev_month_revenue/revenue.

Si nous utilisions plutôt la fonction LEAD, elle prendrait le chiffre d'affaires du mois suivant pour chaque ligne :

SELECT id, revenue, LEAD(revenue, 1) OVER (ORDER BY MONTH) as next_month_revenue
FROM table1 ORDER BY id

id	revenue	next_month_revenue
1	532	492
2	492	393
3	393	723
4	723

Défi

Facile

Tables et colonnes disponibles :
air_conditioners : id, efficiency, strength, month

Comparez l'efficacité de chaque climatiseur avec celui précédent.

Écrivez une requête qui affiche l'efficacité de chaque climatiseur ainsi que l'efficacité du climatiseur précédent installé (basé sur id). Calculez également la différence d'efficacité entre le climatiseur actuel et le précédent. Triez le résultat par id et efficiency par ordre croissant.

Colonnes de sortie attendues :

id
efficiency
previous_efficiency (en utilisant LAG)
efficiency_difference (efficacité actuelle - efficacité précédente)

Enfin, après cette requête, filtrez la ligne où previous_efficiency est vide ou efficiency_difference est vide comme suit :

SELECT * FROM (
    -- Your query here
)
WHERE previous_efficiency != 0

Aide-mémoire

Les fonctions LAG et LEAD permettent d'accéder aux valeurs des lignes précédentes ou suivantes :

Fonction LAG - obtient la valeur de la ligne précédente :

LAG(column_name, n) OVER (ORDER BY column)

Fonction LEAD - obtient la valeur de la ligne suivante :

LEAD(column_name, n) OVER (ORDER BY column)

Exemple utilisant LAG pour obtenir le revenu du mois précédent :

SELECT id, revenue, LAG(revenue, 1) OVER (ORDER BY month) as prev_month_revenue
FROM table1 ORDER BY id

Exemple utilisant LEAD pour obtenir le revenu du mois suivant :

SELECT id, revenue, LEAD(revenue, 1) OVER (ORDER BY MONTH) as next_month_revenue
FROM table1 ORDER BY id

La première ligne aura NULL pour la valeur LAG, et la dernière ligne aura NULL pour la valeur LEAD.

Essayez vous-même

Testez-vous

Cette leçon comprend un petit quiz. Commencez la leçon pour y répondre et suivre votre progression.

LEAD & LAG Functions

Aide-mémoire

Toutes les leçons de Les fondamentaux