PostgreSQL | SQLite＋MySQL経験者向け、30日で習得するPostgreSQL：高度SQL － Day10 ウィンドウ関数②

SQL PostgreSQL
スポンサーリンク

Day10 後半のゴール

「“ビジネス指標をSQLだけで描ける”感覚を持つ」

前半で、「累積」と「移動平均」の動きとウィンドウフレームのイメージはつかめました。
後半では、それを「どんな場面で使うのか」「どう組み合わせると強いのか」を、実務寄りの例で固めていきます。

狙いは、「グラフやダッシュボードでよく見る線を、SQLだけで再現できる」感覚を持つことです。

例題1：サービスの“累積ユーザー数”を出す

日別新規ユーザーから、累積ユーザー数の線を描く

よくある指標として、「累積ユーザー数」があります。
日別の新規登録数は users テーブルからこう取れるとします。

CREATE TABLE users (
  id         SERIAL PRIMARY KEY,
  email      TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL
);
SQL

まず、「日別の新規ユーザー数」を出します。

WITH daily_signups AS (
  SELECT
    DATE(created_at) AS day,
    COUNT(*)         AS signups
  FROM users
  GROUP BY DATE(created_at)
)
SELECT *
FROM daily_signups
ORDER BY day;
SQL

ここに「累積ユーザー数」をくっつけるのが、ウィンドウ関数の出番です。

WITH daily_signups AS (
  SELECT
    DATE(created_at) AS day,
    COUNT(*)         AS signups
  FROM users
  GROUP BY DATE(created_at)
)
SELECT
  day,
  signups,
  SUM(signups) OVER (
    ORDER BY day
  ) AS cumulative_users
FROM daily_signups
ORDER BY day;
SQL

ここでやっていることは、

日別の新規登録数を出す。
SUM(signups) OVER (ORDER BY day) で、「その日までの累積ユーザー数」を計算する。

結果は、「棒グラフ（signups）＋右肩上がりの線（cumulative_users）」の元データになります。
ダッシュボードでよく見る「累積ユーザー数のグラフ」は、まさにこのSQLの延長線上にあります。

重要なのは、「集計（GROUP BY）」と「累積（ウィンドウ関数）」を分けて考えることです。
まず日別に集計し、その結果に対して累積をかける、という二段構えが基本パターンになります。

例題2：売上の“7日移動平均”でトレンドを見る

日々のブレをならして、流れをつかむ

次は、「売上のトレンドを見るために、7日移動平均を出したい」というケースです。
日別売上テーブル daily_sales があるとします。

CREATE TABLE daily_sales (
  day    DATE    NOT NULL,
  amount INTEGER NOT NULL
);
SQL

まずはそのままの売上を見ます。

SELECT day, amount
FROM daily_sales
ORDER BY day;
SQL

これだけだと、「日によってブレが大きくて、トレンドが見えにくい」ことがあります。
そこで、「7日移動平均」を計算します。

