【SQL】LAG関数で1つ前の行の値を取得（履歴テーブル作成で利用）

SnowflakeのSQLで履歴テーブルを作成したのですが、LAG関数とその関連の関数が便利だったため、使い方について記載します。

SQLによる履歴テーブル作成

以下のようなテーブルAをもとに、IDごとの名称の履歴テーブル（FROM~TO形式）を作成したいという要件がありました。

IDごとに名称変更があった場合にレコードを作成し、開始年月と名称を設定する名称開始年月テーブルを作成するSQLを記載します。

・テーブルA
ID　年月　名称
11　2023-01-01　A
11　2023-02-01　A
11　2023-03-01　A
11　2023-05-01　B
11　2023-06-01　C
11　2023-07-01　B
11　2023-08-01　B
21　2023-01-01　D
21　2023-06-01　D
21　2023-07-01　E

・名称開始年月テーブル
ID　開始年月　名称
11　2023-01-01　A
11　2023-05-01　B
11　2023-06-01　C
11　2023-07-01　B
21　2023-01-01　D
21　2023-07-01　E

SnowflakeのSQLは以下のとおりで、LAG関数を使用します。

WITH RankedData AS (
  SELECT 
    ID,
    年月,
    名称,
    LAG(名称, 1) OVER (PARTITION BY ID ORDER BY 年月) AS prev_name
  FROM your_table_name
)
SELECT
  ID,
  年月 AS 開始年月,
  名称
FROM RankedData
WHERE 名称 != prev_name OR prev_name IS NULL
ORDER BY ID, 開始年月;

上記のSQLでは、LAG関数にPARTITION BY IDを追加し、各IDに対して前の行の「名称」を取得します。その後、フィルタリングして、現在の行の「名称」と前の行の「名称」が異なる行を選択します。

終了年月、終了日付が必要であれば、同様にLAG関数で次の行の開始年月を取得し、必要な月数または日数を引き算すれば設定できます。

LAG関数は非常に便利ですね。

他のデータベースでLAG関数が使用できるかChatGPTに聞いてみたところ、以下の回答が返ってきました。真偽は未確認です。

多くのリレーショナルデータベース管理システム（RDBMS）には、LAG関数に類似したウィンドウ関数や分析関数があります。以下は、いくつかの主要なデータベース製品とそのサポート状況を示しています。

Oracle: OracleはLAG関数をサポートしており、非常に先進的なウィンドウ関数のサポートを持っています。

SQL Server: MicrosoftのSQL ServerもLAG関数をサポートしています。SQL Server 2012以降のバージョンで使用可能です。

PostgreSQL: PostgreSQLもLAG関数をサポートしており、多くのウィンドウ関数が利用可能です。

MySQL: MySQL 8.0以降でLAG関数や他のウィンドウ関数がサポートされています。

DB2: IBMのDB2もLAGを含む多くのウィンドウ関数をサポートしています。

Redshift: Amazon RedshiftもLAG関数をサポートしています。

BigQuery: Google BigQueryもLAGをサポートしており、その他のウィンドウ関数も利用可能です。

これらのデータベース製品の多くでは、LAG関数を使用する際の基本的な構文は一貫しており、違いはわずかです。しかし、特定のデータベース製品の詳細な機能や構文に関しては、公式のドキュメントを参照することをおすすめします。

LAG関数と関連する関数やテクニック

SQLの中には、データ解析や処理をより柔軟に行うための数多くの関数やテクニックが存在します。LAG関数と密接に関連する関数やテクニックについて紹介します。

使用例は、一般的なANSI SQLで記載しています。

LEAD関数

LAG関数が指定した行よりも前の行のデータを取得するのに対し、LEAD関数は指定した行よりも後の行のデータを取得します。

使用例は以下のとおりです。

SELECT id,
       sales,
       LEAD(sales) OVER (ORDER BY id) as next_sales
FROM sales_table;

FIRST_VALUE & LAST_VALUE関数

FIRST_VALUE関数は、指定したパーティションの最初の値を取得します。
LAST_VALUE関数は、指定したパーティションの最後の値を取得します。

使用例は以下のとおりです。

SELECT id, 
       sales,
       FIRST_VALUE(sales) OVER (ORDER BY id) as first_sales,
       LAST_VALUE(sales) OVER (ORDER BY id) as last_sales
FROM sales_table;

WINDOW関数のフレーム仕様

LAGやLEADなどのWINDOW関数の結果は、フレーム仕様を使用してカスタマイズすることができます。デフォルトでは、UNBOUNDED PRECEDINGからCURRENT ROWまでの範囲がフレームとして適用されますが、これを変更することで計算範囲をカスタマイズできます。

使用例は以下のとおりです。

SELECT id, 
       sales,
       SUM(sales) OVER (ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as three_day_sales
FROM sales_table;

CUME_DIST & PERCENT_RANK関数

CUME_DIST関数は、指定した値が全体のどの位置にあるかの累積分布を計算します。PERCENT_RANK関数は、指定した値が全体の中でどのパーセンタイルに位置するかを計算します。

使用例は以下のとおりです。

SELECT id,
       sales,
       CUME_DIST() OVER (ORDER BY sales) as cume_dist,
       PERCENT_RANK() OVER (ORDER BY sales) as percent_rank
FROM sales_table;

これらの関数やテクニックは、LAG関数と同じくデータの特定のパターンや傾向を理解するために非常に役立ちます。