题干与适用场景
使用 PostgreSQL 查询 2026 年第二季度每个品类销售额最高的前三档商品；并列第三名全部返回。请说明数据口径，写出 SQL，并解释 ROWNUMBER、RANK 与 DENSERANK 为什么会得到不同结果。
表结构如下：
orders( order_id bigint primary key, ordered_at timestamptz, status text )
order_items( order_id bigint, category_id bigint, product_id bigint, quantity integer, unit_price numeric(12, 2) )
本题采用以下已确认口径：只统计 COMPLETED 订单；季度按 UTC 的半开区间 [2026-04-01, 2026-07-01)；商品销售额为所有有效明细的 quantity * unit_price 之和；退款不在给定模型中；“前三”表示每个品类前三个不同销售额档位，所以第三档有并列商品时全部返回。没有有效销售的商品不出现，少于三档的品类返回现有全部档位。
这是一道面向数据分析、数据工程和需要编写分析 SQL 的后端岗位的中等难度题。2026 年公开 SQL 面试资料仍把每组 Top-N、窗口函数和并列处理列为独立练习。题目没有可核验的公司归属，因此按代表性 SQL 面试题处理。
面试官考察点
第一项是能否先说清输出粒度。原始 order_items 一行是一条订单明细，目标却是一行一个“品类—商品”。如果在明细粒度直接排名，同一商品会占据多个名次，结果即使语法通过也不回答业务问题。
第二项是能否把“前三”翻译成明确的并列语义。ROWNUMBER、RANK 和 DENSERANK 都能在品类内排序，但它们回答三个不同问题。强回答先确认要固定三行、竞赛式名次，还是前三个不同销售额档位，再选函数。
第三项是理解 SQL 的逻辑执行顺序。窗口函数在 WHERE、GROUP BY、HAVING 和普通聚合之后计算，因此要先聚合商品销售额，再在下一层排名，并在外层过滤排名。把窗口别名直接写进同层 WHERE 在 PostgreSQL 中不成立。
最后看结果是否可验证和可维护。强回答会定义时间边界、订单状态、金额类型、最终稳定排序和测试数据，并说明数据规模变大时先减少参与排名的行，而不是只说“加索引”。
回答前需要澄清的问题
- “前三”是三行、三个竞赛名次，还是三个不同金额档位？ 三行用
ROWNUMBER；竞赛式名次可用RANK；本题要求三个不同销售额档位并保留并列，因此用DENSERANK。 - 销售额按什么事件和时区归属？ 下单、付款、完成和退款时间会产生不同报表。这里使用
orders.ordered_at的 UTC 时间，并采用不重叠的半开区间。 - 哪些订单状态有效？ 若取消、测试或失败订单没有排除，聚合值会被高估。本题只统计
COMPLETED。 - 退款、折扣、税费和币种在哪里？ 当前模型只有数量与成交单价，无法推导退款或汇率。若面试官补充退款表或多币种字段，必须先净额化或换算到统一币种再排名。
- 一个商品能否跨品类？ 本题以明细上的
(categoryid, productid)为统计键；如果品类来自当前商品维表，历史订单可能被今天的分类重写，需改用订单时快照或有效期维表。 - 零销售商品是否要展示？ 本题不展示。若要展示，需要从品类—商品全集左连接聚合结果，并明确零值是否参与前三档。
- 最终输出是否要求确定顺序？ 排名只由销售额决定，不能把
productid塞进排名窗口破坏并列；展示层另用ORDER BY categoryid, revenuerank, productid保证稳定输出。
30 秒回答框架
“我先把 UTC 第二季度的完成订单聚合成一行一个品类—商品，再按品类分区、销售额降序使用 DENSERANK，因为题目要三个不同金额档位并保留并列。排名在 CTE 中计算，外层筛 revenuerank <= 3，最终排序只负责稳定展示。我会用并列金额、季度边界、取消订单和不足三档的品类验证。”
完整回答还应解释为什么先聚合、三种排名函数的差异，以及大数据量下怎样验证执行计划与报表口径。
分步骤深入解答
第一步：把业务问题写成输出粒度和不变量
目标结果的主键不是 orderid，而是 (categoryid, product_id)。每个键在聚合层只能出现一行，revenue 等于时间和状态过滤后所有对应明细金额之和。排名层不会改变这个粒度，只会给每一行增加品类内的档位。
可先写出三个不变量：每条有效明细只贡献一次；不同订单中的同一商品必须合并；任何品类的 revenue_rank 只由该品类内的销售额集合决定。它们能快速发现重复连接、明细级排名和全局排名三类错误。
第二步：先过滤事实，再聚合到商品粒度
时间条件使用 >= 起点和 < 下一季度起点。相比 BETWEEN 或写成 2026-06-30 23:59:59，半开区间不会遗漏更高精度时间，也能与下一季度无缝拼接。timestamptz 常量显式写出 +00，让口径不依赖数据库会话时区。
连接后先过滤订单状态与时间，再按 categoryid, productid 分组。quantity * unit_price 在当前 PostgreSQL 模型中得到精确数值类型，SUM 保留精确金额计算；不要为了方便转成浮点数。生产报表还需要币种和退款口径，但给定字段不能凭空补出这些事实。
第三步：按并列规则选择排名函数
假设某品类四个商品的销售额依次为 100, 100, 90, 80：
ROW_NUMBER给出1, 2, 3, 4，两个 100 的相对顺序若没有额外键则不确定；当前例子会返回两个 100 和一个 90，但若并列横跨第 3 行，它会任意保留其中一项。它保证三行，不保证并列完整。RANK给出1, 1, 3, 4；筛到 3 会返回金额 100 和 90，只覆盖两个不同金额档位。DENSE_RANK给出1, 1, 2, 3；筛到 3 会返回四个商品，正好覆盖前三个不同金额档位。
因此本题用 DENSERANK。窗口里的 ORDER BY 只能是 revenue DESC。若加入 productid，相同销售额的行不再是同一 peer group，所谓“保留并列”就被悄悄改成了人为拆分名次。
第四步：用两层 CTE 表达聚合、排名和过滤
完整 PostgreSQL 查询如下：
WITH product_revenue AS ( SELECT oi.category_id, oi.product_id, SUM(oi.quantity * oi.unit_price) AS revenue FROM orders AS o JOIN order_items AS oi ON oi.orderid = o.orderid WHERE o.status = 'COMPLETED' AND o.ordered_at >= TIMESTAMPTZ '2026-04-01 00:00:00+00' AND o.ordered_at < TIMESTAMPTZ '2026-07-01 00:00:00+00' GROUP BY oi.categoryid, oi.productid ), ranked AS ( SELECT category_id, product_id, revenue, DENSE_RANK() OVER ( PARTITION BY category_id ORDER BY revenue DESC ) AS revenue_rank FROM product_revenue ) SELECT categoryid, productid, revenue, revenue_rank FROM ranked WHERE revenue_rank <= 3 ORDER BY categoryid, revenuerank, product_id;
第一层把许多订单明细收敛为商品级事实；第二层只在各品类的商品集合中排序；最外层才看得到窗口结果并过滤。三层分别对应业务口径、排名规则和输出契约，调试时可以逐层检查行数与粒度。
第五步：解释正确性，不只展示语法
对任意品类，第一层为每个商品生成唯一总额。PARTITION BY categoryid 保证其他品类不会参与比较，ORDER BY revenue DESC 把相同总额定义为同一 peer group。DENSERANK 对 peer group 依次编号且不留空号，因此 <= 3 恰好选择前三个不同总额以及这些总额下的全部商品。
最终 ORDER BY 不参与排名，只决定返回行的展示顺序。这个区分很重要：排名窗口的排序定义业务名次，查询末尾的排序定义可重复呈现。两者可以使用不同键。
第六步：为规模与执行计划保留证据边界
若输入有 R 条有效明细、聚合后有 G 个品类—商品组合，逻辑工作包括扫描和聚合 R 行，以及在各品类内排序 G 行。排序量可写成各分区 Gc log Gc 的总和，但数据库可能选择哈希聚合、排序聚合、并行执行或磁盘溢写，不能把这个表达式冒充固定执行计划。
先用 EXPLAIN (ANALYZE, BUFFERS) 在生产规模副本验证过滤行数、连接方式、聚合行数、排序内存与临时文件。orders(status, orderedat, orderid) 和 orderitems(orderid) 可能帮助过滤与连接，是否有效取决于状态选择性、数据分布和分区方式。若季度明细巨大，按时间分区可减少扫描；若查询频繁，再考虑经过对账的日级商品汇总表。预聚合用新鲜度、回补和修正成本换查询速度，不能只写“建物化视图”。
第七步：用小数据证明语义，再用大数据证明代价
最小正确性夹具至少包含：品类 10 的金额 100, 100, 90, 80, 70，预期返回前四个商品且排名为 1, 1, 2, 3；品类 20 只有 50, 40 两档，预期全部返回；一个取消订单，预期不计入；一条恰好在 2026-07-01 00:00:00+00 的订单，预期排除。
再加入同一商品跨多个订单和多条明细，确认只输出一个商品总额；加入两个不同商品 ID 的相同总额，确认最终顺序稳定但名次相同。规模测试则核对扫描分区、聚合后行数、排序是否溢写、运行时间和资源峰值。正确结果与可接受代价是两套证据，不能互相替代。
高质量示范回答
“我会先确认‘前三’的并列规则、时间口径和有效订单状态。这里要求每个品类前三个不同销售额档位，第三档并列全部返回，所以我选 DENSERANK；若只要固定三行才会用 ROWNUMBER。
目标粒度是一行一个品类—商品，而原表是一行订单明细。因此第一层 CTE 连接订单和明细，只保留 UTC 第二季度的 COMPLETED 订单，再按 categoryid, productid 聚合 SUM(quantity * unit_price)。时间用大于等于 4 月 1 日、小于 7 月 1 日，避免季度边界重叠。
第二层在聚合结果上做 DENSERANK() OVER (PARTITION BY categoryid ORDER BY revenue DESC)。排名窗口不能加入 productid，否则相同金额不再并列。PostgreSQL 也不能在同层 WHERE 使用窗口结果，所以最外层筛 revenuerank <= 3，最后再按品类、排名和商品 ID 排序，稳定展示。
正确性上，金额 100, 100, 90, 80 会得到 1, 1, 2, 3，四个商品都返回。测试还要覆盖取消订单、季度右边界、同一商品多条明细和不足三档的品类。数据量大时我会先看过滤与聚合把 R 行缩到多少个商品组合，再用执行计划判断分区裁剪、索引、排序溢写或预聚合是否真的需要。”
这段回答先确定业务语义，再给查询结构、正确性证据和性能验证。它没有把函数名当答案，也没有用未经测量的索引建议代替执行计划。
常见错误
- 在订单明细上直接排名 → 同一商品占据多个名次，输出粒度错误 → 先按品类和商品聚合，再排名聚合结果。
- 使用全局
ORDER BY ... LIMIT 3→ 只返回全表前三，不是每个品类前三 → 用PARTITION BY category_id建立独立排名。 - 未确认并列规则就选
ROW_NUMBER→ 并列商品可能被任意截断 → 先定义三行、竞赛名次或三档金额，再选函数。 - 把
productid加进DENSERANK的窗口排序 → 相同销售额被拆成不同 peer group → 窗口只按业务排名键排序，展示稳定性放到最终ORDER BY。 - 同层
WHERE revenue_rank <= 3→ 窗口函数尚未计算，PostgreSQL 无法引用 → 在 CTE 或子查询中排名，外层过滤。 - 季度结束写成 6 月 30 日 23:59:59 → 更高精度时间可能遗漏，下一周期也难拼接 → 使用
[start, next_start)半开区间。 - 连接商品当前维表取得品类却不说明历史语义 → 商品改类会重写历史归属 → 使用订单时快照或有效期维表，并声明口径。
- 只建议加索引 → 索引可能因低选择性无效，也不解决错误粒度 → 先验证行数、计划、排序溢写和实际瓶颈。
- 用浮点数累计金额 → 舍入误差可能改变并列关系 → 保留精确数值类型并定义币种与净额规则。
追问及应对
追问一：如果业务只允许每个品类返回恰好三行呢？
改用 ROWNUMBER，并为完全相同销售额定义可解释且确定的次级顺序，例如 productid ASC 或业务明确的销量、首次上架时间。这个规则会主动打破并列，因此必须写进输出契约。品类不足三个商品时仍只能返回现有行，除非业务要求补占位符。
追问二：如果“前三名”采用竞赛排名呢？
使用 RANK。金额 100, 100, 90, 80 会得到 1, 1, 3, 4，筛 <= 3 返回 100 和 90 两档。它保留并列后跳号，适合“下一名是第三名”的语义；与 DENSE_RANK 的前三个不同金额档位不是同一需求。
追问三：能否不用 CTE，直接在 HAVING 中筛排名？
不能在同一查询层的 HAVING 或 WHERE 中筛窗口结果，因为窗口函数逻辑上晚于这些子句。可以改成等价的子查询；某些仓库支持 QUALIFY，但 PostgreSQL 18 没有该子句。本题使用 CTE 是为了让聚合、排名和过滤边界可见。
追问四：退款发生在下一季度怎么办？
先定义财务口径。订单归属报表可能把退款回写原订单季度；现金流报表可能按退款发生季度记录负数。两者需要不同事件时间和事实表。不能只在当前查询里减一个没有时间与金额字段的退款表；应建立可追溯的订单、退款事实并声明重算或调整策略。
追问五：十亿条季度明细怎样优化？
先用日期分区裁剪和状态过滤减少扫描，再确认连接键索引、聚合并行度和排序是否溢写。若报表频繁且允许延迟，可维护按天、品类、商品的精确汇总，查询季度时再求和排名；迟到订单、退款和修正必须能幂等回补并与原始事实对账。只有执行计划和代表性负载能决定索引、分区或预聚合哪项有效。
追问六：如何发现连接导致销售额翻倍？
在每一层检查粒度与守恒关系：有效明细连接前后行数是否符合基数，order_id 在订单表是否唯一，商品聚合金额之和是否等于过滤后明细金额之和。再构造一个订单含两条明细、维表含重复键的反例。若连接到非唯一维度，先按有效期和版本选出唯一记录，不能用最终 DISTINCT 掩盖重复贡献。