受注KPI・顧客分析・在庫リスク評価をSQLで自動化する

SQL 100本ノック 第8章（No.071〜No.080）：実務データ分析SQL

[!NOTE] 本資料は、数理工房（もしくは代表である和山個人）が過去に企業研修において使用した notebook を企業様の許可を得て再構成・編集のうえ公開しています。 掲載データはすべて架空のものであり、実在する企業・工場・数値とは一切関係ありません。

はじめに：この記事で扱う製造業の実務課題

製造業の営業・販売管理・在庫管理では、以下のような問いが毎月発生します。

• 「今月の受注件数・売上金額はどうか？前月比・前年比は？」
• 「得意先 1 社あたりの平均受注金額（客単価）は妥当か？」
• 「最も多く発注している得意先はどこか？」
• 「今年初めて取引した新規得意先は何社か？」
• 「2 回以上発注している得意先の割合（リピート率）は？」
• 「最後に発注したのがかなり前の得意先はどこか？（休眠顧客）」
• 「RFM 分析で VIP 顧客と休眠顧客を区別したい」
• 「在庫と直近 3 ヶ月の出荷実績を比較して過不足を確認したい」
• 「在庫切れリスクのある製品を早期に検知したい」
• 「異常に受注が増減している製品を自動で検出したい」

本章では、これら 実務で最も頻繁に使われる SQL パターン を製造業の受注・在庫データで実践します。

テーブル	概要	件数
customers	得意先マスタ（製造業 B2B 顧客）	20 社
products	製品マスタ	10 品目
orders	受注履歴（2024 年 1〜12 月）	約 620 件
inventory	在庫テーブル（2024 年 12 月末時点）	10 品目

現場でよくある状況

中堅製造業の営業マネージャーが毎月末に行う業務を想像してください。

1. 複数の Excel を開いて手作業でコピー＆ペースト → 月次売上報告書 を作成
2. 「この得意先は先月も発注したか」を目視で確認 → リピート顧客を把握
3. 在庫不足リスクの確認は担当者の勘に頼っている → 在庫アラート
4. 売上の急増・急減は事後に気づくことが多い → 異常検知

これらはすべて SQL で自動化できます。
定期バッチ + BI ツール連携により、毎月のレポート作成時間を数時間から数分に短縮 できます。

なぜこの問題は判断が難しいのか

製造業の受注・顧客データ分析が難しい主な理由は 「比較の軸が複数存在する」 ことです。

$$\text{受注KPI} = f(\text{時系列}, \text{得意先}, \text{製品}, \text{在庫})$$

比較軸	難しさ	対応するノック
時系列（月別推移）	季節性・外部要因の影響	No.071, 080
顧客別（誰が何を買っているか）	顧客数が多く手動集計が困難	No.072, 073
顧客の状態変化（新規/継続/休眠）	時間軸と顧客軸の組み合わせ	No.074, 075, 076
顧客の総合評価（RFM）	3 次元評価の同時実施	No.077
在庫と需要の突合	生産計画・発注リードタイムの考慮	No.078, 079

また、RFM 分析のような多軸評価は Excel では管理が困難で、SQL + BIツールとの組み合わせが実務では標準的です。

今回扱うノックの全体像

No.	テーマ	SQL の機能	製造業での用途
071	売上KPIを集計する	GROUP BY + 集計関数	月次受注サマリー自動生成
072	客単価を計算する	AVG, SUM / COUNT	得意先別平均受注金額の算出
073	購入回数を顧客別に集計する	GROUP BY + ORDER BY	VIP顧客・ヘビーユーザーの特定
074	新規顧客と既存顧客を分類する	CASE + DATE比較	新規開拓 vs 既存深耕の把握
075	リピート率を計算する	集計 + 条件計算	受注継続率の KPI 化
076	最終購入日を計算する	MAX(date) + 日付差	休眠顧客の抽出と営業アクション
077	RFM分析用のデータを作成する	CTE + CASE WHEN	顧客セグメント自動分類
078	在庫数と販売数を比較する	LEFT JOIN + 集計	在庫過不足の定量把握
079	在庫切れリスクのある商品を抽出する	CTE + CASE WHEN	補充発注アラートの自動生成
080	異常に売上が増減した商品を抽出する	CTE + LAG ウィンドウ関数	受注異常の自動検知

Python 環境の準備

import subprocess, sys

res = subprocess.run(["sw_vers", "-productVersion"], capture_output=True, text=True)
print(f"macOS : {res.stdout.strip()}")
print(f"Python: {sys.version}")

import sqlite3
from datetime import date, timedelta
import numpy as np
import polars as pl
import matplotlib
import matplotlib.pyplot as plt

matplotlib.rcParams['font.family'] = 'Hiragino Maru Gothic Pro'
%config InlineBackend.figure_format = 'svg'
np.random.seed(42)


def q(conn, sql):
    '''SQL を実行して Polars DataFrame で結果を表示する'''
    print('── SQL ─────────────────────────────────────────')
    for line in sql.strip().split('\n'):
        print(f'  {line}')
    print('───────────────────────────────────────────────')
    cur = conn.execute(sql.strip())
    rows = cur.fetchall()
    cols = [d[0] for d in cur.description]
    data = {col: [row[i] for row in rows] for i, col in enumerate(cols)}
    df = pl.DataFrame(data)
    print(df)
    print(f'↳ {len(rows)} 行取得')
    return df


print(f"sqlite3   : {sqlite3.sqlite_version}")
print(f"polars    : {pl.__version__}")
print(f"numpy     : {np.__version__}")
print(f"matplotlib: {matplotlib.__version__}")
print()
print("ライブラリ読み込み完了")

架空データの作成

conn = sqlite3.connect(':memory:')

