SQL 与 dbt 分析工程实践——SQL 分析实践

在广阔的数据与分析领域中，选择合适的工具和技术来高效处理和操作数据至关重要。有一种工具经受住了时间考验，并始终处于前沿位置，那就是 Structured Query Language（SQL）。它提供了一种强大且通用的数据处理方式，使其成为任何 analytical development task 的优秀首选。SQL 是一种用于管理和操作 relational databases 的标准化编程语言，使 data professionals 能够高效检索、存储、修改和分析数据库中存储的数据。得益于其直观语法和在社区中的广泛接受，SQL 已成为 data specialists 的标准语言，他们使用 SQL 与 databases 交互，并从复杂 datasets 中获得有价值的 insights。

在今天 data-driven 的世界中，SQL 是 data consumption 和 analysis 的支柱。Businesses 高度依赖它来执行 data analytics operations，以获得 competitive advantage。SQL 的通用性和丰富功能使其成为 analytics professionals 的必备工具，使他们能够检索特定 data subsets、执行复杂 aggregations，并 join 多个 tables，以发现 data 中隐藏的 patterns 和 relationships。

SQL 的一个关键优势，是它能够快速 retrieve 和 manipulate data，并提供广泛 query capabilities。这使 data specialists 能够根据特定 criteria 过滤、排序和分组 data，只检索必要 data，从而最小化 resource usage 并提升 performance。此外，SQL 支持 data manipulation operations，例如 inserting、updating 和 deleting records，从而促进 analysis 前的数据清洗和准备工作。

使用 SQL 的另一个相关好处，是它可以与各种 analytics tools 和 ecosystems 无缝集成，例如 Python 或 BI platforms，这使其成为 data professionals 的首选语言，并允许他们将 SQL 的力量与 advanced statistical analysis、machine learning algorithms 和 interactive visualizations 结合起来。此外，cloud-based databases 和 data warehouses 的兴起进一步增强了 SQL 在 analytics consumption 中的重要性，Google BigQuery、Amazon Redshift 和 Snowflake 等平台都支持 SQL 作为主要 querying language。

在本章中，我们将讨论 SQL language 作为最常用 analytics languages 之一所具备的 resiliency。然后，我们将探索 databases 的 fundamentals，并介绍 SQL 作为与 databases 交互的标准语言。我们还会考察 views 的创建和使用，views 为简化 complex queries 和抽象 data structures 提供了强大机制。

随着我们深入 SQL，将回顾 window functions，它们使你能够执行 advanced calculations 和 aggregations。此外，我们将深入 CTEs，它们提供一种创建 temporary result sets 并简化 complex queries 的方式。最后，我们还将简要介绍用于 distributed data processing 的 SQL，并以一个 bonus section 结束，展示 SQL 如何用于 training machine learning models。

The Resiliency of SQL

随着时间推移，我们看到用 SQL 开发的 data engineering pipelines 往往能够持续运行多年，而且 queries 和 stored procedures 仍然是支撑 financial institutions、retail companies，甚至 scientific activities 的多个 critical systems 的核心。然而，令人惊讶的是，SQL 不仅被广泛使用，而且持续演进，不断加入新 features，以满足 modern data processing 的需求。同样令人着迷的是，dbt、DuckDB，甚至新的 data manipulation library Polars 等技术，都通过 SQL interface 提供自身功能。那么，SQL 如此流行的主要原因是什么？我们认为可以突出几个因素。

首先也是最重要的一点，是 code readability。这是 data engineering 中的关键方面。SQL 的 syntax 虽然通用，但根据 context 和 specific requirements，可以支持 imperative 和 declarative 两种使用方式。许多 queries 涉及 imperative tasks，例如为某个 user 检索特定 data，或为给定 date range 计算 results。然而，当你指定想要什么 data，而不是规定如何 retrieve data 时，SQL 的 declarative nature 就会表现出优势。这种 flexibility 使广泛用户，包括 BI developers、business analysts、data engineers 和 data scientists，都能理解和解释代码。不同于某些严格 imperative 的 data processing languages，SQL 允许作者专注于描述 desired results。这种 self-documenting feature 使 SQL code 更 readable 和 understandable，并促进 cross-functional teams 中的有效 collaboration。

另一个令人兴奋的因素是，虽然 SQL 作为 interface 经受住了时间考验，但现实是，它背后的 engines 在过去几年已经发生巨大演进。传统 SQL engines 得到了改进，而 Spark 和 Presto 等 distributed tools 使 SQL 能够处理 massive datasets。近些年，DuckDB 成为 game-changer，它赋予 SQL 在单机上执行极快 parallelized analytics queries 的能力。DuckDB 的功能可以与其他 high-performance alternatives 相媲美，为各种规模的 data engineering tasks 打开了新的可能性。

不过，必须注意，并非所有 SQL-powered systems 都相同。例如，SQL Server 虽然常用于 warehousing，但它是为 OLTP 设计的。另一方面，Snowflake 和 Redshift 等平台是专门的 OLAP data warehouses。它们擅长处理 large-scale analytical workloads，并针对 complex queries 和 reporting 做了优化。这些区别体现了 SQL 的通用性，它可以适配各种 database architectures 和 purposes。SQL 仍然是一种统一语言，弥合 OLAP 与 OLTP systems 之间的 gap，使不同 database types 和 technologies 之间的数据访问和分析成为可能。

Data typing 是 SQL 的另一个显著优势，尤其是在 data engineering 中。经验丰富的 data engineers 都理解在各种 programming languages 和 SQL engines 之间管理 data types 的挑战，这个过程可能费力且容易出错。然而，SQL engines 擅长 enforcing strong data typing，保证 data types 在整个 data pipeline 中一致处理。此外，SQL ecosystem 提供 Apache Arrow 等有价值工具，用于解决 diverse tools 和 databases 带来的 compatibility issues。Arrow 支持跨 R、Python 和各种 databases 等 environments 的 robust 和 consistent data type handling。选择与 Arrow 兼容的 SQL engines，可以有效缓解许多 data typing challenges，简化 maintenance efforts，并降低 dependency management 的负担，使 data engineers 能更多关注 data engineering work 的核心方面。

SQL 与 software engineering best practices 的兼容性，是 data engineering 领域的重要优势。Data engineers 经常处理 complex SQL scripts，而这些 scripts 是组织 data pipelines 的重要组成部分。过去，维护和修改这类 scripts 是一项重大挑战，经常导致代码难以理解和修改。然而，SQL tools 的发展已经应对了这些挑战，使 SQL code 更容易适配良好的 technical practices。一个值得注意的进展是 DuckDB 的出现，它是一种专门为 analytical queries 设计的 SQL engine。DuckDB 的独特 features，例如无 dependencies，并针对 analytical workloads 优化，使 data engineers 能够执行 unit tests，并促进 SQL code 快速迭代。这确保 SQL code 符合 established technical principles，提升其 reliability 和 maintainability。

SQL ecosystem 中另一个有帮助的工具是 CTEs，它们可用于将 large queries 拆分为更小、更 manageable 和 testable 的部分。通过将 complex queries 拆分为 semantically meaningful components，data engineers 可以轻松独立验证和检查每个部分，从而促进更 modular 和 robust 的 development process。

其他改进也在帮助 SQL 走向 analytics engineering 的前沿。Lambda functions 允许 data engineers 直接在 SQL statements 中编写任意 functions。这种能力提升了 SQL code 的 flexibility 和 agility，并支持在 data processing 过程中进行 dynamic calculations 和 transformations。

Window functions 也长期被认为是 SQL 中的有价值工具，因为它们通过将 data 划分为 manageable segments，提供增强的 analytical capabilities。借助 window functions，data engineers 可以在定义好的 data subsets 上执行 complex aggregations、rankings 和 statistical calculations，为 analysis 和 reporting 打开新的可能性。

最后但同样重要的是，modern SQL engines 已经纳入 full-text search、geodata functions 和 user-defined functions 等 features，进一步扩展了 SQL 的能力。这些 additions 面向特定 use cases 和 domain-specific requirements，使 data engineers 能够在 SQL environment 中执行 specialized operations。

所有这些，以及更多因素，共同促成了 SQL 随时间保持 resiliency，并鼓励许多人投入学习和应用 SQL，将其用于日常 analytics activities。现在，让我们退一步，重新回顾 SQL 的核心概念。

Database Fundamentals

扎实理解 database fundamentals 对 analytics 和 data engineers 至关重要。Databases 是存储、组织和检索海量数据的基础支柱。随着时间推移，databases 的演进为 SQL 作为一种强大且被广泛采用的 relational databases 工作语言的出现和完善铺平了道路。不过，在探索 databases 的具体细节之前，有必要先理解 data、information 和 knowledge 的更广阔 context，因为它们要么存在于 databases 中，要么由 databases 派生而来。

在这个 context 的基础上，我们有 DIKW pyramid，如图 3-1 所示。这个 conceptual model 描述了 data、information、knowledge 和 wisdom 之间的 hierarchical relationships。通过一系列 iterative processes，DIKW pyramid 提供了一个 framework，用于理解如何将 raw data 转化为 actionable wisdom。

图 3-1：DIKW pyramid

为了更好理解 DIKW pyramid，让我们分解每一层：

Data

缺乏 context 和 meaning 的 raw facts and figures。Data 可以被看作 information 的 building blocks。Data 示例：1989、teacher、green。

Information

Data 的 organized 和 structured representation，提供 context 并回答 specific questions。Information 示例：

• 我的数学老师出生于 1989 年。
• Albany Ave 和 Avenue J 交叉口的 traffic light 是绿色的。

Knowledge

当我们将 information 与 experience、expertise 和 understanding 结合时，就会产生 knowledge。它表示通过 analyzing 和 interpreting information 获得的 insights，使 individuals 和 organizations 能够做出 informed decisions 并采取 appropriate actions。Knowledge 示例：

• 既然我的数学老师出生于 1989 年，那么他是成年人。
• 我正驶向的 traffic light 正在变绿。

Wisdom

一种超越 knowledge 的 deep understanding 层级。Wisdom 发生在 individuals 和 organizations 能够应用自己的 knowledge 并做出 sound judgments，从而产生 positive effects 和 transformative insights 时。Wisdom 示例：

• 我的数学老师也许该开始考虑 retirement savings plan 了。
• 随着 traffic light 变绿，我可以继续向前行驶。

Databases 在 DIKW pyramid 中发挥重要作用，它们是存储、管理和组织 data 的基础。这使 data 能够转化为 meaningful insights，最终让 businesses 获得做出 educated decisions 所需的 knowledge。

Types of Databases

Databases 是 modern data management systems 的核心组成部分，为 data storage、organization 和 retrieval 提供 structured approaches。为了更好理解 database 如何实现这一点，我们先探索两大 database categories：relational 和 non-relational。通过理解这两类 databases 的 features 和 differences，你将更有能力根据自己的具体 data needs 选择 database solution。

图 3-2 展示了 databases 的两个主要 categories，并在每个 category 中映射了最常见的 database types。

图 3-2：Database categories and their types

Relational databases

这是最常见、采用最广泛的 database category，其中 data 被组织为由 rows 和 columns 组成的 tables。Keys 用于强制 tables 之间的 relationships，而 SQL 用于 querying 和 manipulating data。Relational databases 提供 strong data integrity、transactional reliability，并支持 ACID properties，确保 database transactions reliable、maintain data integrity，并能从 failures 中恢复。

Non-relational databases

Non-relational databases 也称为 NoSQL（not only SQL）databases，它们作为一种替代方案出现，用于以 scalability 和 flexibility 管理大量 unstructured 和 semi-structured data。与 relational databases 相比，non-relational databases 不依赖 fixed schemas。它们可以以多种 formats 存储 data，例如 key-value pairs、documents、wide-column stores 或 graphs。Non-relational databases 优先考虑 high performance、horizontal scalability 和 schema flexibility。它们非常适合 real-time analytics、处理 unstructured data 的 applications，以及 IoT data 等场景。

NOTE

在接下来的章节中，鉴于本章总体目标是介绍 SQL fundamentals，我们将主要聚焦 relational databases。

我们可以把 database 想象为 data universe 的一个子集——它以特定于组织的目的而 built、designed，并 fed with data。Databases 是社会的重要组成部分。以下一些活动广泛分布在整个社会中，其中心都存在一个 database 来存储 data：

• 预订酒店
• 预订机票
• 在知名 marketplace 购买手机
• 登录你最喜欢的社交网络
• 去看医生

但这在实践中是什么样子？进入 relational databases 后，我们将 data 组织到带 rows 和 columns 的 tables 中。Tables 表示我们 universe 中的一个 entity，例如大学中的 student，或图书馆中的 book。Column 描述 entity 的 attribute。例如，student 有 name 或 address。Book 有 title 或 ISBN（International Standard Book Number）。最后，row 就是 data 本身。Student 的 name 可以是 Peter Sousa 或 Emma Rock。对于 book title，一行可以是 “Analytics Engineering with SQL and dbt”。图 3-3 展示了一个 table 及其 columns 和 rows 的示例。

图 3-3：带有 rows 和 columns 的 table 示例

另一个需要考虑的话题，是如何建立 data 之间的 relationships，并保证 consistency。这是 relational databases 中需要强调的关键因素，因为我们可以通过 keys 在 tables 之间强制 connections。在 relational database 中强制这些 relationships 和 connections，涉及实现一些机制来维护 related tables 之间 data 的 integrity 和 consistency。这些机制维护 tables 之间的 relationships，防止 inconsistencies 或 data anomalies。

强制 relationships 的一种方式是使用 primary keys 和 foreign keys。我们稍后会讲到，但现在，图 3-4 展示了 tables 之间的 interrelationship。该 use case 是一所大学，其中一个或多个 students 可以 enroll in 一个或多个 classes。

理解这些 database types 为下一个话题打下基础：database management systems（DBMSs）。下一节中，我们将更深入地讨论 DBMSs 的 functionalities 和 importance，它们是 software tools，使各种 types of databases 中高效 data storage、retrieval 和 management 成为可能。

图 3-4：Tables interrelationship

Database Management System

DBMS 是一个 software system，使 database creation、organization、management 和 manipulation 成为可能。它为 users 和 applications 提供与 databases 交互的 interface 和一组 tools，从而支持高效 data storage、retrieval、modification 和 deletion。

