製造業でデータ分析 | PostgreSQL | LAG、LEADを活用してレコードを１行ずらす

みなさん、こんにちは！

今回は前回同様で時系列データを扱うためのクエリを紹介します。

イメージ

データベース上に下図のように、「1：作業開始時」と「0：停止時」の「時刻」が交互に記録されている状況を考えます。

これを下図のように変換したいと思います。

要は「レコード１行の中に”開始時刻”と”終了時刻”を持たせる」ように変換を掛けます。

LAGの活用

上記の変換で役に立つのが「LAG」です。

LAGの役割

LAG関数を用いることで、N行ずらす（前 or 後）ことができます。

引数は下記の①～④です。

LAG( ①ずらす対象の列, ②何行ずらすか？, ③ずれた際に発生する空欄の処理 ) OVER( ORDER BY ④並び替え対象の列)

LAGに「ずらす対象」、OVER句に「並び替え対象の列」

例１：１行後ろにずらす場合

SELECT
    update_time as 開始時間
    ,lag( update_time,1 ) OVER( ORDER BY update_time ) as 終了時間
FROM product_results

例２：１行前にずらす場合

SELECT
    update_time as 開始時間
    ,lag( update_time,-1 ) OVER( ORDER BY update_time ) as 終了時間
FROM product_results

補足：LEAD句

SELECT
    update_time as 開始時間
    ,lead( update_time,1 ) OVER( ORDER BY update_time ) as 終了時間
    ,lead( update_time,1 ) OVER( ORDER BY update_time ) -update_time as 作業時間
FROM product_results

LAG句と似た役割で「LEAD句」があります。

LEAD( update_time,1 ) と LAG( update_time,-1 )は同じ結果が得られます。

例３：NULLに別の値を入れる場合

SELECT
update_time as 開始時間
,lag( update_time,1,‘9999/9/9’ ) OVER( ORDER BY update_time ) as 終了時間
FROM product_results

クエリ

以上を踏まえたうえで作成したクエリです。

SELECT
    update_time as 開始時間
    ,lag( update_time,-1 ) OVER( ORDER BY update_time ) as 終了時間
    ,lag( update_time,-1 ) OVER( ORDER BY update_time ) -update_time as 作業時間
FROM product_results

想定通りの形に変換することができました。