SQL 競合分析 — LAG/LEAD・RANK・スコアリングの応用

QUESTION 6

LAG() + CTE — 前期比成長率で競合の四半期トレンド加速・減速を可視化する

LAG / LEADCTE前期比成長率NULLIF・0除算防止

前提知識 ▼

LAG(expr, offset, default) はウィンドウ関数の一種で、現在行から offset 行前の値を返します。前期比・前年同期比の計算に不可欠なパターンです。

LAG(revenue) OVER (
  PARTITION BY company_name   -- 企業をまたいで前行を取らない
  ORDER BY     quarter        -- 四半期順で「1行前」を確定
)                              -- デフォルト offset=1, default=NULL

NULLIF で 0 除算を防ぐ：前期が 0 だと revenue / prev_revenue が ZeroDivisionError になります。NULLIF(prev_revenue, 0) は値が 0 のとき NULL を返すため、NULL との除算は NULL（エラーではない）として安全に処理できます。

CTE で LAG を一度だけ計算する：成長率の計算に LAG を直接 SELECT に複数回書くと保守性が下がります。CTE でまず prev_revenue を確定させ、外側クエリで再利用するのがベストプラクティスです。

問題 ▼

3社の四半期売上から、各社の前期売上（prev_revenue）と前期比成長率（growth_rate_pct、小数第1位）を算出してください。取得列は company_name, quarter, revenue, prev_revenue, growth_rate_pct、company_name 昇順 → quarter 昇順で返してください。prev_revenue と growth_rate_pct は NULL になります。

使用テーブル

▸ quarterly_revenue

company_name	quarter	revenue
AlphaTech	2023Q1	300
AlphaTech	2023Q2	360
AlphaTech	2023Q3	280
AlphaTech	2023Q4	400
BetaSoft	2023Q1	250
BetaSoft	2023Q2	220
BetaSoft	2023Q3	290
BetaSoft	2023Q4	310
GammaSys	2023Q1	120
GammaSys	2023Q2	150
GammaSys	2023Q3	170
GammaSys	2023Q4	160

※ revenue 単位: 億円

期待出力 ▼

company_name	quarter	revenue	prev_revenue	growth_rate_pct
AlphaTech	2023Q1	300	NULL	NULL
AlphaTech	2023Q2	360	300	20.0
AlphaTech	2023Q3	280	360	-22.2
AlphaTech	2023Q4	400	280	42.9
BetaSoft	2023Q1	250	NULL	NULL
BetaSoft	2023Q2	220	250	-12.0
BetaSoft	2023Q3	290	220	31.8
BetaSoft	2023Q4	310	290	6.9
GammaSys	2023Q1	120	NULL	NULL
GammaSys	2023Q2	150	120	25.0
GammaSys	2023Q3	170	150	13.3
GammaSys	2023Q4	160	170	-5.9

模範解答コード ▼

WITH lagged AS (
  SELECT
    company_name,
    quarter,
    revenue,
    LAG(revenue) OVER (
      PARTITION BY company_name   -- 企業ごとに独立ウィンドウ
      ORDER BY     quarter        -- 四半期昇順で「1つ前」を定義
    ) AS prev_revenue
  FROM quarterly_revenue
)
SELECT
  company_name,
  quarter,
  revenue,
  prev_revenue,
  ROUND(
    (revenue - prev_revenue) * 100.0
    / NULLIF(prev_revenue, 0),   -- 0除算防止（第1四半期はNULL→NULLのまま安全）
    1
  ) AS growth_rate_pct
FROM   lagged
ORDER BY company_name, quarter;

/*
  実行順序（SQLの論理的な評価順）:
  1. CTE lagged                      → CTE を定義（LAG ウィンドウ関数を評価）
  2. 外側 SELECT FROM lagged         → CTE を読み込む
  3. SELECT                          → 列を評価・整形（NULLIF, ROUND）
  4. ORDER BY company_name, quarter  → 並び替えて出力
*/

解説（テーブル変化・ポイント） ▼

WITH lagged AS (
  SELECT
    company_name, quarter, revenue,
    LAG(revenue) OVER (
      PARTITION BY company_name
      ORDER BY     quarter
    ) AS prev_revenue
  FROM quarterly_revenue
)
SELECT
  company_name, quarter, revenue,
  prev_revenue,
  ROUND(
    (revenue - prev_revenue) * 100.0
    / NULLIF(prev_revenue, 0), 1
  ) AS growth_rate_pct
FROM   lagged
ORDER BY company_name, quarter;

LEGEND ▼

データ取得・読込対象

① FROM quarterly_revenue — 12行読込 ▼

FROM quarterly_revenuequarterly_revenue の12行（3社×4四半期）を読み込みます。この後 LAG() OVER (PARTITION BY company_name ORDER BY quarter) で前期値を取得し、CTE lagged に格納します。

1 / 4

company_name	quarter	revenue
AlphaTech	2023Q1	300
AlphaTech	2023Q2	360
AlphaTech	2023Q3	280
AlphaTech	2023Q4	400
BetaSoft	2023Q1	250
BetaSoft	2023Q2	220
BetaSoft	2023Q3	290
BetaSoft	2023Q4	310
GammaSys	2023Q1	120
GammaSys	2023Q2	150
GammaSys	2023Q3	170
GammaSys	2023Q4	160