DBMS 作为 users 或 applications 与底层 database 之间的中介。它抽象了与 database 交互的复杂性，提供一种方便且 standardized 的 data 工作方式。它作为 software layer，处理 data 的 storage、retrieval 和 management，同时确保 data integrity、security 和 concurrency control。

DBMS 的主要 functions 包括以下内容：

Data definition

DBMS 允许 users 通过创建和修改 database schemas 来定义 data 的 structure 和 organization。它支持定义 tables、columns、relationships 和 constraints，这些内容管理 database 中存储的 data。

Data manipulation

Users 可以使用 query language，通常是 SQL，对 database 中存储的 data 执行 operations。DBMS 提供插入、检索、更新和删除 data 的机制，使 database content 能被高效且受控地操作。

Data security and integrity

DBMS 通过 enforcing access control policies 提供确保 data security 的机制。它支持定义 user roles 和 permissions，限制对 sensitive data 的访问。此外，DBMS 通过实现 constraints 和 validations 来 enforce data integrity，以保持 data 的 consistency 和 accuracy。

Data concurrency and transaction management

DBMS 处理多个 users 或 applications 对 database 的 concurrent access，确保 data 保持一致，并防止 conflicts。它提供 transaction management capabilities，确保一组 operations 能够根据 ACID properties 被 reliable 和 consistent 地执行。

Data recovery and backup

DBMS 纳入确保 data durability 和 recoverability 的 features。它提供 data backup 和 restore mechanisms，使系统 failures 或 disasters 发生时可以 recovery data。

一些最常见的 relational 和 non-relational databases 的 DBMSs 可见于表 3-1。

表 3-1：常见 DBMSs

Relational databases	Non-relational databases
Microsoft Access	MongoDB
Microsoft SQL Server	Apache Cassandra
Postgres	Apache CouchDB
MySQL	Redis
SQLite	Elasticsearch

“Speaking” with a Database

从外部视角来看，通过 DBMS 与 database 交互，提供四类 language：

Data definition language（DDL）

用于处理 schemas，例如 table creation。

Data manipulation language（DML）

用于处理 data。

Data control language（DCL）

用于管理 database permissions。

Transaction control language（TCL）

用于处理 database 中发生的 transactions。

图 3-5 展示了与 database 交互时使用的主要 languages 及其 primary commands。

图 3-5：Main SQL commands

对于本书，我们的主要重点是通过学习如何 query、manipulate 和 define database structures，为 SQL 打下坚实基础，因此会讨论 DDL 和 DML。与 administration tasks 相关的活动，例如 DCL 和 TCL 执行的内容，将不会覆盖。

Creating and Managing Your Data Structures with DDL

DDL 是 SQL 的一个子集，是一种标准化语言，用于创建和修改 database 中 objects 的 structure。它包含用于定义 tables、indexes、sequences、aliases 等的 commands 和 syntax。

最常见的 DDL commands 如下：

CREATE

创建新的 database objects，例如 tables、views、indexes 或 constraints。它指定 object 的名称及其 structure，包括 columns、data types 和任何 additional properties。

DROP

移除或删除已有 database objects。它会永久删除指定 object 及所有相关 data。

ALTER

修改已有 database object 的 structure。你可以使用它添加、修改或删除 table 的 columns、constraints 或其他 properties。它提供适配不断变化 requirements 的 database schema flexibility。

RENAME

重命名已有 database object，例如 table 或 column。它提供一种在不改变 object structure 或 data 的情况下修改 object name 的方式。

TRUNCATE

快速移除 table 中的所有 data，同时保留 table structure。它比使用 DELETE command 删除所有 rows 更快，因为它会释放 data pages，而不是记录每一行 deletion。

CONSTRAINT

定义 table columns 上的 constraints，通过指定 data 必须满足的 rules 或 conditions，确保 data 的 integrity 和 validity。

INDEX

在 table 的一个或多个 columns 上创建 index。通常，index 会通过创建 sorted structure 来提升 data retrieval operations 的 performance，使 data searching 和 sorting 更快。

在进入 hands-on use case 前，有一些 topics 需要详细讨论，也有一个 additional topic 需要介绍。事实是，大多数 DDL commands 某种意义上都是 self-explanatory 的，只要稍后在代码中看到它们，就很容易理解。不过，CONSTRAINT command 的讨论应稍微更详细一些，以介绍它的 particularities。

如前所述，constraints 是 data 必须满足的 rules 或 conditions，用于保证其 integrity。通常，这些 constraints 应用于 column 或 table。最常见的 constraints 如下：

Primary key

Primary-key constraint 确保一个 column 或 columns 组合可以唯一标识 table 中的每一行，防止 duplicate 和 null values。它对 data integrity 至关重要，并经常被 related tables 中的 foreign-key constraints 引用。

Foreign key

Foreign-key constraint 指定两个 tables 之间的 relationship。它确保一个 table 中某个 column 或 columns 组合的 values，与另一个 table 中的 primary-key values 匹配，帮助维护 referential integrity，并在 related tables 之间 enforce data consistency。

Unique

Unique constraint 确保一个 column 或 columns 组合中的 values 是唯一的，不允许 duplicates。不同于 primary key，unique constraint 可以允许 null values，但如果某个 column 有 unique constraint，则只允许一个 null value。

Check

Check constraint 对 column 中允许的 values 施加 condition。这类 constraints 通常用于 enforce business rules、domain-specific requirements，或 data 上的任何其他 custom conditions。

Not null

Not-null constraint 保证 column 不包含 null values，因此带有该 constraint 的特定 column，在每次插入或更新 row 时都必须有 value。这有助于 enforce data completeness，并避免 unexpected null values。

最后，还有一个话题需要讨论：data types，即对 column 或 variable 中可以存储的数据进行分类的类型。这些 fields 可能因 database engine 而异。这里我们保持简单，并使用 MySQL data types 作为参考：

Integer

不带 fractional part 的 whole number。最常见的是 INT、SMALLINT、BIGINT、TINYINT。可能的 values 示例：1、156、2012412、2。

Decimal

带有 fractional part 的 number。最常见的一些类型包括 DECIMAL、NUMERIC、FLOAT、DOUBLE。可能的 values 示例：3.14、94.5482、5161.17620。

Boolean

Binary value。传统上写作 BOOLEAN、BOOL、BIT、TINYINT。用于存储 true/false 或 0/1 values。

Date

大体上 self-explanatory，但 format 可能不同。声明为 DATE，常用标准格式是 2023-07-06。

Time

你也可以决定 time data type 的 format。在 database 中写作 TIME，常见格式是 18:34:59。

Timestamp

Date 和 time 组合在一起。通常使用 TIMESTAMP 或 DATETIME。示例：2017-12-31 18:34:59。

Text

最通用的 data type。但它可以是 alphabetical letters，也可以是 letters、numbers 或任何其他 characters 的混合。通常声明为 CHAR、VARCHAR、NVARCHAR、TEXT。注意，选择正确的 text data type 很重要，因为每种都有指定 maximum length。Text 示例：“hello world”、“porto1987”、“Hélder”、“13,487*5487+588”。

NOTE

我们将使用 MySQL，因为它被广泛采用。你可以通过 MySQL website 下载 MySQL Workbench。

现在你对 DDL commands 和最常见 database data types 有了更清楚的理解，让我们创建一个 database 来管理 O’Reilly books。这与第 2 章中的示例一致，当时我们介绍了一个 database，用于让 O’Reilly 跟踪 books；但这一次，让我们从 physical model creation 开始。

需要说明的是，对 data engineers 来说，熟练掌握所有类型 SQL commands 非常关键，因为他们同时负责 database design（DDL）和 data manipulation（DML）。Analysts 主要专注于 DML SQL commands，通常局限于用于 data analysis 的 SELECT queries。另一方面，analytics engineers 通常会结合使用 DML 和一些 DDL SQL commands，尽管他们经常通过 dbt 等工具抽象 DDL operations。

首先，创建 database 本身。在你的 MySQL client 中执行 Example 3-1 的命令。

Example 3-1：Create the database

-- Create the OReillyBooks database statement
CREATE DATABASE OReillyBooks;

现在 database 创建完成，执行 Example 3-2 中的代码。

Example 3-2：Create the database, part 2

-- Use the database
USE OReillyBooks;

-- Create the tables

-- Table: Authors
CREATE TABLE authors (
  author_id INT PRIMARY KEY,
  author_name VARCHAR(100)
);

-- Table: Books
CREATE TABLE books (
  book_id INT PRIMARY KEY,
  book_title VARCHAR(100),
  author_id INT,
  rating DECIMAL(10,2),
  FOREIGN KEY (author_id) REFERENCES Authors(author_id)
);

-- Table: Category
CREATE TABLE category (
  category_id INT PRIMARY KEY,
  category_name VARCHAR(50)
);

-- Table: bookCategory
CREATE TABLE book_category (
  book_id INT,
  category_id INT,
  FOREIGN KEY (book_id) REFERENCES books(book_id),
  FOREIGN KEY (category_id) REFERENCES category(category_id)
);

总结来说，这两个示例创建了一个名为 OReillyBooks 的 database，并定义了四个 tables：authors、books、category 和 book_category，其中 book_category 表示 books 和 categories 之间的 many-to-many relationship。每个 table 都有自己的 columns 和 constraints，例如 primary keys 和 foreign keys。

最后，为了测试其他 DDL commands，假设出现了一个新 requirement，现在还需要存储 publication_year，表示某本特定 book 出版的年份。对应 syntax 如 Example 3-3 所示。

Example 3-3：ALTER TABLE syntax

-- Add a new column
ALTER TABLE table_name
ADD column_name datatype [column_constraint];

-- Modify a datatype of an existing column
ALTER TABLE table_name
ALTER COLUMN column_name [new_datatype];

-- Rename a column
ALTER TABLE table_name
RENAME COLUMN old_column_name TO new_column_name;

-- Add a new constraint to a column
ALTER TABLE table_name
ADD CONSTRAINT constraint_name constraint_type (column_name);

-- Modify an existing constraint
ALTER TABLE table_name
ALTER CONSTRAINT constraint_name [new_constraint];

-- Remove an existing column
ALTER TABLE table_name
DROP COLUMN column_name;

根据 Example 3-3 中显示的 syntax，符合我们需求的 modification 是添加新 column。现在通过执行 Example 3-4 中的代码片段，添加 publication_year。

Example 3-4：Add the publication year

-- Add publication_year to the books table
ALTER TABLE books
ADD publication_year INT;

Manipulating Data with DML

DML 是 database management 中的重要组成部分。该 language 使 database system 中的数据 selection、insertion、deletion 和 updating 成为可能。它的主要目的是检索和操作 relational database 中的数据，并包含几个关键 commands。

Inserting Data with INSERT

INSERT command 支持向 table 中添加 new data。借助该 command，users 可以将一个或多个 records 无缝插入 database 中的特定 table。使用 INSERT 可以通过加入 additional entries 来扩展 table 内容。该 command 对向初始为空的 table 添加 records 非常重要，同时也允许持续增强 database 中的 existing data。Example 3-5 展示了该 command 的标准 syntax。

Example 3-5：Syntax of an INSERT statement

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

INSERT INTO statement 指定 data 将被插入的 table，其中 table_name 表示 table 本身的名称。(column1, column2, ...) 这个组件是 optional 的，它允许指定 data 将被插入哪些 columns。如果省略 columns，则假设会为 table 中的所有 columns 提供 values。VALUES keyword 表示要插入到指定 columns 中的 values list 的开始。在 VALUES clause 中，(value1, value2, ...) 是要插入到各自 columns 中的实际 values。必须确保提供的 values 数量与指定 columns 的数量匹配。只有这样，才能确保 insertion process 中 values 被正确映射到对应 columns。大多数 database engines 在不满足这一点时会报错。

现在，让我们扩展从 “Manipulating Data with DML” 开始的 use case，并向之前创建的 tables 插入 data。为此，执行 Example 3-6 中的命令。

Example 3-6：Create dummy data

-- Inserting data into the authors table
INSERT INTO authors (author_id, author_name) VALUES
(1, 'Stephanie Mitchell'),
(2, 'Paul Turner'),
(3, 'Julia Martinez'),
(4, 'Rui Machado'),
(5, 'Thomas Brown');

-- Inserting data into the books table
INSERT INTO books (book_id, book_title,
		author_id, publication_year,
		rating)
VALUES
(1, 'Python Crash Course', 1, 2012, 4.5),
(2, 'Learning React', 2, 2014, 3.7),
(3, 'Hands-On Machine Learning with Scikit-Learn, Keras, and TensorFlow',
  3, 2017, 4.9),
(4, 'JavaScript: The Good Parts', 4, 2015, 2.8),
(5, 'Data Science for Business', 5, 2019, 4.2);

-- Inserting data into the category table
INSERT INTO category (category_id, category_name) VALUES
(1, 'Programming'),
(2, 'Machine Learning'),
(3, 'Data Science'),
(4, 'Software Engineering'),
(5, 'Algorithms'),
(6, 'Computer Science');

-- Inserting data into the book_category table
INSERT INTO book_category (book_id, category_id) VALUES
(1, 1),
(2, 1),
(3, 2),
(4, 1),
(5, 3);

这段代码创建了多个 INSERT statements，每个都针对一个特定 table。我们先向 authors table 插入 data。每一行表示一个 author，其中 author_id 和 author_name columns 分别表示 author 的 unique identifier 和 name。

然后，我们向 books table 插入 data。每一行表示一本 book，其中 book_id、book_title 和 author_id columns 分别表示该 book 的 unique identifier、title 和 author identifier。author_id column 连接到 authors table 中的 author_id column，以建立 books 和 authors 之间的 relationship。注意，由于 referential integrity，我们不能插入一本引用不存在 author 的 book。

我们还创建了一个 category table，用于根据 content type 正确分类 book。每一行表示一个 category，其中 category_id 和 category_name columns 分别表示 category 的 unique identifier 和 name。

最后，intermediate table book_category 存储 books 与其对应 categories 之间的 relationship。每一行表示这种 relationship 的一次 occurrence，其中 book_id 和 category_id columns 分别表示 book 和 category identifiers。这些 columns 建立了 books 和 categories 之间的 many-to-many relationship。

