100本ノック / SQL / データ分析のためのSQL入門100本ノック

SQLウィンドウ関数で製造ラインの月次トレンドを多角的に分析する

2020-01-14

SQLウィンドウ関数で製造ラインの月次トレンドを多角的に分析する

SQL 100本ノック第7章（No.061〜No.070）：ウィンドウ関数

本記事は「データ分析のための SQL 入門 100本ノック」シリーズの 第7章 です。第6章（No.051〜060）ではサブクエリと CTE を学びました。本章では ウィンドウ関数 を使い、製造ラインの月次生産データからランキング・前月比・累積集計・移動平均などの高度な分析を行います。

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

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

自動車部品メーカー生産管理部の川田さんが対応している課題です。

ライン別月次生産 KPI モニタリング（毎月第2営業日の定例作業）
  1. 各ラインの当月生産数・不良数を前月と比較して増減を確認
  2. 月次不良率の推移トレンドをグラフ化して改善効果を可視化
  3. 工場・ライン別の年間累積生産量を追跡して目標対比を確認
  4. 不良率の移動平均（3ヶ月/6ヶ月）で季節変動・工程改善効果を分析
  5. ワースト不良率のライン・月をランキング化して優先改善対象を特定

現在は Excel のピボットテーブルと関数（OFFSET、MATCH、INDEX）を組み合わせて対応していますが、月が変わるたびに手動更新が必要で、数式が複雑になっています。

SQL のウィンドウ関数を使えば、これらの集計が GROUP BY を使わずに 元の行を保持したまま計算でき、1クエリで完結する分析が可能になります。

現場でよくある状況

場面	現状の課題	ウィンドウ関数で解決できること
前月比・前期比の計算	別テーブルを自己 JOIN して手動計算	`LAG()` で1行前の値を直接参照
累積生産量の追跡	Excel の OFFSET 関数で構築した複雑な数式	`SUM() OVER (ROWS UNBOUNDED PRECEDING)`
不良率の移動平均	月ごとに期間を手動変更して再集計	`AVG() OVER (ROWS BETWEEN N PRECEDING AND CURRENT ROW)`
ラインのランキング	外部ツールで並び替えてから手動で番号付与	`RANK()` / `DENSE_RANK()`
ワースト月の特定	全月のデータをソートして目視確認	`ROW_NUMBER()` で連番後に `WHERE rn <= N`

ウィンドウ関数は「行を集約せずに計算を付加する」という GROUP BY にはない特性を持ちます。これにより、個別行のデータを残しながら集計値を参照できます。

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

ウィンドウ関数の初学者がつまずきやすい4つのポイントを整理します。

1. OVER() 句の構造を理解する

関数名() OVER (
    PARTITION BY  分割キー     -- 各「窓」の範囲（省略可）
    ORDER BY      並び順       -- 窓内での順序（省略可）
    ROWS BETWEEN  フレーム指定  -- 計算対象の行範囲（省略可）
)

PARTITION BY は GROUP BY と似ていますが、行を集約しません。

2. RANK と DENSE_RANK の違い

同順位（タイ）が発生したとき、次の番号の付け方が異なります。

スコア	RANK()	DENSE_RANK()
2.5%	1	1
2.0%	2	2
2.0%	2	2
1.8%	4 ← 3を飛ばす	3 ← 連続

3. LAG / LEAD のデフォルト値と NULL 処理

LAG(col, 1) は直前行が存在しないとき NULL を返します。 CASE WHEN prev IS NOT NULL THEN ... END で NULL を適切に処理します。

4. フレーム指定（ROWS vs RANGE）

移動平均の ROWS BETWEEN 2 PRECEDING AND CURRENT ROW は「直前2行 + 現在行 = 3行」を対象にします。月次データの移動平均は ROWS 指定が直感的です。

今回扱うノックの全体像

No.	タイトル	製造業での活用場面
061	ウィンドウ関数の考え方を理解する	OVER()句の構造と GROUP BY との違い
062	ROW_NUMBERで連番を付ける	月別生産数の降順で各ラインに連番を付与
063	RANKでランキングを作る	全ライン×全月の不良率ランキング
064	DENSE_RANKで同順位を扱う	同率ランクが発生した場合の正確な順位付け
065	不良損失額の上位ラインを抽出する	工場別ワースト1ラインをピンポイント特定
066	ライン別生産量ランキングを作る	生産量と品質の複合ランキング
067	LAGで前月値を取得する	前月の生産数・不良率を1クエリで参照
068	前月比・前月差を計算する	月次生産変動率と不良率改善量を自動算出
069	累積生産量を計算する	年間目標対比に必要な月次累積値のトラッキング
070	移動平均を計算する	3ヶ月/6ヶ月移動平均で不良率の真のトレンドを把握

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}")

macOS : 26.3
Python: 3.13.1 (main, Dec  3 2024, 17:59:52) [Clang 16.0.0 (clang-1600.0.26.4)]

import sqlite3
import numpy as np
import polars as pl
import matplotlib
import matplotlib.pyplot as plt
from matplotlib.patches import Patch

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

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

sqlite3   : 3.47.2
polars    : 1.42.1
numpy     : 2.5.1
matplotlib: 3.11.0

ライブラリ読み込み完了

架空データの作成

想定シナリオ: 自動車部品メーカー生産管理部 / 月次 KPI モニタリング 分析期間: 2024年1月〜12月（12ヶ月） テーブル構成: 1テーブル（非正規化）

テーブル名	件数	説明
`production`	60件	月次生産記録（5ライン × 12ヶ月）

ウィンドウ関数デモ用の設計ポイント:

12ヶ月の時系列データで LAG / 累積集計 / 移動平均が自然に機能する
夏季（7〜8月）に生産数がやや低下するシーズナリティを付与
年間を通じて不良率が緩やかに改善するトレンドを付与（品質改善活動の効果）
LINE-C1（名古屋工場 / SUS-001）は他ラインより不良率が高めに設定

ライン	工場	部品	単価	月産基準数	基準不良率
LINE-A1	東京 F01	ピストンリング	¥1,200	9,200	1.9%
LINE-A2	東京 F01	ブレーキパッド	¥950	6,800	2.1%
LINE-B1	大阪 F02	ピストンリング	¥1,200	8,600	2.0%
LINE-C1	名古屋 F03	ショックアブソーバ	¥2,800	3,400	2.6%
LINE-D1	福岡 F04	クランクシャフト	¥8,500	7,800	2.2%

# ─────────────────────────────────────────────────────────────────────────
# SQL ヘルパー関数
# ─────────────────────────────────────────────────────────────────────────
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

# ─────────────────────────────────────────────────────────────────────────
# インメモリ DB 作成
# ─────────────────────────────────────────────────────────────────────────
conn = sqlite3.connect(':memory:')

conn.execute('''
CREATE TABLE production (
    month          TEXT    NOT NULL,
    line_code      TEXT    NOT NULL,
    factory_id     TEXT    NOT NULL,
    factory_name   TEXT    NOT NULL,
    part_code      TEXT    NOT NULL,
    part_name      TEXT    NOT NULL,
    unit_price     INTEGER NOT NULL,
    production_qty INTEGER NOT NULL,
    defect_qty     INTEGER NOT NULL,
    PRIMARY KEY (month, line_code)
)''')

# ── 5ライン × 12ヶ月 の月次生産データを生成 ─────────────────────────────────
np.random.seed(42)

LINE_CONFIG = {
    #              factory_id  factory_name   part_code   part_name           unit_price  base_prod  base_dr
    'LINE-A1': ('F01', '東京工場',   'ENG-001', 'ピストンリング',     1200, 9200, 0.019),
    'LINE-A2': ('F01', '東京工場',   'BRK-001', 'ブレーキパッド',      950, 6800, 0.021),
    'LINE-B1': ('F02', '大阪工場',   'ENG-001', 'ピストンリング',     1200, 8600, 0.020),
    'LINE-C1': ('F03', '名古屋工場', 'SUS-001', 'ショックアブソーバ', 2800, 3400, 0.026),
    'LINE-D1': ('F04', '福岡工場',   'ENG-002', 'クランクシャフト',   8500, 7800, 0.022),
}
MONTHS = [f'2024-{m:02d}' for m in range(1, 13)]

records = []
for month_idx, month in enumerate(MONTHS):
    for line_code, (fid, fname, pcode, pname, uprice, base_prod, base_dr) in LINE_CONFIG.items():
        # 夏季（7〜8月）に生産数がやや低下するシーズナリティ
        seasonal = 1.0 - 0.07 * np.exp(-((month_idx - 6.5)**2) / 3.0) + 0.04 * (month_idx >= 10)
        # 年間を通じて不良率が 10% 改善するトレンド
        dr_trend = 1.0 - 0.10 * month_idx / 11
        prod   = max(200, int(base_prod * seasonal + np.random.normal(0, base_prod * 0.025)))
        dr     = base_dr * dr_trend * (1 + np.random.normal(0, 0.11))
        dr     = max(dr, 0.005)
        defect = max(1, round(prod * dr))
        records.append((month, line_code, fid, fname, pcode, pname, uprice, prod, defect))

conn.executemany('INSERT INTO production VALUES (?,?,?,?,?,?,?,?,?)', records)
conn.commit()

n = conn.execute('SELECT COUNT(*) FROM production').fetchone()[0]
print(f'データベース作成完了: production テーブル {n} 件')
print()
q(conn, '''
SELECT line_code, factory_name, part_name, unit_price,
       COUNT(*)                                                 AS months,
       SUM(production_qty)                                      AS annual_prod,
       SUM(defect_qty)                                          AS annual_defect,
       ROUND(SUM(defect_qty) * 100.0 / SUM(production_qty), 2) AS annual_dr_pct
FROM   production
GROUP  BY line_code, factory_name, part_name, unit_price
ORDER  BY annual_prod DESC
''')

データベース作成完了: production テーブル 60 件

── SQL ─────────────────────────────────────────
  SELECT line_code, factory_name, part_name, unit_price,
         COUNT(*)                                                 AS months,
         SUM(production_qty)                                      AS annual_prod,
         SUM(defect_qty)                                          AS annual_defect,
         ROUND(SUM(defect_qty) * 100.0 / SUM(production_qty), 2) AS annual_dr_pct
  FROM   production
  GROUP  BY line_code, factory_name, part_name, unit_price
  ORDER  BY annual_prod DESC
───────────────────────────────────────────────
shape: (5, 8)
┌───────────┬────────────┬────────────┬────────────┬────────┬────────────┬────────────┬────────────┐
│ line_code ┆ factory_na ┆ part_name  ┆ unit_price ┆ months ┆ annual_pro ┆ annual_def ┆ annual_dr_ │
│ ---       ┆ me         ┆ ---        ┆ ---        ┆ ---    ┆ d          ┆ ect        ┆ pct        │
│ str       ┆ ---        ┆ str        ┆ i64        ┆ i64    ┆ ---        ┆ ---        ┆ ---        │
│           ┆ str        ┆            ┆            ┆        ┆ i64        ┆ i64        ┆ f64        │
╞═══════════╪════════════╪════════════╪════════════╪════════╪════════════╪════════════╪════════════╡
│ LINE-A1   ┆ 東京工場   ┆ ピストンリ ┆ 1200       ┆ 12     ┆ 108782     ┆ 2016       ┆ 1.85       │
│           ┆            ┆ ング       ┆            ┆        ┆            ┆            ┆            │
│ LINE-B1   ┆ 大阪工場   ┆ ピストンリ ┆ 1200       ┆ 12     ┆ 100853     ┆ 1903       ┆ 1.89       │
│           ┆            ┆ ング       ┆            ┆        ┆            ┆            ┆            │
│ LINE-D1   ┆ 福岡工場   ┆ クランクシ ┆ 8500       ┆ 12     ┆ 92289      ┆ 1869       ┆ 2.03       │
│           ┆            ┆ ャフト     ┆            ┆        ┆            ┆            ┆            │
│ LINE-A2   ┆ 東京工場   ┆ ブレーキパ ┆ 950        ┆ 12     ┆ 80592      ┆ 1584       ┆ 1.97       │
│           ┆            ┆ ッド       ┆            ┆        ┆            ┆            ┆            │
│ LINE-C1   ┆ 名古屋工場 ┆ ショックア ┆ 2800       ┆ 12     ┆ 40454      ┆ 1004       ┆ 2.48       │
│           ┆            ┆ ブソーバ   ┆            ┆        ┆            ┆            ┆            │
└───────────┴────────────┴────────────┴────────────┴────────┴────────────┴────────────┴────────────┘
↳ 5 行取得

shape: (5, 8)

line_code	factory_name	part_name	unit_price	months	annual_prod	annual_defect	annual_dr_pct
str	str	str	i64	i64	i64	i64	f64
”LINE-A1"	"東京工場"	"ピストンリング”	1200	12	108782	2016	1.85
”LINE-B1"	"大阪工場"	"ピストンリング”	1200	12	100853	1903	1.89
”LINE-D1"	"福岡工場"	"クランクシャフト”	8500	12	92289	1869	2.03
”LINE-A2"	"東京工場"	"ブレーキパッド”	950	12	80592	1584	1.97
”LINE-C1"	"名古屋工場"	"ショックアブソーバ”	2800	12	40454	1004	2.48

