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 ','
;
LOAD DATA LOCAL INPATH '/Users/liuwenqiang/workspace/hive/saleorder.txt' OVERWRITE INTO TABLE ods.ods_saleorder;

select
    a.m_num,a.cmonth,b.y_num,b.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')
    ) a
        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')
    ) b
 on
    substring(a.cmonth,1,4)=b.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%

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;

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;

with a 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
    a1.now_month,a1.now_num,a1.last_month,a2.now_num,
    nvl(round( (a1.now_num-a2.now_num) / a2.now_num, 2 ),0) as ratio
from
    a  a1
inner join
    a a2
on
    a1.last_month=a2.now_month
;

with a 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
    a1.now_month,a1.now_num,a1.last_month,nvl(a2.now_num,0),
    nvl(round( (a1.now_num-a2.now_num) / a2.now_num, 2 ),0) as ratio
from
    a  a1
left join
    a a2
on
    a1.last_month=a2.now_month
;