让我们查看已插入的数据。逐行执行 Example 3-7 中的代码。下一节会详细介绍 SELECT statement，但现在只需要查看每个 table 中的数据即可。

Example 3-7：查询 authors、book_category、books 和 category tables 的 SELECT statement

select * from authors;
select * from book_category;
select * from books;
select * from category;

Selecting Data with SELECT

SELECT 是 SQL 中最基础的 DML commands 之一。该 command 允许你从 database 中 extract specific data。当该 statement 被执行时，它会检索 desired information，并将其组织到一个 structured result table 中，通常称为 result set。该 result set 包含满足 specified criteria 的 data，使 users 能轻松访问和分析所选 information。Example 3-8 中可以分析该 command 最简单的 syntax。

NOTE

如果你已经熟练掌握 SQL 和 SELECT commands，并希望学习更 advanced SQL statements，建议直接参考 “Window Functions”。如果你已经想进入 dbt 世界，可以在第 4 章找到相关内容。

Example 3-8：Syntax of a SELECT statement

SELECT column1, column2, ...
FROM table_name;

该结构中的 SELECT 部分表示要从 table 中检索的 specific columns 或 expressions。FROM 组件指定 data 将从哪个 table 中检索。关于该 command，我们还有很多内容要展开，包括 data filtering 及相关 operators、data grouping 和 joins。接下来的 sections 将逐一讨论每个属性。

Filtering data with WHERE

Optional WHERE clause 允许 users 定义 retrieved data 必须满足的 conditions，从而基于指定 criteria 有效过滤 rows。它是 SQL queries 的基础组成部分，使你能够从 tables 中 filter 并 retrieve specific subsets of data。Example 3-9 展示了 WHERE statement 的 syntax。

Example 3-9：带 WHERE clause 的 SELECT statement syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition ;

为了正确理解如何在 SQL 中写 conditions 并恰当 filter data，我们必须先熟悉 SQL operators，包括 single 或 multiple conditions 下使用的 operators。

SQL operators

SQL operators 经常用于 WHERE clause 中，以建立 filter data 的 conditions。这些 operators 允许你比较 SQL 中满足 defined conditions 的 values 和 expressions。表 3-2 总结了最常见的 operators。

表 3-2：SQL operators

Operator	Operator type	Meaning
=	Comparison	Equal to
<> or !=	Comparison	Not equal to
<	Comparison	Less than
>	Comparison	Greater than
<=	Comparison	Less than or equal to
>=	Comparison	Greater than or equal to
LIKE '%expression%'	Comparison	Contains "expression"
IN ("exp1", "exp2")	Comparison	Contains any of "exp1" or "exp2"
BETWEEN	Logical	Selects values within a given range
AND	Logical	Combines two or more conditions and returns true only if all the conditions are true
OR	Logical	Combines two or more conditions and returns true if at least one of the conditions is true
NOT	Logical	Negates a condition, returning true if the condition is false, and vice versa
UNION	Set	Combines the result of two SELECT statements, removing the duplicated rows
UNION ALL	Set	Combines all records from two SELECT statements, but duplicated rows are not eliminated

为了更好理解它们的应用，让我们探索 comparison operators 和 logical operators 的例子。为了简化，由于我们尚未深入 SQL 的其他元素，例如 joins，这里使用 books table 作为 use cases 的 source。

作为 conditional 和 logical operators 的初始示例，我们尝试查找 2015 年之前出版的 books。然后，只查找 2017 年出版的 books。最后，查找 title 中包含 “Python” 的 books。Example 3-10 提供了三个代码片段，帮助解决这个 challenge。

Example 3-10：使用 conditional operators 选择 data

-- Books published earlier than 2015
SELECT
  book_title,
  publication_year
FROM books
WHERE publication_year < 2015;

-- Books published in 2017
SELECT
  book_title,
  publication_year
FROM books
WHERE publication_year = 2017;

-- Books with "Python" in the title
SELECT
  book_title,
  publication_year
FROM books
WHERE book_title LIKE '%Python%';

Example 3-10 展示了三个使用 conditional operators 的例子。你可以自由修改代码，并测试前面介绍的其他 operators。

最后，为了熟悉 logical operator，让我们查找 2012 年出版或 2015 年之后出版的 books。Example 3-11 会帮助你完成这一点。

Example 3-11：使用 logical operator 选择 data

-- Books published in 2012 or after 2015
SELECT
  book_title,
  publication_year
FROM books
WHERE publication_year = 2012 OR publication_year > 2015;

还需要注意的是，这些 operators 并不只适用于 WHERE clause。它们也可以与其他 filtering techniques 一起使用，例如下一节将介绍的 HAVING clause。

Aggregating data with GROUP BY

GROUP BY clause 是 SQL 中的 optional feature，用于按照一个或多个 columns 对 result set 进行分组。GROUP BY 经常与 aggregate functions 一起使用，用于计算在指定 column 或 columns 中共享 common value 的 row subsets。换句话说，使用 GROUP BY clause 时，result set 会被划分为多个 groups，每个 group 表示给定 aggregated column 或 columns 中 values 的唯一组合。正如前面所述，GROUP BY 通常与 aggregate functions 一起用于这些 groups，从而提供有价值的数据洞察。一些最常见的 aggregate functions 如表 3-3 所示。

表 3-3：Aggregate functions

Aggregate function	Meaning
COUNT()	Calculates the number of rows or non-null values in a column.
SUM()	Calculates the sum of numeric values in a column.
AVG()	Calculates the average (mean) value of a column.
MAX()	Retrieves the maximum value from a column.
MIN()	Retrieves the minimum value from a column.
DISTINCT	Although not an aggregate function in the rigorous sense, the DISTINCT keyword is often used with an aggregate function inside the SELECT statement to calculate distinct values.

GROUP BY 通常用于 trend analysis 和 summary reports，例如 monthly sales reports 和 quarterly user accesses 等。GROUP BY clause 的通用 syntax 如 Example 3-12 所示。

Example 3-12：带 GROUP BY clause 的 SELECT statement syntax

SELECT column1, column2, ...
FROM table_name
GROUP BY column1, column2, ...

现在把这些 functions 应用到一个直接的 use case 中。使用 book_category table，分析每个 category 的 average number of books。Example 3-13 可以帮助你完成这个 challenge。

Example 3-13：选择并聚合 data

SELECT
  category_id,
  COUNT(book_id) AS book_count
FROM bookCategory
GROUP BY category_id;

这里使用了 COUNT() aggregate function，但根据 desired use case，也可以使用其他 aggregate functions。最后，这是一个简单示例，因为我们只看到了 category_id；如果能看到 category name 会更好，但该 field 只在 category table 中可见。要包含它，需要知道如何使用 joins。我们会在 “Joining data with INNER, LEFT, RIGHT, FULL, and CROSS JOIN” 中进一步讨论。

最后，我们来到 HAVING filter。它是与 GROUP BY 紧密相关的 optional clause，用于对 grouped data 应用 conditions。与 WHERE clause 相比，HAVING 在 aggregation 之后过滤 rows，而 WHERE clause 则在 grouping operation 之前执行过滤。不过，HAVING 使用的 operators 与 WHERE statement 中相同，例如 “equal” 和 “greater than” 等。

HAVING filter 的 SQL syntax 如 Example 3-14 所示。

Example 3-14：带 GROUP BY clause 和 HAVING filter 的 SELECT statement syntax

SELECT column1, column2, ...
FROM table_name
GROUP BY column1, column2, ...
HAVING condition

让我们看看 HAVING filter 的实际效果。参考 Example 3-13，现在我们只想要至少出版了两本书的 categories。Example 3-15 可以帮助你完成。

Example 3-15：选择并聚合 data，应用 HAVING filter

SELECT
  category_id,
  COUNT(book_id) AS book_count
FROM bookCategory
GROUP BY category_id
HAVING COUNT(book_id) >= 2;

通过利用 GROUP BY clause 和 HAVING filter，你可以有效地组织和汇总 data，并对 aggregated datasets 执行 calculations，从而发现 data 中的 patterns、trends 和 relationships；促进 data analysis；并支持 decision-making processes。

Sorting data with ORDER BY

ORDER BY clause 是 SQL 中的 sorting statement，通常用于按照特定 sequence 组织 query results，使 data 更容易分析和解释。它根据一个或多个 columns 对 query 的 result set 排序，并允许你为每个 column 指定 sorting order，即 ascending（默认）或 descending order。

ORDER BY clause 的基本 syntax 如 Example 3-16 所示。

Example 3-16：带 ORDER BY clause 的 SELECT statement syntax

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...

在之前的 use cases 中，一个突出示例是按 year 查看 books published。仅看 table 时，很难判断哪些是 newest 和 oldest books。ORDER BY clause 大大简化了这个分析。为了测试该 clause，执行 Example 3-17 中的代码片段，并比较带和不带 ORDER BY 的结果。需要说明的是，如果没有明确声明 ASC/DESC，SQL 默认使用 ASC。

Example 3-17：带 ORDER BY clause 的 SELECT statement

SELECT
  book_title,
  publication_year
FROM books
ORDER BY publication_year DESC;

总之，ORDER BY clause 允许你按照最适合 data exploration 和 analysis 的 desired sequence 排列 result set，从而简化 meaningful data 的捕获。

Joining data with INNER, LEFT, RIGHT, FULL, and CROSS JOIN

Joins 是 SQL 中用于将多个 tables 的 data 组合起来的机制。理解并使用 joins，可以大幅提升你从 complex datasets 中提取 valuable insights，并做出更 informed decisions 的能力。本节将介绍 SQL 中可用的 join types、它们的 syntax 和 usage。

SQL 有多种 joins。每种 join 都允许你基于指定 conditions 将多个 tables 的 data 组合起来。在看到 joins 实际效果之前，我们先通过添加一位新 author 来扩充 dataset。这个 author 不会有任何 books。执行 Example 3-18 中的 statement。

Example 3-18：插入一位没有 books 的 author

INSERT INTO authors (author_id, author_name) VALUES
(6, 'John Doe')

我们创建一位没有任何 books 的 author，是为了探索后面要介绍的几种 joins。以下是最常见的 SQL joins 类型。

INNER JOIN

INNER JOIN 只返回两个 tables 中基于指定 join condition 匹配的 rows。如果用 Venn diagram 来想象，circle A 和 circle B 分别代表一个 dataset，那么 INNER JOIN 只显示包含两个 tables matching values 的重叠区域。图 3-6 可以更好地可视化这个 Venn diagram。

图 3-6：展示 INNER JOIN 的 Venn diagram

INNER JOIN 的 code syntax 如 Example 3-19 所示。

Example 3-19：INNER JOIN syntax

SELECT
  columns
FROM Table_A
INNER JOIN Table_B ON join_condition;

为了查看 INNER JOIN 的实际效果，让我们只获取有 books 的 authors。Example 3-20 展示所需代码。

Example 3-20：仅获取有 books 的 authors

SELECT
  authors.author_id,
  authors.author_name,
  books.book_title
FROM authors
INNER JOIN books ON Authors.author_id = Books.author_id

图 3-7 展示 query output。

图 3-7：INNER JOIN query output，仅显示有 books 的 authors

通过分析结果，我们可以快速识别缺失的 author John Doe。如你所记得，我们创建他时没有任何 books，因此使用 INNER JOIN 时，他按预期被省略。

LEFT JOIN（或 LEFT OUTER JOIN）

LEFT JOIN 返回 left table 中的所有 rows，以及 right table 中匹配的 rows。如果没有 match，则 right table columns 使用 null values。类似上一个练习，一个左侧 circle A 和右侧 circle B 的 Venn diagram 分别表示一个 dataset。在 LEFT JOIN 中，左侧 circle 包含 left table 中的所有 rows，重叠区域表示基于 join condition 匹配的 rows。右侧 circle 包含 right table 中未匹配 rows，这些 rows 在 result set 中以 null values 表示。图 3-8 可以更好地可视化这个 Venn diagram。

图 3-8：展示 LEFT JOIN 的 Venn diagram

LEFT JOIN 的 code syntax 如 Example 3-21 所示。

Example 3-21：LEFT JOIN syntax

SELECT
  columns
FROM Table_A
LEFT JOIN Table_B ON join_condition;

为了测试 LEFT JOIN，我们继续使用 authors 与 books 的关系，但现在我们想列出所有 authors 及其对应 books，并且也必须包含没有任何 book 的 authors。执行 Example 3-22 中的代码片段。

Example 3-22：获取 authors 及其 books

SELECT
  authors.author_id,
  authors.author_name,
  books.book_title
FROM authors
LEFT JOIN books ON authors.author_id = books.author_id

Query output 如图 3-9 所示。

图 3-9：LEFT JOIN query output，显示 authors 及其对应 books

与 INNER JOIN 相比，LEFT JOIN 使我们能够看到 author John Doe。这是因为在 LEFT JOIN 中，left table，也就是 authors，会完整显示，而 right table，也就是 books，只显示与 authors 相交的结果。

RIGHT JOIN（或 RIGHT OUTER JOIN）

RIGHT JOIN 返回 right table 中的所有 rows，以及 left table 中匹配的 rows。如果没有 match，则 left table columns 使用 null values。继续想象 Venn diagram，circle A（left）和 circle B（right）分别表示一个 dataset。在 RIGHT JOIN 中，右侧 circle 包含 right table 中所有 rows，重叠区域表示基于 join condition 匹配的 rows。左侧 circle 包含 left table 中未匹配 rows，这些 rows 在 result set 中用 null values 表示。图 3-10 可以更好地可视化这个 Venn diagram。

图 3-10：展示 RIGHT JOIN 的 Venn diagram

RIGHT JOIN 的 code syntax 如 Example 3-23 所示。

Example 3-23：RIGHT JOIN syntax

SELECT
  columns
FROM Table_A
RIGHT JOIN Table_B ON join_condition;

让我们先 contextualize training，看看 RIGHT JOIN 的实际效果。在这个案例中，我们想看到所有 books 及其 authors，因此执行 Example 3-24 中的代码。

Example 3-24：获取 books 及其 authors

SELECT
  authors.author_id,
  authors.author_name,
  books.book_title
