【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。

相关文章

MySQL 数据库表操作完全指南:创建、读取、更新与删除实战

MySQL 数据库表操作完全指南:创建、读取、更新与删除实战

《MySQL数据库表操作完全指南:创建、读取、更新与删除实战》本文系统讲解MySQL表的增删查改（CURD）操作,涵盖创建、更新、查询、删除及插入查询结果,也是贯穿各类项目开发全流程的基础数据交互原... 目录mysql系列前言一、Create(创建)并插入数据1.1 单行数据 + 全列插入1.2 多行数据
阅读更多...
MySQL中优化CPU使用的详细指南

MySQL中优化CPU使用的详细指南

《MySQL中优化CPU使用的详细指南》优化MySQL的CPU使用可以显著提高数据库的性能和响应时间,本文为大家整理了一些优化CPU使用的方法,大家可以根据需要进行选择... 目录一、优化查询和索引1.1 优化查询语句1.2 创建和优化索引1.3 避免全表扫描二、调整mysql配置参数2.1 调整线程数2.
阅读更多...
MySQL 临时表与复制表操作全流程案例

MySQL 临时表与复制表操作全流程案例

《MySQL临时表与复制表操作全流程案例》本文介绍MySQL临时表与复制表的区别与使用,涵盖生命周期、存储机制、操作限制、创建方法及常见问题,本文结合实例代码给大家介绍的非常详细,感兴趣的朋友跟随小... 目录一、mysql 临时表（一）核心特性拓展（二）操作全流程案例1. 复杂查询中的临时表应用2. 临时
阅读更多...
MySQL 数据库表与查询操作实战案例

MySQL 数据库表与查询操作实战案例

《MySQL数据库表与查询操作实战案例》本文将通过实际案例,详细介绍MySQL中数据库表的设计、数据插入以及常用的查询操作,帮助初学者快速上手,感兴趣的朋友跟随小编一起看看吧... 目录mysql 数据库表操作与查询实战案例项目一：产品相关数据库设计与创建一、数据库及表结构设计二、数据库与表的创建项目二：员
阅读更多...
MySQL实现多源复制的示例代码

MySQL实现多源复制的示例代码

《MySQL实现多源复制的示例代码》MySQL的多源复制允许一个从服务器从多个主服务器复制数据,这在需要将多个数据源汇聚到一个数据库实例时非常有用,下面就来详细的介绍一下,感兴趣的可以了解一下... 目录一、多源复制原理二、多源复制配置步骤2.1 主服务器配置Master1配置Master2配置2.2 从服
阅读更多...
MySQL 临时表创建与使用详细说明

MySQL 临时表创建与使用详细说明

《MySQL临时表创建与使用详细说明》MySQL临时表是存储在内存或磁盘的临时数据表,会话结束时自动销毁,适合存储中间计算结果或临时数据集,其名称以#开头（如#TempTable）,本文给大家介绍M... 目录mysql 临时表详细说明1.定义2.核心特性3.创建与使用4.典型应用场景5.生命周期管理6.注
阅读更多...
MySQL磁盘空间不足问题解决

MySQL磁盘空间不足问题解决

《MySQL磁盘空间不足问题解决》本文介绍查看空间使用情况的方式,以及各种空间问题的原因和解决方案,文中通过示例代码介绍的非常详细,需要的朋友们下面随着小编来一起学习学习吧... 目录查看空间使用情况Binlog日志文件占用过多表上的索引太多导致空间不足大字段导致空间不足表空间碎片太多导致空间不足临时表空间
阅读更多...
MySQL进行分片合并的实现步骤

MySQL进行分片合并的实现步骤

《MySQL进行分片合并的实现步骤》分片合并是指在分布式数据库系统中,将不同分片上的查询结果进行整合,以获得完整的查询结果,下面就来具体介绍一下,感兴趣的可以了解一下... 目录环境准备项目依赖数据源配置分片上下文分片查询和合并代码实现1. 查询单条记录2. 跨分片查询和合并测试结论分片合并（Shardin
阅读更多...
MySQL配置多主复制的实现步骤

MySQL配置多主复制的实现步骤

《MySQL配置多主复制的实现步骤》多主复制是一种允许多个MySQL服务器同时接受写操作的复制方式,本文就来介绍一下MySQL配置多主复制的实现步骤,具有一定的参考价值,感兴趣的可以了解一下... 目录1. 环境准备2. 配置每台服务器2.1 修改每台服务器的配置文件3. 安装和配置插件4. 启动组复制4.
阅读更多...
MySQL数据脱敏的实现方法

MySQL数据脱敏的实现方法

《MySQL数据脱敏的实现方法》本文主要介绍了MySQL数据脱敏的实现方法,包括字符替换、加密等方法,通过工具类和数据库服务整合,确保敏感信息在查询结果中被掩码处理,感兴趣的可以了解一下... 目录一. 数据脱敏的方法二. 字符替换脱敏1. 创建数据脱敏工具类三. 整合到数据库操作1. 创建服务类进行数据库
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2