KEIRINCRAFT 答え合わせページ ── 集計SQL全文（サニタイズ済み）
====================================================================

このファイルには、ページ上のすべての数値を計算した実際のSQLを、接続情報
（ホスト名・ポート・データベース名・ユーザー名・パスワード）を除去したうえで
そのまま掲載しています。集計対象テーブルは PostgreSQL 上の
keirin_prediction_log（締切前に自動記録した標準AI予想のスナップショット）と
keirin_prediction_reconciliation（結果確定後の的中判定）、
keirin_results（確定結果・車番ごとの着順）、keirin_races（レース基本情報・
開催日・締切時刻）です。すべて読み取り専用（SELECT）のクエリで、書き込みは
一切行っていません（2026-09-11、VPS上で sudo -u postgres psql -d keirincraft
を用いて実行）。

母集団の定義（全指標に共通）:
  void_race = false AND late_snapshot = false AND no_valid_snapshot = false
    void_race         = 結果確定から2日経っても着順が確認できず、
                         中止・返還等とみなして終端した行
                         （keirin_prediction_reconcile_daily.py: VOID_GRACE_DAYS = 2）
    late_snapshot      = 締切(close_at_used)以前のスナップショットが1つも
                         無く、記録の中で最も早い行を代用して参考値として
                         採点した行（本来「締切前記録」の前提と矛盾するため除外）
    no_valid_snapshot  = late_snapshot と同時に立つフラグ（同じ事象の別名）。
                         現在まで late_snapshot=true の行は0件（2026-09-11時点）。

指標の定義:
  hit_win      = 本命(予想1位=honmei_car)車が実際の着順で1着だった
                 （keirin_prediction_reconciliation.hit_win 列をそのまま使用）
  hit_in3      = 本命車が実際の着順で3着以内だった（統計上の呼称。
                 車券の「複勝」の的中条件そのものではない。
                 keirin_prediction_reconciliation.hit_in3 列をそのまま使用）
  nisha_fuku   = 予想確率上位2車（cars_jsonのrank=1とrank=2のcar_no）が、
                 順不同で実際の1着・2着の車番と完全に一致した
                 （2連複に相当する集計上の呼称。的中判定列は無いため、
                 本ページの集計のみで独自に算出）
  sanrentan    = 予想確率上位3車（cars_jsonのrank=1,2,3のcar_no）を、
                 確率の高い順に1-2-3着として並べた1点が、実際の着順3車と
                 完全に一致した（同上、独自算出）

採点対象スナップショットの選び方（keirin_prediction_reconcile_daily.py に
実装済み・本ページの集計もこれに従う）:
  締切(close_at_used)以前で最も新しいsnapshot_atの行を採点に使う
  （＝ keirin_prediction_log.snapshot_at = keirin_prediction_reconciliation
      .scored_snapshot_at で結合）

--------------------------------------------------------------------
0) 共通CTE（以下すべてのクエリの基礎となる定義）
--------------------------------------------------------------------

-- 予想確率上位1/2/3車の車番を cars_json (jsonb配列) から取り出す
WITH log_ranks AS (
  SELECT pl.race_id, pl.snapshot_at,
    MAX(CASE WHEN (elem->>'rank')::int=1 THEN (elem->>'car_no')::int END) AS pred_1,
    MAX(CASE WHEN (elem->>'rank')::int=2 THEN (elem->>'car_no')::int END) AS pred_2,
    MAX(CASE WHEN (elem->>'rank')::int=3 THEN (elem->>'car_no')::int END) AS pred_3
  FROM keirin_prediction_log pl, jsonb_array_elements(pl.cars_json) elem
  GROUP BY pl.race_id, pl.snapshot_at
),
-- クリーン母集団（除外条件を適用し、集計対象終了日 {AS_OF_DATE} 以前に限定）
pop AS (
  SELECT r.race_id, k.race_date, r.honmei_car, r.hit_win, r.hit_in3,
         r.finish_pos_of_honmei, lr.pred_1, lr.pred_2, lr.pred_3
  FROM keirin_prediction_reconciliation r
  JOIN keirin_races k ON k.race_id = r.race_id
  JOIN log_ranks lr ON lr.race_id = r.race_id AND lr.snapshot_at = r.scored_snapshot_at
  WHERE r.void_race = false AND r.late_snapshot = false AND r.no_valid_snapshot = false
    AND k.race_date <= DATE '{AS_OF_DATE}'
),
-- 予想上位1/2/3車それぞれの実際の着順を keirin_results から引く
scored AS (
  SELECT p.*,
    res1.finish_pos AS fp1, res2.finish_pos AS fp2, res3.finish_pos AS fp3
  FROM pop p
  LEFT JOIN keirin_results res1 ON res1.race_id = p.race_id AND res1.car_no = p.pred_1
  LEFT JOIN keirin_results res2 ON res2.race_id = p.race_id AND res2.car_no = p.pred_2
  LEFT JOIN keirin_results res3 ON res3.race_id = p.race_id AND res3.car_no = p.pred_3
)

