- Catégories :
Syntaxe et utilisation des fonctions de fenêtre (Classement)
LAG¶
Accède aux données d’une ligne précédente dans le même jeu de résultats sans avoir à joindre la table en soi.
- Voir aussi :
Syntaxe¶
LAG ( <expr> [ , <offset> , <default> ] ) [ { IGNORE | RESPECT } NULLS ]
OVER ( [ PARTITION BY <expr1> ] ORDER BY <expr2> [ { ASC | DESC } ] )
Arguments¶
expr
L’expression à renvoyer en fonction du décalage spécifié.
offset
Le nombre de lignes en arrière par rapport à la ligne en cours pour obtenir une valeur. Par exemple, un
offset
de 2 renvoie la valeurexpr
avec un intervalle de 2 lignes.Notez que le réglage d’un décalage négatif a le même effet que l’utilisation de la fonction LEAD.
La valeur par défaut est 1.
default
L’expression à renvoyer lorsque le décalage sort des limites de la fenêtre. Prend en charge toute expression dont le type est compatible avec
expr
.La valeur par défaut est NULL.
{ IGNORE | RESPECT } NULLS
Ignorer ou respecter les valeurs de NULL lorsqu’une valeur
expr
contient des valeurs NULL :IGNORE NULLS
exclut toute ligne dont l’expression vaut NULL lorsque les lignes décalées sont comptées.RESPECT NULLS
inclut toute ligne dont l’expression vaut NULL lorsque les lignes décalées sont comptées.
Par défaut :
RESPECT NULLS
Notes sur l’utilisation¶
La clause PARTITIONBY partitionne le jeu de résultats généré par la clause FROM en partitions auxquelles la fonction est appliquée. Pour plus d’informations, voir Syntaxe et utilisation des fonctions de fenêtre.
La clause ORDERBY ordonne les données dans chaque partition.
Exemples¶
Créer la table et charger les données :
CREATE OR REPLACE TABLE sales(
emp_id INTEGER,
year INTEGER,
revenue DECIMAL(10,2));
INSERT INTO sales VALUES
(0, 2010, 1000),
(0, 2011, 1500),
(0, 2012, 500),
(0, 2013, 750);
INSERT INTO sales VALUES
(1, 2010, 10000),
(1, 2011, 12500),
(1, 2012, 15000),
(1, 2013, 20000);
INSERT INTO sales VALUES
(2, 2012, 500),
(2, 2013, 800);
Cette requête montre la différence entre les revenus de cette année et ceux de l’année précédente :
SELECT emp_id, year, revenue,
revenue - LAG(revenue, 1, 0) OVER (PARTITION BY emp_id ORDER BY year) AS diff_to_prev
FROM sales
ORDER BY emp_id, year;
+--------+------+----------+--------------+
| EMP_ID | YEAR | REVENUE | DIFF_TO_PREV |
|--------+------+----------+--------------|
| 0 | 2010 | 1000.00 | 1000.00 |
| 0 | 2011 | 1500.00 | 500.00 |
| 0 | 2012 | 500.00 | -1000.00 |
| 0 | 2013 | 750.00 | 250.00 |
| 1 | 2010 | 10000.00 | 10000.00 |
| 1 | 2011 | 12500.00 | 2500.00 |
| 1 | 2012 | 15000.00 | 2500.00 |
| 1 | 2013 | 20000.00 | 5000.00 |
| 2 | 2012 | 500.00 | 500.00 |
| 2 | 2013 | 800.00 | 300.00 |
+--------+------+----------+--------------+
Créer une autre table et charger les données :
CREATE OR REPLACE TABLE t1 (
col_1 NUMBER,
col_2 NUMBER);
INSERT INTO t1 VALUES
(1, 5),
(2, 4),
(3, NULL),
(4, 2),
(5, NULL),
(6, NULL),
(7, 6);
Cette requête montre comment la clause IGNORE NULLS affecte la sortie. Toutes les lignes (sauf la première) contiennent des valeurs non NULL, même si la ligne précédente contenait NULL. Si la ligne précédente contenait NULL, la ligne actuelle utilise la valeur non NULL la plus récente.
SELECT col_1,
col_2,
LAG(col_2) IGNORE NULLS OVER (ORDER BY col_1)
FROM t1
ORDER BY col_1;
+-------+-------+-----------------------------------------------+
| COL_1 | COL_2 | LAG(COL_2) IGNORE NULLS OVER (ORDER BY COL_1) |
|-------+-------+-----------------------------------------------|
| 1 | 5 | NULL |
| 2 | 4 | 5 |
| 3 | NULL | 4 |
| 4 | 2 | 4 |
| 5 | NULL | 2 |
| 6 | NULL | 2 |
| 7 | 6 | 2 |
+-------+-------+-----------------------------------------------+