FROM authors
RIGHT JOIN books ON authors.author_id = books.author_id

Query output 如图 3-11 所示。

图 3-11：RIGHT JOIN query output，显示 books 及其对应 authors

通过分析 query output，可以看到所有 books 及其对应 authors。由于我们没有任何没有 author 的 books，因此看不到 books 和 authors 之间存在 book 但没有 author 的交集。

FULL JOIN（或 FULL OUTER JOIN）

在这种 join 中，会返回两个 tables 中的所有 rows。它结合了 LEFT JOIN 和 RIGHT JOIN 的结果。如果没有 match，则 nonmatching table 的 columns 使用 null values。在一个 Venn diagram 中，circle A（left）和 circle B（right）分别表示一个 dataset，FULL JOIN 的图会显示基于 join condition 匹配 rows 的重叠区域，而每个 circle 的非重叠部分包含各自 table 中 non-matching rows。最终生成的 result set 包含两个 tables 的所有 rows，并对 nonmatching rows 使用 null values。图 3-12 可以更好地可视化它。

图 3-12：展示 FULL JOIN 的 Venn diagram

FULL JOIN 的 code syntax 如 Example 3-25 所示。

Example 3-25：FULL JOIN syntax

SELECT
  columns
FROM Table_A
FULL JOIN Table_B ON join_condition;

NOTE

MySQL 不原生支持 FULL JOIN。我们必须通过对一个 LEFT JOIN statement 和一个 RIGHT JOIN statement 做 UNION 来实现它。这实际上会结合两个方向的数据，复制 FULL JOIN 的行为。

CROSS JOIN

CROSS JOIN，或 Cartesian join，返回两个 tables 的 Cartesian product，将第一个 table 中的每一行与第二个 table 中的每一行组合。它不需要 join condition。在 CROSS JOIN 的 Venn diagram 中，没有重叠 circles，因为它会组合 circle A（left）和 circle B（right）中的每一行。Result set 包含两个 tables 中所有 rows 的所有可能组合，如图 3-13 所示。

图 3-13：展示 CROSS JOIN 的 Venn diagram

CROSS JOIN 的 code syntax 如 Example 3-26 所示。

Example 3-26：CROSS JOIN syntax

SELECT
  columns
FROM Table_A
CROSS JOIN Table_B;

Example 3-27 展示了 authors table 和 books table 的 CROSS JOIN。

Example 3-27：authors 和 books tables 的 CROSS JOIN

SELECT
*
FROM authors
CROSS JOIN books;

总结来说，SQL joins 提供了基于 conditions 将多个 tables 中 data 组合起来的 flexibility。理解其 usage 和 syntax，可以帮助你提取 desired information，并为 tables 中的相关 data 建立 relationships。通过 Venn diagram 可视化 joins，有助于解释 tables 中 data 如何基于 join conditions 重叠和组合，并突出 result set 中 matched 和 unmatched rows，清晰表示 join operation 过程中 tables 之间的 relationship。

Updating Data with UPDATE

UPDATE command 允许我们修改 database 中 existing table 内的 records。通过执行该 command，users 可以有效更新和改变 specific records 中存储的 data。UPDATE 支持对 table 中一个或多个 records 进行 changes，确保 data 准确反映最新 information。通过使用该 command，users 可以无缝修改 table 的内容，根据需要进行 data refinement、corrections 或 updates。Example 3-28 展示了该 command 的 syntax。

Example 3-28：UPDATE statement syntax

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

UPDATE keyword 用于指定将被更新的 table，而 table_name 表示要修改的 table 名称。SET keyword 表示将更新 columns，并为它们分配 new values。在 SET clause 中，column1 = value1, column2 = value2… 指定要更新的 columns 及其对应 new values。最后，optional WHERE clause 允许指定 rows 必须满足的 conditions 才会被更新。它基于 specified conditions 过滤 rows。

为了实际测试 UPDATE statement，假设某个 book title 中有 typo：我们不想要 “Learning React”，而想要 “Learning React Fundamentals”。查看 books table，可以看到 Learning React 的 book_id = 2。你可以参考 Example 3-29 中的代码来完成这个 update。

Example 3-29：Update the books table

UPDATE books
SET book_title = 'Learning React Fundamentals'
WHERE book_id = 2;

就这样。如果再次查看 books table data，就可以看到新的名称（图 3-14）。

图 3-14：Updating the books table

Deleting Data with DELETE

DELETE command 提供了根据指定 criteria 选择性删除某些 records，或删除 table 中所有 records 的能力。DELETE 在 data maintenance 中发挥重要作用，使 users 可以通过移除 unnecessary 或 outdated records，有效管理和清理 table 内容。该 command 确保 data integrity，并通过消除 redundant 或 irrelevant information 帮助优化 database。Example 3-30 展示了该 command 的 syntax。

Example 3-30：DELETE statement syntax

DELETE FROM table_name
WHERE condition;

DELETE FROM 部分表示 data 将从哪个 specific table 中删除，而 table_name 表示 table 的名称。Optional WHERE clause 发挥重要功能，允许 users 定义 rows 必须满足哪些 conditions 才会被删除。借助该 clause，可以基于 specific criteria 过滤 rows。如果不使用 WHERE clause，table 中的所有 rows 都会被删除。最后，condition 指 rows 需要满足的具体 conditions，满足后才 eligible for deletion。

为了实际应用该 command，假设我们不会出版任何 Computer Science category 的 books。查看 category_id，可以看到它是 6。现在执行 Example 3-31，看看会发生什么。

Example 3-31：从 category table 中删除一个 category

DELETE FROM Category
WHERE category_id = 6

如果一切顺利，你应该能够 select category table，并看到 Computer Science category 已经不存在，如图 3-15 所示。

图 3-15：从 category table 删除的 category

最后，也可以使用另一种 data management technique，名为 soft delete，来“删除” data。这种 technique 并不永久擦除 record，而是在 database 中设置一个 flag 或 attribute，表示该 record 应被视为 deleted。这会保留 historical data，在需要时确保 easy recovery，并通过维护 changes 的 audit trail 支持 compliance。

Storing Queries as Views

View 是 database 中由 query 定义的 virtual table。它类似 regular table，由 named columns 和 data rows 组成。不过，与 table 不同，view 不会在 database 中物理存储 data values。相反，当 view 被访问时，它会从其 query 中引用的 tables 动态检索 data。

Example 3-32 展示了创建 view 的通用 syntax。

Example 3-32：VIEW syntax

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

使用我们的 OReillyBooks database，Example 3-33 创建了一个 view，用于分析每位 author 创建了多少 books。

Example 3-33：books database 的 view

CREATE VIEW author_book_count AS
SELECT authors.author_id,
       authors.author_name,
       COUNT(books.book_id) AS book_count
FROM authors
LEFT JOIN books ON authors.author_id = books.author_id
GROUP BY authors.author_id, authors.author_name;

然后，可以 query author_book_count view，分析每位 author 创建了多少 books；见 Example 3-34。

Example 3-34：Query books database 中的 view

SELECT * FROM author_book_count;

View 的主要目的之一，是充当底层 tables 的 filter。定义 view 的 query 可以涉及一个或多个 tables，或同一或不同 databases 中的其他 views。事实上，views 可以被创建用于整合来自多个 heterogeneous sources 的 data，使你能够组合组织中不同 servers 上的 similar data，而每个 server 存储特定 region 的 data。

Views 经常用于简化和定制每个 user 对 database 的感知。通过定义 views，你可以向不同 users 展示 focused 和 tailored 的 data view，隐藏不必要 details，并提供更 intuitive interface。此外，view 可以作为 security mechanism，使 users 能通过 view 访问 data，而不直接授予他们对底层 base tables 的访问权限。这提供了额外 control layer，并确保 users 只能看到自己被授权查看的 data。

在 Example 3-35 中，我们基于 books table 创建 renamed_books view。我们在 SELECT statement 中使用 column aliases，将 columns 重命名为对特定 user 更熟悉的名称，而不改变 table structure。我们甚至可以基于同一 data 创建不同 views，并根据 audience 使用不同 naming conventions。

Example 3-35：用于重命名 columns 的 view

CREATE VIEW renamed_books AS
SELECT
    id AS BookID,
    title AS BookTitle,
    rating AS BookRating
FROM books;

此外，当 table 的 schema 发生变化时，views 也很有帮助。你不需要修改 existing queries 和 applications，而可以创建一个 view 来模拟旧 table structure，为访问 data 提供 backward-compatible interface。这样，就可以在改变底层 data model 的同时保持 compatibility。

虽然 views 提供很多优势，但也有某些 limitations 和 potential dangers。一个 limitation 是对底层 table structure 的依赖，前面我们将其强调为好处，但它也是一个负担。如果 base table structure 发生变化，view definition 必须相应更新，这可能增加 maintenance overhead。此外，views 可能影响 query performance，尤其是涉及多个 tables 或 extensive calculations 的 complex views。

为了避免不必要 overhead，必须持续优化 view queries，并学会有效使用 execution plans 来阻止 inefficiencies。另一个危险是可能创建过于 complex 或 inefficient views，从而导致 poor performance，并使长期维护或修改 views 变得困难。此外，views 可以通过限制对 specific columns 或 rows 的访问，提供一种 data security 的幻觉。然而，它们并不提供 foolproof security，如果 unauthorized users 获得 view 访问权限，仍然可能访问 underlying data。为了确保 data protection，必须实施合适的 database-level security measures。最后，如果没有适当维护，views 可能导致潜在 data integrity issues，因为它们可能不像 physical tables 那样 enforce constraints 或 referential integrity。总体而言，views 提供有价值功能，但我们应理解并最小化它们的 limitations 和 potential risks，以确保它们被有效且安全地使用。

在 Example 3-36 中，我们展示了由于 joins 数量较多，并且包含来自不同 tables 的多个 columns，view 的 complexity 会增加，使其一眼难以阅读和理解。一个有趣的修复方式是使用 CTEs，下一节会介绍。

Example 3-36：Complex views

CREATE VIEW complex_books_view AS
SELECT
     b.book_id,
     b.book_title,
     b.author_id,
     b.rating,
     b.publication_year,
     a.author_id,
     a.author_name,
     c.category_id,
     c.category_name
FROM books b
INNER JOIN authors a ON a.author_id = b.author_id
LEFT JOIN bookCategory bc ON bc.book_id = b.book_id
LEFT JOIN category c ON c.category_id = bc.category_id;

Common Table Expressions

许多 data analysts 和 developers 都面对过理解 complex SQL queries 的挑战。尤其是在 dealing with complicated business logic 和 multiple upstream dependencies 时，很容易不知道 specific query components 的目的和 dependencies。再加上 unexpected query results 带来的困扰，analyst 可能不确定 query 的哪一部分造成了 discrepancy。Common table expressions（CTEs）为这种场景提供了有价值的解决方案。

CTEs 是一种强大工具，用于简化 complex queries 并提升 query maintainability。作为 temporary result set，CTEs 通过将 complex queries 拆分为 manageable blocks，增强 SQL code 的 readability。

Example 3-37 展示了 SQL 中创建 CTE 的通用 syntax。虽然看起来复杂，但它遵循简单 patterns。

Example 3-37：CTE syntax

WITH cte_name (column1, column2, ..., columnN) AS ( 
    -- Query definition goes here                   
)
SELECT column1, column2, ..., columnN               
FROM cte_name                                       
-- Additional query operations go here              

使用 WITH keyword 声明 CTE，并给 expression 一个名称。如果需要，也可以指定 columns，或使用 * character。

在 AS keyword 后定义 CTE query，写出定义该 CTE 的 query。这个 query 可以根据需要简单或复杂，包括 filtering、joining、aggregating 或任何其他 SQL operations。

在后续 query 中使用 CTE，通过名称引用 CTE，就像它是一个实际 table 一样。你可以从 CTE 中 select columns，或对 CTE 的 data 执行 additional operations。

添加更多 query operations，也就是沿着 query pipeline 使用 CTEs。这一步是 optional 的。我们可以包含额外 query operations，例如 filtering、sorting、grouping 或 joining，以进一步操作从 CTE 中检索的 data。

Example 3-38 使用 books table 作为 reference 创建一个 CTE。

Example 3-38：A simple CTE

WITH popular_books AS (
    SELECT title,
           author,
           rating
    FROM books
    WHERE rating >= 4.5
)
SELECT title,
       author
FROM popular_books
ORDER BY rating DESC;

类似 derived tables 和 database views，CTEs 提供多项优势，使 query writing 和 maintenance 更简单。通过将 complex queries 拆分成更小的 reusable blocks，CTEs 提升 code readability，并简化整体 query structure。让我们看看使用 CTE 和仅使用 subquery 的差异。在这个练习中，我们使用一个虚构的 sales table，其中包含所有 book sales，如 Example 3-39 所示。该 table 通过 book_id primary key 与 books table 连接。

Example 3-39：没有 CTE 的 query

SELECT pb.book_id,
       pb.title,
       pb.author,
       s.total_sales
FROM (
    SELECT book_id,
           title,
           author
    FROM books
    WHERE rating >= 4.6
) AS pb
JOIN sales s ON pb.book_id = s.book_id
WHERE s.year = 2022
ORDER BY s.total_sales DESC
LIMIT 5;

这段代码使用 subqueries 而不是 CTEs 来获得 2022 年最畅销的前五本 books。现在，让我们使用 CTEs，看 Example 3-40 中 readability 如何提升。

Example 3-40：带 CTE 的 query

WITH popular_books AS (
    SELECT book_id,
           title,
           author
    FROM books
    WHERE rating >= 4.6
),
best_sellers AS (
    SELECT pb.book_id,
           pb.title,
           pb.author,
           s.total_sales
    FROM popular_books pb
    JOIN sales s ON pb.book_id = s.book_id
    WHERE s.year = 2022
    ORDER BY s.total_sales DESC
    LIMIT 5
)
SELECT *
FROM best_sellers;

我们创建了两层 CTEs。popular_books 是第一个 CTE，它从 books table 中选择 book_id、title 和 author columns，并过滤 rating 为 4.6 或更高的 books。注意，该 CTE 聚焦一个清晰职责：只获取 top-reviewed books。

