| name | wca-stats-db |
| description | Use when writing SQL against local wca_developer_database MySQL dump, or refreshing it from WCA developer export. Covers snake_case schema, key tables, join idioms (sub_id=1/rank<900), value encodings (DNF=-1/DNS=-2/FMC/MBLD), records markers, refresh script. Triggers: "WCA SQL", "WCA dump", "WCA schema", "results 表", "competitions 表", "event_id", "person_id", "result_attempts", "regional_single_record", "sub_id", "更新 WCA dump", "刷新 WCA", "wca export", "wca-developer-database-dump", "update_database". |
WCA Statistics DB
本仓库本地的 WCA dump:MySQL 库名 wca_developer_database (datadir E:\mysql_data\wca_developer_database\),连接配置在 core/packages/stats-build/database.yml。写 SQL 前先看这个文件,凭对 WCA 公开 TSV 的肌肉记忆会踩坑。
从 WCA 官网刷新 dump
CWD core/,一键(下载 ~2GB + 解压 + drop & rebuild + 建索引):
pnpm --filter @cuberoot/stats-build exec tsx src/bin/update_database.ts
脚本 packages/stats-build/src/bin/update_database.ts,源 https://www.worldcubeassociation.org/wst/wca-developer-database-dump.zip。只导 REQUIRED_TABLES(见下),磁盘留 ~10GB,需要 mysql CLI 在 PATH。export timestamp 写进 metadata 表。
已解压过 .sql 不想再下:$env:WCA_DUMP_SQL_PATH = 'path/to/dump.sql' 跳过下载+解压。
dump 里这两张表本来就空,不是 bug:ranks_single / ranks_average —— WCA app 内部 materialized rankings,export 时只 ship schema 不 ship data。脚本 assertion 看 dump 有无 INSERT 决定是否报错,空表合法。stats-build 里所有 SQL 都直接 GROUP BY results 自己算 PB,不查这两表。
🔥 头号坑:results 表没有 value1..value5
WCA 公开 TSV / 老 Ruby 代码 / 训练数据里到处是 r.value1, r.value2, ..., r.value5。本 dump 把 5 次 attempt 拆出去了。要 attempt 值用:
result_attempts(长格式,推荐):(SELECT GROUP_CONCAT(ra.value ORDER BY ra.attempt_number) FROM result_attempts ra WHERE ra.result_id = r.id) AS atts,client 端 attsStr.split(',').map(Number)result_values(宽格式,本地有但不在 CI REQUIRED_TABLES,stats-build 里禁用)
写 r.value[1-5] 的 SQL = 必挂(本地 + CI 都挂)。
⚠️ 本地 dump ≠ CI 可用表
本地 dump 是 WCA 全量；CI (update_database.ts) 只导入 REQUIRED_TABLES 17 张：
championships, competitions, competition_delegates, continents,
countries, eligible_country_iso2s_for_championship,
events, formats, persons, preferred_formats,
ranks_single, ranks_average, result_attempts, results,
round_types, scrambles, users
scrambles（2026-06 加，给 /scramble/stats 打乱长度统计用）：列 event_id + scramble(text) + competition_id / round_type_id / group_id / is_extra / scramble_num。~0.5GB，全表扫即可。
本地能 join 到的 competition_events / rounds / rounds_* / championships_* 关联表 / posts / 任何 _metadata 表 → CI 没有。
写新 SQL（stats、bin 脚本、validate）前必须确认所用表在 REQUIRED_TABLES（core/packages/stats-build/src/core/database.ts）。要新增表就同时改 REQUIRED_TABLES，否则本地通过 / CI 红。
命名约定（最常见的坑）
- 所有列名 snake_case：
event_id / person_id / competition_id / country_id / start_date。不是 camelCase（WCA 公开 TSV 是 eventId）。
- 表名在 Windows MySQL 上大小写不敏感（
lower_case_table_names=1）：Results / results 都能查到同一张表。本仓库代码混用，写新代码统一用小写更稳。
- 反引号裹保留字：
\rank`` 必须 quote，否则语法错。
关键表速查
results（6.4M 行，主表）
id bigint 主键
person_id varchar(10) 选手 WCA ID（→ persons.wca_id，不是 persons.id）
person_name varchar(80) 冗余存的名字
country_id varchar(50) 选手当时国籍（→ countries.id）
competition_id varchar(32) 比赛 ID（→ competitions.id）
event_id varchar(6) 项目（→ events.id，如 '333' '333fm'）
round_type_id varchar(1) 轮次类型（→ round_types.id，如 '1' '2' 'f'）
format_id varchar(1) 赛制（→ formats.id，如 'a' 'm' '3'）
round_id int rounds 表外键
pos smallint 该轮排名
best int 单次最好成绩（特殊值见下）
average int 平均成绩（0 = 该轮无平均）
regional_single_record varchar(3) 'WR' / 'NR' / 'AsR' / 'AfR' / 'ER' / 'NAR' / 'OcR' / 'SAR' / NULL — 大洲精确到 6 个,没有通用 'CR'
regional_average_record varchar(3) 同上
⚠️ 没有 value1..value5 列！每次具体成绩在另两张表（见下）。
result_attempts（长格式，每次一行）/ result_values（宽格式 v1..v5）
两张都有，按需选：
- 聚合多个 attempt（DNF 率、avg of N 重算）→
result_attempts，JOIN result_attempts ra ON ra.result_id = r.id
- 想要
(v1,v2,v3,v4,v5) 平铺 → result_values，JOIN result_values rv ON rv.result_id = r.id
result_attempts.value 用 attempt_number ASC 还原顺序。
competitions（17k 行）
id varchar(32) 字符串 ID（如 'WC2003'）
name varchar(50)
start_date DATE ✅ 直接用，不要 CONCAT(year,month,day)
end_date DATE
country_id varchar(50) → countries.id
city_name varchar(50)
老 Ruby 代码用 CONCAT(c.year,'-',c.month,'-',c.day) —— 本 dump 没有 year/month/day 列，只有 start_date。
persons（283k 行，注意有重复）
id int 自增数字主键（很少用）
wca_id varchar(10) ✅ 真正的 WCA ID
name varchar(80) 带括号本地名（如 "Yiheng Wang (王艺衡)"）
country_id varchar(50)
sub_id tinyint 同一选手可能多行；canonical 行 sub_id=1
join 模板：
JOIN persons p ON p.wca_id = r.person_id AND p.sub_id = 1
忘了 sub_id = 1 会重复算（每个改过国籍的选手都有 ≥2 行）。
events（21 行）
id varchar(6) '333' '222' ... 'magic' 'mmagic' '333mbo' '333ft'
name varchar(54)
format varchar(10) 'time' / 'number'(FMC) / 'multi'(MBLD)
rank int 排序，**rank ≥ 900 = 已废除（magic/mmagic/333mbo/333ft）**
惯用过滤：现役项目 = rank < 900（17 个项目）。
round_types（11 行）
id varchar(1) 'h'/'0'(预赛) 'd'/'1'(首轮) 'e'/'2' 'g'/'3'(半决) 'b'(B Final) 'c'/'f'(决赛)
final tinyint 只有 'c' / 'f' = 1
rank int 排序
决赛：rt.final = 1 或 r.round_type_id IN ('c','f')。
formats（7 行）
'1' '2' '3' '5' 'a'(avg of 5) 'm'(mean of 3) 'h'(head-to-head)。expected_solve_count、trim_fastest_n / trim_slowest_n 决定 avg 计算。
countries / continents
JOIN countries co ON co.id = r.country_id
JOIN continents ct ON ct.id = co.continent_id
- 特殊国家：
XW = "Multiple Countries (World)"（接力等）
- 特殊大洲：
_Multiple Continents（前缀下划线）
iso2 可能 NULL；continents.record_name = 大洲记录代号（'AfR'/'AsR'/'ER'/'NAR'/'OcR'/'SAR'）
成绩值编码（best / average 的语义）
| 值 | 含义 |
|---|
>0 | 正常成绩。计时项目：厘秒（val/100.0 秒）；FMC 单次：步数（直接整数）；FMC mean：步数 × 100 |
0 | 该轮无成绩（如只比单次的轮没 average，或 DNS 之前的空槽） |
-1 | DNF |
-2 | DNS |
惯用过滤：「有效成绩」用 best > 0（自动排掉 0/DNF/DNS）。
多盲（333mbf / 333mbo）特殊编码：DDDTTTTTMM（new）或老格式。别自己解——用 core/solve_time.ts 的 SolveTime 类。
常用 SQL 片段
比赛日期
JOIN competitions c ON c.id = r.competition_id
WHERE c.start_date >= '2020-01-01'
选手 PB 流（每次刷 PB 一行）
WITH solves AS (
SELECT r.person_id, r.best, c.start_date AS d
FROM results r JOIN competitions c ON r.competition_id = c.id
WHERE r.event_id = ? AND r.best > 0
),
running AS (
SELECT person_id, best, d,
MIN(best) OVER (
PARTITION BY person_id ORDER BY d, best
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
) AS prev_min
FROM solves
)
SELECT person_id, best, d FROM running
WHERE prev_min IS NULL OR best < prev_min;
每个选手该项目的 PB（最简）
SELECT person_id, MIN(best) AS pb
FROM results
WHERE event_id = '333' AND best > 0
GROUP BY person_id;
当前 WR（截至今天的最好）
SELECT MIN(r.best) FROM results r
JOIN competitions c ON c.id = r.competition_id
WHERE r.event_id = ? AND r.best > 0 AND c.start_date <= CURDATE();
WR 沿革（历次破纪录）
直接走 regional_single_record = 'WR' / regional_average_record = 'WR'，已有标记，不用自己重算。
SELECT r.person_id, r.best, c.start_date
FROM results r JOIN competitions c ON c.id = r.competition_id
WHERE r.event_id = ? AND r.regional_single_record = 'WR'
ORDER BY c.start_date, r.best;
决赛
WHERE r.round_type_id IN ('c', 'f')
排除已废除项目
JOIN events e ON e.id = r.event_id AND e.rank < 900
常见错误 cheatsheet
| 错误写法 | 应该 |
|---|
r.eventId | r.event_id |
c.year/c.month/c.day 拼日期 | c.start_date（DATE 列） |
rank 不加反引号 | \rank`` |
JOIN persons p ON p.wca_id = r.person_id（漏 sub_id） | 加 AND p.sub_id = 1 |
| 想要 attempts 1..5 在 results 表里找 | 去 result_attempts 或 result_values |
r.best != -1（想排除 DNF） | r.best > 0（连 0/DNS 一起排） |
JOIN events（不过滤） | 加 AND e.rank < 900 排废除项目 |
排查 SQL 报错
凭据 / 端口都 OK 的情况下，typecheck 看不到 SQL 字符串里的错。改完 stat 的 query() 必须：
pnpm --filter @cuberoot/stats-build run validate-queries
会对全部 stat 跑 EXPLAIN，秒级捕获列名 / 表名拼错。详见 stats-build skill。
参考
