SQLウィンドウ関数の使い方｜分析クエリで必須のOVER句を理解する

-- GROUP BY：行が集約される（顧客ごとに1行になる）
SELECT
  customer_id,
  SUM(amount) AS total_amount
FROM orders
GROUP BY customer_id;
-- 結果：顧客ごとに1行

-- ウィンドウ関数：元の行数を保ちながら集計値を追加できる
SELECT
  customer_id,
  order_id,
  order_date,
  amount,
  SUM(amount) OVER (PARTITION BY customer_id) AS customer_total  -- 顧客合計を各行に付加
FROM orders;
-- 結果：全注文が行として残り、各行に顧客合計が追加される

-- 関数名(引数) OVER (
--   PARTITION BY 集計のグループ化キー  -- ← 省略可：全行を1ウィンドウとして扱う
--   ORDER BY 並び順                   -- ← 省略可：順位やランキング系で必要
--   ROWS/RANGE BETWEEN ... AND ...   -- ← 省略可：移動平均などで使う
-- )

SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date)
--              └─ 顧客ごとに ─┘└─ 注文日順に累計 ─┘

-- 各顧客の「最新注文」だけを取得する
WITH ranked_orders AS (
  SELECT
    customer_id,
    order_id,
    order_date,
    amount,
    product_category,
    -- 顧客ごとに注文日の新しい順で番号を振る
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY order_date DESC, order_id DESC  -- 同日なら新しいIDを優先
    ) AS rn
  FROM orders
  WHERE status = 'completed'
)
SELECT
  customer_id,
  order_id,
  order_date      AS last_order_date,
  amount          AS last_order_amount,
  product_category AS last_category
FROM ranked_orders
WHERE rn = 1;  -- 各顧客の1番目（最新）だけ取り出す

-- 顧客別購買金額ランキング（同額がある場合の違い）
SELECT
  customer_id,
  total_amount,
  ROW_NUMBER()   OVER (ORDER BY total_amount DESC) AS row_num,
  -- → 同額でも連番（同額は処理順で1位・2位）
  RANK()         OVER (ORDER BY total_amount DESC) AS rank_num,
  -- → 同額は同順位（1位・1位・3位）
  DENSE_RANK()   OVER (ORDER BY total_amount DESC) AS dense_rank_num
  -- → 同額は同順位だが次の順位は連続（1位・1位・2位）
FROM customer_ltv;

-- 出力例：
-- customer_id | total_amount | row_num | rank_num | dense_rank_num
-- C001        | 50,000       | 1       | 1        | 1
-- C002        | 50,000       | 2       | 1        | 1  ← 同額
-- C003        | 30,000       | 3       | 3        | 2  ← RANKは3位、DENSE_RANKは2位

-- 日別売上の累計グラフ用データ
SELECT
  order_date,
  daily_revenue,
  -- ORDER BY order_date を指定することで「その日までの累計」になる
  SUM(daily_revenue) OVER (
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS cumulative_revenue,
  -- 先月同日比（前月の同じ日までの累計）も同時に出す
  SUM(daily_revenue) OVER (
    ORDER BY order_date
  ) AS running_total
FROM (
  SELECT
    order_date,
    SUM(amount) AS daily_revenue
  FROM orders
  WHERE order_date >= DATE_TRUNC('month', CURRENT_DATE)
    AND status = 'completed'
  GROUP BY order_date
) daily_agg
ORDER BY order_date;

-- 移動平均（直近7日間）
SUM(daily_revenue) OVER (
  ORDER BY order_date
  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW  -- 今日を含む直近7日間
)

-- 全期間累計
SUM(daily_revenue) OVER (
  ORDER BY order_date
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

-- 全グループ合計（ORDER BYなし = ウィンドウが全行）
SUM(amount) OVER (PARTITION BY customer_id)

-- 顧客ごとの購買間隔を計算する
SELECT
  customer_id,
  order_id,
  order_date,
  amount,
  -- 1つ前の注文日を取得
  LAG(order_date, 1) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
  ) AS prev_order_date,
  -- 前回注文からの経過日数
  DATE_DIFF(
    'day',
    LAG(order_date, 1) OVER (
      PARTITION BY customer_id
      ORDER BY order_date
    ),
    order_date
  ) AS days_since_last_order,
  -- 次の注文日（先行き）も見たい場合はLEAD
  LEAD(order_date, 1) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
  ) AS next_order_date
