hive实操-连续3天登录的用户数

1.sql面试题

http://www.coder55.com/article/30056

create table tmp_last_3_day(
userid int,
login_date string);

insert into tmp_last_3_day (userid,login_date) values(123,'2018-08-03');
insert into tmp_last_3_day (userid,login_date) values(123,'2018-08-04');
insert into tmp_last_3_day (userid,login_date) values(456,'2018-11-02');
insert into tmp_last_3_day (userid,login_date) values(456,'2018-12-09');
insert into tmp_last_3_day (userid,login_date) values(789,'2018-01-01');
insert into tmp_last_3_day (userid,login_date) values(789,'2018-04-23');
insert into tmp_last_3_day (userid,login_date) values(789,'2018-09-10');
insert into tmp_last_3_day (userid,login_date) values(789,'2018-09-11');
insert into tmp_last_3_day (userid,login_date) values(789,'2018-09-12');

select b.userid,b.login_date,b.rn from (select t.userid, t.login_date, row_number() over (partition by t.userid order by t.login_date) as rn from tmp_last_3_day t) b;

+-----------+---------------+-------+
| b.userid  | b.login_date  | b.rn  |
+-----------+---------------+-------+
| 123       | 2018-08-02    | 1     |
| 123       | 2018-08-03    | 2     |
| 123       | 2018-08-04    | 3     |
| 789       | 2018-01-01    | 1     |
| 789       | 2018-04-23    | 2     |
| 789       | 2018-09-10    | 3     |
| 789       | 2018-09-11    | 4     |
| 789       | 2018-09-12    | 5     |
| 456       | 2018-11-02    | 1     |
| 456       | 2018-12-09    | 2     |
+-----------+---------------+-------+

select b.userid,date_sub(b.login_date,b.rn) as ds from (select t.userid, t.login_date, row_number() over (partition by t.userid order by t.login_date) as rn from tmp_last_3_day t) b;

+-----------+-------------+
| b.userid  |     ds      |
+-----------+-------------+
| 123       | 2018-08-01  |
| 123       | 2018-08-01  |
| 123       | 2018-08-01  |
| 789       | 2017-12-31  |
| 789       | 2018-04-21  |
| 789       | 2018-09-07  |
| 789       | 2018-09-07  |
| 789       | 2018-09-07  |
| 456       | 2018-11-01  |
| 456       | 2018-12-07  |
+-----------+-------------+

select b.userid,b.login_date,b.rn,date_sub(b.login_date,b.rn) as ds from (select t.userid, t.login_date, row_number() over (partition by t.userid order by t.login_date) as rn from tmp_last_3_day t) b;

+-----------+---------------+-------+-------------+
| b.userid  | b.login_date  | b.rn  |     ds      |
+-----------+---------------+-------+-------------+
| 123       | 2018-08-02    | 1     | 2018-08-01  |
| 123       | 2018-08-03    | 2     | 2018-08-01  |
| 123       | 2018-08-04    | 3     | 2018-08-01  |
| 789       | 2018-01-01    | 1     | 2017-12-31  |
| 789       | 2018-04-23    | 2     | 2018-04-21  |
| 789       | 2018-09-10    | 3     | 2018-09-07  |
| 789       | 2018-09-11    | 4     | 2018-09-07  |
| 789       | 2018-09-12    | 5     | 2018-09-07  |
| 456       | 2018-11-02    | 1     | 2018-11-01  |
| 456       | 2018-12-09    | 2     | 2018-12-07  |
+-----------+---------------+-------+-------------+

select b.userid,count(1) as cnt from (select t.userid, t.login_date, row_number() over (partition by t.userid order by t.login_date) as rn from tmp_last_3_day t) b group by b.userid,date_sub(b.login_date,b.rn) having count(1) >= 3 order by b.userid;

+-----------+------+
| b.userid  | cnt  |
+-----------+------+
| 123       | 3    |
| 789       | 3    |
+-----------+------+