12行読込（3社×4四半期）

学習ポイント

LAG() の PARTITION BY が企業間の混在を防ぐ：PARTITION BY company_name がないと、AlphaTech Q1 行の「前行」が別企業の最終四半期になってしまいます。PARTITION BY で企業ごとに独立したウィンドウを設け、同社内の時系列のみを参照させるのが必須です。LEAD() は逆に「次の行」の値を取得します。

NULLIF が 0 除算とエラーを同時に解決する：NULLIF(prev_revenue, 0) は prev_revenue が 0 のとき NULL を返します。NULL による除算の結果は NULL（エラーではない）のため、ROUND(NULL, 1) = NULL と安全に伝播し、ZeroDivisionError を防ぎます。prev_revenue は最初から NULL なので NULLIF 変換不要ですが、万一 prev が 0 の行があっても安全です。

CTE で LAG を1回だけ計算する：CTE なしで growth_rate_pct を書こうとすると、(revenue - LAG(...) OVER(...)) * 100.0 / NULLIF(LAG(...) OVER(...), 0) のように同じ LAG 式を複数回繰り返す必要があり保守性が下がります。CTE で prev_revenue を一度確定させると外側 SELECT は単純な列参照だけになり読みやすくなります。

アンチパターン

WHERE 句で LAG() を直接使おうとする：WHERE LAG(revenue) OVER(...) > 0 は SQL 文法エラーです。ウィンドウ関数は WHERE 句で使えません。CTE や派生テーブルで先に計算し、外側の WHERE でフィルタするパターンが正解です。

PARTITION BY を省略して企業をまたいだ前期値を取る：PARTITION BY なしの LAG は全行を一つのウィンドウとして扱うため、AlphaTech 最終四半期の「次の行」である BetaSoft 第1四半期が prev_revenue として取られます。前期比はほぼ必ず PARTITION BY を伴います。

実務コラム：前期比 vs 前年同期比 — LAG(1) と LAG(4) の使い分け

四半期データでは「前の四半期」（QoQ）と「前年同期」（YoY）の2種類の比較が頻出します。LAG(revenue, 1)（デフォルト）は直前四半期、LAG(revenue, 4) は4行前（= 1年前の同四半期）を返します。季節性の強いビジネス（小売・旅行・EC）ではQoQ より YoY の方が実態に即した成長率指標になります。両方を同時に出す場合は LAG(revenue,1) OVER(...) AS prev_q, LAG(revenue,4) OVER(...) AS prev_year_q と並べて書くことが可能で、同じパーティションなら2つのウィンドウ計算を1パスで処理します。

QUESTION 7

RANK() + CTE — カテゴリ別競合ランキングと上位 N 社フィルタ

RANK / DENSE_RANKCTE + WHEREカテゴリ別トップN同率順位の扱い

前提知識 ▼

RANK() / DENSE_RANK() / ROW_NUMBER() はいずれも順位を返すウィンドウ関数ですが、同率順位の扱いが異なります。

関数	同率2位が2行ある場合	次の順位
`ROW_NUMBER()`	2, 3（擬似的に一意付与）	4
`RANK()`	2, 2（同率）	4（飛び番）
`DENSE_RANK()`	2, 2（同率）	3（連番）

WITH ranked AS (
  SELECT *, RANK() OVER (PARTITION BY category ORDER BY annual_revenue DESC) AS rnk
  FROM product_revenue
)
SELECT * FROM ranked WHERE rnk <= 2;  -- 各カテゴリの上位2位以内（同率を含む）

CTE + WHERE によるトップN抽出パターン：ウィンドウ関数の結果は同一 SELECT の WHERE では参照できません（論理評価順で WHERE が先）。CTE でランクを付与し、外側の WHERE でフィルタするのが定石です。

問題 ▼

各カテゴリの競合売上データから、カテゴリ内売上ランキング（rank_in_category）を付与し、各カテゴリの上位2位以内（同率を含む）の企業のみを抽出してください。取得列は category, company_name, annual_revenue, rank_in_category、カテゴリはAI/ML→クラウド→セキュリティ→データ分析の指定順、各カテゴリ内は rank_in_category 昇順、同順位は company_name 昇順で返してください。

使用テーブル

▸ product_revenue

company_name	category	annual_revenue
AlphaTech	クラウド	4200
AlphaTech	セキュリティ	1800
AlphaTech	AI/ML	950
BetaSoft	クラウド	2800
BetaSoft	AI/ML	1500
BetaSoft	データ分析	800
GammaSys	セキュリティ	900
GammaSys	AI/ML	650
GammaSys	データ分析	420
DeltaNet	クラウド	1200
DeltaNet	データ分析	600

※ annual_revenue 単位: 億円

期待出力 ▼

