4 篇博文 含有标签「SQL」

在把按天返回的广告数据聚合成周报时，PPC、CPM、ROI 等比率指标暴涨几十倍——PPC 从日粒度实测的 4.70 变成了周报表里的 111.39。

在开发 AI运营 时遇到此问题——基于大语言模型的智能分析，自动洞察市场趋势、用户行为、销售数据，提供精准运营策略。广告数据接口按天返回明细行，入库前要聚合成周粒度供报表消费。聚合上线后首查周报：PPC 111.39（日实测 4.70）、CPM 1585.9（日实测 68.6）、ROI 169.98（日实测 1.82）——13 个比率/均值列全部失真。

TL;DR​

聚合查询里对 CTR、PPC、ROI 这类比率/均值列直接 SUM，得到的是「每天比率的总和」而不是均值，数值按聚合天数成倍放大。两条规则：聚合时只加总可加列（展现、点击、花费等总量），跳过所有比率列；聚合后用总量重算比率（花费÷点击、点击÷展现）。如果手里只有比率没有总量，用分母作权重做加权平均，绝不简单平均。

问题现象​

聚合代码对数值列「全部 SUM」，比率列被静默带进去：

-- 错误写法：所有列都 SUM
SELECT
  campaign_id,
  date_trunc('week', day) AS week,
  SUM(clicks)      AS clicks,
  SUM(impressions) AS impressions,
  SUM(ppc)         AS ppc,   -- 7 天日 PPC 之和！
  SUM(ctr)         AS ctr,   -- 7 天日 CTR 之和！
  SUM(roi)         AS roi    -- 7 天日 ROI 之和！
FROM daily_ad_report
GROUP BY campaign_id, date_trunc('week', day);

实测对比（某计划单周）：

没有任何报错，数据照常入库，报表照常渲染——只有把周报和日明细并排对比，才能发现数值差了一到两个数量级。

根因​

比率与均值是不可加的派生量。 ppc = cost / clicks 的分母每天不同，把 7 天的日 PPC 直接相加，数学上得到的是「7 个相对值的和」，没有任何业务含义。CTR、ROI 同理。

「全部数值列求和」是静默陷阱。 聚合代码通常按列循环统一处理，比率列混在其中不报错、不告警，只是结果悄悄失真。列越多、比率列占比越高，越难肉眼发现。

ROI 放大 93 倍反而更具迷惑性。 它看起来像「投放效果极好」，如果下游直接消费周报做预算决策，错误的数字会一路传到运营动作里。这次事故里，下游还有只读消费方直接读这张周表——表值修对之前，所有消费方都在读错数据。

解决方案​

第一步：聚合只保留可加列​

CREATE VIEW weekly_ad_totals AS
SELECT
  campaign_id,
  date_trunc('week', day) AS week,
  SUM(impressions) AS impressions,
  SUM(clicks)      AS clicks,
  SUM(cost)        AS cost,
  SUM(gmv)         AS gmv
FROM daily_ad_report
GROUP BY campaign_id, date_trunc('week', day);

可加列的特征：它们是「计数/总量」（展现、点击、花费、订单数），跨时间区间相加仍有意义。

第二步：聚合后用总量统一重算比率​

SELECT
  campaign_id,
  week,
  impressions,
  clicks,
  cost,
  CASE WHEN clicks > 0
       THEN cost / NULLIF(clicks, 0)::numeric
       ELSE 0 END AS ppc,
  CASE WHEN impressions > 0
       THEN clicks::numeric / NULLIF(impressions, 0)
       ELSE 0 END AS ctr,
  CASE WHEN cost > 0
       THEN (gmv - cost)::numeric / NULLIF(cost, 0)
       ELSE 0 END AS roi
FROM weekly_ad_totals;

两个细节：PostgreSQL 整数除法会截断，除法前先 ::numeric；分母为 0 统一返回 0，保持与明细层口径一致。

第三步：只有比率、拿不到总量时用加权平均​

-- 用展现量加权聚合日 CTR（展开式：SUM(ctr × impressions) / SUM(impressions)）
SELECT
  date_trunc('week', day) AS week,
  SUM(clicks)::numeric / NULLIF(SUM(impressions), 0) AS ctr_weighted
FROM daily_ad_report
GROUP BY date_trunc('week', day);

加权平均的本质就是「还原分子分母再相除」——只要还拿得到权重列，就永远优先于简单平均。