# ── データ概要グラフ（月次 生産数 / 不良率 のライン別トレンド）────────────────
rows = conn.execute('''
    SELECT month, line_code,
           production_qty,
           ROUND(defect_qty * 100.0 / production_qty, 3) AS dr_pct
    FROM   production
    ORDER  BY line_code, month
''').fetchall()

LINE_CODES = ['LINE-A1', 'LINE-A2', 'LINE-B1', 'LINE-C1', 'LINE-D1']
MONTHS_LBL = [f'{m+1}月' for m in range(12)]
COLORS     = ['#4878CF', '#6ACC65', '#D65F5F', '#B47CC7', '#C4AD66']
COLOR_MAP  = dict(zip(LINE_CODES, COLORS))

# line → month → value の辞書
prod_by_line = {lc: [] for lc in LINE_CODES}
dr_by_line   = {lc: [] for lc in LINE_CODES}
for month, line_code, prod, dr in rows:
    prod_by_line[line_code].append(prod)
    dr_by_line[line_code].append(dr)

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

for ax, data_map, ylabel, title in [
    (axes[0], prod_by_line, '月次生産数（個）', '月次 生産数トレンド（2024年）'),
    (axes[1], dr_by_line,   '不良率（%）',     '月次 不良率トレンド（2024年）'),
]:
    for lc in LINE_CODES:
        ax.plot(range(12), data_map[lc], marker='o', markersize=4,
                linewidth=1.6, color=COLOR_MAP[lc], label=lc)
    ax.set_title(title, fontsize=12, pad=10)
    ax.set_xlabel('月', fontsize=10)
    ax.set_ylabel(ylabel, fontsize=10)
    ax.set_xticks(range(12))
    ax.set_xticklabels(MONTHS_LBL, fontsize=8)
    ax.legend(fontsize=8, loc='upper right')
    ax.grid(alpha=0.3)

plt.tight_layout()
plt.show()
print('データ概要グラフ表示完了（SVG 1/2）')

svg

データ概要グラフ表示完了（SVG 1/2）

No.061：ウィンドウ関数の考え方を理解する

実務での意味

ウィンドウ関数 は、行を集約せずに集計値を各行に付加できる SQL 機能です。 GROUP BY は「行を折りたたむ」のに対し、ウィンドウ関数は「行を保持したまま計算を追加する」点が異なります。

製造業での活用例：

各月の生産数を残しながら「ライン年間合計」を同行に付与 → 月別シェアを計算
不良率を時系列で並べながら「累積平均」を同時に計算
各レコードに「自分の所属するラインの最大・最小不良率」を付与して外れ値検出

分析・モデル化の考え方

ウィンドウ関数の構文：

\underbrace{\text{関数}(\text{列})}_\text{集計関数}\;\; \underbrace{\text{OVER}\,(\text{PARTITION BY 列}\;\;\text{ORDER BY 列})}_\text{ウィンドウの定義}

PARTITION BY は「どのグループ内で計算するか」（省略すると全行）、 ORDER BY は「ウィンドウ内での行の順序」を指定します。

比較項目	GROUP BY	ウィンドウ関数
行数	集約後に減少	元のまま保持
出力	グループごと1行	全行に計算値が付加
使い方	集計レポート	行 + 集計値の同時参照

Python で確認する

# No.061: GROUP BY vs ウィンドウ関数の違いを比較

print('=== GROUP BY: 5行に集約（ライン別年間合計）===')
q(conn, '''
SELECT line_code, factory_name,
       SUM(production_qty) AS annual_prod
FROM   production
GROUP  BY line_code, factory_name
ORDER  BY annual_prod DESC
''')

print()
print('=== ウィンドウ関数: 60行を保持しながら年間合計と月別シェアを付加 ===')
q(conn, '''
SELECT month,
       line_code,
       production_qty,
       SUM(production_qty) OVER (PARTITION BY line_code)  AS line_annual_prod,
       ROUND(
           production_qty * 100.0 /
           SUM(production_qty) OVER (PARTITION BY line_code),
           1
       )                                                   AS month_share_pct
FROM   production
WHERE  line_code = 'LINE-A1'
ORDER  BY month
''')

=== GROUP BY: 5行に集約（ライン別年間合計）===
── SQL ─────────────────────────────────────────
  SELECT line_code, factory_name,
         SUM(production_qty) AS annual_prod
  FROM   production
  GROUP  BY line_code, factory_name
  ORDER  BY annual_prod DESC
───────────────────────────────────────────────
shape: (5, 3)
┌───────────┬──────────────┬─────────────┐
│ line_code ┆ factory_name ┆ annual_prod │
│ ---       ┆ ---          ┆ ---         │
│ str       ┆ str          ┆ i64         │
╞═══════════╪══════════════╪═════════════╡
│ LINE-A1   ┆ 東京工場     ┆ 108782      │
│ LINE-B1   ┆ 大阪工場     ┆ 100853      │
│ LINE-D1   ┆ 福岡工場     ┆ 92289       │
│ LINE-A2   ┆ 東京工場     ┆ 80592       │
│ LINE-C1   ┆ 名古屋工場   ┆ 40454       │
└───────────┴──────────────┴─────────────┘
↳ 5 行取得

=== ウィンドウ関数: 60行を保持しながら年間合計と月別シェアを付加 ===
── SQL ─────────────────────────────────────────
  SELECT month,
         line_code,
         production_qty,
         SUM(production_qty) OVER (PARTITION BY line_code)  AS line_annual_prod,
         ROUND(
             production_qty * 100.0 /
             SUM(production_qty) OVER (PARTITION BY line_code),
             1
         )                                                   AS month_share_pct
  FROM   production
  WHERE  line_code = 'LINE-A1'
  ORDER  BY month
───────────────────────────────────────────────
shape: (12, 5)
┌─────────┬───────────┬────────────────┬──────────────────┬─────────────────┐
│ month   ┆ line_code ┆ production_qty ┆ line_annual_prod ┆ month_share_pct │
│ ---     ┆ ---       ┆ ---            ┆ ---              ┆ ---             │
│ str     ┆ str       ┆ i64            ┆ i64              ┆ f64             │
╞═════════╪═══════════╪════════════════╪══════════════════╪═════════════════╡
│ 2024-01 ┆ LINE-A1   ┆ 9314           ┆ 108782           ┆ 8.6             │
│ 2024-02 ┆ LINE-A1   ┆ 9093           ┆ 108782           ┆ 8.4             │
│ 2024-03 ┆ LINE-A1   ┆ 9536           ┆ 108782           ┆ 8.8             │
│ 2024-04 ┆ LINE-A1   ┆ 9050           ┆ 108782           ┆ 8.3             │
│ 2024-05 ┆ LINE-A1   ┆ 9289           ┆ 108782           ┆ 8.5             │
│ …       ┆ …         ┆ …              ┆ …                ┆ …               │
│ 2024-08 ┆ LINE-A1   ┆ 8690           ┆ 108782           ┆ 8.0             │
│ 2024-09 ┆ LINE-A1   ┆ 8845           ┆ 108782           ┆ 8.1             │
│ 2024-10 ┆ LINE-A1   ┆ 9142           ┆ 108782           ┆ 8.4             │
│ 2024-11 ┆ LINE-A1   ┆ 9231           ┆ 108782           ┆ 8.5             │
│ 2024-12 ┆ LINE-A1   ┆ 9125           ┆ 108782           ┆ 8.4             │
└─────────┴───────────┴────────────────┴──────────────────┴─────────────────┘
↳ 12 行取得

shape: (12, 5)

month	line_code	production_qty	line_annual_prod	month_share_pct
str	str	i64	i64	f64
”2024-01"	"LINE-A1”	9314	108782	8.6
”2024-02"	"LINE-A1”	9093	108782	8.4
”2024-03"	"LINE-A1”	9536	108782	8.8
”2024-04"	"LINE-A1”	9050	108782	8.3
”2024-05"	"LINE-A1”	9289	108782	8.5
…	…	…	…	…
“2024-08"	"LINE-A1”	8690	108782	8.0
”2024-09"	"LINE-A1”	8845	108782	8.1
”2024-10"	"LINE-A1”	9142	108782	8.4
”2024-11"	"LINE-A1”	9231	108782	8.5
”2024-12"	"LINE-A1”	9125	108782	8.4

結果の読み取り

GROUP BY は5ライン×12ヶ月=60行のデータを5行に集約します。月別の内訳は失われます
ウィンドウ関数は12行（LINE-A1 の12ヶ月分）を保持したまま、 line_annual_prod（年間合計）と month_share_pct（月別シェア）を各行に付加します
month_share_pct を見ると、夏季（7〜8月）に生産シェアがやや低下していることが各月の個別データを見ながら確認できます。GROUP BY では取得できない洞察です

No.062：ROW_NUMBERで連番を付ける

実務での意味

ROW_NUMBER() は各行に固有の連番を付けます。同じ値（タイ）が存在しても、必ず異なる番号が付きます（順序は ORDER BY 次第）。

製造業での活用例：

「各ラインで生産数が少なかった月 TOP3」を特定して増産計画の見直しに活用
検査記録に時系列連番を付けて「N 件目のイベント」を参照
データのページング（WHERE rn BETWEEN 11 AND 20）で大量データを分割取得

分析・モデル化の考え方

ROW_NUMBER() は外部クエリ（サブクエリや CTE）でフィルタリングする用途が多いです。

SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY line_code ORDER BY production_qty ASC) AS rn
    FROM production
) WHERE rn <= 3   -- 各ラインで生産数が少なかった月 TOP3

\text{ROW\_NUMBER} = 1, 2, 3, \ldots \quad (\text{同じ値でも必ず異なる番号})

Python で確認する

# No.062: ROW_NUMBER — 各ラインで生産数が少なかった月を特定

print('=== 各ラインで生産数が少なかった月 TOP3（ROW_NUMBER）===')
q(conn, '''
SELECT month, line_code, factory_name, production_qty,
       defect_qty,
       ROUND(defect_qty * 100.0 / production_qty, 2) AS dr_pct,
       ROW_NUMBER() OVER (
           PARTITION BY line_code
           ORDER BY production_qty ASC
       ) AS rn
FROM   production
ORDER  BY line_code, rn
LIMIT  15
''')

print()
print('=== サブクエリで rn <= 2 に絞り込む（各ライン 生産数ワースト2ヶ月）===')
q(conn, '''
SELECT month, line_code, factory_name, production_qty, dr_pct
FROM (
    SELECT month, line_code, factory_name, production_qty,
           ROUND(defect_qty * 100.0 / production_qty, 2) AS dr_pct,
           ROW_NUMBER() OVER (
               PARTITION BY line_code
               ORDER BY production_qty ASC
           ) AS rn
    FROM   production
)
WHERE  rn <= 2
ORDER  BY line_code, production_qty
''')

=== 各ラインで生産数が少なかった月 TOP3（ROW_NUMBER）===
── SQL ─────────────────────────────────────────
  SELECT month, line_code, factory_name, production_qty,
         defect_qty,
         ROUND(defect_qty * 100.0 / production_qty, 2) AS dr_pct,
         ROW_NUMBER() OVER (
             PARTITION BY line_code
             ORDER BY production_qty ASC
         ) AS rn
  FROM   production
  ORDER  BY line_code, rn
  LIMIT  15
───────────────────────────────────────────────
shape: (15, 7)
┌─────────┬───────────┬──────────────┬────────────────┬────────────┬────────┬─────┐
│ month   ┆ line_code ┆ factory_name ┆ production_qty ┆ defect_qty ┆ dr_pct ┆ rn  │
│ ---     ┆ ---       ┆ ---          ┆ ---            ┆ ---        ┆ ---    ┆ --- │
│ str     ┆ str       ┆ str          ┆ i64            ┆ i64        ┆ f64    ┆ i64 │
╞═════════╪═══════════╪══════════════╪════════════════╪════════════╪════════╪═════╡
│ 2024-07 ┆ LINE-A1   ┆ 東京工場     ┆ 8497           ┆ 150        ┆ 1.77   ┆ 1   │
│ 2024-08 ┆ LINE-A1   ┆ 東京工場     ┆ 8690           ┆ 181        ┆ 2.08   ┆ 2   │
│ 2024-09 ┆ LINE-A1   ┆ 東京工場     ┆ 8845           ┆ 162        ┆ 1.83   ┆ 3   │
│ 2024-06 ┆ LINE-A1   ┆ 東京工場     ┆ 8970           ┆ 156        ┆ 1.74   ┆ 4   │
│ 2024-04 ┆ LINE-A1   ┆ 東京工場     ┆ 9050           ┆ 201        ┆ 2.22   ┆ 5   │
│ …       ┆ …         ┆ …            ┆ …              ┆ …          ┆ …      ┆ …   │
│ 2024-01 ┆ LINE-A1   ┆ 東京工場     ┆ 9314           ┆ 174        ┆ 1.87   ┆ 11  │
│ 2024-03 ┆ LINE-A1   ┆ 東京工場     ┆ 9536           ┆ 173        ┆ 1.81   ┆ 12  │
│ 2024-07 ┆ LINE-A2   ┆ 東京工場     ┆ 6173           ┆ 106        ┆ 1.72   ┆ 1   │
│ 2024-08 ┆ LINE-A2   ┆ 東京工場     ┆ 6355           ┆ 146        ┆ 2.3    ┆ 2   │
│ 2024-06 ┆ LINE-A2   ┆ 東京工場     ┆ 6460           ┆ 138        ┆ 2.14   ┆ 3   │
└─────────┴───────────┴──────────────┴────────────────┴────────────┴────────┴─────┘
↳ 15 行取得

