hsql统计两天数据差异的算法及lag()/led()分析函数的使用

--创建存储每日各版本标签总量的表
create table t_total (                      
      data_date string comment'日期',
      tier string comment'标签',
      today_total string comment '今日标签总数'
) partitioned by (dt string comment '分区字段，值同data_date')
set hive.exec.dynamic.partition.mode=nonstrict; --设置自动分区
insert overwrite table t_total partition(dt)
select
t.data_date
,t.tier
,t.today_total
,t.data_date as dt
from (
select 
'$now.date' as data_date --'$now.date',python脚本中的写法
,tier
,count(*) as today_total
from t_tier 
group by tier) t 

--创建数据差异表（可分区，可不分区）
create table t_tier_total_monitor (                      
      data_date string comment'日期',
      tier string comment'标签',
      yesterday_total string comment '昨日标签总数',
      today_total string comment '今日标签总数',
      diff_rate string comment '昨日和今日差异'
) partitioned by (dt string comment '分区字段，值同data_date')
-------------------------------------------------------------------------------------
--统计两天数据差异的方法一：
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table t_tier_total_monitor partition(dt)
select 
 b.today as data_date
,b.tier
,(case when a.today_total is not null and a.today_total<>'0' then a.today_total else 'NaN' end) as yesterday_total
,b.today_total as today_total
,(case when b.today_total is not null and b.today_total<>'0' then round((cast(((b.today_total-a.today_total)/b.today_total*100) as double)),2) else 'NaN' end)as diff_rate
,b.today as dt
from t_total a 
left join
(select tier
,today_total
,data_date as today
,row_number() over(partition by tier order by data_date desc) as rn
from t_total) b  --从t_total表中筛出最近一天的数据（当天数据）
on a.tier=b.tier
where date_sub(b.today,1)=a.data_date and b.rn=1 --筛选出最近一天减1天等于前一天的数据

--统计两天数据差异方法二：
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table t_tier_total_monitor(dt)
select  
data_date 
,tier 
,lag(today_total,1,'NaN') over(partition by tier order by data_date) yesterday_total 
,today_total as today_total 
,round((cast((((today_total-lag(today_total,1,'NaN') over(partition by tier order by data_date))/lag(today_total,1,'NaN') over(partition by tier order by data_date))*100) as double)),2) diff_rate 
,dt 
from t_total