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

このファイルには、ページ上のすべての数値を計算した実際のSQLを、接続情報
（ホスト名・ポート・データベース名・ユーザー名・パスワード）を除去したうえで
そのまま掲載しています。集計対象テーブルは PostgreSQL 上の
prediction_log（締切前に自動記録した標準AI予想）と
prediction_reconciliation（結果確定後の的中判定）、および
race_results（確定結果）です。すべて読み取り専用（SELECT）のクエリで、
書き込みは一切行っていません。

母集団の定義（全指標に共通）:
  void_race = false AND late_snapshot = false
    void_race     = 結果確定から2日経っても着順が確認できず、
                     中止・返還等とみなして終端した行
    late_snapshot = 予想の記録時刻(snapshot_at)が、そのレースの
                     投票締切時刻(race_closed_at)より後だった行

指標の定義:
  hit_1st     = 本命(予想1位)艇が実際の着順で1着だった
  hit_top2    = 本命艇が実際の着順で2着以内だった
  hit_top3    = 本命艇が実際の着順で3着以内だった（統計上の呼称。
                舟券の「複勝」の的中条件そのものではない）
  hit_trifecta = 単勝確率が高い順に3艇を並べた1-2-3着の組み合わせ
                （1点）が、実際の着順3艇と完全一致した

--------------------------------------------------------------------
1) 累計 / 直近30日 / 直近7日 の集計（current_result.json の all_time /
   last_30d / last_7d、および reproduction_result.json の
   repro_* に対応）
--------------------------------------------------------------------

-- 汎用テンプレート。{WHERE_EXTRA} には期間条件を差し込む。
SELECT
  COUNT(*) AS n,
  MIN(race_date) AS date_from,
  MAX(race_date) AS date_to,
  AVG(CASE WHEN hit_1st THEN 1.0 ELSE 0.0 END) AS hit_1st,
  AVG(CASE WHEN hit_top2 THEN 1.0 ELSE 0.0 END) AS hit_top2,
  AVG(CASE WHEN hit_top3 THEN 1.0 ELSE 0.0 END) AS hit_top3,
  AVG(CASE WHEN hit_trifecta THEN 1.0 ELSE 0.0 END) AS hit_trifecta
FROM prediction_reconciliation
WHERE void_race = false AND late_snapshot = false
  {WHERE_EXTRA};
-- 得られた hit_* の割合(0-1)を100倍し、小数点2桁に丸めたものが
-- ページ上の「%」表示。

  current_result.json の "all_time" を再現する {WHERE_EXTRA}:
    (追加条件なし。ただし実行日の CURRENT_DATE が 2026-08-14 で、
     race_date の実データが 2026-08-13 までしか無い状態での実行結果。)

  current_result.json の "last_30d" を再現する {WHERE_EXTRA}:
    AND race_date >= DATE '2026-08-13' - INTERVAL '30 days'

  current_result.json の "last_7d" を再現する {WHERE_EXTRA}:
    AND race_date >= DATE '2026-08-13' - INTERVAL '7 days'

  reproduction_result.json の "repro_all_time_fixed_le_0812" を
  再現する {WHERE_EXTRA}（基準日2026-08-12固定の再現試験）:
    AND race_date <= '2026-08-12'

  reproduction_result.json の "repro_last_30d_fixed" を再現する
  {WHERE_EXTRA}:
    AND race_date >= DATE '2026-08-13' - INTERVAL '30 days'
    AND race_date <= '2026-08-12'

  reproduction_result.json の "repro_last_7d_fixed" を再現する
  {WHERE_EXTRA}:
    AND race_date >= DATE '2026-08-13' - INTERVAL '7 days'
    AND race_date <= '2026-08-12'

--------------------------------------------------------------------
2) 除外件数（void_race / late_snapshot / 未突合）
--------------------------------------------------------------------

SELECT COUNT(*) FROM prediction_reconciliation WHERE void_race = true;
SELECT COUNT(*) FROM prediction_reconciliation WHERE late_snapshot = true;

-- 未突合(まだ結果照合バッチが処理していない行)
SELECT COUNT(*)
FROM prediction_log pl
LEFT JOIN prediction_reconciliation pr
  ON pr.race_date = pl.race_date
 AND pr.stadium_id = pl.stadium_id
 AND pr.race_number = pl.race_number
WHERE pr.id IS NULL
  AND pl.race_date < CURRENT_DATE;   -- 固定再現時は '2026-08-13' を使用

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

SELECT
  TO_CHAR(race_date, 'IYYY-"W"IW') AS iso_week,
  MIN(race_date), MAX(race_date), COUNT(*),
  ROUND((100.0 * AVG(CASE WHEN hit_1st THEN 1.0 ELSE 0.0 END))::numeric, 2),
  ROUND((100.0 * AVG(CASE WHEN hit_top2 THEN 1.0 ELSE 0.0 END))::numeric, 2),
  ROUND((100.0 * AVG(CASE WHEN hit_top3 THEN 1.0 ELSE 0.0 END))::numeric, 2),
  ROUND((100.0 * AVG(CASE WHEN hit_trifecta THEN 1.0 ELSE 0.0 END))::numeric, 2)
