广东省建设厅官方网站,上海专业网站设计制作,可视化网页设计在线,做企业网站哪家公司好目录
一、连续登陆
1.1 连续登陆3天以上的用户
0 问题描述
1 数据准备
2 数据分析
3 小结
1.2 每个用户历史至今连续登录的最大天数
0 问题描述
1 数据准备
2 数据分析
3 小结
1.3 每个用户连续登录的最大天数(间断也算)
0 问题描述
1 数据准备
2 数据分析
3 小…目录
一、连续登陆
1.1 连续登陆3天以上的用户
0 问题描述
1 数据准备
2 数据分析
3 小结
1.2 每个用户历史至今连续登录的最大天数
0 问题描述
1 数据准备
2 数据分析
3 小结
1.3 每个用户连续登录的最大天数(间断也算)
0 问题描述
1 数据准备
2 数据分析
3 小结 一、连续登陆
1.1 连续登陆3天以上的用户
0 问题描述 查询连续登陆3天以上的用户字节面试题
1 数据准备
create table if not exists table1 (id int comment 用户id, date string comment用户登录时间);insert overwrite table table1 values
(1,2019-01-01 19:28:00),
(1,2019-01-02 19:53:00),
(1,2019-01-03 22:00:00),
(1,2019-01-05 20:55:00),
(1,2019-01-06 21:58:00),
(2,2019-02-01 19:25:00),
(2,2019-02-02 21:00:00),
(2,2019-02-04 22:05:00),
(2,2019-02-05 20:59:00),
(2,2019-02-06 19:05:00),
(3,2019-03-04 21:05:00),
(3,2019-03-05 19:10:00),
(3,2019-03-06 19:55:00),
(3,2019-03-07 21:05:00);
2 数据分析
selectdistinct id
from (selectid,difffrom (selectid,date_sub(dt, row_number()over (partition by id order by dt)) difffrom ( --- 同一个用户一天可能登陆多次,所以,先去重selectid,date_format(date,yyyy-MM-dd) as dtfrom table1-- current_date() 获取当前的年月日where date_format(date,yyyy-MM-dd) between date_sub(current_date(),7) and current_date()group by id, date_format(date,yyyy-MM-dd)) tmp1) tmp2group by id, diffhaving count(1) 3) tmp3;
3 小结 “连续登陆”类型的解题思路
1计算 date_sub(login_date,row_number() over (user_id oder by login_date)) diff;
2group by user_id,diff 分组
3count(1) 3天的用户就是连续登陆3天及以上的用户
1.2 每个用户历史至今连续登录的最大天数
0 问题描述 查询每个用户历史至今连续登录的最大天数
1 数据准备
create table if not exists table1 (id int comment 用户id, date string comment用户登录时间);insert overwrite table table1 values
(1,2019-01-01 19:28:00),
(1,2019-01-02 19:53:00),
(1,2019-01-03 22:00:00),
(1,2019-01-05 20:55:00),
(1,2019-01-06 21:58:00),
(2,2019-02-01 19:25:00),
(2,2019-02-02 21:00:00),
(2,2019-02-04 22:05:00),
(2,2019-02-05 20:59:00),
(2,2019-02-06 19:05:00),
(3,2019-03-04 21:05:00),
(3,2019-03-05 19:10:00),
(3,2019-03-06 19:55:00),
(3,2019-03-07 21:05:00);
2 数据分析
selectid,max(cnt) as days
from (selectid,count(1) as cntfrom (selectid,date,date_sub(date, row_number() over (partition by id order by date)) difffrom (--用户在同一天可能登录多次需要去重selectid,date_format(date, yyyy-MM-dd) as datefrom table1group by id, date_format(date, yyyy-MM-dd)) tmp1) tmp2group by id, diff) tmp3
group by id;
3 小结 “连续登陆”类型的解题思路
1计算 date_sub(login_date,row_number() over (user_id oder by login_date)) diff;
2group by user_id,diff 分组
3max(cnt)得到就是每个用户历史至今连续登陆的 最大天数。
1.3 每个用户连续登录的最大天数(间断也算)
0 问题描述 统计各用户最长的连续登录天数间断一天也算作连续。例如一个用户在1,3,5,6号登录则视为连续6天登录。
1 数据准备
create table if not exists table1 (id int comment 用户id, date string comment用户登录时间);insert overwrite table table1 values
(1,2019-01-01 19:28:00),
(1,2019-01-02 19:53:00),
(1,2019-01-03 22:00:00),
(1,2019-01-05 20:55:00),
(1,2019-01-06 21:58:00),
(2,2019-02-01 19:25:00),
(2,2019-02-02 21:00:00),
(2,2019-02-04 22:05:00),
(2,2019-02-05 20:59:00),
(2,2019-02-06 19:05:00),
(3,2019-03-04 21:05:00),
(3,2019-03-05 19:10:00),
(3,2019-03-06 19:55:00),
(3,2019-03-07 21:05:00);
2 数据分析 方式一间断的那一天构造array数组利用炸裂函数进行补全然后按照“用户连续登陆”的思路来做。
selectid,max(cnt) as days
from (selectid,diff,count(1) as cntfrom (selectid,login_date,next_login_date,arr,new_login_date,date_sub(new_login_date, row_number() over (partition by id order by new_login_date)) difffrom (selectid,login_date,next_login_date,arr,new_login_datefrom (selectid,login_date,next_login_date,--间断的那一天构造array数组利用炸裂函数进行补全if(datediff(next_login_date, login_date) 2,array(login_date, date_add(login_date, 1)),array(login_date)) as arrfrom (selectid,login_date,--窗口函数 lead(向后取n行)--lead(column1,n,val)over(partition by column2 order by column3) 查询当前行的后边第n行数据如果没有就为nulllead(login_date, 1, 9999-12-31)over (partition by id order by login_date) next_login_datefrom (--用户在同一天可能登录多次需要去重selectid,date_format(date, yyyy-MM-dd) as login_datefrom table1group by id, date_format(date, yyyy-MM-dd)) tmp1) tmp2) tmp3lateral view explode(arr) tmp as new_login_date) tmp4) tmp5group by id, diff) tmp6
group by id;
方式二对用户多段stage的连续登陆进行划分,思路类似:会话划分
selectid,max(diff) as days
from (selectid,stage,datediff(max(login_date), min(login_date)) 1 as difffrom (selectid,login_date,-- 思路类似会话划分字符串拼接得到stageconcat(id, -, sum(start_point)over (partition by id order by login_date rows between unbounded preceding and current row )) stagefrom (selectid,login_date,--间隔一天也算连续所以差值大于2的数据打上标签if(datediff(login_date, last_login_date) 2, 1, 0) start_pointfrom (selectid,login_date,--窗口函数 lag(向前取n行)--lag(column1,n,val)over(partition by column2 order by column3) 查询当前行的前边第n行数据如果没有就为nulllag(login_date, 1, 1970-01-01)over (partition by id order by login_date) as last_login_datefrom (selectid,date_format(date, yyyy-MM-dd) as login_datefrom table1group by id, date_format(date, yyyy-MM-dd)) tmp1) tmp2) tmp3) tmp4group by id, stage) tmp5
group by id;
3 小结 “间断连续”类型的解题思路
1构造array数组 2炸裂函数 侧写视图 lateral view explode将一行变多行补全间断的那几天 3补全后之后就按照“连续登陆”的情景进行处理
计算 date_sub(login_date,row_number() over (user_id oder by login_date)) diff;group by user_id,diff 分组max(cnt)得到就是每个用户历史至今连续登陆的最大天数。
