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

2024-06-03 20:04
文章标签 sql 变化 统计 每日 商品 排名 hive database 今年 去年 销售额 增长率

本文主要是介绍【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_nametotal_amount_2023total_amount_2024growth_raterk_2023rk_2024rk_diff
Product D610.0670.09.8%110
Product H425.0670.057.6%918
Product J545.0565.03.7%330
Product E510.0550.07.8%541
Product I485.0505.04.1%651
…………………

其中：

  • 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%。

需求实现

SELECTp.product_name,total_amount_2023,total_amount_2024,CASE WHEN total_amount_2024=0 AND total_amount_2023=0THEN "0.0%"WHEN total_amount_2023=0THEN "100.0%"ELSECONCAT(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(SELECTproduct_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_2024FROM(SELECTproduct_id,SUM(IF(year(sale_date)="2023",amount,0)) total_amount_2023,SUM(IF(year(sale_date)="2024",amount,0)) total_amount_2024FROMsalesWHEREyear(sale_date) IN ("2023","2024")GROUP BYproduct_id)t1 )t2
JOINproducts p
ONt2.product_id = p.product_id;

输出结果如下：

在这里插入图片描述

分步解析

（1）获取去年与今年两个年度的数据，并进行聚合统计。

SELECTproduct_id,SUM(IF(year(sale_date)="2023",amount,0)) total_amount_2023,SUM(IF(year(sale_date)="2024",amount,0)) total_amount_2024
FROMsales
WHEREyear(sale_date) IN ("2023","2024")
GROUP BYproduct_id;

在这里插入图片描述

（2）根据（1）中的结果，通过窗口函数排序，获取分别获取两个年度的销售额排名。

SELECTproduct_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(SELECTproduct_id,SUM(IF(year(sale_date)="2023",amount,0)) total_amount_2023,SUM(IF(year(sale_date)="2024",amount,0)) total_amount_2024FROMsalesWHEREyear(sale_date) IN ("2023","2024")GROUP BYproduct_id)t1;

在这里插入图片描述

（3）根据（2）中的结果，判断并计算两个年度的增长率以及排名变化，最终通过 join 连接商品表，获取商品名称。

SELECTp.product_name,total_amount_2023,total_amount_2024,CASE WHEN total_amount_2024=0 AND total_amount_2023=0THEN "0.0%"WHEN total_amount_2023=0THEN "100.0%"ELSECONCAT(CAST((total_amount_2024 - total_amount_2023) / total_amount_2023 as DECIMAL(5,3)) * 100,"%")END growth_rate,rk_2023,rk_2024,rk_2023 - rk_2024 rk_diff
FROM(SELECTproduct_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_2024FROM(SELECTproduct_id,SUM(IF(year(sale_date)="2023",amount,0)) total_amount_2023,SUM(IF(year(sale_date)="2024",amount,0)) total_amount_2024FROMsalesWHEREyear(sale_date) IN ("2023","2024")GROUP BYproduct_id)t1 )t2
JOINproducts p
ONt2.product_id = p.product_id;

在这里插入图片描述

可能对于排名那里存在疑惑，为什么是 rk_2023 - rk_2024，不是 rk_2024 - rk_2023 呢？

惯性思维导致，在排序中，并不是排名越高值越大，相反，因为我们的排名越靠前（越高），其排名值越小，想到这里，就应该明白了。

这篇关于【Hive SQL 每日一题】统计各个商品今年销售额与去年销售额的增长率及排名变化的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/1027974。

相关文章

慢sql提前分析预警和动态sql替换-Mybatis-SQL

慢sql提前分析预警和动态sql替换-Mybatis-SQL

《慢sql提前分析预警和动态sql替换-Mybatis-SQL》为防止慢SQL问题而开发的MyBatis组件,该组件能够在开发、测试阶段自动分析SQL语句,并在出现慢SQL问题时通过Ducc配置实现动... 目录背景解决思路开源方案调研设计方案详细设计使用方法1、引入依赖jar包2、配置组件XML3、核心配
阅读更多...
MySQL数据库约束深入详解

MySQL数据库约束深入详解

《MySQL数据库约束深入详解》：本文主要介绍MySQL数据库约束,在MySQL数据库中,约束是用来限制进入表中的数据类型的一种技术,通过使用约束,可以确保数据的准确性、完整性和可靠性,需要的朋友... 目录一、数据库约束的概念二、约束类型三、NOT NULL 非空约束四、DEFAULT 默认值约束五、UN
阅读更多...
Pandas中统计汇总可视化函数plot()的使用

Pandas中统计汇总可视化函数plot()的使用

《Pandas中统计汇总可视化函数plot()的使用》Pandas提供了许多强大的数据处理和分析功能,其中plot()函数就是其可视化功能的一个重要组成部分,本文主要介绍了Pandas中统计汇总可视化... 目录一、plot()函数简介二、plot()函数的基本用法三、plot()函数的参数详解四、使用pl
阅读更多...
MySQL 多表连接操作方法(INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL OUTER JOIN)

MySQL 多表连接操作方法(INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL OUTER JOIN)

《MySQL多表连接操作方法(INNERJOIN、LEFTJOIN、RIGHTJOIN、FULLOUTERJOIN)》多表连接是一种将两个或多个表中的数据组合在一起的SQL操作,通过连接,... 目录一、 什么是多表连接？二、 mysql 支持的连接类型三、 多表连接的语法四、实战示例 数据准备五、连接的性
阅读更多...
MySQL中的分组和多表连接详解

MySQL中的分组和多表连接详解

《MySQL中的分组和多表连接详解》：本文主要介绍MySQL中的分组和多表连接的相关操作,本文通过实例代码给大家介绍的非常详细,感兴趣的朋友一起看看吧... 目录mysql中的分组和多表连接一、MySQL的分组（group javascriptby ）二、多表连接（表连接会产生大量的数据垃圾）MySQL中的
阅读更多...
MySQL 中的 JSON 查询案例详解

MySQL 中的 JSON 查询案例详解

《MySQL中的JSON查询案例详解》：本文主要介绍MySQL的JSON查询的相关知识,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考下吧... 目录mysql 的 jsON 路径格式基本结构路径组件详解特殊语法元素实际示例简单路径复杂路径简写操作符注意MySQL 的 J
阅读更多...
Pandas统计每行数据中的空值的方法示例

Pandas统计每行数据中的空值的方法示例

《Pandas统计每行数据中的空值的方法示例》处理缺失数据（NaN值）是一个非常常见的问题,本文主要介绍了Pandas统计每行数据中的空值的方法示例,具有一定的参考价值,感兴趣的可以了解一下... 目录什么是空值？为什么要统计空值？准备工作创建示例数据统计每行空值数量进一步分析www.chinasem.cn处
阅读更多...
Windows 上如果忘记了 MySQL 密码 重置密码的两种方法

Windows 上如果忘记了 MySQL 密码 重置密码的两种方法

《Windows上如果忘记了MySQL密码重置密码的两种方法》：本文主要介绍Windows上如果忘记了MySQL密码重置密码的两种方法,本文通过两种方法结合实例代码给大家介绍的非常详细,感... 目录方法 1：以跳过权限验证模式启动 mysql 并重置密码方法 2：使用 my.ini 文件的临时配置在 Wi
阅读更多...
MySQL重复数据处理的七种高效方法

MySQL重复数据处理的七种高效方法

《MySQL重复数据处理的七种高效方法》你是不是也曾遇到过这样的烦恼：明明系统测试时一切正常,上线后却频频出现重复数据,大批量导数据时,总有那么几条不听话的记录导致整个事务莫名回滚,今天,我就跟大家分... 目录1. 重复数据插入问题分析1.1 问题本质1.2 常见场景图2. 基础解决方案：使用异常捕获3.
阅读更多...
SQL中redo log 刷⼊磁盘的常见方法

SQL中redo log 刷⼊磁盘的常见方法

《SQL中redolog刷⼊磁盘的常见方法》本文主要介绍了SQL中redolog刷⼊磁盘的常见方法,将redolog刷入磁盘的方法确保了数据的持久性和一致性,下面就来具体介绍一下,感兴趣的可以了解... 目录Redo Log 刷入磁盘的方法Redo Log 刷入磁盘的过程代码示例（伪代码）在数据库系统中，r
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2