category	company_name	annual_revenue	rank_in_category
AI/ML	BetaSoft	1500	1
AI/ML	AlphaTech	950	2
クラウド	AlphaTech	4200	1
クラウド	BetaSoft	2800	2
セキュリティ	AlphaTech	1800	1
セキュリティ	GammaSys	900	2
データ分析	BetaSoft	800	1
データ分析	DeltaNet	600	2

模範解答コード ▼

WITH ranked AS (
  SELECT
    category,
    company_name,
    annual_revenue,
    RANK() OVER (
      PARTITION BY category             -- カテゴリごとに独立ランキング
      ORDER BY     annual_revenue DESC  -- 売上降順で順位付け
    ) AS rank_in_category
  FROM product_revenue
)
SELECT
  category,
  company_name,
  annual_revenue,
  rank_in_category
FROM   ranked
WHERE  rank_in_category <= 2            -- 各カテゴリの上位2位以内（同率を含む）
ORDER BY CASE category
  WHEN 'AI/ML' THEN 1
  WHEN 'クラウド' THEN 2
  WHEN 'セキュリティ' THEN 3
  WHEN 'データ分析' THEN 4
  END, rank_in_category, company_name;

/*
  実行順序（SQLの論理的な評価順）:
  1. CTE ranked                       → CTE を定義（RANK ウィンドウ関数を評価）
  2. 外側 SELECT FROM ranked          → CTE を読み込む
  3. WHERE rank_in_category <= 2      → 行を絞り込む
  4. SELECT                           → 列を評価
  5. ORDER BY CASE category WHEN 'AI/ML' THEN 1 WHEN 'クラウド' THEN 2 WHEN 'セキュリティ' THEN 3 WHEN 'データ分析' THEN 4 END, rank_in_category, company_name → 並び替えて出力
*/

解説（テーブル変化・ポイント） ▼

WITH ranked AS (
  SELECT
    category, company_name, annual_revenue,
    RANK() OVER (
      PARTITION BY category
      ORDER BY     annual_revenue DESC
    ) AS rank_in_category
  FROM product_revenue
)
SELECT
  category, company_name, annual_revenue, rank_in_category
FROM   ranked
WHERE  rank_in_category <= 2
ORDER BY CASE category WHEN 'AI/ML' THEN 1 WHEN 'クラウド' THEN 2 WHEN 'セキュリティ' THEN 3 WHEN 'データ分析' THEN 4 END, rank_in_category, company_name;

LEGEND ▼

データ取得・読込対象

① FROM product_revenue — 11行読込 ▼

FROM product_revenueproduct_revenue の11行（4カテゴリ×各社参入状況）を読み込みます。各社の annual_revenue が縦持ちで格納されています。RANK() OVER で各カテゴリ内ランキングを付与していきます。

1 / 4

category	company_name	annual_revenue
AI/ML	AlphaTech	950
AI/ML	BetaSoft	1500
AI/ML	GammaSys	650
クラウド	AlphaTech	4200
クラウド	BetaSoft	2800
クラウド	DeltaNet	1200
セキュリティ	AlphaTech	1800
セキュリティ	GammaSys	900
データ分析	BetaSoft	800
データ分析	DeltaNet	600
データ分析	GammaSys	420

11行読込（4カテゴリ）

学習ポイント

RANK / DENSE_RANK / ROW_NUMBER の選択基準：「同率2位が2社いても両社を上位2位として残したい」なら RANK() か DENSE_RANK()。「厳密に2行だけ欲しい」なら ROW_NUMBER()（タイブレークに追加ソートが必要）。競合ランキングでは同率を正しく扱える RANK が最も実態に即していることが多いです。DENSE_RANK との違いは飛び番の有無のみです。

CTE + WHERE によるトップN フィルタは黄金パターン：ウィンドウ関数の計算結果は同じ SELECT の WHERE では参照できません。CTE や派生テーブルでランクを確定させ、外側の WHERE でフィルタするのが唯一の正解です。「各部門トップ3」「各地域トップ5」など多数の場面で使います。

PARTITION BY なし RANK() は全体順位になる：RANK() OVER (ORDER BY annual_revenue DESC)（PARTITION BY なし）は全11行を1ウィンドウとして全体ランキングになります。カテゴリ別ランキングには必ず PARTITION BY category を明示してください。

アンチパターン

WHERE で直接 RANK() を呼び出す：WHERE RANK() OVER (...) <= 2 は構文エラーです。ウィンドウ関数は SELECT リストまたは ORDER BY でのみ使えます。必ず CTE または派生テーブルで一度ランクを確定させてから WHERE でフィルタしてください。

トップNに ROW_NUMBER を使い同率を切り捨てる：「上位2位以内」を ROW_NUMBER で取ると、同率2位が2社いる場合に片方が 3 になり除外されます。競合分析では同率を公平に扱うために RANK または DENSE_RANK を選択し、ROW_NUMBER は「厳密にN行だけ」という要件に限定してください。

実務コラム：カテゴリ別トップN の実務応用パターン

