## [big data bibiji 20210128] use hive to calculate month on month and year on year

Big data is fun 2021-02-23 16:45:03
big data bibiji use hive

##### What is absolute value

Current data - Same period data /| Same period data | example ：2019 year 1 month 1 Japanese gmv -2018 year 1 month 1 Japanese gmv/|2018 year 1 month 1 Japanese gmv|

##### What is the absolute value of the link

Current data - Previous data /| Previous data | example ：2019 year 2 month 2 Japanese gmv -2018 year 2 month 1 Japanese gmv/|2018 year 2 month 1 Japanese gmv|

##### Data set preparation

Create table statement

```create table new_table(
dt string,
area string,
province string,
saleroom int
);```

Data preparation ：

```insert into new_table values('2017-12-01', 'hd', 'sh','3600000');
insert into new_table values('2017-12-02', 'hd', 'js','2800000');
insert into new_table values('2017-12-03', 'hd', 'zj','4500000');
insert into new_table values('2017-12-04', 'hb', 'bj','3000000');
insert into new_table values('2017-12-05', 'hb', 'tj','2800000');
insert into new_table values('2018-12-01', 'hd', 'sh','3000000');
insert into new_table values('2018-12-02', 'hd', 'js','2000000');
insert into new_table values('2018-12-03', 'hd', 'zj','2500000');
insert into new_table values('2018-12-04', 'hb', 'bj','2600000');
insert into new_table values('2018-12-05', 'hb', 'tj','1500000');```

On a year-on-year basis

```with tmp as (
select
dt,
area,
province,
saleroom,
lag(saleroom,1,0) over(partition by concat(month(dt),"-",day(dt)),area,province order by dt asc) pre_sale
from new_table)
select
dt,area,province,saleroom,pre_sale,
if(round((saleroom-pre_sale)/abs(pre_sale)*1.00,2) is null,100,round((saleroom-pre_sale)/abs(pre_sale)*1.00,2)*100)
from tmp;```

```with tmp as (
select
dt,
area,
province,
saleroom,
lag(saleroom,1,0) over(partition by concat(month(dt),"-",day(dt)),area,province order by dt asc) pre_sale
from new_table)
select
dt,area,province,saleroom,pre_sale,
if(round((saleroom-pre_sale)/abs(pre_sale)*1.00,2) is null,100,round((saleroom-pre_sale)/abs(pre_sale)*1.00,2)*100)
from tmp ;```