【PostgreSQL】一行前の値や次の行の値を参照するには？

昔、メモとして残しておいたのを、こっちでも書き直し。
http://tomofumi.blog.jp/archives/7986782.html

結論から書くと LAG() OVER() を使用する事で解決可能。

サンプル

こんな感じのテーブルがあったとする。

id	status
1	1
2	1
3	1
4	1
5	1
6	1
7	0

-- 上記テーブルをWITHで仮想テーブルで作成
WITH A( id, status ) AS (
    SELECT 1, 1 UNION ALL 
    SELECT 2, 1 UNION ALL
    SELECT 3, 1 UNION ALL
    SELECT 4, 1 UNION ALL
    SELECT 5, 1 UNION ALL
    SELECT 6, 1 UNION ALL
    SELECT 7, 0
)
SELECT * FROM a;

LAG OVERを実行してみる。

WITH A( id, status ) AS (
    SELECT 1, 1 UNION ALL 
    SELECT 2, 1 UNION ALL
    SELECT 3, 1 UNION ALL
    SELECT 4, 1 UNION ALL
    SELECT 5, 1 UNION ALL
    SELECT 6, 1 UNION ALL
    SELECT 7, 0
)
SELECT
    *,
    lag( status ) OVER( ORDER BY id )
FROM a;

実行結果は以下のようになる

1行目のLAGの結果は前の行がないので、nullとなっている。
2行目以降、OVERの条件で並び変えた結果の1行前のデータを表している。

LAGとOVERの説明

LAG( arg0, [arg1, arg2] )
arg0 ... 前の行を確認したいカラム名を記述
arg1 ... 省略可能、何行前の値を参照したいか（デフォルト:1）次の行の値を取りたいなら、ここは-1になる
arg2 .... 前の値がない（1行目の時）どんな値を表示したいか(デフォルト:null)カラム名指定も可能。カラムを指定した場合、現在の行数のカラムの値を表示する

ちなみに2行前の値を取りたい場合、arg1に2を設定する事で可能。
同様に2行先の値を取りたい場合は、arg1に-2を設定する事で可能となる。

OVER([省略可能：ORDER BY カラム1[, カラム2, ... カラムn ]])
OVER([省略可能：PARTITION BY カラム1[, カラム2, ... カラムn ]])

ORDER BYを使用した場合、ソート対象項目を指定する。
PARTITION BYを使用した場合、グループ化対象項目を指定する事が出来る。
※ どちらもメインのSQLで適用した表示順を適用する前の結果を元に前の行（あるいは先の行）を参照してくれる。

OVERには、複数条件を記述することが可能
例えばこんなテーブルがあったとして

WITH A( id, group_id, status ) AS (
    SELECT 1, 1, 1 UNION ALL 
    SELECT 2, 2, 2 UNION ALL
    SELECT 3, 2, 3 UNION ALL
    SELECT 4, 1, 4 UNION ALL
    SELECT 5, 1, 5 UNION ALL
    SELECT 6, 3, 6 UNION ALL
    SELECT 7, 1, 7
)
SELECT
    *
FROM a;

上記仮想テーブルの構造はこんな感じね

これにPARTITION BY でグループ化を行い、ORDER BYで並び替えするとこんな感じ。

WITH A( id, group_id, status ) AS (
    SELECT 1, 1, 1 UNION ALL 
    SELECT 2, 2, 2 UNION ALL
    SELECT 3, 2, 3 UNION ALL
    SELECT 4, 1, 4 UNION ALL
    SELECT 5, 1, 5 UNION ALL
    SELECT 6, 3, 6 UNION ALL
    SELECT 7, 1, 7
)
SELECT
    *,
    lag( status ) OVER( PARTITION BY group_id ORDER BY id )
FROM a;