改完后周报 13 个比率列全部与日明细实测一致，历史脏数据用同一套公式回填，下游只读消费方不改一行代码自动变对。

常见问题​

百分比可以直接 SUM 吗？​

不能。百分比/比率是相对值，各行分母不同，直接 SUM 得到的是 N 个相对值之和，按聚合天数放大且无业务含义。正确做法是聚合时跳过比率列，聚合后用总量重算（点击÷展现、花费÷点击）；只有比率没有总量时，用分母作权重做加权平均。

百分比能加起来求平均吗？​

只有分母相同时才可以。分母不同的百分比直接平均等于不加权平均，结果偏向分母小的项（小流量日的极端比率会被放大）。正确做法是分别加总分子和分母再相除，数学上等价于以分母为权重的加权平均。

排查一个数据看板线索：某广告 offer 在「诊断明细」里被判疑似停投、并附了再投资建议，点进「建议投放」清单却查无此 offer——明细页指着一张清单，清单里没有这行。

在开发 AI运营 时遇到此问题——基于大语言模型的智能分析平台，自动洞察市场趋势、用户行为与销售数据；诊断明细与建议清单分别来自管道里两个相邻查询的产出。

TL;DR​

两个查询对同一个业务谓词（「疑似停投」）用了不同的聚合粒度：明细查询按 计划×offer 粒度判定——任一计划末周无消耗即判疑似；候选清单按 offer 整体粒度判定——挂着的全部计划末周均无消耗才入选。某 offer 在其中一个计划末周仍有消耗，于是「明细判疑似、汇总不入选」，呈现层指向的明细悬空。修法三件套：口径统一收紧到整体粒度、两侧判定 CTE 逐字同源、引用对方行集前校验包含关系。

问题现象​

两个查询各自定义「疑似停投」：

-- 查询④ 诊断明细：pair 粒度（计划 × offer）
WITH consumption_paused_q4 AS (
  SELECT offer_id, campaign_id
  FROM spend_weekly
  GROUP BY offer_id, campaign_id        -- 每个计划单独看
  HAVING SUM(spend) FILTER (WHERE is_last_week) = 0
)

-- 查询⑤ 建议投放候选：offer 粒度（跨全部计划汇总）
WITH suspected_paused_q5 AS (
  SELECT offer_id
  FROM spend_weekly
  GROUP BY offer_id                     -- 整个 offer 一起看
  HAVING SUM(spend) FILTER (WHERE is_last_week) = 0
)

出问题的 offer 挂在多个计划下，其中一个计划末周仍有消耗（3.09）：

查询④（pair 粒度）：  计划A 末周消耗 0     → 判疑似 ✓，并给出建议
查询⑤（offer 粒度）： 跨计划汇总末周消耗 3.09 → 不入选 ✗
呈现层：明细页说「见建议投放表」，建议投放表里没有它

任务不报错、行数对得上大数，悬空只有顺着某条明细点过去才会撞见。

根因​

「同源」契约有覆盖盲区。管道规范要求相邻查询的特征列 CTE 逐字同源——这条契约被严格遵守了；但它只约束特征列，判定集（WHERE 之前的那个业务谓词）不在契约范围内。「疑似停投」在两个查询里被独立实现了两次，粒度不同：pair 级判定对「单个计划内无消耗」敏感，offer 级判定对「所有计划都无消耗」敏感。同一个 offer 两种结论，数学上必然存在交叉带。

跨查询引用把分叉放大成了悬空：呈现层拿查询④的行当明细、查询⑤的表做入口，却没人校验过「④的行集 ⊆ ⑤的行集」。聚合口径不一致是数据仓库最经典的一致性陷阱之一——此前写过的 SUM 比率列导致聚合后指标暴涨是它的另一种形态：聚合发生在了错误的层级上。

解决方案​

步骤 1：先定口径，再写 SQL​

业务问题只有一个答案：「这个 offer 还投不投」是 offer 级决策，判定就该收紧到整体粒度——挂着的全部计划末周均无消耗、且无任何运营标注行，才判疑似。口径变更升 rule_version，可追溯。

步骤 2：判定集 CTE 逐字同源​

把判定 CTE 抽成同一段文本，两个查询直接引用；契约同步升级：逐字同源覆盖判定集（含粒度），不止特征列。从此同一谓词只有一处定义，改口径只改一处。

步骤 3：跨查询引用前，校验行集包含关系​