「各カテゴリのトップN社」は競合ベンチマーク・市場レポートで最も頻出するクエリパターンの一つです。具体的な応用例として、「各製品ラインでシェア上位3社を対象にKPI比較」「各営業エリアで売上トップ5顧客を抽出してフォーカスリストを生成」「各広告チャネルでCPA上位10キャンペーンを自動抽出してレポートに差し込む」などがあります。N の値をパラメータ化（WHERE rank_in_category <= :n）することで、ユーザーが N を変更するだけで異なるレポートビューを生成できる再利用可能なクエリテンプレートとして整備できます。

QUESTION 8

複数 CTE スコアリング — 段階的集計で競合の総合 KPI 評価グレードを自動算出する

WITH 複数CTECASE WHEN競合スコアリング段階的集計・グレード付け

前提知識 ▼

複数の CTE は WITH 句の中にカンマで連結して定義でき、前段の CTE を後段の CTE で参照できます。複雑な集計を段階的に分解し、各ステップを名前付きで可読化できます。

WITH
step1 AS (
  SELECT ..., CASE WHEN col >= 30 THEN 3 ELSE 1 END AS score
  FROM source_table
),
step2 AS (          -- step1 を参照可能
  SELECT *, score_a + score_b AS total
  FROM step1
)
SELECT * FROM step2;

CASE WHEN の ELSE 省略に注意：CASE WHEN ... END で ELSE を省略すると ELSE NULL になります。スコアリングでは必ず ELSE 最低点 を明示してください。

問題 ▼

6社の競合 KPI データから、売上規模・成長率・NPS それぞれを 1〜3 点でスコアリングし、合計点（最大9点）と総合グレード（S/A/B/C）を算出してください。取得列は company_name, revenue_score, growth_score, nps_pts, total_score, grade、total_score 降順 → company_name 昇順で返してください。

スコア基準：revenue_score（revenue_bn ≥30→3, ≥10→2, else 1）｜growth_score（growth_pct ≥25→3, ≥10→2, else 1）｜nps_pts（nps_score ≥65→3, ≥50→2, else 1）｜grade（total ≥8→S, ≥6→A, ≥4→B, else C）

使用テーブル

▸ competitor_kpis

company_name	revenue_bn	growth_pct	nps_score
AlphaTech	42.0	18.5	72
BetaSoft	28.0	12.3	58
GammaSys	15.0	31.2	65
DeltaNet	5.0	8.1	48
EpsilonSys	3.0	-2.4	41
ZetaCloud	2.0	5.6	55

※ revenue_bn: 売上（十億円）／ growth_pct: 前年成長率(%)／ nps_score: 顧客NPS

期待出力 ▼

company_name	revenue_score	growth_score	nps_pts	total_score	grade
AlphaTech	3	2	3	8	S
GammaSys	2	3	3	8	S
BetaSoft	2	2	2	6	A
ZetaCloud	1	1	2	4	B
DeltaNet	1	1	1	3	C
EpsilonSys	1	1	1	3	C

模範解答コード ▼

WITH
scores AS (
  SELECT
    company_name, revenue_bn, growth_pct, nps_score,
    CASE WHEN revenue_bn >= 30 THEN 3
         WHEN revenue_bn >= 10 THEN 2
         ELSE                       1 END AS revenue_score, -- 売上規模スコア
    CASE WHEN growth_pct >= 25  THEN 3
         WHEN growth_pct >= 10  THEN 2
         ELSE                        1 END AS growth_score,  -- 成長率スコア
    CASE WHEN nps_score >= 65  THEN 3
         WHEN nps_score >= 50  THEN 2
         ELSE                        1 END AS nps_pts        -- NPSスコア
  FROM competitor_kpis
),
totaled AS (
  SELECT
    *,
    revenue_score + growth_score + nps_pts AS total_score  -- 合計（最大9点）
  FROM scores
)
SELECT
  company_name,
  revenue_score, growth_score, nps_pts, total_score,
  CASE WHEN total_score >= 8 THEN 'S'
       WHEN total_score >= 6 THEN 'A'
       WHEN total_score >= 4 THEN 'B'
       ELSE                        'C' END AS grade  -- 総合グレード
FROM   totaled
ORDER BY total_score DESC, company_name;

/*
  実行順序（SQLの論理的な評価順）:
  1. CTE scores                       → CTE を定義（CASE で各スコアを評価）
  2. CTE totaled                      → CTE を定義（total_score を集計）
  3. 外側 SELECT FROM totaled         → CTE を読み込む
  4. SELECT                           → 列を評価（CASE で grade 判定）
  5. ORDER BY total_score DESC, company_name → 並び替えて出力
*/

解説（テーブル変化・ポイント） ▼

WITH
scores AS (
  SELECT
    company_name, revenue_bn, growth_pct, nps_score,
    CASE WHEN revenue_bn >= 30 THEN 3
         WHEN revenue_bn >= 10 THEN 2
         ELSE                    1 END AS revenue_score,
    CASE WHEN growth_pct >= 25  THEN 3
         WHEN growth_pct >= 10  THEN 2
         ELSE                    1 END AS growth_score,
    CASE WHEN nps_score >= 65   THEN 3
         WHEN nps_score >= 50   THEN 2
         ELSE                    1 END AS nps_pts
  FROM competitor_kpis
),
totaled AS (
  SELECT *, revenue_score + growth_score + nps_pts AS total_score
  FROM scores
)
SELECT
  company_name, revenue_score, growth_score, nps_pts, total_score,
  CASE WHEN total_score >= 8 THEN 'S'
       WHEN total_score >= 6 THEN 'A'
       WHEN total_score >= 4 THEN 'B'
       ELSE                   'C' END AS grade