# ── customers ─────────────────────────────────────────────
conn.execute('''
CREATE TABLE customers (
    customer_id         TEXT PRIMARY KEY,
    customer_name       TEXT,
    region              TEXT,
    first_contract_date TEXT
)
''')
CUST_DATA = [
    ('C001', '山田機械製作所',       '関東', '2022-03-15'),
    ('C002', '田中製鉄株式会社',     '関西', '2021-07-20'),
    ('C003', '鈴木精密工業',         '東海', '2022-11-05'),
    ('C004', '高橋エンジニアリング', '関東', '2023-04-18'),
    ('C005', '伊藤工業株式会社',     '関西', '2021-09-30'),
    ('C006', '渡辺機械工業',         '九州', '2023-01-10'),
    ('C007', '中村製作所',           '東海', '2022-06-22'),
    ('C008', '小林精機',             '関東', '2024-02-15'),
    ('C009', '加藤重工業',           '関西', '2021-12-01'),
    ('C010', '吉田メカトロニクス',   '関東', '2024-04-08'),
    ('C011', '山本工業',             '東海', '2022-08-14'),
    ('C012', '松本製作所',           '九州', '2023-02-28'),
    ('C013', '佐藤機電工業',         '関東', '2024-07-03'),
    ('C014', '清水精工',             '関西', '2021-05-16'),
    ('C015', '市川工業株式会社',     '東海', '2022-10-19'),
    ('C016', '木村製作所',           '九州', '2023-06-30'),
    ('C017', '橋本機械',             '関東', '2024-01-22'),
    ('C018', '石川工業',             '関西', '2021-11-08'),
    ('C019', '荒川精機',             '東海', '2022-04-25'),
    ('C020', '藤田製造所',           '九州', '2023-09-12'),
]
conn.executemany('INSERT INTO customers VALUES (?,?,?,?)', CUST_DATA)

# ── products ──────────────────────────────────────────────
conn.execute('''
CREATE TABLE products (
    product_id     TEXT PRIMARY KEY,
    product_name   TEXT,
    category       TEXT,
    standard_price INTEGER,
    lead_time_days INTEGER
)
''')
PROD_DATA = [
    ('P001', '精密ベアリング',     '機械部品', 2800,  7),
    ('P002', '油圧シリンダー',     '機械部品', 15000, 10),
    ('P003', 'モーターコイル',     '電装部品', 4500,   5),
    ('P004', '制御基板',           '電装部品', 28000, 14),
    ('P005', '工業用ゴムパッキン', '消耗品',    350,   3),
    ('P006', 'ステンレスフランジ', '機械部品',  6800,  7),
    ('P007', '三相モーター',       '電装部品', 35000, 14),
    ('P008', '耐熱ガスケット',     '消耗品',    850,   3),
    ('P009', '転がり軸受',         '機械部品',  3200,  5),
    ('P010', '電磁バルブ',         '電装部品', 12000, 10),
]
conn.executemany('INSERT INTO products VALUES (?,?,?,?,?)', PROD_DATA)
PROD_DICT = {p[0]: p[3] for p in PROD_DATA}

# ── orders ────────────────────────────────────────────────
conn.execute('''
CREATE TABLE orders (
    order_id   INTEGER PRIMARY KEY,
    order_date TEXT,
    customer_id TEXT,
    product_id  TEXT,
    quantity    INTEGER,
    unit_price  INTEGER
)
''')

# 各製品の基準発注数量
BASE_QTY = {'P001':80,'P002':8,'P003':40,'P004':3,'P005':400,
            'P006':15,'P007':2,'P008':150,'P009':60,'P010':8}

# 新規得意先の開始月（2024年内）
NEW_START = {'C008':2, 'C010':4, 'C013':7, 'C017':1}

CUST_IDS = [f'C{i:03d}' for i in range(1, 21)]
PROD_IDS = [f'P{i:03d}' for i in range(1, 11)]

CUST_W = np.array([3.0,2.5,2.0,1.5,2.0,1.0,1.8,0.8,2.2,1.2,
                   1.5,0.7,0.5,1.8,1.2,0.8,1.0,2.0,1.4,0.9])
PROD_W = np.array([3.0,2.0,2.5,1.0,4.0,2.0,0.8,3.5,2.5,1.5])
MONTH_FAC = {1:0.85,2:0.80,3:1.10,4:0.95,5:0.90,6:1.05,
             7:0.85,8:0.75,9:1.00,10:1.05,11:1.10,12:1.20}

orders_rows = []
oid = 1

# 全得意先の基礎注文（各社 weight に比例した件数）
for ci, cid in enumerate(CUST_IDS):
    sm = NEW_START.get(cid, 1)
    n = max(int(CUST_W[ci] * 8), 3)
    for _ in range(n):
        month = np.random.randint(sm, 13)
        day   = np.random.randint(1, 28)
        od    = date(2024, month, day)
        pi    = np.random.choice(10, p=PROD_W / PROD_W.sum())
        pid   = PROD_IDS[pi]
        qty   = max(int(BASE_QTY[pid] * np.random.uniform(0.5, 2.0)), 1)
        up    = int(PROD_DICT[pid] * np.random.uniform(0.92, 1.08))
        orders_rows.append((oid, str(od), cid, pid, qty, up))
        oid += 1

# 追加ランダム受注（月別季節変動付き）
for _ in range(400):
    ci  = np.random.choice(20, p=CUST_W / CUST_W.sum())
    cid = CUST_IDS[ci]
    sm  = NEW_START.get(cid, 1)
    month = np.random.randint(sm, 13)
    fac   = MONTH_FAC[month]
    if np.random.rand() > fac:
        continue  # 繁忙期は受注確率が高い
    day = np.random.randint(1, 28)
    od  = date(2024, month, day)
    pi  = np.random.choice(10, p=PROD_W / PROD_W.sum())
    pid = PROD_IDS[pi]
    qty = max(int(BASE_QTY[pid] * np.random.uniform(0.5, 2.0)), 1)
    up  = int(PROD_DICT[pid] * np.random.uniform(0.92, 1.08))
    orders_rows.append((oid, str(od), cid, pid, qty, up))
    oid += 1

conn.executemany('INSERT INTO orders VALUES (?,?,?,?,?,?)', orders_rows)

# ── inventory ──────────────────────────────────────────────
conn.execute('''
CREATE TABLE inventory (
    product_id    TEXT PRIMARY KEY,
    stock_qty     INTEGER,
    safety_stock  INTEGER,
    last_updated  TEXT
)
''')
INV_DATA = [
    ('P001', 850,  300, '2024-12-31'),
    ('P002', 120,  150, '2024-12-31'),
    ('P003', 380,  200, '2024-12-31'),
    ('P004',  25,   40, '2024-12-31'),
    ('P005',5000, 1500, '2024-12-31'),
    ('P006', 280,  150, '2024-12-31'),
    ('P007',   8,   15, '2024-12-31'),
    ('P008',3200, 1000, '2024-12-31'),
    ('P009', 450,  300, '2024-12-31'),
    ('P010',  65,   80, '2024-12-31'),
]
conn.executemany('INSERT INTO inventory VALUES (?,?,?,?)', INV_DATA)
conn.commit()