把「标记集 ⊆ 对方行集」做成固定校验（可入库为测试）：

-- 悬空检测：④ 判了疑似、⑤ 却查无此行
SELECT q4.offer_id
FROM consumption_paused_q4 q4
LEFT JOIN suspected_paused_q5 q5 USING (offer_id)
WHERE q5.offer_id IS NULL;

这条查询返回 0 行，呈现层才有资格把两个产出拼在同一张页面上。

步骤 4：重放验证后上线​

新口径对历史快照重放：逐行核对翻转方向（疑似↔正常）全部正确、零误伤、零悬空，再重跑生产验证通过，才完成收口。

常见问题​

为什么两条 SQL 对同一份数据给出不同结果？​

常见三个差异：聚合粒度（GROUP BY 维度不同——本例的计划×offer 与 offer 整体）、过滤口径（WHERE/HAVING 条件不同）、取数时点（查询时间不同）。粒度分叉最隐蔽：两条 SQL 各自都对，结论却可以相反。

SQL 的 HAVING 和 WHERE 在聚合判定里怎么选？​

WHERE 在分组前过滤行，HAVING 在分组后过滤组。但选对关键字之前先选对粒度——「以什么为一组」决定了判定的灵敏度：粒度越细越容易命中（单组满足即判），越粗越保守（全部满足才判）。两条 SQL 粒度不同，判定结论就可能相反。

如何避免报表之间的数据口径不一致？​

同一业务谓词只在一处定义，判定 CTE 多查询逐字同源；跨查询引用对方行集前跑包含关系校验（A ⊆ B）；口径变更升版本号并对历史数据重放验证。一致性不靠约定俗成，靠契约加校验。

在一次广告数据源从日表切换到周表后，我发现 schema 里还躺着一张只在最早期的 migration 里建过、0 行数据、运行时代码从不引用的空表——还带着过时的字段名和没规范化的中文列。

在开发 AI运营 时遇到此问题——基于大语言模型的智能分析，自动洞察市场趋势、用户行为、销售数据，提供精准运营策略。这次数据源切换后，写入端早已迁到新的周表，旧日表的清理 migration 也补了，唯独一张只在 baseline 里 CREATE 过的月表被遗漏——它既没有对应的新写入，也没有 DROP，就那样潜伏在 schema 里，带着早已废弃的字段定义。

TL;DR​

废弃表的典型特征：只在早期/baseline migration 里 CREATE、当前代码 0 引用、常带旧字段或未规范化的列名。批量迁移时它们不会被自动处理，需要主动用 information_schema.tables 列出 schema 全表，再与代码引用比对，定位 orphan 表后写一条 DROP migration 清理——而不是手动 psql 删完就了事。

问题现象​

一张典型的废弃表长这样：

• 0 行数据——业务早已不再写入它；
• 0 运行时引用——代码里 grep 不到任何 SELECT/INSERT，只剩 migration 文件里的 CREATE；
• 旧字段残留——字段名是上一版命名（如 ad_plan_id/product_id），与当前规范不一致；
• 未规范化的列名——甚至还有中文列名没来得及改。

它不报错、不影响线上运行，所以从「线上没出问题」的视角完全无感。但它的危害是隐性的：误导后来者以为它仍在用、占用 schema 命名空间、在跨表审计时制造噪音，还可能被某个误判的 SELECT * 意外读到脏数据。

根因​

数据库迁移有一个普遍的模式：migration 是「加法」的。

一次数据源切换通常这样演进：

1. 早期 baseline migration CREATE 了一批表（日表、月表）；
2. 业务跑通后，写入端开始依赖这些表；
3. 需求变化，引入新表（周表），写入端逐步迁移过去；
4. 旧表的写入停了，补一条 migration DROP 旧日表；
5. 但月表/其他只在 baseline 建过、从未被写入端直接引用的表，没有对应的 DROP。

问题出在第 5 步：迁移注意力集中在「现在用到的表」上——哪些表在写入、哪些 SQL 在查。而「曾经存在、但从未进入主链路」的表既不在写入端、也不在查询端，自然不会触发任何 DROP，于是成了 orphan。这类残留和 Airflow 删除 DAG 后元数据残留 是同一类问题：「删了入口、忘了清结构」，是迁移类问题的高发区。

解决方案​

核心流程：列全表 → 比对引用 → 确认空表 → 写 migration DROP → 验证。

步骤 1：用 information_schema 列出 schema 下所有基础表​

