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
    评论
在JPA中,HQL(Hibernate Query Language)是一种面向对象的查询语言,用于查询数据库中的实体对象。HQL与SQL语法类似,但是使用实体类和属性名代替表名和列名。在JPA中,可以使用@Query注解来编写HQL查询。 例如,在使用JPA的@Repository注解标注的接口中,可以使用@Query注解来编写HQL查询语句。在@Query注解中,可以使用实体类和属性名来代替表名和列名。同时,可以使用参数占位符(如:name)来传递参数。 下面是一个使用HQL查询的示例代码: ```java @Repository public interface UserRepository extends JpaRepository<User, Long> { @Query("SELECT u FROM User u WHERE u.email = :email") User findByEmail(@Param("email") String email); } ``` 在上述示例中,使用@Query注解编写了一个HQL查询语句,查询email属性等于传入参数email的User对象。 需要注意的是,HQL查询语句中的实体类和属性名是区分大小写的,且与数据库表和列名无关。因此,在编写HQL查询时,需要确保实体类和属性名的正确性。 希望以上信息对您有所帮助。\[1\]\[3\] #### 引用[.reference_title] - *1* [Spring Data JPA的使用。](https://blog.csdn.net/liuno0/article/details/124979302)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^control_2,239^v3^insert_chatgpt"}} ] [.reference_item] - *2* [JPA中使用delete踩坑记录](https://blog.csdn.net/qq_43680542/article/details/109724364)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^control_2,239^v3^insert_chatgpt"}} ] [.reference_item] - *3* [Spring访问数据库之使用JPA](https://blog.csdn.net/zy199701/article/details/113833903)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^control_2,239^v3^insert_chatgpt"}} ] [.reference_item] [ .reference_list ]

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 9
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

打赏作者

醪糟小丸子

小小帮助,不足挂齿

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

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

打赏作者

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

抵扣说明:

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

余额充值