用 SQL 把 AI 回答整理成可比表：三張表與一次 join

简介： 本文提出一种面向跨平台、跨问法横向对比的“可比表”设计方法：通过**事实表（回答明细）+ 条件表（问法集）+ 标签表（平台维度）** 三张正交表结构，以**批次号（run_id）为唯一可比锚点**，确保同批数据严格对齐。字段精简、语义清晰、时区严谨、分母显式，规避隐式拼接与口径漂移，专为可靠对比而生。（239字）

可比表，指的是把同一批采集结果按固定键对齐，让任意两个平台、两个问法都能直接并排读的一张表。它可不可比，只看一件事：同一批的条件有没有被存进数据里。

而这件事卡住的地方，往往在表结构，不在 SQL 怎么写。同一句问法、同一个平台、隔一周再跑一次，回答可能全变，两张表直接并排放，读出来的差异分不清是环境变了还是样本变了。

我先把结论放这里：表设计只要三张，回答明细存事实，问法集存条件，平台维度存标签；每次采集带一个批次号；跨批比较一律显式声明批次，不做隐式拼接。下面按建表、字段、批次、join、坑、成品查询、边界七段写，例子来自一批真实数据的结构，四十五条一批的那套流程和一次 10,324 条回答的累计统计用的是同一套表。

三张表：事实、条件、标签各归一张

我把三张表的职责切得很开，切不开就会出现「平台名既在明细里又在维度表里，两边还写法不同」这种局面。

表：粒度 ｜ 主键 ｜ 不放什么
回答明细：一条回答一行 ｜ 批次号 + 问法 + 平台 ｜ 不放问法分类、不放平台分组，避免与另两张表打架
问法集：一条问法一行 ｜ 问法原文（或问法编号） ｜ 不放采集结果，问法表是条件表，改一次就是一次口径变更
平台维度：一个平台一行 ｜ 平台编号 ｜ 不放条数，条数是查出来的，不是存出来的

明细表只存事实：谁问了什么、在哪个平台、回答里写了什么、有没有给出可点的地址、什么时刻查的。事实之外的判断全部靠 join 出来。

问法集单独成表的好处，是把「问法」从一段文本升级成一个有编号、有分类、有版本的实体。改问法的正确做法是新增一个版本、老版本留着，历史读数才对得上，而不是直接改一行的字符串。

平台维度表真正的作用是给平台打分组标签，报表按标签聚合时不必硬编码平台清单；少了它，每个查询都要写一长串平台名字。

字段设计：六个字段撑起一张明细表

明细表的字段不多，但每一个都有明确的取舍。

字段：类型 ｜ 必填 ｜ 为什么这么定
question：text ｜ 是 ｜ 存送进模型的原文，不改写、不去标点
platform：text ｜ 是 ｜ 平台编号而非展示名，展示名放维度表
answer：text ｜ 是 ｜ 存全文，空回答也要存空串并打标记
cited_url：text ｜ 否 ｜ 一条回答可能给多个地址，多地址另起一列或用关联表
checked_at：timestamptz ｜ 是 ｜ 带时区，不用本地时间戳
run_id：text ｜ 是 ｜ 批次号，同一批必相同

question 存原文这条我顶住过压力。很多人会顺手把问法做一遍清洗再入库，觉得统一了好看。可是清洗之后还算不算同一句？不算，它已经不是当时送进模型的那句，后续想按原文复跑就对不上了。要统一展示，放到问法集表的展示名里。

cited_url 这个字段我纠结最久。一条回答里出现两个地址是常事，用单值字段存，第二个地址要么丢、要么拼成一个长串。我建议单值字段只存首个地址，多个地址走关联表，计数时以关联表为准；不这么做，带链接的条数会随拼接方式变化。

checked_at 用带时区的类型，是为了让跨批比较不至于莫名其妙差八小时。这个坑下面单独说。

为什么要 run_id：同一批才可比

run_id 是批次号，一次采集任务生成一个，同一次任务写入的所有行共用同一个值。它的作用是让「同一批」这件事在数据里有据可查，而不是靠时间范围猜。

举一个真实的规模感受：一次 10,324 条回答的累计统计里，带链接的有 921 条，去重后落在 144 个域名上。这个累计值来自多次采集，若要算「某个平台在某批里的表现」，就必须先按 run_id 切开；不分批直接聚合，等于把不同时期的问法版本、不同时期的平台接口混在一起。

命名上我建议带日期前缀，排序即时间序；但批次号必须由程序生成，不许手工拼进主键。

一次 join 得到每平台每问法的被引计数

三张表连起来的写法不复杂，容易出错的是连接条件与聚合顺序。

连法是：明细表按平台关联平台维度取标签，按问法关联问法集取分类，然后按平台与问法分组，统计条数、有链接的条数、空回答的条数。分组键用编号而非展示名，避免同名不同写法产生两组。

一个容易被忽略的细节是分母。分母要取「这一批该有的条数」，也就是问法集里某平台应跑的条数，而不是明细表里实际写进来的行数。失败的那几条如果没写入明细，用行数当分母，成功率会一直很好看。

