最近在工作中,业务方提出了一个对数据监控预警的需求。即统计每日的数据量差异,如果此差异超过某个设定的阈值,将会自动邮件给相关的数据维护人员,查找出造成数据量波动较大的原因,及时做出相应调整,防止数据缺失的风险。
在实际的业务需求中,常常会让你统计当天与前一天数据差异的指标。
假设表t_tier的结构及存储数据如下:
uid | name | phone | tier |
001 | ZHANGSAN | 13535211451 | A |
002 | LISI | 15571211461 | B |
003 | WANGWU | 13531218451 | A |
004 | ZHAOLIU | 13531221457 | C |
005 | TOM | 13131216451 | B |
006 | LILEI | 13941211351 | B |
007 | HANMEIMEI | 13531211451 | C |
008 | DABAO | 18532219451 | A |
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字段进行排序,对数据进行查询。