如何结合实际问题练习 SQL 和 Python

主页 » Python » 如何结合实际问题练习 SQL 和 Python

如果你离开 SQL 和 Python 几年后想重新开始，感到迷茫是完全正常的。 ——尤其是如果你上一份工作使用的是专有工具和你现在已经不再使用的便捷的 Databricks 笔记本。如今的招聘启事要求掌握 Python、SQL 甚至 PySpark，这可能会让你望而却步，因为每份指南都以“将你的理赔数据集加载到你的数据仓库中”之类的内容开头，而你却在想：“这正是我没有的。”

好消息是，你可以在自己的笔记本电脑上重现大部分学习体验。 本指南将使用免费工具、小型示例数据集和一套结构化的练习题。我们将用通俗易懂的语言，一步步讲解如何构建一个逼真的本地环境，SQL 的工作原理（从基本查询到 JOIN 和聚合），以及如何用 Python 封装这些 SQL 查询，以便您能够练习在现代数据工作中会遇到的各种任务。

使用 SQLite 和 Python 构建一个简单的本地练习环境

你不需要完整的数据库仓库或 Spark 集群来练习 SQL 和 Python。对于学习和面试准备而言，像 SQLite 这样轻量级的嵌入式数据库就足够了。SQLite 将所有数据存储在磁盘上的单个文件中，这使其非常适合用于玩具项目、原型设计和教学练习。

从概念上讲，SQLite 数据库很像一个包含多个工作表的电子表格。每张纸都是一个 表每一行都是一个 记录每一列都是一个 部分在关系数据库术语中，表有时被称为“关系”，行被称为“元组”，列被称为“属性”，但在实际操作中，您可以放心地使用日常术语表、行和列。

Python 自带一个名为 sqlite3 的内置 SQLite 驱动程序。这意味着您无需安装单独的数据库服务器。您的 Python 脚本将打开与数据库服务器的连接。 .sqlite 文件（如果不存在则创建），获取一个 光标 对象（非常类似于文件句柄），然后通过该游标发送 SQL 命令 execute()。 请参阅我们的 SQLite SELECT 和 WHERE 数据读取和筛选的实用示例指南。

虽然本文主要介绍如何使用 Python 驱动 SQLite，但还有一个名为“SQLite 数据库浏览器”的便捷 GUI 工具。 （有时也以 DB Browser for SQLite 的名义发布）。它允许您以可视化的方式查看数据库表，手动插入或编辑少量行，并运行简单的 SQL 语句。它就像一个数据库文件的文本编辑器：在图形用户界面 (GUI) 中进行快速的手动调整更方便，但任何重复性或复杂的操作最好用 Python 脚本编写。

关系型数据库比 Python 列表或字典更严格：它们坚持使用预定义的模式。创建表时，必须声明列名和预期的数据类型（文本、整数、日期/时间等）。SQLite 会以高效的方式存储和索引数据，即使数据集增长到超出内存容量也能保持高效的查找。有关实用学习路径和实践示例，请参阅 使用 SQL 进行数据分析.

使用 SQL 和 Python 创建表并插入数据

首先，你需要一个表格来开始练习——可以把它想象成设计数据的形状。假设你想要一个小型音乐库表格。使用 Python 的 sqlite3 使用该模块，您可以连接到数据库文件，删除任何旧版本的表（如果存在），然后创建一个具有明确类型列的新表。

以下是该流程在 Python 中的概念性描述。你打电话 sqlite3.connect('music.sqlite') 要打开或创建数据库文件，请调用 conn.cursor() 获取游标。通过该游标，您可以运行如下 SQL 命令： DROP TABLE IF EXISTS Songs 清除任何先前的模式，然后是 CREATE TABLE Songs (title TEXT, plays INTEGER) 定义一个包含两列的新表。

表创建完成后，您就可以从 DDL（数据定义语言）切换到 DML（数据操作语言）了。 INSERT 声明在 Python 中，你应该始终使用参数化查询： INSERT INTO Songs (title, plays) VALUES (?, ?) 并传递一个元组，例如 ('Thunderstruck', 20) 作为第二个论点 execute()问号是占位符，Python 会安全地进行替换，帮助您避免 SQL 注入问题和引用错误。

执行插入或更新操作后，必须调用 conn.commit() 将更改刷新到磁盘在提交之前，这些操作仅存在于事务缓冲区中。这与简单的文件写入不同，也是需要尽早养成的关键习惯之一：查询、修改，然后提交。