=== サブクエリで rn <= 2 に絞り込む（各ライン 生産数ワースト2ヶ月）===
── SQL ─────────────────────────────────────────
  SELECT month, line_code, factory_name, production_qty, dr_pct
  FROM (
      SELECT month, line_code, factory_name, production_qty,
             ROUND(defect_qty * 100.0 / production_qty, 2) AS dr_pct,
             ROW_NUMBER() OVER (
                 PARTITION BY line_code
                 ORDER BY production_qty ASC
             ) AS rn
      FROM   production
  )
  WHERE  rn <= 2
  ORDER  BY line_code, production_qty
───────────────────────────────────────────────
shape: (10, 5)
┌─────────┬───────────┬──────────────┬────────────────┬────────┐
│ month   ┆ line_code ┆ factory_name ┆ production_qty ┆ dr_pct │
│ ---     ┆ ---       ┆ ---          ┆ ---            ┆ ---    │
│ str     ┆ str       ┆ str          ┆ i64            ┆ f64    │
╞═════════╪═══════════╪══════════════╪════════════════╪════════╡
│ 2024-07 ┆ LINE-A1   ┆ 東京工場     ┆ 8497           ┆ 1.77   │
│ 2024-08 ┆ LINE-A1   ┆ 東京工場     ┆ 8690           ┆ 2.08   │
│ 2024-07 ┆ LINE-A2   ┆ 東京工場     ┆ 6173           ┆ 1.72   │
│ 2024-08 ┆ LINE-A2   ┆ 東京工場     ┆ 6355           ┆ 2.3    │
│ 2024-08 ┆ LINE-B1   ┆ 大阪工場     ┆ 7482           ┆ 2.04   │
│ 2024-09 ┆ LINE-B1   ┆ 大阪工場     ┆ 8141           ┆ 1.76   │
│ 2024-07 ┆ LINE-C1   ┆ 名古屋工場   ┆ 3174           ┆ 2.74   │
│ 2024-08 ┆ LINE-C1   ┆ 名古屋工場   ┆ 3188           ┆ 2.35   │
│ 2024-08 ┆ LINE-D1   ┆ 福岡工場     ┆ 7315           ┆ 1.61   │
│ 2024-07 ┆ LINE-D1   ┆ 福岡工場     ┆ 7368           ┆ 1.93   │
└─────────┴───────────┴──────────────┴────────────────┴────────┘
↳ 10 行取得

shape: (10, 5)

month	line_code	factory_name	production_qty	dr_pct
str	str	str	i64	f64
”2024-07"	"LINE-A1"	"東京工場”	8497	1.77
”2024-08"	"LINE-A1"	"東京工場”	8690	2.08
”2024-07"	"LINE-A2"	"東京工場”	6173	1.72
”2024-08"	"LINE-A2"	"東京工場”	6355	2.3
”2024-08"	"LINE-B1"	"大阪工場”	7482	2.04
”2024-09"	"LINE-B1"	"大阪工場”	8141	1.76
”2024-07"	"LINE-C1"	"名古屋工場”	3174	2.74
”2024-08"	"LINE-C1"	"名古屋工場”	3188	2.35
”2024-08"	"LINE-D1"	"福岡工場”	7315	1.61
”2024-07"	"LINE-D1"	"福岡工場”	7368	1.93

結果の読み取り

PARTITION BY line_code により、各ラインで独立した連番が付与されます。ライン内で生産数が少なかった月から順に 1, 2, 3, … と番号が振られます
WHERE rn <= 2 でフィルタリングすることで「ライン別生産ワースト2ヶ月」が一覧化できます。これらの月の不良率（dr_pct）も合わせて確認します
「生産数が少ない月は不良率が高い」傾向があれば、稼働低下時の工程管理見直しが必要です

No.063：RANKでランキングを作る

実務での意味

RANK() は同じ値（タイ）に同じ順位を付け、次の順位を飛ばします。スポーツの成績表の「同点2位が複数いる場合、次は4位になる」のと同じです。

製造業での活用例：

全ライン × 全月の不良率ランキングを作り、改善優先度を数値化
月次の生産量ランキングで、各ラインの順位変動を時系列で追う
工場別の品質 KPI ランキングを月次レポートに組み込む

分析・モデル化の考え方

\text{RANK}:\quad \underbrace{2.5\%}_{\text{1位}},\ \underbrace{2.0\%,\; 2.0\%}_{\text{2位, 2位 (同率)}},\ \underbrace{1.8\%}_{\text{4位} \leftarrow \text{3位を飛ばす}}

タイが多い場合、後続の順位が大きく飛ぶことがあります。順位の連続性が重要な場合は DENSE_RANK（No.064）を使います。

Python で確認する

# No.063: RANK — 全ライン × 全月 の不良率ランキング

print('=== 全ライン × 全月 不良率ランキング（RANK 上位10件）===')
q(conn, '''
SELECT month,
       line_code,
       factory_name,
       production_qty,
       defect_qty,
       ROUND(defect_qty * 100.0 / production_qty, 2) AS dr_pct,
       RANK() OVER (
           ORDER BY defect_qty * 1.0 / production_qty DESC
       ) AS dr_rank
FROM   production
ORDER  BY dr_rank
LIMIT  10
''')

print()
print('=== LINE-A1 に絞ってライン内の月別不良率ランキング ===')
q(conn, '''
SELECT month, line_code,
       ROUND(defect_qty * 100.0 / production_qty, 2) AS dr_pct,
       RANK() OVER (
           PARTITION BY line_code
           ORDER BY defect_qty * 1.0 / production_qty DESC
       ) AS monthly_dr_rank
FROM   production
WHERE  line_code = 'LINE-A1'
ORDER  BY monthly_dr_rank
''')

=== 全ライン × 全月 不良率ランキング（RANK 上位10件）===
── SQL ─────────────────────────────────────────
  SELECT month,
         line_code,
         factory_name,
         production_qty,
         defect_qty,
         ROUND(defect_qty * 100.0 / production_qty, 2) AS dr_pct,
         RANK() OVER (
             ORDER BY defect_qty * 1.0 / production_qty DESC
         ) AS dr_rank
  FROM   production
  ORDER  BY dr_rank
  LIMIT  10
───────────────────────────────────────────────
shape: (10, 7)
┌─────────┬───────────┬──────────────┬────────────────┬────────────┬────────┬─────────┐
│ month   ┆ line_code ┆ factory_name ┆ production_qty ┆ defect_qty ┆ dr_pct ┆ dr_rank │
│ ---     ┆ ---       ┆ ---          ┆ ---            ┆ ---        ┆ ---    ┆ ---     │
│ str     ┆ str       ┆ str          ┆ i64            ┆ i64        ┆ f64    ┆ i64     │
╞═════════╪═══════════╪══════════════╪════════════════╪════════════╪════════╪═════════╡
│ 2024-01 ┆ LINE-C1   ┆ 名古屋工場   ┆ 3534           ┆ 100        ┆ 2.83   ┆ 1       │
│ 2024-05 ┆ LINE-C1   ┆ 名古屋工場   ┆ 3331           ┆ 93         ┆ 2.79   ┆ 2       │
│ 2024-07 ┆ LINE-C1   ┆ 名古屋工場   ┆ 3174           ┆ 87         ┆ 2.74   ┆ 3       │
│ 2024-03 ┆ LINE-C1   ┆ 名古屋工場   ┆ 3301           ┆ 88         ┆ 2.67   ┆ 4       │
│ 2024-02 ┆ LINE-C1   ┆ 名古屋工場   ┆ 3313           ┆ 88         ┆ 2.66   ┆ 5       │
│ 2024-09 ┆ LINE-C1   ┆ 名古屋工場   ┆ 3365           ┆ 84         ┆ 2.5    ┆ 6       │
│ 2024-01 ┆ LINE-A2   ┆ 東京工場     ┆ 6910           ┆ 169        ┆ 2.45   ┆ 7       │
│ 2024-10 ┆ LINE-C1   ┆ 名古屋工場   ┆ 3395           ┆ 83         ┆ 2.44   ┆ 8       │
│ 2024-11 ┆ LINE-C1   ┆ 名古屋工場   ┆ 3692           ┆ 89         ┆ 2.41   ┆ 9       │
│ 2024-12 ┆ LINE-A2   ┆ 東京工場     ┆ 7081           ┆ 170        ┆ 2.4    ┆ 10      │
└─────────┴───────────┴──────────────┴────────────────┴────────────┴────────┴─────────┘
↳ 10 行取得

=== LINE-A1 に絞ってライン内の月別不良率ランキング ===
── SQL ─────────────────────────────────────────
  SELECT month, line_code,
         ROUND(defect_qty * 100.0 / production_qty, 2) AS dr_pct,
         RANK() OVER (
             PARTITION BY line_code
             ORDER BY defect_qty * 1.0 / production_qty DESC
         ) AS monthly_dr_rank
  FROM   production
  WHERE  line_code = 'LINE-A1'
  ORDER  BY monthly_dr_rank
───────────────────────────────────────────────
shape: (12, 4)
┌─────────┬───────────┬────────┬─────────────────┐
│ month   ┆ line_code ┆ dr_pct ┆ monthly_dr_rank │
│ ---     ┆ ---       ┆ ---    ┆ ---             │
│ str     ┆ str       ┆ f64    ┆ i64             │
╞═════════╪═══════════╪════════╪═════════════════╡
│ 2024-04 ┆ LINE-A1   ┆ 2.22   ┆ 1               │
│ 2024-08 ┆ LINE-A1   ┆ 2.08   ┆ 2               │
│ 2024-10 ┆ LINE-A1   ┆ 1.93   ┆ 3               │
│ 2024-01 ┆ LINE-A1   ┆ 1.87   ┆ 4               │
│ 2024-05 ┆ LINE-A1   ┆ 1.86   ┆ 5               │
│ …       ┆ …         ┆ …      ┆ …               │
│ 2024-02 ┆ LINE-A1   ┆ 1.78   ┆ 8               │
│ 2024-07 ┆ LINE-A1   ┆ 1.77   ┆ 9               │
│ 2024-06 ┆ LINE-A1   ┆ 1.74   ┆ 10              │
│ 2024-12 ┆ LINE-A1   ┆ 1.71   ┆ 11              │
│ 2024-11 ┆ LINE-A1   ┆ 1.65   ┆ 12              │
└─────────┴───────────┴────────┴─────────────────┘
↳ 12 行取得

shape: (12, 4)

month	line_code	dr_pct	monthly_dr_rank
str	str	f64	i64
”2024-04"	"LINE-A1”	2.22	1
”2024-08"	"LINE-A1”	2.08	2
”2024-10"	"LINE-A1”	1.93	3
”2024-01"	"LINE-A1”	1.87	4
”2024-05"	"LINE-A1”	1.86	5
…	…	…	…
“2024-02"	"LINE-A1”	1.78	8
”2024-07"	"LINE-A1”	1.77	9
”2024-06"	"LINE-A1”	1.74	10
”2024-12"	"LINE-A1”	1.71	11
”2024-11"	"LINE-A1”	1.65	12

結果の読み取り

不良率上位（dr_rank = 1）の記録が LINE-C1（名古屋工場・ショックアブソーバ）に集中している場合、このラインへの優先的な品質改善投資が正当化されます
ライン内のランキング（PARTITION BY line_code）で自ラインのワースト月を特定することで、「何月に何が起きたか」の原因調査の起点になります
同率順位が発生した場合、RANK では次の番号がスキップされます。この挙動が問題になる場合は No.064 の DENSE_RANK を使います

No.064：DENSE_RANKで同順位を扱う

実務での意味

DENSE_RANK() は同率に同じ順位を付けますが、次の順位を 飛ばさない （RANK との違い）ため、「順位 N 以内」のフィルタリングが直感的です。

製造業での活用例：

「不良率 TOP5 のライン×月を抽出」する際に、4位が存在しないランキングを避けたい
改善プログラムの対象を「下位 N グループ」で選定するとき、グループ数が一定になる
同一 KPI 値のラインを同一「改善ステージ」として扱う場合

分析・モデル化の考え方

\text{RANK vs DENSE\_RANK}

