基于本文回答

播面 播面

文图音视，全方位拆解八股文
0
评论
请从左侧选择一个分类

给定商品销售流水表，统计每个月、每个商品大类（Category）下，销售额排名前 3 的商品 ID 及其销售额。

面试题：每个月各商品大类销售额 Top 3 商品统计

一、 题目描述

在电商数据分析中，流水的实时与离散分析至关重要。给定一张商品销售流水表 sales_records，请编写一个 SQL 查询，统计每个月、每个商品大类（Category）下，销售额排名前 3 的商品 ID 及其对应的总销售额。

二、 示例数据

商品销售流水表 (sales_records)

sale_id sale_date product_id category amount
1 2023-10-01 P001 Electronics 100
2 2023-10-02 P001 Electronics 200
3 2023-10-03 P002 Electronics 500
4 2023-10-04 P003 Electronics 300
5 2023-10-05 P004 Electronics 100
6 2023-10-06 P005 Electronics 600
7 2023-10-10 P006 Clothing 200
8 2023-10-11 P007 Clothing 400
9 2023-10-12 P008 Clothing 100
10 2023-10-13 P009 Clothing 300
11 2023-11-01 P001 Electronics 800
12 2023-11-02 P002 Electronics 200
13 2023-11-03 P003 Electronics 400
14 2023-11-04 P004 Electronics 500

三、 期望输出结果

sale_month category product_id total_amount
2023-10 Clothing P007 400
2023-10 Clothing P009 300
2023-10 Clothing P006 200
2023-10 Electronics P005 600
2023-10 Electronics P002 500
2023-10 Electronics P001 300
2023-11 Electronics P001 800
2023-11 Electronics P004 500
2023-11 Electronics P003 400

数据解析说明：

  • 在 2023-10 的 Electronics 大类中，P001 销售了两次（100+200=300），累计总额后，与 P005(600)、P002(500)、P003(300) 共同竞争。最终前三名为 P005、P002 以及 P001（注：P001 与 P003 销售额相同，这里采用标准窗口函数进行排序截取）。

四、 SQL 解决方案（基于标准 SQL / MySQL 8.0+）

sql 
WITH monthly_sales AS (
    -- 第一步：按月、分类、商品汇总销售额
    SELECT 
        DATE_FORMAT(sale_date, '%Y-%m') AS sale_month,
        category,
        product_id,
        SUM(amount) AS total_amount
    FROM 
        sales_records
    GROUP BY 
        DATE_FORMAT(sale_date, '%Y-%m'),
        category,
        product_id
),
ranked_sales AS (
    -- 第二步：在每个月、每个分类内进行区域内排名
    SELECT 
        sale_month,
        category,
        product_id,
        total_amount,
        ROW_NUMBER() OVER (
            PARTITION BY sale_month, category 
            ORDER BY total_amount DESC
        ) AS rn
    FROM 
        monthly_sales
)
-- 第三步：筛选出排名前 3 的商品
SELECT 
    sale_month,
    category,
    product_id,
    total_amount
FROM 
    ranked_sales
WHERE 
    rn <= 3
ORDER BY 
    sale_month ASC, 
    category ASC, 
    rn ASC;

五、 SQL 深度解析与面试加分项

在面试中，仅仅写出上述 SQL 只能达到及格线。如果能主动向面试官分析以下维度，将大大增加通过率：

1. 核心考点：窗口函数的选择 (ROW_NUMBER vs DENSE_RANK vs RANK)

这是该题最经典的追加提问。在遇到销售额相同（并列）的情况时，这三个函数处理方式不同：

  • ROW_NUMBER()：连续排序，不跳跃。即使销售额相同，也会强制分出 1、2、3 名（如示例中的 P001 和 P003 都是 300，只会随机或按默认顺序取一个进入 Top 3）。
  • DENSE_RANK()：并列排序，且不跳跃。如果出现两个第一名，排序为 1, 1, 2。如果用于此题，可能会输出超过 3 个商品。
  • RANK()：并列排序，但会跳跃。如果出现两个第一名，排序为 1, 1, 3。
  • 面试通关表述：“在实际业务中，如果有并列第三名，我们需要与业务方确认：是必须要严格限制只取3个（用 ROW_NUMBER），还是允许并列一并输出（用 DENSE_RANK）。本方案中我采用了主流的 ROW_NUMBER。”

2. 性能优化：如何建立索引？

对于海量数据的流水表，直接进行 GROUP BY 和 OVER 会触发全表扫描和文件排序（Filesort），性能极差。

  • 推荐索引设计：在 (sale_date, category, product_id, amount) 上建立联合索引。
  • 原理：利用覆盖索引（Covering Index），避免回表查询；同时，索引的有序性可以加速 GROUP BY 的聚合操作。

3. 语法细节：时间的提取

  • 本题使用了 DATE_FORMAT(sale_date, '%Y-%m') 来格式化月份。
  • 如果在 Hive/Spark SQL 环境下，使用 substr(sale_date, 1, 7) 性能通常会优于时间转换函数，因为避免了底层日期引擎的解析消耗。

4. 代码结构：为什么使用 CTE (WITH 语句)？

  • 使用 WITH monthly_sales AS (...)（公用表表达式，CTE）而不是多层嵌套子查询，可以极大提高代码的可读性与可维护性。
  • 现代优化器（如 MySQL 8.0+, PostgreSQL）对 CTE 的优化已经非常成熟，不会带来额外的性能损失。
00:00
00:00