然后是 best_sellers，第二个 CTE 构建在第一个 CTE 之上。它从 popular_books 中选择 book_id、title、author 和 total_sales columns，并基于 book_id column 与 sales table join。此外，它过滤 2022 年发生的 sales，按 total sales 降序排序，并将 output 限制为 top five best-selling books。同样，该 CTE 聚焦另一个清晰职责：基于 sales 获取前五本 best sellers，但只针对 rating = 4.6 的预选 books。

最后，main query 从 best_sellers 中选择所有 columns，并检索 combined results。我们可以在 main query 上应用 additional aggregations 或 filters。不过，最佳实践是保持代码简单，并只专注于选择 final analysis 所需的 attributes。

CTEs 的一个常见 use case，是在单个 query 中多次引用 derived table。CTEs 通过允许 derived table 被定义一次并多次引用，消除 redundant code。这提升 query clarity，并减少 code duplication 导致 errors 的可能性。为了看到它的实际效果，让我们查看 Example 3-41，继续使用虚构的 sales table。

Example 3-41：带 CTE 和 derived tables 的 query

WITH high_ratings AS (
    SELECT book_id,
           title,
           rating
    FROM books
    WHERE rating >= 4.5
),
high_sales AS (
    SELECT book_id,
           count(book_id) AS nbr_sales
    FROM sales
    GROUP BY book_id
)
SELECT hr.title,
       hr.rating,
       hs.sales
FROM high_ratings hr
JOIN high_sales hs ON hr.book_id = hs.book_id;

可以看到，在这个场景中使用 CTEs，可以通过一次性定义 derived tables（high_ratings 和 high_sales）来消除 redundant code。通过这种策略，我们可以在 main query 或任何后续 CTE 中多次引用这些 tables。

另一个 CTEs 表现突出的场景，是替代创建 permanent database view。有时创建 view 可能没有必要或不可行。在这种情况下，CTE 可以作为 temporary 和 dynamic substitute，通过允许你在单个 query 的 scope 内定义并引用 result set，提供 flexibility 和 simplicity。Example 3-42 展示了在这种场景中使用 CTE，可以避免创建 permanent database view。

Example 3-42：使用 CTE 避免永久创建 view 的 query

WITH filtered_books AS (
    SELECT title,
           author
    FROM books
    WHERE rating > 4.0
)
SELECT *
FROM filtered_books;

当同一个 calculation 必须跨 query components 执行时，CTEs 也很有帮助。与其在多个位置重复 calculation，不如用 CTE 定义一次 calculation，并按需复用。这促进 code reusability，减少 maintenance efforts，并提升 query performance。让我们从 Example 3-43 开始。

Example 3-43：使用 CTE 促进 code reusability 的 query

WITH total_sales AS (
    SELECT customer_id,
           SUM(sales_amount) AS total_amount
    FROM sales
    GROUP BY customer_id
)
SELECT ts.customer_id,
       ts.total_amount,
       avg(total_amount) AS avg_amount
FROM total_sales ts;

可以看到，通过使用 total_sales CTE，total sales 的 calculation 在 CTE 中定义一次，并在 main query 中复用于 calculating average，展示了第一次 aggregation 对另一次 aggregation 的 reusability。

总之，CTEs 允许我们通过将复杂问题拆分为更小、更 manageable 的 pieces 来处理 complex problems。通过使用 CTEs，我们可以以更 modular 和 readable 的方式组织和结构化 queries。它们提供一种拆解 complex problems 的方案，使我们能够定义 intermediate result sets，并在单个 query 中多次引用它们。这消除了 redundant code，并促进 code reusability，减少 maintenance efforts 和 code duplication 导致 errors 的可能性。

Window Functions

Window functions 是一种有用工具，在分析 dataset 的 partitions 或 windows 时，可以提升效率并减少 query complexity。它们为更复杂的 SQL concepts 提供了一种替代方法，例如 derived queries，使 advanced analysis operations 更容易执行。

Window functions 的一个常见 use case，是在给定 window 内对 results 进行 ranking，这支持按 group 排名，或基于 specific criteria 创建 relative rankings。此外，window functions 允许访问同一 window 内另一行的数据，这对于按时间段生成 reports，或比较相邻 rows 之间的数据等任务非常有用。

与此同时，window functions 也支持在给定 window 内进行 aggregation，简化 running 或 cumulative totals 等 calculations。使用 window functions 可以使 queries 更高效、更 streamlined、更 meaningful，使 analysts 和 data scientists 无需使用复杂 subqueries 或 procedural logic，就能对 data partitions 执行 sophisticated analyses。最终，window functions 增强了 SQL 的 analytical capabilities，并为 data analysis 提供了通用工具。

更实际地看，window function 是对与当前 row 相关的一组 table rows 执行的 calculation。它类似 aggregate function，但不会将 rows 分组为单个 output row。相反，每一行保留自己的独立身份。Window functions 可以访问 query result 中不只是当前 row 的更多内容。

Window functions 的 syntax 如 Example 3-44 所示，包含多个 components。首先，我们使用 SELECT statement 指定希望包含在 result set 中的 columns。这些 columns 可以是 table 中可用 columns 的任意组合。接下来，选择要使用的 window function。标准 window functions 包括 SUM()、COUNT()、ROW_NUMBER()、RANK()、LEAD()、LAG() 等等。我们可以使用这些 functions 对特定 column 或 columns 集合执行 calculations 或 apply aggregate operations。

Example 3-44：Window function syntax

SELECT column1,
       column2,
       ...,
       window_function() OVER (PARTITION BY column1,
                column2,
                ... ORDER BY column3, column4, ...)
FROM table_name;

要定义 window function 计算所基于的 window frame，可以使用 OVER clause。在 OVER clause 内，有两个主要 components：PARTITION BY 和 ORDER BY。

PARTITION BY clause 根据一个或多个 columns 将 rows 划分为 partitions。Window function 随后会分别应用于每个 partition。当我们想在 table 内不同 data groups 上执行 calculations 时，这非常有用。

ORDER BY clause 允许你指定一个或多个 columns，以确定每个 partition 内的 order。Window function 会基于这个 order 应用。它帮助定义 window function 将处理的数据的 logical sequence 或 order。在 OVER clause 内组合 PARTITION BY 和 ORDER BY clauses，可以精确控制 window function 如何作用于 data，使我们能够对 table 中特定 window 或 rows subset 执行 calculations 或 apply aggregate functions，而不改变整个 result set。

使用 window functions 的一个实际例子是计算 running total。在给定 query 中，running_count column 会根据 publication year 显示 books 的 sequential count。Window function ROW_NUMBER() OVER (ORDER BY publication_year) 会为每本 book 分配 row number，并按 publication year 排序。这段代码见 Example 3-45，query output 如图 3-16 所示。

Example 3-45：Window function example

SELECT book_id,
       book_title,
       publication_year,
       ROW_NUMBER() OVER (ORDER BY publication_year) AS running_count
FROM books;

图 3-16：Running count

借助 window functions，也可以使用 COUNT() 和 AVG() 等 aggregate functions，它们已在 “Aggregating data with GROUP BY” 中介绍。这些 functions 的使用方式类似 regular aggregations，但它们作用于 specified window。

Window functions 还提供额外功能，例如 ROW_NUMBER()、RANK() 和 DENSE_RANK() 用于 numbering 和 ranking rows，NTILE() 用于确定 percentiles 或 quartiles，以及 LAG() 和 LEAD() 用于访问 previous 或 subsequent rows 的 values。

表 3-4 总结了多种 window functions 类型。

表 3-4：Window functions

Type	Function	Example
Aggregate functions	Aggregate within each window and return a single value for each row	MAX()、MIN()、AVG()、SUM()、COUNT()
Ranking functions	Assign a rank or position to each row within the window based on a specified criterion	ROW_NUMBER()、RANK()、DENSE_RANK()、NTILE()、PERCENT_RANK()、CUME_DIST()
Analytics functions	Compute values based on the data in the window without modifying the number of rows	LEAD()、LAG()、FIRST_VALUE()、LAST_VALUE()

为了深入理解每类 function，我们将以 publication_year column 为基础，并尝试一系列 functions。

在第一个例子中，我们想按 ascending order 对 newest 到 oldest book 排名。让我们看 Example 3-46 中的代码片段。

Example 3-46：Window function—RANK()

SELECT book_id,
       book_title,
       publication_year,
       RANK() OVER (ORDER BY publication_year) AS rank
FROM books;

使用 RANK() function 时，一个重要注意事项是：它会根据指定 criteria 为 window 内每一行分配 unique rank。但如果多行共享相同 value，并被分配相同 rank，则后续 ranks 会被跳过。例如，如果两本 books 拥有相同 publication_year，下一个 rank 会按拥有相同 rank 的 rows 数量增加。如果不想出现 repeated ranks，即不同 rows 共享相同 rank，可能应使用 ROW_NUMBER()。

在 Example 3-47 中，我们想按 publication_year 对 data 进行 bucket。

Example 3-47：Window function—NTILE()

SELECT book_id,
       book_title,
       publication_year,
       NTILE(3) OVER (ORDER BY publication_year) AS running_ntile
FROM books;

当你希望将 rows 均匀分布到指定数量的 groups 中，或者需要将 data 划分出来做进一步 analysis 或 processing 时，通常使用 NTILE()。这有助于 data segmentation、percentile calculations，或创建 equal-sized samples 等任务。

最后，我们想知道 previous published book 的 publication_year。为此，使用 LAG() function，如 Example 3-48 所示。

Example 3-48：Window function—LAG()

SELECT book_id,
       book_title,
       publication_year,
       LAG(publication_year) OVER (ORDER BY publication_year) AS previous_year
FROM books;

SQL 中的 LAG() function 允许你访问 window frame 内 previous row 的 data。它会根据 OVER clause 中指定的 ordering，从 preceding row 中检索某个 specified column 的 value。

SQL for Distributed Data Processing

随着 enterprises 走向 cloud，它们会遇到一个常见挑战。现有 relational databases 是 critical applications 的基础，但无法充分发挥 cloud 的潜力，也难以有效扩展。越来越清楚的是，database 本身正在成为 bottleneck，阻碍 transition 的速度和效率。因此，组织正在寻找一种 solution，将 Oracle、SQL Server、Postgres 和 MySQL 等经过验证的 relational data stores 的 reliability，与 cloud 的 scalability 和 global reach 结合起来。

为了满足这些需求，一些公司转向 NoSQL databases。虽然这些 alternatives 经常满足 scalability requirements，但它们往往不适合作为 transactional databases。这种限制源于其设计，因为它们最初并不是从底层开始为 true consistency 而设计的。虽然某些 NoSQL solutions 最近已经引入进展，以处理特定类型 challenges，但它们仍受各种 caveats 约束，最终无法为 banks 或 hospitals 等 critical workloads 提供必要 isolation levels。

认识到 legacy relational databases 和 NoSQL storage 的不足后，公司开始转向一种 promising solution，即 distributed SQL。这种创新方法将一个 single logical database 部署到多个 physical nodes 上，这些 nodes 可以位于单个 data center，也可以根据需要分布在多个 data centers 中。通过利用 distributed architecture 的力量，distributed SQL 将 elastic scalability 与坚定的 resilience 结合起来。

Distributed SQL 的关键好处之一，是它能够无缝 scale，以满足 modern cloud environments 不断演进的需求。随着 data volumes 增长和 user demands 增加，组织可以轻松向 distributed deployment 添加 additional nodes，使 database 能够 horizontally expand。这种 elastic scaling 确保即使在 heavy workloads 下，performance 仍保持 optimal，并消除 traditional relational databases 经常面对的限制。

与此同时，distributed SQL 提供无与伦比的 resilience。因为 data 分布在多个 nodes 上，所以它天然 fault-tolerant。如果一个 node fail 或不可用，system 可以自动将 queries 转发到剩余 healthy nodes，确保对 critical data 的 uninterrupted access。这种 robust resilience 显著降低 downtime 和 data loss 风险，并提升 database 的整体 reliability。其 distributed nature 也支持 global coverage 和 data availability。组织可以在不同 geographic regions 部署 nodes，将它们 strategic 地放置在更靠近 end users 的位置，并降低 latency。这种 geographically distributed approach 确保 data 可以从全球任何地方快速访问，从而促进 efficient data delivery，并使组织能够服务 global user base。

本书的重点并不在实际 distributed processing engines，也不在它们如何工作；我们只会触及它们暴露给我们用于交互的 interfaces。大多数这类 engines 最终会暴露 API 或 SDK。然而，少数更聚焦 data analytics 的工具会使用 SQL 作为 interface language。实际上，distributed processing 和 SQL 已经成为强大组合，其中 SQL 作为一种方便且熟悉的 interface，用于利用 distributed computing capabilities。

Spark、Hadoop 和 Dask 等 distributed processing frameworks 提供 infrastructure，用于跨多台机器或 clusters 处理 large-scale data。这些 frameworks 会分布 workload，并 parallelize computations，从而支持更快、更高效的 data processing。另一方面，SQL 提供一种 declarative 且 intuitive 的方式来表达 data operations。通过将 SQL 集成为 distributed processing 的 interface，users 可以利用自己的 SQL skills 来发挥 distributed computing frameworks 的力量。这种 approach 支持 seamless scalability、高效 data processing，并能够在 vast datasets 上处理 complex analytics tasks，同时使用熟悉的 SQL syntax。

这种组合使 users 能够以直接且高效的方式执行 advanced data analytics 和 processing tasks。DuckDB、dbt 本身，甚至 FugueSQL，都是这种强大组合的例子。这些 interfaces 作为 distributed computing engines 之上的一层，使 users 能够编写 SQL queries，并利用他们对 SQL syntax 和 semantics 的熟悉度。DuckDB 特别旨在支持 SQL queries 的 efficient 和 scalable execution，同时利用 distributed computing 的力量。它允许 users 使用 SQL 表达 analysis 和 data processing workflows，而底层 distributed processing engine 则处理多组 machines clusters 上的 parallel execution。

然而，尽管存在这些 SQL interfaces，它们仍经常与 Python code 配合使用。即便在 Spark documentation 中，Python code 仍然用于各种 tasks，例如 data transformations、DataFrame loading，以及 SQL query 执行后的 post processing。这种对 Python code 的依赖，源于标准 SQL 缺少 grammatical constructs，无法表达 distributed computing environments 中 users 通常执行的许多 operations。因此，仅靠 SQL 往往不足以表达 comprehensive end-to-end workflows。

