【SQL】LAG関数を使った別行との比較

2025年8月11日

やりたいこと

LAG関数を使ってSELECTの検索結果中の別行との比較を行います。

例として以下のような注文テーブルにユーザー別、日別に注文額が保存されているとします。このテーブルからユーザーごとに月毎に注文額を合計し、前月との増減額を求めます。

ユーザー名	注文年月日	注文額
user1	2023-01-01	8111.20
user1	2023-01-02	5927.40
…
user2	2023-01-01	2329.69
user2	2023-01-02	5885.06

テーブル定義

以下のSQLでテーブルを作成します。今回はLAG関数を試すためのテーブルなのでキーやインデックスの設定は省いています。

DROP TABLE IF EXISTS test_orders;
CREATE TABLE test_orders (
    user_name    TEXT,
    order_date   DATE,
    order_amount NUMERIC(10,2)
);

テストデータ作成

以下のSQLでuser1 ~ 5の5ユーザー、2023/01/01 ~ 2024/12/31 までの2年分のデータを投入します。

INSERT INTO test_orders (user_name, order_date, order_amount)
SELECT
  u.user_name,
  d.order_date,
  ROUND((RANDOM() * 9000 + 1000)::numeric, 2) AS order_amount
FROM
  unnest(ARRAY['user1','user2','user3','user4','user5']) AS u(user_name)
CROSS JOIN
  generate_series('2023-01-01'::date, '2024-12-31'::date, '1 day') AS d(order_date)
CROSS JOIN LATERAL
  generate_series(1, (RANDOM() * 3)::int) AS n; 

LAG関数を使った前月データとの比較

ユーザー別、月別の注文額の合計と前月との差分を求めるために、以下のようなSQLを作成しました。

WITH monthly_orders AS (
  SELECT
      user_name,
      to_char(date_trunc('month', order_date), 'YYYY-MM') AS order_month,
      SUM(order_amount) AS total_order_amount
  FROM test_orders
  GROUP BY
      user_name,
      to_char(date_trunc('month', order_date), 'YYYY-MM')
),
with_prev AS (
  SELECT
      user_name,
      order_month,
      total_order_amount,
      LAG(total_order_amount) OVER (
        PARTITION BY user_name ORDER BY order_month
      ) AS previous_month_total_order_amount
  FROM monthly_orders
)
SELECT
    user_name,
    order_month,
    total_order_amount,
    COALESCE(previous_month_total_order_amount, 0) AS previous_month_total_order_amount,
    COALESCE(total_order_amount - previous_month_total_order_amount,0) AS diff_from_previous_month
FROM with_prev
ORDER BY user_name, order_month;

まずGROUP BYとSUMを使ってユーザー別、月別に注文金額を合計します。order_dateは年月日であるため、date_truncで日を切り捨ててGROUP BYすることで月別に注文額を合計します。

この結果を一時テーブル(monthly_orders)とし、LAG関数で前月の注文金額の合計(=一時テーブルの1行前のデータ)を取得します。このテーブルも一時テーブル(with_prev)とします。

一時テーブル(with_prev)のデータから今月と前月の差分を計算します。2023年1月は前月データが存在せずNULLとなるため、COALESCE関数で0に変換しています。