print(f"customers: {len(CUST_DATA)} 件")
print(f"products : {len(PROD_DATA)} 件")
print(f"orders   : {len(orders_rows)} 件")
print(f"inventory: {len(INV_DATA)} 件")

for tbl in ['customers','products','orders','inventory']:
    n = conn.execute(f'SELECT COUNT(*) FROM {tbl}').fetchone()[0]
    print(f'{tbl:12s}: {n:5d} 件')
print()
q(conn, 'SELECT * FROM products')

No.071：売上KPIを集計する

実務での意味

月次の 受注サマリー（件数・金額・数量） は製造業の営業管理における基本 KPI です。
毎月自動で算出できれば、経営会議・予算管理・前月比分析に即座に対応できます。

分析・モデル化の考え方

月別集計には strftime('%Y-%m', order_date) で年月を取り出し、GROUP BY で集計します。

$$\text{月次売上} = \sum_{i} \text{quantity}_i \times \text{unit\_price}_i$$

主要 KPI の定義：

KPI	SQL 表現	意味
受注件数	COUNT(order_id)	月に何回発注があったか
売上金額	SUM(quantity * unit_price)	月の総受注金額
客単価	SUM(金額) / COUNT(DISTINCT customer_id)	1社あたりの月平均金額
平均受注金額	SUM(金額) / COUNT(order_id)	1受注あたりの平均金額

Python で確認する

print("=== No.071 売上KPIを集計する ===\n")

# ① 月次受注 KPI
print("① 月次受注 KPI サマリー")
df71 = q(conn, '''
SELECT
  strftime('%Y-%m', order_date)           AS ym,
  COUNT(order_id)                         AS order_count,
  COUNT(DISTINCT customer_id)             AS unique_customers,
  SUM(quantity * unit_price)              AS total_revenue,
  ROUND(SUM(quantity * unit_price) * 1.0
        / COUNT(order_id), 0)             AS avg_order_value
FROM orders
GROUP BY ym
ORDER BY ym
''')

# ② 可視化：月次売上＋受注件数（二軸グラフ）
YMS     = df71['ym'].to_list()
REV     = [v / 1_000_000 for v in df71['total_revenue'].to_list()]  # 百万円
ORDERS  = df71['order_count'].to_list()

fig, ax1 = plt.subplots(figsize=(10, 5))
ax2 = ax1.twinx()
ax1.bar(YMS, REV,    color='#3498db', alpha=0.7, label='売上金額（百万円）')
ax2.plot(YMS, ORDERS, color='#e74c3c', marker='o', linewidth=2, label='受注件数')
ax1.set_title('月次 受注 KPI（No.071：売上金額 + 受注件数）', fontsize=13)
ax1.set_xlabel('年月')
ax1.set_ylabel('売上金額（百万円）')
ax2.set_ylabel('受注件数')
ax1.grid(axis='y', alpha=0.3)
lines1, labels1 = ax1.get_legend_handles_labels()
lines2, labels2 = ax2.get_legend_handles_labels()
ax1.legend(lines1 + lines2, labels1 + labels2, loc='upper left', fontsize=9)
plt.xticks(rotation=45)
plt.tight_layout()
plt.show()

結果の読み取り

• 12 月は月別売上が高い傾向（年度末需要・納品集中）、8 月は夏季休暇で低め
• avg_order_value（平均受注金額）が月によって大きく変わる場合、大口受注の有無を確認する
• unique_customers（ユニーク得意先数）が少ない月は、特定の大口得意先への依存が高い可能性がある
  → 売上集中リスク の把握に活用できる

No.072：客単価を計算する

実務での意味

客単価（得意先別平均受注金額） は、営業戦略の立案において欠かせない指標です。
「高単価・低頻度」の得意先と「低単価・高頻度」の得意先では、対応すべき営業戦略が異なります。

分析・モデル化の考え方

$$\text{客単価} = \frac{\text{総受注金額}}{\text{受注件数}}$$

年間総受注金額と受注件数の両方を確認することで、得意先の 取引特性 を把握できます。

特性	高単価・低頻度	低単価・高頻度
例	高額設備・受注生産品	消耗品・定期発注品
営業対応	担当者を固定し丁寧にフォロー	自動発注・EDI 連携を推進
リスク	取引が途絶えると売上への影響大	価格競争になりやすい

Python で確認する

print("=== No.072 客単価を計算する ===\n")

# ① 得意先別 年間客単価（上位 10 社）
print("① 得意先別 年間客単価（降順）")
df72 = q(conn, '''
SELECT
  o.customer_id,
  c.customer_name,
  c.region,
  COUNT(o.order_id)                              AS order_count,
  SUM(o.quantity * o.unit_price)                 AS total_revenue,
  ROUND(SUM(o.quantity * o.unit_price) * 1.0
        / COUNT(o.order_id), 0)                  AS avg_order_value
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
GROUP BY o.customer_id
ORDER BY avg_order_value DESC
''')

# ② 全体平均客単価
print("\n② 全体平均客単価")
q(conn, '''
SELECT ROUND(SUM(quantity * unit_price) * 1.0 / COUNT(order_id), 0) AS overall_avg
FROM orders
''')

結果の読み取り

• 客単価が高い得意先は 高額製品（制御基板・三相モーター） を多く発注している傾向がある
• 客単価が低くても受注件数が多い得意先は 消耗品の定期発注先 として安定した売上基盤になる
• 客単価ランキング上位と受注件数ランキング上位が一致しない場合、薄利多売 vs 高付加価値 の取引構造を分析する基点になる

No.073：購入回数を顧客別に集計する

実務での意味

「年間に何回発注しているか」を得意先別に把握することで、
ロイヤルカスタマーの特定、低頻度顧客への働きかけ が可能になります。

分析・モデル化の考え方

受注回数（Frequency）は RFM 分析の「F」成分です。

$$F_i = \text{COUNT}(\text{order\_id}) \text{ WHERE customer\_id} = i$$