スコア	RANK()	DENSE_RANK()
2.5%（最高不良率）	1	1
2.0%（同率）	2	2
2.0%（同率）	2	2
1.8%	4 ← 3を飛ばす	3 ← 連続

DENSE_RANK では「3位が存在しない」状況が発生しません。

Python で確認する

# No.064: DENSE_RANK vs RANK の違いを確認

print('=== 説明用例: タイがある場合の RANK vs DENSE_RANK ===')
q(conn, '''
WITH example(line, dr) AS (
    VALUES ('LINE-C1（2.6%）', 2.6),
           ('LINE-D1（2.2%）', 2.2),
           ('LINE-A2（2.1%）', 2.1),
           ('LINE-B1（2.0%）', 2.0),
           ('LINE-A1（2.0%）', 2.0),
           ('LINE-XX（1.9%）', 1.9)
)
SELECT line, dr,
       RANK()       OVER (ORDER BY dr DESC) AS rank_result,
       DENSE_RANK() OVER (ORDER BY dr DESC) AS dense_rank_result
FROM   example
''')

print()
print('=== 実データ: 全月 不良率を ROUND(1桁) で丸めた場合のランキング比較 ===')
q(conn, '''
SELECT month, line_code,
       ROUND(defect_qty * 100.0 / production_qty, 1) AS dr_pct_1dec,
       RANK()       OVER (ORDER BY ROUND(defect_qty * 100.0 / production_qty, 1) DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY ROUND(defect_qty * 100.0 / production_qty, 1) DESC) AS dense_rnk
FROM   production
ORDER  BY dr_pct_1dec DESC
LIMIT  15
''')

=== 説明用例: タイがある場合の RANK vs DENSE_RANK ===
── SQL ─────────────────────────────────────────
  WITH example(line, dr) AS (
      VALUES ('LINE-C1（2.6%）', 2.6),
             ('LINE-D1（2.2%）', 2.2),
             ('LINE-A2（2.1%）', 2.1),
             ('LINE-B1（2.0%）', 2.0),
             ('LINE-A1（2.0%）', 2.0),
             ('LINE-XX（1.9%）', 1.9)
  )
  SELECT line, dr,
         RANK()       OVER (ORDER BY dr DESC) AS rank_result,
         DENSE_RANK() OVER (ORDER BY dr DESC) AS dense_rank_result
  FROM   example
───────────────────────────────────────────────
shape: (6, 4)
┌─────────────────┬─────┬─────────────┬───────────────────┐
│ line            ┆ dr  ┆ rank_result ┆ dense_rank_result │
│ ---             ┆ --- ┆ ---         ┆ ---               │
│ str             ┆ f64 ┆ i64         ┆ i64               │
╞═════════════════╪═════╪═════════════╪═══════════════════╡
│ LINE-C1（2.6%） ┆ 2.6 ┆ 1           ┆ 1                 │
│ LINE-D1（2.2%） ┆ 2.2 ┆ 2           ┆ 2                 │
│ LINE-A2（2.1%） ┆ 2.1 ┆ 3           ┆ 3                 │
│ LINE-B1（2.0%） ┆ 2.0 ┆ 4           ┆ 4                 │
│ LINE-A1（2.0%） ┆ 2.0 ┆ 4           ┆ 4                 │
│ LINE-XX（1.9%） ┆ 1.9 ┆ 6           ┆ 5                 │
└─────────────────┴─────┴─────────────┴───────────────────┘
↳ 6 行取得

=== 実データ: 全月 不良率を ROUND(1桁) で丸めた場合のランキング比較 ===
── SQL ─────────────────────────────────────────
  SELECT month, line_code,
         ROUND(defect_qty * 100.0 / production_qty, 1) AS dr_pct_1dec,
         RANK()       OVER (ORDER BY ROUND(defect_qty * 100.0 / production_qty, 1) DESC) AS rnk,
         DENSE_RANK() OVER (ORDER BY ROUND(defect_qty * 100.0 / production_qty, 1) DESC) AS dense_rnk
  FROM   production
  ORDER  BY dr_pct_1dec DESC
  LIMIT  15
───────────────────────────────────────────────
shape: (15, 5)
┌─────────┬───────────┬─────────────┬─────┬───────────┐
│ month   ┆ line_code ┆ dr_pct_1dec ┆ rnk ┆ dense_rnk │
│ ---     ┆ ---       ┆ ---         ┆ --- ┆ ---       │
│ str     ┆ str       ┆ f64         ┆ i64 ┆ i64       │
╞═════════╪═══════════╪═════════════╪═════╪═══════════╡
│ 2024-01 ┆ LINE-C1   ┆ 2.8         ┆ 1   ┆ 1         │
│ 2024-05 ┆ LINE-C1   ┆ 2.8         ┆ 1   ┆ 1         │
│ 2024-02 ┆ LINE-C1   ┆ 2.7         ┆ 3   ┆ 2         │
│ 2024-03 ┆ LINE-C1   ┆ 2.7         ┆ 3   ┆ 2         │
│ 2024-07 ┆ LINE-C1   ┆ 2.7         ┆ 3   ┆ 2         │
│ …       ┆ …         ┆ …           ┆ …   ┆ …         │
│ 2024-11 ┆ LINE-C1   ┆ 2.4         ┆ 7   ┆ 4         │
│ 2024-12 ┆ LINE-A2   ┆ 2.4         ┆ 7   ┆ 4         │
│ 2024-01 ┆ LINE-D1   ┆ 2.3         ┆ 13  ┆ 5         │
│ 2024-06 ┆ LINE-D1   ┆ 2.3         ┆ 13  ┆ 5         │
│ 2024-08 ┆ LINE-A2   ┆ 2.3         ┆ 13  ┆ 5         │
└─────────┴───────────┴─────────────┴─────┴───────────┘
↳ 15 行取得

shape: (15, 5)

month	line_code	dr_pct_1dec	rnk	dense_rnk
str	str	f64	i64	i64
”2024-01"	"LINE-C1”	2.8	1	1
”2024-05"	"LINE-C1”	2.8	1	1
”2024-02"	"LINE-C1”	2.7	3	2
”2024-03"	"LINE-C1”	2.7	3	2
”2024-07"	"LINE-C1”	2.7	3	2
…	…	…	…	…
“2024-11"	"LINE-C1”	2.4	7	4
”2024-12"	"LINE-A2”	2.4	7	4
”2024-01"	"LINE-D1”	2.3	13	5
”2024-06"	"LINE-D1”	2.3	13	5
”2024-08"	"LINE-A2”	2.3	13	5

結果の読み取り

説明用例では LINE-B1 と LINE-A1 が同率 2.0% のとき、 RANK() は次に 4位（3をスキップ）を付けます。 DENSE_RANK() は次に 3位（連続）を付けます
実データで ROUND(..., 1) を使うと同率が発生しやすくなり、両関数の挙動の違いが実際の値で確認できます
「TOP3 のラインに改善投資を集中する」方針の場合、 DENSE_RANK <= 3 で抽出すると RANK より多くのラインが含まれることがあります。ビジネス要件に合わせた選択が重要です

No.065：不良損失額の上位ラインを抽出する

実務での意味

ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) で カテゴリ（工場）ごとの上位 N 行を抽出するパターンは、 SQL ウィンドウ関数の最も実用的な用途の一つです。

製造業での活用例：

工場ごとのワースト不良損失額ラインを1クエリで特定
カテゴリ（部品種別）ごとの品質上位・下位ラインを抽出
月ごとに「生産量増減幅が最大のライン」を追跡

分析・モデル化の考え方

不良損失額は単価 × 不良数で計算します。

\text{不良損失額} = \sum_{t=1}^{12} \text{defect\_qty}_t \times \text{unit\_price}

単価の高い部品（クランクシャフト ¥8,500 / ショックアブソーバ ¥2,800）は不良数が少なくても損失額が大きくなります。 損失額ベースのランキングが経営意思決定に直結します。

Python で確認する

# No.065: ROW_NUMBER で工場別 不良損失額ワースト1ラインを抽出

print('=== 年間 不良損失額・生産額（ライン別集計）===')
q(conn, '''
WITH annual AS (
    SELECT line_code, factory_id, factory_name, part_name, unit_price,
           SUM(production_qty)              AS annual_prod,
           SUM(defect_qty)                  AS annual_defect,
           SUM(production_qty * unit_price) AS production_value,
           SUM(defect_qty    * unit_price)  AS defect_loss
    FROM   production
    GROUP  BY line_code, factory_id, factory_name, part_name, unit_price
),
ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY factory_id
               ORDER BY defect_loss DESC
           ) AS rn
    FROM   annual
)
SELECT factory_id, factory_name, line_code, part_name, unit_price,
       annual_prod, annual_defect, production_value, defect_loss, rn
FROM   ranked
ORDER  BY defect_loss DESC
''')

print()
print('=== 工場別 ワースト1ライン（rn = 1）===')
q(conn, '''
WITH annual AS (
    SELECT line_code, factory_id, factory_name, part_name, unit_price,
           SUM(defect_qty * unit_price) AS defect_loss
    FROM   production
    GROUP  BY line_code, factory_id, factory_name, part_name, unit_price
),
ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY factory_id ORDER BY defect_loss DESC) AS rn
    FROM   annual
)
SELECT factory_id, factory_name, line_code, part_name, defect_loss
FROM   ranked
WHERE  rn = 1
ORDER  BY defect_loss DESC
''')

=== 年間 不良損失額・生産額（ライン別集計）===
── SQL ─────────────────────────────────────────
  WITH annual AS (
      SELECT line_code, factory_id, factory_name, part_name, unit_price,
             SUM(production_qty)              AS annual_prod,
             SUM(defect_qty)                  AS annual_defect,
             SUM(production_qty * unit_price) AS production_value,
             SUM(defect_qty    * unit_price)  AS defect_loss
      FROM   production
      GROUP  BY line_code, factory_id, factory_name, part_name, unit_price
  ),
  ranked AS (
      SELECT *,
             ROW_NUMBER() OVER (
                 PARTITION BY factory_id
                 ORDER BY defect_loss DESC
             ) AS rn
      FROM   annual
  )
  SELECT factory_id, factory_name, line_code, part_name, unit_price,
         annual_prod, annual_defect, production_value, defect_loss, rn
  FROM   ranked
  ORDER  BY defect_loss DESC
───────────────────────────────────────────────
shape: (5, 10)
┌────────────┬────────────┬───────────┬────────────┬───┬────────────┬────────────┬───────────┬─────┐
│ factory_id ┆ factory_na ┆ line_code ┆ part_name  ┆ … ┆ annual_def ┆ production ┆ defect_lo ┆ rn  │
│ ---        ┆ me         ┆ ---       ┆ ---        ┆   ┆ ect        ┆ _value     ┆ ss        ┆ --- │
│ str        ┆ ---        ┆ str       ┆ str        ┆   ┆ ---        ┆ ---        ┆ ---       ┆ i64 │
│            ┆ str        ┆           ┆            ┆   ┆ i64        ┆ i64        ┆ i64       ┆     │
╞════════════╪════════════╪═══════════╪════════════╪═══╪════════════╪════════════╪═══════════╪═════╡
│ F04        ┆ 福岡工場   ┆ LINE-D1   ┆ クランクシ ┆ … ┆ 1869       ┆ 784456500  ┆ 15886500  ┆ 1   │
│            ┆            ┆           ┆ ャフト     ┆   ┆            ┆            ┆           ┆     │
│ F03        ┆ 名古屋工場 ┆ LINE-C1   ┆ ショックア ┆ … ┆ 1004       ┆ 113271200  ┆ 2811200   ┆ 1   │
│            ┆            ┆           ┆ ブソーバ   ┆   ┆            ┆            ┆           ┆     │
│ F01        ┆ 東京工場   ┆ LINE-A1   ┆ ピストンリ ┆ … ┆ 2016       ┆ 130538400  ┆ 2419200   ┆ 1   │
│            ┆            ┆           ┆ ング       ┆   ┆            ┆            ┆           ┆     │
│ F02        ┆ 大阪工場   ┆ LINE-B1   ┆ ピストンリ ┆ … ┆ 1903       ┆ 121023600  ┆ 2283600   ┆ 1   │
│            ┆            ┆           ┆ ング       ┆   ┆            ┆            ┆           ┆     │
│ F01        ┆ 東京工場   ┆ LINE-A2   ┆ ブレーキパ ┆ … ┆ 1584       ┆ 76562400   ┆ 1504800   ┆ 2   │
│            ┆            ┆           ┆ ッド       ┆   ┆            ┆            ┆           ┆     │
└────────────┴────────────┴───────────┴────────────┴───┴────────────┴────────────┴───────────┴─────┘
↳ 5 行取得

