Utilisation de la fonction LAG dans MySQL

MySQL Version 8.0 a introduit les fonctions de fenêtre MySQL, vous permettant d'effectuer des requêtes dans une méthode plus facile et organisée. Ainsi, augmentant le traitement et les performances. De telles fonctions incluent: rank (), row_rank (), last_value () et bien d'autres.

Dans ce tutoriel, nous nous concentrerons sur l'utilisation de l'une des fonctions MySQL: LAG (). Il s'agit d'une fonction de fenêtre qui vous permet d'accéder et de récupérer la valeur des lignes précédentes de la ligne actuelle dans le même ensemble de résultats.

Syntaxe de base

La syntaxe générale pour l'utilisation de la fonction MySQL LAG () est:

LAG (Expression, OffsetValue, DefaultVar) sur (
Partition par [expression]
Ordre par expression [ASC | DESC]
));

Prenons un moment pour expliquer certains des paramètres de la syntaxe de la fonction LAG ().

Ils sont les suivants:

Expression: Ceci est la valeur renvoyée par la fonction de la ligne qui mène la ligne actuelle par la valeur de décalage spécifiée.

OffsetValue: Cette valeur représente le nombre de lignes précédant la ligne actuelle à partir de laquelle obtenir la valeur. Cette valeur doit être une valeur 0 ou une valeur supérieure à 0.

NOTE: La valeur de 0 représente la ligne actuelle.

Defaultvar: Cette valeur est renvoyée comme la valeur par défaut par la fonction si aucune ligne précédente n'existe. Si la valeur par défaut n'est pas définie dans le paramètre de fonction et qu'aucune ligne précédente n'existe, la fonction renvoie une valeur nul.

Partition par: La clause de partition by divise les lignes dans un ensemble de partition logique. La fonction de décalage est ensuite appliquée aux partitions divisées.

COMMANDÉ PAR: Comme d'habitude, cette valeur spécifie l'ordre des lignes dans les partitions disponibles.

Exemple de cas d'utilisation

Regardons les exemples de cas d'utilisation de la fonction LAG () pour comprendre comment cela fonctionne. Commencez par créer un exemple de base de données appelée Sample_DB;

Drop Database si existe Sample_Database;
Créer une base de données Sample_Database;
Utilisez Sample_Database;
Déposez la table si existe des utilisateurs;
Créer des utilisateurs de table
(
id int key primaire auto_increment,
Nom Varchar (255),
Score Int,
Date d'escrol_date
));
Insérer dans les utilisateurs (id, nom, score, inscrire_date)
Valeurs (1, "Alexandra", 99, '2021-01-10'),
(2, "Jacob", 81, '2021-05-20'),
(3, "Leonard", 67, '2020-01-02'),
(4, "Peter", 88, '2021-03-03'),
(5, "Amy", 100, '2021-05-05');
Sélectionner * chez les utilisateurs;

Maintenant que nous avons un exemple de base de données avec laquelle travailler, nous pouvons procéder et illustrer comment travailler avec la fonction MySQL LAG.

Exemple 1: fonction de décalage sans valeur par défaut
Considérez l'exemple ci-dessous qui applique la fonction de décalage sur le INSCH_DATE avec une valeur de décalage de 1.

SELECT *, LAG (INSCORL_DATE, 1) Over (Order by Id ASC) comme précédent_date à partir de Sample_Database.utilisateurs;

Une fois que nous exécutons la requête ci-dessus, nous obtenons une nouvelle colonne précédente_Date qui contient la valeur précédente de la ligne comme spécifié avec une valeur de décalage de 1. Puisqu'il n'y a pas de valeur antérieure dans la première ligne, la valeur est nul.

Remarque: vous pouvez spécifier la valeur par défaut si une ligne n'a pas de valeur précédente.

La sortie est comme indiqué ci-dessous:

Exemple 2: fonction de décalage avec valeur par défaut
Vous pouvez également spécifier une valeur par défaut pour une ligne où la valeur précédente n'existe pas. Dans notre exemple, nous définirons la valeur par défaut à la date actuelle.

Remarque: Dans cet exemple, nous définirons également la valeur de décalage comme 2 au lieu de 1.

Considérez la requête ci-dessous:

SELECT *, LAG (ISCROLL_DATE, 2, CURDATE ()) sur (Ordre par id ASC) comme précédent_date de Sample_Database.utilisateurs;

Une fois que nous exécutons la requête ci-dessus, nous allons obtenir des valeurs avec une valeur de décalage de deux et la date actuelle comme par défaut pour les valeurs nul.

La sortie est comme indiqué ci-dessous:

Exemple 3: fonction de décalage avec partition par
Nous pouvons utiliser la fonction LAG () avec la clause Partition par clause. Cette clause regroupe d'abord les données dans divers sous-ensembles logiques, puis applique la fonction de décalage aux partitions.

Avant de continuer, voyons les données dans le tableau de l'utilisateur. Considérez la requête suivante:

Insérer dans les utilisateurs (id, nom, score, inscrire_date)
Valeurs (1, "Alexandra", 99, '2021-01-10'),
(2, "Jacob", 81, '2021-05-20'),
(3, "Leonard", 67, '2020-01-02'),
(4, "Peter", 88, '2021-03-03'),
(5, "Amy", 100, '2021-05-05'),
(6, "Tobias", 100, '2020-06-06'),
(7, "Kurtzman", 67, '2020-07-10'),
(8, "Immortal", 50, '2021-03-01'),
(9, "Anthony", 81, '2021-01-01'),
(10, "James", 77, '2021-02-03');

Maintenant que nous avons un tableau avec 10 valeurs, nous pouvons partitionner les données par le score, puis appliquer la fonction de décalage.

L'opération ci-dessus est illustrée dans la requête ci-dessous:

SELECT *, LAG (INCORL_DATE, 1, CURDATE ()) sur (partition par ordre de score par id ASC) comme précédent_date de Sample_Database.utilisateurs;

Dans la requête ci-dessus, nous commençons par partitionner les données en fonction du score, puis appliquant la fonction de décalage avec une valeur de décalage de 1. Nous définissons également la valeur par défaut en tant que date en cours. Le résultat de sortie est comme indiqué ci-dessous:

Remarque: vous pouvez également noter que la première ligne de chaque partition contient la date actuelle, ce qui signifie qu'il n'y a pas de valeur antérieure dans la ligne définie.

Conclusion

Ce tutoriel a expliqué comment la fonction LAG () fonctionne pour obtenir des valeurs des lignes précédentes dans la ligne actuelle.

Récapituler:

• La fonction MySQL est une fonction de fenêtre qui obtient la valeur de la ligne précédente en fonction de la valeur de décalage spécifiée. Ce qui signifie que si la valeur de décalage est 1, elle obtient la valeur directement au-dessus.
• Par défaut, la fonction LAG () utilise une valeur de décalage de 1, sauf si spécifiée explicitement.
• Si les données sont hors de portée (aucune valeur antérieure dans le décalage spécifié), la valeur est définie sur NULL.
• La fonction LAG () accepte également la clause partition par la clause, qui regroupe les données dans diverses partitions logiques basées sur la colonne ou la condition spécifiée.

Merci pour la lecture.