【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 BETWEEN 语句的基本用法详解

SQL BETWEEN 语句的基本用法详解

《SQLBETWEEN语句的基本用法详解》SQLBETWEEN语句是一个用于在SQL查询中指定查询条件的重要工具,它允许用户指定一个范围,用于筛选符合特定条件的记录,本文将详细介绍BETWEEN语... 目录概述BETWEEN 语句的基本用法BETWEEN 语句的示例示例 1：查询年龄在 20 到 30 岁
阅读更多...
MySQL DQL从入门到精通

MySQL DQL从入门到精通

《MySQLDQL从入门到精通》通过DQL,我们可以从数据库中检索出所需的数据,进行各种复杂的数据分析和处理,本文将深入探讨MySQLDQL的各个方面,帮助你全面掌握这一重要技能,感兴趣的朋友跟随小... 目录一、DQL 基础：SELECT 语句入门二、数据过滤：WHERE 子句的使用三、结果排序：ORDE
阅读更多...
MySQL MCP 服务器安装配置最佳实践

MySQL MCP 服务器安装配置最佳实践

《MySQLMCP服务器安装配置最佳实践》本文介绍MySQLMCP服务器的安装配置方法,本文结合实例代码给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友参考下... 目录mysql MCP 服务器安装配置指南简介功能特点安装方法数据库配置使用MCP Inspector进行调试开发指
阅读更多...
mysql中insert into的基本用法和一些示例

mysql中insert into的基本用法和一些示例

《mysql中insertinto的基本用法和一些示例》INSERTINTO用于向MySQL表插入新行,支持单行/多行及部分列插入,下面给大家介绍mysql中insertinto的基本用法和一些示例... 目录基本语法插入单行数据插入多行数据插入部分列的数据插入默认值注意事项在mysql中，INSERT I
阅读更多...
一文详解MySQL如何设置自动备份任务

一文详解MySQL如何设置自动备份任务

《一文详解MySQL如何设置自动备份任务》设置自动备份任务可以确保你的数据库定期备份,防止数据丢失,下面我们就来详细介绍一下如何使用Bash脚本和Cron任务在Linux系统上设置MySQL数据库的自... 目录1. 编写备份脚本1.1 创建并编辑备份脚本1.2 给予脚本执行权限2. 设置 Cron 任务2
阅读更多...
SQL Server修改数据库名及物理数据文件名操作步骤

SQL Server修改数据库名及物理数据文件名操作步骤

《SQLServer修改数据库名及物理数据文件名操作步骤》在SQLServer中重命名数据库是一个常见的操作,但需要确保用户具有足够的权限来执行此操作,：本文主要介绍SQLServer修改数据... 目录一、背景介绍二、操作步骤2.1 设置为单用户模式（断开连接）2.2 修改数据库名称2.3 查找逻辑文件名
阅读更多...
SQL Server数据库死锁处理超详细攻略

SQL Server数据库死锁处理超详细攻略

《SQLServer数据库死锁处理超详细攻略》SQLServer作为主流数据库管理系统,在高并发场景下可能面临死锁问题,影响系统性能和稳定性,这篇文章主要给大家介绍了关于SQLServer数据库死... 目录一、引言二、查询 Sqlserver 中造成死锁的 SPID三、用内置函数查询执行信息1. sp_w
阅读更多...
canal实现mysql数据同步的详细过程

canal实现mysql数据同步的详细过程

《canal实现mysql数据同步的详细过程》：本文主要介绍canal实现mysql数据同步的详细过程,本文通过实例图文相结合给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的... 目录1、canal下载2、mysql同步用户创建和授权3、canal admin安装和启动4、canal
阅读更多...
SQL中JOIN操作的条件使用总结与实践

SQL中JOIN操作的条件使用总结与实践

《SQL中JOIN操作的条件使用总结与实践》在SQL查询中,JOIN操作是多表关联的核心工具,本文将从原理,场景和最佳实践三个方面总结JOIN条件的使用规则,希望可以帮助开发者精准控制查询逻辑... 目录一、ON与WHERE的本质区别二、场景化条件使用规则三、最佳实践建议1.优先使用ON条件2.WHERE用
阅读更多...
MySQL存储过程之循环遍历查询的结果集详解

MySQL存储过程之循环遍历查询的结果集详解

《MySQL存储过程之循环遍历查询的结果集详解》：本文主要介绍MySQL存储过程之循环遍历查询的结果集,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录前言1. 表结构2. 存储过程3. 关于存储过程的SQL补充总结前言近来碰到这样一个问题：在生产上导入的数据发现
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2