Hive实现环比和同比

• 环比

环比增长率计算公式=(当月值-上月值)/上月值x100%

---

自己和自己关联核心思路，以下两种写法，核心思想一致，区别在于

当前月+1个月

当前月-1个月

---

-- 1>当前月+1个月=下个月   
-- 2>left join on 当前月=下个月   下个月对应的value是上个月的
-- 3> 当前月value-上个月value

with temp as (
  SELECT cmonth,order_num, date(concat(cmonth,'-','01')) + interval 1 month as last_month from (
      SELECT order_id,order_time,order_num,DATE_FORMAT(order_time,'yyyy') as cyear,DATE_FORMAT(order_time,'yyyy-MM') as cmonth from (
          SELECT 1 as order_id , '2021-04-20' as order_time , 420 as order_num 
          union all 
          SELECT 1 as order_id , '2021-05-05' as order_time , 800 as order_num 
          union all 
          SELECT 1 as order_id , '2021-06-04' as order_time , 500 as order_num 
          union all 
          SELECT 1 as order_id , '2022-04-04' as order_time , 600 as order_num 
          union all 
          SELECT 1 as order_id , '2022-05-04' as order_time , 200 as order_num
          union all 
          SELECT 1 as order_id , '2022-06-04' as order_time , 950 as order_num
      ) t1 
  ) t2 
)
select  
tmp1.*,
tmp2.* ,
(tmp1.order_num-tmp2.order_num) as diff,
((tmp1.order_num-tmp2.order_num)/tmp2.order_num) as ratio   
from temp as tmp1 left join temp as tmp2 on tmp1.cmonth=date_format(tmp2.last_month,'yyyy-MM')

 

-- 1>当前月-1个月=上个月   
-- 2>left join on 当前月的上个月=当前月   当前月对应的value是上个月的
-- 3> 当前月value-上个月value

with temp as (
  SELECT cmonth,order_num, date(concat(cmonth,'-','01')) - interval 1 month as last_month from (
      SELECT order_id,order_time,order_num,DATE_FORMAT(order_time,'yyyy') as cyear,DATE_FORMAT(order_time,'yyyy-MM') as cmonth from (
          SELECT 1 as order_id , '2021-04-20' as order_time , 420 as order_num 
          union all 
          SELECT 1 as order_id , '2021-05-05' as order_time , 800 as order_num 
          union all 
          SELECT 1 as order_id , '2021-06-04' as order_time , 500 as order_num 
          union all 
          SELECT 1 as order_id , '2022-04-04' as order_time , 600 as order_num 
          union all 
          SELECT 1 as order_id , '2022-05-04' as order_time , 200 as order_num
          union all 
          SELECT 1 as order_id , '2022-06-04' as order_time , 950 as order_num
      ) t1 
  ) t2 
)
select  
tmp1.*,
tmp2.* ,
(tmp1.order_num-tmp2.order_num) as diff,
((tmp1.order_num-tmp2.order_num)/tmp2.order_num) as ratio   
from temp as tmp1 left join temp as tmp2 on date_format(tmp1.last_month,'yyyy-MM')=tmp2.cmonth

 

---

---

---

• 同比

同比增长率计算公式=(当年值-上年值)/上年值x100%

实现核心和环比一样

---

当前月+12个月

当前月-12个月

---

with temp as (
  SELECT cmonth,order_num, date(concat(cmonth,'-','01')) + interval 12 month as last_month from (
      SELECT order_id,order_time,order_num,DATE_FORMAT(order_time,'yyyy') as cyear,DATE_FORMAT(order_time,'yyyy-MM') as cmonth from (
          SELECT 1 as order_id , '2021-04-20' as order_time , 420 as order_num 
          union all 
          SELECT 1 as order_id , '2021-05-05' as order_time , 800 as order_num 
          union all 
          SELECT 1 as order_id , '2021-06-04' as order_time , 500 as order_num 
          union all 
          SELECT 1 as order_id , '2022-04-04' as order_time , 600 as order_num 
          union all 
          SELECT 1 as order_id , '2022-05-04' as order_time , 200 as order_num
          union all 
          SELECT 1 as order_id , '2022-06-04' as order_time , 950 as order_num
      ) t1 
  ) t2 
)
select  
tmp1.*,
tmp2.* ,
(tmp1.order_num-tmp2.order_num) as diff,
((tmp1.order_num-tmp2.order_num)/tmp2.order_num) as ratio   
from temp as tmp1 left join temp as tmp2 on tmp1.cmonth=date_format(tmp2.last_month,'yyyy-MM')

with temp as (
  SELECT cmonth,order_num, date(concat(cmonth,'-','01')) - interval 12 month as last_month from (
      SELECT order_id,order_time,order_num,DATE_FORMAT(order_time,'yyyy') as cyear,DATE_FORMAT(order_time,'yyyy-MM') as cmonth from (
          SELECT 1 as order_id , '2021-04-20' as order_time , 420 as order_num 
          union all 
          SELECT 1 as order_id , '2021-05-05' as order_time , 800 as order_num 
          union all 
          SELECT 1 as order_id , '2021-06-04' as order_time , 500 as order_num 
          union all 
          SELECT 1 as order_id , '2022-04-04' as order_time , 600 as order_num 
          union all 
          SELECT 1 as order_id , '2022-05-04' as order_time , 200 as order_num
          union all 
          SELECT 1 as order_id , '2022-06-04' as order_time , 950 as order_num
      ) t1 
  ) t2 
)
select  
tmp1.*,
tmp2.* ,
(tmp1.order_num-tmp2.order_num) as diff,
((tmp1.order_num-tmp2.order_num)/tmp2.order_num) as ratio   
from temp as tmp1 left join temp as tmp2 on date_format(tmp1.last_month,'yyyy-MM')=tmp2.cmonth