让我们通过一个例子深入一点。假设我们需要创建一个 SQL query，理解 O’Reilly authors 自创立以来所有 sold units。这会是一个直接查询，如 Example 3-49 所示。

Example 3-49：A basic SQL query

-- Retrieve top-selling O'Reilly books
SELECT Title,
       UnitsSold
FROM Sales
WHERE Publisher = 'O''Reilly'
ORDER BY UnitsSold DESC
LIMIT 5

此时，SQL query 为我们提供了所需 aggregated results。然而，如果我们想执行 additional data manipulations，或将 results 与 external systems 集成，通常需要求助于 Python 或其他 programming languages。

例如，我们可以将 aggregated results 与存储在单独 dataset 中的 customer demographic data join，以获得更深 insights。这个 operation 通常需要写 Python code 来执行 data merge 和 post-processing steps。此外，如果我们打算将 results visualized，或导出到另一种 format，也同样需要 Python code 来完成这些任务。

一个常见 use case 实际上是将 data 暴露为 API，而 SQL 并不提供这种 capability。Example 3-50 展示了如何结合 SQL 和 Python 实现 end-to-end flow。

Example 3-50：A basic FastAPI

from fastapi import FastAPI
import duckdb

app = FastAPI()

@app.get("/top_books")
def get_top_books():
    # Establish a connection to the DuckDB database
    conn = duckdb.connect()

    # Execute the SQL query
    query = '''
        SELECT Title, UnitsSold
        FROM sales
        WHERE Publisher = "O'Reilly"
        ORDER BY UnitsSold DESC
        LIMIT 5
    '''
    result = conn.execute(query)

    # Convert the query result to a list of dictionaries
    books = []
    for row in result:
        book = {
            "title": row[0],
            "units_sold": row[1]
        }
        books.append(book)

    # Return the result as JSON
    return {"top_books": books}

我们开发了一个 FastAPI application，并设置了一个 GET endpoint，可通过 /top_books route 访问。更简单地说，endpoint 是一个特定 web address（URL），我们可以用它从 application 中检索 information。当有人通过 web browser 或 application 访问这个 URL 时，它会触发我们定义的特定 function，即 get_top_books 的执行。这个 function 包含了当有人从 /top_books endpoint 检索 information 时要执行的 instructions。本质上，它就像一个特定按钮，被按下时会让 application 执行特定 action，例如提供 top-selling books 列表。

在 function 内部，我们使用 duckdb.connect() 建立到 DuckDB database 的 connection。然后，在 connection object 上使用 execute() method 执行 SQL query。该 query 从 sales table 中选择 titles 和 units sold，并按 publisher O’Reilly 过滤。结果按 units sold 降序排序，并限制为前五本 books。

Query result 随后被转换成一个 dictionaries list；每个 dictionary 表示一本 book，包含 title 和 units sold。最后，结果被包装在带有 key top_books 的 dictionary 中，并以 JSON 返回。

通过结合两种语言，我们可以借助友好的 SQL interface 创建和操作 data，并通过优秀的 FastAPI framework 将其作为 API 暴露。下一节中，我们将探索三个知名 Python frameworks，它们通过类似 SQL 的 interface 抽象对 distributed data processing 的访问：DuckDB、FugueSQL 和 Polars。

Data Manipulation with DuckDB

谈到 data processing libraries，大多数 data scientists 都非常熟悉 pandas，这是 Python 中占主导地位的 data processing library。Pandas 以其 simplicity、versatility，以及管理多种 data formats 和 sizes 的能力而闻名。它为 data manipulation 提供了 intuitive user interface。熟悉 SQL 的人也会欣赏其强大 features，这些 features 允许 users 使用简洁 syntax 执行复杂 data transformations。然而，在某些情况下，execution speed 与 tools 的 ease of use 或 expressiveness 之间必须做出 trade-off。当处理超过 memory limits 的 large datasets，或需要复杂 data processing operations 时，这种 dilemma 会变得尤其困难。

在这种情况下，使用 SQL 而不是 pandas 可能是更好的解决方案。这正是 DuckDB 发挥作用的地方。DuckDB 通过提供快速高效的 SQL query execution engine，将 pandas 和 SQL 的优势结合起来，能够在 large datasets 上处理 complex queries。它与 pandas DataFrames 无缝集成，并允许直接在 DataFrames 上执行 queries，而无需频繁 data transfers。借助 DuckDB，data scientists 可以在使用 pandas 的同时发挥 SQL 的力量，在 performance 和 ease of use 之间取得平衡。

此外，我们正在看到一种趋势：一些公司决定用 dbt 结合 DuckDB 来替代 Spark 作为 data processing engine。当然，这需要 case-by-case 判断，但它确实为 analysts 支持更复杂 data transformations 打开了大门，这些 transformations 可以 ad hoc 运行，也可以在 data pipeline 中自动运行。

Installing DuckDB

DuckDB 是一个非常轻量的 database engine，可以在 host process 内部运行，没有 external dependencies。安装很直接，只需要几个简单步骤。

要安装 DuckDB，根据 operating system 和想要的 installation type，有多个选项。目前，先看如何使用 pip package manager 安装 DuckDB，如 Example 3-51 所示。

Example 3-51：Installing DuckDB

pip install duckdb

就这样。现在可以像使用任何其他 library 一样在 Python 中使用 DuckDB。Example 3-52 展示了将 pandas DataFrame 加载到 DuckDB、操作 data，并将结果存回 DataFrame 有多简单。

Example 3-52：Using DuckDB

import pandas as pd
import duckdb

mydf = pd.DataFrame({'a' : [1, 2, 3]})
result = duckdb.query("SELECT SUM(a) FROM mydf").to_df()

可以看到，这段代码同时导入 pandas library，别名为 pd，以及 DuckDB library。这使代码可以访问这些 libraries 提供的 functionalities。接下来，创建名为 mydf 的 pandas DataFrame，它由单个 column a 组成，有三行，values 为 [1, 2, 3]。随后代码使用 DuckDB interface 执行 SQL query。Query 是 SELECT SUM(a) FROM mydf，它计算 mydf DataFrame 中 a column 的 values 之和。SQL query 的结果存储在 result variable 中。通过在 DuckDB query result 上使用 to_df() method，data 被转换为 pandas DataFrame。这允许后续使用 pandas 丰富的 functions 和 methods 进行 further data manipulation 或 analysis。

Running SQL queries with DuckDB

现在我们已经看到一个简单示例，让我们更仔细地看看 DuckDB 的一些核心 features。不同于传统 systems，DuckDB 直接在 application 内部工作，消除了 external processes 或 client/server architectures 的需要。这个 paradigm 与 SQLite 的 in-process model 高度一致，并确保 SQL queries 的 seamless integration 和 efficient execution。

这种 approach 的重要性也延伸到 OLAP 领域。OLAP 是一种支持对大型 enterprise databases 进行 sophisticated analysis，同时最小化对 transactional systems 影响的技术。与其他 OLAP-oriented database management systems 一样，DuckDB 通过其创新的 vectorized query execution engine 处理 complex analytical workloads。其 column-oriented approach 提升 performance 和 scalability，使其成为处理 analytical queries 的理想选择。

DuckDB 的一个显著优势是 self-contained design。不同于传统 databases，DuckDB 不需要你安装、更新或维护任何 external dependencies 或 server software。这种 streamlined、self-contained architecture 简化 deployment，并支持 application 与 database 之间快速 data transfer。结果是一个响应极快且高效的系统。

DuckDB 的另一个有趣 feature 是，它的技术能力归功于勤奋且能力出色的 developers，他们确保了 DuckDB 的 stability 和 maturity。通过来自 leading systems 的数百万 queries 的 rigorous testing，确认了 DuckDB 的 performance 和 reliability。它遵守 ACID property principles，支持 secondary indexes，并提供 robust SQL capabilities，证明其 versatility 和对 demanding analytical workloads 的适用性。

DuckDB 与 Python 和 R 等流行 data analysis frameworks 集成，支持 seamless 和 efficient interactive data analysis。此外，它不仅支持 Python 和 R，还为 C、C++ 和 Java 提供 APIs，这使它可以用于多种 programming languages 和 environments。DuckDB 以 exceptional performance 和 flexibility 著称，非常适合高效处理和查询大量 data。使用 DuckDB 运行 SQL queries 是 analysts 的一项有价值技能。Analysts 可以利用 DuckDB 的力量，轻松执行 complex SQL queries，并从 data 中获得 valuable insights。

现在我们已经对 DuckDB 有了更多了解，让我们做一个 step-by-step exercise 来说明一些 benefits。我们将使用之前同样的 book analysis query。首先，导入所需 libraries：pandas 和 DuckDB，如 Example 3-53 所示。

Example 3-53：Importing libs in DuckDB

import duckdb
import pandas as pd

下一步是连接到 DuckDB 的 in-memory database（Example 3-54）。

Example 3-54：Connecting to DuckDB

con = duckdb.connect()

先创建一个虚构 pandas DataFrame，用 DuckDB 来操作它。执行 Example 3-55 中的代码。

Example 3-55：Loading the data file

import pandas as pd

data = [
    {
        'Title': 'Python for Data Analysis',
        'Author': 'Wes McKinney',
        'Publisher': "O'Reilly",
        'Price': 39.99,
        'UnitsSold': 1000
    },
    {
        'Title': 'Hands-On Machine Learning',
        'Author': 'Aurélien Géron',
        'Publisher': "O'Reilly",
        'Price': 49.99,
        'UnitsSold': 800
    },
    {
        'Title': 'Deep Learning',
        'Author': 'Ian Goodfellow',
        'Publisher': "O'Reilly",
        'Price': 59.99,
        'UnitsSold': 1200
    },
    {
        'Title': 'Data Science from Scratch',
        'Author': 'Joel Grus',
        'Publisher': "O'Reilly",
        'Price': 29.99,
        'UnitsSold': 600
    }
]

df = pd.DataFrame(data)

现在，我们将 DuckDB 引入代码。具体来说，我们从 DataFrame 创建 DuckDB table。这是通过使用 connection 注册 DataFrame 并给它一个名称完成的，这里名称为 sales，如 Example 3-56 所示。这使我们可以使用 SQL 查询和操作 data。

Example 3-56：Creating a DuckDB table

con.register('sales', df)

有了可以 query 的 table 后，现在就可以执行所需 analytics tasks。例如，可以计算 O’Reilly books 的 total revenue，如 Example 3-57 所示。

Example 3-57：Applying an analytics query

query_total_revenue = """
    SELECT SUM(Price * UnitsSold) AS total_revenue
    FROM sales
    WHERE Publisher = "O'Reilly"
"""
total_revenue = con.execute(query_total_revenue).fetchall()[0][0]

如果我们不想 fetch results，而是希望将执行结果存储为 DataFrame，可以在 execution 后直接调用 DuckDB 的 df() function。Example 3-58 创建了 df_total_revenue DataFrame，我们可以继续在 pandas 中操作它。这展示了在 DuckDB 的 SQL interface 和 pandas 之间切换有多顺畅。

Example 3-58：Calling the df() function

query_total_revenue = """
    SELECT SUM(price * unitsSold) AS total_revenue
    FROM sales
    WHERE publisher = "O'Reilly"
"""
df_total_revenue = con.execute(query_total_revenue).df()

最后，使用 Python 中任何可用的 data visualization library 绘制结果，如 Example 3-59 所示。

Example 3-59：Data visualization

# Create a bar plot
plt.bar("O'Reilly", total_revenue)

# Set the plot title and axis labels
plt.title("Total Revenue for O'Reilly Books")
plt.xlabel("Publisher")
plt.ylabel("Total Revenue")

回到 pandas，它确实提供 pandas.read_sql command，允许在 existing database connection 上执行 SQL queries，然后加载到 pandas DataFrames 中。虽然这种 approach 适合 lightweight operations，但它并没有针对 intensive data processing tasks 优化。传统 relational database management systems，例如 Postgres 和 MySQL，会按行顺序处理 rows，导致较长 execution times 和显著 CPU overhead。另一方面，DuckDB 专门为 online analytical processing 设计，并采用 column vectorized approach。这一决策使 DuckDB 能够有效并行化 disk I/O 和 query execution，从而带来显著 performance gains。

在内部，DuckDB 使用 Postgres SQL parser，并提供与 Postgres SQL functions 的完整兼容性。这使你能够使用熟悉的 SQL functions，同时利用 DuckDB 高效的 column processing。凭借对 performance 和 efficiency 的关注，DuckDB 是运行 SQL queries 和 resource-intensive data processing tasks 的有吸引力解决方案，尤其是与传统 RDBMSs 相比。

Data Manipulation with Polars

与 DuckDB 类似，Polars 也专注于克服 pandas 在处理 large datasets 时 performance 低和效率不足的问题。Polars 是一个完全用 Rust 编写的 high-performance DataFrame library，其关键优势之一是 DataFrame 不使用 index。不同于 pandas 依赖经常冗余的 index，Polars 消除了 index 的需求，简化 DataFrame operations，并使它们更直观、更高效。

此外，Polars 使用 Apache Arrow arrays 作为内部 data representation。这与使用 NumPy arrays 的 pandas 不同，尽管 pandas 2.0 可能会修复这一点。使用 Arrow arrays 在 load time、memory usage 和 computation 方面带来显著好处。Polars 利用这种高效 data representation，可以轻松处理 large datasets，并更高效地执行 computations。

Polars 的另一个优势是支持 parallel operations。由于使用 Rust 编写，而 Rust 是一种以 performance 和 concurrency 为重点的语言，Polars 可以利用 multithreading，并行运行多个 operations。这种增强的 parallelization capability 支持更快、更可扩展的 data processing tasks。最后，它还引入了一种强大的 optimization technique，称为 lazy evaluation。当在 Polars 中执行 query 时，library 会检查并优化 query，寻找加速 execution 或减少 memory usage 的机会。这种 optimization process 提升整体 query performance，并增强 data processing efficiency。相比之下，pandas 只支持 eager evaluation，即 expressions 一遇到就立即 evaluate。

