1. 求5min内浏览次数达到100的用户-- LAG和LEAD函数
- 有如下场景:
某公司网站每日访问量达到10亿级别的访问量,每次访问记录一条数据,数据包含如下字段:用户ID,访问时间(毫秒级),访问页面。
要求使用hive求出所有在5分钟内访问次数达到100次的用户(求出用户ID即可) - **思路:**利用窗口函数Lag
- 详细思路:
1.选出当天访问次数达到100次的用户(即当天有100及以上条数据的用户):根据用户ID分组,count
2.在每个 用户ID小组内(步骤1已进行分组)按 访问时间进行升序排序
3.计算time-lag(time,100),若time-lag(time,100)<=5601000(毫秒),即为满足条件的用户,筛选出。 - HQL语句书写
select t.id from{
select
id,
time-lag(time,100,time-5601000-1) over(partition by id order by time) as time_length
from log
group by id
} t
group by id
having min(time_length)<=5601000
注释:time-lag(time,100,time-5601000-1) 中100表示取前100行的数据,若无前100行的数据,则取默认值time-5601000-1。当去默认值time-5601000-1时,time-(time-5601000-1)为5min零1毫秒,大于5min,不满足条件,后续过滤掉。
ps:该HQL只表示大致思路,有优化空间。
5. LAG和LEAD函数
LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值
第一个参数为列名,
第二个参数为往上第n行(可选,默认为1),
第三个参数为默认值(当往上第n行为NULL时候,取默认值,如不指定,则为NULL)
LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值
第一个参数为列名,
第二个参数为往下第n行(可选,默认为1),
第三个参数为默认值(当往下第n行为NULL时候,取默认值,如不指定,则为NULL)
2.成绩表列转行-上表转成下表
转载自
那么最后如果需要将“表-4”的内容转换为“表-3”的内容应该怎么做呢?
一种比较好的方式是这样:
select table_4.name,
a.item,
a.score
from table_4
lateral view explode(
str_to_map(concat('math=',math,'&english=',english),'&','=')
) a as item,score;
解析:首先使用str_to_map函数将math字段与english字段拼接后的结果转换为map类型,然后通过侧视图和explode函数将其爆炸开,给生成的临时侧视图一个名字,取名a并给列名取名为item,score,因为explode(map)爆炸的结果是每一个item为行,key为1列,value为1列,这样就恰好形成我们想要的结果。这个示例理解起来稍微有点难度,大家不熟悉这些函数的用法的话,可以首先熟悉一下。
2.1 str_to_map hive 字符串转为map格式
英语翻译如下:
使用两个分隔符将文本拆分为键值对。 Delimiter1将文本分成K-V对,Delimiter2分割每个K-V对。对于delimiter1和Delimiter2之间的默认分隔符是’,‘,对于delimiter2默认分隔符是’='。
案例1:
hive>
> select str_to_map('aaa:11&bbb:22', '&', ':')
> from tmp.tmp_jzl_20140725_test11;
OK
{"bbb":"22","aaa":"11"}
案例2:
hive> select str_to_map('aaa:11&bbb:22', '&', ':')['aaa']
> from tmp.tmp_jzl_20140725_test11;
OK
11
2.2 lateral view explode(str_to_map())例子
select user_id,gender,city,year_old
from table
lateral view explode(str_to_map(user_info,',',':') as gender,city,year_old
2.3 待做
# 待商榷是否正确
select t.dtm,id,
sum(t.num_tmp) over (partition by t.dtm,t.id) as num, #
sum(t.num_tmp) over(partition by t.dtm) as total_num
from(
select dtm,id,num_tmp
from t
lateral view explode(split(id,',')) tmp1 as id,
lateral view explode(split(num,',')) tmp2 as num_tmp #两个分别分割使用两次
)t
3.连续登录7天的用户
转载自
具体思路:
1、因为每天用户登录次数可能不止一次,所以需要先将用户每天的登录日期去重。
2、再用row_number() over(partition by _ order by _)函数将用户id分组,按照登陆时间进行排序。
3、计算登录日期减去第二步骤得到的结果值,用户连续登陆情况下,每次相减的结果都相同。
4、按照id和日期分组并求和,筛选大于等于7的即为连续7天登陆的用户。
select id,count(*) from (
select *,date(日期)-cum as 结果 from (
select *,row_number() over(PARTITION by id order by 日期) as cum from (
select DISTINCT date(date) as 日期,id from orde)a
)b
)c
GROUP BY id,结果 having count(*)>=7;