一、初级巩固题(1-78题，烂熟于心)
1、查询每个部门工资最高的员工 → RANK() 或 DENSE_RANK() + 子查询/CTE。
2、连续登录3天以上用户 → ROW_NUMBER() + 日期差分组 (例如：login_date - ROW_NUMBER() OVER(...))。
3、从未下过单的用户 → LEFT JOIN + IS NULL 或 NOT EXISTS。
4、各城市销售占比 → SUM() OVER() 计算总和再除，或使用窗口函数 SUM(sales) * 1.0 / SUM(SUM(sales)) OVER() (SUM(SUM(...))：特殊语法，它必须出现在一个包含 GROUP BY 子句的查询中，否则会报错)。
说明：SUM(sales) * 1.0 / SUM(SUM(sales)) OVER() 这种写法在 GROUP BY 子句中，部分数据库(如：MySQL)会报错，因为它不允许在同一层 SELECT 中，将一个聚合函数(SUM(sales))和一个作用于聚合结果的窗口函数(SUM(SUM(sales)) OVER())混合使用。
推荐：我们使用子查询或 CTE (通用表表达式)是更标准、兼容性更好的写法。
-- 使用子查询 (更通用)
SELECT
city,
SUM(sales) AS city_sales,
SUM(sales) * 1.0 / (SELECT SUM(sales) FROM orders) AS sales_ratio
FROM orders
GROUP BY city;
-- 使用 CTE (可读性更好)
WITH total_sales AS (
SELECT SUM(sales) AS total FROM orders
)
SELECT
city,
SUM(sales) AS city_sales,
SUM(sales) * 1.0 / ts.total AS sales_ratio
FROM orders, total_sales ts
GROUP BY city;
5、行转列(月份销售额) → CASE WHEN + GROUP BY (或使用 PIVOT 操作符，如果数据库支持)。
6、查询重复记录 → GROUP BY + HAVING COUNT(*) > 1。
7、删除重复记录保留一条 → ROW_NUMBER() + 子查询删除 (通常基于某个唯一标识或时间戳)。
8、查询第N高工资 → DISTINCT + ORDER BY + LIMIT OFFSET 或 DENSE_RANK()。
9、查询最近一次登录用户 → MAX(login_time) GROUP BY user_id (通常需要结合用户信息表)。
10、查询平均工资高于公司平均的部门 → 子查询或窗口函数比较 (AVG(salary) > AVG(AVG(salary)) OVER())。
更直观：我们使用子查询计算公司平均工资，然后在外层进行比较，意图更清晰。
-- 使用子查询 (意图更清晰)
SELECT
department_id,
AVG(salary) AS dept_avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > (SELECT AVG(salary) FROM employees);
11、查询本月订单 → WHERE DATE_TRUNC('month', order_date) = DATE_TRUNC('month', CURRENT_DATE) (或使用 YEAR(), MONTH() 函数)。
12、查询上季度销售额 → BETWEEN 或 DATEADD/DATE_SUB 计算区间。
13、查询周末下单用户 → DAYOFWEEK() 或 EXTRACT(DOW) (注意不同数据库周起始日定义)。
14、查询生日在本月的员工 → MONTH(birth_date) = MONTH(CURDATE()) (注意闰年2月29日问题)。
15、查询订单金额前10%用户 → PERCENT_RANK() 或 NTILE(10)。
16、查询每个用户首次订单 → ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY order_date ASC)。
17、查询每个商品销量排名 → RANK() OVER(ORDER BY SUM(qty) DESC) PARTITION BY category (如果需要分品类)。
18、查询订单金额中位数 → PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) (标准SQL) 或 ROW_NUMBER() + 中间值。
19、查询用户复购率 → (下单次数>1的用户数) / (总下单用户数)。
20、查询沉默用户(30天未登录) → MAX(login_time) < DATE_SUB(NOW(), INTERVAL 30 DAY) GROUP BY user_id。
21、查询新用户(首次登录在最近7天) → MIN(login_time) >= DATE_SUB(NOW(), INTERVAL 7 DAY) GROUP BY user_id。
22、查询订单取消率 → SUM(CASE WHEN status='cancelled' THEN 1 ELSE 0 END) * 1.0 / COUNT(*)。
23、查询每个城市订单数增长率 → 自连接或 LAG() 计算环比 ((current - previous) / previous)。
24、查询订单金额分布(分桶) → CASE WHEN 或 WIDTH_BUCKET (如果支持)。
25、查询用户平均下单间隔 → AVG(DATEDIFF(next_order, current_order)) + LEAD() OVER(PARTITION BY user_id ORDER BY order_date)。
26、查询每个用户最近3次订单 → ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY date DESC) <=3。
27、查询订单金额波动最大用户 → STDDEV(amount) OVER(PARTITION BY user_id) ORDER BY DESC LIMIT 1。
28、查询每个商品类目销售Top3 → RANK() OVER(PARTITION BY category ORDER BY SUM(amount) DESC) <= 3。
29、查询订单金额累计和 → SUM(amount) OVER(ORDER BY date ROWS UNBOUNDED PRECEDING)。
30、查询订单金额移动平均 → AVG(amount) OVER(ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)。
31、查询每个用户订单金额总和及排名 → SUM(amount) GROUP BY user_id + RANK() OVER(ORDER BY SUM(amount) DESC)。
32、查询订单状态变化记录 → 自连接或 LAG(status) OVER(PARTITION BY order_id ORDER BY status_time)。
33、查询每个用户首次支付成功订单 → ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY order_date) = 1 AND status='paid'。
34、查询退款率最高的商品 → SUM(refund_amount)/SUM(order_amount) GROUP BY product_id ORDER BY DESC LIMIT 1。
35、查询订单高峰期(小时) → HOUR(order_time) GROUP BY + ORDER BY COUNT(*) DESC LIMIT 1。
36、查询用户下单频次分布 → COUNT(order_id) GROUP BY user_id + 分组统计 (如：频次区间)。
37、查询订单金额分位数 → PERCENTILE_DISC 或 NTILE。
38、查询每个用户最近一次支付金额 → FIRST_VALUE(amount) OVER(PARTITION BY user_id ORDER BY payment_time DESC) 或 ROW_NUMBER()。
39、查询订单金额同比去年 → LAG(SUM(amount)) OVER(PARTITION BY EXTRACT(MONTH FROM date) ORDER BY EXTRACT(YEAR FROM date))。
40、查询每个用户消费总额及占比 → SUM(amount) GROUP BY user_id + SUM(amount) OVER()。
41、查询订单取消原因分布 → GROUP BY cancel_reason + COUNT(*)。
42、查询用户生命周期价值(LTV) → SUM(amount) GROUP BY user_id。
43、查询商品复购率 → (购买该商品次数>1的用户数) / (购买过该商品的用户总数)。
44、查询沉默商品(30天无销售) → MAX(sale_date) < DATE_SUB(NOW(), INTERVAL 30 DAY) GROUP BY product_id。
45、查询热销商品(销量Top10%) → NTILE(10) OVER(ORDER BY SUM(qty) DESC) = 1。
46、查询订单配送时长 → DATEDIFF(delivery_time, order_time) (或 TIMESTAMPDIFF)。
47、查询超时未支付订单 → payment_time IS NULL AND order_time < DATE_SUB(NOW(), INTERVAL 1 HOUR)。
48、查询用户首次下单到支付时长 → MIN(TIMESTAMPDIFF(HOUR, order_time, payment_time)) GROUP BY user_id (需处理未支付订单)。
49、查询订单金额离群值(>3σ) → WHERE ABS(amount - AVG(amount) OVER()) > 3 * STDDEV(amount) OVER()。
50、查询每个用户平均订单金额 → AVG(amount) GROUP BY user_id。
51、查询订单金额中位数分城市 → PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) OVER(PARTITION BY city)。
52、查询每个商品首次上架销售日 → MIN(order_date) GROUP BY product_id。
53、查询用户活跃天数 → COUNT(DISTINCT login_date) GROUP BY user_id。
54、查询订单状态流转路径 → STRING_AGG(status ORDER BY status_time SEPARATOR '→') (或 GROUP_CONCAT)。
55、查询每个用户最近登录设备 → FIRST_VALUE(device_id) OVER(PARTITION BY user_id ORDER BY login_time DESC) 或 ROW_NUMBER()。
56、查询订单金额分城市排名 → RANK() OVER(PARTITION BY city ORDER BY amount DESC)。
57、查询每个用户订单金额标准差 → STDDEV(amount) GROUP BY user_id。
58、查询订单金额分星期分布 → DAYOFWEEK(order_date) GROUP BY + COUNT(*)。
59、查询用户首次登录渠道 → FIRST_VALUE(channel) OVER(PARTITION BY user_id ORDER BY login_time ASC) 或 ROW_NUMBER()。
60、查询每个商品类目订单数占比 → COUNT(*) * 1.0 / SUM(COUNT(*)) OVER() GROUP BY category。
61、查询订单金额分小时峰值 → HOUR(order_time) GROUP BY + COUNT(*) ORDER BY DESC LIMIT 1。
62、查询用户平均活跃间隔 → AVG(DATEDIFF(next_login, current_login)) + LEAD(login_date) OVER(PARTITION BY user_id ORDER BY login_date)。
63、查询订单金额分用户等级分布 → JOIN user_level_table + GROUP BY level + SUM(amount)。
64、查询用户首次复购时间 → MIN(LEAD(order_date) OVER(PARTITION BY user_id ORDER BY order_date) - order_date) GROUP BY user_id (取首次非空差值)。
65、计算沉默用户唤醒率 → (唤醒后7天内下单的沉默用户数) / (触达的沉默用户总数)。
66、订单金额分箱后统计各箱用户数 → CASE WHEN amount BETWEEN 0 AND 100 THEN '0-100' ... END AS bucket + GROUP BY bucket + COUNT(DISTINCT user_id)。
67、用户最近一次行为距今天数 → DATEDIFF(NOW(), MAX(event_time)) GROUP BY user_id。
68、商品类目销售贡献度(帕累托) → 先计算每个类目的销售额占比，然后使用窗口函数计算累计销售额占比。
-- 正确的计算方式（我们推荐）
WITH category_sales AS (
SELECT
category,
SUM(amount) AS cat_sales
FROM orders
GROUP BY category
),
category_ratio AS (
SELECT
category,
cat_sales,
cat_sales * 1.0 / (SELECT SUM(cat_sales) FROM category_sales) AS sales_ratio
FROM category_sales
)
SELECT
category,
cat_sales,
sales_ratio,
SUM(sales_ratio) OVER (ORDER BY cat_sales DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_ratio
FROM category_ratio
WHERE SUM(sales_ratio) OVER (ORDER BY cat_sales DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) <= 0.8;
注意：ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 是 SUM() OVER (ORDER BY ...) 的默认行为，但显式写出可以避免在某些数据库中因 ORDER BY 导致的 RANGE 窗口行为歧义。更简洁的写法(嵌套窗口函数)：
SELECT *
FROM (
SELECT
category,
SUM(amount) AS total_sales,
SUM(SUM(amount)) OVER() AS grand_total,
SUM(amount) / SUM(SUM(amount)) OVER() AS sales_ratio,
SUM(SUM(amount) / SUM(SUM(amount)) OVER()) OVER(ORDER BY SUM(amount) DESC) AS cumulative_ratio
FROM your_sales_table
GROUP BY category
) AS t
WHERE cumulative_ratio <= 0.8;
69、用户分群后各群转化率对比 → JOIN user_segment_table + GROUP BY segment + SUM(CASE WHEN converted THEN 1 ELSE 0 END) * 1.0 / COUNT(*)。
70、促销期间 vs 非促销期间 GMV 对比 → CASE WHEN is_promo_period(order_date) THEN '促销' ELSE '非促销' END + SUM(amount) GROUP BY。
71、用户设备首次使用与末次使用间隔 → MAX(event_time) - MIN(event_time) GROUP BY user_id, device_id。
72、订单取消原因与时效关联分析 → AVG(TIMESTAMPDIFF(HOUR, order_time, cancel_time)) GROUP BY cancel_reason。
73、用户登录城市切换频率 → COUNT(DISTINCT city) * 1.0 / COUNT(*) GROUP BY user_id (按登录次数计算)。
74、商品评分与销量相关性粗略分析 → 若数据库支持 CORR(rating, sales)，否则导出计算或使用近似方法。
75、用户行为路径长度分布 → COUNT(event_id) GROUP BY session_id + 分组统计 (如：路径长度区间)。
76、订单履约 SLA 达成率 → SUM(CASE WHEN TIMESTAMPDIFF(HOUR, order_time, delivery_time) <= SLA_HOURS THEN 1 ELSE 0 END) * 1.0 / COUNT(*)。
77、用户首次访问渠道 vs 末次渠道留存对比 → 使用 FIRST_VALUE(channel) 和 LAST_VALUE(channel) OVER(PARTITION BY user_id ORDER BY event_time ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)，然后分组计算各渠道组合的留存率。
78、商品库存周转天数 → 365 * AVG(inventory_level) / COALESCE(SUM(sales_cost), 1)，平均库存 = (期初库存 + 期末库存) / 2。
二、中级进阶题(78-155题，举一反三)
78、用户行为路径转化率 → LEAD() 或会话内 STRING_AGG() + LIKE '%A→B→C%' 或精确匹配。
79、留存率矩阵(第1/3/7/30日) → 多次 LEFT JOIN (按日期差) 或 SUM(CASE WHEN DATEDIFF(day, install_date, event_date) = 1 THEN 1 ELSE 0 END) / COUNT(DISTINCT install_user)。
80、滑动窗口Top N商品 → ROWS BETWEEN 2 PRECEDING AND CURRENT ROW + RANK() OVER(ORDER BY sales DESC)。
81、异常订单(>3倍均值) → WHERE amount > 3 * AVG(amount) OVER(PARTITION BY user_id) (或全局均值)。
82、库存流水余额 → SUM(qty_change) OVER(ORDER BY transaction_time ROWS UNBOUNDED PRECEDING)。
83、用户分群(RFM模型) → NTILE(5) OVER(ORDER BY last_order_date) (Recency), NTILE(5) OVER(ORDER BY order_frequency) (Frequency), NTILE(5) OVER(ORDER BY total_monetary) (Monetary)，然后组合得分。
84、会话切割(30分钟无操作算新会话) → LAG(event_time) OVER(PARTITION BY user_id ORDER BY event_time) 计算间隔，SUM(CASE WHEN gap_minutes > 30 THEN 1 ELSE 0 END) OVER(PARTITION BY user_id ORDER BY event_time) 生成会话ID。
85、用户生命周期阶段划分 → 基于首次/末次/频次/金额定义规则 (如：新客、活跃、沉默、流失)。
86、商品交叉销售推荐 → SELF JOIN orders o1 JOIN orders o2 ON o1.order_id = o2.order_id AND o1.product_id != o2.product_id + GROUP BY o1.product_id, o2.product_id + ORDER BY COUNT(*) DESC。
87、用户流失预警模型 → 最近登录距今 > 阈值 AND 近N天活跃度环比下降 > X%。
88、订单金额预测区间 → 基于用户历史 AVG(amount) ± k * STDDEV(amount) (k通常取1.96或2)。
89、动态价格区间分组 → WIDTH_BUCKET(amount, (SELECT MIN(amount) FROM table), (SELECT MAX(amount) FROM table), 10)。
⚠️ MySQL 本身不原生支持 WIDTH_BUCKET 函数！通用替代：我们使用 NTILE(n) 窗口函数，它可以将有序的数据行平均分配到 n 个桶中。
-- 使用 NTILE (通用)
SELECT
amount,
NTILE(10) OVER (ORDER BY amount) AS price_bucket
FROM orders;
注意：NTILE 是按“行数”均分，而 WIDTH_BUCKET 是按“数值范围”均分。当数据分布不均时，结果会有差异。但对于快速探索，NTILE 是很好的通用选择。对于追求“数值范围”分箱且数据库不支持 WIDTH_BUCKET 的情况，我们有时会用 FLOOR((value - min_value) / bucket_width) 或 CEIL((value - min_value + 1) / bucket_width) 来手动计算桶号。但这需要预先知道 min_value, max_value 或 bucket_width，不如 NTILE 方便。
90、用户价值分层(帕累托80/20) → SUM(amount) ORDER BY DESC + 计算累计占比 CUME_DIST() 或手动累加 WHERE 累计占比 <= 0.8。
91、多维度漏斗分析 → 逐层 COUNT(DISTINCT user_id) (注意去重) + 计算层间转化率。
92、用户行为序列模式挖掘 → MATCH_RECOGNIZE(Oracle) 或自定义路径匹配 (如：使用字符串函数或递归CTE)。
说明：MATCH_RECOGNIZE 是一个极其强大的 SQL 功能，专为模式识别设计，非常适合用户行为路径分析。但其兼容性极差，目前主要在 Oracle 和 Spark SQL 中得到支持，在 MySQL, PostgreSQL, SQL Server 等中均不支持。通用替代：对于不支持 MATCH_RECOGNIZE 的数据库，我们通常有两种方案：
- 字符串聚合 + 模糊匹配:
STRING_AGG/GROUP_CONCAT，性能较差，且不精确。
- 自连接 + 窗口函数: 这是更精确和性能更好的方法，但 SQL 会非常复杂，需要多次自连接或使用递归 CTE 来追踪路径。对于复杂路径，通常会将数据导出到 Python/Spark 等环境中用专门的算法库处理。
93、订单状态异常检测(未支付却发货) → WHERE status='shipped' AND payment_time IS NULL。
94、用户地域迁移分析 → LAG(city) OVER(PARTITION BY user_id ORDER BY login_time) AS prev_city, city AS current_city WHERE prev_city != current_city。
95、商品生命周期分析(导入→成长→成熟→衰退) → 基于周/月销量的移动平均趋势和增长率分段。
96、用户推荐效果评估 → COUNT(DISTINCT invited_user_id) WHERE inviter_id IS NOT NULL AND invited_user_id IN (SELECT user_id FROM paid_orders)。
97、促销活动ROI计算 → (增量GMV * 毛利率 - 促销成本) / 促销成本，增量GMV需对比同期或对照组。
98、用户设备切换分析 → COUNT(DISTINCT device_id) > 1 GROUP BY user_id。
99、订单配送路线优化 → 地理函数 ST_DISTANCE + 聚合最近仓库 (通常需结合GIS系统，SQL用于数据准备)。
100、用户兴趣标签挖掘 → 基于点击/购买商品类目聚合，COUNT(*) GROUP BY user_id, category ORDER BY COUNT DESC LIMIT top_n per user。
101、库存预警模型 → WHERE current_stock < average_daily_sales * safety_days。
102、用户沉默唤醒效果 → A/B Test: 对比实验组(被唤醒)和对照组(未被唤醒)在唤醒后N天的活跃率/转化率。
103、商品搭配销售分析 → JOIN order_items oi1 JOIN order_items oi2 ON oi1.order_id=oi2.order_id AND oi1.product_id < oi2.product_id + GROUP BY oi1.product_id, oi2.product_id + COUNT(*)。
104、用户流失原因归因 → 关联最后N次行为日志 (如：是否有差评、投诉、页面停留时间骤降)。
105、订单金额季节性波动 → EXTRACT(MONTH FROM order_date) + AVG(amount) GROUP BY month。
106、用户复购周期预测 → AVG(LEAD(order_date) OVER(PARTITION BY user_id ORDER BY order_date) - order_date) GROUP BY user_id。
107、商品评分趋势分析 → AVG(rating) OVER(ORDER BY DATE_TRUNC('week', review_date) ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)。
108、用户渠道质量评估 → 各渠道用户平均LTV / 该渠道平均获客成本 (CAC)。
109、订单履约时效分析 → AVG(TIMESTAMPDIFF(HOUR, order_time, delivery_time)) GROUP BY region。
110、用户行为热力图(时段×行为) → HOUR(event_time) + event_type + COUNT(*) GROUP BY hour, event_type。
111、商品库存周转率 → SUM(sales_cost) / AVG(inventory_level) (注意时间周期一致性)。
112、用户推荐裂变率 → COUNT(DISTINCT invited_and_paid_user_id) / COUNT(DISTINCT inviter_id)。
113、订单退款原因聚类 → 文本关键词提取 (如：LIKE '%质量%') + GROUP BY reason_category。
114、用户登录频次衰减分析 → 计算每次登录间隔，AVG(interval_growth_rate) GROUP BY user_id (间隔增长率 = (当前间隔 - 上次间隔) / 上次间隔)。
115、商品价格弹性分析 → 计算价格变动百分比 vs 销量变动百分比的相关系数 (需多期数据)。
116、用户分时活跃度 → HOUR(login_time) + COUNT(*) GROUP BY hour。
117、订单金额分渠道分布 → JOIN channel_mapping + GROUP BY channel + SUM(amount)。
118、用户设备类型偏好 → GROUP BY device_type + COUNT(DISTINCT user_id)。
119、商品评论情感分析 → 通常需NLP处理，SQL用于聚合结果 (如：GROUP BY product_id, sentiment_score_bucket)。
120、用户地域分布热力 → COUNT(*) GROUP BY province/city。
121、订单支付方式占比 → GROUP BY payment_method + COUNT(*)。
122、用户年龄分层消费 → CASE WHEN age < 25 THEN '青年' ... END AS age_group + SUM(amount) GROUP BY age_group。
123、商品上新效果评估 → 上新后7天总销量 vs 上新前7天同类商品平均销量。
124、用户沉默周期分布 → DATEDIFF(MAX(login_date), MIN(login_date)) GROUP BY user_id (或计算沉默期长度)。
125、订单取消高峰期 → HOUR(cancel_time) + COUNT(*) GROUP BY hour ORDER BY count DESC。
126、用户推荐层级关系 → 递归查找邀请链 (有限层级，如：3层) WITH RECURSIVE referral_chain AS (...)。
127、商品类目销售趋势 → DATE_TRUNC('month', order_date) + SUM(amount) OVER(PARTITION BY category ORDER BY month ROWS UNBOUNDED PRECEDING) (累计) 或直接分组。
128、用户登录IP异常检测 → COUNT(DISTINCT ip_address) > threshold GROUP BY user_id。
129、订单金额分会员等级 → JOIN member_level + GROUP BY level + AVG(amount)。
130、用户行为路径长度 → COUNT(event_id) GROUP BY session_id。
131、商品库存健康度 → current_stock / (average_daily_sales_last_7_days)。
132、用户复购商品重合度 → 计算用户A和用户B购买商品集合的交集大小 / 并集大小 (Jaccard相似度，需自连接或应用层计算)。
133、订单配送超时率 → SUM(CASE WHEN TIMESTAMPDIFF(HOUR, order_time, delivery_time) > SLA_HOURS THEN 1 ELSE 0 END) * 1.0 / COUNT(*)。
134、用户生命周期阶段转化 → 定义阶段规则，统计从阶段X到阶段Y的用户数及转化率。
135、大表 UPDATE，我们如何避免锁表和性能问题？ → 分批更新 (WHERE id > ? AND id <= ? LIMIT batch_size) + 睡眠 + 事务控制 (COMMIT each batch) + 在从库或低峰期执行。
136、我们如何调试一条慢 SQL？ → EXPLAIN / EXPLAIN ANALYZE → 看执行计划 → 是否走索引 → 是否有临时表/文件排序 → 重写SQL或加索引或调整配置。
137、窗口函数中 ORDER BY 对性能影响？ → 会触发排序，大数据量可考虑预排序 (创建索引) 或改用聚合 + 自连接 (如果逻辑允许)。
138、COUNT(DISTINCT) 性能差怎么办？ → HyperLogLog 近似算法、分桶去重后合并、预聚合、Bitmap (如：RoaringBitmap)、升级硬件或数据库。
139、我们如何测试 SQL 结果正确性？ → 单元测试 (固定输入输出)、抽样比对、A/B环境验证、数据血缘回溯、与业务方确认口径。
140、SQL 脚本如何做版本管理？ → Git + SQL文件 + 变更说明 (DDL/DML) + 审核流程 + Flyway/Liquibase/Alembic等迁移工具。
141、多团队协作 SQL 规范有哪些？ → 命名规范 (表、列、别名)、注释规范 (逻辑、作者、日期)、缩进、避免 SELECT *、明确 JOIN 条件、避免子查询嵌套过深、使用CTE提高可读性。
142、我们如何防止 SQL 注入在动态 SQL 中？ → 强制使用参数绑定 (Prepared Statement / Parameterized Query)、输入校验和过滤、最小权限原则、避免字符串拼接SQL。
143、NULL 值在聚合/比较中的陷阱有哪些？ → SUM(NULL)=NULL, COUNT(NULL)=0, = NULL 无效需用 IS NULL, NOT IN (含NULL列表) 返回空集, NULL AND/OR anything 结果可能为NULL。
144、浮点数精度导致金额计算错误怎么办？ → 使用 DECIMAL / NUMERIC 类型、避免 FLOAT/DOUBLE 做财务计算、应用层用整数分 (乘以100存储)。
145、日期时区处理不当导致数据错乱？ → 统一用 UTC 存储、展示时按需转换、避免用 NOW()/CURRENT_TIMESTAMP 直接入库 (用明确时区函数)、用 TIMESTAMP WITH TIME ZONE (如果支持)。
146、数据重复插入，我们如何避免？ → INSERT IGNORE (MySQL)、ON DUPLICATE KEY UPDATE (MySQL)、MERGE (SQL标准)、唯一约束 (UNIQUE KEY) + 应用层幂等设计。
147、我们如何实现“分页+排序+过滤”高性能？ → 覆盖索引 (Covering Index) + 延迟关联 (先查ID再JOIN主表) + 避免大 OFFSET (用游标分页: WHERE id > last_id ORDER BY id LIMIT N)。
148、统计类 SQL 如何保证幂等和可重跑？ → 基于时间分区/批次号、幂等写入 (如：INSERT OVERWRITE 或 MERGE)、记录执行状态和时间戳、使用事务。
149、我们如何做 SQL 性能压测和容量评估？ → 构造生产级数据量 + Sysbench/自定义脚本/JMeter + 监控 QPS/RT/CPU/IO/Memory + 瓶颈分析 (慢查询、锁、IO) + 容量规划。
150、BI 用户说“数据不准”，我们如何排查？ → 1、确认指标口径和时间范围 2、查SQL逻辑和数据源 3、查ETL过程延迟或错误 4、查权限/过滤条件 5、提供数据血缘报告 6、对比源系统。
151、产品经理要“实时看板”，技术上如何实现？ → Flink/Spark Streaming 流处理 + 实时聚合 + 缓存 (Redis/Druid) + 前端轮询/WebSocket + 降级策略 (如：降为分钟级)。
152、如何设计“用户可自助拖拽的分析平台”？ → 维度/指标配置化元数据 + 动态SQL生成引擎 + 细粒度权限控制 + 查询限流和超时 + 结果缓存 + 预聚合支持。
153、数据分析师提的 SQL 太慢，我们如何优化？ → Review SQL和执行计划 → 建议加索引/改写SQL → 提供预聚合宽表/物化视图 → 培训SQL规范和性能意识 → 接入查询审核平台。
154、我们如何用 SQL 支持 A/B 实验分析？ → JOIN experiment_assignment_table + GROUP BY variant + 计算核心指标 (如：CTR, CVR) + 计算置信区间或P值 (通常需UDF或导出到统计工具)。
155、数据看板中“日环比下降10%”是否异常？我们如何判断？ → 看历史同期波动 (标准差/控制图)、看节假日/活动影响、看渠道/用户群变化、用预测模型 (如：Prophet) 计算预测区间 (预测值±2σ)。
三、高级压轴题(156-222题，面试真题解析)
156、递归查询所有下属 → WITH RECURSIVE subordinate_tree AS (SELECT ... UNION ALL SELECT ... FROM subordinate_tree JOIN employees ...) (注意循环检测)。
157、会话间隔>1小时算新会话 → LAG(event_time) OVER(PARTITION BY user_id ORDER BY event_time) 计算秒级间隔，SUM(CASE WHEN gap_seconds > 3600 THEN 1 ELSE 0 END) OVER(PARTITION BY user_id ORDER BY event_time) 生成会话ID。
158、动态分组(每组总额≈100万) → SUM(amount) OVER(ORDER BY some_ordering_column ROWS UNBOUNDED PRECEDING) AS running_total, FLOOR((running_total - 1) / 1000000) AS group_id (贪心算法)。
159、订单表 vs 支付表一致性校验 → FULL OUTER JOIN orders o ON o.order_id = p.order_id FULL OUTER JOIN payments p + WHERE o.order_id IS NULL OR p.order_id IS NULL OR o.amount != p.amount (注意处理NULL，使用 COALESCE(o.amount, 0) != COALESCE(p.amount, 0))。
说明：使用COALESCE(o.amount, 0) != COALESCE(p.amount, 0)来处理NULL是一个常见做法，逻辑上可以接受。但在严格的财务对账中，有时会认为NULL和0是不同的状态(例如，订单存在但金额未录入 vs 订单金额确实是0)，可能需要更精细的处理(比如，分别检查IS NULL的情况)。
160、千万级 GROUP BY + ORDER BY 优化 → 覆盖索引、增加内存/临时表空间、分区表、近似算法 (如：HyperLogLog for COUNT DISTINCT)、预聚合。
161、实时Top N热销商品 → Flink SQL + TUMBLE/SLIDE WINDOW + RANK() OVER(PARTITION BY window_start ORDER BY sales DESC) + 状态存储 (RocksDB) + 输出到Redis/DB。
162、用户路径去重统计 → COUNT(DISTINCT session_id) + 对路径字符串进行标准化 (如：排序去重步骤) 后再统计。
163、多事实表关联性能优化 → 星型/雪花模型设计、预聚合宽表、物化视图、列式存储、避免大表JOIN (改用IN或EXISTS)。
164、数据血缘追踪 → 元数据管理工具 (如：Apache Atlas, DataHub) + 解析SQL/ETL脚本依赖关系 + 图数据库存储。
165、缓慢变化维处理(SCD Type 2) → 新增版本记录 + 生效日期(start_date) / 失效日期(end_date) + 当前标识(is_current)。
166、时间旅行查询(某时刻数据状态) → 基于SCD2维度表 或 数据库原生功能 (如：Snowflake Time Travel, BigQuery Snapshot Decorator, Delta Lake VERSION AS OF)。
167、数据质量监控规则 → COUNT(*) WHERE value NOT IN (SELECT valid_value FROM ref_table) OR value IS NULL (针对枚举值) 或范围检查。
168、异常检测(3σ原则) → WHERE ABS(value - AVG(value) OVER()) > 3 * STDDEV(value) OVER() (注意窗口范围，可按分区)。
169、用户分群AB测试效果 → 计算实验组和对照组的核心指标差异，显著性检验需在应用层或使用统计UDF (如：计算Z值、P值)，SQL主要用于数据准备。
170、数据脱敏查询 → CASE WHEN CURRENT_USER() IN ('admin', 'analyst') THEN real_phone ELSE CONCAT(LEFT(real_phone,3), '****', RIGHT(real_phone,4)) END AS phone。
171、行级权限控制 → WHERE user_id = @current_user_id OR department_id IN (SELECT dept_id FROM user_dept_mapping WHERE user_id = @current_user_id)。
172、多租户数据隔离 → WHERE tenant_id = @current_tenant_id + 在所有相关表上为 tenant_id 创建索引。
173、数据版本对比 → 使用 EXCEPT / MINUS 操作符比较两个结果集，或计算行级checksum后比对。
174、数据归档策略 → 按时间分区 (如：RANGE partition by date) + 定期将老分区 ALTER TABLE ... DROP PARTITION 并迁移至冷存储 (如：S3, OSS)，或使用归档表。
175、查询重写优化 → 使用物化视图 (Materialized View) 并启用查询重写 (如：Oracle, PostgreSQL) 或依赖计算引擎自动优化 (如：Spark Catalyst)。
176、高基数列去重优化 → HyperLogLog (HLL) 近似去重算法 (如：APPROX_COUNT_DISTINCT in BigQuery, Spark) 或 Bitmap (如：groupBitmap in ClickHouse)。
177、大表JOIN优化 → 广播小表 (Broadcast Join)、分桶JOIN (Bucketed Join)、预聚合减少JOIN数据量、调整JOIN顺序。
178、窗口函数性能瓶颈 → 减少 PARTITION BY 的基数 (避免按高基数列分区)、为 ORDER BY 和 PARTITION BY 列创建复合索引、限定数据范围。
179、递归CTE深度限制 → 设置递归深度限制 (如：PostgreSQL SET work_mem;, SQL Server OPTION (MAXRECURSION n)) 或在业务逻辑中加入深度计数器并在 WHERE 中限制。
180、实时库存扣减一致性 → 悲观锁: SELECT stock FROM inventory WHERE sku='xxx' FOR UPDATE; UPDATE inventory SET stock=stock-1 WHERE sku='xxx' AND stock>=1; 或 乐观锁: UPDATE inventory SET stock=stock-1, version=version+1 WHERE sku='xxx' AND version=old_version AND stock>=1;。
181、分布式ID生成 → 数据库序列 (Sequence)、雪花算法 (Snowflake)、Redis INCR、UUID (注意无序性)。
182、数据倾斜处理 → 加随机前缀打散 (如：CONCAT(salt, key))、两阶段聚合 (先局部聚合再全局聚合)、调整分区键。
183、热点数据更新冲突 → 应用层重试机制 (带退避)、队列缓冲更新请求、数据分片 (Sharding) 分散热点。
184、大事务拆分 → 分批处理 (每批处理N条记录后COMMIT)、使用游标逐条或分批处理、避免在事务中执行耗时操作。
185、历史数据快照 → 按日/周进行全量快照 (Snapshot) 或使用CDC (Change Data Capture) 捕获增量变更并定期合并。
186、用户行为事件回放 → 按 event_timestamp 升序排序 + 按 session_id 分组 + 使用窗口函数或应用层逻辑重构路径。
187、多维度下钻性能 → 预聚合Cube (如：Apache Kylin, Druid)、位图索引 (Bitmap Index)、列式存储 (Columnar Storage)、物化视图。
188、实时用户画像更新 → 流处理引擎 (Flink) + 维度表JOIN (如：HBase, Redis) + 状态管理 + 定期批量同步到数仓。
189、数据冷热分离 → 按访问频率或时间分区，热数据存放在SSD/高性能存储，温/冷数据存放在HDD/OSS等低成本存储，通过生命周期策略自动迁移。
190、查询缓存策略 → 应用层缓存 (如：Redis, Memcached)、数据库查询缓存 (Query Cache, 注意其局限性，如：MySQL 8.0已移除)、物化视图。
191、数据压缩存储 → 使用列式存储格式 (如：Parquet, ORC) + 高效压缩算法 (如：Snappy, ZSTD, LZ4) + 字典编码 (Dictionary Encoding)。
192、高可用读写分离 → 主从复制 (Master-Slave Replication) + 读写分离中间件 (如：ProxySQL, ShardingSphere) 或应用层路由。
193、故障自动切换 → 心跳检测 (Heartbeat) + VIP漂移 (Keepalived) + 自动主从切换脚本 (如：MHA, Orchestrator) + 监控告警。
194、数据库分库分表 → 按用户ID哈希 (Hash) 或范围 (Range) 分片 + 使用分库分表中间件 (如：ShardingSphere, MyCat) 或应用层路由。
195、全局二级索引 → 异步构建 (通过CDC监听主表变更)、保证最终一致性、避免跨分片JOIN (将索引与数据冗余存储在同一分片)。
196、分布式事务 → 两阶段提交 (2PC, 性能差)、TCC (Try-Confirm-Cancel)、Saga (补偿事务)、本地消息表 (最终一致性)。
197、数据库审计日志 → 使用数据库自带审计功能 (如：MySQL Audit Plugin, Oracle Audit Vault) 或触发器 (Trigger) 记录关键操作。
198、敏感操作审批 → 应用层拦截敏感SQL (如：DROP, DELETE without WHERE) + 集成工单系统 + DBA人工审核 + 审批后执行。
199、自动化数据修复 → 设置数据质量监控规则 → 检测到异常 → 自动触发修复脚本 (需谨慎，通常需人工复核确认) → 记录修复日志。
200、数据血缘可视化 → 解析SQL/ETL脚本 → 提取表/列级依赖关系 → 存储到图数据库 (如：Neo4j) → 前端可视化展示。
201、数据质量报告 → 定时运行数据质量检查任务 → 生成报告 (合格率、异常明细) → 设置阈值告警 (邮件/钉钉) → 可视化仪表盘。
202、数据字典自动生成 → 读取 INFORMATION_SCHEMA.COLUMNS / INFORMATION_SCHEMA.TABLES 等系统表 → 生成文档或维护在线数据字典平台。
203、SQL注入防御 → 强制参数化查询、输入校验和过滤 (白名单)、最小权限原则、Web应用防火墙 (WAF)、定期安全扫描。
204、慢查询分析 → EXPLAIN / EXPLAIN ANALYZE 分析执行计划 → 识别全表扫描、缺失索引、临时表、文件排序 → 优化SQL或添加索引。
205、数据库容量规划 → 监控存储空间、CPU、内存、连接数增长趋势 → 预测未来需求 → 制定扩容计划 (垂直/水平) → 自动化扩容脚本。
206、WHERE column != 'value' 为什么不包含 NULL？ → NULL 与任何值比较结果都是 UNKNOWN (非TRUE/FALSE)，WHERE 子句会排除结果为 FALSE 或 UNKNOWN 的行，只保留结果为 TRUE 的行 → 需额外 OR column IS NULL。
207、NOT IN (子查询含NULL) 为什么返回空？ → x NOT IN (a, b, NULL) 等价于 x!=a AND x!=b AND x!=NULL → x!=NULL 结果为 UNKNOWN → 整个AND表达式为 FALSE/UNKNOWN → 无行返回 → 我们应使用 NOT EXISTS 或保证子查询无NULL。
208、LEFT JOIN 后 WHERE 过滤右表字段，为什么变成 INNER JOIN？ → WHERE 在 JOIN 后执行，会过滤掉右表为NULL的行 (即左表独有行) → 应将过滤条件放在 ON 子句中: LEFT JOIN ... ON ... AND right_table.column = 'value'。
209、SUM(column) 有 NULL 会怎样？ → NULL 值被忽略，但如果所有行该列均为NULL，结果是 NULL → 建议 COALESCE(SUM(column), 0) 避免NULL。
210、RANK() 和 ROW_NUMBER() 在并列时区别？ → RANK: 并列排名相同，后续排名跳过 (1,1,3)；ROW_NUMBER: 无并列，强制排序 (1,2,3)；DENSE_RANK: 并列排名相同，后续排名不跳 (1,1,2)。
211、GROUP BY 多列时，顺序影响结果吗？ → 不影响分组结果 (分组是集合操作)，但可能影响输出行的默认排序 (若未显式 ORDER BY) 和性能 (索引匹配顺序)。
212、COUNT(*) vs COUNT(1) vs COUNT(column) 区别？* → `COUNT()和COUNT(1)统计所有行数 (包括NULL)；COUNT(column)` 统计该列非NULL**值的行数。
213、TIMESTAMP 与时区转换常见错误？ → 存储时未用UTC → 展示时未转换 → 直接比较带有时区和不带时区的时间 → 我们应使用 AT TIME ZONE 显式转换 (如：event_time AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Shanghai')。
214、浮点数比较为什么不准？ → 二进制浮点数无法精确表示某些十进制小数 → 用 ABS(a - b) < tolerance (如：0.0001) 代替 a = b → 或改用 DECIMAL 类型。
215、递归CTE无限循环怎么办？ → 设置最大递归深度限制 (如：MAXRECURSION in SQL Server, CYCLE clause in Oracle/PostgreSQL) → 在递归成员中加入业务逻辑深度限制 (如：depth < 10)。
216、用户要求“删除我的所有数据”，你如何用 SQL 实现 GDPR 合规？ → 级联删除 (外键 ON DELETE CASCADE) 或 软删除 (标记 is_deleted=1, deleted_at=NOW()) + 数据备份隔离 + 审计日志记录删除操作 + 定期物理清理软删除数据。
217、数据中台如何统一“GMV”指标口径？ → 建立指标字典平台 → 定义原子指标 (如：订单金额) 和派生指标 (GMV=原子指标+过滤条件) → 唯一SQL定义或API暴露 → 使用审计 → 变更需通知。
218、你如何用 SQL 实现“用户分层动态调整”？ → 每日凌晨跑批任务 → 根据最新行为数据 (如：最近30天消费) 重新计算用户分层 → 更新 user_level 表 (记录生效时间戳) → 业务系统按生效时间读取。
219、数据产品如何支持“下钻到明细数据”？ → 明细表预关联常用维度 → 支持分页查询 (LIMIT/OFFSET 或游标) → 严格的行级/列级权限控制 → 导出功能限制 (如：最大行数) → 性能优化 (索引、分区)。
220、我们如何用 SQL 实现“智能预警”？ → 定时任务计算核心指标 → 与预设阈值或预测值 (如：AVG±3*STDDEV) 比较 → 若超阈值则触发告警 (调用API发送邮件/钉钉) → 记录告警事件到日志表。
221、数据探索中发现“某渠道ROI异常高”，你如何归因？ → 拆解分子分母: ROI=收入/成本 → 分别看收入是否虚高 (刷单?)、成本是否漏计 → 看用户质量: 该渠道用户LTV、留存率是否正常 → 看作弊可能性: IP/设备集中、行为异常 → 对比历史/其他渠道。
四、专家级开放题(222-300题，架构思维)
222、任意维度下钻报表系统 → 宽表预聚合 (Rollup) + 动态SQL生成 + 物化视图 + OLAP引擎 (如：Apache Kylin, Doris, ClickHouse) 。
223、实时用户行为漏斗 → Flink SQL + Watermark处理乱序 + Session Window或Tumble Window + 精确去重 (如：Bitmap或HyperLogLog) + 输出到存储。
224、亿级表加索引不锁表 → Online DDL (如：MySQL 8.0 InnoDB, PostgreSQL)、工具如：pt-online-schema-change (pt-osc)、gh-ost、分批建索引 (创建新表+增量同步+切换)。
225、千亿级日志分析架构 → 数据采集 (Fluentd/Logstash) → 存储 (Elasticsearch for检索, ClickHouse/Doris for分析) → 预聚合 + 采样 + 可视化 (Grafana/Kibana)。
226、实时风控引擎SQL实现 → Flink CEP (Complex Event Processing) 定义规则 + 状态存储 (RocksDB) + 布隆过滤器 (Bloom Filter) 快速判断 + 输出风险信号。
227、数据中台指标体系设计 → 统一指标定义 (原子指标、派生指标、衍生指标) + 维度建模 (星型模型) + 指标字典管理 + API服务化。
228、数据湖查询加速 → 数据湖格式 (Delta Lake, Apache Hudi, Iceberg) 支持ACID和时间旅行 → 分区 + 列存 + Z-Order等索引 → 缓存层 (Alluxio)。
229、多数据源联邦查询 → 查询引擎 (Presto/Trino, Apache Drill) + Connector连接不同数据源 (MySQL, Hive, Kafka, ES) + 查询下推优化。
230、数据服务化(Data API) → API网关 (如：GraphQL, REST) + 缓存 (Redis) + 权限认证 + 限流熔断 + 监控日志。
231、数据血缘自动采集 → SQL解析器 (如：ANTLR解析SQL) + Hook机制 (拦截ETL任务) + 存储到图数据库 (Neo4j, JanusGraph) + 可视化。
232、数据质量监控平台 → 规则引擎 (定义完整性、准确性、一致性等规则) + 异常检测 (阈值、波动) + 告警通知 + 修复建议 + 质量报告。
233、实时数据仓库架构 → Lambda架构 (批流并存) 或 Kappa架构 (纯流式) → 流批一体计算引擎 (Flink + Iceberg/Hudi) → 实时OLAP存储 (Druid, ClickHouse)。
234、数据版本管理 → 数据湖表格式支持 (Delta Lake DESCRIBE HISTORY, Iceberg Snapshot) → 类似Git的版本控制 → 时间旅行查询 (查询历史版本数据)。
235、数据权限精细化控制 → RBAC (Role-Based) + ABAC (Attribute-Based) + 行列级权限 (Row/Column Level Security) + 动态数据脱敏 (Dynamic Data Masking)。
236、数据冷热温分层存储 → 热数据 (SSD, 高频访问)、温数据 (HDD, 中频访问)、冷数据 (OSS/S3, 低频访问) + 生命周期策略自动迁移 + 透明访问。
237、数据库自动化运维 → 监控 (Prometheus+Grafana) + 诊断 (慢查询、锁) + 优化建议 (索引、参数) + 自愈 (如：自动扩容、主从切换)。
238、数据库性能压测 → 工具 (Sysbench, BenchmarkSQL) + 自定义SQL模板 (模拟业务) + 监控指标 (QPS, RT, CPU, IO) + 瓶颈分析 + 优化迭代。
239、数据库灾备方案 → 主从复制 + Binlog/归档日志 → 跨机房同步 (异步/半同步) → 定时全量备份 + 增量备份 → 定期恢复演练。
240、数据库安全加固 → 透明数据加密 (TDE) → 审计日志 → 网络防火墙/IP白名单 → 最小权限原则 → 定期漏洞扫描和补丁更新。
241、数据库成本优化 → 资源监控和利用率分析 → 识别闲置实例/存储 → 自动降配或释放 → 冷数据归档至低成本存储 → 预留实例/节省计划。
242、数据产品埋点设计 → 事件模型 (Event: who, when, what, where, how) → 唯一事件ID + 上下文信息 (page, referrer) → 校验机制 (数据完整性、格式)。
243、数据指标一致性保障 → 统一指标字典 + 单一计算引擎/数据源 + 数据血缘追踪 + 变更影响分析 + 使用审计 + 定期对账。
244、数据服务SLA保障 → 监控 (可用性、延迟、错误率) → 降级策略 (缓存、默认值) → 限流熔断 → 多活/异地容灾 → 告警和应急响应。
245、数据探索平台设计 → 交互式Notebook (Jupyter, Zeppelin) + 可视化图表 + 自助SQL查询 + 权限沙箱 (资源隔离) + 结果导出限制。
246、数据建模方法论 → 维度建模 (Kimball: 星型/雪花模型) vs Data Vault 2.0 (Hub, Link, Satellite) vs Data Mesh (领域驱动、数据即产品)。
247、数据治理框架 → 元数据管理 + 数据质量管理 + 数据安全管理 + 数据生命周期管理 + 数据标准管理 + 组织与流程。
248、数据资产目录 → 自动采集元数据 → 打标签 (业务、技术、管理) → 搜索引擎 → 资产评分 (热度、质量) → 智能推荐。
249、数据驱动增长体系 → AARRR海盗模型 (Acquisition, Activation, Retention, Revenue, Referral) → 指标拆解 → A/B实验平台 → 归因分析 (首次点击、末次点击、线性)。
250、数据智能预警系统 → 时序预测模型 (如：Prophet, LSTM) → 异常检测算法 (如：Isolation Forest, 3σ) → 自动告警 (多通道) → 根因分析建议。
251、数据架构演进路线 → 单库 → 主从读写分离 → 分库分表 → 传统数仓 (T+1) → 实时数仓/数据湖 (Lambda/Kappa) → 湖仓一体 (Lakehouse) → Data Mesh (去中心化)。
252、我们如何用SQL分析大模型(LLM)API调用日志？ → 按 user_id, model_name, DATE_TRUNC('hour', request_time) 聚合 → 计算 SUM(prompt_tokens), SUM(completion_tokens), COUNT(*) → 成本计算 (SUM(tokens * price_per_token)) → 异常检测 (超长prompt, 高频调用)。
253、用户与AI Agent对话路径分析怎么做？ → 按 session_id 分组 → STRING_AGG(intent_type ORDER BY event_timestamp SEPARATOR '→') AS path → 对路径进行聚类分析或计算标准路径 (如：欢迎→问答→结束) 的转化率。
254、AIGC内容生成质量如何用SQL评估？ → JOIN人工评分表 (如：content_id, rating, reviewer_id) → 按 prompt_template_id, model_version, content_category 分组 → 计算 AVG(rating), STDDEV(rating) → 过滤低分或异常评分 (如：WHERE rating > 2) → 分析不同维度下的质量差异。
255、我们如何用SQL支持“向量相似推荐”？ → 若数据库支持向量类型和索引 (如：PostgreSQL + pgvector, ClickHouse, Milvus) → SELECT item_id, title FROM items ORDER BY embedding <-> (SELECT embedding FROM queries WHERE query_id = 123) LIMIT 10 (余弦相似度) → 需预先为 embedding 列创建向量索引。
256、多模态行为(图文+视频+语音)如何统一建模？ → 设计统一的 user_behavior_events 事实表 → 包含 event_type (view_image, watch_video, listen_audio), content_id, duration, engagement_score → 聚合时按 event_type 加权 (如：视频权重=1.5, 图文=1.0) → SUM(engagement_score * CASE event_type WHEN 'watch_video' THEN 1.5 ELSE 1.0 END) AS weighted_engagement。
257、如何分析“AI推荐 vs 人工推荐”转化率差异？ → 在 recommendation_log 表中记录 recommendation_type (ai, manual) → 计算各类型 COUNT(DISTINCT CASE WHEN converted=1 THEN user_id END) / COUNT(DISTINCT user_id) AS conversion_rate → 统计显著性检验 (如：卡方检验) 需在应用层或使用UDF完成，SQL仅提供基础数据。
258、Prompt工程效果评估SQL怎么写？ → 按 prompt_template_id 或 prompt_version 分组 → 计算成功率 (SUM(CASE WHEN status='success' THEN 1 ELSE 0 END) / COUNT(*))、平均响应质量评分 (AVG(quality_score))、平均响应时长 → 结合A/B测试框架，对比不同Prompt版本的效果。
259、LLM幻觉检测SQL逻辑？ → 假设有 fact_checking_results 表记录生成内容与事实的比对结果 → SELECT llm_output_id, COUNT(*) AS hallucination_count FROM fact_checking_results WHERE is_hallucination = 1 GROUP BY llm_output_id → 计算整体幻觉率: SUM(hallucination_count) / SUM(total_statements_checked) → 按 model, prompt_type 分析幻觉率。
260、AI Agent任务完成率分析？ → 按 task_id, agent_id, task_complexity (simple, complex) 分组 → SUM(CASE WHEN final_status = 'completed' THEN 1 ELSE 0 END) / COUNT(*) AS completion_rate → 分析不同Agent、不同复杂度任务的完成率差异 → 识别瓶颈。
261、如何用SQL支持“RAG检索命中率”分析？ → 假设日志表记录每次检索的 retrieved_chunk_ids (数组) 和 relevant_chunk_ids (数组) → 使用数组函数计算交集: ARRAY_LENGTH(ARRAY_INTERSECT(retrieved_chunk_ids, relevant_chunk_ids)) / ARRAY_LENGTH(retrieved_chunk_ids) AS hit_rate → 按 query_type, embedding_model 分组统计平均命中率。
262、万亿级明细表如何实现“秒级任意维度聚合”？ → 预聚合立方体 (Cube) + 物化视图 + 列式存储 (ClickHouse, Doris, StarRocks) + 智能索引 (Bitmap, Bloom Filter) + 分区和分桶 + 向量化执行引擎 + MPP架构 → 牺牲存储空间和ETL时间换取查询性能。
263、如何实现“亚秒级 TopN 实时排行榜”？ → 方案1 (内存): Redis Sorted Set (ZADD, ZRANGE) + 定期从DB同步或流式更新 → 方案2 (流式): Flink + 状态后端 (RocksDB) 维护TopN + 定时输出到DB/缓存 → 方案3 (专用OLAP): ClickHouse/Doris 的 LIMIT N BY 或物化视图 + 高频刷新。
264、COUNT(DISTINCT user_id) 在千亿级如何优化？ → 近似算法: HyperLogLog (HLL) 是行业标准 → 精确算法: 分桶Bitmap (如：RoaringBitmap) + 分布式聚合 (如：Spark, Flink) → 预聚合: 按天/小时预计算去重UV，查询时再合并(如：Bitmap OR)。
265、JOIN 两个十亿级表如何不爆内存？ → 分桶JOIN (Bucketed Join): 保证两表按JOIN键分桶且桶数一致 → 广播JOIN: 若一表可放入内存 → 两阶段JOIN: 先按JOIN键哈希分片，再分片内JOIN → 改用IN/EXISTS: 若右表可高效过滤 → 增加资源/调优参数: 如：Spark spark.sql.shuffle.partitions, spark.executor.memory。
266、窗口函数在亿级数据上太慢怎么办？ → 限定数据范围: WHERE 子句过滤 → 分区裁剪: 按分区键过滤 → 索引优化: 为 PARTITION BY 和 ORDER BY 列建索引 → 改用自连接: 若逻辑简单，有时自连接+索引更快 → 预计算: 预先计算排名或序号存储。
267、我们如何用GPU加速SQL聚合？ → 使用支持GPU的查询引擎: BlazingSQL (基于RAPIDS cuDF), DuckDB-GPU, Spark with RAPIDS Accelerator → 将列式数据加载到GPU显存 → 利用GPU并行计算能力执行聚合、JOIN等操作 → 适合计算密集型而非IO密集型场景。
268、“实时去重UV”在高并发下如何保证性能+准确？ → 精确方案: Redis Bitmap (内存占用大) 或 RoaringBitmap (压缩) + 异步落盘 + 定期对账 → 近似方案: Redis HyperLogLog (HLL, 内存占用小，误差可控) → 混合方案: 热点数据用Bitmap，冷数据或全局用HLL → 分布式方案: 分片 + 合并 (如：Spark Streaming + HLL/Bitmap)。
269、大表模糊搜索(LIKE '%关键词%')如何优化？ → 全文索引: 使用Elasticsearch/Solr 或 数据库内置全文索引 (MySQL FULLTEXT, PG tsvector) → 倒排索引: 自建或使用专用搜索引擎 → n-gram分词: 支持前缀/后缀/中缀匹配 → 改用联邦查询: 将模糊查询下推到ES，其他条件在数据库 → 避免: 尽量重构业务，使用精确匹配或前缀匹配 (LIKE '关键词%')。
270、我们如何实现“滚动7天去重UV”高性能计算？ → 每日存储Bitmap: 每天一个Bitmap存储当日UV → 按位OR: 查询时对最近7天的Bitmap执行 BIT_OR (如：ClickHouse groupBitmapOr) → Population Count: 对合并后的Bitmap执行 BIT_COUNT 得到去重UV → 预计算: 每日凌晨计算昨日的7日滚动UV并存储。
271、万亿级数据如何快速抽样？ → 数据库内置采样: TABLESAMPLE BERNOULLI(1) (行采样) 或 TABLESAMPLE SYSTEM(1) (块采样) → 分片采样: 按分片ID随机选取几个分片 → Bloom Filter预过滤: 若采样条件复杂，可用BF快速排除不满足条件的行 → 分层采样: 按关键维度分层后分别采样。
272、一条SQL上线前要经过哪些检查？ → 执行计划审核: EXPLAIN 看是否走索引、有无全表扫描 → 影响行数评估: EXPLAIN 预估或 COUNT 验证 → 索引确认: 必要索引是否存在 → 权限验证: 执行账号权限是否足够且最小化 → 灰度发布: 先小流量或测试环境 → 回滚方案: 准备好回滚脚本或备份。
273、我们如何做SQL变更的灰度发布？ → 按用户/租户分桶: WHERE user_id % 100 < 10 (先10%) → 按时间灰度: 特定时间段内生效 → 功能开关: 通过配置中心控制是否启用新SQL → 监控指标: 对比灰度组和对照组的核心指标 → 无异常后全量。
274、SQL执行出错如何快速回滚？ → 事务包裹: 保证DML在事务中，出错可回滚 → 幂等设计: SQL本身可重试或重复执行无副作用 → 备份快照: 执行前备份相关数据 → 闪回查询: 利用数据库闪回功能 (如：Oracle Flashback, MySQL Binlog) → Temporal Table: 使用系统版本表查询历史状态。
275、如何监控SQL服务质量(SLA)？ → 埋点监控: 记录每条SQL的执行时间、影响行数、错误码 → 设置阈值: 对执行时间、错误率、QPS设置告警阈值 → 可视化大盘: Grafana展示成功率、P99延迟等 → 自动熔断: 超阈值自动暂停或降级 → 根因分析: 关联慢查询日志、系统指标。
276、生产环境禁止哪些SQL写法？ → SELECT * (应明确列名) → 无WHERE条件的UPDATE/DELETE → 笛卡尔积JOIN (无ON条件) → 过深嵌套子查询 (影响可读性和优化器) → 未使用索引的模糊查询 (LIKE '%...%') → 大表全表扫描 → 高成本窗口函数无分区/排序优化。
277、我们如何做SQL注入自动化扫描？ → 静态扫描: 扫描代码库中的SQL文件或字符串拼接 → 动态扫描: WAF (Web应用防火墙) 监控生产流量 → 规范强制: 开发框架强制参数化查询 → 审计日志: 记录所有执行的SQL用于事后分析 → 渗透测试: 定期进行安全渗透测试。
278、数据订正SQL如何保证安全？ → 双人审核: 至少两人Review SQL逻辑和影响范围 → 备份原数据: 执行前备份相关表或记录 → 事务执行: 包裹在事务中，可回滚 → 影响行数校验: 执行后检查影响行数是否符合预期 → 异步执行: 避免高峰时段，使用任务队列 → 结果核对: 订正后抽样核对数据准确性。
279、你如何管理“临时分析SQL”避免影响生产？ → 独立只读从库: 为分析人员提供专用从库 → 查询超时限制: 设置 max_execution_time → 资源组隔离: 限制CPU/内存/IO配额 → 自动KILL: 监控并自动终止超长或高负载查询 → 权限控制: 仅授予必要表的SELECT权限。
280、SQL版本如何与应用版本联动？ → Git版本控制: SQL脚本与应用代码同库管理，打相同Tag → 变更脚本版本化: Flyway/Liquibase 的 V1__xxx.sql → 发布清单: 发布包包含SQL变更列表 → 自动化部署: CI/CD流水线自动执行SQL变更 → 回滚脚本: 提供对应的 U1__xxx.sql 回滚脚本。
281、你如何做“慢SQL自动优化建议”？ → 解析执行计划: 识别全表扫描、缺失索引、临时表、文件排序 → 推荐索引: CREATE INDEX idx_xxx ON table (col1, col2) → 重写建议: 如：将子查询改JOIN，避免SELECT * → 集成平台: 将建议推送到DBA平台或CI/CD流程 → 人工确认: 自动建议需人工审核后执行。
282、多币种金额如何统一分析？ → 事实表存储: 存原始币种和金额 + 汇率版本号/日期 → 维度表: exchange_rate 表存储各币种对基准币种 (如：USD) 的汇率 → 查询时转换: SUM(amount * rate) AS amount_usd → T+1转换: 批处理任务按日转换并存储 → 标记汇率: 记录使用的汇率版本，保证可追溯。
283、多语言标签如何支持SQL查询？ → 维度表设计: product_name 表包含 product_id, language_code, name → 应用层传参: 查询时传入用户语言 lang='zh-CN' → SQL过滤: JOIN product_name pn ON p.id = pn.product_id AND pn.language_code = 'zh-CN' → 默认值兜底: COALESCE(pn.name, p.default_name) → 缓存: 按语言缓存常用标签。
284、GDPR“被遗忘权”SQL实现要点？ → 软删除优先: 标记 is_deleted=1, deleted_at=NOW() → 数据隔离: 将软删除数据迁移到隔离分区或表 → 审计留痕: 记录删除操作 (谁、何时、删了什么) → 避免JOIN泄露: 查询时默认过滤 WHERE is_deleted = 0 → 定期物理清理: 在合规周期后物理删除软删除数据 → 级联处理: 保证关联数据也被处理。
285、CCPA 合规的数据访问SQL如何设计？ → 提供导出接口: SQL需能按 user_id 拉取该用户所有关联数据 (订单、日志、画像等) → JOIN多表: 需关联多个业务表 → 数据脱敏: 导出前对敏感字段脱敏 (如：手机号、地址) → 加密传输: 导出文件加密 → 访问日志: 记录数据访问请求和操作。
286、算法推荐中的“公平性”你如何用SQL监控？ → 分组统计: 按性别、年龄、地域等敏感属性分组 → 计算指标: 各组的曝光率、点击率、转化率 → 设置阈值: 各组指标差异超过阈值则告警 → 归因分析: 关联特征分析差异原因 → 定期报告: 生成公平性监控报告。
287、某次大促“库存超卖”，SQL层面可能原因？ → 未加锁: SELECT stock ... 后 UPDATE ... stock=stock-1 未加 FOR UPDATE → 事务隔离: 未使用足够隔离级别 (如：未用Serializable或未加锁) → 乐观锁失效: UPDATE ... SET stock=stock-1, version=version+1 WHERE id=? AND version=? AND stock>=1 未校验更新行数 → 缓存不一致: 库存缓存与数据库不同步 → 并发控制: 未限制并发请求数。
288、BI看板数据“昨日突增10倍”，SQL排查路径？ → 查ETL: 是否重跑或重复执行 → 查源表: 源表是否被重复插入或数据异常 → 查JOIN: 是否产生笛卡尔积 (缺少JOIN条件) → 查时间分区: WHERE条件错误 (如：date >= '2025-01-01' 误写) → 查聚合: 是否遗漏GROUP BY导致聚合错误 → 查数据字典: 指标口径是否被修改。
289、用户投诉“被错误分群”，SQL如何复盘？ → 回溯SQL脚本：定位执行的分群逻辑版本，确认无代码误改或参数漂移；检查数据源时效：确认用户属性表(如：年龄、城市)是否延迟或脏数据；验证边界条件：如：age >= 30 与 age > 30 是否语义混淆；处理NULL值：检查是否因未处理NULL导致误排除(如：WHERE age >= 30 自动过滤NULL)；重算验证：在隔离环境重跑SQL，输入投诉用户ID，比对输出标签与预期；输出差异报告：标注错误原因(数据源异常/逻辑错误/NULL处理缺失)，推动修复。
290、Text-to-SQL(NL2SQL)在企业落地难点？ → 语义歧义：自然语言模糊性 vs 表结构精确性(如：“最近”指7天还是30天？)；指标口径不一致：同一指标多版本定义(如：“活跃用户”含不含机器人？)；权限动态注入：生成SQL需自动附加行级/列级权限条件(如：AND dept_id = 'current_user_dept')；性能不可控：AI生成SQL可能未优化，导致全表扫描或笛卡尔积；解决方案：构建语义层(Semantic Layer)统一指标 + 人工审核机制 + 查询缓存 + 成本拦截策略(如：超100万行自动拒绝)。
291、AI辅助写SQL会取代数据分析师吗？ → 不会取代，而是增强：AI擅长语法补全、索引推荐、注释生成、模板填充；人类不可替代价值：提出正确业务问题、解读异常波动、推动策略落地、跨部门沟通；角色演进：从“写SQL的执行者” → “定义问题+校验结果+驱动增长”的数据策略官；未来必备技能：Prompt工程能力 + SQL逻辑校验能力 + 业务抽象能力。
292、如何用SQL支持“用户画像实时更新”？ → 流式处理层：Flink SQL消费Kafka行为日志，实时计算标签(如：“最近7天加购次数”)；存储层选型：写入低延迟KV存储(Redis/HBase)供在线服务调用；数仓同步层：定时批量合并至离线数仓(如：Hive/Iceberg)，支持T+1深度分析；一致性保障：通过版本号或时间戳避免并发写冲突，保证“读你所写”。
293、数据权限如何做到“不同部门看不同城市”？ → 行级权限实现：WHERE city_code IN (SELECT city_code FROM dept_city_mapping WHERE dept_id = CURRENT_DEPT())；动态函数封装：数据库支持Session变量或自定义函数注入当前用户部门ID；权限元数据管理：维护 dept_city_mapping 映射表，支持灵活配置与审计；性能优化：映射表加索引，避免子查询膨胀；可预计算权限Bitmap加速过滤。
294、你如何用SQL实现“近似去重”满足性能要求？ → HyperLogLog (HLL)**：PG/Spark/Flink/ClickHouse 原生支持，误差<2%，内存极小；Bitmap方案：ClickHouse/RoaringBitmap，适合精确或近似去重，支持交并差运算；采样+放大估算：对大数据集采样后乘以采样率，适用于趋势分析非精确场景；选型建议：高精度选Bitmap，极致性能选HLL，临时探索选采样。
295、数据冷启动阶段无历史数据，你如何做分析？ → 小流量实验：灰度发布功能，收集初期用户行为；人工标注兜底：运营/专家标注种子数据，训练初始模型；规则引擎替代：基于业务经验配置规则(如：“新用户首单满减”)；快速迭代机制：建立埋点+反馈闭环，72小时内完成“假设-验证-迭代”；迁移学习：借鉴相似业务/竞品数据做冷启动迁移。
296、我们如何评估一个SQL查询的“成本”？ → 执行时间：端到端延迟(P50/P95/P99)；资源消耗：CPU核时、内存峰值、磁盘IO、网络传输量；锁与阻塞：是否持有行锁/表锁，阻塞其他关键查询；数据库成本模型：利用 EXPLAIN (ANALYZE, COSTS) 或内置代价估算器；业务影响：是否影响核心链路SLA，是否触发熔断。
297、数据伦理题：用SQL分析用户“可能怀孕”，是否合规？ → 法律前提：必须获得用户明确授权(Opt-in)，且符合GDPR/CCPA/《个人信息保护法》；技术实现：避免直接存储“怀孕”标签，改用匿名化特征(如：“母婴品类浏览频次”)；权限隔离：敏感模型仅限授权团队访问，审计留痕；伦理审查：设立数据伦理委员会，评估模型用途与潜在歧视风险；用户权利：提供“反对画像”与“数据删除”通道。
298、SQL如何与Python协作完成复杂分析？ → 分工明确：SQL负责数据提取、聚合、预处理(高效利用引擎)；Python负责统计建模(Scikit-learn)、可视化(Matplotlib/Seaborn)、复杂逻辑(Pandas)；接口方式：通过JDBC/ODBC连接，或使用PySpark/DuckDB嵌入式执行；结果回写：将模型预测结果、特征工程中间表写回数据库供下游消费；调度协同：Airflow/Dagster 编排SQL+Python任务流，确保依赖与重试。
299、未来SQL会被取代吗？请说一下你的观点？ → 不会被取代，而是进化融合：SQL作为声明式语言，在表达集合操作、数据关系上无可替代；扩展方向：流式SQL：Flink SQL、ksqlDB 实现实时计算；图SQL：Cypher、GSQL、SQL/PGQ 标准支持图遍历；AI-SQL：嵌入ML函数(如：PREDICT())、向量相似度、NL2SQL；多模SQL：统一查询关系、JSON、XML、时序、地理空间数据；结论：SQL是数据领域的“普通话”，未来是“SQL+”生态，而非被替代。
300、请用一句话总结你对“数据驱动”的理解。 → “数据驱动”不是看报表，而是用数据定义问题、验证假设、量化结果、闭环迭代，最终让每一次决策都有迹可循、有数可依。
熟练掌握这些 SQL 面试题不仅能帮助你在技术面试中脱颖而出，更是提升日常数据分析与解决复杂业务问题能力的基石。要想深入探讨某一具体技术点或获取更多实战案例，欢迎访问云栈社区的数据库与技术栈板块。同时，对于正在准备求职的同学，可以关注我们的面试求职频道，获取更多简历优化、模拟面试和职业规划的干货内容。如果你对构建高可用、可扩展的数据架构感兴趣，后端与架构板块也有丰富的深度文章等待你的探索。
发表于 前天 02:16
|
查看: 15|
回复: 0