要读取您的数据，您可以使用 SELECT 语句并遍历游标。。 例如， SELECT title, plays FROM Songs 将每一行数据作为 Python 元组进行流式传输，例如： ('Thunderstruck', 20)游标不会一次性加载所有结果；而是延迟加载行，这在最终处理更大的数据集时非常有用。

核心 SQL 查询元素和 WHERE 筛选

每个 SQL 查询都由一组按标准顺序排列的子句构成。: SELECT, FROM, WHERE, GROUP BY, HAVING和 ORDER BY至少您需要指定所需的列（SELECT）以及来自哪个表（FROM）然后，可选子句可以对结果进行细化、聚合、筛选聚合结果以及对输出进行排序。

此 WHERE 子句会在任何分组或聚合操作之前筛选行。对于数值列，您可以使用比较运算符，例如 =, != （或 <>), >, <, >=, <=文本列支持这些功能，还可以通过模式匹配进行匹配。 LIKE 并通过会员资格检查 IN日期/时间值支持相同的关系比较，并且您经常会看到用以下方式表示的范围： BETWEEN.

SQL 中的空值处理机制非常特殊，值得专门关注。常规比较，例如 = 和 != 不要表现得像你预期的那样 NULL因此，SQL 提供 IS NULL 和 IS NOT NULL 检查缺失值。布尔列通常用于 = 和 !=但你仍然需要 IS NULL 布尔值本身可能缺失。

当您同时满足多个条件时，请记住…… AND 和 OR 遵循优先规则如果你写 age < 5 OR age > 10 AND breed = 'Ragdoll'SQL 将评估 AND 首先，要表达“5岁以下或10岁以上的布偶猫”，应该使用括号： (age < 5 OR age > 10) AND breed = 'Ragdoll'熟悉这些逻辑组合对于实际的分析工作至关重要。

模式匹配 LIKE 允许您搜索以特定片段开头、结尾或包含特定片段的字符串。百分号 % 是任意字符序列的通配符，因此 breed LIKE 'R%' 找出以“R”开头的品种， fav_toy LIKE 'ball%' 找到名称以“球”开头的玩具， coloration LIKE '%m' 查找以“m”结尾的颜色模式。与 AND/OR这就变成了一个强大的文本过滤工具包。

使用玩具数据集练习单表查询

培养肌肉记忆的一个有效方法是，在脑海中固定一个简单的模式，并针对该模式解决大量查询。。 想象一下 cat 表格，列如下： id, name, breed, coloration, age, sex和 fav_toy这为您提供了足够的种类——文本、数字、简单的分类——来练习大多数基本的查询模式。

对于布尔型检查，通常先按一列进行筛选，然后再叠加其他条件。要列出没有记录喜爱玩具的“无趣”公猫，您可以选择 name 协调 sex = 'M' 和 fav_toy IS NULL这说明了如何将空值检查与直接比较相结合，以隔离特定的行子集。

要针对特定​​品种或将其排除在外，你需要将等式与逻辑否定结合起来。只选择特定年龄的布偶猫 breed = 'Ragdoll'（不包括波斯猫和暹罗猫）可能看起来像 breed NOT LIKE 'Persian' AND breed NOT LIKE 'Siamese'虽然有些数据库支持 NOT IN ('Persian', 'Siamese')练习这种明确的模式有助于巩固你对……的理解 NOT 和 LIKE.

像“喜欢逗猫棒但不是波斯猫或暹罗猫的雌猫”这样的练习题，会迫使你综合运用文本过滤器、等式运算符和逻辑运算符。你会选择 id, name, breed, coloration 并使用以下方式约束行 sex = 'F', fav_toy = 'teaser'以及一个排除不想要的品种的复合条件。注意括号内的说明，确保所有子条件都按预期组合应用。

熟悉了这些用原始 SQL 编写的示例之后，请使用参数化查询通过 Python 重新实现它们。编写简短的脚本，询问犬种、最小年龄或玩具犬类型。 input()将它们插入 WHERE 执行子句，并打印结果。这正是许多初级数据工程师所期望的，连接查询编写和实际应用程序代码的桥梁。

理解和实践 SQL 连接

一旦你不再纠结于玩具问题，你就会不断地加入多个桌子。连接（JOIN）用于连接相关的数据集：例如客户与订单、艺术家与作品、游戏与公司等等。在 SQL 中，您需要指定哪些列在表之间应该匹配，数据库引擎会将这些行合并成一个组合的结果集。

在面试和实际项目中，你会遇到四种主要的加入类型。: INNER JOIN （通常写作“ JOIN), LEFT JOIN, RIGHT JOIN和 FULL OUTER JOIN内连接仅返回两个表具有匹配键的行；左连接保留左表中的所有行，并将左表中的所有行填充到右表中。 NULL当右表中没有匹配项时，右连接会执行对称操作；而全外连接会返回两侧的每一行，尽可能进行匹配并使用 NULL 哪里没有。