{AS_OF_DATE} = '2026-09-10'（直近の突合完了日。2026-09-11は当日進行中のため
除外。keirin_racesとkeirin_prediction_log/reconciliationを日別に突き合わせ、
「その日のレース総数=予想記録数=突合済み件数」が揃う最後の日を機械的に確認した）

--------------------------------------------------------------------
1) 累計 / 直近30日 / 直近7日 の集計 ── current_result.json の
   all_time / last_30d / last_7d に対応
--------------------------------------------------------------------

<0)のCTEに続けて>
periods AS (
  SELECT 'all_time' AS period, race_date, hit_win, hit_in3, fp1, fp2, fp3 FROM scored
  UNION ALL
  SELECT 'last_30d', race_date, hit_win, hit_in3, fp1, fp2, fp3 FROM scored
    WHERE race_date >= DATE '2026-09-10' - INTERVAL '30 days'
  UNION ALL
  SELECT 'last_7d', race_date, hit_win, hit_in3, fp1, fp2, fp3 FROM scored
    WHERE race_date >= DATE '2026-09-10' - INTERVAL '7 days'
)
SELECT
  period, COUNT(*) AS n, MIN(race_date) AS date_from, MAX(race_date) AS date_to,
  ROUND((100.0*AVG(CASE WHEN hit_win=1 THEN 1.0 ELSE 0.0 END))::numeric,2) AS honmei_1st_pct,
  ROUND((100.0*AVG(CASE WHEN fp1 IN (1,2) AND fp2 IN (1,2) AND fp1<>fp2
                    THEN 1.0 ELSE 0.0 END))::numeric,2) AS nisha_fuku_pct,
  ROUND((100.0*AVG(CASE WHEN hit_in3=1 THEN 1.0 ELSE 0.0 END))::numeric,2) AS fukusho_pct,
  ROUND((100.0*AVG(CASE WHEN fp1=1 AND fp2=2 AND fp3=3
                    THEN 1.0 ELSE 0.0 END))::numeric,2) AS sanrentan_pct
FROM periods GROUP BY period ORDER BY period;

--------------------------------------------------------------------
2) 除外件数（void_race / late_snapshot / no_valid_snapshot）
--------------------------------------------------------------------

SELECT void_race, late_snapshot, no_valid_snapshot, COUNT(*)
FROM keirin_prediction_reconciliation
GROUP BY 1,2,3 ORDER BY 1,2,3;
-- => (f,f,f)=3154  (t,f,f)=4  （2026-09-11実行時点。全期間・as_of制限なし）

--------------------------------------------------------------------
3) 週次（全期間・LIMIT無し） ── weekly_all.json
--------------------------------------------------------------------

<0)のCTEに続けて（as_of制限込みのscoredをそのまま使用）>
SELECT
  TO_CHAR(race_date, 'IYYY-"W"IW') AS iso_week,
  MIN(race_date) AS date_from, MAX(race_date) AS date_to, COUNT(*) AS n,
  ROUND((100.0*AVG(CASE WHEN hit_win=1 THEN 1.0 ELSE 0.0 END))::numeric,2) AS honmei_1st_pct,
  ROUND((100.0*AVG(CASE WHEN fp1 IN (1,2) AND fp2 IN (1,2) AND fp1<>fp2
                    THEN 1.0 ELSE 0.0 END))::numeric,2) AS nisha_fuku_pct,
  ROUND((100.0*AVG(CASE WHEN hit_in3=1 THEN 1.0 ELSE 0.0 END))::numeric,2) AS fukusho_pct,
  ROUND((100.0*AVG(CASE WHEN fp1=1 AND fp2=2 AND fp3=3
                    THEN 1.0 ELSE 0.0 END))::numeric,2) AS sanrentan_pct
FROM scored GROUP BY iso_week ORDER BY iso_week;
-- LIMIT句は意図的に付けていません(将来週が増えても打ち切られない設計)。

--------------------------------------------------------------------
4) 月次（全期間） ── current_result.json.monthly
--------------------------------------------------------------------

<3)と同じscoredから、GROUP BY TO_CHAR(race_date,'YYYY-MM')>

--------------------------------------------------------------------
5) 較正帯（直近30日・本命車の予想確率 honmei_p による5分位）
   ── current_result.json.calibration_30d
--------------------------------------------------------------------

