【Hive SQL 每日一题】环比增长率、环比增长率、复合增长率

本文涉及的产品
实时计算 Flink 版,5000CU*H 3个月
检索分析服务 Elasticsearch 版,2核4GB开发者规格 1个月
大数据开发治理平台 DataWorks,不限时长
简介: 该文介绍了环比增长率、同比增长率和复合增长率的概念及计算公式,并提供了SQL代码示例来计算商品的月度增长率。环比增长率是相邻两期数据的增长率,同比增长率是与去年同期相比的增长率,复合增长率则是连续时间段内平均增长的速率。文章还包含了一组销售数据用于演示如何运用这些增长率进行计算。

@[toc]

环比增长率

环比增长率是指两个相邻时段之间某种指标的增长率。通常来说,环比增长率是比较两个连续时间段内某项数据的增长量大小的百分比。

环比增长率反映了两个相邻时间段内某种经济指标的变化速度,被广泛用于企业、社会和国民经济等方面的经济分析中。

环比增长率的计算公式如下:

环比增长率 = (本期数值 - 上期数值)/ 上期数值 * 100%

其中:

  • 本期数值是指当前时间段内的指标数值;

  • 上期数值是指上一个时间段内的指标数值。

通过计算两者之间的差异,再以百分比的形式表示出来,就得到了环比增长率。

例如,如果某公司今年第一季度的销售额为 100 万人民币,第二季度的销售额为 120 万人民币,那么环比增长率可以按照以下步骤计算:

环比增长率 = (120 - 100) / 100 * 100% = 20%

这样就得到了该指标在本期相对于上期的增长率为 20%

同比增长率

同比增长率是指与去年同期相比的增长率。它用于比较同一时间段内的两个不同年份的数据变化情况,判断增长趋势和比较不同年度的表现。常用于分析经济、市场等领域的年度变化趋势。

同比增长率的计算公式如下:

同比增长率 = (本期数值 - 去年同期数值)/ 去年同期数值 * 100%

其中:

  • 本期数值是指当前时间段内的指标数值;

  • 去年同期数值是指上一个年度同一时间段内的指标数值。

通过计算两者之间的差异,再以百分比的形式表示出来,就得到了同比增长率。

举例来说,如果某项指标在今年第一季度120,而去年同期第一季度100,那么同比增长率可以按照以下步骤计算:

同比增长率 = (120 - 100) / 100 * 100% = 20%

这样就得到了该指标在今年第一季度相对于去年同期的增长率为 20%

复合增长率

复合增长率是指在一段连续的时间内,某项指标每个月或年平均增长的复合增长率。它用于衡量某指标在一段时间内(月均或年均)的整体增长速度。

复合增长率的计算公式如下:

复合增长率 = (最终值 / 初始值)^( 1 / n) - 1

其中:

  • 最终值是指期末的数值;
  • 初始值是指起始的数值;
  • n 时间段数量是指经过 n 个时间段的增长所到达的值。

举例来说,假设某项指标在起始时刻(一月份)为 100,经过 6 个月(到达七月份)的增长,最终值为 200,则可以按照以下步骤计算月均复合增长率:

月均复合增长率 = ( (200 / 100)^(1 / (7-1)) -1) * 100%

这样就得到了某指标经过 6 个月的整体增长率(复合增长率) 为12.25%

在计算月均或年均复合增长率时,需要使用连续的起始值和结束值来进行计算。假设有 n 个连续的月份数据,那么起始值到结束值的时间跨度为 n-1 个月,只计算后续的增长情况。

测试数据

数据来源:Github

-- 创建销量表sales_monthly
-- product表示产品名称,ym表示年月,amount表示销售金额(元)
CREATE TABLE sales_monthly(product string, ym string, amount decimal(10, 2));