FROM   totaled
ORDER BY total_score DESC, company_name;

LEGEND ▼

データ取得・読込対象

① FROM competitor_kpis — 6行読込 ▼

FROM competitor_kpiscompetitor_kpis の6行を読み込みます。revenue_bn・growth_pct・nps_score の3指標を CASE WHEN でスコア化していきます。

1 / 4

company_name	revenue_bn	growth_pct	nps_score
AlphaTech	42	18.5	72
BetaSoft	28	12.3	58
GammaSys	15	31.2	65
DeltaNet	5	8.1	48
EpsilonSys	3	-2.4	41
ZetaCloud	2	5.6	55

6行読込

学習ポイント

CTE の連鎖で計算の依存関係を明示する：WITH scores AS (...), totaled AS (... FROM scores) のように前段 CTE を後段 CTE が参照する連鎖構造では、「①スコア算出 → ②合計 → ③グレード判定」という変換段階が名前付きで可読化され、デバッグ時に CTE 単位でセレクトして確認できるメリットがあります。

CASE WHEN によるスコアリングで ORDER BY・集計が可能になる：定性的な条件（「成長率25%以上は優良」）を数値スコアに変換することで、ORDER BY total_score や AVG(total_score) といった定量的な操作が可能になります。このスコアリングは与信審査・CRM ランク付け・HR 評価など定性基準を定量化する際の標準パターンです。

CTE は定義順に参照できる（後段→前段は不可）：totaled が scores を参照できるのは scores が先に定義されているからです。後段 CTE から前段 CTE への相互参照はできません。複雑な依存関係はDAG状に整理してから CTE の順序を決定してください。

アンチパターン

CASE WHEN で ELSE を省略する：ELSE を書かないと、どの条件にも合致しない行は NULL になります。スコアリングで NULL が混入すると total_score = 3 + NULL + 2 = NULL となり集計が崩れます。スコアリングでは必ず ELSE に最低点を明示してください。

CTE を過度に深く連鎖させる（5段以上）：CTE の連鎖が5段以上になるとクエリプランナーの最適化が不安定になります。3〜4段を超える場合は一時テーブル・ビュー・アプリケーション側での段階処理を検討してください。PostgreSQL 12以降では CTE を MATERIALIZED/NOT MATERIALIZED で明示指定できます。

実務コラム：スコアリングモデルの実務応用

CASE WHEN スコアリング + CTE の組み合わせは「定性条件を定量化して一括評価する」ユースケースに広く使われます。典型的な応用例：競合分析では本問のように複数 KPI から総合グレードを自動付与、与信スコアリングでは財務指標・取引履歴から貸倒リスクを A〜D 評価、CRM リード評価では企業規模・業種・行動履歴から商談優先度スコアを算出、サプライヤー評価では納期遵守率・品質・コストから調達リスクを格付け。閾値（30→10→else など）をパラメータテーブル化しておくと、スコアリング基準を SQL 変更なしに更新できる保守性の高い設計になります。

QUESTION 9

CASE WHEN ピボット — 縦持ち四半期売上を横持ちに変換し年内成長率を算出する

SUM + CASE WHENGROUP BY縦持ち→横持ち変換条件付き集計・NULLIF

前提知識 ▼

条件付き集計（Conditional Aggregation）は SUM(CASE WHEN ... END) パターンで縦持ち（行方向）データを横持ち（列方向）に変換します。PostgreSQL には専用の PIVOT 構文がないため、このパターンが標準的な実装です。

SELECT
  company_name,
  SUM(CASE WHEN quarter = 'Q1' THEN revenue ELSE 0 END) AS q1_rev,
  SUM(CASE WHEN quarter = 'Q2' THEN revenue ELSE 0 END) AS q2_rev
FROM   quarterly_sales
GROUP BY company_name;

ELSE 0 vs ELSE NULL の違い：ELSE 0 は対象外の行を 0 として合計します。ELSE NULL にすると SUM は NULL を無視するため結果は同じですが、AVG や COUNT に影響が出ます。ピボット集計では慣習的に ELSE 0 が使われます。

問題 ▼

3社の2024年四半期売上から、四半期別売上（第1〜第4四半期）を横持ちに変換し、年間合計（annual_total）と第1四半期→第4四半期の年内成長率（q4_vs_q1_pct、小数第1位）を算出してください。取得列は company_name, q1_rev, q2_rev, q3_rev, q4_rev, annual_total, q4_vs_q1_pct、annual_total 降順で返してください。

使用テーブル

▸ quarterly_sales

company_name	year	quarter	revenue
AlphaTech	2024	Q1	340
AlphaTech	2024	Q2	410
AlphaTech	2024	Q3	320
AlphaTech	2024	Q4	450
BetaSoft	2024	Q1	260
BetaSoft	2024	Q2	240
BetaSoft	2024	Q3	310
BetaSoft	2024	Q4	340
GammaSys	2024	Q1	130
GammaSys	2024	Q2	165
GammaSys	2024	Q3	185
GammaSys	2024	Q4	170

