LAG¶

Syntaxe¶

LAG ( <expr> [ , <offset> , <default> ] ) [ { IGNORE | RESPECT } NULLS ]
    OVER ( [ PARTITION BY <expr1> ] ORDER BY <expr2> [ { ASC | DESC } ] [ NULLS { FIRST | LAST } ] )

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.

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