=== 工場別 ワースト1ライン（rn = 1）===
── SQL ─────────────────────────────────────────
  WITH annual AS (
      SELECT line_code, factory_id, factory_name, part_name, unit_price,
             SUM(defect_qty * unit_price) AS defect_loss
      FROM   production
      GROUP  BY line_code, factory_id, factory_name, part_name, unit_price
  ),
  ranked AS (
      SELECT *,
             ROW_NUMBER() OVER (PARTITION BY factory_id ORDER BY defect_loss DESC) AS rn
      FROM   annual
  )
  SELECT factory_id, factory_name, line_code, part_name, defect_loss
  FROM   ranked
  WHERE  rn = 1
  ORDER  BY defect_loss DESC
───────────────────────────────────────────────
shape: (4, 5)
┌────────────┬──────────────┬───────────┬────────────────────┬─────────────┐
│ factory_id ┆ factory_name ┆ line_code ┆ part_name          ┆ defect_loss │
│ ---        ┆ ---          ┆ ---       ┆ ---                ┆ ---         │
│ str        ┆ str          ┆ str       ┆ str                ┆ i64         │
╞════════════╪══════════════╪═══════════╪════════════════════╪═════════════╡
│ F04        ┆ 福岡工場     ┆ LINE-D1   ┆ クランクシャフト   ┆ 15886500    │
│ F03        ┆ 名古屋工場   ┆ LINE-C1   ┆ ショックアブソーバ ┆ 2811200     │
│ F01        ┆ 東京工場     ┆ LINE-A1   ┆ ピストンリング     ┆ 2419200     │
│ F02        ┆ 大阪工場     ┆ LINE-B1   ┆ ピストンリング     ┆ 2283600     │
└────────────┴──────────────┴───────────┴────────────────────┴─────────────┘
↳ 4 行取得

shape: (4, 5)

factory_id	factory_name	line_code	part_name	defect_loss
str	str	str	str	i64
”F04"	"福岡工場"	"LINE-D1"	"クランクシャフト”	15886500
”F03"	"名古屋工場"	"LINE-C1"	"ショックアブソーバ”	2811200
”F01"	"東京工場"	"LINE-A1"	"ピストンリング”	2419200
”F02"	"大阪工場"	"LINE-B1"	"ピストンリング”	2283600

結果の読み取り

LINE-D1（福岡工場・クランクシャフト ¥8,500）は不良率は中程度でも、単価が最も高いため 不良損失額が圧倒的に大きく なります。「不良数ランキング」ではなく「損失額ランキング」を使うことで経営インパクトに直結した優先順位付けができます
F01（東京工場）は2ライン（LINE-A1, LINE-A2）を持つため、 PARTITION BY factory_id による工場内ランキングが有効に機能します
ROW_NUMBER() ... WHERE rn = 1 パターンは、「カテゴリ別の代表行を1行だけ取得する」最も汎用的な SQL テクニックです

No.066：ライン別生産量ランキングを作る

実務での意味

複数の RANK() または DENSE_RANK() を同一クエリに含めることで、 複数 KPI の複合ランキングを一度に算出できます。

製造業での活用例：

生産量ランキング（高いほど良い）と不良率ランキング（低いほど良い）を同時表示
「生産量は高いが品質が悪い」ラインと「生産量は低いが品質は良い」ラインを特定
複合スコア（例: 生産量順位 + 品質順位）でバランスの良いラインを評価

分析・モデル化の考え方

2つの KPI を組み合わせたランキング分析は、意思決定のトレードオフを可視化します。

\text{複合スコア} = w_1 \cdot \text{rank}(\text{生産量}) + w_2 \cdot \text{rank}(\text{品質})

重み $w_1, w_2$ は経営方針（量優先か品質優先か）によって調整します。本ノックでは $w_1 = w_2 = 1$ のシンプルな合計ランクを使います。

Python で確認する

# No.066: 年間 生産量ランキング × 不良率ランキング の複合評価

print('=== ライン別 生産量ランキング × 品質ランキング（複合評価）===')
q(conn, '''
WITH totals AS (
    SELECT line_code, factory_name, part_name,
           SUM(production_qty)                                      AS annual_prod,
           SUM(defect_qty)                                          AS annual_defect,
           ROUND(SUM(defect_qty) * 100.0 / SUM(production_qty), 2) AS annual_dr,
           SUM(defect_qty * unit_price)                             AS defect_loss
    FROM   production
    GROUP  BY line_code, factory_name, part_name
)
SELECT line_code, factory_name, part_name,
       annual_prod, annual_defect, annual_dr, defect_loss,
       RANK() OVER (ORDER BY annual_prod DESC) AS prod_rank,
       RANK() OVER (ORDER BY annual_dr   ASC)  AS quality_rank,
       RANK() OVER (ORDER BY annual_prod DESC) +
       RANK() OVER (ORDER BY annual_dr   ASC)  AS composite_rank
FROM   totals
ORDER  BY composite_rank ASC
''')

=== ライン別 生産量ランキング × 品質ランキング（複合評価）===
── SQL ─────────────────────────────────────────
  WITH totals AS (
      SELECT line_code, factory_name, part_name,
             SUM(production_qty)                                      AS annual_prod,
             SUM(defect_qty)                                          AS annual_defect,
             ROUND(SUM(defect_qty) * 100.0 / SUM(production_qty), 2) AS annual_dr,
             SUM(defect_qty * unit_price)                             AS defect_loss
      FROM   production
      GROUP  BY line_code, factory_name, part_name
  )
  SELECT line_code, factory_name, part_name,
         annual_prod, annual_defect, annual_dr, defect_loss,
         RANK() OVER (ORDER BY annual_prod DESC) AS prod_rank,
         RANK() OVER (ORDER BY annual_dr   ASC)  AS quality_rank,
         RANK() OVER (ORDER BY annual_prod DESC) +
         RANK() OVER (ORDER BY annual_dr   ASC)  AS composite_rank
  FROM   totals
  ORDER  BY composite_rank ASC
───────────────────────────────────────────────
shape: (5, 10)
┌───────────┬───────────┬───────────┬───────────┬───┬───────────┬───────────┬───────────┬──────────┐
│ line_code ┆ factory_n ┆ part_name ┆ annual_pr ┆ … ┆ defect_lo ┆ prod_rank ┆ quality_r ┆ composit │
│ ---       ┆ ame       ┆ ---       ┆ od        ┆   ┆ ss        ┆ ---       ┆ ank       ┆ e_rank   │
│ str       ┆ ---       ┆ str       ┆ ---       ┆   ┆ ---       ┆ i64       ┆ ---       ┆ ---      │
│           ┆ str       ┆           ┆ i64       ┆   ┆ i64       ┆           ┆ i64       ┆ i64      │
╞═══════════╪═══════════╪═══════════╪═══════════╪═══╪═══════════╪═══════════╪═══════════╪══════════╡
│ LINE-A1   ┆ 東京工場  ┆ ピストン  ┆ 108782    ┆ … ┆ 2419200   ┆ 1         ┆ 1         ┆ 2        │
│           ┆           ┆ リング    ┆           ┆   ┆           ┆           ┆           ┆          │
│ LINE-B1   ┆ 大阪工場  ┆ ピストン  ┆ 100853    ┆ … ┆ 2283600   ┆ 2         ┆ 2         ┆ 4        │
│           ┆           ┆ リング    ┆           ┆   ┆           ┆           ┆           ┆          │
│ LINE-D1   ┆ 福岡工場  ┆ クランク  ┆ 92289     ┆ … ┆ 15886500  ┆ 3         ┆ 4         ┆ 7        │
│           ┆           ┆ シャフト  ┆           ┆   ┆           ┆           ┆           ┆          │
│ LINE-A2   ┆ 東京工場  ┆ ブレーキ  ┆ 80592     ┆ … ┆ 1504800   ┆ 4         ┆ 3         ┆ 7        │
│           ┆           ┆ パッド    ┆           ┆   ┆           ┆           ┆           ┆          │
│ LINE-C1   ┆ 名古屋工  ┆ ショック  ┆ 40454     ┆ … ┆ 2811200   ┆ 5         ┆ 5         ┆ 10       │
│           ┆ 場        ┆ アブソー  ┆           ┆   ┆           ┆           ┆           ┆          │
│           ┆           ┆ バ        ┆           ┆   ┆           ┆           ┆           ┆          │
└───────────┴───────────┴───────────┴───────────┴───┴───────────┴───────────┴───────────┴──────────┘
↳ 5 行取得

shape: (5, 10)

line_code	factory_name	part_name	annual_prod	annual_defect	annual_dr	defect_loss	prod_rank	quality_rank	composite_rank
str	str	str	i64	i64	f64	i64	i64	i64	i64
”LINE-A1"	"東京工場"	"ピストンリング”	108782	2016	1.85	2419200	1	1	2
”LINE-B1"	"大阪工場"	"ピストンリング”	100853	1903	1.89	2283600	2	2	4
”LINE-D1"	"福岡工場"	"クランクシャフト”	92289	1869	2.03	15886500	3	4	7
”LINE-A2"	"東京工場"	"ブレーキパッド”	80592	1584	1.97	1504800	4	3	7
”LINE-C1"	"名古屋工場"	"ショックアブソーバ”	40454	1004	2.48	2811200	5	5	10

結果の読み取り

prod_rank（生産量ランキング）と quality_rank（不良率ランキング）が同時に表示されることで「量 vs 品質」のトレードオフが一目で確認できます
composite_rank（複合スコア: 合計ランク）が低いラインが生産量・品質の両面でバランスが取れています
「生産量ランキング高、品質ランキング低」のラインは 量産優先で品質が犠牲になっている可能性があります。設備・工程の見直しや品質エンジニアのアサイン優先度を検討します

No.067：LAGで前回値を取得する

実務での意味

LAG(列, N) は現在行から N 行前の値を取得します。 PARTITION BY で分割することで「ライン内の前月値」を正確に参照できます。

製造業での活用例：

今月の生産数と先月の生産数を1行に並べて増減を把握
今月の不良率と先月の不良率を比較して改善トレンドを確認
2ヶ月前の値（LAG(col, 2)）と比較して短期トレンドを検出

分析・モデル化の考え方

LAG は「行の差分計算」の前処理として使います。

\Delta x_t = x_t - \underbrace{x_{t-1}}_{\text{LAG}(x,\, 1)}

時系列解析では、この差分が**ステーショナリティ（定常性）**を確保するために使われることもあります（ARIMA モデルの I(1) 差分）。

月次生産管理では「前月比」の計算のベースとして必須の操作です。

Python で確認する

# No.067: LAG — 前月の生産数・不良率を同一行に取得

print('=== LINE-A1: 月別 生産数・不良率 と 前月値 ===')
q(conn, '''
WITH base AS (
    SELECT month, line_code, factory_name, production_qty,
           ROUND(defect_qty * 100.0 / production_qty, 2) AS dr_pct
    FROM   production
    WHERE  line_code = 'LINE-A1'
)
SELECT month, line_code, production_qty, dr_pct,
       LAG(production_qty, 1) OVER (ORDER BY month) AS prev_prod,
       LAG(dr_pct,          1) OVER (ORDER BY month) AS prev_dr
FROM   base
ORDER  BY month
''')

print()
print('=== 全ライン: 前月値付き（PARTITION BY line_code でライン別に参照）===')
q(conn, '''
WITH base AS (
    SELECT month, line_code, production_qty,
           ROUND(defect_qty * 100.0 / production_qty, 2) AS dr_pct
    FROM   production
)
SELECT month, line_code, production_qty, dr_pct,
       LAG(production_qty, 1) OVER (PARTITION BY line_code ORDER BY month) AS prev_prod,
       LAG(dr_pct,          1) OVER (PARTITION BY line_code ORDER BY month) AS prev_dr
FROM   base
ORDER  BY line_code, month
LIMIT  15
''')

=== LINE-A1: 月別 生産数・不良率 と 前月値 ===
── SQL ─────────────────────────────────────────
  WITH base AS (
      SELECT month, line_code, factory_name, production_qty,
             ROUND(defect_qty * 100.0 / production_qty, 2) AS dr_pct
      FROM   production
      WHERE  line_code = 'LINE-A1'
  )
  SELECT month, line_code, production_qty, dr_pct,
         LAG(production_qty, 1) OVER (ORDER BY month) AS prev_prod,
         LAG(dr_pct,          1) OVER (ORDER BY month) AS prev_dr
  FROM   base
  ORDER  BY month