発注頻度の分布を把握することで、顧客層の構造（パレートの法則：上位 20% の顧客が売上の 80% を占める）を確認できます。

Python で確認する

print("=== No.073 購入回数を顧客別に集計する ===\n")

# ① 得意先別 受注件数（全件）
print("① 得意先別 受注件数")
df73 = q(conn, '''
SELECT
  o.customer_id,
  c.customer_name,
  c.region,
  COUNT(o.order_id)              AS order_count,
  SUM(o.quantity * o.unit_price) AS total_revenue
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
GROUP BY o.customer_id
ORDER BY order_count DESC
''')

# ② 可視化：受注件数 TOP10（横棒グラフ）
df73_top = df73.head(10)
names  = df73_top['customer_name'].to_list()
counts = df73_top['order_count'].to_list()
revs   = [v / 1_000_000 for v in df73_top['total_revenue'].to_list()]

fig, axes = plt.subplots(1, 2, figsize=(13, 5))

# 受注件数
axes[0].barh(names[::-1], counts[::-1], color='#3498db', edgecolor='white')
axes[0].set_title('得意先別 受注件数 TOP10', fontsize=12)
axes[0].set_xlabel('受注件数')
axes[0].set_ylabel('')
axes[0].grid(axis='x', alpha=0.4)

# 売上金額
axes[1].barh(names[::-1], revs[::-1], color='#2ecc71', edgecolor='white')
axes[1].set_title('得意先別 売上金額 TOP10（百万円）', fontsize=12)
axes[1].set_xlabel('売上金額（百万円）')
axes[1].set_ylabel('')
axes[1].grid(axis='x', alpha=0.4)

plt.tight_layout()
plt.show()

結果の読み取り

• 受注件数 TOP と売上金額 TOP が一致しない得意先は 発注頻度は低いが大口 の可能性がある
• 受注件数が極端に少ない（1〜2件）得意先は、次の受注が来るか不確実な 休眠候補 かもしれない
• パレートの法則の検証：上位数社で全体の売上の 8 割を占めているかを確認し、営業リソース配分 の見直しに活用する

No.074：新規顧客と既存顧客を分類する

実務での意味

「今年新しく取引が始まった得意先」と「以前から継続取引している得意先」を分類することで、
新規開拓の効果（営業投資の成果）と 既存顧客の深耕状況 を同時に把握できます。

分析・モデル化の考え方

customers.first_contract_date（初回契約日）を基準に CASE 式で分類します。

$$\text{顧客タイプ} = \begin{cases} \text{新規顧客} & \text{if } \text{first\_contract\_date} \geq \text{'2024-01-01'} \\ \text{既存顧客} & \text{otherwise} \end{cases}$$

指標	意味
新規顧客数	今年の新規開拓件数
新規顧客の総受注金額	新規開拓の売上貢献度
新規顧客の客単価	初年度の取引規模

Python で確認する

print("=== No.074 新規顧客と既存顧客を分類する ===\n")

# ① 新規 vs 既存 の分類サマリー
print("① 新規 / 既存 得意先サマリー（2024 年を基準）")
df74a = q(conn, '''
SELECT
  CASE
    WHEN c.first_contract_date >= '2024-01-01' THEN '新規顧客（2024年新規契約）'
    ELSE '既存顧客（2024年以前から）'
  END AS customer_type,
  COUNT(DISTINCT c.customer_id)  AS customer_count,
  COUNT(o.order_id)              AS order_count,
  SUM(o.quantity * o.unit_price) AS total_revenue,
  ROUND(SUM(o.quantity * o.unit_price) * 1.0
        / COUNT(o.order_id), 0)  AS avg_order_value
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY customer_type
ORDER BY customer_type DESC
''')

# ② 新規顧客一覧
print("\n② 新規顧客（2024年新規契約）一覧")
q(conn, '''
SELECT
  c.customer_id,
  c.customer_name,
  c.region,
  c.first_contract_date,
  COUNT(o.order_id)              AS order_count,
  SUM(o.quantity * o.unit_price) AS annual_revenue
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE c.first_contract_date >= '2024-01-01'
GROUP BY c.customer_id
ORDER BY c.first_contract_date
''')

結果の読み取り

• 新規顧客（2024 年初回契約）は 4 社：C008 小林精機（2月）、C010 吉田メカトロニクス（4月）、C017 橋本機械（1月）、C013 佐藤機電工業（7月）
• 新規顧客は年度途中から取引が始まるため、初年度の年間売上は既存顧客より少ない場合が多い
• 新規顧客の客単価を既存顧客と比較することで、新規開拓ターゲットの精度検証 に活用できる

No.075：リピート率を計算する

実務での意味

リピート率 = 年内に 2 回以上発注した得意先の割合。
製造業の B2B では、リピート率が高いほど安定した受注基盤があることを意味します。
リピート率の低下傾向が見られる場合は、顧客満足度の低下や競合への流出のサインです。

分析・モデル化の考え方

$$\text{リピート率} = \frac{\text{受注件数} \geq 2 \text{ の得意先数}}{\text{全受注得意先数}} \times 100$$

CASE 式と集計関数を組み合わせて 1 つの SQL で算出できます：

SUM(CASE WHEN order_count >= 2 THEN 1 ELSE 0 END) * 100.0 / COUNT(*)

Python で確認する

print("=== No.075 リピート率を計算する ===\n")

# ① 全体リピート率
print("① 全体リピート率")
df75a = q(conn, '''
WITH order_counts AS (
  SELECT customer_id, COUNT(*) AS order_count
  FROM orders
  GROUP BY customer_id
)
SELECT
  COUNT(*)                                       AS total_customers,
  SUM(CASE WHEN order_count >= 2 THEN 1 ELSE 0 END) AS repeat_customers,
  SUM(CASE WHEN order_count  = 1 THEN 1 ELSE 0 END) AS one_time_customers,
  ROUND(SUM(CASE WHEN order_count >= 2 THEN 1 ELSE 0 END)
        * 100.0 / COUNT(*), 1)                  AS repeat_rate_pct
FROM order_counts
''')

