1. 题目背景与需求解析1148. 文章浏览 I是力扣LeetCode数据库分类中的一道经典题目主要考察SQL查询语句的基本编写能力。这道题出现在许多互联网公司的初级数据分析师和后台开发岗位的面试中因为它能有效检验候选人对基础SQL操作的掌握程度。题目通常会给出一个名为Views的数据库表包含以下字段article_id文章唯一标识author_id作者IDviewer_id浏览者IDview_date浏览日期核心需求是找出所有浏览过自己文章的作者。换句话说需要筛选出那些author_id等于viewer_id的记录。注意实际面试中这道题常被用作热身题但许多候选人会忽略去重或排序的要求导致无法通过所有测试用例。2. 解题思路分析与SQL方案选型2.1 基础解法直接条件筛选最直观的解法是使用WHERE子句直接筛选作者和浏览者相同的记录SELECT DISTINCT author_id AS id FROM Views WHERE author_id viewer_id ORDER BY id;这个方案包含三个关键操作WHERE author_id viewer_id核心筛选条件DISTINCT确保结果去重ORDER BY id按ID升序排列题目常见要求2.2 性能优化思考当数据量较大时比如百万级记录我们可以考虑以下优化方向索引设计如果该查询频繁执行应该在author_id和viewer_id上创建复合索引CREATE INDEX idx_author_viewer ON Views(author_id, viewer_id);执行计划分析使用EXPLAIN检查是否使用了索引EXPLAIN SELECT DISTINCT author_id AS id FROM Views WHERE author_id viewer_id;替代写法对比以下两种写法在大多数数据库中性能相当但第二种可读性更好-- 写法1 SELECT DISTINCT author_id AS id FROM Views WHERE author_id viewer_id; -- 写法2 SELECT author_id AS id FROM Views WHERE author_id viewer_id GROUP BY author_id;3. 完整解决方案与测试用例3.1 标准答案实现考虑所有边界条件后的完整解决方案SELECT DISTINCT author_id AS id FROM Views WHERE author_id viewer_id ORDER BY id ASC;3.2 测试用例设计完整的测试应该包含以下场景测试场景示例数据预期结果基础案例(1, 3, 3, 2023-08-01)包含3无自浏览(1, 3, 5, 2023-08-01)空结果多篇文章[(1,3,3,...), (2,3,3,...)]只返回3一次空表情况空表空结果大量数据100万条含10%自浏览返回约10万不重复ID3.3 执行计划解读对优化后的查询执行EXPLAIN典型输出如下----------------------------------------------------------------------------------------------------------------------- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | ----------------------------------------------------------------------------------------------------------------------- | 1 | SIMPLE | Views | NULL | ref | idx_av | idx_av | 8 | const| 1000 | 100.00 | Using index; Using temporary | -----------------------------------------------------------------------------------------------------------------------关键指标解读type: ref表示使用了索引查找Using index说明是覆盖索引扫描Using temporary表明需要临时表处理DISTINCT4. 常见错误与调试技巧4.1 新手常见错误忘记去重-- 错误写法可能返回重复作者ID SELECT author_id AS id FROM Views WHERE author_id viewer_id;忽略排序要求-- 错误写法结果顺序不确定 SELECT DISTINCT author_id AS id FROM Views WHERE author_id viewer_id;错误使用GROUP BY-- 错误写法缺少聚合函数 SELECT author_id, viewer_id FROM Views WHERE author_id viewer_id GROUP BY author_id;4.2 调试技巧分步验证法-- 第一步确认基础筛选是否正确 SELECT * FROM Views WHERE author_id viewer_id LIMIT 10; -- 第二步添加去重 SELECT DISTINCT author_id FROM Views WHERE author_id viewer_id; -- 第三步完善最终格式 SELECT DISTINCT author_id AS id FROM Views WHERE author_id viewer_id ORDER BY id;使用CTE提高可读性WITH self_views AS ( SELECT DISTINCT author_id FROM Views WHERE author_id viewer_id ) SELECT author_id AS id FROM self_views ORDER BY id;5. 题目变种与扩展思考5.1 常见变种题目变种1统计自浏览次数SELECT author_id, COUNT(*) AS self_view_count FROM Views WHERE author_id viewer_id GROUP BY author_id;变种2查找从未自浏览的作者SELECT DISTINCT author_id FROM Views WHERE author_id NOT IN ( SELECT DISTINCT author_id FROM Views WHERE author_id viewer_id );变种3查找浏览自己文章超过3次的作者SELECT author_id FROM Views WHERE author_id viewer_id GROUP BY author_id HAVING COUNT(*) 3;5.2 实际业务场景应用这个查询模式在实际业务中有多种应用场景用户行为分析识别哪些内容创作者会查看自己的作品异常检测频繁自浏览可能是刷量行为作者活跃度自浏览频率反映作者对自己内容的关注程度在真实业务中我们通常会加入时间维度分析SELECT author_id, DATE_FORMAT(view_date, %Y-%m) AS month, COUNT(*) AS self_view_count FROM Views WHERE author_id viewer_id GROUP BY author_id, DATE_FORMAT(view_date, %Y-%m) ORDER BY author_id, month;6. 性能优化进阶6.1 大数据量优化方案当表数据量超过千万级时可以考虑以下优化物化视图为这个高频查询创建物化视图CREATE MATERIALIZED VIEW author_self_views AS SELECT DISTINCT author_id AS id FROM Views WHERE author_id viewer_id ORDER BY id;分区表设计如果按日期查询频繁可以按月份分区CREATE TABLE Views ( article_id INT, author_id INT, viewer_id INT, view_date DATE ) PARTITION BY RANGE (YEAR(view_date)*100 MONTH(view_date)) ( PARTITION p202301 VALUES LESS THAN (202302), PARTITION p202302 VALUES LESS THAN (202303), ... );使用覆盖索引确保查询只需要扫描索引ALTER TABLE Views ADD INDEX idx_cover (author_id, viewer_id, view_date);6.2 执行计划优化案例假设我们有以下慢查询SELECT DISTINCT author_id AS id FROM Views WHERE author_id viewer_id AND view_date 2023-01-01 ORDER BY id;优化步骤添加复合索引ALTER TABLE Views ADD INDEX idx_optimize (author_id, viewer_id, view_date);重写查询利用索引SELECT author_id AS id FROM Views USE INDEX (idx_optimize) WHERE author_id viewer_id AND view_date 2023-01-01 GROUP BY author_id ORDER BY NULL; -- 避免filesort对于极大数据集考虑分批处理-- 第一段ID 1-10000 SELECT DISTINCT author_id AS id FROM Views WHERE author_id viewer_id AND author_id BETWEEN 1 AND 10000; -- 第二段ID 10001-20000 SELECT DISTINCT author_id AS id FROM Views WHERE author_id viewer_id AND author_id BETWEEN 10001 AND 20000;7. 不同数据库方言实现7.1 MySQL vs PostgreSQLMySQL特有优化SELECT SQL_NO_CACHE author_id AS id FROM Views FORCE INDEX (idx_author_viewer) WHERE author_id viewer_id GROUP BY author_id;PostgreSQL优化方案-- 利用PG的CTE优化 WITH self_views AS MATERIALIZED ( SELECT DISTINCT author_id FROM Views WHERE author_id viewer_id ) SELECT author_id AS id FROM self_views ORDER BY id;7.2 大数据平台适配Hive SQL实现SELECT DISTINCT author_id AS id FROM views WHERE author_id viewer_id DISTRIBUTE BY id SORT BY id;Spark SQL优化-- 利用广播变量优化小表关联 SELECT /* BROADCAST(v1) */ DISTINCT v1.author_id AS id FROM Views v1 WHERE v1.author_id v1.viewer_id ORDER BY id;8. 实际业务场景扩展8.1 用户画像应用将自浏览行为纳入用户画像分析SELECT author_id, COUNT(DISTINCT article_id) AS authored_articles, SUM(author_id viewer_id) AS self_views, SUM(author_id viewer_id) * 100.0 / COUNT(*) AS self_view_ratio FROM Views GROUP BY author_id HAVING COUNT(*) 10 -- 只分析活跃作者 ORDER BY self_view_ratio DESC;8.2 内容质量分析自浏览率与内容质量的相关性分析SELECT CASE WHEN self_views 10 THEN 高自浏览作者 ELSE 普通作者 END AS author_type, AVG(likes.avg_likes) AS avg_likes_per_article FROM ( SELECT author_id, SUM(author_id viewer_id) AS self_views FROM Views GROUP BY author_id ) AS author_stats JOIN ( SELECT author_id, AVG(like_count) AS avg_likes FROM Articles GROUP BY author_id ) AS likes ON author_stats.author_id likes.author_id GROUP BY author_type;8.3 时间序列分析分析自浏览行为的时间模式SELECT author_id, HOUR(view_date) AS hour_of_day, COUNT(*) AS self_view_count, COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (PARTITION BY author_id) AS percentage FROM Views WHERE author_id viewer_id GROUP BY author_id, HOUR(view_date) ORDER BY author_id, hour_of_day;9. 面试技巧与答题策略9.1 面试官考察重点基础能力WHERE条件的使用DISTINCT的理解ORDER BY的运用进阶考察能否想到GROUP BY替代方案是否考虑大数据量情况能否讨论索引优化业务思维能否联想到实际应用场景是否考虑数据质量异常如刷量能否提出扩展分析维度9.2 答题策略建议标准回答流程先写出基础解法主动讨论去重和排序的必要性提出可能的变种问题讨论性能优化方向加分项展示-- 展示对执行计划的理解 EXPLAIN SELECT DISTINCT author_id AS id FROM Views WHERE author_id viewer_id; -- 展示对索引的理解 CREATE INDEX idx_author_viewer ON Views(author_id, viewer_id); -- 展示对大数据量的考虑 SELECT author_id AS id FROM Views WHERE author_id viewer_id GROUP BY author_id -- 替代DISTINCT ORDER BY id;常见追问问题准备如何优化百万级数据的查询如果要求实时更新结果怎么办如何设计数据模型来更高效支持这类查询10. 学习资源与进阶路径10.1 推荐学习资料SQL基础《SQL必知必会》LeetCode数据库题目分类性能优化《高性能MySQL》各数据库官方文档中的优化指南数据分析《SQL for Data Analysis》窗口函数专项练习10.2 系统学习路径初级阶段掌握SELECT基础查询熟练使用WHERE、GROUP BY、ORDER BY理解JOIN操作中级阶段学习索引原理与优化掌握执行计划解读练习复杂子查询高级阶段分区表设计物化视图应用分布式SQL优化10.3 实战练习建议LeetCode题目进阶简单题175, 176, 181中等题184, 534, 550困难题569, 571, 579业务场景模拟-- 模拟电商场景查找浏览自己发布商品的商家 SELECT DISTINCT seller_id FROM product_views WHERE seller_id user_id; -- 模拟社交网络查找查看自己主页的用户 SELECT DISTINCT user_id FROM profile_views WHERE user_id viewer_id;性能对比实验在不同数据量下测试DISTINCT vs GROUP BY对比有无索引的查询时间差异测试不同数据库引擎的表现