SELECT
  day,
  amount,
  AVG(amount) OVER (
    ORDER BY day
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7
FROM daily_sales
ORDER BY day;
SQL

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW は、「直近7日分（最大7行）」という意味です。

この結果は、

amount → 日々の売上（ギザギザした線）
moving_avg_7 → 7日移動平均（なめらかな線）

という2本の線の元データになります。

ここでの重要ポイントは、「移動平均は“ノイズをならして流れを見る”ためのもの」という理解です。
SQLで移動平均を出せると、「アプリ側で配列をぐるぐる回して計算する」必要がなくなります。

例題3：ユーザーごとの“累積課金額”と“ランク”を同時に出す

累積＋RANKで“課金ランク”を作る

ウィンドウ関数は組み合わせるとさらに強くなります。
例えば、「ユーザーごとの累積課金額と、その中でのランキングを出したい」というケースです。

課金履歴テーブル payments があるとします。

CREATE TABLE payments (
  id         SERIAL PRIMARY KEY,
  user_id    INTEGER NOT NULL,
  amount     INTEGER NOT NULL,
  paid_at    TIMESTAMPTZ NOT NULL
);
SQL

まず、「ユーザーごとの累積課金額」を出します。

WITH user_payments AS (
  SELECT
    user_id,
    SUM(amount) AS total_amount
  FROM payments
  GROUP BY user_id
)
SELECT *
FROM user_payments;
SQL

ここに「ランキング」を付けるのが、RANK の出番です。

WITH user_payments AS (
  SELECT
    user_id,
    SUM(amount) AS total_amount
  FROM payments
  GROUP BY user_id
),
ranked_users AS (
  SELECT
    user_id,
    total_amount,
    RANK() OVER (
      ORDER BY total_amount DESC
    ) AS payment_rank
  FROM user_payments
)
SELECT *
FROM ranked_users
ORDER BY payment_rank
LIMIT 10;
SQL

これで、「累積課金額トップ10ユーザー」が一発で出せます。

ここでのポイントは、

累積（SUM＋GROUP BY）で「ユーザーごとの合計」を作る。
RANK（ウィンドウ関数）で「その合計に順位を付ける」。

という二段構えです。
「累積」と「ランキング」は、ビジネス指標で本当によく組み合わさるので、このパターンは覚えておくとかなり使えます。

例題4：ユーザーごとの“継続利用日数の推移”を見る

累積で“何日続いているか”を可視化する

少し応用的ですが、「ユーザーが何日連続でアクティブか」を見る例も、累積の良い練習になります。
ここではシンプルに、「そのユーザーがアクティブだった日」を user_active_days というテーブルで持っているとします。

CREATE TABLE user_active_days (
  user_id INTEGER NOT NULL,
  day     DATE    NOT NULL
);
SQL

「ユーザーごとに、アクティブ日数の累積」を出すと、
「このユーザーは今まで何日使ってくれているか」という線が描けます。

SELECT
  user_id,
  day,
  SUM(1) OVER (
    PARTITION BY user_id
    ORDER BY day
  ) AS active_days_count
FROM user_active_days
ORDER BY user_id, day;
SQL

SUM(1) は、「行数を数える」という意味です。
PARTITION BY user_id ORDER BY day で、「ユーザーごとに、日付順に累積」しています。

結果は、

user_id = 1
1日目 → active_days_count = 1
2日目 → active_days_count = 2
3日目 → active_days_count = 3

という感じで、「継続日数の推移」がそのまま列として出てきます。

ここからさらに、「30日以上継続しているユーザーだけを抽出する」といった分析にもつなげられます。
累積は、「時間とともに増えていくもの」を扱うときに、とても相性がいいです。

累積・移動平均を選ぶときの思考パターン

「“積み上げたい”のか、“ならしたい”のか」

ここまでの例を踏まえて、頭の中の分岐をシンプルにしておきます。

「その時点までの合計が知りたい」「右肩上がりの線を描きたい」
こういうときは、累積（SUM(...) OVER (ORDER BY ...)）です。

「日々のブレをならしてトレンドを見たい」「直近N日間の平均を見たい」
こういうときは、移動平均（AVG(...) OVER (ORDER BY ... ROWS BETWEEN N PRECEDING AND CURRENT ROW)）です。

どちらも「元の行を残したまま、“線を描くための値”を列として追加する」イメージで、
「積み上げるか」「ならすか」の違いだけを意識すれば、どちらを使うかは自然に決まります。

CTE＋累積・移動平均で“分析用SQL”を組み立てる

「集計 → 累積／移動平均 → 抽出」の3段構成

最後に、構造としてのパターンをまとめます。
累積や移動平均を使うSQLは、だいたい次の3段構成になります。

日別・ユーザー別など、まずは「粒度をそろえた集計」をする（GROUP BY）。
その結果に対して、「累積」や「移動平均」のウィンドウ関数をかける。
必要なら、「特定期間だけ」「特定ユーザーだけ」などを抽出する。

CTEを使うと、これをそのままSQLの形にできます。

WITH daily_signups AS (
  SELECT
    DATE(created_at) AS day,
    COUNT(*)         AS signups
  FROM users
  GROUP BY DATE(created_at)
),
with_cumulative AS (
  SELECT
    day,
    signups,
    SUM(signups) OVER (ORDER BY day) AS cumulative_users
  FROM daily_signups
)
SELECT *
FROM with_cumulative
WHERE day >= CURRENT_DATE - INTERVAL '90 days'
ORDER BY day;
SQL

このように、「集計」「累積」「抽出」を段階に分けて書くと、
SQLがそのまま「分析の手順書」になります。

Day10 後半のまとめ

累積は、「時間順に並べたときに先頭から現在行までを積み上げる」計算として、日別新規ユーザー数から累積ユーザー数を出したり、ユーザーごとの累積課金額を出してRANKと組み合わせて“課金ランク”を作ったり、ユーザーごとの継続利用日数の推移を可視化したりする場面で使える。
移動平均は、「現在行から見た直近N行だけを対象に平均を取る」計算として、日々の売上に対して7日移動平均を出し、ギザギザした売上の線を“なめらかなトレンド線”に変えるのに向いている。
どちらも SUM(...) OVER (ORDER BY ... [フレーム]) や AVG(...) OVER (ORDER BY ... ROWS BETWEEN N PRECEDING AND CURRENT ROW) の形で、「積み上げたいのか（累積）」「ならしたいのか（移動平均）」を選び、CTEと組み合わせて「集計 → 累積／移動平均 → 抽出」という3段構成で書くことで、ダッシュボードや分析でよく見る“線グラフの中身”をSQLだけで表現できる――これが Day10 後半の着地点になる。

前へ
12
スポンサーリンク
タイトルとURLをコピーしました