# ② 受注件数別の得意先数分布
print("\n② 受注件数別の得意先数分布")
q(conn, '''
WITH order_counts AS (
  SELECT customer_id, COUNT(*) AS order_count
  FROM orders
  GROUP BY customer_id
)
SELECT
  CASE
    WHEN order_count >= 30 THEN '30件以上'
    WHEN order_count >= 20 THEN '20〜29件'
    WHEN order_count >= 10 THEN '10〜19件'
    WHEN order_count >=  5 THEN '5〜9件'
    WHEN order_count >=  2 THEN '2〜4件'
    ELSE '1件のみ'
  END AS freq_band,
  COUNT(*) AS customer_count
FROM order_counts
GROUP BY freq_band
ORDER BY MIN(order_count) DESC
''')

結果の読み取り

• リピート率が高い（80〜90%以上）場合、既存顧客の関係性は良好といえる
• 「1 件のみ」の得意先が多い場合、トライアル発注から継続発注につなげる フォローが必要
• 年度ごとのリピート率の推移を追うことで、顧客ロイヤルティ指標 として経営判断に活用できる

No.076：最終購入日を計算する

実務での意味

最終発注日からの経過日数（Recency） は RFM 分析の「R」成分です。
長期間発注がない得意先は 休眠顧客 として、営業のフォローアップ対象になります。

分析・モデル化の考え方

SQLite では julianday() 関数で日付を浮動小数点数（ユリウス通日）に変換し、
差を取ることで経過日数を計算できます。

$$\text{経過日数} = \text{julianday}(\text{'2024-12-31'}) - \text{julianday}(\text{MAX(order\_date)})$$

経過日数	分類	対応
0〜30 日	アクティブ	次回発注の提案
31〜90 日	要注意	フォローアップ連絡
91 日以上	休眠候補	関係再構築が必要

Python で確認する

print("=== No.076 最終購入日を計算する ===\n")

# ① 最終発注日と経過日数
print("① 得意先別 最終発注日（経過日数順）")
df76 = q(conn, '''
SELECT
  o.customer_id,
  c.customer_name,
  c.region,
  MAX(o.order_date)                                           AS last_order_date,
  ROUND(julianday('2024-12-31') - julianday(MAX(o.order_date)), 0) AS days_since_last,
  CASE
    WHEN julianday('2024-12-31') - julianday(MAX(o.order_date)) <= 30 THEN 'アクティブ'
    WHEN julianday('2024-12-31') - julianday(MAX(o.order_date)) <= 90 THEN '要注意'
    ELSE '休眠候補'
  END AS recency_status
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
GROUP BY o.customer_id
ORDER BY days_since_last DESC
''')

# ② ステータス別サマリー
print("\n② 最終発注経過日数ステータス別 得意先数")
q(conn, '''
WITH recency AS (
  SELECT
    customer_id,
    ROUND(julianday('2024-12-31') - julianday(MAX(order_date)), 0) AS days_since_last
  FROM orders
  GROUP BY customer_id
)
SELECT
  CASE
    WHEN days_since_last <= 30 THEN 'アクティブ（30日以内）'
    WHEN days_since_last <= 90 THEN '要注意（31〜90日）'
    ELSE '休眠候補（91日以上）'
  END AS recency_status,
  COUNT(*) AS customer_count
FROM recency
GROUP BY recency_status
ORDER BY MIN(days_since_last)
''')

結果の読み取り

• 経過日数が 90 日以上の得意先は休眠候補として営業優先リストに自動追加できる
• データは定期的（例：毎週月曜朝）に再計算することで、リアルタイムの休眠アラート として機能する
• 休眠候補でも年間売上が高い場合は、優先度の高い営業案件 として取り扱う

No.077：RFM分析用のデータを作成する

実務での意味

RFM 分析は、顧客を 3 つの指標でスコアリングする顧客セグメント分析手法です。

指標	英語	定義	製造業での意味
R	Recency（近接性）	最終発注からの経過日数	最近発注しているか
F	Frequency（頻度）	受注件数	何回発注しているか
M	Monetary（金額）	総受注金額	どれだけ買っているか

RFM スコアが高い顧客（RFM = 3-3-3）は 最重要顧客（VIP）、低い顧客（1-1-1）は 離反リスク があります。

分析・モデル化の考え方

各指標をスコア（1〜3）に変換します：

$$R_{score} = \begin{cases} 3 & R \leq 30 \\ 2 & 31 \leq R \leq 90 \\ 1 & R > 90 \end{cases} \quad F_{score} = \begin{cases} 3 & F \geq 25 \\ 2 & 10 \leq F < 25 \\ 1 & F < 10 \end{cases} \quad M_{score} = \begin{cases} 3 & M \geq 2{,}000{,}000 \\ 2 & 800{,}000 \leq M < 2{,}000{,}000 \\ 1 & M < 800{,}000 \end{cases}$$

Python で確認する

print("=== No.077 RFM分析用のデータを作成する ===\n")

# ① RFM データ作成
print("① RFM スコアテーブル")
df77 = q(conn, '''
WITH rfm_raw AS (
  SELECT
    o.customer_id,
    c.customer_name,
    ROUND(julianday('2024-12-31') - julianday(MAX(o.order_date)), 0) AS recency,
    COUNT(o.order_id)              AS frequency,
    SUM(o.quantity * o.unit_price) AS monetary
  FROM orders o
  JOIN customers c ON o.customer_id = c.customer_id
  GROUP BY o.customer_id
)
SELECT
  customer_id,
  customer_name,
  recency,
  frequency,
  monetary,
  CASE WHEN recency  <=  30 THEN 3 WHEN recency  <=  90 THEN 2 ELSE 1 END AS r_score,
  CASE WHEN frequency >= 25 THEN 3 WHEN frequency >= 10  THEN 2 ELSE 1 END AS f_score,
  CASE WHEN monetary >= 2000000 THEN 3 WHEN monetary >= 800000 THEN 2 ELSE 1 END AS m_score
FROM rfm_raw
ORDER BY monetary DESC
''')