Polars 的 data manipulation 对 analytics engineers 非常有价值，因为它具备独特 capabilities。Polars 设计时高度关注 performance 和 scalability，因此非常适合高效处理大量 data。处理 large datasets 的 analytics engineers 可以受益于其 memory-efficient operations 和 parallel processing support，从而实现更快 data transformations。Polars 与 Rust ecosystem 的集成也使其成为 analysts 在 Rust-based data pipelines 中工作的有价值工具，提供 compatibility 和 ease of use。Query optimization capabilities、advanced data manipulation features，以及对 multiple data sources 的支持，使 Polars 成为我们工具箱中有价值的补充，使我们能够以 efficiency 和 flexibility 处理 complex data tasks。

Installing Polars

要安装 Polars，根据 operating system 和想要的 installation type，有多个选项。这里看 Example 3-60，它展示了如何使用 pip package manager 安装 Polars 的简单示例。

Example 3-60：Installing Polars

pip install polars

这会立即让 Polar library 可在 Python context 中使用。让我们通过执行 Example 3-61 中的代码片段测试它。

Example 3-61：Polars DataFrame

import polars as pl

df = pl.DataFrame(
    {
        'Title': ['Python Crash Course', 'Hands-On Machine Learning',
                  'Data Science for Business', 'Learning SQL',
                  'JavaScript: The Good Parts', 'Clean Code'],
        'UnitsSold': [250, 180, 320, 150, 200, 280],
        'Publisher': ["O'Reilly", "O'Reilly", "O'Reilly", "O'Reilly",
                      "O'Reilly", "O'Reilly"],
    }
)
df

我们有一个包含三列的 DataFrame：Title、UnitsSold 和 Publisher。Title column 表示各种 O’Reilly books 的 titles。UnitsSold column 表示每本 book 的 units sold 数量，而 Publisher column 指明所有 books 都由 O’Reilly 出版。

Running SQL queries with Polars

使用 Polars，我们可以对这个 DataFrame 执行各种 operations，以获得关于 O’Reilly book sales 的 insights。无论是计算 total revenue、按 book title 或 author 分析 sales，还是识别 top-selling books，如 Example 3-62 所示，Polars 都提供了一个通用且高效的数据分析平台。

Example 3-62：Polars DataFrame—top-selling books

# Sort the DataFrame by UnitsSold column in descending order
top_selling_books = df.sort(by="UnitsSold", reverse=True)

# Get the top-selling books' title and units sold
top_books_data = top_selling_books.select(["Title",
  "UnitsSold"]).limit(5).to_pandas()

print("Top-selling O'Reilly Books:")
print(top_books_data)

可以看到，我们使用 sort method 根据 UnitsSold column 对 DataFrame df 进行 descending order 排序。然后，使用 limit method 选择前五本 books。最后，使用 to_pandas() 将 resulting DataFrame 转换为 pandas DataFrame，以便更容易打印和展示。

虽然这很有趣，也展示了它在 syntax 上与 pandas 的相似性，但我们之前提到过 Polars 能够将其 functionalities 暴露为 SQL。实际上，Polars 提供多种方式在其 framework 内使用 SQL capabilities。

就像 pandas 一样，Polars 可以与 DuckDB 等 external libraries 无缝集成，使你能够利用它们的 SQL functionalities。你可以从 DuckDB 或 pandas 将 data 导入 Polars，对导入 data 执行 SQL queries，并无缝组合 SQL operations 和 Polars DataFrame operations。这种集成提供了一个全面的数据分析和操作 ecosystem，兼具 SQL 和 Polars 的优点。

在 Example 3-63 中，我们使用 duckdb.connect() 创建 DuckDB connection。然后，创建一个 Polars DataFrame df，包含 O’Reilly books data 的 Title、Author、Publisher、Price 和 UnitsSold columns。我们使用 con.register() 将该 DataFrame 注册为 DuckDB 中名为 books 的 table。接下来，在 books table 上使用 con.execute() 执行 SQL query，选择 Title 和 UnitsSold columns，并按 Publisher = "O'Reilly" 过滤。结果作为 tuples list 返回。我们将结果转换为带指定 column names 的 Polars DataFrame result_df。

Example 3-63：Polars DataFrame with DuckDB

import polars as pl
import duckdb

# Create a DuckDB connection
con = duckdb.connect()

# Create a Polars DataFrame with O'Reilly books data
df = pl.DataFrame({
    'Title': ['Python for Data Analysis'
             , 'Hands-On Machine Learning'
             , 'Deep Learning'
             , 'Data Science from Scratch'],
    'Author': ['Wes McKinney'
              , 'Aurélien Géron'
              , 'Ian Goodfellow'
              , 'Joel Grus'],
    'Publisher': ["O'Reilly"
                 , "O'Reilly"
                 , "O'Reilly"
                 , "O'Reilly"],
    'Price': [39.99, 49.99, 59.99, 29.99],
    'UnitsSold': [1000, 800, 1200, 600]
})

# Register the DataFrame as a table in DuckDB
con.register('books', df)

