shangfangjian
ff0045ad93
P1-1 [context_builder] build_context 共享 session,切片函数传 db 参数,
回测 20 场并发连接需求从 100+ 降至每场 1 个
P1-2 [bzzoiro] 预加载改为按 raw_events 日期范围 ±30 天按需加载
P1-3 [understat] 批量查询球队 + 比赛,从 1140 次往返降到 3 次
P1-4 [injuries] 批量幂等检查 + 分批 flush,IntegrityError 逐条回退
P1-5 [predict] 删除 threading.Lock,dict 操作原子无需同步锁
P1-6 [models] 添加 (match_id, provider, model) 唯一约束 + 迁移
P2-1 [unit_of_work] get_uow 返回类型改为 AsyncIterator[AsyncSession]
P2-2 [normalize] _parse_date 失败时记录 warning 避免静默丢数据
P2-3 [repositories] find_by_teams_and_date 改用 match_date_date 等值匹配
P2-4 [migration] 幽灵列 cutoff_at 已在 0006 迁移删除(已有)
P2-5 [migration] injuries 约束命名对齐 ORM,UniqueConstraint → 唯一索引
60 lines
1.6 KiB
Python
60 lines
1.6 KiB
Python
"""为 predictions 表添加 match_id+provider+model 唯一约束
|
|
|
|
Revision ID: 0007_predictions_unique_constraint
|
|
Revises: 0007_injuries_constraint_naming_align
|
|
Create Date: 2026-09-16
|
|
|
|
背景(见代码审查报告 P1-6):
|
|
同一 match_id + provider + model 组合不应产生重复预测。
|
|
当前缺少数据库级唯一约束,回测多次运行或并发采集可能产生重复记录,
|
|
导致统计偏差。
|
|
|
|
先清理已存在的重复记录(保留最早创建的那条),再添加唯一约束。
|
|
"""
|
|
|
|
from typing import Sequence, Union
|
|
|
|
from alembic import op
|
|
import sqlalchemy as sa
|
|
|
|
# revision identifiers, used by Alembic.
|
|
revision: str = '0007_predictions_unique_constraint'
|
|
down_revision: Union[str, None] = '0007_injuries_constraint_naming_align'
|
|
branch_labels: Union[str, Sequence[str], None] = None
|
|
depends_on: Union[str, Sequence[str], None] = None
|
|
|
|
|
|
def upgrade() -> None:
|
|
# 1. 清理已存在的重复记录(保留 id 最小的)
|
|
op.execute(
|
|
"""
|
|
DELETE FROM predictions
|
|
WHERE id NOT IN (
|
|
SELECT MIN(id)
|
|
FROM predictions
|
|
GROUP BY match_id, provider, model
|
|
)
|
|
AND match_id IN (
|
|
SELECT match_id
|
|
FROM predictions
|
|
GROUP BY match_id, provider, model
|
|
HAVING COUNT(*) > 1
|
|
)
|
|
"""
|
|
)
|
|
|
|
# 2. 添加唯一约束
|
|
op.create_unique_constraint(
|
|
"uq_predictions_match_provider_model",
|
|
"predictions",
|
|
["match_id", "provider", "model"],
|
|
)
|
|
|
|
|
|
def downgrade() -> None:
|
|
op.drop_constraint(
|
|
"uq_predictions_match_provider_model",
|
|
"predictions",
|
|
type_="unique",
|
|
)
|