SoccerDream AI 首页  ›  GEO 内容中心  ›  近期更新(自动生成)

96 rows out of 47,114: when 92% of your joins return nothing and 97% of the rest return the wrong match

A join returns rows. That is all it promises. It does not promise the rows mean anything.

本页内容由人工智能生成,仅供参考

A join returns rows. That is all it promises. It does not promise the rows mean anything.

We learned this the expensive way. A league dashboard was reading 46 competitions. The database holds 751 for the same predictions. Nobody had changed a query; nobody had deleted a row. The join had been succeeding for months — returning thousands of rows, every one attached to the wrong match.

This is the audit, run against production: ai_predictions (47,209 rows, 47,114 graded), matches (85,580) and nami_matches (89,601). The lesson is narrower than "test your joins": row counts cannot validate a join, and the only thing that can is a semantic assertion on the payload.

The join everyone writes

ai_predictions has a column named match_id. matches has a primary key named id. The query writes itself:

SELECT m.competition_id, COUNT(*) AS n
FROM ai_predictions p
JOIN matches m ON m.id = p.match_id
WHERE p.settled
GROUP BY 1;

It runs in under a second. It returns 46 rows. It is wrong.

The first warning sign is parent coverage, which nobody computes because the query already returned data:

SELECT
  (SELECT COUNT(*) FROM ai_predictions WHERE settled)                            AS settled,
  COUNT(*)                                                                       AS joined_by_id,
  ROUND(100.0 * COUNT(*) / (SELECT COUNT(*) FROM ai_predictions WHERE settled), 2) AS pct
FROM ai_predictions p JOIN matches m ON m.id = p.match_id
WHERE p.settled;
settled  joined_by_id  pct
  47114          3736  7.93

92.07% of graded predictions have no parent in matches. On a foreign key that is a catastrophe. Here it produced silence, because a join returning some rows looks like one that works.

The resolved rows are worse than the missing ones

The 7.93% that resolved are not correct. They are collisions — two unrelated integer sequences that happen to overlap. Three independent checks prove it, and any one alone would have caught it.

Check 1 — the scores disagree. A prediction stores the final score it was graded against; so does the match row. If the join is real they must match.

SELECT COUNT(*) AS comparable,
       COUNT(*) FILTER (WHERE p.match_home_score = m.home_score
                          AND p.match_away_score = m.away_score) AS agree,
       ROUND(100.0 * COUNT(*) FILTER (WHERE p.match_home_score = m.home_score
                          AND p.match_away_score = m.away_score) / COUNT(*), 2) AS pct
FROM ai_predictions p
JOIN matches m ON m.id = p.match_id
WHERE p.settled AND p.match_home_score IS NOT NULL AND m.home_score IS NOT NULL;
comparable  agree  pct
      3581     96  2.68

97.32% of resolved rows hang off a match with a different final score. The same check against the correct key returns 99.77% — coming up.

Check 2 — the calendar disagrees. Compare the day the prediction was written with the day the joined match kicked off:

|created_at - kickoff|    rows
0 days                       0
1-3 days                     6
4-30 days                2,479
31-180 days              1,251

Zero same-day. Six within three days. 3,730 rows point at a match that had already been played — sometimes weeks before the prediction existed.

Check 3 — the labels disagree. Join both tables to the same 3,736 predictions and compare competition IDs: 3,736 of 3,736 disagree, 100%. And the true competitions behind those rows number just 4 — four real competitions scattered across 46 invented ones.

That is the failure mode: a broken join does not produce empty output. It produces plausible, confidently wrong output, and the error is concentrated rather than random.

Why nothing stopped it: three ID spaces, zero foreign keys

The schema holds more than one identity per match, and every one is a bare integer:

matches.id                 811  ..  92,780         (dense, curated)
matches.nami_id        4,336,437 .. 4,670,168      (vendor natural key)
nami_matches.nami_id   4,336,437 .. 4,670,170
nami_matches.id              3 .. 25,340,015       (sparse surrogate)
ai_predictions.match_id  3,974 .. 24,360,892

