SQLite 数据分组 - 聚合函数、HAVING 子句和排序

介绍

在这个实验中，你将学习如何使用聚合函数和分组子句在 SQLite 中汇总和分析数据。你将探索 COUNT 和 SUM 进行计算，按单列对数据进行分组，使用 HAVING 过滤组，以及对分组输出进行排序。这种实践经验将为你提供必要的 SQLite 数据操作技能。

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL sqlite(("SQLite")) -.-> sqlite/SQLiteGroup(["SQLite"]) sqlite/SQLiteGroup -.-> sqlite/init_db("Create SQLite Database") sqlite/SQLiteGroup -.-> sqlite/make_table("Create New Table") sqlite/SQLiteGroup -.-> sqlite/get_all("Select All Rows") sqlite/SQLiteGroup -.-> sqlite/query_where("Filter With WHERE") sqlite/SQLiteGroup -.-> sqlite/sort_data("Sort With ORDER BY") subgraph Lab Skills sqlite/init_db -.-> lab-552547{{"SQLite 数据分组"}} sqlite/make_table -.-> lab-552547{{"SQLite 数据分组"}} sqlite/get_all -.-> lab-552547{{"SQLite 数据分组"}} sqlite/query_where -.-> lab-552547{{"SQLite 数据分组"}} sqlite/sort_data -.-> lab-552547{{"SQLite 数据分组"}} end

创建 Orders 表并插入数据

在这一步中，你将创建一个名为 sales.db 的数据库，并在其中创建一个 orders 表。然后，你将向该表中插入示例数据。在整个实验中，该表将用于练习数据分组技术。

首先，在 LabEx 虚拟机中打开你的终端。你的默认路径是 /home/labex/project。

首先，创建 sales.db 数据库并使用以下命令连接到它：

sqlite3 sales.db

此命令将打开 SQLite shell，你将看到类似 sqlite> 的提示符。

接下来，创建包含 order_id、customer_id、product_name、quantity 和 price 列的 orders 表。执行以下 SQL 命令：

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    product_name TEXT,
    quantity INTEGER,
    price REAL
);

此命令将创建具有指定列和数据类型的 orders 表。order_id 列被设置为主键（primary key）。

现在，将示例数据插入到 orders 表中。逐个运行以下 INSERT 语句：

INSERT INTO orders (customer_id, product_name, quantity, price) VALUES
(1, 'Laptop', 1, 1200.00),
(1, 'Mouse', 2, 25.00),
(2, 'Keyboard', 1, 75.00),
(2, 'Monitor', 1, 300.00),
(3, 'Laptop', 1, 1200.00),
(3, 'Headphones', 1, 100.00),
(1, 'Keyboard', 1, 75.00);

这些命令将七行数据插入到 orders 表中，表示不同的客户订单。

要验证数据是否已正确插入，你可以运行一个简单的 SELECT 查询：

SELECT * FROM orders;

此命令将显示 orders 表中的所有行和列。

使用 COUNT 和 SUM 进行聚合

在这一步中，你将使用 COUNT 和 SUM 聚合函数对 orders 表中的数据执行计算。聚合函数允许你将来自多行的数据汇总为单个结果。

你应该仍然连接到上一步中的 sales.db 数据库。如果不是，请使用以下命令重新连接：

sqlite3 sales.db

首先，让我们使用 COUNT 函数来确定表中订单的总数。执行以下 SQL 命令：

SELECT COUNT(*) FROM orders;

此查询将返回 orders 表中的总行数，表示订单总数。

COUNT(*) 函数计算表中的所有行，无论任何列是否包含 NULL 值。

接下来，让我们使用 SUM 函数来计算所有已订购产品的总数量。执行以下 SQL 命令：

SELECT SUM(quantity) FROM orders;

此查询将返回 orders 表中所有行的 quantity 列的总和。

SUM 函数将指定列中的值相加。

最后，让我们计算所有订单产生的总收入。执行以下 SQL 命令：

SELECT SUM(quantity * price) FROM orders;

此查询将每行的 quantity 和 price 列相乘，然后对结果求和，从而得出总收入。

按单列分组

在这一步中，你将学习如何使用 GROUP BY 子句根据一个或多个列中的值对行进行分组。这通常与聚合函数结合使用，以计算每个组的汇总统计信息。

你应该仍然连接到上一步中的 sales.db 数据库。如果不是，请使用以下命令重新连接：

sqlite3 sales.db

让我们按 customer_id 对 orders 表进行分组，并计算每个客户的订单数量。执行以下 SQL 命令：

SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id;

此查询将返回每个唯一客户的 customer_id 和订单数量 (order_count)。GROUP BY customer_id 子句告诉 SQLite 根据 customer_id 列中的值对行进行分组。然后，COUNT(*) 函数计算每个组中的行数。

接下来，让我们按 product_name 对 orders 表进行分组，并计算每个产品的总订购数量。执行以下 SQL 命令：

