【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开发中,字符串与数值的转换是高频操作,本文从隐式转换原理、显式转换方法、典型场景案例、风险防控四个维度系统梳理,助您精准掌握这一核心技能,需要的朋友可... 目录一、隐式转换：自动但需警惕的&ld编程quo;双刃剑”二、显式转换：三大核心方法详解三、典型场景
阅读更多...
MySQL中between and的基本用法、范围查询示例详解

MySQL中between and的基本用法、范围查询示例详解

《MySQL中betweenand的基本用法、范围查询示例详解》BETWEENAND操作符在MySQL中用于选择在两个值之间的数据,包括边界值,它支持数值和日期类型,示例展示了如何使用BETWEEN... 目录一、between and语法二、使用示例2.1、betwphpeen and数值查询2.2、be
阅读更多...
MySQL快速复制一张表的四种核心方法(包括表结构和数据)

MySQL快速复制一张表的四种核心方法(包括表结构和数据)

《MySQL快速复制一张表的四种核心方法(包括表结构和数据)》本文详细介绍了四种复制MySQL表（结构+数据）的方法,并对每种方法进行了对比分析,适用于不同场景和数据量的复制需求,特别是针对超大表（1... 目录一、mysql 复制表（结构+数据）的 4 种核心方法（面试结构化回答）方法 1：CREATE
阅读更多...
SQL Server中行转列方法详细讲解

SQL Server中行转列方法详细讲解

《SQLServer中行转列方法详细讲解》SQL行转列、列转行可以帮助我们更方便地处理数据,生成需要的报表和结果集,：本文主要介绍SQLServer中行转列方法的相关资料,需要的朋友可以参考下... 目录前言一、为什么需要行转列二、行转列的基本概念三、使用PIVOT运算符进行行转列1.创建示例数据表并插入数
阅读更多...
MySQL MHA集群详解(数据库高可用)

MySQL MHA集群详解(数据库高可用)

《MySQLMHA集群详解(数据库高可用)》MHA（MasterHighAvailability）是开源MySQL高可用管理工具,用于自动故障检测与转移,支持异步或半同步复制的MySQL主从架构,本... 目录mysql 高可用方案：MHA 详解与实战1. MHA 简介2. MHA 的组件组成（1）MHA
阅读更多...
SQL 注入攻击(SQL Injection)原理、利用方式与防御策略深度解析

SQL 注入攻击(SQL Injection)原理、利用方式与防御策略深度解析

《SQL注入攻击(SQLInjection)原理、利用方式与防御策略深度解析》本文将从SQL注入的基本原理、攻击方式、常见利用手法,到企业级防御方案进行全面讲解,以帮助开发者和安全人员更系统地理解... 目录一、前言二、SQL 注入攻击的基本概念三、SQL 注入常见类型分析1. 基于错误回显的注入（Erro
阅读更多...
MySQL基本表查询操作汇总之单表查询+多表操作大全

MySQL基本表查询操作汇总之单表查询+多表操作大全

《MySQL基本表查询操作汇总之单表查询+多表操作大全》本文全面介绍了MySQL单表查询与多表操作的关键技术,包括基本语法、高级查询、表别名使用、多表连接及子查询等,并提供了丰富的实例,感兴趣的朋友跟... 目录一、单表查询整合（一）通用模版展示（二）举例说明（三）注意事项（四）Mapper简单举例简单查询
阅读更多...
MySQL中的DELETE删除数据及注意事项

MySQL中的DELETE删除数据及注意事项

《MySQL中的DELETE删除数据及注意事项》MySQL的DELETE语句是数据库操作中不可或缺的一部分,通过合理使用索引、批量删除、避免全表删除、使用TRUNCATE、使用ORDERBY和LIMI... 目录1. 基本语法单表删除2. 高级用法使用子查询删除删除多表3. 性能优化策略使用索引批量删除避免
阅读更多...
MySQL 数据库进阶之SQL 数据操作与子查询操作大全

MySQL 数据库进阶之SQL 数据操作与子查询操作大全

《MySQL数据库进阶之SQL数据操作与子查询操作大全》本文详细介绍了SQL中的子查询、数据添加（INSERT）、数据修改（UPDATE）和数据删除（DELETE、TRUNCATE、DROP）操作... 目录一、子查询：嵌套在查询中的查询1.1 子查询的基本语法1.2 子查询的实战示例二、数据添加：INSE
阅读更多...
在SpringBoot+MyBatis项目中实现MySQL读写分离的实战指南

在SpringBoot+MyBatis项目中实现MySQL读写分离的实战指南

《在SpringBoot+MyBatis项目中实现MySQL读写分离的实战指南》在SpringBoot和MyBatis项目中实现MySQL读写分离,主要有两种思路：一种是在应用层通过代码和配置手动控制... 目录如何选择实现方案核心实现：应用层手动分离实施中的关键问题与解决方案总结在Spring Boot和
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2