※ revenue 単位: 億円

期待出力 ▼

company_name	q1_rev	q2_rev	q3_rev	q4_rev	annual_total	q4_vs_q1_pct
AlphaTech	340	410	320	450	1520	32.4
BetaSoft	260	240	310	340	1150	30.8
GammaSys	130	165	185	170	650	30.8

模範解答コード ▼

SELECT
  company_name,
  SUM(CASE WHEN quarter = 'Q1' THEN revenue ELSE 0 END) AS q1_rev,  -- Q1以外は0
  SUM(CASE WHEN quarter = 'Q2' THEN revenue ELSE 0 END) AS q2_rev,
  SUM(CASE WHEN quarter = 'Q3' THEN revenue ELSE 0 END) AS q3_rev,
  SUM(CASE WHEN quarter = 'Q4' THEN revenue ELSE 0 END) AS q4_rev,
  SUM(revenue)                                               AS annual_total,  -- 全四半期合計
  ROUND(
    (SUM(CASE WHEN quarter = 'Q4' THEN revenue ELSE 0 END) -
     SUM(CASE WHEN quarter = 'Q1' THEN revenue ELSE 0 END)) * 100.0
    / NULLIF(
        SUM(CASE WHEN quarter = 'Q1' THEN revenue ELSE 0 END), 0
      ),
    1
  )                                                          AS q4_vs_q1_pct  -- Q1→Q4成長率
FROM   quarterly_sales
WHERE  year = 2024                                                          -- 2024年のみ
GROUP BY company_name
ORDER BY annual_total DESC;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM quarterly_sales   → 行を読み込む
  2. WHERE year が 2024      → 行を絞り込む
  3. GROUP BY company_name  → グループ化
  4. SELECT                 → 集計関数を評価・整形（SUM, NULLIF, ROUND）
  5. ORDER BY annual_total DESC  → 並び替えて出力
  */

解説（テーブル変化・ポイント） ▼

SELECT
  company_name,
  SUM(CASE WHEN quarter = 'Q1' THEN revenue ELSE 0 END) AS q1_rev,
  SUM(CASE WHEN quarter = 'Q2' THEN revenue ELSE 0 END) AS q2_rev,
  SUM(CASE WHEN quarter = 'Q3' THEN revenue ELSE 0 END) AS q3_rev,
  SUM(CASE WHEN quarter = 'Q4' THEN revenue ELSE 0 END) AS q4_rev,
  SUM(revenue) AS annual_total,
  ROUND(
    (SUM(CASE WHEN quarter='Q4' THEN revenue ELSE 0 END)
     - SUM(CASE WHEN quarter='Q1' THEN revenue ELSE 0 END)) * 100.0
    / NULLIF(SUM(CASE WHEN quarter='Q1' THEN revenue ELSE 0 END), 0), 1
  ) AS q4_vs_q1_pct
FROM   quarterly_sales
WHERE  year = 2024
GROUP BY company_name
ORDER BY annual_total DESC;

LEGEND ▼

データ取得・読込対象

① FROM quarterly_sales WHERE year=2024 — 12行読込 ▼

FROM quarterly_sales WHERE year = 2024quarterly_sales から year=2024 の12行（3社×4四半期）を読み込みます。この縦持ちデータに対して、まず各行で CASE WHEN による四半期の判定を行います。

1 / 4

company_name	quarter	revenue
AlphaTech	Q1	340
AlphaTech	Q2	410
AlphaTech	Q3	320
AlphaTech	Q4	450
BetaSoft	Q1	260
BetaSoft	Q2	240
BetaSoft	Q3	310
BetaSoft	Q4	340
GammaSys	Q1	130
GammaSys	Q2	165
GammaSys	Q3	185
GammaSys	Q4	170

12行（2024年、3社×4四半期）

学習ポイント

条件付き集計の本質 — 「フィルタ付き SUM」：SUM(CASE WHEN quarter='Q1' THEN revenue ELSE 0 END) は「第1四半期の行だけ revenue を足し、他は 0 を足す」という処理です。結果的に第1四半期の revenue だけが集計されます。GROUP BY と組み合わせることで、各グループ内で条件別集計を同時に行うのがこのパターンの核心です。

ELSE 0 vs ELSE NULL の集計への影響：SUM で使う場合、ELSE 0 と ELSE NULL はほぼ同じ結果になります（SUM は NULL を無視するため）。ただし COUNT(CASE WHEN ... END) では ELSE 0 の場合もカウントされてしまいます。ピボット集計では ELSE 0 が慣習で、AVG/COUNT を使う場合は ELSE NULL の方が意図に沿うケースが多いです。

PostgreSQL に PIVOT 構文はない — 動的ピボットは crosstab()：SQL Server・Oracle には PIVOT 構文がありますが PostgreSQL には標準実装がありません。カラム数が動的（四半期数が変動など）な場合は tablefunc 拡張の crosstab() や、アプリ側での後処理が現実的です。固定カラム数なら本問のパターンが最も可読性が高いです。

