窗口和分析函数的应用
- 需求:统计连续N天登陆人数
table
logindate userid
2019-08-28 00:03:00 tom
2019-08-28 10:00:00 frank
2019-08-28 11:00:00 jack
2019-08-29 00:03:00 tom
2019-08-29 10:00:00 frank
2019-08-30 10:00:00 tom
2019-08-30 12:00:00 jack- 统计连续登陆2天的人数:tom、frank
-
第一种方案:使用笛卡尔积来做:date_add(date1,1) = date2
select
a.longindate as longina,
a.userid as userida,
b.longindate as longinb,
b.userid as useridb
from table a join table b;–没有给定关联字段就产生笛卡尔积结果是多少条:a*b = 49条 longina userida longinb useridb 2019-08-28 00:03:00 tom 2019-08-28 00:03:00 tom 2019-08-28 00:03:00 tom 2019-08-28 10:00:00 frank 2019-08-28 00:03:00 tom 2019-08-28 11:00:00 jack 2019-08-28 00:03:00 tom 2019-08-29 00:03:00 tom …… a表中的 每一条会与b表的每一条Join一下 select * from rs where userida = useridb and date_add(date(longina),1) = date(longinb) 2019-08-28 00:03:00 tom 2019-08-29 00:03:00 tom
- 工作中不能使用这样的 方案
- 数据量多大的时候,笛卡尔积非常大,导致性能非常差
- 只能实现连续两天的登录统计
- 工作中不能使用这样的 方案
-
第二种方案:窗口函数来做
logindate userid
2019-08-28 tom
2019-08-28 frank
2019-08-28 jack
2019-08-29 tom
2019-08-29 frank
2019-08-30 tom
2019-08-30 jack- lead来实现
select
longindate,
userid,
date_add(longindate,1) as tomorrow,–取当前登录日期的后一天
lead(longindate,1,0) over (partition by userid order by longindate) as nextLogin
from table;
logindate userid tomorrow nextLogin
2019-08-28 tom 2019-08-29 2019-08-29
2019-08-29 tom 2019-08-30 2019-08-30
2019-08-30 tom 2019-08-31 0
2019-08-28 frank 2019-08-29 2019-08-29
2019-08-29 frank 2019-08-30 0
2019-08-28 jack 2019-08-29 2019-08-30
2019-08-30 jack 2019-08-31 0- 哪些人连续登陆两天:tomorrow = nextLogin
- lead来实现
-
- 统计连续登陆3天的人数:tom
-
如果使用笛卡尔积来做
2019-08-28 11:00:00 jack 2019-08-30 12:00:00 jackselect * from rs where userida = useridb and date_add(date(longina),2) = date(longinb);
-
使用窗口来实现连续三天登录
select
longindate,
userid,
date_add(longindate,2) as tomorrow,–取当前登录日期的后两天
lead(longindate,2,0) over (partition by userid order by longindate) as nextLogin
from table;
logindate userid tomorrow nextLogin
2019-08-28 tom 2019-08-30 2019-08-30
2019-08-29 tom 2019-08-31 0
2019-08-30 tom 2019-09-01 0
2019-08-28 frank 2019-08-30 0
2019-08-29 frank 2019-08-31 0
2019-08-28 jack 2019-08-30 0
2019-08-30 jack 2019-09-01 0- 哪些人连续登陆三天:tomorrow = nextLogin
-
- 统计连续登陆2天的人数:tom、frank
最后
以上就是震动河马最近收集整理的关于连续登录hiveSQL实现的全部内容,更多相关连续登录hiveSQL实现内容请搜索靠谱客的其他文章。
发表评论 取消回复