-- ④ 每平台每问法：条数、带链接、空回答
SELECT r.platform,
q.category,
r.question,
count(*)                                  AS n_total,
count(*) FILTER (WHERE r.cited_url IS NOT NULL) AS n_cited,
count(*) FILTER (WHERE r.answer = '')     AS n_empty
FROM   answer_record r
JOIN   platform_dim  p ON p.platform = r.platform
JOIN   question_set  q ON q.question = r.question
WHERE  r.run_id = :run_id
GROUP  BY r.platform, q.category, r.question
ORDER  BY r.platform, q.category;

-- ⑥-1 三个成品查询之一：某批各平台的有链接条数（分母用计划条数）
SELECT p.platform, p.tier,
count(r.cited_url)                          AS cited_rows,
(SELECT count(*) FROM question_set q
WHERE q.active)                           AS planned_rows
FROM   answer_record r
JOIN   platform_dim  p ON p.platform = r.platform
WHERE  r.run_id = :run_id
GROUP  BY p.platform, p.tier;

-- ⑥-2 三个成品查询之二：三个计数一次取齐
SELECT count(*)                                      AS n_rows,
count(cited_url)                              AS n_with_url,
count(*) FILTER (WHERE answer = '')           AS n_blank,
count(DISTINCT regexp_replace(cited_url, '^https?://([^/]+).*$', '\1'))
AS n_domain
FROM   answer_record
WHERE  run_id = :run_id;

-- ⑥-3 三个成品查询之三：失败与空回答分开数
SELECT p.platform,
count(*) FILTER (WHERE r.answer IS NULL)      AS n_missing,
count(*) FILTER (WHERE r.answer = '')         AS n_blank,
count(*) FILTER (WHERE r.cited_url IS NULL)   AS n_no_url
FROM   answer_record r
JOIN   platform_dim  p ON p.platform = r.platform
WHERE  r.run_id = :run_id
GROUP  BY p.platform;

我每次写完先跑 EXPLAIN，确认 run_id 上有索引、平台维度表走了小表关联。明细表到十万级之后，没有索引的分组会拖到几十秒。

上线顺序我按三步走：第一步，先建三张表和唯一约束，用十条假数据跑一遍写入与冲突更新；第二步，跑一次真实批次，把 EXPLAIN 出来的计划拿去核对；第三步，拿同一批数据分别用两个成品查询算一遍，两边对不上就说明分母没对齐。

为什么要先对分母再去优化速度？因为慢只是体验问题，分母错是结论问题。

常见坑：时区、空值、重复样本

时区坑是我踩得最深的一个，也最安静。checked_at 用不带时区的类型存本地时间，跨机器汇总时就会有人按自己的时区再解释一遍，同一批数据在两张报表里差八小时，看起来像两次采集。定死了带时区类型还不算完，写入和查询两侧都要显式声明时区，否则驱动会替你决定。

空值参与 join 是我第二次踩到的坑。平台字段若允许为空，内连接会把这些行静默丢掉，总数变少却没人报错。要么写入时挡住空值，要么外连接后单列一行「未归属平台」。

空回答与缺失值要分开。空串表示平台答了但正文为空，NULL 表示这一条压根没采到；两者在语义上不同，在计数上也该不同。混成一个字段，失败率永远算不准。

重复样本是第四个坑。同一批里同一条问法写了两次，来源可能是重试，也可能是任务被重复调度。判据是批次号加问法加平台的组合出现两行；解决靠唯一约束与冲突更新，不能靠跑完之后去重，因为去重时你无法判断该留哪一行。

还有一个坑：跨批的条数可以相加，比例不能。分母跨批，读数就失去意义。

三个可直接用的查询：条数、带链接、失败

三个查询我放在上面的代码块里，这里说清各自的读法。

第一个按平台出条数与带链接条数，分母取问法集里的计划条数，用来判断某一批在各个平台上的完成度。分母取计划条数这一条要写进注释，否则接手的人一定会改成行数。

第二个把总数、带链接、空回答、去重域名一次取齐，适合放在日报里。去重域名用正则截出主机名再计数，如果同一页面有多种写法，还要再加一层归一化，那属于另一个话题。

第三个把缺失、空回答、无地址分成三列，用来定位问题出在采集侧还是平台侧。三列同时抬头说明通道有问题，只有一列抬头说明是局部故障。

三个查询都带 run_id 过滤，这是硬要求。跨批看趋势时，用批次号分组再按时间排序，不要用时间范围过滤，因为采集可能中断补跑，时间范围会把补跑那一批切碎。

边界

我把这套表设计限定在单一批次的横向比较，不做时间序列建模。要看某个读数随批次怎么变，得在明细之上再建一层聚合表，按批次号与问法分组，那是另一件事。

它也不处理多值地址的完整展开。一条回答里三个地址，单值字段只留第一个，想要完整清单必须有关联表，这里只是留了位置，没有展开写。

数值层面只有计数，没有比例结论。四十五条也好，上万条也好，能算的是条数与去重数；比例要看分母怎么定，而分母的定法属于统计口径，不属于表设计。

数据源是公开可访问的回答正文，查询日 2026-10-28，样本为单批 45 条与累计 10,324 条两组，口径为固定问法集、平台维度分组、按批次号切开，局限在于本套表只支持描述性统计，不回答因果，也不承诺任何平台的返回结果。

本文含 AI 辅助生成内容，经人工核实。