FROM orders
WHERE status = 'completed'
ORDER BY customer_id, order_date;

-- 月別売上の前月比を計算する
WITH monthly_sales AS (
  SELECT
    DATE_TRUNC('month', order_date)   AS month,
    SUM(amount)                        AS monthly_revenue
  FROM orders
  WHERE status = 'completed'
  GROUP BY DATE_TRUNC('month', order_date)
)
SELECT
  month,
  monthly_revenue,
  LAG(monthly_revenue, 1) OVER (ORDER BY month)    AS prev_month_revenue,
  -- 前月比（%）
  ROUND(
    100.0 * (monthly_revenue - LAG(monthly_revenue, 1) OVER (ORDER BY month))
      / NULLIF(LAG(monthly_revenue, 1) OVER (ORDER BY month), 0)
  , 1)                                              AS mom_growth_pct
FROM monthly_sales
ORDER BY month DESC;

-- 出力例：
-- month      | monthly_revenue | prev_month_revenue | mom_growth_pct
-- 2024-12    | 12,500,000      | 10,800,000         | +15.7
-- 2024-11    | 10,800,000      | 11,200,000         | -3.6
-- 2024-10    | 11,200,000      | 9,600,000          | +16.7

-- RFMスコアリング（SQL道場#01でも詳しく解説）
WITH rfm_raw AS (
  SELECT
    customer_id,
    DATE_DIFF('day', MAX(order_date), DATE '2024-12-31')  AS recency_days,
    COUNT(order_id)                                        AS frequency,
    SUM(amount)                                            AS monetary
  FROM orders
  WHERE
    status = 'completed'
    AND order_date BETWEEN DATE '2024-01-01' AND DATE '2024-12-31'
  GROUP BY customer_id
)
SELECT
  customer_id,
  recency_days,
  frequency,
  monetary,
  -- R：経過日数が少ない（最近買った）ほど高スコア → 降順でNTILE
  NTILE(5) OVER (ORDER BY recency_days DESC)  AS r_score,
  -- F：回数が多いほど高スコア → 昇順でNTILE
  NTILE(5) OVER (ORDER BY frequency ASC)       AS f_score,
  -- M：金額が高いほど高スコア → 昇順でNTILE
  NTILE(5) OVER (ORDER BY monetary ASC)        AS m_score
FROM rfm_raw;

-- 顧客の「入口カテゴリ」と「最新カテゴリ」を同時に把握する
SELECT DISTINCT
  customer_id,
  -- 最初の購買カテゴリ（入口）
  FIRST_VALUE(product_category) OVER (
    PARTITION BY customer_id
    ORDER BY order_date ASC, order_id ASC
  ) AS first_category,
  -- 最新の購買カテゴリ
  LAST_VALUE(product_category) OVER (
    PARTITION BY customer_id
    ORDER BY order_date ASC, order_id ASC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    -- ← LAST_VALUE は ROWS BETWEEN の明示指定が必要（デフォルトが現在行まで）
  ) AS latest_category,
  -- 総購買回数
  COUNT(order_id) OVER (PARTITION BY customer_id) AS total_orders
FROM orders
WHERE status = 'completed'
ORDER BY customer_id;

-- セグメント別の顧客数と全体比率を1クエリで算出
SELECT
  customer_segment,
  COUNT(customer_id)                          AS segment_count,
  -- 全顧客数（OVERに何も指定しない = 全行が対象）
  COUNT(customer_id) OVER ()                  AS total_customers,
  -- セグメント内の顧客数 ÷ 全顧客数 = 構成比
  ROUND(
    100.0 * COUNT(customer_id)
      / COUNT(customer_id) OVER ()
  , 1)                                        AS pct_of_total
FROM customer_segments
GROUP BY customer_segment
ORDER BY segment_count DESC;

