当前位置：首页 > article >正文

如何在 MySQL 5.6 中实现按季度分组并找到销量最高的书籍

article 2025/2/21 9:27:56

如何在 MySQL 5.6 中实现按季度分组并找到销量最高的书籍

- 引言
- 问题描述
- 实现步骤
- - 1. 计算每本书在每个季度的累计销量
  - 2. 找到每个季度的最高累计销量
  - 3. 匹配最高销量的书籍
- 总结
- 扩展练习

引言

在数据分析和业务报表中，经常需要对数据进行分组统计，并找到每个分组中的最大值或最小值。例如，在图书销售系统中，我们可能需要按季度统计每本书的销量，并找到每个季度销量最高的书籍。然而，在 MySQL 5.6 中，由于不支持窗口函数（如 RANK() 或 ROW_NUMBER()）和 WITH 语句（CTE），实现这一功能需要一些技巧。本文将详细介绍如何在 MySQL 5.6 中实现这一需求。

问题描述

假设我们有两个表：

books 表：存储书籍信息，包括 bookid 和 title。
sales 表：存储销售记录，包括 saleid、bookid、saledate 和 quantity。

我们的目标是：

按季度统计每本书的累计销量。
找到每个季度销量最高的书籍。
如果某个季度有多本书的销量相同且都是最高销量，则一起展示。

实现步骤

1. 计算每本书在每个季度的累计销量

首先，我们需要计算每本书在每个季度的累计销量。可以通过 GROUP BY 和 SUM() 函数来实现。

SELECT
    t2.title,
    t1.bookid,
    QUARTER(t1.saledate) AS sale_QUARTER,
    SUM(t1.quantity) AS total_quantity
FROM
    sales t1
JOIN
    books t2 ON t1.bookid = t2.bookid
WHERE
    YEAR(t1.saledate) = 2023  -- 假设查询 2023 年的数据
GROUP BY
    t1.bookid, t2.title, QUARTER(t1.saledate);

解释：

使用 QUARTER(t1.saledate) 提取季度信息。
使用 SUM(t1.quantity) 计算每本书在每个季度的累计销量。
按 bookid、title 和 quarter 分组。

结果示例：

title	bookid	sale_QUARTER	total_quantity
Book A	1	1	25
Book B	2	1	30
Book C	3	1	30

2. 找到每个季度的最高累计销量

接下来，我们需要找到每个季度的最高累计销量。可以通过子查询和 MAX() 函数来实现。

SELECT
    sale_QUARTER,
    MAX(total_quantity) AS max_quantity
FROM (
    SELECT
        QUARTER(t1.saledate) AS sale_QUARTER,
        SUM(t1.quantity) AS total_quantity
    FROM
        sales t1
    WHERE
        YEAR(t1.saledate) = 2023
    GROUP BY
        t1.bookid, QUARTER(t1.saledate)
) AS quarterly_sales
GROUP BY
    sale_QUARTER;

解释：

子查询计算每本书在每个季度的累计销量。
外层查询按季度分组，并使用 MAX(total_quantity) 找到每个季度的最高销量。

结果示例：

sale_QUARTER	max_quantity
1	30
2	40

3. 匹配最高销量的书籍

最后，我们将第一步的结果与第二步的结果连接，找到每个季度累计销量等于最高销量的书籍。

SELECT
    table1.sale_QUARTER,
    table1.title,
    table1.bookid,
    table1.total_quantity
FROM (
    -- 第一步的结果
    SELECT
        t2.title,
        t1.bookid,
        QUARTER(t1.saledate) AS sale_QUARTER,
        SUM(t1.quantity) AS total_quantity
    FROM
        sales t1
    JOIN
        books t2 ON t1.bookid = t2.bookid
    WHERE
        YEAR(t1.saledate) = 2023
    GROUP BY
        t1.bookid, t2.title, QUARTER(t1.saledate)
) AS table1
JOIN (
    -- 第二步的结果
    SELECT
        sale_QUARTER,
        MAX(total_quantity) AS max_quantity
    FROM (
        SELECT
            QUARTER(t1.saledate) AS sale_QUARTER,
            SUM(t1.quantity) AS total_quantity
        FROM
            sales t1
        WHERE
            YEAR(t1.saledate) = 2023
        GROUP BY
            t1.bookid, QUARTER(t1.saledate)
    ) AS quarterly_sales
    GROUP BY
        sale_QUARTER
) AS max_sales
ON table1.sale_QUARTER = max_sales.sale_QUARTER
   AND table1.total_quantity = max_sales.max_quantity
ORDER BY
    table1.sale_QUARTER;