- カテゴリ：
ウィンドウ関数の構文と使用法 （ランキング）
LAG¶
テーブルをそれ自体に結合することなく、同じ結果セットの前の行のデータにアクセスします。
- こちらもご参照ください。
構文¶
LAG ( <expr> [ , <offset> , <default> ] ) [ { IGNORE | RESPECT } NULLS ]
OVER ( [ PARTITION BY <expr1> ] ORDER BY <expr2> [ { ASC | DESC } ] )
引数¶
expr指定したオフセットに基づいて返される式。
offset値を取得する現在の行から後方の行数。たとえば、2の
offsetは、2行の間隔でexprの値を返します。負のオフセットを設定すると、 LEAD 関数を使用した場合と同じ効果があります。
デフォルトは1です。
defaultオフセットがウィンドウの境界から出たときに返す式です。
exprと互換性のある型の式をサポートします。デフォルトは NULL です。
{ IGNORE | RESPECT } NULLSexprに 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 |
+-------+-------+-----------------------------------------------+