───────────────────────────────────────────────
shape: (12, 6)
┌─────────┬───────────┬────────────────┬────────┬───────────┬─────────┐
│ month   ┆ line_code ┆ production_qty ┆ dr_pct ┆ prev_prod ┆ prev_dr │
│ ---     ┆ ---       ┆ ---            ┆ ---    ┆ ---       ┆ ---     │
│ str     ┆ str       ┆ i64            ┆ f64    ┆ i64       ┆ f64     │
╞═════════╪═══════════╪════════════════╪════════╪═══════════╪═════════╡
│ 2024-01 ┆ LINE-A1   ┆ 9314           ┆ 1.87   ┆ null      ┆ null    │
│ 2024-02 ┆ LINE-A1   ┆ 9093           ┆ 1.78   ┆ 9314      ┆ 1.87    │
│ 2024-03 ┆ LINE-A1   ┆ 9536           ┆ 1.81   ┆ 9093      ┆ 1.78    │
│ 2024-04 ┆ LINE-A1   ┆ 9050           ┆ 2.22   ┆ 9536      ┆ 1.81    │
│ 2024-05 ┆ LINE-A1   ┆ 9289           ┆ 1.86   ┆ 9050      ┆ 2.22    │
│ …       ┆ …         ┆ …              ┆ …      ┆ …         ┆ …       │
│ 2024-08 ┆ LINE-A1   ┆ 8690           ┆ 2.08   ┆ 8497      ┆ 1.77    │
│ 2024-09 ┆ LINE-A1   ┆ 8845           ┆ 1.83   ┆ 8690      ┆ 2.08    │
│ 2024-10 ┆ LINE-A1   ┆ 9142           ┆ 1.93   ┆ 8845      ┆ 1.83    │
│ 2024-11 ┆ LINE-A1   ┆ 9231           ┆ 1.65   ┆ 9142      ┆ 1.93    │
│ 2024-12 ┆ LINE-A1   ┆ 9125           ┆ 1.71   ┆ 9231      ┆ 1.65    │
└─────────┴───────────┴────────────────┴────────┴───────────┴─────────┘
↳ 12 行取得

=== 全ライン: 前月値付き（PARTITION BY line_code でライン別に参照）===
── SQL ─────────────────────────────────────────
  WITH base AS (
      SELECT month, line_code, production_qty,
             ROUND(defect_qty * 100.0 / production_qty, 2) AS dr_pct
      FROM   production
  )
  SELECT month, line_code, production_qty, dr_pct,
         LAG(production_qty, 1) OVER (PARTITION BY line_code ORDER BY month) AS prev_prod,
         LAG(dr_pct,          1) OVER (PARTITION BY line_code ORDER BY month) AS prev_dr
  FROM   base
  ORDER  BY line_code, month
  LIMIT  15
───────────────────────────────────────────────
shape: (15, 6)
┌─────────┬───────────┬────────────────┬────────┬───────────┬─────────┐
│ month   ┆ line_code ┆ production_qty ┆ dr_pct ┆ prev_prod ┆ prev_dr │
│ ---     ┆ ---       ┆ ---            ┆ ---    ┆ ---       ┆ ---     │
│ str     ┆ str       ┆ i64            ┆ f64    ┆ i64       ┆ f64     │
╞═════════╪═══════════╪════════════════╪════════╪═══════════╪═════════╡
│ 2024-01 ┆ LINE-A1   ┆ 9314           ┆ 1.87   ┆ null      ┆ null    │
│ 2024-02 ┆ LINE-A1   ┆ 9093           ┆ 1.78   ┆ 9314      ┆ 1.87    │
│ 2024-03 ┆ LINE-A1   ┆ 9536           ┆ 1.81   ┆ 9093      ┆ 1.78    │
│ 2024-04 ┆ LINE-A1   ┆ 9050           ┆ 2.22   ┆ 9536      ┆ 1.81    │
│ 2024-05 ┆ LINE-A1   ┆ 9289           ┆ 1.86   ┆ 9050      ┆ 2.22    │
│ …       ┆ …         ┆ …              ┆ …      ┆ …         ┆ …       │
│ 2024-11 ┆ LINE-A1   ┆ 9231           ┆ 1.65   ┆ 9142      ┆ 1.93    │
│ 2024-12 ┆ LINE-A1   ┆ 9125           ┆ 1.71   ┆ 9231      ┆ 1.65    │
│ 2024-01 ┆ LINE-A2   ┆ 6910           ┆ 2.45   ┆ null      ┆ null    │
│ 2024-02 ┆ LINE-A2   ┆ 6841           ┆ 1.64   ┆ 6910      ┆ 2.45    │
│ 2024-03 ┆ LINE-A2   ┆ 6810           ┆ 1.73   ┆ 6841      ┆ 1.64    │
└─────────┴───────────┴────────────────┴────────┴───────────┴─────────┘
↳ 15 行取得

shape: (15, 6)

month	line_code	production_qty	dr_pct	prev_prod	prev_dr
str	str	i64	f64	i64	f64
”2024-01"	"LINE-A1”	9314	1.87	null	null
”2024-02"	"LINE-A1”	9093	1.78	9314	1.87
”2024-03"	"LINE-A1”	9536	1.81	9093	1.78
”2024-04"	"LINE-A1”	9050	2.22	9536	1.81
”2024-05"	"LINE-A1”	9289	1.86	9050	2.22
…	…	…	…	…	…
“2024-11"	"LINE-A1”	9231	1.65	9142	1.93
”2024-12"	"LINE-A1”	9125	1.71	9231	1.65
”2024-01"	"LINE-A2”	6910	2.45	null	null
”2024-02"	"LINE-A2”	6841	1.64	6910	2.45
”2024-03"	"LINE-A2”	6810	1.73	6841	1.64

結果の読み取り

2024-01 は前月値が存在しないため prev_prod / prev_dr が NULL になります。これはデータが存在しないことを正しく表しています
PARTITION BY line_code を使うことで、ライン間の値が混在せずに「同じラインの前月値」だけが参照されます。 PARTITION BY を省略すると異なるラインの最後の行が前月値として使われてしまいます
不良率の prev_dr と現月の dr_pct を比較して、改善（-）か悪化（+）かを確認する基盤になります（No.068 で計算）

No.068：前月比・前月差を計算する

実務での意味

前月比（Month-over-Month = MoM）は生産管理の最も基本的な変動指標です。「前月から何 % 増減したか」を自動計算することで、月次レポートの手動更新工数を削減し、異常変動の早期検出が可能になります。

製造業での活用例：

生産数の前月比（%）が基準値（±10%）を超えた場合にアラートを発行
不良率の前月差（+ or −）で改善プログラムの効果を定量評価
年度累計 MoM の推移で生産計画の達成率を追跡

分析・モデル化の考え方

前月比と前月差の計算式：

\text{前月比（\%）} = \frac{x_t - x_{t-1}}{x_{t-1}} \times 100 \qquad \text{前月差} = x_t - x_{t-1}

前月値が 0 や NULL のとき、前月比は定義不能です。 CASE WHEN prev IS NOT NULL AND prev > 0 THEN ... ELSE NULL END で処理します。

Python で確認する

# No.068: 前月比 MoM — 月次生産数・不良率の前月比を計算

print('=== 前月比・前月差（LINE-A1 / LINE-B1 / LINE-D1）===')
df_mom = q(conn, '''
WITH base AS (
    SELECT month, line_code, factory_name,
           production_qty,
           ROUND(defect_qty * 100.0 / production_qty, 2) AS dr_pct
    FROM   production
),
with_lag AS (
    SELECT month, line_code, factory_name, production_qty, dr_pct,
           LAG(production_qty, 1) OVER (PARTITION BY line_code ORDER BY month) AS prev_prod,
           LAG(dr_pct,          1) OVER (PARTITION BY line_code ORDER BY month) AS prev_dr
    FROM   base
)
SELECT month, line_code, production_qty, prev_prod,
       CASE WHEN prev_prod IS NOT NULL
            THEN ROUND((production_qty - prev_prod) * 100.0 / prev_prod, 1)
            ELSE NULL
       END AS mom_pct,
       dr_pct, prev_dr,
       CASE WHEN prev_dr IS NOT NULL
            THEN ROUND(dr_pct - prev_dr, 2)
            ELSE NULL
       END AS dr_diff
FROM   with_lag
WHERE  line_code IN ('LINE-A1', 'LINE-B1', 'LINE-D1')
ORDER  BY line_code, month
''')

=== 前月比・前月差（LINE-A1 / LINE-B1 / LINE-D1）===
── SQL ─────────────────────────────────────────
  WITH base AS (
      SELECT month, line_code, factory_name,
             production_qty,
             ROUND(defect_qty * 100.0 / production_qty, 2) AS dr_pct
      FROM   production
  ),
  with_lag AS (
      SELECT month, line_code, factory_name, production_qty, dr_pct,
             LAG(production_qty, 1) OVER (PARTITION BY line_code ORDER BY month) AS prev_prod,
             LAG(dr_pct,          1) OVER (PARTITION BY line_code ORDER BY month) AS prev_dr
      FROM   base
  )
  SELECT month, line_code, production_qty, prev_prod,
         CASE WHEN prev_prod IS NOT NULL
              THEN ROUND((production_qty - prev_prod) * 100.0 / prev_prod, 1)
              ELSE NULL
         END AS mom_pct,
         dr_pct, prev_dr,
         CASE WHEN prev_dr IS NOT NULL
              THEN ROUND(dr_pct - prev_dr, 2)
              ELSE NULL
         END AS dr_diff
  FROM   with_lag
  WHERE  line_code IN ('LINE-A1', 'LINE-B1', 'LINE-D1')
  ORDER  BY line_code, month
───────────────────────────────────────────────
shape: (36, 8)
┌─────────┬───────────┬────────────────┬───────────┬─────────┬────────┬─────────┬─────────┐
│ month   ┆ line_code ┆ production_qty ┆ prev_prod ┆ mom_pct ┆ dr_pct ┆ prev_dr ┆ dr_diff │
│ ---     ┆ ---       ┆ ---            ┆ ---       ┆ ---     ┆ ---    ┆ ---     ┆ ---     │
│ str     ┆ str       ┆ i64            ┆ i64       ┆ f64     ┆ f64    ┆ f64     ┆ f64     │
╞═════════╪═══════════╪════════════════╪═══════════╪═════════╪════════╪═════════╪═════════╡
│ 2024-01 ┆ LINE-A1   ┆ 9314           ┆ null      ┆ null    ┆ 1.87   ┆ null    ┆ null    │
│ 2024-02 ┆ LINE-A1   ┆ 9093           ┆ 9314      ┆ -2.4    ┆ 1.78   ┆ 1.87    ┆ -0.09   │
│ 2024-03 ┆ LINE-A1   ┆ 9536           ┆ 9093      ┆ 4.9     ┆ 1.81   ┆ 1.78    ┆ 0.03    │
│ 2024-04 ┆ LINE-A1   ┆ 9050           ┆ 9536      ┆ -5.1    ┆ 2.22   ┆ 1.81    ┆ 0.41    │
│ 2024-05 ┆ LINE-A1   ┆ 9289           ┆ 9050      ┆ 2.6     ┆ 1.86   ┆ 2.22    ┆ -0.36   │
│ …       ┆ …         ┆ …              ┆ …         ┆ …       ┆ …      ┆ …       ┆ …       │
│ 2024-08 ┆ LINE-D1   ┆ 7315           ┆ 7368      ┆ -0.7    ┆ 1.61   ┆ 1.93    ┆ -0.32   │
│ 2024-09 ┆ LINE-D1   ┆ 7438           ┆ 7315      ┆ 1.7     ┆ 2.15   ┆ 1.61    ┆ 0.54    │
│ 2024-10 ┆ LINE-D1   ┆ 7733           ┆ 7438      ┆ 4.0     ┆ 1.97   ┆ 2.15    ┆ -0.18   │
│ 2024-11 ┆ LINE-D1   ┆ 8153           ┆ 7733      ┆ 5.4     ┆ 1.99   ┆ 1.97    ┆ 0.02    │
│ 2024-12 ┆ LINE-D1   ┆ 8334           ┆ 8153      ┆ 2.2     ┆ 2.15   ┆ 1.99    ┆ 0.16    │
└─────────┴───────────┴────────────────┴───────────┴─────────┴────────┴─────────┴─────────┘
↳ 36 行取得

# No.068 可視化: 月次生産数 MoM（前月比）の推移（3ライン）
TARGET_LINES = ['LINE-A1', 'LINE-B1', 'LINE-D1']
COLORS_3     = ['#4878CF', '#D65F5F', '#C4AD66']
MONTHS_LBL   = [f'{m+1}月' for m in range(12)]

# ラインごとの月次生産数と MoM を取得
prod_data = {lc: [] for lc in TARGET_LINES}
mom_data  = {lc: [] for lc in TARGET_LINES}

for row in df_mom.to_dicts():
    if row['line_code'] in TARGET_LINES:
        prod_data[row['line_code']].append(row['production_qty'])
        mom_data[row['line_code']].append(row['mom_pct'])

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

# 左: 月次生産数の推移（折れ線）
ax1 = axes[0]
for lc, col in zip(TARGET_LINES, COLORS_3):
    ax1.plot(range(12), prod_data[lc], marker='o', markersize=4,
             linewidth=1.8, color=col, label=lc)
ax1.set_title('月次 生産数の推移（2024年）', fontsize=12, pad=10)
ax1.set_xlabel('月', fontsize=10)
ax1.set_ylabel('生産数（個）', fontsize=10)
ax1.set_xticks(range(12))
ax1.set_xticklabels(MONTHS_LBL, fontsize=8)
ax1.legend(fontsize=8)
ax1.grid(alpha=0.3)