The ranges overlap, which is why the collision rate is 7.9% instead of 0%. The column type cannot tell the spaces apart, and neither can the planner. The constraint that would have failed loudly is missing:

SELECT conname, conrelid::regclass AS tbl, confrelid::regclass AS ref
FROM pg_constraint
WHERE contype = 'f' AND conrelid::regclass::text IN ('ai_predictions','matches');
matches_competition_id_fkey  matches  competitions
matches_home_team_id_fkey    matches  teams
matches_away_team_id_fkey    matches  teams

matches has three outbound foreign keys. ai_predictions has none — its match_id, model_id and user_id are unconstrained integers, each a join waiting to mis-resolve.

The key that is actually correct

ai_predictions.match_id references nami_matches.id, the sparse surrogate of the raw feed. Not matches.id, not matches.nami_id. Same query, different target:

WITH joined AS (
  SELECT p.id, p.created_at, n.match_time,
         p.match_home_score AS p_home, p.match_away_score AS p_away,
         n.home_score_regular AS n_home, n.away_score_regular AS n_away
  FROM ai_predictions p
  LEFT JOIN nami_matches n ON n.id = p.match_id
  WHERE p.settled
)
SELECT COUNT(*)                                                        AS settled,
       COUNT(*) FILTER (WHERE match_time IS NULL)                      AS no_parent,
       COUNT(*) FILTER (WHERE created_at > match_time)                 AS written_after_kickoff,
       COUNT(*) FILTER (WHERE n_home IS NOT NULL AND p_home IS NOT NULL
                          AND (p_home <> n_home OR p_away <> n_away))  AS score_mismatch,
       ROUND(100.0 * COUNT(*) FILTER (WHERE n_home IS NOT NULL AND p_home IS NOT NULL
                          AND p_home = n_home AND p_away = n_away)
             / COUNT(*) FILTER (WHERE n_home IS NOT NULL AND p_home IS NOT NULL), 2) AS score_agreement_pct
FROM joined;
settled  no_parent  written_after_kickoff  score_mismatch  score_agreement_pct
  47114          0                     81             106                99.77

100% parent coverage, zero orphans, scores agreeing on 46,680 of 46,786 comparable rows. The league dimension goes from 46 competitions to 751 and from 3,736 rows to 47,114 — a 12.6× larger evidence base, obtained by changing one identifier in an ON clause.

The residual deserves naming rather than rounding: 81 rows written after kickoff and 106 score disagreements (0.23%). Those are real defects, not join errors. A working key does not mean a clean table; it means the defects are visible at the right grain instead of laundered into wrong-parent rows.

The missing bridge

matches remains worth keeping — curated competition and team foreign keys, lottery classification, scheduling metadata. It is just reachable by four hops, not one, since matches.nami_id resolves against nami_matches.nami_id for 85,314 of 85,580 rows (99.69%):

ai_predictions.match_id -> nami_matches.id -> nami_matches.nami_id
                        -> matches.nami_id -> matches.id

The fix: make the join assert on its own payload

A foreign key on nami_matches.id prevents future orphans, and we added one. But it would not have caught this bug: match_id values inside matches.id's range are valid children of matches.id, because they happen to be. Referential integrity was never violated. Semantics were.

So the durable fix is a validated view that carries the assertion inside it:

CREATE OR REPLACE VIEW v_predictions_graded AS
SELECT p.id AS prediction_id, p.model_id, p.created_at,
       p.prediction_direction, p.hit, p.hit_1x2,
       n.id AS nami_match_id, n.competition_id, n.match_time,
       n.home_score_regular, n.away_score_regular,
       (p.match_home_score = n.home_score_regular
        AND p.match_away_score = n.away_score_regular) AS score_consistent,
       (p.created_at <= n.match_time)                  AS written_before_kickoff