アンチパターン

MAX(CASE WHEN...) を誤って使う：SUM ではなく MAX を使う実装も見かけますが、MAX(CASE WHEN quarter='Q1' THEN revenue ELSE NULL END) は「1社に複数のQ1行がある場合の最大値」を返します。データが1企業1四半期1行なら同じ結果になりますが、重複行がある場合は SUM と MAX で結果が異なります。意図を明確にするために SUM を使いましょう。

カラム数が増えると保守性が下がる：月次に変えると12列分 CASE WHEN を書く必要があり、保守コストが増大します。カラム数が多くなる場合は crosstab() への移行や、アプリケーション側でのピボット変換（pandas の pivot_table 等）を検討してください。

実務コラム：横持ちデータの活用 — BI ツールとの親和性

「全社×全期間」の横持ちマトリクスは、Tableau・Looker・Power BI などの BI ツールへの入力データとして最適な形式です。BI ツールは縦持ちから自動的にピボットする機能も持っていますが、事前に横持ちに変換してビューやテーブルとして保存しておくことで、BI 側のクエリを単純化し、ダッシュボードのレスポンスを改善できます。また Pandas の df.pivot_table() や Excel へのエクスポートとも相性がよく、「集計→エクスポート→レポート配布」の自動化パイプラインでも頻用されます。さらに q4_vs_q1_pct のような派生指標を SQL で事前計算しておくことで、BI ツール側での計算フィールド定義を不要にし、指標の一元管理が実現できます。

QUESTION 10

PERCENT_RANK() + LAG() + 2段 CTE — 競合の相対ポジション変化を年次追跡する

PERCENT_RANKLAG + 2段CTE相対ポジション変化パーセンタイル追跡

前提知識 ▼

PERCENT_RANK() は各行の相対的な順位をパーセンタイル（0.0〜1.0）で返します。計算式は (rank - 1) / (total_rows - 1) です。

計算式	5行の例（ORDER BY revenue ASC）
`(rank-1) / (5-1)`	最小売上=0.0 / 中位=0.5 / 最大売上=1.0

PERCENT_RANK() OVER (
  PARTITION BY year             -- 年度ごとに独立した順位空間
  ORDER BY     revenue_bn       -- 小さい順→0.0、大きい順→1.0
) * 100                         -- 百分率（0〜100）に変換

2段 CTE による依存処理：「PERCENT_RANK を計算」→「前年の PERCENT_RANK を LAG で取得」という2ステップは依存関係があるため、1段目 CTE で pct_rank を確定させ、2段目 CTE で LAG を適用する構造になります。

問題 ▼

5社の2年分の売上データから、年度内での相対売上ポジション（pct_rank、小数第1位・百分率）と前年比ポジション変化（pct_rank_change）を算出してください。取得列は company_name, year, revenue_bn, pct_rank, prev_pct_rank, pct_rank_change、year 昇順 → pct_rank 降順で返してください。

使用テーブル

▸ annual_performance

company_name	year	revenue_bn
AlphaTech	2022	35.0
BetaSoft	2022	26.0
GammaSys	2022	18.0
DeltaNet	2022	12.0
EpsilonSys	2022	7.0
AlphaTech	2023	42.0
BetaSoft	2023	28.0
GammaSys	2023	15.0
DeltaNet	2023	16.0
EpsilonSys	2023	9.0

期待出力 ▼

company_name	year	revenue_bn	pct_rank	prev_pct_rank	pct_rank_change
AlphaTech	2022	35.0	100.0	NULL	NULL
BetaSoft	2022	26.0	75.0	NULL	NULL
GammaSys	2022	18.0	50.0	NULL	NULL
DeltaNet	2022	12.0	25.0	NULL	NULL
EpsilonSys	2022	7.0	0.0	NULL	NULL
AlphaTech	2023	42.0	100.0	100.0	0.0
BetaSoft	2023	28.0	75.0	75.0	0.0
DeltaNet	2023	16.0	50.0	25.0	25.0
GammaSys	2023	15.0	25.0	50.0	-25.0
EpsilonSys	2023	9.0	0.0	0.0	0.0

模範解答コード ▼

WITH
yearly_ranks AS (
  SELECT
    company_name,
    year,
    revenue_bn,
    ROUND(
      (PERCENT_RANK() OVER (
        PARTITION BY year         -- 年度ごとに独立した順位空間
        ORDER BY     revenue_bn   -- 小さい順=0.0, 大きい順=1.0
      ) * 100)::numeric,
      1
    ) AS pct_rank
  FROM annual_performance
),
with_lag AS (
  SELECT
    company_name,
    year,
    revenue_bn,
    pct_rank,
    LAG(pct_rank) OVER (
      PARTITION BY company_name  -- 企業ごとに時系列を独立させる
      ORDER BY     year          -- 年度順に前行（前年）を取得
    ) AS prev_pct_rank
  FROM yearly_ranks
)
SELECT
  company_name,
  year,
  revenue_bn,
  pct_rank,
  prev_pct_rank,
  ROUND(pct_rank - prev_pct_rank, 1) AS pct_rank_change  -- NULLの演算→NULL
