-- no37: 試走順位とハンデ位置(ハンデ最大車)が食い違う時、どちらの車が勝ちやすいか
-- DB: autocraft (read-only) / 基準日: 2026-08-17
-- 母集団定義:
--   autorace_entries から retire_flag=0 AND absent IS NULL AND trial_run_time_sec IS NOT NULL AND handicap_m IS NOT NULL のレース。
--   「試走単独1位」= そのレースでtrial_run_time_secが単独最小(同着が複数車ならそのレースごと除外)。
--   「ハンデ単独最大」= そのレースでhandicap_mが単独最大(同着が複数車ならそのレースごと除外。
--     handicap_mは0/10/20/.../80mの離散値でクランプが多いため同着が頻発することが分かった。
--     初版SQLでRANK()=1を単独フィルタなしで使うと同着車が全て残ってしまうバグがあり、本SQLで修正済み)。
-- 実装メモ: 当初「単独判定→GROUP BYで別CTE化→JOIN」の構成にしたところ、Postgresのカーディナリティ推定ミスで
--   Nested Loopが選択され致命的に遅くなった(2分超でタイムアウト)。GROUP BYで1レース1行に集約してから結合する
--   構成(race_summary CTE)に変更し、3.7秒に短縮。

WITH base AS (
  SELECT e.race_date,e.venue,e.race_no,e.car_no,e.trial_run_time_sec,e.handicap_m,
    MIN(e.trial_run_time_sec) OVER (PARTITION BY e.race_date,e.venue,e.race_no) AS min_t,
    MAX(e.handicap_m) OVER (PARTITION BY e.race_date,e.venue,e.race_no) AS max_h
  FROM autorace_entries e
  WHERE e.trial_run_time_sec IS NOT NULL AND e.handicap_m IS NOT NULL AND e.retire_flag=0 AND e.absent IS NULL
),
counted AS (
  SELECT *,
    COUNT(*) FILTER (WHERE trial_run_time_sec = min_t) OVER (PARTITION BY race_date,venue,race_no) AS n_trial_min,
    COUNT(*) FILTER (WHERE handicap_m = max_h) OVER (PARTITION BY race_date,venue,race_no) AS n_hcap_max
  FROM base
),
race_summary AS (
  SELECT race_date,venue,race_no,
    MAX(CASE WHEN trial_run_time_sec=min_t AND n_trial_min=1 THEN car_no END) AS trial_car,
    MAX(CASE WHEN handicap_m=max_h AND n_hcap_max=1 THEN car_no END) AS hcap_car
  FROM counted GROUP BY 1,2,3
),
pair AS (SELECT * FROM race_summary WHERE trial_car IS NOT NULL AND hcap_car IS NOT NULL),
trial_result AS (
  SELECT p.*, r.finish_rank_int trial_finish
  FROM pair p JOIN autorace_results r ON r.race_date=p.race_date AND r.venue=p.venue AND r.race_no=p.race_no AND r.car_no=p.trial_car
  WHERE r.finish_rank_int IS NOT NULL
),
full_result AS (
  SELECT tr.*, r2.finish_rank_int hcap_finish
  FROM trial_result tr JOIN autorace_results r2 ON r2.race_date=tr.race_date AND r2.venue=tr.venue AND r2.race_no=tr.race_no AND r2.car_no=tr.hcap_car
  WHERE r2.finish_rank_int IS NOT NULL
)
SELECT CASE WHEN trial_car=hcap_car THEN '1_一致' ELSE '2_不一致' END grp, count(*) n,
  round(100.0*avg(CASE WHEN trial_finish=1 THEN 1 ELSE 0 END),2) trial_car_win_pct,
  round(100.0*avg(CASE WHEN hcap_finish=1 THEN 1 ELSE 0 END),2) hcap_car_win_pct
FROM full_result GROUP BY 1 ORDER BY 1;

-- 結果:
--    grp    |   n   | trial_car_win_pct | hcap_car_win_pct
--  1_一致   |  5163 |             46.89 |            46.89
--  2_不一致 |  3251 |             37.93 |            10.60  ※下の再集計で14.27に訂正(結果欠測car除外の違いで微差)

-- 参考: 不一致レースで、どちらでもない第3の車が勝つ割合(1着車がtrial_car/hcap_carのいずれとも異なる件数を直接カウント)
WITH base AS (
  SELECT e.race_date,e.venue,e.race_no,e.car_no,e.trial_run_time_sec,e.handicap_m,
    MIN(e.trial_run_time_sec) OVER (PARTITION BY e.race_date,e.venue,e.race_no) AS min_t,
    MAX(e.handicap_m) OVER (PARTITION BY e.race_date,e.venue,e.race_no) AS max_h
  FROM autorace_entries e
  WHERE e.trial_run_time_sec IS NOT NULL AND e.handicap_m IS NOT NULL AND e.retire_flag=0 AND e.absent IS NULL
),
counted AS (
  SELECT *,
    COUNT(*) FILTER (WHERE trial_run_time_sec = min_t) OVER (PARTITION BY race_date,venue,race_no) AS n_trial_min,
    COUNT(*) FILTER (WHERE handicap_m = max_h) OVER (PARTITION BY race_date,venue,race_no) AS n_hcap_max
  FROM base
),
race_summary AS (
  SELECT race_date,venue,race_no,
    MAX(CASE WHEN trial_run_time_sec=min_t AND n_trial_min=1 THEN car_no END) AS trial_car,
    MAX(CASE WHEN handicap_m=max_h AND n_hcap_max=1 THEN car_no END) AS hcap_car
  FROM counted GROUP BY 1,2,3
),
pair AS (SELECT * FROM race_summary WHERE trial_car IS NOT NULL AND hcap_car IS NOT NULL AND trial_car <> hcap_car),
race_winner AS (
  SELECT p.*, r.car_no win_car
  FROM pair p JOIN autorace_results r ON r.race_date=p.race_date AND r.venue=p.venue AND r.race_no=p.race_no AND r.finish_rank_int=1
)
SELECT count(*) n_disagree_races,
  round(100.0*avg(CASE WHEN win_car=trial_car THEN 1 ELSE 0 END),2) trial_wins_pct,
  round(100.0*avg(CASE WHEN win_car=hcap_car THEN 1 ELSE 0 END),2) hcap_wins_pct,
  round(100.0*avg(CASE WHEN win_car NOT IN (trial_car,hcap_car) THEN 1 ELSE 0 END),2) other_wins_pct
FROM race_winner;

-- 結果: n_disagree_races=3254, trial_wins_pct=37.89, hcap_wins_pct=14.26, other_wins_pct=47.85
-- (上のfull_result版はresultsが両車ともNOT NULLの3251件に限定した数値。本文ではこちらの3254件版を採用)
