PostgreSQL | SQLite＋MySQL経験者向け、30日で習得するPostgreSQL：高度SQL － Day15 中間課題

Day15 前半のゴール

「“分析用SQLを設計する頭の使い方”をつかむ」

ここまでで、CTE・ウィンドウ関数・JSON・配列・全文検索と、かなり武器が増えました。
Day15 はその中間課題として、「売上データを分析するSQL」と「ランキングを出すSQL」を、自分で組み立てるイメージを固めていきます。

前半のゴールはこうです。
どんなテーブル構造から、どんな指標を出すかを“言葉で設計”できる。
売上集計の基本（期間・粒度・集計軸）をSQLに落とし込むイメージを持てる。
ランキングを出すために「集計 → ウィンドウ関数」という二段構えを自然に考えられる。

ここではまず、「どんなデータがある前提で、何を出したいのか」を丁寧に分解していきます。

想定する売上データのテーブル構造

「最低限これがあれば“分析っぽいこと”ができる」

まずは、よくあるECサイトっぽいテーブルを1つ決めます。

CREATE TABLE order_items (
  id          SERIAL PRIMARY KEY,
  order_id    INTEGER NOT NULL,
  user_id     INTEGER NOT NULL,
  product_id  INTEGER NOT NULL,
  quantity    INTEGER NOT NULL,
  unit_price  NUMERIC(10,2) NOT NULL,
  ordered_at  TIMESTAMPTZ NOT NULL
);

CREATE TABLE order_items (
  id          SERIAL PRIMARY KEY,
  order_id    INTEGER NOT NULL,
  user_id     INTEGER NOT NULL,
  product_id  INTEGER NOT NULL,
  quantity    INTEGER NOT NULL,
  unit_price  NUMERIC(10,2) NOT NULL,
  ordered_at  TIMESTAMPTZ NOT NULL
);

この1行は、「ある注文の中の、ある商品1種類分」を表しているイメージです。

売上金額は、quantity * unit_price で計算できます。
分析では、だいたい次のような軸で見たくなります。

いつ売れたか（日時・日別・月別）。
誰に売れたか（ユーザー単位）。
何が売れたか（商品単位）。

Day15 では、このテーブルをベースに「売上集計」と「ランキング」を作っていきます。

売上分析で最初に決めるべき3つのこと

「期間・粒度・集計軸」

いきなりSQLを書き始めるのではなく、先に“設計の質問”を自分に投げます。

いつの期間を対象にするか
直近30日なのか、今年なのか、全期間なのか。

どの粒度で見るか
日別なのか、月別なのか、ユーザー別なのか、商品別なのか。

何を集計するか
売上金額なのか、注文数なのか、購入ユーザー数なのか。

例えば、「直近30日の日別売上金額を見たい」と決めたら、
SQLの形はほぼ自動的に決まってきます。

期間 → WHERE ordered_at >= CURRENT_DATE - INTERVAL '30 days'
粒度 → DATE(ordered_at) で日付に丸めて GROUP BY
集計 → SUM(quantity * unit_price)

この「期間・粒度・集計軸」を先に言葉で決める癖をつけると、
分析用SQLの設計が一気に楽になります。

例題1：日別売上金額を出す

「分析用SQLの“基本フォーム”を体に入れる」

まずは一番基本の「日別売上金額」です。

SELECT
  DATE(ordered_at) AS day,
  SUM(quantity * unit_price) AS sales_amount
FROM order_items
GROUP BY DATE(ordered_at)
ORDER BY day;

SELECT
  DATE(ordered_at) AS day,
  SUM(quantity * unit_price) AS sales_amount
FROM order_items
GROUP BY DATE(ordered_at)
ORDER BY day;

ここでやっていることを丁寧に言葉にすると、

DATE(ordered_at) で「日付だけ」に丸める（粒度の決定）。
GROUP BY DATE(ordered_at) で「日別」にグループ化する。
SUM(quantity * unit_price) で「その日の売上金額の合計」を出す（集計軸の決定）。

この形は、どんな分析でもほぼ共通する“基本フォーム”です。

期間を絞りたいなら、こう足します。

WHERE ordered_at >= CURRENT_DATE - INTERVAL '30 days'

WHERE ordered_at >= CURRENT_DATE - INTERVAL '30 days'

「日別売上金額」は、あとで「累積売上」「移動平均」「前年比比較」などの土台にもなるので、
このSQLは“分析の母体”としてしっかりイメージを持っておいてほしいところです。

例題2：商品別売上ランキングの“前段階”

「まずは“商品別売上合計”を出す」

ランキングを出す前に、まずは「商品別売上合計」を出します。
いきなり RANK を書かず、「集計 → ランク付け」の二段構えを意識します。

SELECT
  product_id,
  SUM(quantity * unit_price) AS sales_amount
FROM order_items
GROUP BY product_id
ORDER BY sales_amount DESC;

SELECT
  product_id,
  SUM(quantity * unit_price) AS sales_amount
FROM order_items
GROUP BY product_id
ORDER BY sales_amount DESC;

ここでの設計はこうです。