# ② RFM セグメント集計
print("\n② RFM セグメント集計")
df77s = q(conn, '''
WITH rfm_raw AS (
  SELECT
    o.customer_id,
    ROUND(julianday('2024-12-31') - julianday(MAX(o.order_date)), 0) AS recency,
    COUNT(o.order_id)              AS frequency,
    SUM(o.quantity * o.unit_price) AS monetary
  FROM orders o
  GROUP BY o.customer_id
),
scored AS (
  SELECT
    customer_id,
    CASE WHEN recency  <=  30 THEN 3 WHEN recency  <=  90 THEN 2 ELSE 1 END AS r,
    CASE WHEN frequency >= 25 THEN 3 WHEN frequency >= 10  THEN 2 ELSE 1 END AS f,
    CASE WHEN monetary >= 2000000 THEN 3 WHEN monetary >= 800000 THEN 2 ELSE 1 END AS m
  FROM rfm_raw
)
SELECT
  r || '-' || f || '-' || m AS rfm_segment,
  CASE
    WHEN r=3 AND f=3 AND m=3 THEN 'VIP顧客'
    WHEN r=1 AND f<=2 AND m<=2 THEN '離反リスク'
    WHEN r>=2 AND f>=2 THEN '優良顧客'
    ELSE 'その他'
  END AS segment_label,
  COUNT(*) AS customer_count
FROM scored
GROUP BY rfm_segment
ORDER BY rfm_segment DESC
''')

# 可視化：F vs M 散布図（色 = R スコア）
R_COLORS = {1: '#e74c3c', 2: '#f39c12', 3: '#2ecc71'}
fig, ax = plt.subplots(figsize=(9, 6))
for r_val in [3, 2, 1]:
    sub = df77.filter(pl.col('r_score') == r_val)
    ax.scatter(
        sub['frequency'].to_list(),
        [v / 1_000_000 for v in sub['monetary'].to_list()],
        color=R_COLORS[r_val],
        label=f'R={r_val}（{"直近アクティブ" if r_val==3 else "要注意" if r_val==2 else "休眠候補"}）',
        s=80, alpha=0.8, edgecolors='white', linewidth=0.5
    )
ax.set_title('RFM 分析：受注頻度 vs 売上金額（色 = Recency スコア）', fontsize=13)
ax.set_xlabel('受注件数（Frequency）')
ax.set_ylabel('年間売上金額（百万円）')
ax.legend(fontsize=9)
ax.grid(alpha=0.3)
plt.tight_layout()
plt.show()

結果の読み取り

• 右上（高 F・高 M）かつ緑色（R=3）の点が VIP 顧客：直近まで多頻度・大口取引
• 左下（低 F・低 M）かつ赤色（R=1）の点が 離反リスク顧客：発注が止まっている
• RFM スコアは定期的に再計算することで、顧客ポートフォリオの変化 を追跡できる
  → セグメントが悪化している顧客に対して、早期に営業アクションを打てる

No.078：在庫数と販売数を比較する

実務での意味

「今の在庫量は何ヶ月分の需要に相当するか」を把握することで、
過剰在庫（キャッシュフローの圧迫） と 在庫不足（機会損失） の両リスクを管理できます。

分析・モデル化の考え方

直近 3 ヶ月（10〜12 月）の出荷実績から月次平均需要を算出し、在庫月数を計算します：

$$\text{在庫月数} = \frac{\text{現在庫数量}}{\text{月次平均需要}}$$

在庫月数	評価	対応
≥ 6 ヶ月	過剰在庫	生産計画見直し・バーゲンセール
2〜5 ヶ月	適正在庫	現状維持
1〜2 ヶ月	要注意	補充発注の検討
< 1 ヶ月	危険水域	緊急発注・代替品の手配

Python で確認する

print("=== No.078 在庫数と販売数を比較する ===\n")

# ① 在庫 vs 直近3ヶ月出荷実績
print("① 製品別 在庫 vs 直近3ヶ月需要（在庫月数順）")
df78 = q(conn, '''
WITH recent_demand AS (
  SELECT
    product_id,
    SUM(quantity)                    AS qty_3m,
    ROUND(SUM(quantity) * 1.0 / 3, 0) AS monthly_avg_qty
  FROM orders
  WHERE order_date >= '2024-10-01'
  GROUP BY product_id
)
SELECT
  p.product_id,
  p.product_name,
  p.category,
  i.stock_qty,
  i.safety_stock,
  COALESCE(d.monthly_avg_qty, 0) AS monthly_avg_qty,
  CASE
    WHEN COALESCE(d.monthly_avg_qty, 0) = 0 THEN 999
    ELSE ROUND(i.stock_qty * 1.0 / d.monthly_avg_qty, 1)
  END AS stock_months
FROM products p
JOIN inventory i ON p.product_id = i.product_id
LEFT JOIN recent_demand d ON p.product_id = d.product_id
ORDER BY stock_months ASC
''')

# ② 可視化：在庫量 vs 安全在庫（棒グラフ）
names   = df78['product_name'].to_list()
stocks  = df78['stock_qty'].to_list()
safetys = df78['safety_stock'].to_list()
months  = df78['stock_months'].to_list()
bar_colors = ['#e74c3c' if s < sf else '#f39c12' if s < sf * 2 else '#2ecc71'
              for s, sf in zip(stocks, safetys)]

x = range(len(names))
fig, ax = plt.subplots(figsize=(11, 5))
ax.bar(x, stocks,  color=bar_colors, label='現在庫', edgecolor='white', alpha=0.9)
ax.plot(x, safetys, 's--', color='#e74c3c', markersize=8, label='安全在庫ライン', linewidth=1.5)
ax.set_title('製品別 現在庫 vs 安全在庫（No.078）', fontsize=13)
ax.set_xlabel('製品')
ax.set_ylabel('在庫数量')
ax.set_xticks(list(x))
ax.set_xticklabels(names, rotation=30, ha='right')
ax.legend(fontsize=10)
ax.grid(axis='y', alpha=0.4)
plt.tight_layout()
plt.show()

結果の読み取り

• グラフの 赤棒（現在庫 < 安全在庫）は在庫切れリスクがある製品（→ No.079 で詳細分析）
• グラフの オレンジ棒（安全在庫〜2倍の範囲）は補充検討が必要な製品
• stock_months が大きい製品（消耗品 P005 パッキン等）は、過剰在庫による保管コスト を削減できる余地がある

No.079：在庫切れリスクのある商品を抽出する

実務での意味

在庫切れは 機会損失・生産停止・得意先への迷惑 につながります。
発注リードタイムを考慮した 残在庫日数（Runout Days） を計算して、
補充発注が必要な製品を自動でアラートすることが重要です。

分析・モデル化の考え方

$$\text{残在庫日数} = \frac{\text{現在庫数}}{\text{日次平均需要}} = \frac{\text{stock\_qty}}{\text{monthly\_avg\_qty} / 30}$$