-- 列出某 schema 下所有基础表（排除视图）
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'your_schema'
  AND table_type = 'BASE TABLE'
ORDER BY table_name;

information_schema.tables 是 SQL 标准目录视图，跨 PostgreSQL/MySQL/SQL Server 通用，字段稳定，非常适合写进审计脚本。

步骤 2：grep 代码库确认运行时引用​

对每张候选表，在代码库里搜索引用，排除 migration 文件本身：

# 搜索运行时代码引用，排除 migrations 目录
grep -rn "ad_product_monthly_stats" src/ --include="*.py" \
  | grep -v "migrations/"
# 0 行输出 → 运行时无引用，进入候选

0 引用是判定 orphan 的关键证据。注意一定要排除 migration 目录——baseline 里的 CREATE 不算「引用」。

步骤 3：确认是空表​

SELECT count(*) FROM your_schema.ad_product_monthly_stats;
-- 0 → 确认无数据，可安全清理

对有数据的表要格外谨慎：先确认它真的废弃（而非只是近期没写入），有疑问就先做逻辑备份再处理。

步骤 4：写一条 migration DROP（而非手动删）​

-- db-migrations/{project}/027_drop_ad_product_monthly_stats.sql
DROP TABLE IF EXISTS your_schema.ad_product_monthly_stats;

务必走 migration 文件：它会被版本控制、在所有环境（开发/预发/生产）一致重放，留下审计轨迹。手动 psql 删一次，换台机器就又长回来了。

步骤 5：验证已删除​

SELECT to_regclass('your_schema.ad_product_monthly_stats');
-- 返回 NULL 表示表已不存在

to_regclass() 是验证表是否存在的标准手段，返回 NULL 即确认删除成功。

批量审计：按前缀一次性排查同类遗漏​

单张表清掉后，按前缀把同类表全部列出来逐个核对，避免「清了一张、漏了兄弟」：

-- 列出某前缀下所有表，逐个走 步骤2-5
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'your_schema'
  AND table_name LIKE 'ad_%'
ORDER BY table_name;

常见问题​

怎么列出 PostgreSQL 数据库中的所有表？​

查 information_schema.tables，过滤 table_schema 和 table_type = 'BASE TABLE'，即可列出某 schema 下所有基础表。它比 psql 的 \dt 更适合写进脚本做自动化审计，且是 SQL 标准、跨数据库通用，代码可移植性更好。

PostgreSQL 怎么找出没被使用的废弃表？​

用 information_schema.tables 列出全部表，再与代码库或查询日志的引用做比对，运行时代码 0 引用且无写入的表即为废弃候选；空表可进一步用 SELECT count(*) 确认行数，确认无数据、无外键依赖后再写 migration DROP 清理。

information_schema 和 pg_catalog 有什么区别？​

information_schema 是 SQL 标准定义的目录视图，跨 PostgreSQL/MySQL/SQL Server 通用、字段稳定不易变，适合写可移植的审计脚本；pg_catalog 是 PostgreSQL 专有目录，信息更全更细（如精确行数估算、存储细节），但版本间可能调整。做通用 schema 审计优先用 information_schema。

在为某张表收紧唯一键（移除一个不再区分数据的列）之后，原本正常的 UPSERT 写入立刻批量报错——there is no unique or exclusion constraint matching the ON CONFLICT specification。

在开发 AI运营 时遇到此问题——基于大语言模型的智能分析，自动洞察市场趋势、用户行为、销售数据，提供精准运营策略。

TL;DR​

PostgreSQL 的 ON CONFLICT (cols) 要求 cols 精确匹配一个已存在的唯一约束或唯一索引（列与顺序都要一致，否则错误码 42P10）。一旦你 ALTER 了唯一键，所有引用它的 INSERT ... ON CONFLICT 必须同步修改；而且 migration 跑完后写入端要立刻部署，中间窗口会持续报错。

问题现象​

唯一键改造一上线，定时导入任务全量失败，写库日志只剩这一条：

ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
SQL state: 42P10

业务表 0 行写入，但同一张表的其它纯 SELECT 查询完全正常——问题只出在带 ON CONFLICT 的写入路径上。

根因​

ON CONFLICT (cols) 里指定的列集叫仲裁器（arbiter）。PostgreSQL 要求它精确匹配表上某个 UNIQUE 约束或唯一索引：

