Catégories :: Syntaxe et utilisation des fonctions de fenêtre (Classement)

LEAD¶

Accède aux données d’une ligne suivante dans le même jeu de résultats sans avoir à joindre la table en soi.

Voir aussi :: LAG

Syntaxe¶

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

Arguments¶

expr

L’expression de chaîne à renvoyer.

offset

Le nombre de lignes en avant 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 LAG.

La valeur par défaut est 1. Si IGNORE NULLS est spécifié, le maximum est 1 000 000.

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¶

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

SELECT emp_id,
       year,
       revenue,
       LEAD(revenue) OVER (PARTITION BY emp_id ORDER BY year) - revenue AS diff_to_next
  FROM sales
  ORDER BY emp_id, year;

+--------+------+----------+--------------+
| EMP_ID | YEAR |  REVENUE | DIFF_TO_NEXT |
|--------+------+----------+--------------|
|      0 | 2010 |  1000.00 |       500.00 |
|      0 | 2011 |  1500.00 |     -1000.00 |
|      0 | 2012 |   500.00 |       250.00 |
|      0 | 2013 |   750.00 |         NULL |
|      1 | 2010 | 10000.00 |      2500.00 |
|      1 | 2011 | 12500.00 |      2500.00 |
|      1 | 2012 | 15000.00 |      5000.00 |
|      1 | 2013 | 20000.00 |         NULL |
|      2 | 2012 |   500.00 |       300.00 |
|      2 | 2013 |   800.00 |         NULL |
+--------+------+----------+--------------+

CREATE OR REPLACE TABLE t1 (
  c1 NUMBER,
  c2 NUMBER);

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

SELECT c1,
       c2,
       LEAD(c2) IGNORE NULLS OVER (ORDER BY c1)
  FROM t1;

+----+------+------------------------------------------+
| C1 | C2   | LEAD(C2) IGNORE NULLS OVER (ORDER BY C1) |
|----+------+------------------------------------------|
|  1 |  5   |                                        4 |
|  2 |  4   |                                        2 |
|  3 | NULL |                                        2 |
|  4 |  2   |                                        6 |
|  5 | NULL |                                        6 |
|  6 | NULL |                                        6 |
|  7 |  6   |                                     NULL |
+----+------+------------------------------------------+