做网站先买域名,app开发企业网站建设,湖南做网站360o,58同城招聘网找工作附近往往用了很久的函数却只知道其单一的应用场景#xff0c;本文将不断完善所遇到的好用的hive内置函数。
1.聚合函数或者求最大最小值函数搭配开窗函数使用可以实现滑动窗口 例#xff1a; collect_list函数#xff0c;搭配开窗函数#xff0c;实现了在滑动窗口内对事件路径…往往用了很久的函数却只知道其单一的应用场景本文将不断完善所遇到的好用的hive内置函数。
1.聚合函数或者求最大最小值函数搭配开窗函数使用可以实现滑动窗口 例 collect_list函数搭配开窗函数实现了在滑动窗口内对事件路径的全记录且是按照分组内前后顺序的不断累计。 同理把COLLECT_LIST替换为sum则实现的是滑动窗口分组内前后顺序的不断累计求和求和替换为row_number就是对窗口内数据的排序。 例题 有这样一张表每个sesisonid 有多个event和time可以认为他们是某一个路径 请找出每次路径下之前的所有路径组成新列 event time session_id 新列 a 1 1 a b 2 1 a,b c 3 1 a,b,c 表session_test
SELECT event,time,session_id,COLLECT_LIST(event) OVER (PARTITION BY session_id ORDER BY time) AS new_column--COLLECT_LIST(event) OVER (PARTITION BY session_id ORDER BY time rows between unbounded preceding and current row) AS new_column 效果等同
;2.rullup、cube、与grouping sets函数 grouping sets() 后跟的是自定义的维度组合实现效果是将结果的各个维度数据union all起来 rullup是cube的子集实现效果是以最左侧的维度为起点遍历与他相关的所有维度组合 cube函数是对所选维度的全组合。
select
GROUPING__ID,
year_name,
cn_quarter,
month_name,count(*) as num,
GROUPING(year_name),
GROUPING(cn_quarter),
GROUPING(month_name)
from dim_date_df
where year_name2021
group by rollup(year_name,cn_quarter,month_name)
order by GROUPING__ID;3.窗口函数 --方法一使用日期补足策略将活动期间的日期全部补充完整然后去重计数
select
brand,
count(distinct newdate)
from
(select
brand,
stt,
edt,
datesub,
index,
date_add(stt,index) newdate
from
(select
brand,
stt,
edt,
datediff(edt,stt) datesub
from date_test
)temp0
lateral view posexplode(split(space(datesub),)) tmp as index,value
)temp0
group by brand;--方法二使用整体时间范围-累计时间间隔的方式
select
temp3.brand,
--join 用整体时间范围-整体间隔时间
alldate-nvl(sumlossdate,0)
from
(--对间隔时间求和selectbrand,
sum(lossdate) sumlossdate
from
(--过滤出开始日期截止上一行的最大结束日期的数据并求差值看间隔了几天才重新开始活动selectbrand,
stt,
edt,
nowmaxedt,
stt partnewstt,
datediff(stt,nowmaxedt)-1 lossdate
from (--查询截止当前行上一行的最大结束日期select brand,stt,edt,max(edt) over(partition by brand order by stt,edt rows between unbounded preceding and 1 preceding) nowmaxedtfrom date_test)temp0
where sttdate_add(nowmaxedt,1) and nowmaxedt is not null
)temp1
group by brand
)temp2 right join (select
brand,
datediff(max(edt),min(stt))1 alldate
from date_test
group by brand
)temp3 on temp2.brandtemp3.brand;关于窗口函数中行号的取值
--查询截止当前行上一行的最大结束日期
--rows between unbounded preceding 从分区中的第1行开始 1 preceding表示当前行的前1行实现了滑动窗口。CURRENT ROW表示当前行 following表示后多少行
select brand,stt,edt,max(edt) over(partition by brand order by stt,edt rows between unbounded preceding and 1 preceding) nowmaxedt
from date_test;--1 following 表当前行的后1行。负数时候会报错
select brand,stt,edt,max(edt) over(partition by brand order by stt,edt rows between unbounded preceding and 1 following) nowmaxedt
from date_test;--使用range取范围时候order by 语句后只能跟一个排序字段range不关心顺序。
select brand,stt,edt,max(edt) over(partition by brand order by stt RANGE BETWEEN UNBOUNDED PRECEDING and 1 preceding ) nowmaxedt
from date_test;