WITH pop30 AS (
  SELECT r.race_id, r.scored_snapshot_at, k.race_date, r.hit_win
  FROM keirin_prediction_reconciliation r
  JOIN keirin_races k ON k.race_id = r.race_id
  WHERE r.void_race=false AND r.late_snapshot=false AND r.no_valid_snapshot=false
    AND k.race_date <= DATE '2026-09-10'
    AND k.race_date >= DATE '2026-09-10' - INTERVAL '30 days'
),
joined AS (
  SELECT p.*, pl.honmei_p
  FROM pop30 p
  JOIN keirin_prediction_log pl
    ON pl.race_id = p.race_id AND pl.snapshot_at = p.scored_snapshot_at
)
SELECT
  CASE WHEN honmei_p < 0.20 THEN '0-20%'
       WHEN honmei_p < 0.40 THEN '20-40%'
       WHEN honmei_p < 0.60 THEN '40-60%'
       WHEN honmei_p < 0.80 THEN '60-80%'
       ELSE '80-100%' END AS band,
  COUNT(*) AS n,
  ROUND((100.0*AVG(CASE WHEN hit_win=1 THEN 1.0 ELSE 0.0 END))::numeric,1) AS actual_pct,
  ROUND((100.0*AVG(honmei_p))::numeric,1) AS predicted_pct
FROM joined GROUP BY band ORDER BY band;

--------------------------------------------------------------------
6) 車立て数の内訳（クリーン母集団・全期間） ── 「7車立て中心」の根拠
--------------------------------------------------------------------

SELECT k.num_cars, COUNT(*)
FROM keirin_prediction_reconciliation r
JOIN keirin_races k ON k.race_id = r.race_id
WHERE r.void_race=false AND r.late_snapshot=false AND r.no_valid_snapshot=false
  AND k.race_date <= DATE '2026-09-10'
GROUP BY 1 ORDER BY 1;
-- => 5車:16 / 6車:135 / 7車:2703(85.7%) / 8車:11 / 9車:289

--------------------------------------------------------------------
7) 未記録レースの件数と内訳（初期稼働＋モーニング競輪記録漏れ）
   ── coverage_daily.json
--------------------------------------------------------------------

-- 対象期間中（記録開始2026-07-30〜集計対象終了日2026-09-10）に、
-- keirin_racesに存在するレースのうち、keirin_prediction_logに
-- 1行も無い(=予想が一度も記録されなかった)レース数を日別・締切時刻別に集計
WITH pred_races AS (SELECT DISTINCT race_id FROM keirin_prediction_log)
SELECT k.race_date, k.venue, k.race_no, k.close_time
FROM keirin_races k
LEFT JOIN pred_races pl ON pl.race_id = k.race_id
WHERE k.race_date BETWEEN '2026-07-30' AND '2026-09-10'
  AND pl.race_id IS NULL
ORDER BY k.race_date, k.close_time;

-- 内訳の分類（人手で3区分に整理。分類根拠は同じクエリの出力そのもの）:
--  (a) 07-30(稼働初日): 66件。当日22:55にシステムが稼働開始したため、
--      それ以前の日中〜夜のレースは記録の仕組みがまだ動いていなかった。
--  (b) 07-31〜08-21(23日間): 73件。すべて各開催の1〜3R・締切8:25〜9:06
--      （通称「モーニング競輪」）に完全一致。race_timing_shadow.pyの
--      cron収集窓が早朝帯を捉えられていなかったことが原因（2026-08-21修正）。
--      この期間、モーニング競輪の対象レースは100%が記録漏れ（73/73)。
--  (c) 08-22〜09-10(20日間): 0件。モーニング競輪を含め欠測なし。
-- 合計 66+73+0 = 139件（対象期間の全レース3297件の4.2%）。
-- この139件は集計の分子・分母どちらにも含まれていません
-- （keirin_prediction_reconciliationに行が無いため、void_raceにもなり得ない）。

--------------------------------------------------------------------
8) DB権限の確認（改ざん耐性の根拠）
--------------------------------------------------------------------

\dp keirin_prediction_log
\dp keirin_prediction_reconciliation
-- => keirincraft=ar/postgres （a=INSERT, r=SELECT のみ。w=UPDATE, d=DELETE
--    権限は付与されていない。postgres管理者ロールを除く。2026-09-11確認）

注記: keirin_prediction_reconcile_daily.py のコメントに「一度書いた行は
書き換えない(INSERT ON CONFLICT DO NOTHING)」と明記されており、権限設定と
運用ロジックの両面で「結果確定後に予想記録を更新しない」設計になっています
（/opt/keirincraft/keirin_prediction_reconcile_daily.py、
  /opt/keirincraft/keirin_prediction_snapshot.py のdocstringより）。