题解 | #每个商品的销售总额#

这道题目要求我们计算每个商品的销售总量，下面是这个SQL查询的思路和实现步骤。

拆解题目

1. 确定总体问题

目标是计算每个商品的销售总量，并按商品的还款能力级别对其进行排名。输出商品名称、销售总量和在其类别中的排名，并按还款能力级别和排名进行排序。

2. 分析关键问题

• 连接表：我们需要将products和orders表连接起来，以便获取每个订单的商品信息。
• 计算销售总量：对于每个商品，计算其销售总量。
• 分组和排名：按还款能力级别分组，并在每个组内对商品按销售总量进行排名。
• 格式化输出：输出商品名称、销售总量和在其类别中的排名，并按还款能力级别和排名进行排序。

3. 解决每个关键问题的代码及讲解

1. 连接表及计算销售总量

目标：将products和orders表连接起来，以便获取每个订单的商品信息。

代码：

WITH sales AS (
    SELECT 
        p.product_id,
        p.name AS product_name,
        p.category,
        SUM(o.quantity) AS total_sales
    FROM 
        products p
    JOIN 
        orders o ON p.product_id = o.product_id
    GROUP BY 
        p.product_id, p.name, p.category
)

分析：

• 连接操作：使用JOIN将products表和orders表连接，连接条件是p.product_id = o.product_id，即通过商品ID进行连接。
• 计算销售总量：使用SUM(o.quantity)计算每个商品的销售总量。
• 分组：使用GROUP BY按商品ID、名称和类别分组，以便计算每个商品的总销售量。

2. 分组和排名

目标：在每个类别中对商品按销售总量进行排名。

代码：

SELECT 
    product_name,
    total_sales,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY total_sales DESC, product_id ASC) AS category_rank
FROM 
    sales

分析：

• 窗口函数：使用ROW_NUMBER()窗口函数对每个类别中的商品进行排名。
• 分区：PARTITION BY category表示按商品类别分区。
• 排序：ORDER BY total_sales DESC, product_id ASC表示在每个类别中，首先按销售总量降序排列，如果销售总量相同，则按商品ID升序排列。

3. 格式化输出

目标：按类别和排名进行排序输出。

代码：

ORDER BY 
    category ASC,
    total_sales DESC,
    product_id ASC;

分析：

• 排序：ORDER BY category ASC表示按类别升序排序，total_sales DESC表示在每个类别中按销售总量降序排序，product_id ASC表示在销售总量相同的情况下按商品ID升序排序。

完整代码

WITH sales AS (
    SELECT 
        p.product_id,
        p.name AS product_name,
        p.category,
        SUM(o.quantity) AS total_sales
    FROM 
        products p
    JOIN 
        orders o ON p.product_id = o.product_id
    GROUP BY 
        p.product_id, p.name, p.category
)

SELECT 
    product_name,
    total_sales,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY total_sales DESC, product_id ASC) AS category_rank
FROM 
    sales
ORDER BY 
    category ASC,
    total_sales DESC,
    product_id ASC;