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

2024-06-03 20:04
文章标签 销售额 hive sql 增长率 排名 每日 统计 去年 商品 变化 今年 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索引四（组合索引）

单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引；组合索引，即一个索引包含多个列。 因为有事，下面内容全部转自：https://www.cnblogs.com/farmer-cabbage/p/5793589.html 为了形象地对比单列索引和组合索引，为表添加多个字段： 　　 CREATE TABLE mytable( ID INT NOT NULL, use
阅读更多...
mysql索引三（全文索引）

mysql索引三（全文索引）

前面分别介绍了mysql索引一（普通索引）、mysql索引二（唯一索引）。 本文学习mysql全文索引。 全文索引（也称全文检索）是目前搜索引擎使用的一种关键技术。它能够利用【分词技术】等多种算法智能分析出文本文字中关键词的频率和重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。 在MySql中，创建全文索引相对比较简单。例如：我们有一个文章表（article），其中有主键ID（
阅读更多...
mysql索引二（唯一索引）

mysql索引二（唯一索引）

前文中介绍了MySQL中普通索引用法，和没有索引的区别。mysql索引一（普通索引） 下面学习一下唯一索引。 创建唯一索引的目的不是为了提高访问速度，而只是为了避免数据出现重复。唯一索引可以有多个但索引列的值必须唯一，索引列的值允许有空值。如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该使用关键字UNIQUE，把它定义为一个唯一索引。 添加数据库唯一索引的几种
阅读更多...
mysql索引一（普通索引）

mysql索引一（普通索引）

mysql的索引分为两大类，聚簇索引、非聚簇索引。聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引则不同。聚簇索引能够提高多行检索的速度、非聚簇索引则对单行检索的速度很快。         在这两大类的索引类型下，还可以降索引分为4个小类型：         1，普通索引：最基本的索引，没有任何限制，是我们经常使用到的索引。         2，唯一索引：与普通索引
阅读更多...
【服务器运维】MySQL数据存储至数据盘

【服务器运维】MySQL数据存储至数据盘

查看磁盘及分区 [root@MySQL tmp]# fdisk -lDisk /dev/sda: 21.5 GB, 21474836480 bytes255 heads, 63 sectors/track, 2610 cylindersUnits = cylinders of 16065 * 512 = 8225280 bytesSector size (logical/physical)
阅读更多...
据阿谱尔APO Research调研显示，2023年全球髓内钉市场销售额约为4.7亿美元

据阿谱尔APO Research调研显示，2023年全球髓内钉市场销售额约为4.7亿美元

根据阿谱尔 (APO Research）的统计及预测，2023年全球髓内钉市场销售额约为4.7亿美元，预计在2024-2030年预测期内将以超过3.82%的CAGR（年复合增长率）增长。 髓内钉市场是指涉及髓内钉制造、分销和销售的行业。髓内钉是一种用于整形外科手术的医疗器械，用于稳定长骨骨折，特别是股骨、胫骨和肱骨。髓内钉通常由不銹钢或钛等材料制成，并插入骨的髓管中，以在愈合过程中提供结构支
阅读更多...
SQL Server中，查询数据库中有多少个表，以及数据库其余类型数据统计查询

SQL Server中，查询数据库中有多少个表，以及数据库其余类型数据统计查询

sqlserver查询数据库中有多少个表 sql server 数表:select count(1) from sysobjects where xtype='U'数视图:select count(1) from sysobjects where xtype='V'数存储过程select count(1) from sysobjects where xtype='P' SE
阅读更多...
SQL Server中，always on服务器的相关操作

SQL Server中，always on服务器的相关操作

在SQL Server中，建立了always on服务，可用于数据库的同步备份，当数据库出现问题后，always on服务会自动切换主从服务器。 例如192.168.1.10为主服务器，12为从服务器，当主服务器出现问题后，always on自动将主服务器切换为12，保证数据库正常访问。 对于always on服务器有如下操作： 1、切换主从服务器：假如需要手动切换主从服务器时（如果两个服务
阅读更多...
SQL Server中，isnull（）函数以及null的用法

SQL Server中，isnull（）函数以及null的用法

SQL Serve中的isnull()函数：          isnull(value1,value2)         1、value1与value2的数据类型必须一致。         2、如果value1的值不为null，结果返回value1。         3、如果value1为null，结果返回vaule2的值。vaule2是你设定的值。        如
阅读更多...
SQL Server中，添加数据库到AlwaysOn高可用性组条件

SQL Server中，添加数据库到AlwaysOn高可用性组条件

1、将数据添加到AlwaysOn高可用性组，需要满足以下条件： 2、更多具体AlwaysOn设置，参考：https://msdn.microsoft.com/zh-cn/library/windows/apps/ff878487(v=sql.120).aspx 注：上述资源来自MSDN。
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2