カテゴリ：

ウィンドウ関数の構文と使用法 （ランキング）

LAG

テーブルをそれ自体に結合することなく、同じ結果セットの前の行のデータにアクセスします。

こちらもご参照ください。

LEAD

構文

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

引数

expr

指定したオフセットに基づいて返される式。

offset

値を取得する現在の行から後方の行数。たとえば、2の offset は、2行の間隔で expr の値を返します。

負のオフセットを設定すると、 LEAD 関数を使用した場合と同じ効果があります。

デフォルトは1です。

default

オフセットがウィンドウの境界から出たときに返す式です。 expr と互換性のある型の式をサポートします。

デフォルトは NULL です。

{ IGNORE | RESPECT } NULLS

expr に NULL の値が含まれている場合に、 NULL の値を無視するか尊重するかを指定します。

  • IGNORE NULLS は、オフセット行をカウントする際に、式が NULL に評価される行を除外します。

  • RESPECT NULLS には、オフセット行を数えたときに式が NULL に評価される行が含まれます。

デフォルト: RESPECT NULLS

使用上の注意

  • PARTITION BY 句は、 FROM 句によって生成された結果セットを関数が適用されるパーティションに分割します。詳細については、 ウィンドウ関数の構文と使用法 をご参照ください。

  • ORDER BY 句は、各パーティション内のデータを順序付けます。

例

テーブルを作成し、データをロードします。

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

別のテーブルを作成し、データをロードします。

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

このクエリは、 IGNORE NULLS 句が出力にどのように影響するかを示しています。前の行に NULLが含まれていたとしても、すべての行（最初の行を除く）には非NULL 値が含まれています。前の行に NULLが含まれていた場合、現在の行は最新の非NULL 値を使用します。

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