-- 生成测试数据
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201801',10159.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201802',10211.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201803',10247.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201804',10376.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201805',10400.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201806',10565.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201807',10613.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201808',10696.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201809',10751.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201810',10842.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201811',10900.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201812',10972.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201901',11155.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201902',11202.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201903',11260.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201904',11341.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201905',11459.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('苹果','201906',11560.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201801',10138.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201802',10194.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201803',10328.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201804',10322.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201805',10481.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201806',10502.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201807',10589.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201808',10681.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201809',10798.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201810',10829.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201811',10913.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201812',11056.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201901',11161.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201902',11173.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201903',11288.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201904',11408.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201905',11469.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('香蕉','201906',11528.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201801',10154.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201802',10183.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201803',10245.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201804',10325.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201805',10465.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201806',10505.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201807',10578.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201808',10680.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201809',10788.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201810',10838.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201811',10942.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201812',10988.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201901',11099.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201902',11181.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201903',11302.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201904',11327.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201905',11423.00);
INSERT INTO sales_monthly (product,ym,amount) VALUES ('桔子','201906',11524.00);

需求说明

根据所给数据,分别计算下列三个指标:

  1. 计算各类商品的月环比增长率;

  2. 计算各类商品的月同比增长率;

  3. 计算各类商品的月均复合增长率。

需求实现

理解环比增长率、同比增长率、复合增长率的概念以及计算公式就能够比较容易的实现这个需求。

-- 月环比增长率
select
    product,
    ym,
    total_amount,
    lag_total_amount,
    if(
        lag_total_amount = 0,
        '0.00%',
        concat(cast((total_amount - lag_total_amount) / lag_total_amount * 100 as decimal (10,2)),'%')
    ) growth_rate
from
    (select
        product, ym, total_amount,
        lag(total_amount,1,0) over (partition by product order by ym) lag_total_amount
    from
        (select
            product,
            ym,
            sum(amount) total_amount
        from
            sales_monthly
        group by
            product,ym)t1 )t2;


-- 月同比增长率
select
    product,
    year,
    month,
    total_amount,
    lag_total_amount,
    if(
        lag_total_amount = 0,
        '0.00%',
        concat(cast( (total_amount - lag_total_amount) / lag_total_amount * 100 as decimal(10,2)),'%')
        )  year_growth
from
    (select
        product,
        substr(ym,1,4) year,
        substr(ym,-2) month,
        total_amount,
        lag(total_amount,1,0) over (partition by product,substr(ym,-2) order by substr(ym,1,4)) lag_total_amount
    from
        (select
            product,
            ym,
            sum(amount) total_amount
        from
            sales_monthly
        group by
            product, ym)t1 )t2
order by
    product,year,month;


-- 月均复合增长率
select
    product,
    ym,
    first_total_amount,
    total_amount,
    concat(cast(ifnull((pow(total_amount / first_total_amount, 1 / (rn-1) ) - 1)*100,0.00) as decimal(10,2)),'%') month_avg_compound_growth_rate
from
    (select
        product,
        ym,
        total_amount,
        first_value(total_amount) over (partition by product order by ym) first_total_amount,
        row_number() over (partition by product order by ym) rn
    from
        (select
            product,
            ym,
            sum(amount) total_amount
        from
            sales_monthly
        group by
            product, ym)t1 )t2;
相关文章
|
21天前
|
SQL HIVE
【Hive SQL 每日一题】统计用户连续下单的日期区间
该SQL代码用于统计用户连续下单的日期区间。首先按`user_id`和`order_date`分组并去除重复,然后使用`row_number()`标记行号,并通过`date_sub`与行号计算潜在的连续日期。接着按用户ID和计算后的日期分组,排除连续订单数少于2的情况,最后提取连续下单的起始和结束日期。输出结果展示了用户连续下单的日期范围。
|
21天前
|
SQL HIVE
【Hive SQL】字符串操作函数你真的会用吗?
本文介绍了SQL中判断字符串是否包含子串的几种方法。`IN`函数判断元素是否完全等于给定元素组中的某项,而非包含关系。`INSTR`和`LOCATE`函数返回子串在字符串中首次出现的位置,用于检测是否存在子串。`SUBSTR`则用于提取字符串的子串。`LIKE`用于模糊匹配,常与通配符配合使用。注意`IN`并非用于判断子串包含。
|
22天前
|
SQL 关系型数据库 HIVE
【Hive SQL 每日一题】统计最近1天/7天/30天商品的销量
这段内容是关于SQL查询的示例,目标是统计`sales`表中最近1天、7天和30天的商品销量和销售次数。表结构包含`id`、`product_id`、`quantity`和`sale_date`字段。初始查询方法通过三个独立的子查询完成,但效率较低。优化后的查询使用了`lateral view explode`将数据炸裂,通过一次查询同时获取所有所需时间段的数据,提高了效率。示例中展示了优化前后的SQL代码及结果对比。
|
22天前
|
SQL HIVE
【Hive SQL 每日一题】统计最近7天内连续下单3日的用户量
创建了一个名为`sales`的测试表,包含`user_id`、`product_id`、`quantity`和`sale_date`字段,插入了多条销售数据。需求是找出最近7天内连续下单3天的用户数量。SQL查询通过分组和窗口函数`row_number()`检查日期连续性,最终计算满足条件的唯一用户数。示例结果显示有3名用户符合条件。
|
22天前
|
SQL BI HIVE
【Hive SQL 每日一题】统计用户留存率
用户留存率是衡量产品成功的关键指标,表示用户在特定时间内持续使用产品的比例。计算公式为留存用户数除以初始用户数。例如,游戏发行后第一天有10000玩家,第七天剩5000人,第一周留存率为50%。提供的SQL代码展示了如何根据用户活动数据统计每天的留存率。需求包括计算系统上线后的每日留存率,以及从第一天开始的累计N日留存率。通过窗口函数`LAG`和`COUNT(DISTINCT user_id)`,可以有效地分析用户留存趋势。
|
22天前
|
SQL HIVE 索引
【Hive SQL 每日一题】行列转换
该文介绍了如何使用SQL进行数据的行列转换。首先展示了行转列的例子,通过创建一个学生成绩表,利用`IF`和`SUM`函数按学生ID分组,将每个学生的各科成绩转换为独立列。然后,文章讲述了列转行的需求,利用`LATERAL VIEW`和`POSEXPLODE`将已转换的表格恢复为原始行格式,通过索引匹配过滤笛卡尔积避免错误结果。此外,还提到了使用`UNION ALL`的另一种列转行方法。
|
22天前
|
SQL HIVE
【Hive SQL 每日一题】分组排名取值
创建了一个名为`sales_data`的测试表,包含商品ID、销售额和销售日期。展示了部分示例数据。接着,提供了三个SQL查询:1) 查找每个商品销售额最高的记录;2) 获取每个商品最近和最远的销售记录;3) 求每个商品距今第二近的销售记录。每个查询都利用了窗口函数来处理数据,并给出了相应的查询结果图。
|
22天前
|
SQL 数据挖掘 HIVE
【Hive SQL 每日一题】在线课程学生行为数据分析
该数据分析师任务是分析在线学习平台的学生行为,以优化课程内容和学习体验。提供的数据包括`students`表(含学生ID、姓名、年龄和性别)和`course_activity`表(含活动ID、学生ID、课程ID、活动日期和学习时长)。分析涉及:1) 学生参加的课程数量,2) 课程总学习时长,3) 按性别分组的平均学习时长,4) 学生首次参加的课程及日期,5) 学生最近一次学习的时长,以及6) 参与学生最多的课程。所有查询都使用了SQL,部分涉及窗口函数和分组统计。数据集可在给定链接下载。
|
6天前
|
SQL DataWorks NoSQL
DataWorks产品使用合集之如何将SQL Server中的数据转存到MongoDB
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
|
1月前
|
SQL API 流计算
实时计算 Flink版产品使用合集之在Mac M1下的Docker环境中开启SQL Server代理的操作步骤是什么
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
156 1

热门文章

最新文章