# Execute a SQL query on the DuckDB table using Polars
result = con.execute("SELECT Title, UnitsSold FROM books WHERE Publisher =
  'O''Reilly'")

# Convert the result to a Polars DataFrame
result_df = pl.DataFrame(result, columns=['Title', 'UnitsSold'])

# Print the result
print(result_df)

# Close the DuckDB connection
con.close()

Polars 也提供 native support，可直接执行 SQL queries，而无需依赖 external libraries。借助 Polars，你可以直接在代码中编写 SQL queries，利用 SQL syntax 执行 data transformations、aggregations 和 filtering operations。这使你能够在 Polars framework 内发挥 SQL 的力量，从而提供一种 convenient 和 efficient 的 structured data 工作方式。

在 Polars 中使用 SQL 是一个简单直接的过程。可以按照以下步骤对 Polars DataFrame 执行 SQL operations。首先，创建 SQL context，用于建立执行 SQL queries 的 environment。该 context 使你能够在 Polars framework 内无缝使用 SQL，如 Example 3-64 所示。

Example 3-64：Create a SQL context

# Create a Polars DataFrame with O'Reilly books data
df = pl.DataFrame({
    'Title': ['Python for Data Analysis'
              , 'Hands-On Machine Learning'
              , 'Deep Learning'
              , 'Data Science from Scratch'],
    'Author': ['Wes McKinney'
               , 'Aurélien Géron'
               , 'Ian Goodfellow'
               , 'Joel Grus'],
    'Publisher': ["O'Reilly"
                 , "O'Reilly"
                 , "O'Reilly"
                 , "O'Reilly"],
    'Price': [39.99, 49.99, 59.99, 29.99],
    'UnitsSold': [1000, 800, 1200, 600]
})

# Create the SQL Context
sql = pl.SQLContext()

Example 3-65 展示了下一步：注册你想 query 的 DataFrame。

Example 3-65：Register the DataFrame

# Register the DataFrame in the context
sql.register('df', df)

通过为 DataFrame 提供名称，你为 SQL queries 建立了一个 reference point。这个 registration step 确保 DataFrame 与一个可识别 identifier 关联起来。

DataFrame 注册后，可以使用 Polars 提供的 query() function 对其执行 SQL queries。该 function 接收 SQL query 作为 input，并返回 Polars DataFrame 作为 result。这个 DataFrame 包含满足 SQL query 中指定 criteria 的 data。让我们看 Example 3-66。

Example 3-66：Run analytics queries

# Run your SQL query
result_df = sql.execute(
    """
      select
        *
      from df
      where Title = 'Python for Data Analysis'
    """
).collect()

通过将 SQL 与 Polars 集成，具备深厚 SQL knowledge 的 data professionals 可以轻松利用 Polars 的力量和效率。他们可以利用已有 SQL skills，并在 Polars framework 内直接应用到 data analysis 和 manipulation tasks 中。这种 seamless integration 允许 users 使用自己熟悉的 SQL syntax，同时利用 library 优化后的 query execution engine。

Data Manipulation with FugueSQL

Fugue 是一个强大的 unified interface，用于 distributed computing，使 users 能够在 Spark、Dask 和 Ray 等流行 distributed frameworks 上无缝运行 Python、pandas 和 SQL code。借助 Fugue，users 可以用最少代码修改，充分发挥这些 distributed systems 的潜力。

Fugue 的主要 use cases 围绕 parallelizing 和 scaling 现有 Python 与 pandas code，使其能够轻松跨 distributed frameworks 运行。通过无缝迁移代码到 Spark、Dask 或 Ray，users 无需大量重写 code，就能利用这些 systems 的 scalability 和 performance benefits。

与我们讨论相关的是，Fugue 提供了一个独特 feature，名为 FugueSQL，使 users 能够通过 advanced SQL interface 在 pandas、Spark 和 Dask DataFrames 上定义 end-to-end workflows。它结合了熟悉的 SQL syntax 与调用 Python code 的能力。这为 users 提供了一个强大工具，用于 streamlining 和 automating data processing workflows。

FugueSQL 提供了多种好处，可以在多个 scenarios 中利用，包括作为 Fugue project 总体目标一部分的 parallel code execution，或在单机上进行 standalone querying。无论是在 distributed systems 中工作，还是在 local machine 上执行 data analysis，它都允许我们高效 query DataFrames。

Installing Fugue and FugueSQL

根据 operating system 和 installation type，有多个选项可以安装 Fugue。Example 3-67 使用 pip install。

Example 3-67：Install Fugue

pip install fugue

Fugue 提供各种 installation extras，用于增强功能，并支持不同 execution engines 和 data processing libraries。这些 installation extras 包括以下内容：

sql

该 extra 启用 FugueSQL support。虽然 Fugue 的非 SQL functionalities 在没有该 extra 的情况下仍然可用，但如果你打算使用 FugueSQL，就必须安装它。为此，执行 Example 3-68 中的代码片段。

Example 3-68：Install FugueSQL

pip install "fugue[sql]"

spark

安装该 extra 会为 Fugue 添加 Spark 作为 ExecutionEngine 的支持。借助该 extra，users 可以利用 Spark capabilities 执行 Fugue workflows。要添加该 extra，运行 Example 3-69 中的代码。

Example 3-69：Install FugueSpark

pip install "fugue[spark]"

dask

该 extra 启用 Fugue 中对 Dask 作为 ExecutionEngine 的支持。安装该 extra 后，users 可以在 Fugue framework 内利用 Dask 的 distributed computing capabilities。

ray

安装该 extra 会添加对 Ray 作为 Fugue 中 ExecutionEngine 的支持。借助该 extra，users 可以在 Fugue workflows 中利用 Ray 的 efficient task scheduling 和 parallel execution capabilities。

duckdb

该 extra 启用 Fugue 中对 DuckDB 作为 ExecutionEngine 的支持。安装该 extra 后，users 可以在 Fugue framework 内使用 DuckDB 的 blazing fast in-memory database 进行 efficient query execution。

polars

安装该 extra 提供对 Polars DataFrames 和使用 Polars library 的 extensions 的支持。借助该 extra，users 可以在 Fugue 内利用 Polars 的 features 和 functionalities 进行 data processing。

ibis

启用该 extra 允许 users 将 Ibis 集成到 Fugue workflows 中。Ibis 提供一种 expressive 和 powerful interface，用于处理 SQL-like queries。安装该 extra 后，users 可以将 Ibis functionality 纳入 Fugue workflows。

cpp_sql_parser

启用该 extra 会将 CPP（C++）antlr parser 用于 Fugue SQL，相比 pure Python parser，它提供显著更快的 parsing。虽然针对主要 Python versions 和 platforms 有 prebuilt binaries，但对于其他 platforms，该 extra 可能需要 C++ compiler 进行 on-the-fly build。

实际上，我们可以在一个 pip install command 中安装前面多个 extras。在 Example 3-70 中，我们用一条 command 安装 Fugue 的 duckdb、polars 和 spark extras。

Example 3-70：Install multiple Fugue extras

pip install "fugue[duckdb,spark,polars]"

另一个有趣 extra 与 notebooks 相关。FugueSQL 为 Jupyter Notebooks 和 JupyterLab 提供 notebook extension。该 extension 提供 syntax highlighting。我们可以再运行一个 pip install 来安装 extension（Example 3-71）。

Example 3-71：Install the notebook extension

pip install fugue-jupyter

fugue-jupyter install startup

第二条 command fugue-jupyter install startup 会将 Fugue 注册到 Jupyter 的 startup script 中，使它在你每次打开 Jupyter Notebooks 或 JupyterLab 时都可用。

如果你已经安装 Fugue 并使用 JupyterLab，%%fsql cell magic 默认会自动注册。这意味着你可以在 JupyterLab environment 中直接使用 cell magic，无需额外步骤。不过，如果你使用 Classic Jupyter Notebooks，或者 %%fsql cell magic 未注册，可以在 notebook 中使用 Example 3-72 的 command 启用它。

Example 3-72：Enable notebooks extensions

from fugue_notebook import setup
setup(is_lab=True)

Running SQL queries with FugueSQL

FugueSQL 专为希望使用 pandas、Spark 和 Dask 等 Python DataFrames 的 SQL users 设计。FugueSQL 提供一个 SQL interface，可以在你选择的底层 engine 上 parse 和运行。这对 data scientists 和 analysts 尤其有利，因为他们更愿意专注于 defining logic 和 data transformations，而不是处理 execution complexity。

但它同样面向 SQL enthusiasts 的需求，使他们能够跨 pandas、Spark 和 Dask 等流行 data processing engines，用 SQL 定义 end-to-end workflows。这样，SQL enthusiasts 可以利用 SQL skills，并轻松编排 complex data pipelines，而无需在不同 tools 或 languages 之间切换。

Fugue 为主要使用 pandas，并希望利用 Spark 或 Dask capabilities 来处理 large datasets 的 data scientists 提供了实用解决方案。使用 Fugue，他们可以轻松 scale pandas code，并无缝迁移到 Spark 或 Dask，以极小努力发挥 distributed computing 的潜力。例如，如果某人将 FugueSQL 与 Spark 一起使用，该 framework 会使用 SparkSQL 和 PySpark 执行 queries。尽管 FugueSQL 支持 nonstandard SQL commands，但必须强调，Fugue 仍与 standard SQL syntax 完全兼容。这种 compatibility 确保 SQL users 可以无缝切换到 Fugue，并利用已有 SQL knowledge 和 skills，而无需重大 customizations 或 complications。

最后，Fugue 正在证明自己是从事 Big Data projects 的 data teams 的宝贵资产，这些 teams 经常面对 code maintenance issues。通过采用 Fugue，这些 teams 可以受益于一个 unified interface，它简化了跨 distributed computing platforms 执行 code 的过程，确保整个 development process 中的 consistency、efficiency 和 maintainability。

Example 3-73 展示了使用 FugueSQL 的 end-to-end 示例。

Example 3-73：FugueSQL full example

import pandas as pd
from pyspark.sql import SparkSession
from fugue.api import fugue_sql_flow

data = [
    {
        'Title': 'Python for Data Analysis',
        'Author': 'Wes McKinney',
        'Publisher': "OReilly",
        'Price': 39.99,
        'UnitsSold': 1000
    },
    {
        'Title': 'Hands-On Machine Learning',
        'Author': 'Aurélien Géron',
        'Publisher': "OReilly",
        'Price': 49.99,
        'UnitsSold': 800
    },
    {
        'Title': 'Deep Learning',
        'Author': 'Ian Goodfellow',
        'Publisher': "OReilly",
        'Price': 59.99,
        'UnitsSold': 1200
    },
    {
        'Title': 'Data Science from Scratch',
        'Author': 'Joel Grus',
        'Publisher': "OReilly",
        'Price': 29.99,
        'UnitsSold': 600
    }
]

# Save the data as parquet
df = pd.DataFrame(data)
df.to_parquet("/tmp/df.parquet")

# Fugue with pandas Engine
import fugue.api as fa

query = """
LOAD "/tmp/df.parquet"

SELECT Author, COUNT(Title) AS NbBooks
 GROUP BY Author
 PRINT
"""

pandas_df = fa.fugue_sql(query, engine="pandas")

# Fugue with Spark Engine
import fugue.api as fa

query = """
LOAD "/tmp/df.parquet"

SELECT Author, COUNT(Title) AS NbBooks
 GROUP BY Author
 PRINT
"""

spark_df = fa.fugue_sql(query, engine="spark")


# Fugue with DuckDB
import fugue.api as fa
import duckdb

query = """
df = LOAD "/tmp/df.parquet"

res = SELECT *
        FROM df
       WHERE Author = 'Wes McKinney'

SAVE res OVERWRITE "/tmp/df2.parquet"
"""

fa.fugue_sql(query, engine="duckdb")

with duckdb.connect() as conn:
        df2 = conn.execute("SELECT * FROM '/tmp/df2.parquet'").fetchdf()
        print(df2.head())

这个示例创建了一个 FugueSQLWorkflow instance。我们使用 workflow.df() method 将 pandas DataFrame df 注册为 table。然后，在 workflow.run() method 内编写 SQL queries，对 data 执行各种 operations。FugueSQLWorkflow 是 Fugue library 提供的 class，作为执行 FugueSQL code 的入口点。它允许我们在各种 data sources 上定义和执行 SQL queries，如前所述，无需显式 data transformations 或处理底层 execution engines。

该示例展示了三个 queries：

• 计算 O’Reilly books 的 total revenue
• 计算 O’Reilly books 的 average price
• 检索 top-selling O’Reilly books

结果被存储在 result object 中，我们可以使用 first() 和 collect() methods 访问 data。

最后，将结果打印到 console。注意，我们在 SQL queries 中使用两个单引号 '' 来 escape Publisher name "O'Reilly" 中的单引号，以确保 syntax 正确。

有人可能会问，FugueSQL 是否是 pandas 的替代或演进，因为 pandas 有 pandasql。我们的观点是，pandasql 只支持 SQLite 作为 backend，而 FugueSQL 支持多个 local backends，例如 pandas、DuckDB、Spark 和 SQLite。当使用 pandas backend 运行 FugueSQL 时，SQL queries 会直接转换为 pandas operations，从而消除 data transfer 的需要。类似地，DuckDB 对 pandas 有出色支持，因此 data transfer overhead 极小。因此，pandas 和 DuckDB 都是 FugueSQL 中 local data processing 的推荐 backends。总而言之，FugueSQL 是一个非常好的 framework，可以利用 SQL syntax，并增加 distributed processing 和 scale 下 data manipulation 的能力。

总体而言，Fugue、DuckDB 和 pandas 都是强大的工具，提供高效 data processing capabilities。然而，无论使用哪种技术，必须认识到 proper data modeling 是 successful scalability 的基础。如果没有设计良好的 data model，任何 system 都难以高效处理 large-scale data processing。

Robust data model 的 foundation 确保 data 被 structured、organized，并针对 analysis 和 manipulation 优化。通过理解 data entities 之间的 relationships、定义适当 data types，并建立高效 indexing strategies，我们可以创建 scalable architecture，最大化 performance，并支持跨 platforms 和 tools 的 seamless data operations。因此，虽然 Fugue、DuckDB 和 pandas 对高效 data processing 有贡献，但若想实现 scalability，proper data modeling 的重要性怎么强调都不为过。这也是我们在第 2 章覆盖 data modeling 的主要原因之一。

Bonus：Training Machine Learning Models with SQL

这个标题可能让你觉得我们在推进 SQL-like capabilities 的边界，但现实是，得益于一个非常特定的 library，也就是 dask-sql，确实可以在 SQL 中使用 Python machine learning ecosystem。

Dask-sql 是一个近期开发的 SQL query engine，目前处于 experimental phase，它建立在基于 Python 的 Dask distributed library 之上。它提供一种独特能力，可以无缝集成 Python 和 SQL，使 users 能够执行 distributed 和 scalable computations。这个创新 library 打开了利用 Python 和 SQL 各自优势进行 data analysis 和 processing 的机会。

我们可以运行 pip install 来安装 extension，如 Example 3-74 所示。

Example 3-74：Install dask-sql

pip install dask-sql

在 Example 3-75 中，我们通过 c = Context() 这一行创建 Context class 的 instance。借助它，我们正在为 SQL queries 初始化一个新的 execution context。该 context 可用于对 data 执行 SQL queries，并进行 filtering、aggregating 和 joining 等 operations；它也可以应用 Dask 提供的一种特殊 command，用于 training 和 testing machine learning models。

Example 3-75：Import the context from dask_sql

from dask_sql import Context

c = Context()

现在我们拥有加载 dataset 所需的全部工具。在 Example 3-76 中，我们使用 Dask 的 read_csv() function 读取 Iris dataset。Data 加载完成后，可以作为 Dask DataFrame 访问和操作它。

下一步是将加载的 Dask DataFrame（df）注册为 dask-sql Context 中名为 iris 的 table。使用 Context class 的 create_table method 注册 table。该步骤完成后，我们就可以使用 SQL syntax query data。

Example 3-76：Load the data as a Dask DataFrame and register it as a table

# Load data: Download the iris dataset
df = dd.read_csv('https://datahub.io/machine-learning/iris/r/iris.csv')

# Register a Dask table
c.create_table("iris", df)

让我们使用 dask-sql Context object 的 sql() function 运行一个简单 select，并将 SQL query 作为 parameter 传入，如 Example 3-77 所示。

Example 3-77：Access the dask-sql table

# Test accessing the data
c.sql("""
    select * from iris
""")

Data 准备好后，现在可以使用 training components 训练 machine learning model。为此，我们先使用 CREATE OR REPLACE MODEL statement，这是 dask-sql 的 extension，允许你在 SQL context 中定义并训练 machine learning models。

在这个案例中，clustering model 名为 clustering，并使用来自 scikit-learn library 的 KMeans algorithm 创建 model。KMeans 是一种流行的 unsupervised learning algorithm，用于 clustering data points。很有意思的是，dask-sql 允许我们使用来自 scikit-learn 等 third-party libraries 的 model classes。n_clusters parameter 设置为 3，表示该 algorithm 应在 data 中识别三个 clusters。

在 Example 3-78 中，我们展示了该 model 的 training data 来自注册在 c context 中的 iris table。SELECT statement 指定用于 training 的 features，包括 sepallength、sepalwidth、petallength 和 petalwidth columns。

Example 3-78：Create our clustering model

# Train: Create our clustering model using sklearn.cluster.KMeans algorithm
c.sql("""
  CREATE OR REPLACE MODEL clustering WITH (
      model_class = 'sklearn.cluster.KMeans',
      wrap_predict = True,
      n_clusters = 3
  ) AS (
      SELECT sepallength, sepalwidth, petallength, petalwidth
      FROM iris
  )
""")

现在可以运行 SHOW MODELS command，验证 model 确实已被创建（Example 3-79）。该 command 类似传统 SQL engines 中常用的 SHOW TABLES。后者显示 database 某个 schema 中的所有 tables，而前者列出在 dask-sql context 中创建并可用的所有 models。

Example 3-79：Show the list of models

# Show the list of models which are trained and stored in the context.
c.sql("""
  SHOW MODELS
""")

另一个有趣 command 是 DESCRIBE MODEL MODEL_NAME（Example 3-80），它显示用于训练该 model 的所有 hyperparameters。

Example 3-80：Get all hyperparameters of a certain model

# To get the hyperparameters of the trained MODEL
c.sql("""
  DESCRIBE MODEL clustering
""")

在 Example 3-81 中，我们展示了 dask-sql 中最吸引人的 commands 之一：PREDICT command。它使用刚创建的 clustering model，为 df DataFrame 的 rows 预测 cluster classes。带 PREDICT 的 SELECT statement 会在 SQL context 中，将 trained machine learning models 应用于某个 table 中的新 data points。

在这个案例中，PREDICT command 被用于将 clustering model 应用到 iris table 的前 100 行。MODEL clause 指定要使用的 model 名称，也就是 clustering。PREDICT command 中的 SELECT statement 指定用于 prediction 的 features，这些 features 与 model training step 中使用的相同，如 Example 3-81 所示。

Example 3-81：Make predictions

''' Predict: Test the recently created model by applying
the predictions to the rows of the df—
in this case assign each observation to a cluster'''
c.sql("""
  SELECT * FROM PREDICT (
      MODEL clustering,
      SELECT sepallength, sepalwidth, petallength, petalwidth FROM iris
      LIMIT 100
  )
""")

Dask-sql 的另一个有趣能力是 experiments component。它会通过使用 CREATE EXPERIMENT statement 运行一个 experiment，为 clustering model 尝试不同 hyperparameter values。

在 Example 3-82 中，experiment 名为 first_experiment。它使用来自 scikit-learn 的 GridSearchCV class，这是一种流行的 hyperparameter tuning technique。本案例中被 tuning 的 hyperparameter 是 clusters 数量（n_clusters），这里只是为了展示 capability。tune_parameters parameter 指定要为 n_clusters hyperparameter 尝试的 values 范围。在这个例子中，experiment 会尝试三个 values（2、3 和 4），也就是我们期望获得的 cluster 数量。

在 machine learning project 的真实场景中，应专注于选择 model 中最 relevant 的 hyperparameters。这取决于问题是 classification 还是 regression task，以及所使用的 algorithms 类型。

Example 3-82：Hyperparameter tuning

# Hyperparameter tuning: Run an experiment to try different parameters
c.sql("""
  CREATE EXPERIMENT first_experiment WITH (
    model_class = 'sklearn.cluster.KMeans',
    experiment_class = 'GridSearchCV',
    tune_parameters = (n_clusters = ARRAY [2, 3, 4]),
    experiment_kwargs = (n_jobs = -1),
    target_column = 'target'
    ) AS (
        SELECT sepallength, sepalwidth, petallength, petalwidth, class AS target
        FROM iris
        LIMIT 100
    )
""")

最后，还有 EXPORT MODEL statement，如 Example 3-83 所示。在这个案例中，model 通过将 format parameter 设置为 pickle，以 pickle format 导出。Pickle 是 Python-specific binary serialization format，允许保存和加载 Python objects。

location parameter 指定 exported model file 应保存的 path 和 filename。在这个例子中，model 被保存在当前目录下，文件名为 clustering.pkl。

Example 3-83：Export the model as a pickle file

# Export the model: Export as a pickle file to be used in other contexts
c.sql("""
  -- for pickle model serialization
  EXPORT MODEL clustering WITH (
      format ='pickle',
      location = './clustering.pkl'
  )
""")

总体而言，dask-sql 是一个强大且有前景的 machine learning 工具，它为 large datasets 上的 data manipulation 和 machine learning operations 提供 SQL interface。借助 dask-sql，我们可以使用熟悉的 SQL syntax 来 query 和 transform data，也可以使用 scikit-learn 等流行 libraries 来 train 和 evaluate machine learning models。它允许我们注册 data tables，应用 SQL queries 进行 data preprocessing，并在 SQL context 中创建和训练 machine learning models。

不过，必须强调的是，dask-sql 仍处于 experimental phase。虽然它对想探索 machine learning 领域的 SQL lovers 来说是一个很迷人的工具，但随着它成长和成熟，使用时仍需谨慎。

Summary

在本章结束时，让我们回顾 databases 和 SQL 的重要旅程，以及它们对过去和未来不可否认的影响。SQL 仍然是不断前进的数据格局中可靠且坚定的组成部分，它结合了成熟技术和现代 analytical insights，从而确保一个乐观的未来。

我们的探索表明，从清晰的 table structures，到满足紧迫 business requirements 的 sophisticated models，SQL 的重要性仍在持续，而 databases 也在不断创新。

然而，值得承认的是，这些工具的有效性取决于使用者的技能。持续学习和 flexibility 对 analytics engineers 至关重要。SQL、database management 和 data analysis fields 都在不断演进。要取得成功，我们必须保持更新、保持好奇，并自信面对挑战。

随着 data landscape 持续快速扩张，data engineering、analysis 和 data science 中各角色之间的区别正在变得更加明显。虽然这些角色当然存在重叠和融合的领域，但 data 的巨大 volume 和 complexity 推动了对 specialized skills 和 expertise 的需求。本章的结论提醒我们，analytics engineering 领域既广阔又迷人。在每个 query 和 database 中，都蕴藏着新的探索与创新机会，而这一切都由日益增长的 specialized roles 需求所驱动，这些角色负责驾驭当今 data landscape 的复杂性。