# 右: 前月比 MoM（%）の棒グラフ（2024-02〜12 の11ヶ月）
ax2 = axes[1]
x = range(11)  # 2024-02 to 2024-12
width = 0.27
for i, (lc, col) in enumerate(zip(TARGET_LINES, COLORS_3)):
    vals = mom_data[lc][1:]  # skip 2024-01 (None)
    vals_plot = [v if v is not None else 0 for v in vals]
    bars = ax2.bar([xi + i * width for xi in x], vals_plot, width,
                   color=col, alpha=0.8, label=lc)
ax2.axhline(0, color='black', linewidth=0.8, linestyle='--')
ax2.set_title('前月比（MoM %）の推移（2024年2〜12月）', fontsize=12, pad=10)
ax2.set_xlabel('月', fontsize=10)
ax2.set_ylabel('前月比（%）', fontsize=10)
ax2.set_xticks([xi + width for xi in x])
ax2.set_xticklabels([f'{m+2}月' for m in range(11)], fontsize=8)
ax2.legend(fontsize=8)
ax2.grid(axis='y', alpha=0.3)

plt.tight_layout()
plt.show()
print('前月比グラフ表示完了（SVG 2/2）')

svg

前月比グラフ表示完了（SVG 2/2）

結果の読み取り

左グラフ: 夏季（7〜8月）に各ラインの生産数がやや低下し、年末（11〜12月）に増加するシーズナリティが確認できます
右グラフ: MoM がマイナスの月（前月より生産数が減少）が棒グラフの0以下に表示されます。急激なマイナス（−15% 以下など）は設備トラブル・資材不足・連休の影響である可能性があります
不良率の dr_diff が負（改善）の月が続いているラインは、品質改善活動が効果を上げている証拠です。逆に正（悪化）が続く場合は早急な原因調査が必要です

No.069：累積生産量を計算する

実務での意味

SUM() OVER (ORDER BY ... ROWS UNBOUNDED PRECEDING) で 累積集計（ランニングトータル）を計算できます。 GROUP BY を使わずに月次データを保持しながら年度累計を算出できます。

製造業での活用例：

年間生産目標（例: 100,000個）に対する月次の累積達成量と達成率の追跡
累積不良数が許容上限を超えた月を特定してアラートを設定
複数年間の累積生産量を比較して設備の長期トレンドを分析

分析・モデル化の考え方

累積集計の数式：

S_t = \sum_{\tau=1}^{t} x_{\tau} \quad\Leftrightarrow\quad \text{SUM}(x) \text{ OVER } (\text{ORDER BY } t \text{ ROWS UNBOUNDED PRECEDING})

ROWS UNBOUNDED PRECEDING は「現在行より前のすべての行（先頭行から現在行まで）」を意味します。これにより、月が進むにつれて累積値が単調増加します。

Python で確認する

# No.069: 累積生産量 と 累積不良損失額 の計算

print('=== 全ライン 月次 + 累積生産量・累積損失額 ===')
q(conn, '''
WITH monthly AS (
    SELECT month, line_code, factory_name,
           production_qty,
           defect_qty * unit_price AS defect_loss_monthly
    FROM   production
)
SELECT month, line_code, factory_name,
       production_qty,
       SUM(production_qty) OVER (
           PARTITION BY line_code
           ORDER BY month
           ROWS UNBOUNDED PRECEDING
       )                         AS cumsum_prod,
       defect_loss_monthly,
       SUM(defect_loss_monthly) OVER (
           PARTITION BY line_code
           ORDER BY month
           ROWS UNBOUNDED PRECEDING
       )                         AS cumsum_loss
FROM   monthly
ORDER  BY line_code, month
LIMIT  24
''')

print()
print('=== 年間目標 100,000 個 に対する累積達成率（LINE-A1）===')
q(conn, '''
WITH monthly AS (
    SELECT month, production_qty
    FROM   production
    WHERE  line_code = 'LINE-A1'
)
SELECT month,
       production_qty,
       SUM(production_qty) OVER (ORDER BY month ROWS UNBOUNDED PRECEDING) AS cumsum,
       ROUND(
           SUM(production_qty) OVER (ORDER BY month ROWS UNBOUNDED PRECEDING) * 100.0 / 100000,
           1
       ) AS achievement_pct
FROM   monthly
ORDER  BY month
''')

=== 全ライン 月次 + 累積生産量・累積損失額 ===
── SQL ─────────────────────────────────────────
  WITH monthly AS (
      SELECT month, line_code, factory_name,
             production_qty,
             defect_qty * unit_price AS defect_loss_monthly
      FROM   production
  )
  SELECT month, line_code, factory_name,
         production_qty,
         SUM(production_qty) OVER (
             PARTITION BY line_code
             ORDER BY month
             ROWS UNBOUNDED PRECEDING
         )                         AS cumsum_prod,
         defect_loss_monthly,
         SUM(defect_loss_monthly) OVER (
             PARTITION BY line_code
             ORDER BY month
             ROWS UNBOUNDED PRECEDING
         )                         AS cumsum_loss
  FROM   monthly
  ORDER  BY line_code, month
  LIMIT  24
───────────────────────────────────────────────
shape: (24, 7)
┌─────────┬───────────┬──────────────┬────────────────┬─────────────┬────────────────┬─────────────┐
│ month   ┆ line_code ┆ factory_name ┆ production_qty ┆ cumsum_prod ┆ defect_loss_mo ┆ cumsum_loss │
│ ---     ┆ ---       ┆ ---          ┆ ---            ┆ ---         ┆ nthly          ┆ ---         │
│ str     ┆ str       ┆ str          ┆ i64            ┆ i64         ┆ ---            ┆ i64         │
│         ┆           ┆              ┆                ┆             ┆ i64            ┆             │
╞═════════╪═══════════╪══════════════╪════════════════╪═════════════╪════════════════╪═════════════╡
│ 2024-01 ┆ LINE-A1   ┆ 東京工場     ┆ 9314           ┆ 9314        ┆ 208800         ┆ 208800      │
│ 2024-02 ┆ LINE-A1   ┆ 東京工場     ┆ 9093           ┆ 18407       ┆ 194400         ┆ 403200      │
│ 2024-03 ┆ LINE-A1   ┆ 東京工場     ┆ 9536           ┆ 27943       ┆ 207600         ┆ 610800      │
│ 2024-04 ┆ LINE-A1   ┆ 東京工場     ┆ 9050           ┆ 36993       ┆ 241200         ┆ 852000      │
│ 2024-05 ┆ LINE-A1   ┆ 東京工場     ┆ 9289           ┆ 46282       ┆ 207600         ┆ 1059600     │
│ …       ┆ …         ┆ …            ┆ …              ┆ …           ┆ …              ┆ …           │
│ 2024-08 ┆ LINE-A2   ┆ 東京工場     ┆ 6355           ┆ 53059       ┆ 138700         ┆ 991800      │
│ 2024-09 ┆ LINE-A2   ┆ 東京工場     ┆ 6826           ┆ 59885       ┆ 118750         ┆ 1110550     │
│ 2024-10 ┆ LINE-A2   ┆ 東京工場     ┆ 6621           ┆ 66506       ┆ 116850         ┆ 1227400     │
│ 2024-11 ┆ LINE-A2   ┆ 東京工場     ┆ 7005           ┆ 73511       ┆ 115900         ┆ 1343300     │
│ 2024-12 ┆ LINE-A2   ┆ 東京工場     ┆ 7081           ┆ 80592       ┆ 161500         ┆ 1504800     │
└─────────┴───────────┴──────────────┴────────────────┴─────────────┴────────────────┴─────────────┘
↳ 24 行取得

=== 年間目標 100,000 個 に対する累積達成率（LINE-A1）===
── SQL ─────────────────────────────────────────
  WITH monthly AS (
      SELECT month, production_qty
      FROM   production
      WHERE  line_code = 'LINE-A1'
  )
  SELECT month,
         production_qty,
         SUM(production_qty) OVER (ORDER BY month ROWS UNBOUNDED PRECEDING) AS cumsum,
         ROUND(
             SUM(production_qty) OVER (ORDER BY month ROWS UNBOUNDED PRECEDING) * 100.0 / 100000,
             1
         ) AS achievement_pct
  FROM   monthly
  ORDER  BY month
───────────────────────────────────────────────
shape: (12, 4)
┌─────────┬────────────────┬────────┬─────────────────┐
│ month   ┆ production_qty ┆ cumsum ┆ achievement_pct │
│ ---     ┆ ---            ┆ ---    ┆ ---             │
│ str     ┆ i64            ┆ i64    ┆ f64             │
╞═════════╪════════════════╪════════╪═════════════════╡
│ 2024-01 ┆ 9314           ┆ 9314   ┆ 9.3             │
│ 2024-02 ┆ 9093           ┆ 18407  ┆ 18.4            │
│ 2024-03 ┆ 9536           ┆ 27943  ┆ 27.9            │
│ 2024-04 ┆ 9050           ┆ 36993  ┆ 37.0            │
│ 2024-05 ┆ 9289           ┆ 46282  ┆ 46.3            │
│ …       ┆ …              ┆ …      ┆ …               │
│ 2024-08 ┆ 8690           ┆ 72439  ┆ 72.4            │
│ 2024-09 ┆ 8845           ┆ 81284  ┆ 81.3            │
│ 2024-10 ┆ 9142           ┆ 90426  ┆ 90.4            │
│ 2024-11 ┆ 9231           ┆ 99657  ┆ 99.7            │
│ 2024-12 ┆ 9125           ┆ 108782 ┆ 108.8           │
└─────────┴────────────────┴────────┴─────────────────┘
↳ 12 行取得

shape: (12, 4)

month	production_qty	cumsum	achievement_pct
str	i64	i64	f64
”2024-01”	9314	9314	9.3
”2024-02”	9093	18407	18.4
”2024-03”	9536	27943	27.9
”2024-04”	9050	36993	37.0
”2024-05”	9289	46282	46.3
…	…	…	…
“2024-08”	8690	72439	72.4
”2024-09”	8845	81284	81.3
”2024-10”	9142	90426	90.4
”2024-11”	9231	99657	99.7
”2024-12”	9125	108782	108.8

結果の読み取り

cumsum_prod は月が進むにつれて単調増加します。夏季の生産低下月（前月比減少月）でも累積は増え続けるため、年間目標との乖離が月次で把握できます
cumsum_loss（累積不良損失額）の増加ペースが速いラインは早期の品質改善介入が必要です。年間での損失総額の見通しが月次で得られます
achievement_pct（達成率）が月次で確認できるため、「このままのペースで行くと年末の達成率は何 % か」の予測が容易になります

No.070：移動平均を計算する

実務での意味

移動平均（Moving Average）は、直近 N ヶ月の平均を逐次計算することで月次データの短期ノイズを除去してトレンドを抽出します。

製造業での活用例：

3ヶ月移動平均不良率で「一時的なスパイク」と「真の品質悪化」を区別
6ヶ月移動平均生産数で季節変動を除去した長期トレンドを把握
移動平均 vs 実績の乖離が大きい月を異常検知のシグナルとして使用

分析・モデル化の考え方

$n$ ヶ月移動平均の定義：

\text{MA}_n(t) = \frac{1}{n} \sum_{\tau=t-n+1}^{t} x_{\tau}

SQL での実装：

AVG(x) OVER (
    PARTITION BY line_code
    ORDER BY month
    ROWS BETWEEN (n-1) PRECEDING AND CURRENT ROW
)

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW は「現在行 + 直前2行」= 3行を対象にします。

Python で確認する

# No.070: 3ヶ月・6ヶ月 移動平均不良率の計算

print('=== 月次不良率 + 3ヶ月 / 6ヶ月 移動平均（LINE-A1, LINE-C1）===')
q(conn, '''
WITH monthly AS (
    SELECT month, line_code, factory_name,
           ROUND(defect_qty * 100.0 / production_qty, 3) AS dr_pct
    FROM   production
)
SELECT month, line_code, factory_name, dr_pct,
       ROUND(AVG(dr_pct) OVER (
           PARTITION BY line_code
           ORDER BY month
           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ), 3) AS ma3,
       ROUND(AVG(dr_pct) OVER (
           PARTITION BY line_code
           ORDER BY month
           ROWS BETWEEN 5 PRECEDING AND CURRENT ROW
       ), 3) AS ma6
FROM   monthly
WHERE  line_code IN ('LINE-A1', 'LINE-C1')
ORDER  BY line_code, month
''')

print()
print('=== 実績値と3ヶ月移動平均の乖離が大きい月（|乖離| > 0.1%）===')
q(conn, '''
WITH monthly AS (
    SELECT month, line_code,
           ROUND(defect_qty * 100.0 / production_qty, 3) AS dr_pct
    FROM   production
),
with_ma AS (
    SELECT month, line_code, dr_pct,
           ROUND(AVG(dr_pct) OVER (
               PARTITION BY line_code
               ORDER BY month
               ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
           ), 3) AS ma3
    FROM   monthly
)
SELECT month, line_code, dr_pct, ma3,
       ROUND(ABS(dr_pct - ma3), 3) AS deviation
FROM   with_ma
WHERE  ABS(dr_pct - ma3) > 0.1
ORDER  BY deviation DESC
LIMIT  10
''')