• 列的集合必须相同；
• 列的顺序也要相同；
• 如果是带 WHERE 的部分唯一索引（partial unique index），ON CONFLICT 还要带上相同的 WHERE。

找不到匹配项时，PostgreSQL 不知道用哪个索引来判断"冲突"，于是抛出 42P10。

典型触发场景是收缩唯一键：原先唯一键含 3 列，你发现其中一列（比如 audience）的 4 个取值对应的指标行 100% 全等、纯属冗余，于是把唯一键降到 2 列。这是对的优化方向，但旧的 INSERT 仍写着 ON CONFLICT (c1, c2, c3)，而表上只剩 (c1, c2) 的唯一约束——仲裁器找不到落点，报错。

旧唯一键: UNIQUE (store_id, metric_key, audience)
新唯一键: UNIQUE (store_id, metric_key)

旧 INSERT: ON CONFLICT (store_id, metric_key, audience)   ← 找不到匹配

解决方案​

下面是最小复现，建表、触发、修复一条龙，可直接在 psql 里跑：

-- 1. 带 3 列唯一键的表
CREATE TABLE daily_metric (
    store_id   TEXT NOT NULL,
    metric_key TEXT NOT NULL,
    audience   TEXT NOT NULL,
    value      NUMERIC,
    CONSTRAINT daily_metric_unique UNIQUE (store_id, metric_key, audience)
);

-- 2. 旧 UPSERT：ON CONFLICT 含 audience
INSERT INTO daily_metric (store_id, metric_key, audience, value)
VALUES ('s1', 'revenue', 'visitor', 100)
ON CONFLICT (store_id, metric_key, audience)
DO UPDATE SET value = EXCLUDED.value;

-- 3. 收缩唯一键：移除 audience
ALTER TABLE daily_metric
    DROP CONSTRAINT daily_metric_unique,
    ADD  CONSTRAINT daily_metric_unique_new UNIQUE (store_id, metric_key);

-- 4. 再跑第 2 步的 INSERT，立刻报错 ↓
-- ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

修复就是把 INSERT 的 ON CONFLICT 列同步收缩到 2 列；既然 audience 不再区分数据，写入端干脆把它的值固定为字面量，避免按入参凭空拼出多行：

INSERT INTO daily_metric (store_id, metric_key, audience, value)
VALUES ('s1', 'revenue', 'visitor', 100)
ON CONFLICT (store_id, metric_key)         -- ← 同步收缩
DO UPDATE SET value = EXCLUDED.value;

真正容易踩的是部署顺序，不是 SQL 本身：

1. 先发 migration（DROP 旧约束 + ADD 新约束）；
2. 紧接着发布写入端代码（INSERT 的 ON CONFLICT 改为 2 列）；
3. 两步之间不要留间隔——旧代码撞新 schema 必报 42P10，新代码撞旧 schema 同样报 42P10（找不到 2 列的唯一约束）。

如果你用 Drizzle 这类 ORM，ON CONFLICT 的列一旦在 sql 模板里写死，改 schema 时极易漏改——schema 与写入端不同步的代价，在另一篇 Drizzle + PostgreSQL 的坑里也领教过。

注意事项​

常见问题​

PostgreSQL ON CONFLICT 必须有唯一约束吗？​

只有指定列时才必须。ON CONFLICT (cols) 的 cols 要精确对应一个已存在的 UNIQUE 约束或唯一索引，否则报 42P10。如果你只想"有任何冲突就跳过"、不关心具体哪个约束，用不带列的 ON CONFLICT DO NOTHING，它不需要匹配特定索引。

PostgreSQL ON CONFLICT 可以指定多个唯一约束吗？​

不能。单条 INSERT 的 ON CONFLICT 只能指定一个仲裁约束（一个列集，或一个索引名）。表上可以有多个唯一键，但一条语句只能选其一做冲突判定。需要按不同唯一键分别处理时，要么拆成多次写入，要么在应用层先查再决定 INSERT 还是 UPDATE。

报 there is no unique or exclusion constraint matching the ON CONFLICT specification 怎么办？​

这是错误码 42P10，含义是 ON CONFLICT 指定的列集在表上找不到匹配的唯一索引。按顺序排查：确认存在覆盖这些列的 UNIQUE 约束、列与顺序完全一致、最近改过唯一键后 INSERT 已同步更新；如果用的是带 WHERE 的部分唯一索引，ON CONFLICT 还要补上相同的 WHERE 子句。