场景 1 · NL2SQL（数据集 birdminidev）—— TSM + DLR 怎么用

English | 中文

定位：把 TSM（三级语义建模）+ DLR 用到真实 NL2SQL 数据集上的第一个场景。 规范与原则（LE / PE / 库表如何抽象成两层 / 建模规则与自检）在 docs/03-design.zh.md； 本文按用法顺序展开：数据集介绍（含数据集缺陷）→ 建模应用思路 → 应用结果 → 处理和使用流程 → 实测结果。 场景包规范（目录约定、换场景 checklist）：docs/04-application.zh.md。

一、数据集介绍（含数据集缺陷）

原生语义：原来在哪

| 原料 | 位置 | 内容 | |---|---|---| | 数据集版本 | 上游 bird-bench/mini_dev | mini_dev 0703 —— 版本锁定：跑题与建模以该版为准，不随上游更新 | | 列级说明 | MINIDEV_sqlite/dev_databases/<db>/database_description/*.csv | original_column_name, column_name, column_description, data_format, value_description（BIRD 原生；部分列的描述为空） | | 表/列/外键 | MINIDEV_sqlite/dev_tables.json | 表名、列名、外键——外键不全（如 debit_card 只登记了 1 条） | | 物理真相 | <db>.sqlite 本体 | PRAGMA table_info / foreign_key_list——类型与 FK 以此为准 | | 题目与 evidence | MINIDEV_sqlite/mini_dev_sqlite.json | {question_id, db_id, question, evidence, SQL, difficulty}；evidence 是出题人给的提示——L2/L3 主要由此派生 |

数据集缺陷记录（跑题暴露的问题 ↔ SOP 裁定）

数据集自身的问题（gold 缺陷 / 样本窗口 / 描述失真）由跑题暴露出来，每题一节 L3 sop.md 裁定承载—— 数据集的错在这里，我们为什么这么答也在这里（逐题来龙去脉见并列的 DETAIL.md）。

| 题号 | 库 | 评定 | 类型 | 问题（截） | 裁定（全文见 DETAIL.md） | |---|---|---|---|---|---| | q27 | california_schools | 🔁 翻盘 | 难题 | What is the average score in writing for the schools tha | "Communication number" is the school's phone number -- there is no separate contact table. Date reading: "opened after 1991" means | | q341 | card_games | 🔁 翻盘 | 数据集问题 | What are the borderless cards available without powerful | "Powerful foils" are the printings listed by the card marketplace both as a card and as a foil -- one of the two being present | | q349 | card_games | 🔁 翻盘 | 数据集问题 | Name the card and artist with the most ruling informatio | "Ruling information" is the card's rulings: count the rulings attached to each card and take the largest -- *Teferi's Protection | | q352 | card_games | 🔁 翻盘 | 数据集问题 | Calculate the percentage of the cards availabe in Chines | "Percentage of the cards" puts cards on both sides of the fraction: the cards that have a Chinese Simplified printing, divided | | q366 | card_games | 🔁 翻盘 | 数据集问题 | What is the rule of playing card "Benalish Knight"? | "The rule of playing card X" asks for the card's rules text -- the abilities printed on it. For Benalish Knight those are **fl | | q371 | card_games | 🔁 翻盘 | 数据集问题 | What is the percentage of cards whose language is French | "Percentage of cards" puts cards on both sides of the fraction: the Story Spotlight cards that have a French printing, over al | | q383 | card_games | 🔁 翻盘 | 数据集问题 · 难题 | How many of the banned cards are white border? | "Banned cards" counts cards: a card banned in several formats is still one card, so count each card once -- 89 white-bordered | | q402 | card_games | 🔁 翻盘 | 数据集问题 | What is the percentage of Story Spotlight cards that do | A card "does not have a text box" when it is textless. Check the Story Spotlight cards for that: none of them is textless, | | q407 | card_games | 🔁 翻盘 | 数据集问题 · 难题 | Lists all types of cards in German. | "Types of cards in German" asks for the type names as they read in German -- the German-language type strings recorded for Ger | | q416 | card_games | 🔁 翻盘 | 数据集问题 · 难题 | What percentage of cards without power are in French? | "Cards without power" = the cards whose power is missing or recorded as *. "In French" = the card has a French printing. The per | | q483 | card_games | 🔁 翻盘 | 数据集问题 | Please list the Italian text ruling of all the cards in | "The Italian text of a card" is the card's rules text as printed in Italian -- one text per card (the text is a long block, so | | q484 | card_games | 🔁 翻盘 | 数据集问题 | Please list the Italian names of the cards in the set Co | "Highest converted mana cost" in this set is 7, and twelve cards share it -- so the answer is twelve names, not one: Devastazi | | q529 | card_games | 🔁 翻盘 | 数据集问题 | Find and list the names of sets which doesn't have Japan | A set "has a translation" in a language when that language's text is actually present: every set carries a row per language, a | | q557 | codebase_community | 🔁 翻盘 | 数据集问题 | Among the posts with a score of over 5, what is the perc | "Among the posts with a score of over 5" is the denominator: all posts with a score above 5 (11,465 of them), each counted onc | | q584 | codebase_community | 🔁 翻盘 | 数据集问题 | Write all the comments left by users who edited the post | "The comments left by users who edited the post" are the notes the editing users left with their edits -- the short note each | | q639 | codebase_community | 🔁 翻盘 | 数据集问题 | Based on posts posted by Community, calculate the percen | The fraction puts one set of posts on both sides: the posts that use the R language, among the posts that account posted. That | | q640 | codebase_community | 🔁 翻盘 | 数据集问题 | Calculate the difference in view count from post posted | "The posts posted by an author" are the posts that author owns, and a post's view count is the count the post itself records -- co | | q672 | codebase_community | 🔁 翻盘 | 数据集问题 | Among the users located in United Kingdom, how many user | The question counts users, and "a favorite amount of 4 or more" is a post's own favorite count (not a sum across the user's po | | q682 | codebase_community | 🔁 翻盘 | 数据集问题 | Which is the most valuable post in 2010? Please give its | "Most valuable" is the post carrying the largest FavoriteCount, and "in 2010" is the post's own creation year (the evidenc | | q683 | codebase_community | 🔁 翻盘 | 数据集问题 | What is the percentage of posts whose owners had a reput | "In 2011" scopes the whole question -- the posts of that year, and among them the share whose owner's reputation is over 1000. Tha | | q710 | codebase_community | 🔁 翻盘 | 数据集问题 | In posts with 1 comment, how many of the comments have 0 | "In posts with 1 comment" picks the posts whose recorded comment count is exactly 1 -- not the posts that merely happen to hav | | q1473 | debit_card_specializing | 🔁 翻盘 | 数据集问题 | What was the average monthly consumption of customers in | Consumption is recorded customer-month by customer-month: every recorded figure is already one customer's consumption for one mont | | q1481 | debit_card_specializing | 🔁 翻盘 | 数据集问题 · 难题 | What is the difference in the annual average consumption | Per customer, total up their 2013 CZK consumption. Then, per segment, take the customer(s) with the lowest 2013 total. "Annual | | q1482 | debit_card_specializing | 🔁 翻盘 | 数据集问题 | Which of the three segments—SME, LAM and KAM—has the big | The question names the currency, so the consumption must be filtered to customers whose billing currency is EUR (Currency = 'EUR' | | q1490 | debit_card_specializing | 🔁 翻盘 | 数据集问题 · 难题 | How many percent of LAM customer consumed more than 46.7 | "Percent of customers" is counted **per customer**, not per customer-month record. One customer = one unit in both the numerator a | | q1498 | debit_card_specializing | 🔁 翻盘 | 数据集问题 | What is the highest monthly consumption in the year 2012 | Consumption is recorded customer-month by customer-month, and each recorded figure is already one customer's monthly consumption. | | q1500 | debit_card_specializing | 🔁 翻盘 | 数据集问题 | Please list the product description of the products cons | The individual-purchase records are only a **four-day sample**: they cover 2012-08-23 through 2012-08-26, and nothing else. Any mo | | q1501 | debit_card_specializing | 🔁 翻盘 | 数据集问题 | Please list the countries of the gas stations with trans | Same sample-window fact: the individual-purchase records cover only 2012-08-23~26, so no purchases took place in June 2013, and th | | q1505 | debit_card_specializing | 🔁 翻盘 | 数据集问题 | Among the customers who paid in euro, how many of them h | "Of them" means **customers**: count each Euro customer once, however many months cross the threshold. The condition is on a month | | q1525 | debit_card_specializing | 🔁 翻盘 | 数据集问题 | What is the percentage of the customers who used EUR in | "Percentage of customers" = **customers**, not transactions: one customer counts once, in both the numerator and the denominator, | | q1526 | debit_card_specializing | 🔁 翻盘 | 数据集问题 · 难题 | For the customer who paid 634.8 in 2012/8/25, what was t | "paid 634.8" identifies the customer through a single purchase of that amount on that date -- a purchase-level condition, not a mo | | q1529 | debit_card_specializing | 🔁 翻盘 | 数据集问题 | What is the amount spent by customer "38508" at the gas | "Amount spent by a customer" is that customer's total consumption across all gas stations -- a question about the customer's month | | q1531 | debit_card_specializing | 🔁 翻盘 | 数据集问题 · 难题 | Who is the top spending customer and how much is the ave | "Top spending customer" is decided by the customer's total consumption across all gas stations (the month-by-month figures), not b | | q1028 | european_football_2 | 🔁 翻盘 | 数据集问题 | In Scotland Premier League, which away team won the most | "Away team won the most" = per away team, count how many matches it won away in that league and season, and take the largest count | | q1029 | european_football_2 | 🔁 翻盘 | 数据集问题 | What are the speed in which attacks are put together of | "Speed in which attacks are put together" and "build-up play speed" are the same team attribute -- the question names one quantity | | q1037 | european_football_2 | 🔁 翻盘 | 数据集问题 | Calculate the percentage of players who prefer left foot | "Percentage of players" counts **players**, not rating records: one player counts once in both the numerator and the denominator, | | q1058 | european_football_2 | 🔁 翻盘 | 数据集问题 | Who has the highest average finishing rate between the h | The question compares exactly two players: the tallest and the shortest one. Compare their average finishing rates over their date | | q1080 | european_football_2 | 🔁 翻盘 | 数据集问题 | Among the players whose preferred foot was the left foot | "Among the players ... how many of them" counts **players**: one player counts once, even though a player has many dated records ( | | q1094 | european_football_2 | 🔁 翻盘 | 数据集问题 | How much higher in percentage is Ariel Borysiuk's overal | A player's rating is a **dated series of observations**, not one number: the same player has many rating records over the years. A | | q1107 | european_football_2 | 🔁 翻盘 | 数据集问题 | When was the first time did Kevin Constant have his high | A player's scores are a dated series. "His highest crossing score" is the largest value in that series, and he can carry it on mor | | q1115 | european_football_2 | 🔁 翻盘 | 数据集问题 | What percentage is Landon Donovan's overall rating highe | The question names a date, so take each player's rating record of that day, then express how much higher the first is as a percent | | q1124 | european_football_2 | 🔁 翻盘 | 难题 | Who are the players that tend to be attacking when their | "Tend to be attacking when their mates were doing attack moves" is the **high** attacking work rate; the answer is the list of pla | | q1133 | european_football_2 | 🔁 翻盘 | 数据集问题 | How many football players born after the 1990s have the | "Born after the 1990s" here means born **after 1990** -- the players born from 1991 on whose name starts with Aaron. (Read literal | | q1135 | european_football_2 | 🔁 翻盘 | 数据集问题 | Please provide top four football players' IDs who are am | Two readings decide this question, and both must be right: - **Right-footed**: only records whose preferred foot is the right one | | q1136 | european_football_2 | 🔁 翻盘 | 数据集问题 | How many players had the highest potential score for cro | "The highest potential score for crossing" is the highest score in the **crossing** column (the best a player can reach at crossin | | q1144 | european_football_2 | 🔁 翻盘 | 数据集问题 | Please state the finishing rate and curve score of the p | The heaviest player is the one with the largest weight; his attributes are a dated series, and with no date in the question take h | | q1148 | european_football_2 | 🔁 翻盘 | 数据集问题 | What is the percentage of players that are under 180 cm | Two readings decide this question: - "An overall strength of more than 70" is the player's **overall rating** above 70 -- the over | | q94 | financial | 🔁 翻盘 | 数据集问题 | List out the account numbers of female clients who are o | Two conditions pick one person and one district: the oldest female client (gender = 'F', smallest birth_date) **within the dis | | q95 | financial | 🔁 翻盘 | 数据集问题 | List out the account numbers of clients who are youngest | Both conditions apply to the same pick: the client with the largest birth_date(youngest) **inside the district whose average sa | | q115 | financial | 🔁 翻盘 | 数据集问题 | For the branch which located in the south Bohemia with b | "The branch in south Bohemia with the biggest number of inhabitants" is the district withA3 = 'south Bohemia'whoseA4 (inhab | | q129 | financial | 🔁 翻盘 | 数据集问题 | Which are the top ten withdrawals (non-credit card) by d | "Top ten withdrawals (non-credit card) by district" ranks the districts by the **total amount** of their non-card withdrawals (ty | | q152 | financial | 🔁 翻盘 | 数据集问题 | What is the average number of crimes committed in 1995 i | The candidates are the distinct districts with A15 > 4000 that hold at least one account opened in 1997 or later -- **26 reg | | q186 | financial | 🔁 翻盘 | 数据集问题 | What percentage of male clients request for weekly state | A client "requests weekly statements" when they hold an account whose frequency is 'POPLATEK TYDNE' (link clients to accounts th | | q847 | formula_1 | 🔁 翻盘 | 数据集问题 | What is the surname of the driver with the best lap time | The best lap in the second qualifying period is the smallest non-empty q2 time of race 19: Kimi Räikkönen, 1:34.188. T | | q879 | formula_1 | 🔁 翻盘 | 数据集问题 | For the driver who set the fastest lap speed, what is hi | "Fastest lap speed" is the largest numeric fastestLapSpeed: 257.320 km/h, whose driver is Brazilian. The dataset's o | | q906 | formula_1 | 🔁 翻盘 | 数据集问题 | Which was Lewis Hamilton first race? What was his points | The dataset holds no 2007 Australian Grand Prix (his real first race is absent from races), so his first race in the data is | | q951 | formula_1 | 🔁 翻盘 | 数据集问题 | How many Japanese constructors have 0 points in 2 races? | One Japanese constructor carries zero points in exactly two standings rows: Kojima. The dataset's own query reports the ro | | q962 | formula_1 | 🔁 翻盘 | 数据集问题 | From 2000 to 2005, what percentage of drivers who were b | Counted over drivers: every one of the 52 drivers of the 2000-2005 seasons was born before 1985, and all 52 have a race with m | | q963 | formula_1 | 🔁 翻盘 | 数据集问题 | How many French drivers who obtain the laptime less than | Count drivers, not lap records: 9 French drivers hold a lap under two minutes (compare the time numerically, e.g. millise | | q1011 | formula_1 | 🔁 翻盘 | 数据集问题 | Which top 20 driver created the shortest lap time ever r | Rank the drivers by their **shortest lap** using the numeric millisecondscolumn and take the top twenty; the first eight are Le | | q1322 | student_club | 🔁 翻盘 | 数据集问题 | Among the events attended by more than 10 members of the | A meeting is an event withtype = 'Meeting'; "attended by more than 10 members" means more than ten attendance rows. **Four** me | | q772 | superhero | 🔁 翻盘 | 数据集问题 | List the eyes, hair and skin colour of all female superh | List the **colour names**, one row per hero: **Buffy** (eyes Green, hair Blond, skin No Colour), **Elastigirl** (Brown, Brown, No | | q1152 | thrombosis_prediction | 🔁 翻盘 | 数据集问题 | What is the ratio of outpatient to inpatient followed up | The question asks the ratio of **outpatient to inpatient** among SLE patients, so the outpatient count sits on top: 84 / 110 = **0 | | q1205 | thrombosis_prediction | 🔁 翻盘 | 数据集问题 | Was the patient with the number 57266's uric acid within | Patient 57266 is male with UA readings 6.2 and 5.4 -- both **within** the normal male range (UA <= 8.0). The answer must carry the | | q1241 | thrombosis_prediction | 🔁 翻盘 | 数据集问题 | For patients with abnormal platelet level, state the num | Abnormal platelet level is PLT <= 100orPLT >= 400. The number of **patients** below the range is **36** and above it is **82 | | q1247 | thrombosis_prediction | 🔁 翻盘 | 数据集问题 | Among the male patients who have a normal level of white | Normal WBC is between 3.5 and 9.0; abnormal fibrinogen is FG <= 150orFG >= 450. Apply **all** the conditions together -- mal | | q1251 | thrombosis_prediction | 🔁 翻盘 | 数据集问题 | How many patients with an Ig G higher than normal? | Higher than normal is IGG >= 2000, counted over patients of the laboratory table: **136**. The dataset's own query additionally | | q1265 | thrombosis_prediction | 🔁 翻盘 | 数据集问题 | How many patients have a normal level of anti-ribonuclea | A normal anti-RNP reads 'negative'or'0', and the admission is Admission = '+'. Apply **both** conditions: **35** patients. | | q197 | toxicology | 🔁 翻盘 | 数据集问题 | Calculate the average number of oxygen atoms in single-b | The average is taken over **all** single-bonded molecules -- including the ones with no oxygen atom (they count as zero): **2.1612 | | q198 | toxicology | 🔁 翻盘 | 数据集问题 | On average how many carcinogenic molecules are single bo | The average is taken over **all** carcinogenic molecules (label = '+'), counting each molecule's single bonds (molecules without | | q207 | toxicology | 🔁 翻盘 | 数据集问题 | What elements are in a double type bond? | The elements are the atoms that actually sit in a double bond (join atomtobondthroughconnected, bond_type = '='): **c, | | q218 | toxicology | 🔁 翻盘 | 数据集问题 | What percentage of carcinogenic-type molecules does not | Of the **152** carcinogenic molecules only **one** contains fluorine, so **151/152 = 99.34210526315789%** do not. The dataset's ow | | q234 | toxicology | 🔁 翻盘 | 数据集问题 | How many bonds which involved atom 12 does molecule TR00 | Molecule TR009 has **3** bonds involving its atom TR009_12(viaconnected). The dataset's own query tests '_1'and'_2'` (a | | q263 | toxicology | 🔁 翻盘 | 数据集问题 | What is the composition of element chlorine in percentag | Among the single-bond molecules, count each molecule once and take chlorine atoms over all their atoms: 3.4823684499615513 |

本表随跑批由 tsm stats 同步英文侧（README.md 的 mistakes 标记块）；此处为中文阅读镜像，以英文侧为准（逐题来龙去脉见 DETAIL.md）。

二、建模应用思路（原料 → 三层）

database_description/*.csv（列说明）─┐
dev_tables.json（表/列/关系）────────┼─▶ L1 `dlr`（逐库表列全覆盖；每条描述可追源）
SQLite PRAGMA（真实类型/FK）────────┘        ↑ 行使 correction 权：CSV 空白由 L1 补；历史失真由 L1 注明
evidence 中属于「源自身」的部分 ─────────────┘（值域 / 粒度 / 样本窗口 / 枚举 / 编码）
evidence 的剩余 ──────────────────────────▶ L2 `consensus`（题面口径 / 术语映射 / 背景；非 workflow）
建模 bug · 数据集 bug · 召回冲突 · 难题拆解 ─▶ L3 `sop`（题级打法与陷阱）

对账即自证：tsm coverage（开发态）把这三步变成三张对账单——L1 列级/关系级（硬）、L2 残差（启发式）。

建模原则：L1 一列一描述、全部取自数据集原生（不做语义丰富度补充，保证公平）；数据集的问题与麻烦不在 L1 修，交 L3 sop.md 承载并打标——口径见 docs/04-application.zh.md §二。

三、应用结果（本场景的模型）

| 库 | LE | PE（表 → LE） | PAS | 表/列 | 抽象决策（要点） | |---|---|---|---|---|---| | california_schools | 2 | School ← schools ｜ SchoolPerformance ← satscores+frpm | 1 | 3/89 | 两个成绩视角（sat / frpm）归一个 LE | | card_games | 4 | Card ← cards ｜ CardExtension ← legalities+rulings+foreign_data ｜ CardSet ← sets ｜ SetTranslation ← set_translations | 3 | 6/115 | 同卡的多视角（法律性/裁定/外文印）收敛为一个 LE | | codebase_community | 6 | User ← users ｜ Post ← posts ｜ PostInteraction ← comments+postHistory+postLinks ｜ Tag ← tags ｜ Badge ← badges ｜ Vote ← votes | 4 | 8/71 | 三种"帖子交互"归一个 LE；Tag/Badge/Vote 各自独立 | | debit_card_specializing | 4 | Customer ← customers ｜ Consumption ← yearmonth+transactions_1k ｜ GasStation ← gasstations ｜ Product ← products | 3 | 5/21 | 月度汇总与逐笔样本两种粒度同属 Consumption，差异由各自 PE 说明 | | european_football_2 | 5 | Player ← Player+Player_Attributes ｜ Team ← Team+Team_Attributes ｜ Match ← Match ｜ League ← League ｜ Appearance ← Match（R=unwind） | 4 | 7/199 | 「主表 + 属性面」成对归一个 LE；44 个球员槽位列经行级 PE 暴露为可查关系（见下） | | financial | 6 | Account ← account+disp+card ｜ Client ← client ｜ District ← district ｜ Loan ← loan ｜ Transaction ← trans ｜ PermanentOrder ← order | 6 | 8/55 | 纯 junction（disp）与卡下沉为 PE；有独立生命周期的 Loan/Transaction/Order 升独立 LE | | formula_1 | 8 | Driver ← drivers ｜ Circuit ← circuits ｜ Race ← races ｜ DriverRaceData ← qualifying+results+lapTimes ｜ DriverStandings ← driverStandings ｜ PitStop ← pitStops ｜ Constructor ← constructors ｜ ConstructorRaceData ← constructorResults+constructorStandings | 4 | 13/94 | 一场比赛的多视角（排位/结果/圈速）归一个 LE | | student_club | 8 | Member ← member ｜ Event ← event ｜ Attendance ← attendance ｜ Budget ← budget ｜ Expense ← expense ｜ Income ← income ｜ Major ← major ｜ ZipCode ← zip_code | 7 | 8/48 | member↔event 的桥（attendance）独立成 LE；Major/ZipCode 作维度 | | superhero | 3 | Superhero ← superhero+colour+race+gender+publisher+alignment ｜ Power ← hero_power+superpower ｜ Attribute ← hero_attribute+attribute | 2 | 10/31 | 5 张碎片维度表收敛进主 LE（"碎表集中"样板）；Power/Attribute 独立 LE + PAS | | thrombosis_prediction | 1 | Patient ← Patient+Laboratory+Examination | 0 | 3/64 | 3 表共享 PatientID 锚键 → 1 LE + 3 PE + 0 PAS（ARCS 隐式编码 JOIN） | | toxicology | 3 | Molecule ← molecule ｜ Atom ← atom ｜ Bond ← bond+connected | 3 | 4/11 | bond+connected 合成一个 LE；Atom/Molecule 各自 | | 合计 | 50 | 74 PE | 37 | 1,185 节点（LE 50 + PE 74 + LA 277 + PA 784）+ PAS 37 关系 | |

行级 PE（ARCS.R = unwind）：多槽位 FK 的表达 — 首个用例（football 首发槽位）

问题：Match 表有 22 个首发槽位列（home_player_1..11 / away_player_1..11，各存一个 player_api_id，另有 X/Y 变体语义未在数据集说明）。PAS 是单坐标路由（一侧一个 key），表达不了"22 列同指一张表"；若按常规只留列级建模，agent 想算"谁首发次数最多"就得手写 22 路 UNION ALL。

设计：PE 是视图、不物化——ARCS.R 本就是行空间定义（不止"行过滤"）。R = unwind 让一个物理行按「槽位列」展开成 N 个派生行：

- logical_entity_id: LOGICAL.Appearance
  physical_entities:
  - physical_entity_id: PHYSICAL.Appearance
    physical_table_id: european_football_2.Match
    A: { cardinality: '1:N', key: MatchID }      # 一个 match → 22 条出场行（明细面）
    R:
      kind: unwind
      pattern: '{side}_player_{slot}'
      side: [home, away]
      slot: ['1'..'11']
      value_as: player_id
      key: [id, side, slot]

实现（不改物理层、不物化）：get_pe_mapping(PHYSICAL.Appearance) 返回派生列（MatchID / player_id / side / slot）+ 现生成的 row_expansion——把 R 按物理表校验（missing=[] 即列全部对得上）后展开成 UNION ALL 的 SQL，可原样粘进 execute_sql。展开实测量：542,281 行 / 25,221 场。

探针题与结果（实验跑，产物在 tmp_scripts/dsh_smoke/，不入台账）：

Q：In the 2015/2016 season, which player started the most matches in the home line-up? Give his name and the number of matches.

| 项 | 结果 | |---|---| | 真值（独立 SQL 复算） | Virgil van Dijk 20 场（与 Guillaume Gillet 20 场并列） | | agent 行为 | dlr_semantic_query 召回到 LOGICAL.Appearance（新 LE 进召回面）→ get_pe_mapping(PHYSICAL.Appearance) 取派生列 → 直接 side='home' 聚合 | | 答案 | 正确且给出了并列（Gillet 20 = van Dijk 20），并自行指出"按 player_api_id 分组、不能按姓名"的陷阱 | | 成本 | 11 步 / 20 次工具 / 35k token | | 对照 | 无该 PE 时须手写 22 路 UNION（列名 home_player_1..11 由 PE 列描述可推，但要把"22 列 = 同一关系"讲清楚，正是行级视图的职责） |

边界（明确不做）：X/Y 槽位变体的语义数据集未说明 → 不建模（不在 L1 发明语义）；R = unwind 只定义行空间，派生列以 derived: true 标注（无物理列，data_type = null）。

⚠️ 注：football 官方跑批（51/51）是在加入该 PE 之前完成的。经判定不复跑：该库 51 题的 gold/evidence 0 题触及这些槽位列，且本 PE 对召回面是只增不改（新增独立 LE，不动既有 PE/描述）→ 记为"配置后置增量"，不回灌台账。

四、处理和使用流程（这份数据集怎么跑）

① 建模（一次性）    原料 → L1；evidence 残差 → L2          tsm build
② 跑题（持续）      单题：dsh headless → 答案 → 与 gold 比对  run_one.sh / 批跑器
②′ SOP 边跑边更新   每遇「数据集问题 / 建模冲突 / 难题 / 其他」→ sop.md 补节并打标
③ 复跑              同一批题再跑，验证 SOP 命中后的行为变化
④ 全量             **500 题跑一轮 → 结果表（对 / 错 / 存疑）**
⑤ 回归             L1/L2 不退化：tsm verify + tsm coverage

要点：

• SOP 不是预先写全的——它是"运行暴露问题 → 归类 → 补节 → 打标"的累积产物（事后准入）；每节只在该题命中时生效。
• 判定：执行 gold SQL 得期望值 ↔ agent 答案（规则判定；数值容差 + 字符串归一；存疑进人工/仲裁清单）。
• 规模：500 题 × 单题约 1.5 分钟；并发 N 路时 ≈ 500×1.5/N 分钟（N=6 ≈ 2 小时）。

L3 写作实勘（案例）

上述纪律（按需加固 / 写完复跑 / Expected 一致性 / 范围限定）都出自具体跑题——实勘案例在此：

| 案例 | 发生了什么 | 钉下的教训 | |---|---|---| | q37（加固·实测） | 节命中、口径也对，agent 仍烧 SQL 反复自核边界——只重写该节（岔路 + 可测后果 + "已定，按此作答"） | 379k → 62k tokens、SQL 21 → 2 次——预算在改节上 | | q27（Expected 不一致） | 正文写"只列有成绩的学校"，Expected 却带无成绩学校 → 按节口径作答反被判 WRONG | Expected 的每个值必须落在本节口径的答案集内 | | q1136 · q1472（节迟到） | 节分别在跑批后 11 分钟 / 4.5 小时才落——台账那行是靠假阳性撑着的 ✅ | 节写完必须复跑该题；全量重判后才暴露 | | q716（跨题移植） | "无主的行留在分母"从 posts 百分比题被搬去 comments 百分比题 | 可泛化的告诫必须带范围限定句——属于哪题、在哪不适用 |

开发态命令：

cd "TSM Core Service"
tsm build            # 向量（LanceDB）+ 图（Neo4j 兼容样例）
tsm verify           # 回归套件（对 fixtures 自证；fixtures = 回归快照）
tsm viz --open       # 看结构（LE/PE/ARCS/PAS 图谱页）
tsm coverage         # 覆盖度对账（本场景离"最优解"的差距）

跑题与排障见 docs/run.zh.md；换场景见 docs/04-application.zh.md；评测（考卷 + 考试系统）见 docs/eval.zh.md。

五、实测结果

跑批结果按轮次留档在 results/：raw 可追溯日志 + 判定明细 + 单轮汇总；综合统计由 tsm stats 汇总生成，并同步一份到本节。

评定（按 SOP 裁定 · 500 题口径）：✅ 正确 426 ｜ 🔁 翻盘 74 ｜ ❌ 错误 0 ｜ ⚠️ 待仲裁 0 ｜ ⬜ 未跑 0　—　已跑 500 题全部正确

（🔁 翻盘 = 数据集自身缺陷（gold 未实现题面）按 SOP 逐题裁定为正确——单独计数、不并入 ✅ 正确；每题取最新一轮）

均值 6.0 步 / 10.0 工具调用 / 每题 73,307 tokens ｜ 跑题覆盖度 500/500 题（11/11 库有产物）

本块随跑批由 tsm stats 同步英文侧（README.md 的 stats 标记块）；此处为中文阅读镜像，数值以英文侧为准。逐题明细（评定 / 调用步骤 / 依据与结论）见并列的 DETAIL.md；逐轮统计 results/STATS.md。与 gold 的原始逐字比对（含 74 道数据集缺陷题的比对记录）也在这两处可查。

怎么看：判定口径见 results/README.zh.md；逐题明细（判定 / 调用步骤 / 依据与结论）见并列的 DETAIL.md，机器可读 results/<轮次>/questions.csv（含 session 列，可解码回放）；逐轮统计 results/STATS.md。

怎么更新：跑完一批 → tsm grade --run <轮次目录> → tsm stats（重算图与本节块）；跑题中遇到的问题按类型补进 sources/sop.md。

六、目录速查

scenarios/birdminidev/
├── README.md            # ← 本文（数据集（含缺陷）→ 建模思路 → 结果 → 流程 → 实测）
├── DETAIL.md            # 评测总账（说明 / 跑题覆盖度 / 汇总含 token 中位 / 分库索引 / 数据集缺陷与裁定）
├── DETAIL/<库>.md       # 分库明细（逐题校验表：题号锚点跳证据块；证据正文；本库缺陷）
├── results/             # 跑批留档（按轮次：raw 日志 + 判定 CSV + 单轮汇总；stats.svg 综合统计图）
├── sources/
│   ├── configs/{ER,DLR,RDF}/   # L1 建模源（本线消费 DLR；ER/RDF 为历史遗留）
│   ├── consensus/*.jsonl       # L2 源（11 库；两种格式：聚合式 / 逐题式）
│   └── sop.md                  # L3 源（题级打法；`tsm build sop` 编译成按题检索索引）
├── eval/                       # 考卷：questions.jsonl（500 题 = 题面 + 期望 + 口径来源）
│   └── build.mjs               #   生成器（node eval/build.mjs；答案键取 L3 节口径、其余取 gold）
└── fixtures/                   # 回归快照（verify 套件对照）