想想 LEFT JOIN 和 RIGHT JOIN 作为“更信任这一方”的行动使用左连接时，左表是主要数据源：即使右表没有贡献任何数据，左表中的每一行也至少会在输出中出现一次。使用全连接时，两侧没有特殊地位——只需将两个表中的所有键合并在一起，并在重叠部分进行对齐即可。

为了保持多表查询的可读性，请始终为表设置别名。而不是写 SELECT artist.name 反复写 FROM artist AS a 然后引用列为 a.name。 同样的， piece_of_art 可以变成 poa和 museum 可 m当你的查询增加到三个或更多连接时，好的别名是清晰与混乱之间的区别。

经典的训练设置使用三张表格： artist, museum和 piece_of_art。 该 artist 桌子可能容纳 id, name, birth_year, death_year 以及像水彩画或雕塑这样的主修领域。 museum 桌子商店 id, name 和 country。 该 piece_of_art 桌子 id, name, artist_id 和 museum_id最后两列是外键，将每件艺术品与其创作者和所在地关联起来。

利用该模式，您可以练习内连接、左连接和条件筛选。例如，要列出1800年后出生且活过50年的艺术家及其作品名称，您需要加入 artist 和 piece_of_art on artist.id = piece_of_art.artist_id 然后进行过滤 death_year - birth_year > 50 和 birth_year > 1800将选定的列另名为 artist_name 和 piece_name 为清楚起见。

要查看所有艺术品及其收藏博物馆名称和国家/地区——包括那些“遗失”且无博物馆收藏的艺术品—— 你会用到一个 LEFT JOIN ，来自 piece_of_art 至 museum on museum_id这样一来，即使没有关联博物馆的艺术作品也会出现在结果中。 NULL 在博物馆列表栏中。筛选符合条件的行 artist_id IS NULL 让您能够发现不知名艺术家的作品，同时还能加入收藏这些作品的博物馆。

更高级的练习需要你同时连接三张桌子。要列出每件艺术品的艺术家和博物馆名称，您需要加入。 museum 至 piece_of_art on museum.id = piece_of_art.museum_id然后加入 artist on artist.id = piece_of_art.artist_id使用普通 JOIN （内部连接）有意删除缺少艺术家或博物馆的艺术品，让您了解连接类型如何影响行数。

聚合、GROUP BY 和 HAVING 的实际应用

一旦能够检索和合并数据，下一个重要的技能就是汇总数据。聚合函数 SUM(), AVG(), COUNT(), MAX()和 MIN() 计算多行集合的指标。 GROUP BY 它会将数据集划分成多个组，并在每个组内应用这些函数——例如，按年份、公司或艺术家划分一个组。如果您更喜欢通过结构化的课程来练习这些概念，请参阅…… 全面的 SQL 课程.

想象一个简单的 sales_table 带列 year, month和 sales一片平原 SELECT SUM(sales) AS total_sales FROM sales_table 计算所有行的总和。 GROUP BY year 问题发生了变化：现在你问的是每年的总销售额，而不是一个总数。

关键规则是，您表格中的每个非聚合列都必须是这样的： SELECT 必须出现在 GROUP BY。 如果你选择 year 和 SUM(sales)，你按以下方式分组 year。 如果你选择 year 和 month 结合聚合数据，然后按两者进行分组。 year 和 month从概念上讲，分组列的不同组合定义了各个组。

WHERE 和 HAVING 两者都是过滤器，但它们在不同的阶段发挥作用。. WHERE 在进行任何分组或聚合之前，先对原始行进行筛选。 HAVING 使用聚合表达式筛选分组结果。例如，您可以…… WHERE production_year BETWEEN 2000 AND 2009 然后 HAVING SUM(revenue) > 4000000 只保留那些“优秀游戏”创造的收入超过四百万美元的公司。

更贴近实际的练习方案是 games 表 像这样的列 id, title, company, type, production_year, system, production_cost, revenue和 rating仅凭这张表，你就可以进行平均值、计数、求和、分组和排名——这些都是分析 SQL 的基本功能。

例如，要计算2010年至2015年间发行且评级大于7的游戏的平均制作成本你会选择 AVG(production_cost) 并用以下方式约束行 WHERE production_year BETWEEN 2010 AND 2015 AND rating > 7这是一个经典的面试题，你可以轻松地将其嵌入 Python 代码中，并打印出结果的单个数字。

