闰年同比 HQL；利用add_months实现同比（闭坑闰年）

议题：闰年2月日同比

办法：代码块02

阅读：https://www.cnblogs.com/huaxiaoyao/p/4364141.html

常用hive函数：https://www.cnblogs.com/fanyi0922/p/12187436.html

--试试

select add_months('2020-03-31',-1)

,add_months('2019-02-28',-1)

,add_months('2021-03-01',-12)

,add_months('2021-02-28',-12)

,add_months('2021-02-27',-12)

,add_months('2020-02-29',-12)

,add_months('2020-02-28',-12)

,add_months('2020-02-27',-12)



,add_months('2019-02-29',+12) -- null

,add_months('2019-02-28',+12)

,add_months('2019-02-27',+12)

,add_months('2019-02-28',+12)

,add_months('2020-02-28',+12)

,add_months('2020-02-29',+12)

-- 具体实操
with base_0
as (
    select
    business_type
    ,process_date as date
    ,substring(process_date,6,10) as month_date
    ,brand_chinese
    ,sum(pay_tickets) as pay_tickets
    from tmp_busdb.bus_ship_ticket_profit_level_one  -- dim: date、productline、brand
    group by business_type
        ,process_date
        ,brand_chinese
    )
 
 
     
select
this_year.business_type
,this_year.date
,this_year.brand_chinese
,this_year.pay_tickets
 
,last_year.pay_tickets as pay_tickets_lastyear
from
(
select
business_type
,date
,brand_chinese
,pay_tickets
from base_0
) this_year
 
-- 同比数据
left join
(
select
business_type
,date
,brand_chinese
,pay_tickets
from base_0
) last_year
on this_year.business_type  = last_year.business_type
  and this_year.brand_chinese  = last_year.brand_chinese
   -- 正确；
   -- 01若this_year.date含有2.29，则用add_months(this_year.date,-12)去年2.28与之同比
   -- 02若this_year.date仅含有2.28，则用add_months(this_year.date,-12)去年2.28或2.29与之同比
  and add_months(this_year.date,-12)  = last_year.date
   
   
   
   
   -- 错误；
   -- 01若last_year.date 2.28，则add_months(last_year.date,+12) 2.29或2.28
   -- 02若last_year.date 2.29，则add_months(last_year.date,+12) 2.28
   -- and this_year.date  = add_months(last_year.date,+12)