SELECT product_name, SUM(quantity) AS total_quantity FROM orders GROUP BY product_name;

此查询将返回每个唯一产品的 product_name 和总订购数量 (total_quantity)。GROUP BY product_name 子句告诉 SQLite 根据 product_name 列中的值对行进行分组。然后，SUM(quantity) 函数计算每个组的 quantity 列的总和。

最后，让我们按 customer_id 对 orders 表进行分组，并计算每个客户产生的总收入。执行以下 SQL 命令：

SELECT customer_id, SUM(quantity * price) AS total_revenue FROM orders GROUP BY customer_id;

此查询将返回每个客户的 customer_id 和总收入 (total_revenue)。

将 HAVING 应用于分组

在这一步中，你将学习如何使用 HAVING 子句在 GROUP BY 子句创建分组后对其进行过滤。HAVING 子句类似于 WHERE 子句，但它作用于组而不是单个行。

你应该仍然连接到上一步中的 sales.db 数据库。如果不是，请使用以下命令重新连接：

sqlite3 sales.db

让我们按 customer_id 对 orders 表进行分组，并计算每个客户的订单数量。然后，我们将使用 HAVING 子句来过滤结果，仅包括下过一个以上订单的客户。执行以下 SQL 命令：

SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id HAVING COUNT(*) > 1;

此查询将返回下过一个以上订单的每个客户的 customer_id 和订单数量 (order_count)。GROUP BY customer_id 子句按 customer_id 对行进行分组，HAVING COUNT(*) > 1 子句过滤这些组，仅包括订单计数大于 1 的组。

接下来，让我们按 product_name 对 orders 表进行分组，并计算每个产品的总订购数量。然后，我们将使用 HAVING 子句来过滤结果，仅包括总订购数量大于 1 的产品。执行以下 SQL 命令：

SELECT product_name, SUM(quantity) AS total_quantity FROM orders GROUP BY product_name HAVING SUM(quantity) > 1;

此查询将返回总订购数量大于 1 的每个产品的 product_name 和总订购数量 (total_quantity)。

最后，让我们按 customer_id 对 orders 表进行分组，并计算每个客户产生的总收入。然后，我们将使用 HAVING 子句来过滤结果，仅包括产生超过 1000 美元收入的客户。执行以下 SQL 命令：

SELECT customer_id, SUM(quantity * price) AS total_revenue FROM orders GROUP BY customer_id HAVING SUM(quantity * price) > 1000;

此查询将返回产生超过 1000 美元收入的每个客户的 customer_id 和总收入 (total_revenue)。

排序分组输出

在这一步中，你将学习如何使用 ORDER BY 子句对包含 GROUP BY 子句的查询的输出进行排序。对分组输出进行排序可以更轻松地分析和理解数据。

你应该仍然连接到上一步中的 sales.db 数据库。如果不是，请使用以下命令重新连接：

sqlite3 sales.db

让我们按 customer_id 对 orders 表进行分组，并计算每个客户的订单数量。然后，我们将使用 ORDER BY 子句根据订单数量以降序对结果进行排序。执行以下 SQL 命令：

SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id ORDER BY order_count DESC;

此查询将返回每个客户的 customer_id 和订单数量 (order_count)，并根据 order_count 降序排序。GROUP BY customer_id 子句按 customer_id 对行进行分组，ORDER BY order_count DESC 子句根据 order_count 别名以降序对结果进行排序。

接下来，让我们按 product_name 对 orders 表进行分组，并计算每个产品的总订购数量。然后，我们将使用 ORDER BY 子句根据产品名称以升序对结果进行排序。执行以下 SQL 命令：

SELECT product_name, SUM(quantity) AS total_quantity FROM orders GROUP BY product_name ORDER BY product_name ASC;

此查询将返回每个产品的 product_name 和总订购数量 (total_quantity)，并根据 product_name 升序排序。

最后，让我们按 customer_id 对 orders 表进行分组，并计算每个客户产生的总收入。然后，我们将使用 ORDER BY 子句根据总收入以降序对结果进行排序。执行以下 SQL 命令：

SELECT customer_id, SUM(quantity * price) AS total_revenue FROM orders GROUP BY customer_id ORDER BY total_revenue DESC;

此查询将返回每个客户的 customer_id 和总收入 (total_revenue)，并根据 total_revenue 降序排序。

要退出 SQLite shell，请运行：

.exit

总结

在这个实验中，你已经学习了如何使用聚合函数（aggregate function），如 COUNT 和 SUM 来总结 SQLite 中的数据。你创建了一个包含 orders 表的 sales.db 数据库，并插入了示例数据。然后，你使用 COUNT(*) 确定了订单总数，并使用 SUM(quantity * price) 计算了总收入。你还学习了如何使用 GROUP BY 子句对数据进行分组，使用 HAVING 子句过滤分组，以及使用 ORDER BY 子句对输出进行排序。这些技能为你在 SQLite 中进行数据分析提供了坚实的基础。