FROM ai_predictions p
JOIN nami_matches n ON n.id = p.match_id
WHERE p.settled
  AND p.match_home_score IS NOT NULL
  AND n.home_score_regular IS NOT NULL;

Consumers read the view, not the table. The two booleans then become a contract test that fails the build instead of the dashboard:

import psycopg2

DSN = "postgresql://user:pass@127.0.0.1:5433/soccerdream"
SQL = """
WITH t AS (
  SELECT p.created_at, p.match_home_score AS p_home, p.match_away_score AS p_away,
         n.match_time, n.home_score_regular AS n_home, n.away_score_regular AS n_away
  FROM ai_predictions p LEFT JOIN nami_matches n ON n.id = p.match_id
  WHERE p.settled)
SELECT COUNT(*), COUNT(*) FILTER (WHERE n_home IS NULL),
       COUNT(*) FILTER (WHERE created_at > match_time),
       COUNT(*) FILTER (WHERE p_home = n_home AND p_away = n_away),
       COUNT(*) FILTER (WHERE n_home IS NOT NULL AND p_home IS NOT NULL)
FROM t
"""

def test_join_integrity():
    with psycopg2.connect(DSN) as conn, conn.cursor() as cur:
        cur.execute("SET statement_timeout = '120s'")
        cur.execute(SQL)
        n, orphans, late, agree, comparable = cur.fetchone()
    assert round(100.0 * (n - orphans) / n, 2) >= 100.0, f"orphans={orphans}/{n}"
    assert round(100.0 * agree / comparable, 2) >= 99.5, "score disagreement too high"
    assert late <= 200, f"late-written rows={late} exceed known defect class"

Against the old join target the first assertion fails by 92 points. Against the correct one it passes with 0 orphans, 99.77% agreement and 81 late rows — three numbers a reviewer can argue with.

What the wrong join cost

naive matches.idcorrect nami_matches.id
rows reached3,73647,114
competitions46751
score agreement2.68%99.77%
orphansunmeasurable0
rows both joinable and semantically true9646,680

96 of 47,114. 0.20%. That is the end-to-end correctness of the join as written; every per-league rate built on it was a statistic about collisions.

The generalisable part is that join success rate is a vanity metric: it measures whether two integer domains intersect, which in any schema with more than one surrogate key they always will. The only metric that means something is semantic agreement on a field both sides independently observed — here the final score, plus the calendar. Two checks, a few lines of SQL, available the whole time.

For every join in your reporting layer, name the field that would have to agree if the join were real. If you cannot name one, you do not have a join. You have a Cartesian product with good manners.

Analytics and research content. Not betting advice. 18+

本内容仅供数据分析与操作参考,不构成任何决策建议。

风险提示:足球比赛结果具有内在不确定性,任何数据分析工具都无法消除这种随机性。本页内容仅供数据操作与参考,不构成任何决策建议或收益承诺。请理性看待体育赛事,远离任何形式的非法决策活动。
看完方法论,直接看它怎么用在今天的比赛上

SoccerDream AI 每场给出胜平负概率分布、总进球区间与置信度, 赛后自动复盘、命中记录公开可查。注册后可查看全部 AI 模型推演与已结算战绩。

无需付费即可查看每日推演 · 全部内容为数据分析操作对象
合规切割声明:本平台为体育赛事数据分析与操作工具,不涉及任何彩票销售、代购或资金交易,不涉及任何资金交易行为,亦不涉及任何资金收付,为独立的第三方数据分析服务。全部内容仅供操作与参考。请遵守所在地法律法规,理性对待。
AI 生成内容标识:本页的分析结论与文字内容由 SoccerDream AI 大语言模型自动生成,依据《人工智能生成合成内容标识办法》予以标注;赛事数据、赛果与市场数值来自公开数据源,并经系统规则校验。内容仅供操作参考,不构成任何建议。