使用 GROUP BY 进行数据库分析：以图书销售数据库为例

让我们通过一个简单但实用的例子来理解 GROUP BY 的使用。我们将以一个图书销售数据库为例。这个数据库包含两张表：一张是图书信息表 (books)，另一张是销售记录表 (sales)。我们会先创建这两张表，然后插入一些数据，并展示如何使用 GROUP BY 来分析销售数据。

背景信息

在数据库管理和数据分析中，GROUP BY 语句是一个强大的工具，它可以让我们根据某些列的值对数据进行分组。这对于汇总信息、生成报告或进行市场分析等任务至关重要。在本例中，我们将展示如何利用 GROUP BY 来插入、更新和分析图书销售数据。

建表和插入数据

我们的数据库设计涉及两张主要表格：图书信息表 (books) 和销售记录表 (sales)。以下是这两张表的结构和示例数据

图书信息表 (books):

• book_id (图书ID)
• title (图书标题)
• author (作者)

销售记录表 (sales):

• sale_id (销售ID)
• book_id (图书ID)
• sale_date (销售日期)
• quantity (数量)
• price (价格)

sql如下：

-- 创建书籍表
CREATE TABLE test.books (
    book_id INT PRIMARY KEY,
    title VARCHAR(100),
    author VARCHAR(100)
);
-- 创建销售表
CREATE TABLE test.sales (
    sale_id INT PRIMARY KEY,
    book_id INT,
    sale_date DATE,
    quantity INT,
    price DECIMAL(10, 2),
    FOREIGN KEY (book_id) REFERENCES books(book_id)
);

-- 插入数据
INSERT INTO test.books (book_id, title, author) VALUES
(1, 'Data Science 101', 'Alex Johnson'),
(2, 'SQL Essentials', 'Maria Lee'),
(3, 'Advanced Analytics', 'An Nguyen');


INSERT INTO test.sales (sale_id, book_id, sale_date, quantity, price) VALUES
(1, 1, '2023-06-01', 10, 29.99),
(2, 2, '2023-06-02', 5, 59.99),
(3, 3, '2023-06-03', 2, 89.99),
(4, 1, '2023-06-04', 3, 29.99),
(5, 2, '2023-06-05', 7, 59.99);

使用 GROUP BY

GROUP BY 语句允许我们按照一个或多个列来聚集数据，这对于理解数据模式非常有用。在此示例中，我们将展示如何用它来计算每本书的总销售量、每位作者的书籍总销售额以及每本书的最大单次销售量。

计算每本书的总销售量：

SELECT b.title, SUM(s.quantity) as total_quantity
FROM sales s
JOIN books b ON s.book_id = b.book_id
GROUP BY b.title;

如下：

计算每位作者的书籍总销售额：

select b.author,sum(s.price * s.quantity) as total
FROM test.books  b
join test.sales s  on b.book_id = s.book_id
group by b.author;

如下：

查找每本书的最大单次销售量：

SELECT b.title, MAX(s.quantity) as max_quantity
FROM test.sales s
JOIN test.books b ON s.book_id = b.book_id
GROUP BY b.title;

如下：

结论

通过上述例子，我们可以看到 GROUP BY 在数据库查询中的实用性和灵活性。无论是简单的数据汇总还是复杂的数据分析，GROUP BY 都是一个不可或缺的工具。理解并掌握它的使用，对于任何涉及数据库管理和数据分析的工作都是极其重要的。