Snowflakeで学ぶ LAG関数

LAG関数とは
LAG 関数の基本構文
よくある利用シーン
サンプルデータでLAG関数を理解する
PARTITION BY と ORDER BY の役割
NULL を回避したい場合
LAG関数を使用する際の注意点
まとめ

LAG関数とは

LAG 関数は一言でいうと、
「前の行の値を参照するための関数」です。

SQL は基本的に「行同士の関係」を意識せずに処理しますが、
LAG 関数を使うことで、
・前日との差分
・前回の売上との比較
・前のステータスとの変化
といった “時系列の文脈” をSQLに持たせることができます。

LAG 関数の基本構文

SnowflakeにおけるLAG関数の基本構文

LAG(対象カラム, 何行前か, デフォルト値)
OVER (
    PARTITION BY 分割条件
    ORDER BY 並び順
)

・LAG = 過去を見る
・OVER句で「どの順番で」「どの単位で」過去に戻るかを決める

よくある利用シーン

LAG関数は以下のような場面でよく活用されます。
・前日比・前月比の算出
・ステータス変更の検知
・累積値の増減チェック
・異常値（急増・急減）の発見

サンプルデータでLAG関数を理解する

以下のようなサンプルデータを用意します。
テーブル名：SALES_DAILY

Case1：LAG関数を使って「前日の売上」を取得する

SELECT
    STORE_ID,
    SALES_DATE,
    SALES_AMOUNT,
    LAG(SALES_AMOUNT, 1) OVER (
        PARTITION BY STORE_ID
        ORDER BY SALES_DATE
    ) AS PREV_SALES_AMOUNT
FROM SALES_DAILY
ORDER BY STORE_ID, SALES_DATE;

クエリの実行結果は以下のようになります。

POINT
・PARTITION BY STORE_ID
　→ 店舗ごとに「前日」を計算
・ORDER BY SALES_DATE
　→ 日付順に「前」を定義
・最初の日は前日が存在しないため NULL

Case2：LAG関数を使って「前日比（差分）」を計算してみる

SELECT
    STORE_ID,
    SALES_DATE,
    SALES_AMOUNT,
    SALES_AMOUNT
      - LAG(SALES_AMOUNT) OVER (
            PARTITION BY STORE_ID
            ORDER BY SALES_DATE
        ) AS DIFF_FROM_PREV_DAY
FROM SALES_DAILY
ORDER BY STORE_ID, SALES_DATE;

クエリの結果は以下のようになります。

増減が一目でわかるようになりました。

PARTITION BY と ORDER BY の役割

・ORDER BY
どの順番で「前」を定義するか
時系列分析ではほぼ必須

・PARTITION BY
どのグループごとに LAG を計算するか
店舗別、ユーザー別、商品別などに使う

例：店舗ごとの前日売上

LAG(sales_amount)
OVER (
    PARTITION BY store_id
    ORDER BY sales_date
)

NULL を回避したい場合

最初の行を 0 扱いにしたい場合は、第3引数を使います。

LAG(SALES_AMOUNT, 1, 0)

以下のようにクエリを修正し、実行すると初日が null にならず 0 で出力されます。

SELECT
    STORE_ID,
    SALES_DATE,
    SALES_AMOUNT,
    LAG(SALES_AMOUNT, 1, 0) OVER (
        PARTITION BY STORE_ID
        ORDER BY SALES_DATE
    ) AS PREV_SALES_AMOUNT
FROM SALES_DAILY
ORDER BY STORE_ID, SALES_DATE;