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

       最近在工作中,业务方提出了一个对数据监控预警的需求。即统计每日的数据量差异,如果此差异超过某个设定的阈值,将会自动邮件给相关的数据维护人员,查找出造成数据量波动较大的原因,及时做出相应调整,防止数据缺失的风险。

             在实际的业务需求中,常常会让你统计当天与前一天数据差异的指标。

             假设表t_tier的结构及存储数据如下:

uidnamephonetier
001ZHANGSAN13535211451A
002LISI15571211461B
003WANGWU13531218451A
004ZHAOLIU13531221457C
005TOM13131216451B
006LILEI13941211351B
007HANMEIMEI13531211451C
008DABAO18532219451A

              uid的tier每天都会变化(比如:6月24属于A,6月25属于B),故将该表放在在调度系统中每日跑数,就会造成类别A、B、C每日的数据量不同

              现要统计每个类别当天与前一天数据的差异指标,表结构如下,t_differ

             思路:

             1、首先,创建一个分区表t_total,统计出每日每个类别的总量,存入当日分区;

             2、接着,选出t_total表中日期最大的条目再与t_total自连接,过滤出当日与前日的数据,然后计算相应指标。       

            代码实现:

--创建存储每日各版本标签总量的表
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天等于前一天的数据

    使用lag()函数

--统计两天数据差异方法二:
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

  lag()/led()分析函数

lag与lead函数是跟偏移量相关的两个分析函数

       通过这两个函数可以在一次查询中取出同一字段的前N行的数据(lag)和后N行的数据(lead)作为独立的列,从而更方便地进行进行数据过滤,该操作可代替表的自联接,且效率更高

lag()/lead()
  lead(field, num, defaultvalue)
    field: 需要查找的字段
    num: 往后查找的num行的数据
    defaultvalue: 没有符合条件的默认值
over()
  表示lag()与lead()操作的数据都在over()的范围内,里面可以使用以下子句
    partition by 语句(用于分组)
    order by 语句()用于排序)
  如:over(partition by a order by b) 表示以a字段进行分组,再以b字段进行排序,对数据进行查询。

参考:https://www.cnblogs.com/shiliye/p/12361624.html

  • 6
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 9
    评论
评论 9
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

醪糟小丸子

小小帮助,不足挂齿

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值