粒度 → 商品別（product_id で GROUP BY）。
集計 → 売上金額合計（SUM(quantity * unit_price)）。
並び順 → 売上金額の大きい順（ランキングの前段階）。

この時点で、「売上が多い商品順」の一覧が取れています。
ここにウィンドウ関数を足すと、「正式なランキング」になります。

例題3：商品別売上ランキング（RANK の導入）

「集計結果に“順位”を付ける」

さっきの「商品別売上合計」をCTEにして、そこに RANK() をかけます。

WITH product_sales AS (
  SELECT
    product_id,
    SUM(quantity * unit_price) AS sales_amount
  FROM order_items
  GROUP BY product_id
)
SELECT
  product_id,
  sales_amount,
  RANK() OVER (ORDER BY sales_amount DESC) AS sales_rank
FROM product_sales
ORDER BY sales_rank
LIMIT 20;

WITH product_sales AS (
  SELECT
    product_id,
    SUM(quantity * unit_price) AS sales_amount
  FROM order_items
  GROUP BY product_id
)
SELECT
  product_id,
  sales_amount,
  RANK() OVER (ORDER BY sales_amount DESC) AS sales_rank
FROM product_sales
ORDER BY sales_rank
LIMIT 20;

流れを分解すると、

product_sales で「商品別売上合計」を作る（集計）。
その結果に対して RANK() OVER (ORDER BY sales_amount DESC) で順位を付ける（ランキング）。
ORDER BY sales_rank LIMIT 20 で「売上トップ20商品」を出す。

ここでの重要ポイントは、「集計とランキングを分けて考える」ことです。
いきなり1つのSELECTで全部やろうとすると、SQLが一気に読みにくくなります。

CTEを使って「まず集計」「次にランキング」と段階を分けると、
SQLがそのまま“分析の手順書”になります。

例題4：ユーザー別売上ランキング

「軸を変えても“型”は同じ」

今度は、「ユーザー別に、誰が一番お金を使っているか」を見てみます。
やることは商品別と同じで、「集計の軸」を product_id から user_id に変えるだけです。

WITH user_sales AS (
  SELECT
    user_id,
    SUM(quantity * unit_price) AS sales_amount
  FROM order_items
  GROUP BY user_id
)
SELECT
  user_id,
  sales_amount,
  RANK() OVER (ORDER BY sales_amount DESC) AS sales_rank
FROM user_sales
ORDER BY sales_rank
LIMIT 20;

WITH user_sales AS (
  SELECT
    user_id,
    SUM(quantity * unit_price) AS sales_amount
  FROM order_items
  GROUP BY user_id
)
SELECT
  user_id,
  sales_amount,
  RANK() OVER (ORDER BY sales_amount DESC) AS sales_rank
FROM user_sales
ORDER BY sales_rank
LIMIT 20;

設計の視点で見ると、

粒度 → ユーザー別（user_id）。
集計 → 売上金額合計。
ランキング → 売上金額の大きい順。

というだけの違いです。

「集計の軸を変えるだけで、同じ型のSQLが使い回せる」という感覚は、
分析用SQLを書くうえでかなり大事です。

中間課題として意識してほしい“設計の順番”

「SQLを書く前に、紙に“分析の日本語”を書く」

Day15 は「中間課題」という位置づけなので、
あえて“書き方”より“考え方”を強調しておきます。

SQLを書く前に、必ず日本語でこう書き出してみてください。

どのテーブルを使うか。
どの期間を対象にするか。
どの粒度（商品別・ユーザー別・日別など）で見るか。
何を集計するか（売上金額・数量・注文数など）。
ランキングが必要かどうか。

例えば、

「直近30日間について、商品別に売上金額を集計し、売上の高い順にランキングしてトップ20を出す」

と日本語で書けたら、
それをそのままSQLに翻訳するだけです。

WHERE で期間を絞る。
GROUP BY product_id で商品別に集計する。
SUM(quantity * unit_price) で売上金額を出す。
CTEにして RANK() OVER (ORDER BY sales_amount DESC) をかける。
ORDER BY sales_rank LIMIT 20 でトップ20を出す。

この「日本語 → SQL」の変換プロセスを意識することが、
“なんとなく書くSQL”から“意図を持って設計されたSQL”への一歩になります。

Day15 前半のまとめ

売上・ランキング系の分析用SQLは、「どの期間を対象にするか」「どの粒度（商品別・ユーザー別・日別など）で見るか」「何を集計するか（売上金額・数量・注文数など）」という3つを先に日本語で決めてから、GROUP BY と SUM(quantity * unit_price) のような基本集計に落とし込むのが出発点になる。
商品別売上ランキングなら、「商品別に売上合計を出すCTE（product_sales）を作り、その結果に RANK() OVER (ORDER BY sales_amount DESC) をかけてランキングを付ける」という“集計 → ランク付け”の二段構えで書き、ユーザー別ランキングも軸を user_id に変えるだけで同じ型が使い回せる。
Day15 前半の着地点は、「SQLを書く前に“分析の日本語”を書き出し、それを WHERE（期間）＋GROUP BY（粒度）＋集計関数（指標）＋ウィンドウ関数（ランキング） に翻訳する」という設計の流れを、自分の中の標準の思考パターンとして持つこと。