【Hive SQL 每日一题】统计各个商品今年销售额与去年销售额的增长率及排名变化

测试数据

-- 创建商品表
DROP TABLE IF EXISTS products;
CREATE TABLE products (
    product_id INT,
    product_name STRING
);

INSERT INTO products VALUES
(1, 'Product A'),
(2, 'Product B'),
(3, 'Product C'),
(4, 'Product D'),
(5, 'Product E'),
(6, 'Product F'),
(7, 'Product G'),
(8, 'Product H'),
(9, 'Product I'),
(10, 'Product J'),
(11, 'Product K');

-- 创建销售表
DROP TABLE IF EXISTS sales;
CREATE TABLE sales (
    sale_id INT,
    product_id INT,
    sale_date STRING,
    amount DOUBLE
);

INSERT INTO sales VALUES
(101, 1, '2023-01-01', 100.0),
(102, 1, '2023-02-01', 150.0),
(103, 2, '2023-03-01', 200.0),
(104, 3, '2023-04-01', 50.0),
(105, 4, '2023-05-01', 300.0),
(106, 5, '2023-06-01', 250.0),
(107, 1, '2024-01-01', 120.0),
(108, 1, '2024-02-01', 180.0),
(109, 2, '2024-03-01', 220.0),
(110, 3, '2024-04-01', 70.0),
(111, 4, '2024-05-01', 330.0),
(112, 5, '2024-06-01', 270.0),
(113, 2, '2023-07-01', 180.0),
(114, 3, '2023-08-01', 60.0),
(115, 4, '2023-09-01', 310.0),
(116, 5, '2023-10-01', 260.0),
(117, 1, '2023-11-01', 190.0),
(118, 2, '2023-12-01', 210.0),
(119, 3, '2024-01-01', 75.0),
(120, 4, '2024-02-01', 340.0),
(121, 5, '2024-03-01', 280.0),
(122, 6, '2023-01-01', 130.0),
(123, 6, '2023-02-01', 160.0),
(124, 7, '2023-03-01', 190.0),
(125, 8, '2023-04-01', 220.0),
(126, 9, '2023-05-01', 250.0),
(127, 10, '2023-06-01', 280.0),
(128, 6, '2024-01-01', 140.0),
(129, 6, '2024-02-01', 170.0),
(130, 7, '2024-03-01', 200.0),
(131, 8, '2024-04-01', 230.0),
(132, 9, '2024-05-01', 260.0),
(133, 10, '2024-06-01', 290.0),
(134, 7, '2023-07-01', 175.0),
(135, 8, '2023-08-01', 205.0),
(136, 9, '2023-09-01', 235.0),
(137, 10, '2023-10-01', 265.0),
(138, 6, '2023-11-01', 145.0),
(139, 7, '2023-12-01', 175.0),
(140, 8, '2024-01-01', 215.0),
(141, 9, '2024-02-01', 245.0),
(142, 10, '2024-03-01', 275.0),
(143, 6, '2024-04-01', 155.0),
(144, 7, '2024-05-01', 185.0),
(145, 8, '2024-06-01', 225.0),
(147, 11, '2023-06-09', 0.0),
(146, 11, '2024-06-01', 233.0);

需求说明

统计各个商品今年销售额与去年销售额的增长率及销售额的排名变化。

增长率计算公式：（当期份额-上期份额）/ 上期份额 * 100%

结果示例：

其中：

• product_name 表示商品名称；
• total_amount_2023 表示商品在 2023 年度的销售额；
• total_amount_2024 表示商品在 2024 年度的销售额；
• growth_rate 表示商品的增长率；
• rk_2023 表示商品在 2023 年度中的销售额排名；
• rk_2024 表示商品在 2024 年度中的销售额排名；
• rk_diff 表示该商品年度销售额排名的变化。

注意，在这里商品销售额可能存在两种情况：

1. 假设某商品 2023 年销售 0.0，而在 2024 年销售 50，那么这种情况下，销售额增长率统一设置为 100.0%；
2. 如果在两个年度销售均为 0.0，那么销售额增长率设置为 0.0%。

需求实现

SELECT
    p.product_name,
    total_amount_2023,
    total_amount_2024,
    CASE WHEN total_amount_2024=0 AND total_amount_2023=0
        THEN "0.0%"
        WHEN total_amount_2023=0
        THEN "100.0%"
        ELSE
            CONCAT(CAST((total_amount_2024 - total_amount_2023) / total_amount_2023 as DECIMAL(5,3)) * 100,"%")
    END growth_rate,
    rk_2023,
    rk_2024,
    rk_2024 - rk_2023 rk_diff
FROM
    (SELECT
        product_id,
        total_amount_2023,
        total_amount_2024,
        RANK() OVER(ORDER BY total_amount_2023 DESC) rk_2023,
        RANK() OVER(ORDER BY total_amount_2024 DESC) rk_2024
    FROM
        (SELECT
            product_id,
            SUM(IF(year(sale_date)="2023",amount,0)) total_amount_2023,
            SUM(IF(year(sale_date)="2024",amount,0)) total_amount_2024
        FROM
            sales
        WHERE
            year(sale_date) IN ("2023",