Hive实现同比环比计算
同比环比的计算
测试数据
1,2020-04-20,420
2,2020-04-04,800
3,2020-03-28,500
4,2020-03-13,100
5,2020-02-27,300
6,2020-01-07,450
7,2019-04-07,800
8,2019-03-15,1200
9,2019-02-17,200
10,2019-02-07,600
11,2019-01-13,300
CREATE TABLE ods_saleorder (
order_id int ,
order_time date ,
order_num int
)ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',';
销售量的月年占比
关联实现
select
t1.m_num,
t1.cmonth,
t2.y_num,
t2.cyear,
round( m_num / y_num, 2 ) AS ratio
from(
select
sum(order_num) as m_num,
DATE_FORMAT(order_time,'yyyy-MM') as cmonth
from
ods_saleorder
group by
DATE_FORMAT(order_time,'yyyy-MM')
) as t1
inner join
(
select
sum(order_num) as y_num,
DATE_FORMAT(order_time,'yyyy') as cyear
from
ods_saleorder
group by
DATE_FORMAT(order_time,'yyyy')
) as t2
on substring(t1.cmonth,1,4) = t2.cyear;
窗口实现
SELECT
order_month,
num,
total,
round( num / total, 2 ) AS ratio
FROM
(
select
substr(order_time, 1, 7) as order_month,
sum(order_num) over (partition by substr(order_time, 1, 7)) as num,
sum(order_num) over (partition by substr( order_time, 1, 4 )) total,
row_number() over (partition by substr(order_time, 1, 7)) as rk
from ods_saleorder
) temp
where rk = 1;
同比环比
与上年度数据对比称同比，与上月数据对比称环比。
相关公式如下：
同比增长率计算公式=(当年值-上年值)/上年值x100%
环比增长率计算公式=(当月值-上月值)/上月值x100%
lead lag 的实现
这里我们就用环比做个例子，同比类似
select
now_month,
now_num,
last_num,
round( (now_num-last_num) / last_num, 2 ) as ratio
FROM(
select
now_month,
now_num,
lag( t1.now_num, 1) over (order by t1.now_month ) as last_num
from
(
select
substr(order_time, 1, 7) as now_month,
sum(order_num) as now_num
from ods_saleorder
group by
substr(order_time, 1, 7)
) t1
) t2;
我们看到有null 值，这里我们可以使用，lag的默认值做一次优化
select
now_month,
now_num,
last_num,
-- 分母是0的话返回值是null
nvl(round( (now_num-last_num) / last_num, 2 ),0)as ratio
FROM(
select
now_month,
now_num,
lag( t1.now_num, 1,0) over (order by t1.now_month ) as last_num
from
(
select
substr(order_time, 1, 7) as now_month,
sum(order_num) as now_num
from ods_saleorder
group by
substr(order_time, 1, 7)
) t1
) t2;
其实到这里我们就处理完了，但是这样真的对吗？
我们看到2020-01 的last_num 是800 ，也就是2019-04，其实到这里我们就明白了，我们的数据是不连续的，所以我们这样计算是不行的，如果每个月都齐全，都有数据lag(num,12)就可以。
那就只能做自关联了，这样的话我们可以对时间做精准的限制
自关联的实现
with temp as (
select
now_month,
now_num,
substr(date(concat(now_month,'-','01')) - INTERVAL '1' month, 1, 7) as last_month
from(
select
substr(order_time, 1, 7) as now_month,
sum(order_num) as now_num
from ods_saleorder
group by
substr(order_time, 1, 7)
) tmp
)
select
t1.now_month,
t1.now_num,
t1.last_month,
t2.now_num,
nvl(round( (t1.now_num - t2.now_num) / t2.now_num, 2 ),0) as ratio
from temp as t1
inner join temp as t2
on t1.last_month = t2.now_month;
这里的时间计算INTERVAL 你也可以换成其他函数
with temp as (
select
now_month,
now_num,
substr(add_months(concat(now_month,'-','01'),-1), 1, 7) as last_month
from(
select
substr(order_time, 1, 7) as now_month,
sum(order_num) as now_num
from ods_saleorder
group by
substr(order_time, 1, 7)
) tmp
)
select
t1.now_month,
t1.now_num,
t1.last_month,
nvl(t2.now_num,0),
nvl(round( (t1.now_num - t2.now_num) / t2.now_num, 2 ),0) as ratio
from temp as t1
left join temp as t2
on t1.last_month=t2.now_month;