【数据分析面试】31. 产品销售额排名（SQL窗口函数）

题目：

假设你有一个名为 sales 的表，其中包含了销售记录，字段包括 product_id（产品ID）、sales_date（销售日期）和 amount（销售额）。

请编写一条SQL查询，求每个销售日期内的产品销售额的排名，结果按照销售日期顺序排列。

示例

输入：
sales表

product_id	sales_date	amount	region
1	2024-04-01	100	East
2	2024-04-01	200	West
1	2024-04-02	150	North
2	2024-04-02	180	South
3	2024-04-01	300	East
3	2024-04-02	250	West

输出：

product_id	sales_date	amount	sales_rank
3	2024/4/1	300	1
2	2024/4/1	200	2
1	2024/4/1	100	3
3	2024/4/2	250	1
2	2024/4/2	180	2
1	2024/4/2	150	3

答案

SELECT 
    product_id,
    sales_date,
    amount,
    RANK() OVER (PARTITION BY sales_date ORDER BY amount DESC) AS sales_rank
FROM 
    sales
ORDER BY sales_date

• RANK() 是一个窗口函数，它根据指定的排序条件为分组后的计算结果排名。
• PARTITION BY sales_date 指定了窗口函数的分区方式，即按照销售日期分组。
• ORDER BY amount DESC 指定了排序方式，按销售额降序排列。
• ORDER BY sales_date 最后补上题目要求，按照日期排序

补充：表格生成代码：

-- 创建 sales 表
CREATE TABLE sales (
    product_id INT,
    sales_date DATE,
    amount DECIMAL(10, 2),
    region VARCHAR(50)
);

-- 插入数据
INSERT INTO sales (product_id, sales_date, amount, region) VALUES
    (1, '2024-04-01', 100.00, 'East'),
    (2, '2024-04-01', 200.00, 'West'),
    (1, '2024-04-02', 150.00, 'North'),
    (2, '2024-04-02', 180.00, 'South'),
    (3, '2024-04-01', 300.00, 'East'),
    (3, '2024-04-02', 250.00, 'West');

更多练习

1. 在原数据表格中增加一列——每个销售日期的销售额总和total_sales_amount。（提示：SUM()）
2. 增加一列每个地区的平均日销售额avg_sales_amount，并显示结果。（提示：使用AVG()）
3. 为每个销售记录分配一个唯一的序号sales_record_id，并按照销售日期降序排列。（提示：使用ROW_NUMBER()）
4. 为每个销售地区的销售额进行排名。该使用RANK() 还是 DENSE_RANK()？二者有什么不同？

更多详细答案可关注公众号查阅。