DAY 54 W8 · 把故事讲出去：面试冲刺 测评

限时手写 ②：多表连接与子查询专场

第二场手写专项：多表、子查询、集合运算。

学 0 min
练 70 min
盘 50 min
共 120 分钟

规则

  1. 01同样 60 分钟 8 题，闭卷手写

练 · 120 min

  1. 60 分钟闭卷手写 8 题（多表 + 子查询 + 集合运算）
    参考答案

    多表题第一步永远是画连接链（哪几张表、按什么键、几对几）；集合运算题先确认两边的输出列同型。反连接三写法的口径差异要能口述：NOT IN 怕 NULL，另两个不怕。

    -- 三大家族题型示例：
-- ① 多表聚合报表（先画连接链，再写 FROM）
-- ② 反连接 / 半连接（EXISTS、LEFT JOIN ... IS NULL、IN 三写法）
-- ③ 集合运算（UNION / UNION ALL / EXCEPT / INTERSECT）

-- ① 按一级类目统计 GMV（明细 -> 商品 -> 二级类目 -> 一级类目连三级）
select top.name as 一级类目,
       sum(oi.qty * oi.unit_price) as gmv
from order_items oi
join products p on p.id = oi.product_id
join categories c on c.id = p.category_id
join categories top on top.id = coalesce(c.parent_id, c.id)
group by 1
order by gmv desc;

-- ② 从未下过单的用户（两种等价写法；NOT IN 遇 user_id 为 NULL 会整题翻车）
select u.id from users u
where not exists (select 1 from orders o where o.user_id = u.id);

select u.id from users u
left join orders o on o.user_id = u.id
where o.id is null;

-- ③ 集合运算：近 30 天下过单、却从未登录过的用户
select distinct user_id from orders
where created_at >= current_date - 30
except
select user_id from user_logins
where login_at >= current_date - 30;
  2. 上机验证并订正
    参考答案

    验证顺序：先用宽松条件 count 核对量级（多表连接少一条 join 条件行数会暴涨，量级立刻暴露），再抽查具体行；集合运算题把两半分别跑一遍，确认差集真的该差。

  3. 把 D53 和今天的错题合并成一张「临场易错清单」
    参考答案

    清单条目四件套：场景 -> 错误写法 -> 正确写法 -> 触发词。只收 D53 和今天两场里真错过的题，不收「可能会错」的--那是知识大全，不是易错清单。

  4. 清单控制在一页纸内，面试前 10 分钟只看它
    参考答案

    一页纸 = 最多 15 条。超过就先砍只错过一次的：清单越长，面试前 10 分钟越看不完，等于没有。

  5. 对着清单再默写一遍最容易错的 3 条
    参考答案

    默写标准是白板级别：不运行、不看任何提示直接写对。最该进前三的候选：NOT IN 遇 NULL、left join 后 count 错列（该 count 右表主键）、窗口函数结果做筛选必须包子查询。

过关标准 8 题一次通过 ≥ 6 题；易错清单成稿。

今日复盘

  1. D+1 默写 D53 限时手写 ①：窗口函数专场
    60 分钟闭卷手写 8 题（从 D27 的题型里随机抽）
    参考答案

    先判断题型属于哪个家族，SQL 就是框架默写--这是限时手写的全部秘密。口述模板：子查询排好编号 / 算好累计，外层再筛。

    -- D27 的题型基本落在四个家族，闭卷时先认题型再套框架：
-- ① 分组内编号（每组 TOP N / 第 N 个）② 连续区间（差值分组法）
-- ③ 累计（sum over）④ 偏移（lag/lead，如环比、连续 3 天上涨）

-- ① 每个用户消费额前 3 的订单
select id, user_id, total_amount
from (select id, user_id, total_amount,
             row_number() over (partition by user_id order by total_amount desc) as rn
      from orders) t
where rn <= 3;

-- ② 连续登录 7 天及以上的用户（日期 - 编号，连续的日子里这个差值不变）
select user_id
from (select user_id,
             login_at::date - (row_number() over (partition by user_id order by login_at::date))::int as grp
      from (select distinct user_id, login_at::date from user_logins) t) g
group by user_id, grp
having count(*) >= 7;

-- ③ 贡献 80% GMV 的头部用户（累计占比）
with u as (select user_id, sum(total_amount) as gmv from orders where status = 2 group by 1)
select user_id, gmv
from (select user_id, gmv,
             sum(gmv) over (order by gmv desc) * 100.0 / sum(gmv) over () as 累计占比
      from u) t
where 累计占比 <= 80;

-- ④ 每个用户的第 2 笔订单
select id, user_id, created_at
from (select id, user_id, created_at,
             row_number() over (partition by user_id order by created_at) as rn
      from orders) t
where rn = 2;
  2. D+3 重做 D51 八股 ②：事务、隔离级别、锁、MVCC 20 问
    整理 20 题并写口述稿
    参考答案

    题单方向：ACID 各自靠什么实现（原子性靠 WAL、隔离靠 MVCC+锁）；四个隔离级别 × 三种异常的矩阵；脏读 / 不可重复读 / 幻读各是什么（幻读强调「查询结果的集合变了」）；PG 默认 RC；PG 的 RR 靠快照、MySQL 的 RR 靠间隙锁；MVCC 的 xmin/xmax 与死元组回收；死锁四条件；乐观锁 vs 悲观锁。「幻读」和「两库 RR 差异」这两题单独各写一版 90 秒稿。

  3. D+7 口头解释 D47 大促前的代码评审：反模式清点
    N+1 查询、SELECT *、大事务、无限制 IN 列表
    易错点

    N+1 的识别：应用日志里短时间海量<b>相同模板</b>语句；ORM 的懒加载是重灾区，列表场景要显式预取。