リードタイムが $L$ 日の製品について：

$$\text{要補充条件} :\quad \text{残在庫日数} < L \cdot 1.5 \quad (\text{安全係数 1.5 を適用})$$

Python で確認する

print("=== No.079 在庫切れリスクのある商品を抽出する ===\n")

# ① 在庫リスク評価
print("① 在庫リスク評価（残在庫日数 + ステータス）")
df79 = q(conn, '''
WITH recent_demand AS (
  SELECT
    product_id,
    ROUND(SUM(quantity) * 1.0 / 3, 0) AS monthly_avg_qty
  FROM orders
  WHERE order_date >= '2024-10-01'
  GROUP BY product_id
),
stock_eval AS (
  SELECT
    i.product_id,
    i.stock_qty,
    i.safety_stock,
    p.product_name,
    p.lead_time_days,
    COALESCE(d.monthly_avg_qty, 0) AS monthly_avg_qty,
    CASE
      WHEN COALESCE(d.monthly_avg_qty, 0) = 0 THEN 9999
      ELSE ROUND(i.stock_qty * 30.0 / d.monthly_avg_qty, 0)
    END AS runout_days,
    CASE
      WHEN i.stock_qty < i.safety_stock THEN '在庫切れリスク'
      WHEN i.stock_qty < i.safety_stock * 2 THEN '要補充'
      ELSE '在庫十分'
    END AS stock_status
  FROM inventory i
  JOIN products p ON i.product_id = p.product_id
  LEFT JOIN recent_demand d ON i.product_id = d.product_id
)
SELECT * FROM stock_eval ORDER BY runout_days ASC
''')

# ② リスク品目のみ表示
print("\n② 在庫切れリスク / 要補充 品目")
risk = df79.filter(pl.col('stock_status').is_in(['在庫切れリスク', '要補充']))
for row in risk.iter_rows(named=True):
    print(f"  {row['product_id']} {row['product_name']:18s} "
          f"在庫:{row['stock_qty']:5d} 安全在庫:{row['safety_stock']:5d} "
          f"残:{row['runout_days']:4.0f}日 LT:{row['lead_time_days']}日 [{row['stock_status']}]")

# 可視化：残在庫日数（ガントバー風）
STATUS_COLORS = {'在庫切れリスク': '#e74c3c', '要補充': '#f39c12', '在庫十分': '#2ecc71'}
names79   = df79['product_name'].to_list()
runouts   = [min(r, 180) for r in df79['runout_days'].to_list()]
statuses  = df79['stock_status'].to_list()
bar_cols  = [STATUS_COLORS[s] for s in statuses]
lead_days = df79['lead_time_days'].to_list()

fig, ax = plt.subplots(figsize=(10, 5))
ax.barh(names79, runouts, color=bar_cols, edgecolor='white')
for i, (ld, nm) in enumerate(zip(lead_days, names79)):
    ax.axvline(ld * 1.5, color='gray', linestyle=':', linewidth=0.8, alpha=0.5)
ax.axvline(30, color='#e74c3c', linestyle='--', linewidth=1.5, label='30日ライン（警戒）')
ax.set_title('製品別 残在庫日数（No.079：在庫切れリスク評価）', fontsize=12)
ax.set_xlabel('残在庫日数')
ax.set_ylabel('製品')
ax.legend(fontsize=9)
ax.grid(axis='x', alpha=0.4)
plt.tight_layout()
plt.show()

結果の読み取り

• P002 油圧シリンダー、P004 制御基板、P007 三相モーター、P010 電磁バルブ は在庫が安全在庫を下回っており、早急な補充発注が必要
• これらの製品は lead_time_days が 10〜14 日と長いため、発注から入荷まで 2 週間必要 → 直ちに補充発注を起票すべき
• この SQL を毎朝定期実行し、stock_status = '在庫切れリスク' の製品を調達担当者にメール通知する仕組みを構築できる

No.080：異常に売上が増減した商品を抽出する

実務での意味

月次売上の 急増・急減は、受注の集中・喪失、特定得意先の大口発注、価格変動など様々な要因で発生します。
これを自動検知することで、問題の早期把握と機会の活用 が可能になります。

分析・モデル化の考え方

前月比変化率を算出し、絶対値が閾値（例：±30%）を超える月・製品を抽出します：

$$\text{前月比変化率} = \frac{\text{当月売上} - \text{前月売上}}{\text{前月売上}} \times 100$$

ウィンドウ関数 LAG() を使うと、各製品の時系列で前月値を取得できます：

LAG(monthly_revenue) OVER (PARTITION BY product_id ORDER BY ym)

PARTITION BY product_id により、製品ごとに独立した前月比を計算できます。

Python で確認する

print("=== No.080 異常に売上が増減した商品を抽出する ===\n")

# ① 月次製品別売上 + 前月比
print("① 前月比 ±30%以上の異常レコード")
df80 = q(conn, '''
WITH monthly AS (
  SELECT
    o.product_id,
    p.product_name,
    strftime('%Y-%m', o.order_date) AS ym,
    SUM(o.quantity * o.unit_price)  AS monthly_revenue
  FROM orders o
  JOIN products p ON o.product_id = p.product_id
  GROUP BY o.product_id, ym
),
with_lag AS (
  SELECT
    *,
    LAG(monthly_revenue) OVER (PARTITION BY product_id ORDER BY ym) AS prev_revenue
  FROM monthly
)
SELECT
  product_id,
  product_name,
  ym,
  monthly_revenue,
  prev_revenue,
  ROUND((monthly_revenue - prev_revenue) * 100.0 / prev_revenue, 1) AS mom_pct
FROM with_lag
WHERE prev_revenue IS NOT NULL
  AND ABS(ROUND((monthly_revenue - prev_revenue) * 100.0 / prev_revenue, 1)) >= 30
ORDER BY ABS(ROUND((monthly_revenue - prev_revenue) * 100.0 / prev_revenue, 1)) DESC
LIMIT 15
''')

# ② 製品別月次推移データ（全12ヶ月）
df80_all = q(conn, '''
WITH monthly AS (
  SELECT
    o.product_id,
    p.product_name,
    strftime('%Y-%m', o.order_date) AS ym,
    SUM(o.quantity * o.unit_price)  AS monthly_revenue
  FROM orders o
  JOIN products p ON o.product_id = p.product_id
  GROUP BY o.product_id, ym
)
SELECT * FROM monthly
ORDER BY product_id, ym
''')