=== 月次不良率 + 3ヶ月 / 6ヶ月 移動平均（LINE-A1, LINE-C1）===
── SQL ─────────────────────────────────────────
  WITH monthly AS (
      SELECT month, line_code, factory_name,
             ROUND(defect_qty * 100.0 / production_qty, 3) AS dr_pct
      FROM   production
  )
  SELECT month, line_code, factory_name, dr_pct,
         ROUND(AVG(dr_pct) OVER (
             PARTITION BY line_code
             ORDER BY month
             ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
         ), 3) AS ma3,
         ROUND(AVG(dr_pct) OVER (
             PARTITION BY line_code
             ORDER BY month
             ROWS BETWEEN 5 PRECEDING AND CURRENT ROW
         ), 3) AS ma6
  FROM   monthly
  WHERE  line_code IN ('LINE-A1', 'LINE-C1')
  ORDER  BY line_code, month
───────────────────────────────────────────────


shape: (24, 6)
┌─────────┬───────────┬──────────────┬────────┬───────┬───────┐
│ month   ┆ line_code ┆ factory_name ┆ dr_pct ┆ ma3   ┆ ma6   │
│ ---     ┆ ---       ┆ ---          ┆ ---    ┆ ---   ┆ ---   │
│ str     ┆ str       ┆ str          ┆ f64    ┆ f64   ┆ f64   │
╞═════════╪═══════════╪══════════════╪════════╪═══════╪═══════╡
│ 2024-01 ┆ LINE-A1   ┆ 東京工場     ┆ 1.868  ┆ 1.868 ┆ 1.868 │
│ 2024-02 ┆ LINE-A1   ┆ 東京工場     ┆ 1.782  ┆ 1.825 ┆ 1.825 │
│ 2024-03 ┆ LINE-A1   ┆ 東京工場     ┆ 1.814  ┆ 1.821 ┆ 1.821 │
│ 2024-04 ┆ LINE-A1   ┆ 東京工場     ┆ 2.221  ┆ 1.939 ┆ 1.921 │
│ 2024-05 ┆ LINE-A1   ┆ 東京工場     ┆ 1.862  ┆ 1.966 ┆ 1.909 │
│ …       ┆ …         ┆ …            ┆ …      ┆ …     ┆ …     │
│ 2024-08 ┆ LINE-C1   ┆ 名古屋工場   ┆ 2.353  ┆ 2.496 ┆ 2.49  │
│ 2024-09 ┆ LINE-C1   ┆ 名古屋工場   ┆ 2.496  ┆ 2.53  ┆ 2.461 │
│ 2024-10 ┆ LINE-C1   ┆ 名古屋工場   ┆ 2.445  ┆ 2.431 ┆ 2.537 │
│ 2024-11 ┆ LINE-C1   ┆ 名古屋工場   ┆ 2.411  ┆ 2.451 ┆ 2.473 │
│ 2024-12 ┆ LINE-C1   ┆ 名古屋工場   ┆ 2.039  ┆ 2.298 ┆ 2.414 │
└─────────┴───────────┴──────────────┴────────┴───────┴───────┘
↳ 24 行取得

=== 実績値と3ヶ月移動平均の乖離が大きい月（|乖離| > 0.1%）===
── SQL ─────────────────────────────────────────
  WITH monthly AS (
      SELECT month, line_code,
             ROUND(defect_qty * 100.0 / production_qty, 3) AS dr_pct
      FROM   production
  ),
  with_ma AS (
      SELECT month, line_code, dr_pct,
             ROUND(AVG(dr_pct) OVER (
                 PARTITION BY line_code
                 ORDER BY month
                 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
             ), 3) AS ma3
      FROM   monthly
  )
  SELECT month, line_code, dr_pct, ma3,
         ROUND(ABS(dr_pct - ma3), 3) AS deviation
  FROM   with_ma
  WHERE  ABS(dr_pct - ma3) > 0.1
  ORDER  BY deviation DESC
  LIMIT  10
───────────────────────────────────────────────
shape: (10, 5)
┌─────────┬───────────┬────────┬───────┬───────────┐
│ month   ┆ line_code ┆ dr_pct ┆ ma3   ┆ deviation │
│ ---     ┆ ---       ┆ ---    ┆ ---   ┆ ---       │
│ str     ┆ str       ┆ f64    ┆ f64   ┆ f64       │
╞═════════╪═══════════╪════════╪═══════╪═══════════╡
│ 2024-04 ┆ LINE-C1   ┆ 1.992  ┆ 2.438 ┆ 0.446     │
│ 2024-02 ┆ LINE-A2   ┆ 1.637  ┆ 2.042 ┆ 0.405     │
│ 2024-12 ┆ LINE-A2   ┆ 2.401  ┆ 2.0   ┆ 0.401     │
│ 2024-08 ┆ LINE-D1   ┆ 1.613  ┆ 1.956 ┆ 0.343     │
│ 2024-05 ┆ LINE-C1   ┆ 2.792  ┆ 2.483 ┆ 0.309     │
│ 2024-05 ┆ LINE-D1   ┆ 1.705  ┆ 1.997 ┆ 0.292     │
│ 2024-04 ┆ LINE-A1   ┆ 2.221  ┆ 1.939 ┆ 0.282     │
│ 2024-12 ┆ LINE-C1   ┆ 2.039  ┆ 2.298 ┆ 0.259     │
│ 2024-09 ┆ LINE-D1   ┆ 2.151  ┆ 1.897 ┆ 0.254     │
│ 2024-06 ┆ LINE-D1   ┆ 2.327  ┆ 2.074 ┆ 0.253     │
└─────────┴───────────┴────────┴───────┴───────────┘
↳ 10 行取得

shape: (10, 5)

month	line_code	dr_pct	ma3	deviation
str	str	f64	f64	f64
”2024-04"	"LINE-C1”	1.992	2.438	0.446
”2024-02"	"LINE-A2”	1.637	2.042	0.405
”2024-12"	"LINE-A2”	2.401	2.0	0.401
”2024-08"	"LINE-D1”	1.613	1.956	0.343
”2024-05"	"LINE-C1”	2.792	2.483	0.309
”2024-05"	"LINE-D1”	1.705	1.997	0.292
”2024-04"	"LINE-A1”	2.221	1.939	0.282
”2024-12"	"LINE-C1”	2.039	2.298	0.259
”2024-09"	"LINE-D1”	2.151	1.897	0.254
”2024-06"	"LINE-D1”	2.327	2.074	0.253

結果の読み取り

ma3（3ヶ月移動平均）は月次の不良率が急上昇したとき、その変動を緩和します。 ma3 > dr_pct の場合、今月は直近3ヶ月平均より改善されていることを意味します
ma6（6ヶ月移動平均）は季節変動をより強く除去するため、長期トレンド（年間を通じた改善 or 悪化）の把握に適しています
deviation（実績 vs 3ヶ月 MA の乖離）が大きい月は工程の異常変動が疑われます。これをアラートのしきい値として設定することで、品質管理の自動モニタリングが実現できます

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

No.061〜070 で学んだウィンドウ関数を通じて、以下の製造業 KPI 分析が1クエリで実現できます。

実務 KPI	SQL パターン
月次生産シェア	`SUM() OVER (PARTITION BY line)`
生産数ワースト月の特定	`ROW_NUMBER() + WHERE rn <= N`
不良率ランキング	`RANK() / DENSE_RANK() OVER (ORDER BY dr DESC)`
工場別ワーストライン	`ROW_NUMBER() OVER (PARTITION BY factory ORDER BY loss DESC)`
前月比（MoM %）	`LAG(prod, 1)` → `(prod - prev_prod) / prev_prod * 100`
累積生産量・累積損失額	`SUM() OVER (ORDER BY month ROWS UNBOUNDED PRECEDING)`
不良率移動平均（ノイズ除去）	`AVG(dr) OVER (ROWS BETWEEN N PRECEDING AND CURRENT ROW)`

**GROUP BY では1クエリで実現不可能な「行を保持しながら集計する」**分析が、ウィンドウ関数によって自在に操れるようになります。

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

1. ウィンドウ関数対応の DB バージョン確認

SQLite 3.28.0 以降、PostgreSQL 8.4 以降、MySQL 8.0 以降でウィンドウ関数が使えます。本ノートブックで使用した SQLite は 3.39.0+ で RIGHT JOIN も加わり、ほぼすべての標準ウィンドウ関数が利用可能です。

2. パフォーマンス最適化

ウィンドウ関数はフルテーブルスキャンになりやすいため、大量データでは PARTITION BY のキー列にインデックスを設定してください。 CREATE INDEX idx_prod_line ON production(line_code, month);

3. NULL 処理の徹底

LAG() で取得した前月値が NULL の場合（最初の月）、前月比の計算は CASE WHEN prev IS NOT NULL THEN ... ELSE NULL END で保護します。 NULL をゼロとして扱うと誤った前月比（−100% など）が発生します。

4. CTE との組み合わせ

複雑なウィンドウ関数クエリは CTE（WITH 句）を使って段階的に分解することで、保守性が大幅に向上します。No.069〜070 で示した CTE + ウィンドウ関数のパターンを活用してください。

まとめ

本章では、SQL のウィンドウ関数を使って製造ラインの月次生産 KPI を多角的に分析しました。

ノック	主な関数・構文	製造業での活用ポイント
No.061	`OVER()` の基本構造	GROUP BY との違い・月別シェア計算
No.062	`ROW_NUMBER()`	各ラインの生産ワースト月を連番で特定
No.063	`RANK()`	全ライン×全月の不良率ランキング
No.064	`DENSE_RANK()`	同率ランキングでの連続番号付与
No.065	`ROW_NUMBER() PARTITION BY`	工場別ワースト損失ラインの抽出
No.066	`RANK()` 複数同時適用	生産量 × 品質の複合ランキング
No.067	`LAG(col, 1)`	前月の生産数・不良率を同行に参照
No.068	LAG + CASE	月次前月比（MoM %）と不良率前月差の自動算出
No.069	`SUM() OVER (ROWS UNBOUNDED PRECEDING)`	月次累積生産量・累積損失額の追跡
No.070	`AVG() OVER (ROWS BETWEEN N PRECEDING)`	移動平均でノイズ除去・トレンド抽出

次章（第8章: 実務データ分析 SQL）では、これらの技術を組み合わせた KPI 集計・RFM 分析・在庫管理などの実務クエリを学びます。

法人向けのご相談

製造業のデータ分析・SQL 教育・DX 推進について、以下よりお気軽にご相談ください。

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

SQLウィンドウ関数で製造ラインの月次トレンドを多角的に分析する

SQLウィンドウ関数で製造ラインの月次トレンドを多角的に分析する

SQL 100本ノック 第7章（No.061〜No.070）：ウィンドウ関数

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

現場でよくある状況

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

1. OVER() 句の構造を理解する

2. RANK と DENSE_RANK の違い

3. LAG / LEAD のデフォルト値と NULL 処理

4. フレーム指定（ROWS vs RANGE）

今回扱うノックの全体像

Python 環境の準備

架空データの作成

No.061：ウィンドウ関数の考え方を理解する

実務での意味

分析・モデル化の考え方

Python で確認する

結果の読み取り

No.062：ROW_NUMBERで連番を付ける

実務での意味

分析・モデル化の考え方

Python で確認する

結果の読み取り

No.063：RANKでランキングを作る

実務での意味

分析・モデル化の考え方

Python で確認する

結果の読み取り

No.064：DENSE_RANKで同順位を扱う

実務での意味

分析・モデル化の考え方

Python で確認する

結果の読み取り

No.065：不良損失額の上位ラインを抽出する

実務での意味

分析・モデル化の考え方

Python で確認する

結果の読み取り

No.066：ライン別生産量ランキングを作る

実務での意味

分析・モデル化の考え方

Python で確認する

結果の読み取り

No.067：LAGで前回値を取得する

実務での意味

分析・モデル化の考え方

Python で確認する

結果の読み取り

No.068：前月比・前月差を計算する

実務での意味

分析・モデル化の考え方

Python で確認する

結果の読み取り

No.069：累積生産量を計算する

実務での意味

分析・モデル化の考え方

Python で確認する

結果の読み取り

No.070：移動平均を計算する

実務での意味

分析・モデル化の考え方

Python で確認する

結果の読み取り

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

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

1. ウィンドウ関数対応の DB バージョン確認

2. パフォーマンス最適化

3. NULL 処理の徹底

4. CTE との組み合わせ

まとめ

法人向けのご相談

Related Posts

製造ラインのデータ品質管理・SQL設計・BI連携を実務に落とし込む

SQL応用分析テクニックで製造業の意思決定を深掘りする

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

サブクエリ・CTEで製造ラインの異常検知とKPI集計を自動化する

SQLのJOINで工場・部品・検査記録を横断分析する

SQL 100本ノック第7章（No.061〜No.070）：ウィンドウ関数