LAG¶

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 valeur expr 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.

Notes sur l’utilisation¶

• Lorsque la clause IGNORE NULLS est définie, toute ligne dont l’expression est évaluée à NULL n’est pas incluse lorsque les lignes de décalage sont comptées. Si { IGNORE | RESPECT } NULLS n’est pas spécifié, la valeur par défaut est RESPECT NULLS.

• La clause PARTITION BY 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 sur cela et sur la clause ORDER BY, voir Fonctions de la fenêtre.

• La clause ORDER BY 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));

Copy

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);

Copy

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 |
+--------+------+----------+--------------+

Copy

Créer une autre table et charger les données :

CREATE OR REPLACE TABLE t1 (col_1 NUMBER, col_2 NUMBER);

Copy

INSERT INTO t1 VALUES 
    (1, 5),
    (2, 4),
    (3, NULL),
    (4, 2),
    (5, NULL),
    (6, NULL),
    (7, 6);

Copy

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 |
+-------+-------+-----------------------------------------------+

Copy