FROM   with_lag
ORDER BY year, pct_rank DESC;

/*
  実行順序（SQLの論理的な評価順）:
  1. CTE yearly_ranks            → CTE を定義（PERCENT_RANK ウィンドウ関数を評価）
  2. CTE with_lag                → CTE を定義（LAG ウィンドウ関数を評価）
  3. SELECT                      → 列を評価・整形（ROUND）
  4. ORDER BY year, pct_rank DESC     → 並び替えて出力
*/

解説（テーブル変化・ポイント） ▼

WITH
yearly_ranks AS (
  SELECT
    company_name, year, revenue_bn,
    ROUND(
      (PERCENT_RANK() OVER (
        PARTITION BY year
        ORDER BY     revenue_bn
      ) * 100)::numeric, 1
    ) AS pct_rank
  FROM annual_performance
),
with_lag AS (
  SELECT
    company_name, year, revenue_bn, pct_rank,
    LAG(pct_rank) OVER (
      PARTITION BY company_name
      ORDER BY     year
    ) AS prev_pct_rank
  FROM yearly_ranks
)
SELECT
  company_name, year, revenue_bn,
  pct_rank, prev_pct_rank,
  ROUND(pct_rank - prev_pct_rank, 1) AS pct_rank_change
FROM   with_lag
ORDER BY year, pct_rank DESC;

LEGEND ▼

データ取得・読込対象

① FROM annual_performance — 10行読込 ▼

FROM annual_performanceannual_performance の10行（5社×2年）を読み込みます。2022→2023年でGammaSysの revenue が18.0→15.0に減少し、DeltaNetが12.0→16.0に増加しています。

1 / 4

company_name	year	revenue_bn
AlphaTech	2022	35
BetaSoft	2022	26
GammaSys	2022	18
DeltaNet	2022	12
EpsilonSys	2022	7
AlphaTech	2023	42
BetaSoft	2023	28
GammaSys	2023	15
DeltaNet	2023	16
EpsilonSys	2023	9

10行読込（5社×2年）

学習ポイント

PERCENT_RANK() の計算式と解釈：PERCENT_RANK = (rank - 1) / (total_rows - 1)。5社なら最小売上=0.0、最大売上=1.0（ORDER BY ASC の場合）。×100 で百分率に変換し「50.0% = 業界中位」として解釈できます。絶対的な売上金額ではなく、業界内での相対ポジションを表す指標のため、市場全体が成長しても自社のポジションが変化しないケースも視覚化できます。

2段 CTE の必要性 — 1パスでは PERCENT_RANK と LAG を同時に使えない：PERCENT_RANK と LAG を同一 SELECT で組み合わせようとすると、PERCENT_RANK の結果が確定する前に LAG が参照しようとするため論理エラーになります。1段目 CTE で pct_rank を確定させ、2段目 CTE で LAG を適用するという依存関係を明示した構造が必要です。

LAG の PARTITION BY は企業ごとの時系列を守る：PARTITION BY company_name により、各企業の年次データが独立したウィンドウで処理されます。これがないと「企業Aの2023年」の前行が「企業Bの2022年」になり、まったく別企業のポジションが prev_pct_rank として取られてしまいます。複数エンティティの時系列比較では PARTITION BY の設定が最重要です。

アンチパターン

PERCENT_RANK と LAG を1段の CTE で同時に書こうとする：SELECT ..., PERCENT_RANK() OVER(...) AS p, LAG(PERCENT_RANK() OVER(...)) OVER(...) は PostgreSQL ではエラーになります。ウィンドウ関数のネスト（別のウィンドウ関数を LAG の引数に入れること）は許可されていません。必ず CTE を2段に分けて、1段目で pct_rank を確定させてください。

PERCENT_RANK と CUME_DIST を混同する：CUME_DIST() の計算式は (rank) / total_rows で、常に最小値 > 0 かつ最大値 = 1.0 になります。PERCENT_RANK は最小値 = 0.0 になります。「業界最下位が0%」を表したい場合は PERCENT_RANK、「自分より低い企業が何%いるか」（累積分布）を表したい場合は CUME_DIST を使い分けてください。

実務コラム：パーセンタイルによる競合ポジショニングの実務応用

絶対値の売上金額だけでは「業界全体が伸びている中で自社の相対ポジションが落ちている」ことに気づきにくいですが、PERCENT_RANK を使えば市場内での相対的な立ち位置の変化を明示できます。本問の DeltaNet のように「売上は12.0→16.0億円と増加しているが、業界内では3位→2位に上昇した（pct_rank_change=+25.0）」という分析が一目でわかります。実務では四半期ごとのパーセンタイル追跡を折れ線グラフで可視化し、自社が業界内でどのトレンドに乗っているかを経営陣へのレポートに組み込む手法がよく使われます。また、PERCENT_RANK() OVER (PARTITION BY year, category ORDER BY revenue) のようにカテゴリ×年度の2軸で分割することで、製品ライン別のポジション変化も同じパターンで分析できます。