您还可以直接从同一数据源生成年度统计数据。 games 表分组 production_year然后计算 COUNT(*) AS count, AVG(production_cost) AS avg_cost和 AVG(revenue) AS avg_revenue这种查询方式可以提供简洁的时间序列视图，这在 BI 仪表板和报表工具中非常常见。

要按公司历年的毛利润进行排名，您可以汇总以下数据： company一个方便的图案是 SELECT company, SUM(revenue - production_cost) AS gross_profit_sum FROM games GROUP BY 1 ORDER BY 2 DESC。 这里 GROUP BY 1 和 ORDER BY 2 使用列位置 SELECT 列表可以保持简洁，但必须谨慎使用，以免以后重新排列列顺序而破坏查询。

更复杂的提示将筛选器、分组和聚合后筛选器联系起来。假设你将“优秀游戏”定义为 2000 年至 2009 年间制作、评分高于 6 分且收入大于制作成本的游戏。对于每家公司，你希望统计其优秀游戏数量及其总收入，但仅限于优秀游戏收入超过 4,000,000 万美元的公司。你可以使用以下方法筛选数据行。 WHERE on production_year, rating以及盈利能力，按以下方式分组 company, 计算 COUNT(company) 和 SUM(revenue)，然后申请 HAVING SUM(revenue) > 4000000这一个查询就涵盖了你在分析任务中会遇到的绝大多数实际思维步骤。

使用多个表和键对数据进行建模

单表设计虽然也能满足大部分需求，但关系型数据库的优势在于能够对跨多个表的数据进行规范化处理。规范化是指消除冗余存储并通过键来表示关系的过程。这可以使数据库更小、更快、更不容易出错。

一个简单但很有启发性的例子来自抓取类似 Twitter 的社交图谱。假设你想追踪用户账号以及它们之间的“关注”关系。一种简单的办法是创建一个单独的表格，每行都以文本形式记录关注者和被关注者的名称。但这很快就会导致大量重复和拼写不一致。

相反，你将事物分成…… People 一张桌子和一个 Follows 表. People 可能有一个整数 id 作为主键，一个唯一的 name （屏幕名称或用户名），以及 retrieved 标记表明您是否已经抓取过该帐户的好友列表。 Follows 存储整数对 from_id 和 to_id表示从一个用户到另一个用户的定向连接。

该模型由三个关键概念构成：逻辑键、主键和外键。逻辑键是外部世界用来指代记录的符号——在这里，指的是 Twitter 账号。 name主键通常是数据库生成的整数（id外键能够唯一标识每一行，并且索引和比较的成本很低。外键是一个整数，它指向另一个表中的主键。 from_id 和 to_id ，在 Follows 表中的外键引用了 People.id.

为了确保数据质量，您需要在表定义中声明约束。。 例如， name TEXT UNIQUE in People 确保您不会意外插入两行具有相同句柄的数据。 UNIQUE(from_id, to_id) 约束 Follows 防止您多次存储相同的跟随边。这些限制在您开始用 Python 编写 upsert 逻辑时，也起到了安全保障的作用。

在Python中 sqlite3 模块，一种常见的模式是使用 INSERT OR IGNORE 优雅地尊重这些限制如果您尝试插入一个 name 如果该操作已存在，SQLite 会静默跳过该操作，而不是报错。然后您可以检查该操作。 cursor.rowcount 为了查看是否确实添加了一行，并依赖于 cursor.lastrowid 发现分配 id 适用于新添加的用户。

当你的代码接收到新的屏幕名称时，它应该首先尝试查找对应的屏幕名称。 id。 如果一个 SELECT id FROM People WHERE name = ? 返回一行，你可以重复使用该整数。否则，你需要插入名称。 retrieved = 0提交，然后读取 lastrowid这种“查找或插入”模式是许多数据导入脚本的核心。

一旦知道了关注者和被关注者的 ID，就可以记录这种关系了。 Follows 只是另一个 INSERT OR IGNORE。 你的 UNIQUE(from_id, to_id) 约束可以解决重复项问题，您可以专注于接下来要抓取哪些配置文件的更高层次的逻辑，而不是对行去重进行微观管理。

使用 JOIN 从规范化表中重建关系

规范化模式以间接性取代了冗余：你存储的是整数而不是重复的字符串，但现在你必须连接表才能重建完整的数据结构。这正是 SQL 的精髓所在。 JOIN 它的设计初衷就是为了方便用户使用，一旦你习惯了，就会觉得使用 JOIN 操作的查询非常自然。

在社交图谱示例中，如果您想查看哪些用户与 id = 2 正在关注你会加入 Follows 至 People 在目标侧。从概念上讲，你运行 SELECT * FROM Follows JOIN People ON Follows.to_id = People.id WHERE Follows.from_id = 2这样就生成了包含每个被关注者的数字边和人类可读名称的组合行。