FROM prediction_reconciliation
WHERE void_race = false AND late_snapshot = false
GROUP BY iso_week
ORDER BY iso_week;
-- LIMIT句は意図的に付けていません(将来週が増えても打ち切られない設計)。

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

SELECT
  TO_CHAR(race_date, 'YYYY-MM') AS ym,
  MIN(race_date), MAX(race_date), COUNT(*),
  ROUND((100.0 * AVG(CASE WHEN hit_1st THEN 1.0 ELSE 0.0 END))::numeric, 2),
  ROUND((100.0 * AVG(CASE WHEN hit_top2 THEN 1.0 ELSE 0.0 END))::numeric, 2),
  ROUND((100.0 * AVG(CASE WHEN hit_top3 THEN 1.0 ELSE 0.0 END))::numeric, 2),
  ROUND((100.0 * AVG(CASE WHEN hit_trifecta THEN 1.0 ELSE 0.0 END))::numeric, 2)
FROM prediction_reconciliation
WHERE void_race = false AND late_snapshot = false
GROUP BY ym
ORDER BY ym;

--------------------------------------------------------------------
5) 較正帯（直近30日） ── current_result.json.calibration_30d /
   reproduction_result.json.calibration_repro_fixed_asof_0813
--------------------------------------------------------------------

SELECT
  CASE WHEN pl.top1_prob < 0.20 THEN '0-20%'
       WHEN pl.top1_prob < 0.40 THEN '20-40%'
       WHEN pl.top1_prob < 0.60 THEN '40-60%'
       WHEN pl.top1_prob < 0.80 THEN '60-80%'
       ELSE '80-100%' END AS band,
  COUNT(*) AS n,
  ROUND((100.0 * AVG(CASE WHEN pr.hit_1st THEN 1.0 ELSE 0.0 END))::numeric, 1) AS actual_pct,
  ROUND((100.0 * AVG(pl.top1_prob))::numeric, 1) AS predicted_pct
FROM prediction_log pl
JOIN prediction_reconciliation pr
  ON pr.race_date = pl.race_date
 AND pr.stadium_id = pl.stadium_id
 AND pr.race_number = pl.race_number
WHERE pr.void_race = false AND pr.late_snapshot = false
  AND {DATE_COND}
GROUP BY band
ORDER BY band;

  current_result.json の calibration_30d を再現する {DATE_COND}:
    pl.race_date >= DATE '2026-08-13' - INTERVAL '30 days'

  reproduction_result.json の calibration_repro_fixed_asof_0813 を
  再現する {DATE_COND}:
    pl.race_date >= DATE '2026-08-13' - INTERVAL '30 days'
    AND pl.race_date <= '2026-08-12'

--------------------------------------------------------------------
6) 未記録レースの件数と日別内訳 ── coverage_daily.json
--------------------------------------------------------------------

-- 対象期間中に実際に行われ結果が確定したレースの総数
SELECT COUNT(DISTINCT (race_date, stadium_id, race_number))
FROM race_results
WHERE race_date BETWEEN '2026-07-12' AND '2026-08-13';
-- => race_results_distinct = 5192

-- うち、標準AI予想(prediction_log)が記録されていないレース数
SELECT COUNT(*) FROM (
  SELECT DISTINCT race_date, stadium_id, race_number
  FROM race_results
  WHERE race_date BETWEEN '2026-07-12' AND '2026-08-13'
  EXCEPT
  SELECT DISTINCT race_date, stadium_id, race_number
  FROM prediction_log
  WHERE race_date BETWEEN '2026-07-12' AND '2026-08-13'
) x;
-- => missing_count = 400

-- 日別の内訳
SELECT race_date, COUNT(*) FROM (
  SELECT DISTINCT race_date, stadium_id, race_number
  FROM race_results
  WHERE race_date BETWEEN '2026-07-12' AND '2026-08-13'
  EXCEPT
  SELECT DISTINCT race_date, stadium_id, race_number
  FROM prediction_log
  WHERE race_date BETWEEN '2026-07-12' AND '2026-08-13'
) x
GROUP BY race_date
ORDER BY race_date;
-- => 07-12:177, 07-13:71, 07-14:60, 07-15:78, 07-16:14 (それ以外の日は0件、合計400件)

注記: この差集合は「race_results（確定結果があるレース）」を基準にした
DISTINCT(race_date, stadium_id, race_number) の集合差です。単純な
COUNT(DISTINCT ...)同士の引き算（race_results件数 - prediction_log件数）
とは一致しません（prediction_logには、この期間のrace_resultsと1対1に
対応しない行が別途含まれるため）。coverage_daily.json の数値は必ず
この EXCEPT クエリで算出したものを使用しています。