-- 出力例：
-- customer_segment    | segment_count | total_customers | pct_of_total
-- 休眠顧客            | 42,150        | 125,000         | 33.7
-- 優良顧客            | 28,750        | 125,000         | 23.0
-- 新規育成中          | 19,300        | 125,000         | 15.4
-- VIPロイヤル顧客     | 11,250        | 125,000         | 9.0
-- その他              | 23,550        | 125,000         | 18.9

-- LTVの分布を把握する（上位10%・25%・50%ラインを確認）
SELECT
  customer_id,
  total_ltv,
  ROUND(
    100.0 * PERCENT_RANK() OVER (ORDER BY total_ltv ASC)
  , 1)                                          AS ltv_percentile,
  NTILE(10) OVER (ORDER BY total_ltv ASC)       AS ltv_decile,  -- 10分位
  CASE
    WHEN PERCENT_RANK() OVER (ORDER BY total_ltv ASC) >= 0.9
    THEN '上位10%（高LTV顧客）'
    WHEN PERCENT_RANK() OVER (ORDER BY total_ltv ASC) >= 0.75
    THEN '上位25%（準高LTV顧客）'
    WHEN PERCENT_RANK() OVER (ORDER BY total_ltv ASC) >= 0.5
    THEN '上位50%（中LTV顧客）'
    ELSE '下位50%（低LTV顧客）'
  END                                           AS ltv_tier
FROM customer_ltv
ORDER BY total_ltv DESC;

-- 7日移動平均売上（直近7日間の平均）
WITH daily_sales AS (
  SELECT
    order_date,
    SUM(amount) AS daily_revenue
  FROM orders
  WHERE
    status = 'completed'
    AND order_date >= DATE_ADD('day', -90, CURRENT_DATE)
  GROUP BY order_date
)
SELECT
  order_date,
  daily_revenue,
  -- 7日移動平均（今日を含む直近7日間の平均）
  ROUND(
    AVG(daily_revenue) OVER (
      ORDER BY order_date
      ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    )
  , 0)                                            AS revenue_7day_ma,
  -- 30日移動平均（季節性を除去した月次トレンド）
  ROUND(
    AVG(daily_revenue) OVER (
      ORDER BY order_date
      ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
    )
  , 0)                                            AS revenue_30day_ma
FROM daily_sales
ORDER BY order_date;

-- コホート別の月次リテンション率を算出する
WITH first_purchase AS (
  -- 各顧客の初回購買月（コホート定義）
  SELECT
    customer_id,
    DATE_TRUNC('month', MIN(order_date))  AS cohort_month
  FROM orders
  WHERE status = 'completed'
  GROUP BY customer_id
),
monthly_activity AS (
  -- 各顧客の月別購買有無
  SELECT DISTINCT
    o.customer_id,
    DATE_TRUNC('month', o.order_date)     AS activity_month
  FROM orders o
  WHERE o.status = 'completed'
),
cohort_activity AS (
  SELECT
    fp.cohort_month,
    ma.activity_month,
    COUNT(DISTINCT ma.customer_id)        AS active_customers,
    -- コホートの初月の人数（ウィンドウ関数で取得）
    FIRST_VALUE(COUNT(DISTINCT ma.customer_id)) OVER (
      PARTITION BY fp.cohort_month
      ORDER BY ma.activity_month ASC
    )                                     AS cohort_size,
    -- 初月からの経過月数
    DATE_DIFF(
      'month', fp.cohort_month, ma.activity_month
    )                                     AS months_since_first
  FROM first_purchase fp
  JOIN monthly_activity ma
    ON fp.customer_id = ma.customer_id
  GROUP BY fp.cohort_month, ma.activity_month
)
SELECT
  cohort_month,
  months_since_first,
  active_customers,
  cohort_size,
  ROUND(100.0 * active_customers / cohort_size, 1) AS retention_rate_pct
FROM cohort_activity
ORDER BY cohort_month, months_since_first;

-- TDのUNIXタイム（time列）と組み合わせる場合
SELECT
  customer_id,
  TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'JST') AS order_date,
  amount,
  SUM(amount) OVER (
    PARTITION BY customer_id
    ORDER BY time  -- UNIXタイムで並び替えるとPRECISEになる
  ) AS cumulative_amount
FROM td_orders
WHERE TD_TIME_RANGE(time, '2024-01-01 00:00:00 JST', '2024-12-31 23:59:59 JST')