# 可視化：上位3製品の月次推移（折れ線）
TOP_PRODS = (df80['product_id'].unique().to_list())[:3]
COLORS_80 = ['#3498db', '#e74c3c', '#2ecc71']
ALL_YMS   = sorted(df80_all['ym'].unique().to_list())

fig, ax = plt.subplots(figsize=(11, 5))
for i, pid in enumerate(TOP_PRODS):
    sub  = df80_all.filter(pl.col('product_id') == pid).sort('ym')
    pname = sub['product_name'][0]
    revs  = [sub.filter(pl.col('ym') == ym)['monthly_revenue'][0] / 1_000_000
             if ym in sub['ym'].to_list() else 0 for ym in ALL_YMS]
    ax.plot(ALL_YMS, revs, marker='o', color=COLORS_80[i], linewidth=2,
            label=f'{pid} {pname}')
    # 異常点を強調
    anoms = df80.filter(pl.col('product_id') == pid)
    for row in anoms.iter_rows(named=True):
        ym_idx = ALL_YMS.index(row['ym']) if row['ym'] in ALL_YMS else -1
        if ym_idx >= 0:
            ax.scatter(row['ym'], row['monthly_revenue'] / 1_000_000,
                       color=COLORS_80[i], s=150, zorder=5,
                       marker='*', edgecolors='black', linewidth=0.5)

ax.set_title('製品別 月次売上推移（★ = 前月比 ±30%以上の異常点）（No.080）', fontsize=12)
ax.set_xlabel('年月')
ax.set_ylabel('月次売上（百万円）')
ax.legend(fontsize=9)
ax.grid(alpha=0.3)
plt.xticks(rotation=45)
plt.tight_layout()
plt.show()

結果の読み取り

• ★ マーク（異常点）は前月比が ±30% 以上の月を示す
• 急増した月は「大口発注の集中」「キャンペーン効果」「競合からの切り替え」などが原因として考えられる
• 急減した月は「重要得意先の発注停止」「価格交渉中」「在庫過剰による発注抑制」などのサインかもしれない
• 閾値（±30%）は業種・製品ライフサイクルに応じて調整し、社内の異常検知ルール として標準化できる

対象ノックを通して見える実務上の示唆

製造業B2B の「受注管理 SQL パターン集」

課題	SQL パターン	ビジネス価値
KPI 自動集計	GROUP BY + strftime	月次レポート作成時間の削減
顧客ランキング	ORDER BY + TOP-N	営業リソースの優先配分
顧客分類	CASE + DATE比較	新規開拓 vs 既存深耕の可視化
リピート率	条件集計 + 除算	顧客ロイヤルティ KPI 化
休眠顧客検出	MAX(date) + 日付差	離反防止の先手対応
RFM セグメント	CTE + CASE スコアリング	顧客セグメント別の施策設計
在庫管理	LEFT JOIN + 在庫月数計算	過剰在庫 / 欠品リスクの定量把握
異常検知	LAG ウィンドウ関数	売上異変の自動アラート

「SQLで自動化できること」と「人間の判断が必要なこと」

SQL は データの集計・変換・フィルタリング を自動化しますが、
「なぜその変化が起きたか」の 原因分析 は人間の知識・経験が不可欠です。

• SQL → 「何が・いつ・どのくらい変化したか」を提示
• 人間 → 「なぜ変化したか・どう対処するか」を判断

この役割分担を明確にすることで、SQL 活用の効果が最大化されます。

実務導入する場合に必要なこと

1. データ品質の担保

• 受注データに 欠損・重複・入力ミル がないか定期的に検証する（→ No.094〜095 で扱う予定）
• order_date のフォーマットを統一する（YYYY-MM-DD）

2. 定期実行の自動化

毎朝 8:00  → 在庫リスク SQL を実行 → 在庫切れリスク品目をメール通知
毎月 1 日  → RFM 再計算 → セグメント変化を担当者に通知
四半期末  → KPI ダッシュボード SQL を実行 → 経営報告書に自動挿入

3. データソースの接続

本ノックでは SQLite を使用しましたが、実務では以下のシステムと接続します：

システム	DB 例	対応 SQL
販売管理システム	MySQL / PostgreSQL	ほぼ同一（日付関数は方言あり）
ERP（SAP etc）	SQL Server / Oracle	方言対応が必要
DWH	BigQuery / Snowflake	ほぼ標準 SQL で動作

4. セキュリティ・権限管理

• 受注データ・顧客データは個人情報を含む可能性がある
• DWH にはロールベースのアクセス制御（RBAC）を設定し、必要最小限の権限 を付与する

まとめ

本章では、製造業 B2B の受注・在庫データを使って 実務データ分析 SQL を 10 本実践しました。

No.	習得した機能	実務での価値
071	月次 KPI 集計	受注サマリーの自動生成
072	客単価計算	得意先別の取引特性把握
073	顧客別購入回数	VIP 顧客・低頻度顧客の特定
074	新規 / 既存顧客分類	新規開拓効果の可視化
075	リピート率計算	顧客ロイヤルティ KPI
076	最終購入日・経過日数	休眠顧客の自動検出
077	RFM 分析データ	顧客セグメント設計
078	在庫 vs 需要比較	在庫過不足の定量把握
079	在庫切れリスク抽出	補充発注アラートの自動化
080	売上異常検知	急増・急減の早期把握

次章（第9章） では 応用分析 SQL（コホート分析・ファネル分析・A/Bテスト集計など）を学びます。
本章で習得した基本集計パターンを組み合わせた、より高度な分析手法を習得しましょう。

法人向けのご相談

製造業における 受注データ分析基盤の構築、顧客分析・RFM 分析の実装、
在庫リスク管理の SQL 自動化、SQL 研修・内製化支援 に関して、
数理工房では法人様向けのご相談を承っております。

本ノックで扱ったような「受注 KPI・顧客セグメント・在庫アラートを SQL で自動化する仕組み」の
実装支援から教育プログラムの設計まで、お気軽にお問い合わせください。

---

📩 お問い合わせ: surikobo.co.jp/contact まずはお気軽にご相談ください。