
Hello,我是小兔大白糖。 这篇整理面试口述与高频追问要点,方便对照站内同主题通识文。
正文
定位:面试前 2 小时速查 + 白板现场填空式复用。 原则:每道题都是 大型加密货币交易所/Risk 真实业务场景,背 schema + 套模板即可。 方言:MySQL 8.0+ / StarRocks / ClickHouse 兼容写法,避免 PG 独有语法。
目录
- 窗口函数 — 交易对成交量分层排名
- CTE — 递归查询邀请关系链与返佣层级
- CTE — 非递归多步 KYC 状态聚合
- JOIN 优化 — 成交明细关联盘口快照,避免笛卡尔积
- Top-N per Group — 每个用户每日成交最活跃的前 3 个交易对
- 连续 streak — 连续登录天数检测(风险评分模型输入)
- 留存分析 — 新注册用户 Day-1 / Day-7 / Day-30 交易留存
- 漏斗分析 — KYC 认证五步转化与流失
- 移动平均 / 累积和 — 用户近 7 日提现金额滑动窗口与异常检测
- 重复检测与去重 — 提现请求重复提交识别与幂等去重
- 时序聚合 — 每小时成交额 rollup 与补零
- 面试现场建议
- 常见追问应对
Q1: 窗口函数 — 交易对成交量分层排名
Problem Statement
"给定
trade_statistics_daily表,请按交易对 (symbol) 的日成交额 (quote_volume) 进行: (a) 不分组全局排名; (b) 按market_type(SPOT / FUTURES) 分组排名; (c) 将每个市场类型的交易对按成交量划分为 4 个分位桶。 要求同时展示ROW_NUMBER、RANK、DENSE_RANK的区别。"
Table Schema
CREATE TABLE trade_statistics_daily (
stat_date DATE NOT NULL COMMENT '统计日期',
symbol VARCHAR(32) NOT NULL COMMENT '交易对,如 BTCUSDT',
market_type VARCHAR(16) NOT NULL COMMENT 'SPOT / FUTURES / MARGIN',
quote_volume DECIMAL(38,8) NOT NULL COMMENT '日成交额(计价币)',
trade_count BIGINT NOT NULL COMMENT '日成交笔数',
-- 生产环境通常还有分区与 TTL
PRIMARY KEY (stat_date, symbol, market_type)
) ENGINE=InnoDB
PARTITION BY RANGE COLUMNS(stat_date) (
PARTITION p202401 VALUES LESS THAN ('2024-02-01'),
PARTITION p202402 VALUES LESS THAN ('2024-03-01')
)
COMMENT='日级交易对统计,保留 90 天';Sample Data
INSERT INTO trade_statistics_daily VALUES
('2024-06-01', 'BTCUSDT', 'SPOT', 450000000.00, 1200000),
('2024-06-01', 'ETHUSDT', 'SPOT', 280000000.00, 900000),
('2024-06-01', 'BNBUSDT', 'SPOT', 120000000.00, 600000),
('2024-06-01', 'BTCUSDT', 'FUTURES', 890000000.00, 2100000),
('2024-06-01', 'ETHUSDT', 'FUTURES', 560000000.00, 1800000),
('2024-06-01', 'SOLUSDT', 'FUTURES', 560000000.00, 1500000), -- ETH 与 SOL 成交量相同
('2024-06-01', 'DOGEUSDT', 'SPOT', 80000000.00, 400000);Complete SQL Answer
-- ============================================================
-- (a)(b) 全局排名 + 分组排名,同时对比三种排名函数
-- ============================================================
SELECT
stat_date,
symbol,
market_type,
quote_volume,
-- 1) 全局 ROW_NUMBER:严格连续,无并列,1,2,3,4...
ROW_NUMBER() OVER (ORDER BY quote_volume DESC) AS global_rn,
-- 2) 全局 RANK:并列跳号,1,2,2,4...
RANK() OVER (ORDER BY quote_volume DESC) AS global_rk,
-- 3) 全局 DENSE_RANK:并列不跳号,1,2,2,3...
DENSE_RANK() OVER (ORDER BY quote_volume DESC) AS global_dr,
-- 4) 市场类型内 ROW_NUMBER
ROW_NUMBER() OVER (PARTITION BY market_type ORDER BY quote_volume DESC) AS type_rn,
-- 5) 市场类型内 RANK
RANK() OVER (PARTITION BY market_type ORDER BY quote_volume DESC) AS type_rk
FROM trade_statistics_daily
WHERE stat_date = '2024-06-01'
ORDER BY market_type, type_rn;
-- ============================================================
-- (c) NTILE(4) 分位桶 —— 每个 market_type 分成 4 档
-- ============================================================
SELECT
stat_date,
symbol,
market_type,
quote_volume,
NTILE(4) OVER (PARTITION BY market_type ORDER BY quote_volume DESC) AS quartile_bucket
FROM trade_statistics_daily
WHERE stat_date = '2024-06-01'
ORDER BY market_type, quartile_bucket, quote_volume DESC;Common Mistakes / Anti-Patterns
| 错误 | 原因 |
|---|---|
ORDER BY quote_volume 忘写 DESC | 默认 ASC,导致排名结果完全相反 |
RANK() 与 DENSE_RANK() 选错 | 需要"不跳号连续等级"时用 DENSE_RANK;需要"真实百分比位置"时用 RANK |
NTILE(4) 在数据量 < 4 时使用 | 每桶至少 1 行,桶数 > 行数时部分桶为空,生产需加 COUNT(*) OVER() 判断 |
忘记 PARTITION BY market_type | 所有交易对混在一起排名,SPOT 和 FUTURES 不可比 |
Key Concept Tip
ROW_NUMBER 给唯一序号,RANK 允许并列但跳号,DENSE_RANK 允许并列且不跳号,NTILE 做分桶。 面试一句话:
ROW_NUMBER去重取 Top-N,RANK算百分比,DENSE_RANK做等级分层。
Q2: CTE — 递归查询邀请关系链与返佣层级
Problem Statement
"用户邀请系统里,
user_referral表保存了直接邀请关系 (referrer_id→referee_id)。 给定一个用户 ID,请递归查询出他的所有下级(直推 + 间推),并计算每个下级的层级 depth 以及该链条上的 root 用户。 假设 大型加密货币交易所 返佣最多 3 级,请只返回 depth <= 3 的记录。"
Table Schema
CREATE TABLE user_referral (
referrer_id BIGINT NOT NULL COMMENT '邀请人 UID',
referee_id BIGINT NOT NULL COMMENT '被邀请人 UID',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (referrer_id, referee_id),
KEY idx_referee (referee_id) -- 反向查上级时用
) ENGINE=InnoDB
COMMENT='用户邀请关系,记录不删除,软删用状态字段';Sample Data
INSERT INTO user_referral VALUES
(1001, 1002, '2024-01-01 10:00:00'), -- 1001 -> 1002
(1002, 1003, '2024-01-02 11:00:00'), -- 1002 -> 1003
(1003, 1004, '2024-01-03 12:00:00'), -- 1003 -> 1004 (depth 3 from 1001)
(1001, 1005, '2024-01-04 13:00:00'), -- 1001 -> 1005
(1005, 1006, '2024-01-05 14:00:00'), -- 1005 -> 1006
(1006, 1007, '2024-01-06 15:00:00'), -- 1006 -> 1007 (depth 3 from 1001)
(1002, 1008, '2024-01-07 16:00:00'); -- 1002 -> 1008Complete SQL Answer
WITH RECURSIVE referral_chain AS (
-- 锚定成员:直接下级(depth = 1)
SELECT
referrer_id AS root_user,
referee_id AS current_user,
1 AS depth,
CAST(referee_id AS CHAR(255)) AS path -- MySQL 递归 CTE 需要显式类型一致
FROM user_referral
WHERE referrer_id = 1001
UNION ALL
-- 递归成员:下级的下级
SELECT
rc.root_user,
ur.referee_id AS current_user,
rc.depth + 1 AS depth,
CONCAT(rc.path, '->', ur.referee_id) AS path
FROM referral_chain rc
INNER JOIN user_referral ur ON ur.referrer_id = rc.current_user
WHERE rc.depth < 3 -- 返佣层级上限,防止无限递归
)
SELECT
root_user,
current_user,
depth,
path
FROM referral_chain
ORDER BY depth, current_user;Common Mistakes / Anti-Patterns
| 错误 | 原因 |
|---|---|
忘写 depth < 3 终止条件 | MySQL 默认 cte_max_recursion_depth = 1000,数据量大时栈溢出或超时 |
UNION 代替 UNION ALL | 递归 CTE 必须用 UNION ALL,UNION 会去重导致递归中断 |
path 字段类型不一致 | 锚定行是 BIGINT,递归行是 CONCAT(...),MySQL 8 要求 CTE 列类型严格一致,需显式 CAST |
没有反向索引 idx_referee | ON ur.referrer_id = rc.current_user 需要 referee 做驱动,无索引全表扫描 |
| 试图递归 JOIN 大表全量 | 必须先 WHERE referrer_id = ? 锚定,再做递归,否则 O(n^2) 爆炸 |
Key Concept Tip
递归 CTE = 锚定行 + UNION ALL + 终止条件。 面试写之前先说:"我会用递归 CTE,锚点查直接邀请,然后递归 JOIN,限制 depth 防循环。"
Q3: CTE — 非递归多步 KYC 状态聚合
Problem Statement
"KYC 审核流有 5 个步骤,每个步骤的状态变更记录在
kyc_step_log中。 请用非递归 CTE 查询每个用户最新的 KYC 整体状态:要求把 5 个步骤的最新状态 pivot 成一行,并标记是否全部通过。"
Table Schema
CREATE TABLE kyc_step_log (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT NOT NULL COMMENT '用户 UID',
step_name VARCHAR(32) NOT NULL COMMENT '步骤:REGISTER / EMAIL_VERIFY / ID_UPLOAD / FACIAL / MANUAL_REVIEW',
status VARCHAR(16) NOT NULL COMMENT 'PENDING / PASSED / FAILED / RETRY',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
KEY idx_user_step_time (user_id, step_name, created_at DESC)
) ENGINE=InnoDB
COMMENT='KYC 步骤状态变更流水,只插入不更新';Sample Data
INSERT INTO kyc_step_log (user_id, step_name, status, created_at) VALUES
(2001, 'REGISTER', 'PASSED', '2024-06-01 08:00:00'),
(2001, 'EMAIL_VERIFY', 'PASSED', '2024-06-01 08:05:00'),
(2001, 'ID_UPLOAD', 'PASSED', '2024-06-01 08:10:00'),
(2001, 'FACIAL', 'FAILED', '2024-06-01 08:15:00'),
(2001, 'FACIAL', 'PASSED', '2024-06-01 08:20:00'), -- 重试通过
(2001, 'MANUAL_REVIEW', 'PENDING','2024-06-01 08:25:00'),
(2002, 'REGISTER', 'PASSED', '2024-06-01 09:00:00'),
(2002, 'EMAIL_VERIFY', 'PASSED', '2024-06-01 09:05:00'),
(2002, 'ID_UPLOAD', 'FAILED', '2024-06-01 09:10:00');Complete SQL Answer
-- ============================================================
-- Step 1: CTE 取每个用户每个步骤的最新一条记录
-- ============================================================
WITH latest_step AS (
SELECT
user_id,
step_name,
status,
created_at,
-- 每个 (user, step) 取最新
ROW_NUMBER() OVER (PARTITION BY user_id, step_name ORDER BY created_at DESC) AS rn
FROM kyc_step_log
)
-- ============================================================
-- Step 2: 把 5 个步骤 pivot 成一行,并计算是否全通过
-- ============================================================
SELECT
user_id,
MAX(CASE WHEN step_name = 'REGISTER' THEN status END) AS register_status,
MAX(CASE WHEN step_name = 'EMAIL_VERIFY' THEN status END) AS email_status,
MAX(CASE WHEN step_name = 'ID_UPLOAD' THEN status END) AS id_status,
MAX(CASE WHEN step_name = 'FACIAL' THEN status END) AS facial_status,
MAX(CASE WHEN step_name = 'MANUAL_REVIEW' THEN status END) AS manual_status,
-- 判断是否全部 PASSED(NULL 表示该步骤无记录,视为未完成)
CASE
WHEN COUNT(DISTINCT CASE WHEN status = 'PASSED' THEN step_name END) = 5
THEN 'FULL_PASSED'
ELSE 'INCOMPLETE'
END AS overall_status
FROM latest_step
WHERE rn = 1
GROUP BY user_id
ORDER BY user_id;Common Mistakes / Anti-Patterns
| 错误 | 原因 |
|---|---|
用 GROUP BY 直接取 MAX(status) | 状态是字符串,MAX 按字典序,'PASSED' < 'PENDING' 可能错 |
不用 ROW_NUMBER() 而直接用 MAX(created_at) | 需要子查询再次 JOIN 取 status,性能差且易错 |
Pivot 时忘写 MAX() 聚合 | 没有聚合函数的 CASE 放在 GROUP BY 中会报错 |
没有索引 (user_id, step_name, created_at DESC) | 每个用户每个步骤都要排序,全表扫描代价高 |
Key Concept Tip
非递归 CTE 的核心价值是"分步可读":先过滤/打标,再聚合。 Pivot 口诀:
CASE WHEN ... THEN ... END放在聚合函数里,GROUP BY用户。
Q4: JOIN 优化 — 成交明细关联盘口快照,避免笛卡尔积
Problem Statement
"
trade_fill记录逐笔成交,order_book_snapshot记录每秒盘口快照(买一卖一)。 请关联出每笔成交发生时最近的盘口快照,要求: (a) 写出正确的 JOIN SQL; (b) 指出一种会产生笛卡尔积的错误写法; (c) 说明如何建索引。"
Table Schema
-- 逐笔成交表(量大,按日分区)
CREATE TABLE trade_fill (
fill_id BIGINT NOT NULL COMMENT '成交 ID',
symbol VARCHAR(32) NOT NULL COMMENT '交易对',
fill_time DATETIME(3) NOT NULL COMMENT '成交时间,毫秒精度',
price DECIMAL(38,16) NOT NULL,
qty DECIMAL(38,16) NOT NULL,
PRIMARY KEY (fill_id, fill_time),
KEY idx_symbol_time (symbol, fill_time)
) ENGINE=InnoDB
PARTITION BY RANGE (TO_DAYS(fill_time)) (
PARTITION p20240601 VALUES LESS THAN (TO_DAYS('2024-06-02'))
)
COMMENT='逐笔成交,TTL 30 天';
-- 盘口快照表(每秒一条,按 symbol 分表或分区)
CREATE TABLE order_book_snapshot (
snapshot_id BIGINT AUTO_INCREMENT PRIMARY KEY,
symbol VARCHAR(32) NOT NULL,
snapshot_time DATETIME(3) NOT NULL,
best_bid_price DECIMAL(38,16) NOT NULL,
best_ask_price DECIMAL(38,16) NOT NULL,
KEY idx_symbol_time (symbol, snapshot_time)
) ENGINE=InnoDB
COMMENT='盘口快照,保留 7 天';Sample Data
INSERT INTO trade_fill VALUES
(1, 'BTCUSDT', '2024-06-01 10:00:00.500', 69950.00, 0.5),
(2, 'BTCUSDT', '2024-06-01 10:00:01.200', 69951.00, 1.2),
(3, 'BTCUSDT', '2024-06-01 10:00:02.800', 69952.00, 0.3);
INSERT INTO order_book_snapshot (symbol, snapshot_time, best_bid_price, best_ask_price) VALUES
('BTCUSDT', '2024-06-01 10:00:00.000', 69949.50, 69950.50),
('BTCUSDT', '2024-06-01 10:00:01.000', 69950.50, 69951.50),
('BTCUSDT', '2024-06-01 10:00:02.000', 69951.50, 69952.50);Complete SQL Answer
-- ============================================================
-- 正确写法:AsOf JOIN(MySQL 无原生 AsOf,用 window + JOIN 模拟)
-- ============================================================
WITH snapshot_ranked AS (
SELECT
symbol,
snapshot_time,
best_bid_price,
best_ask_price,
-- 为每个 symbol 生成有序序列
ROW_NUMBER() OVER (PARTITION BY symbol ORDER BY snapshot_time) AS snap_rn
FROM order_book_snapshot
WHERE symbol = 'BTCUSDT'
),
fills AS (
SELECT
fill_id,
symbol,
fill_time,
price,
qty
FROM trade_fill
WHERE symbol = 'BTCUSDT'
)
SELECT
f.fill_id,
f.fill_time,
f.price AS fill_price,
s.best_bid_price,
s.best_ask_price,
s.snapshot_time AS matched_snapshot_time
FROM fills f
-- 关联:snapshot_time <= fill_time 的最新一条
LEFT JOIN snapshot_ranked s
ON f.symbol = s.symbol
AND s.snapshot_time = (
SELECT MAX(snapshot_time)
FROM order_book_snapshot s2
WHERE s2.symbol = f.symbol
AND s2.snapshot_time <= f.fill_time
)
ORDER BY f.fill_time;
-- ============================================================
-- 更优生产写法:利用 LATERAL/窗口函数预先打标(StarRocks/CH 可用)
-- MySQL 8.0 推荐用上述子查询,或程序端合并(避免大数据量 JOIN)
-- ============================================================Common Mistakes / Anti-Patterns
错误写法(笛卡尔积):
-- 错误!没有 snapshot_time 关联条件,或条件方向错误
SELECT *
FROM trade_fill f
JOIN order_book_snapshot s ON f.symbol = s.symbol -- 每秒 N 条成交 × 86400 条快照 = 爆炸
WHERE s.snapshot_time <= f.fill_time; -- 过滤在 WHERE,先 JOIN 后过滤| 错误 | 原因 |
|---|---|
只按 symbol JOIN | 同 symbol 一天有 8.6 万条快照,笛卡尔积巨大 |
用 BETWEEN 或 DATE() 函数关联 | 无法使用索引,全表扫描 |
WHERE s.snapshot_time <= f.fill_time 写在 WHERE | 某些引擎先执行 JOIN 再过滤,中间结果膨胀 |
没有 symbol 过滤条件 | 两表都是海量数据,必须带 symbol 或日期裁剪 |
Key Concept Tip
"最近一条关联" = 不等值 JOIN,必须限制单侧范围,或子查询先取 MAX。 生产环境大数据量时,不要用 SQL 做 AsOf JOIN,用 Flink/Spark 或程序端二分查找。
Q5: Top-N per Group — 每个用户每日成交最活跃的前 3 个交易对
Problem Statement
"给定
user_trade_daily表,请查询每个用户每个自然日成交额最高的前 3 个交易对。 如果第 3 名出现并列,全部保留。"
Table Schema
CREATE TABLE user_trade_daily (
stat_date DATE NOT NULL,
user_id BIGINT NOT NULL COMMENT '用户 UID',
symbol VARCHAR(32) NOT NULL COMMENT '交易对',
quote_volume DECIMAL(38,8) NOT NULL COMMENT '当日成交额',
trade_count BIGINT NOT NULL,
PRIMARY KEY (stat_date, user_id, symbol),
KEY idx_user_date_vol (user_id, stat_date, quote_volume DESC)
) ENGINE=InnoDB
PARTITION BY RANGE COLUMNS(stat_date) (
PARTITION p202406 VALUES LESS THAN ('2024-07-01')
)
COMMENT='用户日级交易统计,保留 90 天';Sample Data
INSERT INTO user_trade_daily VALUES
('2024-06-01', 3001, 'BTCUSDT', 50000.00, 10),
('2024-06-01', 3001, 'ETHUSDT', 30000.00, 8),
('2024-06-01', 3001, 'BNBUSDT', 20000.00, 5),
('2024-06-01', 3001, 'SOLUSDT', 20000.00, 4), -- 与 BNBUSDT 并列第 3
('2024-06-01', 3002, 'BTCUSDT', 100000.00, 20),
('2024-06-01', 3002, 'ETHUSDT', 80000.00, 15),
('2024-06-02', 3001, 'BTCUSDT', 60000.00, 12);Complete SQL Answer
-- ============================================================
-- 法一:窗口函数 + 外层过滤(最通用)
-- ============================================================
WITH ranked AS (
SELECT
stat_date,
user_id,
symbol,
quote_volume,
-- 需求:前 3 名,并列全保留 → 用 DENSE_RANK
DENSE_RANK() OVER (
PARTITION BY user_id, stat_date
ORDER BY quote_volume DESC
) AS dr
FROM user_trade_daily
WHERE stat_date = '2024-06-01' -- 分区裁剪
)
SELECT
stat_date,
user_id,
symbol,
quote_volume,
dr AS rank_in_day
FROM ranked
WHERE dr <= 3
ORDER BY user_id, stat_date, dr, symbol;
-- ============================================================
-- 法二:MySQL 8 变量法(兼容旧思维,不推荐)
-- ============================================================
-- 面试时优先写法一,展示你掌握现代 SQL。Common Mistakes / Anti-Patterns
| 错误 | 原因 |
|---|---|
用 ROW_NUMBER() <= 3 | 并列时只保留一条,不符合"并列全保留"需求 |
用 RANK() <= 3 | 若第 2 名有 3 个并列,RANK 会跳号到 5,导致只保留 2 个组,可能漏掉真正的第 3 名 |
没有 PARTITION BY user_id, stat_date | 所有用户所有日期混在一起排名 |
| 没有 WHERE 日期裁剪 | 90 天分区的表,全表扫描代价极高 |
Key Concept Tip
Top-N 并列处理口诀:去并列用 ROW_NUMBER,允许并列不跳号用 DENSE_RANK,允许并列要跳号用 RANK。 本题 "并列全保留" =
DENSE_RANK() <= 3。
Q6: 连续 streak — 连续登录天数检测(风险评分模型输入)
Problem Statement
"风控模型需要识别用户连续登录行为。给定
user_login_log表,请查询每个用户截至今天的最长连续登录天数,以及当前是否处于连续登录状态。 连续定义:自然日连续,不要求时刻连续。"
Table Schema
CREATE TABLE user_login_log (
user_id BIGINT NOT NULL,
login_date DATE NOT NULL COMMENT '登录自然日,去重',
login_count INT NOT NULL DEFAULT 1 COMMENT '当日登录次数',
PRIMARY KEY (user_id, login_date),
KEY idx_date (login_date) -- 用于批量统计
) ENGINE=InnoDB
COMMENT='用户登录日汇总,从流水表聚合写入,保留 1 年';Sample Data
INSERT INTO user_login_log VALUES
(4001, '2024-06-01', 3),
(4001, '2024-06-02', 1),
(4001, '2024-06-03', 2),
(4001, '2024-06-05', 1), -- 断了一天
(4001, '2024-06-06', 1),
(4002, '2024-06-01', 1),
(4002, '2024-06-03', 1), -- 不连续
(4002, '2024-06-04', 1);Complete SQL Answer
-- ============================================================
-- 核心技巧:login_date 减去行号 = 连续组标识(等差数列原理)
-- ============================================================
WITH numbered AS (
SELECT
user_id,
login_date,
-- 按日期排序的行号
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
FROM user_login_log
),
grouped AS (
SELECT
user_id,
login_date,
-- 关键:DATE_SUB 后相同的属于同一连续组
DATE_SUB(login_date, INTERVAL rn DAY) AS grp
FROM numbered
)
SELECT
user_id,
COUNT(*) AS streak_length,
MIN(login_date) AS streak_start,
MAX(login_date) AS streak_end,
-- 判断当前 streak 是否包含今天(假设今天是 2024-06-06)
CASE WHEN MAX(login_date) = CURRENT_DATE THEN 'ACTIVE' ELSE 'BROKEN' END AS current_status
FROM grouped
GROUP BY user_id, grp
ORDER BY user_id, streak_length DESC;
-- ============================================================
-- 若只取每个用户的最长 streak:
-- ============================================================
WITH ... /* 同上 */ ...
SELECT
user_id,
MAX(streak_length) AS max_streak
FROM (
SELECT user_id, grp, COUNT(*) AS streak_length
FROM grouped
GROUP BY user_id, grp
) t
GROUP BY user_id;Common Mistakes / Anti-Patterns
| 错误 | 原因 |
|---|---|
用 LEAD/LAG 逐行比较 date_diff = 1 | 可以判断相邻是否连续,但统计连续长度需要再嵌套一层,代码更复杂 |
DATE_SUB 后比较忘了 INTERVAL rn DAY | 行号是整数,不能直接与 DATE 运算,必须显式转换 |
把 login_count > 0 作为去重条件 | 表本身已按日去重,无需再判断 |
| 没有处理跨月/跨年 | DATE_SUB 自动处理历法,无需担心 |
Key Concept Tip
连续日期问题万能公式:
date - ROW_NUMBER() = constant。同一 constant 即为同一连续段。 记住原理:等差数列减等差数列 = 常数。
Q7: 留存分析 — 新注册用户 Day-1 / Day-7 / Day-30 交易留存
Problem Statement
"给定
user_registration和user_trade_daily,请按注册日期 cohort 统计: 每日新增用户数、Day-1 交易留存率、Day-7 交易留存率、Day-30 交易留存率。 留存定义:注册后第 N 天有成交(不要求金额)。"
Table Schema
CREATE TABLE user_registration (
user_id BIGINT PRIMARY KEY,
register_date DATE NOT NULL,
channel VARCHAR(32) COMMENT '注册渠道',
KEY idx_register_date (register_date)
) ENGINE=InnoDB;
CREATE TABLE user_trade_daily (
stat_date DATE NOT NULL,
user_id BIGINT NOT NULL,
symbol VARCHAR(32) NOT NULL,
quote_volume DECIMAL(38,8) NOT NULL,
PRIMARY KEY (stat_date, user_id, symbol)
) ENGINE=InnoDB;Sample Data
INSERT INTO user_registration VALUES
(5001, '2024-06-01', 'APP'),
(5002, '2024-06-01', 'WEB'),
(5003, '2024-06-01', 'APP'),
(5004, '2024-06-02', 'APP');
INSERT INTO user_trade_daily VALUES
('2024-06-02', 5001, 'BTCUSDT', 100), -- Day-1 留存
('2024-06-08', 5001, 'ETHUSDT', 200), -- Day-7 留存
('2024-06-09', 5002, 'BTCUSDT', 50), -- Day-8 不算 Day-7
('2024-06-02', 5003, 'BTCUSDT', 300), -- Day-1 留存
('2024-06-03', 5004, 'BTCUSDT', 100); -- Day-1 留存Complete SQL Answer
-- ============================================================
-- Step 1: 构建注册 cohort 基表
-- Step 2: 左连接交易表,判断注册后第 N 天是否有交易
-- ============================================================
WITH cohort AS (
SELECT
register_date,
COUNT(*) AS new_users
FROM user_registration
WHERE register_date BETWEEN '2024-06-01' AND '2024-06-02'
GROUP BY register_date
),
retention_flags AS (
SELECT
r.register_date,
r.user_id,
-- 是否有 Day-1 交易
MAX(CASE WHEN t.stat_date = DATE_ADD(r.register_date, INTERVAL 1 DAY) THEN 1 END) AS d1_flag,
-- 是否有 Day-7 交易
MAX(CASE WHEN t.stat_date = DATE_ADD(r.register_date, INTERVAL 7 DAY) THEN 1 END) AS d7_flag,
-- 是否有 Day-30 交易
MAX(CASE WHEN t.stat_date = DATE_ADD(r.register_date, INTERVAL 30 DAY) THEN 1 END) AS d30_flag
FROM user_registration r
LEFT JOIN user_trade_daily t
ON r.user_id = t.user_id
AND t.stat_date IN (
DATE_ADD(r.register_date, INTERVAL 1 DAY),
DATE_ADD(r.register_date, INTERVAL 7 DAY),
DATE_ADD(r.register_date, INTERVAL 30 DAY)
)
WHERE r.register_date BETWEEN '2024-06-01' AND '2024-06-02'
GROUP BY r.register_date, r.user_id
)
SELECT
c.register_date,
c.new_users,
COUNT(CASE WHEN rf.d1_flag = 1 THEN 1 END) / c.new_users AS d1_retention_rate,
COUNT(CASE WHEN rf.d7_flag = 1 THEN 1 END) / c.new_users AS d7_retention_rate,
COUNT(CASE WHEN rf.d30_flag = 1 THEN 1 END) / c.new_users AS d30_retention_rate
FROM cohort c
LEFT JOIN retention_flags rf ON c.register_date = rf.register_date
GROUP BY c.register_date, c.new_users
ORDER BY c.register_date;Common Mistakes / Anti-Patterns
| 错误 | 原因 |
|---|---|
INNER JOIN 代替 LEFT JOIN | 未交易用户被过滤,留存率分母变小,结果虚高 |
用 DATEDIFF(t.stat_date, r.register_date) = 1 | 必须确保 t.stat_date >= r.register_date,否则负数也会匹配 |
把条件 t.stat_date IN (...) 写在 WHERE | WHERE 中对右表过滤会把 LEFT JOIN 变成 INNER JOIN |
用 COUNT(r.user_id) 做分子 | 应该 COUNT(CASE WHEN ... THEN 1 END) 只统计有交易的用户 |
没限制 register_date 范围 | 全量注册表 JOIN 全量交易表,数据爆炸 |
Key Concept Tip
留存率 = 有行为的用户数 / cohort 总用户数。 关键:
LEFT JOIN保全体,CASE WHEN做标记,COUNT(标记)求和,再除 cohort 人数。 生产环境用日期位图或 RoaringBitmap 预计算,SQL 仅用于离线报表。
Q8: 漏斗分析 — KYC 认证五步转化与流失
Problem Statement
"KYC 流程共 5 步:
REGISTER→EMAIL_VERIFY→ID_UPLOAD→FACIAL→MANUAL_REVIEW。 给定kyc_step_log,请统计 2024-06-01 当日 cohort 每一步的转化人数、转化率、相对上一步的流失率。 注意:用户可能重试某一步,只算最新状态;FAILED视为未通过该步。"
Table Schema
-- 复用 Q3 的 kyc_step_log 表结构,增加步骤顺序映射表
CREATE TABLE kyc_step_definition (
step_name VARCHAR(32) PRIMARY KEY,
step_order INT NOT NULL COMMENT '1~5'
) ENGINE=InnoDB;
INSERT INTO kyc_step_definition VALUES
('REGISTER', 1), ('EMAIL_VERIFY', 2), ('ID_UPLOAD', 3),
('FACIAL', 4), ('MANUAL_REVIEW', 5);Sample Data
复用 Q3 数据,增加一条:
INSERT INTO kyc_step_log (user_id, step_name, status, created_at) VALUES
(2003, 'REGISTER', 'PASSED', '2024-06-01 07:00:00'),
(2003, 'EMAIL_VERIFY', 'PASSED', '2024-06-01 07:05:00'),
(2003, 'ID_UPLOAD', 'FAILED', '2024-06-01 07:10:00'); -- 卡在第三步Complete SQL Answer
-- ============================================================
-- 漏斗分析:每一步的转化人数、转化率、流失率
-- ============================================================
WITH latest_status AS (
-- 每个用户每个步骤最新状态
SELECT
user_id,
step_name,
status,
ROW_NUMBER() OVER (PARTITION BY user_id, step_name ORDER BY created_at DESC) AS rn
FROM kyc_step_log
WHERE DATE(created_at) = '2024-06-01'
),
passed_users AS (
-- 只保留 PASSED 的
SELECT DISTINCT user_id, step_name
FROM latest_status
WHERE rn = 1 AND status = 'PASSED'
),
step_counts AS (
SELECT
d.step_order,
d.step_name,
COUNT(DISTINCT p.user_id) AS passed_count
FROM kyc_step_definition d
LEFT JOIN passed_users p ON d.step_name = p.step_name
GROUP BY d.step_order, d.step_name
)
SELECT
step_order,
step_name,
passed_count,
-- 相对于第一步的转化率
passed_count / MAX(passed_count) OVER () AS overall_conversion_rate,
-- 相对于上一步的转化率
passed_count / LAG(passed_count) OVER (ORDER BY step_order) AS step_conversion_rate,
-- 相对于上一步的流失率
1 - (passed_count / LAG(passed_count) OVER (ORDER BY step_order)) AS churn_rate
FROM step_counts
ORDER BY step_order;Common Mistakes / Anti-Patterns
| 错误 | 原因 |
|---|---|
不去重 DISTINCT user_id | 一个用户可能在多步出现,直接 COUNT(*) 会重复计数 |
没有过滤 rn = 1 | 重试记录导致旧状态干扰,必须用最新一条 |
FAILED 也算通过 | 漏斗只看成功转化,FAILED 应视为未进入该步 |
第一步用 LAG 计算流失率 | LAG(passed_count) 第一步为 NULL,需用 COALESCE 或 UNION 加初始值 |
Key Concept Tip
漏斗 = cohort → 每步去重计数 →
LAG求环比。 面试画金字塔口述:"先取每个用户每步最新状态,过滤出 PASSED,按步骤去重计数,再用窗口函数 LAG 算每步转化率和流失率。"
Q9: 移动平均 / 累积和 — 用户近 7 日提现金额滑动窗口与异常检测
Problem Statement
"风控需要监控用户提现行为异常。给定
withdrawal_request表,请统计每个用户最近 7 天(含当日)的: (a) 每日提现总额; (b) 7 日滑动平均提现额; (c) 当日提现额 / 7 日均值的比率(> 3 倍标记为 HIGH_RISK)。 缺失日期补零。"
Table Schema
CREATE TABLE withdrawal_request (
request_id BIGINT PRIMARY KEY,
user_id BIGINT NOT NULL,
amount DECIMAL(38,8) NOT NULL COMMENT '提现金额 USDT',
status VARCHAR(16) NOT NULL COMMENT 'SUCCESS / PENDING / REJECTED',
created_at DATETIME NOT NULL,
completed_at DATETIME COMMENT '成功时间',
KEY idx_user_completed (user_id, completed_at)
) ENGINE=InnoDB
PARTITION BY RANGE (TO_DAYS(completed_at)) (
PARTITION p202406 VALUES LESS THAN (TO_DAYS('2024-07-01'))
)
COMMENT='提现请求,只统计 status=SUCCESS 且 completed_at 非空';Sample Data
INSERT INTO withdrawal_request VALUES
(1, 6001, 1000.00, 'SUCCESS', '2024-06-01 10:00:00', '2024-06-01 10:05:00'),
(2, 6001, 2000.00, 'SUCCESS', '2024-06-02 11:00:00', '2024-06-02 11:05:00'),
(3, 6001, 1500.00, 'SUCCESS', '2024-06-03 12:00:00', '2024-06-03 12:05:00'),
(4, 6001, 1000.00, 'SUCCESS', '2024-06-05 13:00:00', '2024-06-05 13:05:00'), -- 6-04 缺失
(5, 6001, 10000.00,'SUCCESS', '2024-06-06 14:00:00', '2024-06-06 14:05:00'), -- 异常大额
(6, 6002, 500.00, 'SUCCESS', '2024-06-01 09:00:00', '2024-06-01 09:05:00');Complete SQL Answer
-- ============================================================
-- Step 1: 生成日期序列(MySQL 8 可用递归 CTE 生成;生产中常从日历表取)
-- Step 2: 与用户每日提现额 LEFT JOIN(补零)
-- Step 3: 窗口函数计算滑动平均与累积和
-- ============================================================
WITH RECURSIVE date_series AS (
SELECT DATE('2024-06-01') AS dt
UNION ALL
SELECT DATE_ADD(dt, INTERVAL 1 DAY)
FROM date_series
WHERE dt < DATE('2024-06-07')
),
user_list AS (
SELECT DISTINCT user_id FROM withdrawal_request
WHERE completed_at BETWEEN '2024-06-01' AND '2024-06-07 23:59:59'
),
date_user_grid AS (
-- 笛卡尔积:每个用户 × 每天(小范围日期允许,大数据量用日历表 JOIN)
SELECT d.dt, u.user_id FROM date_series d CROSS JOIN user_list u
),
daily_amount AS (
SELECT
user_id,
DATE(completed_at) AS dt,
SUM(amount) AS daily_total
FROM withdrawal_request
WHERE status = 'SUCCESS'
AND completed_at BETWEEN '2024-06-01' AND '2024-06-07 23:59:59'
GROUP BY user_id, DATE(completed_at)
)
SELECT
g.dt,
g.user_id,
COALESCE(d.daily_total, 0) AS daily_total,
-- 7 日滑动平均(当前行往前 6 天 = 含当日共 7 天)
AVG(COALESCE(d.daily_total, 0)) OVER (
PARTITION BY g.user_id
ORDER BY g.dt
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS moving_avg_7d,
-- 7 日累积和
SUM(COALESCE(d.daily_total, 0)) OVER (
PARTITION BY g.user_id
ORDER BY g.dt
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS cumulative_7d,
-- 异常比率:当日 / 7 日均值(前 6 天不足时不标记)
CASE
WHEN COUNT(*) OVER (
PARTITION BY g.user_id
ORDER BY g.dt
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) >= 7
AND COALESCE(d.daily_total, 0) > 0
THEN COALESCE(d.daily_total, 0) / AVG(COALESCE(d.daily_total, 0)) OVER (
PARTITION BY g.user_id
ORDER BY g.dt
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
)
ELSE NULL
END AS spike_ratio,
CASE
WHEN COALESCE(d.daily_total, 0) > 3 * AVG(COALESCE(d.daily_total, 0)) OVER (
PARTITION BY g.user_id
ORDER BY g.dt
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
)
THEN 'HIGH_RISK'
ELSE 'NORMAL'
END AS risk_flag
FROM date_user_grid g
LEFT JOIN daily_amount d ON g.user_id = d.user_id AND g.dt = d.dt
ORDER BY g.user_id, g.dt;Common Mistakes / Anti-Patterns
| 错误 | 原因 |
|---|---|
RANGE BETWEEN 代替 ROWS BETWEEN | RANGE 按值划窗,日期重复时会多算;日期序列去重后应使用 ROWS |
忘写 COALESCE(d.daily_total, 0) | 缺失日期 JOIN 后为 NULL,AVG/SUM 会忽略 NULL,导致均值虚高 |
用 UNBOUNDED PRECEDING | 那是累积从开头到当前,不是滑动 7 日 |
| 没有日期裁剪 | withdrawal_request 量大,必须先 WHERE completed_at 范围 |
| 缺失日期未补零 | 风控场景 6-04 无提现不能直接跳过,必须显式补 0 |
Key Concept Tip
滑动窗口语法:
AVG(x) OVER (PARTITION BY user ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)。 补零关键:先生成日期网格CROSS JOIN用户,再LEFT JOIN实际数据。
Q10: 重复检测与去重 — 提现请求重复提交识别与幂等去重
Problem Statement
"用户前端可能因网络抖动重复点击提交提现。给定
withdrawal_request表,请: (a) 找出过去 1 小时内,同一用户、同一金额、同一地址的重复提交(保留第一条,其余标记 DUPLICATE); (b) 写出幂等去重后的唯一请求视图。"
Table Schema
CREATE TABLE withdrawal_request (
request_id BIGINT PRIMARY KEY,
user_id BIGINT NOT NULL,
amount DECIMAL(38,8) NOT NULL,
to_address VARCHAR(128) NOT NULL COMMENT '链上地址',
chain VARCHAR(32) NOT NULL COMMENT 'ETH / BSC / TRON',
status VARCHAR(16) NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
client_ref_id VARCHAR(64) COMMENT '客户端幂等键',
KEY idx_user_amount_address_time (user_id, amount, to_address, created_at),
KEY idx_created_at (created_at)
) ENGINE=InnoDB;Sample Data
INSERT INTO withdrawal_request
(request_id, user_id, amount, to_address, chain, status, created_at, client_ref_id) VALUES
(101, 7001, 100.00, '0xAbc...', 'ETH', 'PENDING', '2024-06-01 10:00:00', 'ref-001'),
(102, 7001, 100.00, '0xAbc...', 'ETH', 'PENDING', '2024-06-01 10:00:02', 'ref-002'), -- 重复
(103, 7001, 100.00, '0xAbc...', 'ETH', 'PENDING', '2024-06-01 10:00:05', 'ref-003'), -- 重复
(104, 7001, 200.00, '0xDef...', 'ETH', 'PENDING', '2024-06-01 10:10:00', 'ref-004'),
(105, 7002, 50.00, '0xXyz...', 'BSC', 'PENDING', '2024-06-01 10:00:01', 'ref-005');Complete SQL Answer
-- ============================================================
-- (a) 找出重复记录(保留第一条,其余标记 DUPLICATE)
-- ============================================================
WITH dedup_window AS (
SELECT
request_id,
user_id,
amount,
to_address,
chain,
created_at,
-- 按时间排序,第一条为 1
ROW_NUMBER() OVER (
PARTITION BY user_id, amount, to_address, chain
ORDER BY created_at ASC, request_id ASC
) AS rn
FROM withdrawal_request
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
)
SELECT
request_id,
user_id,
amount,
to_address,
created_at,
CASE WHEN rn = 1 THEN 'KEEP' ELSE 'DUPLICATE' END AS action
FROM dedup_window
WHERE rn > 1 -- 只看重复项;面试时可去掉 WHERE 展示全部
ORDER BY user_id, created_at;
-- ============================================================
-- (b) 幂等去重后的唯一请求视图(保留第一条)
-- ============================================================
WITH dedup AS (
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY user_id, amount, to_address, chain
ORDER BY created_at ASC, request_id ASC
) AS rn
FROM withdrawal_request
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
)
SELECT *
FROM dedup
WHERE rn = 1;Common Mistakes / Anti-Patterns
| 错误 | 原因 |
|---|---|
用 GROUP BY + MIN(request_id) | 只能取到 request_id,其他字段需要再 JOIN 一次,效率低 |
PARTITION BY 少了 chain | 不同链地址格式可能相同,必须区分 |
ORDER BY created_at DESC | 应该保留最早的一条(最可能是用户真实意图),DESC 会留最晚的 |
没有 client_ref_id 判断 | 生产环境优先用客户端幂等键去重,服务端 user+amount+address 是兜底 |
| 全表扫描 | created_at 必须带范围,否则大数据表性能灾难 |
Key Concept Tip
去重公式:
ROW_NUMBER() OVER (PARTITION BY 业务唯一键 ORDER BY 时间 ASC) = 1即保留。 生产先防:客户端传client_ref_id,服务端 Redis SETNX 拦截;SQL 去重是事后补偿。
Q11: 时序聚合 — 每小时成交额 rollup 与补零
Problem Statement
"交易大屏需要展示某交易对过去 24 小时每小时成交额。给定
trade_fill表(含毫秒时间),请: (a) 按小时汇总成交额; (b) 缺失小时补零; (c) 同时展示环比上小时增长率。"
Table Schema
复用 Q4 的 trade_fill 表:
CREATE TABLE trade_fill (
fill_id BIGINT NOT NULL,
symbol VARCHAR(32) NOT NULL,
fill_time DATETIME(3) NOT NULL,
price DECIMAL(38,16) NOT NULL,
qty DECIMAL(38,16) NOT NULL,
amount DECIMAL(38,16) GENERATED ALWAYS AS (price * qty) STORED, -- 成交额
PRIMARY KEY (fill_id, fill_time),
KEY idx_symbol_time (symbol, fill_time)
) ENGINE=InnoDB;Sample Data
INSERT INTO trade_fill (fill_id, symbol, fill_time, price, qty) VALUES
(1, 'BTCUSDT', '2024-06-01 10:15:00.000', 70000.00, 1.0),
(2, 'BTCUSDT', '2024-06-01 10:45:00.000', 70100.00, 0.5),
(3, 'BTCUSDT', '2024-06-01 12:10:00.000', 70200.00, 2.0); -- 11 点缺失Complete SQL Answer
-- ============================================================
-- Step 1: 递归生成过去 24 小时整点序列
-- Step 2: 左连接实际成交汇总(补零)
-- Step 3: LAG 计算环比
-- ============================================================
WITH RECURSIVE hours AS (
SELECT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 23 HOUR), '%Y-%m-%d %H:00:00') AS hour_slot
UNION ALL
SELECT DATE_ADD(hour_slot, INTERVAL 1 HOUR)
FROM hours
WHERE hour_slot < DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00')
),
hourly_trades AS (
SELECT
DATE_FORMAT(fill_time, '%Y-%m-%d %H:00:00') AS hour_slot,
SUM(price * qty) AS hourly_volume
FROM trade_fill
WHERE symbol = 'BTCUSDT'
AND fill_time >= DATE_SUB(NOW(), INTERVAL 24 HOUR)
GROUP BY DATE_FORMAT(fill_time, '%Y-%m-%d %H:00:00')
)
SELECT
h.hour_slot,
COALESCE(t.hourly_volume, 0) AS hourly_volume,
-- 环比上小时
COALESCE(t.hourly_volume, 0) / NULLIF(LAG(COALESCE(t.hourly_volume, 0)) OVER (ORDER BY h.hour_slot), 0) - 1 AS mom_rate,
-- 标记是否有成交
CASE WHEN t.hourly_volume IS NULL THEN 'NO_TRADE' ELSE 'TRADED' END AS trade_status
FROM hours h
LEFT JOIN hourly_trades t ON h.hour_slot = t.hour_slot
ORDER BY h.hour_slot;Common Mistakes / Anti-Patterns
| 错误 | 原因 |
|---|---|
DATE_FORMAT(fill_time, '%Y-%m-%d %H') 直接 GROUP | 返回字符串小时,JOIN 时类型需一致;建议统一格式化为整点时间戳 |
用 UNION ALL 手工写 24 行 | 不优雅,且时间窗口变化时需改代码;递归 CTE 或程序生成更灵活 |
没有 COALESCE(..., 0) | 缺失小时显示 NULL,前端图表会断线 |
LAG 跨 NULL 计算环比 | 上小时无成交时 LAG 取 0,除零需用 NULLIF 保护 |
没有 symbol 过滤 | trade_fill 通常按 symbol 分片,不带 symbol 不走分区裁剪 |
Key Concept Tip
时序补零 = 日期网格
LEFT JOIN实际聚合。 面试一句话:"先生成连续小时序列,再左连接成交汇总,COALESCE 补零,LAG 算环比。"
面试现场建议
1. 当面试官没给 Schema 时 —— 主动追问
不要直接写 SQL。先问清楚以下问题:
| 要问的问题 | 为什么重要 |
|---|---|
| "表的数据量级大概是多少?百万、千万、还是百亿?" | 决定用单机 SQL 还是分布式引擎(StarRocks/ClickHouse) |
| "有没有分区或索引?主键是什么?" | 决定你的查询能否走索引,是否要做分区裁剪 |
| "NULL 是怎么处理的?字段允许 NULL 吗?" | 决定要不要 COALESCE / IFNULL |
| "时间精度要求?到天、小时、还是毫秒?" | 决定 GROUP BY 的粒度 |
| "去重逻辑是什么?取最早、最晚、还是随机一条?" | 决定 ORDER BY 方向 |
| "输出需要全部列,还是只需要几个聚合指标?" | 决定 SELECT 范围和是否需要 JOIN 回主表 |
面试官不给你 schema,是故意看你会不会问问题。 风控/后端岗位尤其看重"需求澄清能力"。
2. 如何在脑中测试你的 SQL
即使没有 IDE,也可以做" mentally dry run":
- 画小表:在草稿纸上写 5~8 行 sample data,亲手推导窗口函数结果。
- 跟踪 PARTITION 边界:每换一行,问自己 "PARTITION BY 变了没有?ORDER BY 变了没有?"
- 检查 NULL:在脑中给某个 cell 填 NULL,看 JOIN 后会不会整行消失(LEFT JOIN 是否变 INNER JOIN)。
- 检查边界:第一行和最后一行的
LAG/LEAD是否为 NULL?是否符合预期? - 估算行数:每一步 CTE / 子查询后,输出大概多少行?是否比输入爆炸?
3. 如何逐步讲解你的 SQL
推荐"三段式"口述:
Step 1 — 过滤与裁剪:
"我先做日期裁剪和 symbol 过滤,利用分区/索引减少数据量。"
Step 2 — 打标与分组:
"然后用窗口函数 / CTE 给每行打标(如 ROW_NUMBER / DENSE_RANK / 连续组标识)。"
Step 3 — 聚合与输出:
"最后 GROUP BY 做聚合,SELECT 出最终指标,并做风险标记(如 HIGH_RISK)。"
边说边写,不要闷头写 3 分钟再交卷。面试官要的是你的思维过程。
4. 如果忘了函数名
| 忘了什么 | 现场补救方案 |
|---|---|
DENSE_RANK 拼写 | 说 "我用 RANK 的稠密版本,就是不跳号的那个" |
LAG 语法 | 说 "我用 offset 为 1 的前一行引用" |
DATE_SUB vs DATE_ADD | 说 "我用日期减去一个 INTERVAL" |
ROWS vs RANGE | 说 "我用基于物理行的滑动窗口,不是基于值的" |
| 递归 CTE 语法 | 先写非递归 CTE,然后说 "如果层级深,我可以改成递归 CTE" |
承认"函数名可能拼错,但逻辑是这样",比硬猜一个错的更有专业性。
常见追问应对
Q1: "How would you optimize this query?"
模型答案:
"优化分三层:
- SQL 层面:确保 WHERE 条件命中分区键和索引键,避免函数索引列(如
DATE(column)),把计算移到常量侧;- 索引层面:对 JOIN 条件和 ORDER BY 字段建联合索引,遵循最左前缀;对高基数字段在前,低基数在后;
- 架构层面:如果表过十亿行,单机 MySQL 扛不住,我会考虑按 user_id 分库分表,或者把分析型查询迁移到 StarRocks/ClickHouse,利用列存和向量化执行。"
Q2: "What index would you add?"
模型答案:
"看查询的过滤、JOIN、排序三要素:
- 本查询 WHERE 有
user_id = ? AND stat_date BETWEEN ? AND ?,我会建联合索引(user_id, stat_date, quote_volume DESC);stat_date放在第二列因为它有范围查询,再后面放quote_volume DESC可以避免 filesort;- 如果是覆盖索引场景,我把 SELECT 需要的字段也加进索引尾部,避免回表。"
Q3: "How does this execute under the hood?"
模型答案:
"我会用
EXPLAIN ANALYZE看执行计划:
- 先看
type是不是range或ref,避免ALL全表扫描;- 看
Extra有没有Using filesort或Using temporary,窗口函数通常需要内部临时表排序;- 对于
PARTITION BY user_id ORDER BY date的窗口函数,如果数据按(user_id, date)有序,可以省去一次排序;- 递归 CTE 看是否走了
referee_id索引,每次递归迭代都是一次索引查找。"
Q4: "What if the table has 1 billion rows?"
模型答案:
"十亿级表在 MySQL 里做复杂分析查询会触达单机天花板:
- 存储层:按时间分区 + TTL,冷数据归档到 S3/OSS;
- 查询层:把 OLTP 和 OLAP 分离,实时报表用 StarRocks/ClickHouse 物化视图预聚合;
- 计算层:如果必须在线查明细,按 user_id 分片,把查询路由到对应分片并行执行;
- 业务层:留存/漏斗类指标不需要秒级实时,T+1 离线 Spark 作业预计算到 Redis/HBase。"
Q5: "How to handle NULLs?"
模型答案:
"我遵循三个原则:
- 存储层面:业务上必须有值的字段(如 user_id, amount)设
NOT NULL加默认值;- 查询层面:聚合函数前用
COALESCE(col, 0),避免 AVG/SUM 忽略 NULL 导致结果偏差;- JOIN 层面:LEFT JOIN 后的右表字段可能为 NULL,做比率计算时用
NULLIF(denominator, 0)防除零;- 窗口函数层面:
LAG(col, 1, 0)用第三个参数指定默认值,避免首行 NULL。"
Q6: "What's the difference between RANK and DENSE_RANK?"
模型答案:
"两者都允许并列,但处理跳号不同:
RANK():1, 2, 2, 4 —— 并列后跳号,适合计算真实百分比分位;DENSE_RANK():1, 2, 2, 3 —— 并列不跳号,适合分层(如 A/B/C/D 等级);ROW_NUMBER():1, 2, 3, 4 —— 无并列,适合去重取唯一一条。"
Q7: "Why use CTE instead of subquery?"
模型答案:
"三个原因:
- 可读性:复杂查询分步命名,面试官和队友都能看懂;
- 复用性:同一个 CTE 可以在主查询中多次引用,子查询要复制粘贴;
- 递归能力:只有 CTE 支持
WITH RECURSIVE,子查询无法实现树形/层级遍历。 性能上现代优化器对 CTE 和子查询通常做同等优化,不会更差。"
Q8: "How would you implement this in a streaming pipeline?"
模型答案:
"风控场景很多指标需要近实时:
- Flink SQL:用
OVER窗口做滑动平均,MATCH_RECOGNIZE做连续事件检测;- 物化视图:StarRocks 的异步物化视图可以预计算小时级 rollup,查询直接读 MV;
- 增量聚合:留存/漏斗用位图(RoaringBitmap)每日增量合并,而不是每次全量 SQL 重算;
- Lambda 架构:实时 Flink 算近似值,离线 Spark 算精确值,T+1 修正。"
附录:速查口诀卡
| 场景 | 口诀 |
|---|---|
| Top-N 去并列 | ROW_NUMBER() = 1 |
| Top-N 保并列不跳号 | DENSE_RANK() <= N |
| 连续日期检测 | date - ROW_NUMBER() = grp |
| 留存率计算 | LEFT JOIN + CASE WHEN + COUNT |
| 漏斗流失 | LAG(count) OVER (ORDER BY step) |
| 滑动平均 | AVG(x) OVER (ROWS BETWEEN N PRECEDING AND CURRENT ROW) |
| 去重保留最早 | ROW_NUMBER() OVER (PARTITION BY key ORDER BY time ASC) = 1 |
| 时序补零 | "日期网格 CROSS JOIN 用户,LEFT JOIN 实际数据,COALESCE 补 0" |
| 递归树 | WITH RECURSIVE ... UNION ALL ... depth < N |
| Pivot 行转列 | MAX(CASE WHEN step = 'X' THEN status END) |
最后提醒:大型加密货币交易所 面试白板写 SQL 时,先写 CTE 框架再填细节。即使某个函数名不确定,先把
WITH ... AS ()的骨架搭好,向面试官确认字段名后再填充,展示结构化思维比一次写对更重要。