-- BOAT #23 「1号艇が実測1コースを外れた場合の勝率は？」
-- 実行日: 2026-08-25 / boatcraft DB / read-only (SET default_transaction_read_only=on)
-- 使用列の注記: race_results.entry_position = 実際のスタートコース(進入後の実測値)。
--   race_results.boat_number = 固定の枠番(1〜6)。両者が一致しない行(全体の約10.3%, 2021-)は
--   進入変更(枠番と実際のコースが異なるケース。理由はデータ上区別できない)を示すと解釈。
--   entry_position別の勝率が1コース55.74%→6コース1.89%と教科書的なコース別勝率と一致することを
--   別クエリで確認済み。
-- 母集団定義: race_results, boat_number=1, entry_position IS NOT NULL, arrival_order BETWEEN 1 AND 6,
--   race_date >= 2021-01-01

-- [1] 年次安定性チェック: 1号艇がentry_position=1のまま走れた割合(2019-2026)
SELECT date_part('year',race_date) yr,
  count(*) total,
  count(*) FILTER (WHERE entry_position=1) kept_course1,
  round(100.0*count(*) FILTER (WHERE entry_position=1)/count(*),2) AS pct_kept
FROM race_results
WHERE boat_number=1 AND entry_position IS NOT NULL AND arrival_order BETWEEN 1 AND 6
GROUP BY yr ORDER BY yr DESC LIMIT 8;

-- [2] 本体: 1コースのまま走れた1号艇 vs 実測1コースを外れた1号艇、成績比較(2021-01-01〜基準日)
SELECT
  CASE WHEN entry_position=1 THEN '1コースのまま' ELSE '実測1コースを外れた' END AS group_label,
  count(*) n,
  count(*) FILTER (WHERE arrival_order=1) wins,
  round(100.0*count(*) FILTER (WHERE arrival_order=1)/count(*),2) AS win_pct,
  count(*) FILTER (WHERE arrival_order<=2) top2,
  round(100.0*count(*) FILTER (WHERE arrival_order<=2)/count(*),2) AS top2_pct,
  count(*) FILTER (WHERE arrival_order<=3) top3,
  round(100.0*count(*) FILTER (WHERE arrival_order<=3)/count(*),2) AS top3_pct
FROM race_results
WHERE boat_number=1 AND entry_position IS NOT NULL AND arrival_order BETWEEN 1 AND 6 AND race_date >= '2021-01-01'
GROUP BY group_label;

-- [3] 押し出された先のコース別内訳(参考。件数が少ないコースは目安程度)
SELECT entry_position, count(*) n,
  count(*) FILTER (WHERE arrival_order=1) wins,
  round(100.0*count(*) FILTER (WHERE arrival_order=1)/count(*),2) AS win_pct
FROM race_results
WHERE boat_number=1 AND entry_position IS NOT NULL AND entry_position<>1 AND arrival_order BETWEEN 1 AND 6 AND race_date >= '2021-01-01'
GROUP BY entry_position ORDER BY entry_position;