结果中的每一行都是一个“元行”，它合并了两个表中的列。前两列可能是 (from_id, to_id) ，来自 Follows而后续的列则属于 People - 喜欢 (id, name, retrieved)。 因为 JOIN 条件强制执行 Follows.to_id = People.id您可以清楚地看到这种关系：每一行的第二列和第三列都匹配。

这种模式自然而然地扩展到了更多表格。你已经看到了。 artist, piece_of_art和 museum推特爬虫程序也用这种方式说明了这一点。 People 和 Follows在更复杂的分析管道中，您可以将事实表（事件、订单）与多个维度表（用户、产品、活动）连接起来，以回答多方面的问题。

在调试代码或了解数据库模式如何运作时，“先运行 Python，然后用 DB Browser for SQLite 进行检查”的工作流程非常有效。执行脚本以填充数据库，关闭任何锁定文件的 GUI 实例，然后打开 .sqlite 在浏览器中打开文件。从那里您可以检查每个表的内容并运行临时命令。 SELECT 提出问题以验证您的假设。

需要注意的是：SQLite 会强制执行文件锁定，因此如果数据库浏览器以编辑模式打开数据库，您的 Python 脚本可能无法连接或提交数据。解决方法是在再次运行 Python 代码之前，先在图形用户界面 (GUI) 中关闭数据库（或者完全退出浏览器）。养成关闭锁定数据库文件的工具的习惯，可以避免出现莫名其妙的“数据库已锁定”错误。

将这些技术结合起来——模式设计、约束、Python 中的参数化查询、JOIN、GROUP BY 和 HAVING——就能构建一个强大的本地实验室。 它能让你练习到实际工作中会遇到的 SQL 和 Python 操作。只需 SQLite 和几个结构良好的示例表，你就可以练习面试题型、构建分析逻辑原型，并重拾使用现代数据工具的信心。

DataLemur 等平台和互动课程的定位

除了本地实践之外，互动平台还可以为您提供更具指导性的体验和即时反馈。源于真实行业经验的工具——例如，由前 Facebook 和 Google 数据工程师创建的平台，他们每天编写 SQL 和 Python 并运行 A/B 测试——通常以真实的面试问题和分析场景为中心来构建内容。

涵盖统计学、机器学习和数据访谈商业直觉的书籍对理论学习很有帮助。但它们并不总是能提供许多学习者渴望的动手实践 SQL 环境。一些现代工具正是为了填补这一空白而开发的：它们将数百个面试题型重新打包到一个浏览器内的 SQL 和分析环境中，这样你就可以运行、调整和重新运行查询，而无需担心本地配置。你还可以尝试一些应用示例，例如： 客户流失风险评估 将 SQL 与基本机器学习工作流程相结合。

你还可以找到与我们这里讨论过的主题相对应的交互式 SQL 课程。：单表查询 SELECT 和 WHERE包括跨两到三个表的连接、聚合和分组、子查询等等。许多此类课程都依赖于真实的数据集——例如游戏、博物馆或交易销售数据——因此，这些问题感觉像是真正的商业问题，而不是人为设计的谜题。

如果你觉得 PySpark、DuckDB 或 dbt 等工具的文档太多难以理解，完全可以等到你的 SQL 基础扎实后再去学习它们。首先专注于 SQLite 和 Python，可以让你轻松掌握核心查询模式，而无需费力配置集群或处理云权限问题。一旦掌握了这些基础知识，学习 PySpark 的重点就更多地放在分布式执行上，而不是新的查询概念上。

最终，简单的本地设置、结构化的练习题以及偶尔使用交互式平台相结合，取得了成功。 它能让你兼得所有优势：完全掌控学习环境、夯实概念基础，并接触到顶尖雇主最青睐的题型。通过持续练习，曾经令人望而生畏的 SQL、Python 和数据工程工具组合将变成一套你能够自信运用、甚至乐在其中的工具包，助你在新的岗位上游刃有余。

综上所述，你的前进方向很明确：用 Python 搭建一个 SQLite 数据库，设计几个实际可用的表，深入学习基础和中级 SQL 模式（过滤器、连接、聚合、分组、HAVING 子句），将这些查询封装成 Python 脚本，还可以选择使用由经验丰富的从业者构建的交互式 SQL 平台来辅助学习，这些从业者也曾和你一样处于这个阶段。这样做，你将重建你的技术本能，减少对现代数据栈的焦虑，并准备好应对当今数据角色对 SQL 和 Python 的需求。