HIVE关于环比数据实现的方法

HIVE关于环比数据实现同期比值的方法

本篇介绍同期环比与月份环比之间比较的SQL写法
场景1：同期环比
最近在解决业务需求时，遇到了hiveSQL里面关于同期数据的环比计算。
业务场景为：
按月份计算数据及其对应的环比数据，月份数据展示为最后一天数据信息
如当前一月则一月当日与十二月同期比较，十二月月末与十一月月末比较
环比计算公式为：
当前时间计算：
(当前时间数据-上月同期数据)/上月同期数据
历史时间计算：
(上月月末数据-上上月末数据)/上上月末数据
数据存放为：
表A存放每日数据(每日为当月累计数据，即2号为1号加2号数据)，月份数据展示为最后一天数据信息。
字段：时间(data_time),数据量cc

data_time	cc
2021-01-12	80
2021-01-11	60
…	…
2020-12-31	60
…	…
2020-12-12	40
…	…
2020-11-30	20

应展示为：

实现思路
1.考虑临时表取出当月数据与上月同期数据
将规律数据与不规律数据分而治之，当月与历史月分开处理
关键函数：上月同期使用函数add_months获取，最后一天数据使用last_day获取
例1：

2.考虑将数据全部排序后取排序数据
使用hive的自带函数lead结合开窗函数取出数据与上月对应数据
关键函数：上月同期使用函数add_months获取，最后一天数据使用last_day获取，lead往下行数获取数据，使用over开窗分组排序
数据格式取值需求为：

data_time	cc
2021-01-12	80
2020-12-12	40
2020-12-31	60
2020-11-30	20

难点在于：
1.获取数据上月同期数据，将数据规整后直接取出上月环比值；
2.数据排序后月数据2020-12-31会出现在2020-12-12前，如果通过2021-01-12获取前行的数据为2020-12-31
3.如何实现排序可以通过2021-01-12下行为2020-12-12，我采取的方式为数据双分组
例2：
步骤1：
求出
当月-当前日期数据与上月同期数据
历史月-最后一天数据

数据为：

data_time	cc
2021-01-12	80
2020-12-31	60
2020-12-12	40
2020-11-30	20

步骤2：
通过over开窗函数，两次排序实现年月desc，同年月asc排序，并根据lead当前行求出往下一行数据

data_time	cc
2021-01-12	80
2020-12-12	40
2020-12-31	60
2020-11-30	20

步骤3：
过滤掉2020-12-12数据，直接求出环比数据

场景2：每月环比
1.自关联表
with tmp1 as (
select
substr(data_time,1,7) data_time,
max(cc) cc
from A
group by substr(data_time,1,7)
)
select
t1.data_time,
t1.cc,
(t1.cc-t2.cc)/t2.cc
from
tmp1 t1 join tmp2 t2
on t1.data_time=substr(add_months(concat(t2.data_time,’-01’),1),1,7)
2.临时表加1月
with tmp1 as (
select
substr(data_time,1,7) data_time,
max(cc) cc
from A
group by substr(data_time,1,7)
),
tmp2 as (–使用加一个月记录上月数据，关联时月份相同即可
select
substr(add_months(concat(t2.data_time,’-01’),1),1,7),
cc
from tmp1
)
select
t1.data_time,
t1.cc,
(t1.cc-t2.cc)/t2.cc
from
tmp1 t1 join tmp2 t2
on t1.data_time=t2.data_time;