はじめに
オートレースの予想アプリを個人で作っています。オートレースにはレース前の「試走」があり、そのタイムの順位と 1 着の関係をよく数えます。
試走では同じタイムがよく並びます。最速のタイムが 2 台以上で並んだレースは、26,125 レースのうち 5,542(21%)ありました。この時、SQL の窓関数のどれで順位を付けるかで、集計の数がはっきり変わりました。この記事では、その違いと、確かめ方を書きます。
例のデータ
記事のための小さな表です(実際の表ではありません)。
CREATE TABLE trial (race_id int, car_no int, trial_time numeric(4,2), finish int);
INSERT INTO trial VALUES
(1, 1, 3.35, 2), (1, 2, 3.33, 1), (1, 3, 3.33, 3), (1, 4, 3.38, 4),
(2, 1, 3.40, 1), (2, 2, 3.36, 3), (2, 3, 3.37, 2), (2, 4, 3.41, 4);
レース 1 では、2 号車と 3 号車が 3.33 で並んでいます。
3 つの窓関数
SELECT race_id, car_no, trial_time,
RANK() OVER w AS rnk,
DENSE_RANK() OVER w AS dense_rnk,
ROW_NUMBER() OVER (PARTITION BY race_id ORDER BY trial_time, car_no) AS row_no
FROM trial
WINDOW w AS (PARTITION BY race_id ORDER BY trial_time);
| race_id | car_no | trial_time | rnk | dense_rnk | row_no |
|---|---|---|---|---|---|
| 1 | 2 | 3.33 | 1 | 1 | 1 |
| 1 | 3 | 3.33 | 1 | 1 | 2 |
| 1 | 1 | 3.35 | 3 | 2 | 3 |
| 1 | 4 | 3.38 | 4 | 3 | 4 |
-
RANK: 並んだ車は同じ順位。次の順位は飛ぶ(1, 1, 3)。 -
DENSE_RANK: 並んだ車は同じ順位。次の順位は飛ばない(1, 1, 2)。 -
ROW_NUMBER: 並んでもすべて別の順位。並びの決め方(ここでは車番)をORDER BYに足さないと、結果が実行ごとに変わることがあります。
「1 位の 1 着率」は数え方で変わる
2023 年 4 月〜2026 年 9 月の、試走タイムのある 26,125 レースで、試走 1 位の車の 1 着率を数えると、次のようになりました(当方の集計)。
| 数え方 | 1 位の車の数 | 1 位の車の 1 着 | 1 着率 |
|---|---|---|---|
RANK() = 1(並んだ車は全部 1 位) |
32,945 | 11,567 | 35.1% |
ROW_NUMBER() = 1(並んだら車番の小さい方) |
26,125 | 10,001 | 38.3% |
| 最速が 1 台だけのレースに絞る | 20,583 | 8,620 | 41.9% |
RANK では 1 位の車が 26,125 レースに 32,945 台いて、レースの数より 6,820 台多くなります。分母が増えるので、率は下がります。逆に、最速が 1 台だけのレースに絞ると、はっきり速い車だけが残るので、率は上がります。
どれが正しいというより、問いが違います。
- 「試走 1 位と呼ばれる車は、どのくらい勝つか」なら
RANK。 - 「1 レースに 1 台の本命を決めたら、どのくらい勝つか」なら
ROW_NUMBER(並んだ時の決め方を書く)。
確かめの式
順位ごとの 1 着の数を足すと、レースの数に戻るはずです。
WITH r AS (
SELECT race_id, finish, RANK() OVER (PARTITION BY race_id ORDER BY trial_time) AS rnk
FROM trial
), by_rank AS (
SELECT rnk, SUM(CASE WHEN finish = 1 THEN 1 ELSE 0 END) AS wins
FROM r
GROUP BY rnk
)
SELECT SUM(wins) AS wins_total,
(SELECT COUNT(DISTINCT race_id) FROM trial) AS races
FROM by_rank;
実際のデータでは、1 着の合計が 26,134 で、レースの数 26,125 より 9 多くなりました。差の 9 は、1 着が同着だったレースの数と一致しました。こうして数が「閉じる」かを見ておくと、取りこぼしや二重の数えに気づけます。
おわりに
同じタイムが多いデータでは、窓関数の選び方がそのまま結果に出ます。記事やアプリの注には、どの順位の付け方か(並んだ時の扱い)を書くようにしています。