📖 本篇简介：本篇收官大厂面试专题，覆盖数据岗笔试和面试中最常被考察的两大能力：SQL 与 pandas 双写对照（SELECT/WHERE/GROUP BY/JOIN/WINDOW 一一对应）和规模化与工程化素养（分块处理、Parquet、DuckDB、SQL 预聚合、生产环境的常见坑）。前置知识：第 40 章的原理深挖与第 41 章的业务题。学完后你能做到"同一道题 SQL 和 pandas 都能写、数据大知道怎么办、生产环境不踩坑"。
大厂数据岗的笔试/一面，很多公司要求 SQL 和 Python 二选一甚至双写；二三面聊项目时，"你的处理在千万行/亿级数据上还能跑吗"是必问题。本篇把两套表达焊在一起讲。
目录
1. SQL ↔ pandas 对照总表
2. 对照题 1：SELECT / WHERE / ORDER BY
3. 对照题 2：GROUP BY + 聚合 + HAVING
4. 对照题 3：JOIN 与子查询
5. 对照题 4：窗口函数 row_number / lag
6. 对照题 5：去重 / 取数 / 分页
7. 规模化方案：从单机到分布式
8. SQL 预聚合 + pandas 收尾的经典架构
9. 生产环境五大坑
10. 本章小结与自测清单
1. SQL ↔ pandas 对照总表
先把"翻译表"背下来，笔试遇到 SQL 题直接用 pandas 翻译（反之亦然）：
| SQL | pandas 等价物 | 说明 |
|-----|--------------|------|
| SELECT a, b FROM t | df[["a", "b"]] | 选列 |
| SELECT * | df | 全列 |
| WHERE | df[布尔条件] / df.query() | 行过滤 |
| ORDER BY a DESC | df.sort_values("a", ascending=False) | 排序 |
| GROUP BY k | df.groupby("k") | 分组 |
| COUNT(*) | .size() / .count() | 计数 |
| SUM/AVG/MIN/MAX | .sum()/.mean()/.min()/.max() | 聚合 |
| HAVING | .filter(...) 或聚合后过滤 | 组级过滤 |
| DISTINCT | .drop_duplicates() / .nunique() | 去重 |
| LIMIT n | .head(n) / .iloc[:n] | 取前 n |
| JOIN ... ON | .merge(...) | 键连接 |
| UNION ALL | pd.concat | 上下拼接 |
| ROW_NUMBER() OVER (PARTITION BY k ORDER BY v) | groupby(k)[v].rank() 或 cumcount | 窗口排名 |
| LAG(v, 1) OVER (...) | groupby(k)[v].shift(1) | 取上一条 |
| CASE WHEN | np.where / pd.cut | 条件分支 |
| SUBSTRING/LIKE | .str[:] / .str.contains | 字符串函数 |
2. 对照题 1：SELECT / WHERE / ORDER BY
题目
表 sales(sales_id, product, amount, date)。查询：2026 年 7 月、金额大于 100 的订单，按金额降序，只要前 5 条。SQL 写法
SELECT sales_id, product, amount
FROM sales
WHERE date >= '2026-07-01' AND date < '2026-08-01'
AND amount > 100
ORDER BY amount DESC
LIMIT 5;
pandas 写法
import pandas as pd
sales = pd.read_csv("sales.csv", parse_dates=["date"])
# SELECT 列
cols = ["sales_id", "product", "amount"]
# WHERE：时间范围 + 金额条件（布尔索引）
mask = (sales["date"] >= "2026-07-01") & (sales["date"] < "2026-08-01")
mask = mask & (sales["amount"] > 100)
# ORDER BY + LIMIT
result = sales.loc[mask, cols].sort_values("amount", ascending=False).head(5)
print(result)
对照要点：SQL 的 WHERE 与 pandas 布尔索引一一对应；多个条件用 &（注意加括号）；LIMIT 5 = .head(5)。
3. 对照题 2：GROUP BY + 聚合 + HAVING
题目
按 product 分组求总额、平均额、订单数；只保留总额大于 10000 的产品组，按总额降序。SQL 写法
SELECT product,
SUM(amount) AS total,
AVG(amount) AS avg_amt,
COUNT(*) AS cnt
FROM sales
GROUP BY product
HAVING SUM(amount) > 10000
ORDER BY total DESC;
pandas 写法
# GROUP BY + 多聚合
agg = sales.groupby("product")["amount"].agg(
total="sum",
avg_amt="mean",
cnt="count",
)
# HAVING：对"组结果"过滤
agg = agg[agg["total"] > 10000]
# ORDER BY
result = agg.sort_values("total", ascending=False)
print(result)
对照要点：GROUP BY = groupby；HAVING 作用于组结果，在 pandas 里就是"聚合之后再过滤一行"，千万别在 groupby 前过滤（那是 WHERE）。
4. 对照题 3：JOIN 与子查询
题目
订单表orders(order_id, user_id, amount)关联用户表users(user_id, name, level)，求每个用户的订单总额（含无订单用户则补 0）。
SQL 写法
SELECT u.user_id, u.name, IFNULL(SUM(o.amount), 0) AS total
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id, u.name;
pandas 写法
# LEFT JOIN = merge(how="left")
merged = users.merge(orders, on="user_id", how="left")
# 无订单用户金额为 NaN -> 补 0
result = merged.groupby(["user_id", "name"])["amount"].sum().fillna(0).reset_index()
print(result)
对照要点：LEFT JOIN = merge(how="left")；IFNULL = fillna；子查询（WHERE user_id IN (SELECT ...)）在 pandas 里用 isin：
# 子查询等价：找出下过单的用户
buyer_ids = orders["user_id"].unique()
active = users[users["user_id"].isin(buyer_ids)]
print(active)
5. 对照题 4：窗口函数 row_number / lag
题目
求每个产品销售额 Top3 的订单；以及每个订单金额较上一单的环比变化。
SQL 写法
-- Top3（row_number）
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY product ORDER BY amount DESC) AS rn
FROM sales
) t WHERE rn <= 3;
-- 环比（lag）
SELECT sales_id, amount,
amount - LAG(amount, 1) OVER (PARTITION BY product ORDER BY date) AS diff
FROM sales;
pandas 写法
# ---------- Top3：分组内排序取前 3 ----------
# 方法一：sort_values 全局排序 + groupby head(3)
top3 = sales.sort_values("amount", ascending=False).groupby("product").head(3)
print(top3)
# 方法二：rank 后过滤（等价 row_number）
sales["rn"] = sales.groupby("product")["amount"].rank(
method="first", ascending=False).astype(int)
top3_v2 = sales[sales["rn"] <= 3]
# ---------- 环比：分组内 shift ----------
sales["prev_amount"] = sales.groupby("product")["amount"].shift(1)
sales["diff"] = sales["amount"] - sales["prev_amount"]
print(sales.head())
对照要点：ROW_NUMBER() OVER (PARTITION BY ...) = groupby(...).rank(method="first") 或"排序后 groupby.head"；LAG = shift(1)；LEAD = shift(-1)。
6. 对照题 5：去重 / 取数 / 分页
题目
① 求sales表中不重复的product列表；② 每页 10 条、取第 3 页。
参考答案
# ① DISTINCT
products = sales["product"].drop_duplicates().tolist()
print(products)
# 等价：value_counts 还带频次
print(sales["product"].value_counts())
# ② 分页：OFFSET = (page-1) * page_size
page, page_size = 3, 10
offset = (page - 1) * page_size
page_data = sales.iloc[offset:offset + page_size]
print(page_data)
对照要点：DISTINCT = drop_duplicates；LIMIT x OFFSET y = .iloc[y:y+x]。
7. 规模化方案：从单机到分布式
面试官三连问："数据大了怎么办？再多呢？再多呢？" 对应这张升级路线：
import pandas as pd
# ① 数据量 1~10GB：单机 pandas + 省内存三板斧
# usecols 只读要用的列 / dtype 压类型 / 分块 chunksize
df = pd.read_csv("big.csv", usecols=["user_id", "amount"],
dtype={"user_id": "int32", "amount": "float32"})
# ② 10~50GB：换列存格式 Parquet + 分块 + 只聚合不落全量
# df.to_parquet("big.parquet")
# pd.read_parquet("big.parquet", columns=["amount"])
# ③ 50GB~TB：交给"SQL 引擎"做预聚合，pandas 只收尾
# 方案 A：DuckDB 直接查 CSV/Parquet（单机、快、零部署）
# import duckdb
# duckdb.sql("SELECT product, SUM(amount) FROM 'big.parquet' GROUP BY 1")
# 方案 B：把数据导入 MySQL/ClickHouse，SQL 聚合后再导出小结果集
# 方案 D：分布式 Spark（多节点、容错、适合数仓链路）
# ④ 面试金句："先把聚合下推到数据库/数仓，pandas 只处理最后
# 一公里的结果集——这比让 pandas 硬扛全量数据优雅一个数量级。"
选型判断三问（背下来）：
1. 数据能否装进单机内存（文件体积 × 3~5 倍）？→ 能则 pandas；
2. 是否高频/多并发访问？→ 是则上数据库/数仓，别每次全量读文件；
3. 是否需要跨节点并行与容错？→ 需要则 Spark。
8. SQL 预聚合 + pandas 收尾的经典架构
真实生产中最常见的架构是"重活交给 SQL、精细活交给 pandas"：
# ---------- 架构示意：SQL 做大聚合，pandas 做业务计算 ----------
# 步骤 1（在数仓/MySQL 执行）：
sql_1 = """
SELECT user_id, COUNT(*) AS order_cnt, SUM(amount) AS gmv
FROM orders
WHERE date >= '2026-01-01' AND date < '2026-04-01'
GROUP BY user_id
"""
# 百万行订单 -> 压缩成几十万行的用户聚合
# 步骤 2（pandas 读聚合结果做业务计算）：
agg_user = pd.read_sql(sql_1, engine) # 用 SQLAlchemy engine 连接
# 步骤 3：业务分层（RFM 雏形）
def rfm_level(row):
if row["order_cnt"] >= 5 and row["gmv"] >= 2000:
return "高价值"
if row["order_cnt"] >= 2:
return "中价值"
return "低价值"
agg_user["分层"] = agg_user.apply(rfm_level, axis=1)
print(agg_user.groupby("分层").agg(人数=("user_id", "count"), GMV=("gmv", "sum")))
为什么这套架构是大厂主流：数据库擅长并行聚合与索引过滤；pandas 擅长灵活变换与可视化。各干各的，谁也不硬扛。
9. 生产环境五大坑
面试最后常问"你上线踩过什么坑"，五个高频坑提前备好：
| # | 坑 | 现象 | 规避 |
|---|----|------|------|
| 1 | 编码问题 | 中文乱码 | 统一 UTF-8，read_csv(encoding="utf-8")，入库前 encode/decode 校验 |
| 2 | 时区问题 | 跨天数据错位 | 统一存 UTC，展示时转本地时区，tz_localize/tz_convert |
| 3 | 浮点精度 | 金额对不上账 | 金额用 Decimal/分单位整数，不用 float64 |
| 4 | 静默数据漂移 | 结果与昨天差很多没人发现 | 每次跑批加"断言"：行数、sum、分布与基准对比，超阈值告警 |
| 5 | 上线没人复核 | 口径改了结果变 | 口径变更留痕、双写对比一周（新旧代码同跑对账） |
# 坑 3 示例：金额千万不要用 float 直接相加
amount_float = 0.1 + 0.2 # 0.30000000000000004
amount_cents = 10 + 20 # 30（分单位整数，永远精确）
print(amount_float, "-> 建议用分单位:", amount_cents)
# 坑 4 示例：跑批断言（数据质量门禁）
def run_batch(df_new):
assert df_new.shape[0] > 100_000, "行数异常偏低"
assert abs(df_new["amount"].sum() - 1_234_567_890) < 0.01, "总额与基准不符"
print("质量门禁通过，可以上线")
10. 本章小结与自测清单
=== 本章核心 ===
1. SQL 与 pandas 是同一套思维的两种表达：对照总表背熟，笔试双写无压力；
2. 窗口函数：rank = row_number、shift = lag/lead；
3. 规模化路线：pandas(单机) → Parquet/分块 → DuckDB/数据库预聚合 → Spark；
4. 生产环境：编码、时区、金额精度、数据漂移断言、口径变更留痕；
5. 经典架构：SQL 做重聚合，pandas 做业务计算与可视化。
面试前自测清单：
- [ ] 能否不看表把"对照总表"默写出来？
- [ ] HAVING 和 WHERE 在 pandas 里分别怎么翻译？
- [ ] ROW_NUMBER 和 LAG 的 pandas 写法能脱口而出吗？
- [ ] 10GB / 100GB / 1TB 三档数据你分别选什么方案？
- [ ] 能讲清楚"SQL 预聚合 + pandas 收尾"为什么是主流架构？
- [ ] 金额计算为什么不用 float？
到这里，大厂面试专题（40~42 章）全部完成。整个 pandas 系列已扩展到 42 篇。回到总目录，按学习路径继续，或把面试篇当作"上战场前的最后冲刺"。
文章回复
0 条公开回复