需求二：根据以下数据实现统计：连续登录N天的用户（N＞=2）

需求二：根据以下数据实现统计：连续登录N天的用户（N>=2）

--需求二：根据以下数据实现统计：连续登录N天的用户（N>=2）
输入：
A,2019-08-22
B,2019-08-22
C,2019-08-22
A,2019-08-23
C,2019-08-23
A,2019-08-24
B,2019-08-24

输出：
连续2天登录：A    C
连续3天登录：A

连续两天登录：tomorrow = nextLogin，说明第二天也登录，连续登录两天

连续两天登录：tomorrow = nextLogin，说明第二天也登录，连续登录两天
-- 1.在hive中创建表，并加载原始数据
create table pratics2(
username string,
longinstr string
)row format delimited fields terminated by ',';

load data local inpath '/export/datas/pratics2.txt' into table pratics2; 
-- 2.用SQL语句对表进行分析得到期望结果
select 
a.username
from ( 
    select  username, 
    longinstr,  
    date_add(longinstr,1) as tomorrow,  --这是登录第二天的日期
    lead(longinstr,1,0) over (partition by username order  by longinstr) as next_login --这是下一次登录的时间
    from  pratics2) a 
    where a.tomorrow = a.next_login 
    group by a.username;
    
 +-----------+-------------+-------------+-------------+--+
| username  |  longinstr  |  tomorrow   |  nextlogin  |
+-----------+-------------+-------------+-------------+--+
| A         | 2019-08-22  | 2019-08-23  | 2019-08-23  |
| A         | 2019-08-23  | 2019-08-24  | 2019-08-24  |
| A         | 2019-08-24  | 2019-08-25  | 0           |
| B         | 2019-08-22  | 2019-08-23  | 2019-08-24  |
| B         | 2019-08-24  | 2019-08-25  | 0           |
| C         | 2019-08-22  | 2019-08-23  | 2019-08-23  |
| C         | 2019-08-23  | 2019-08-24  | 0           |
+-----------+-------------+-------------+-------------+--+   

连续3天登录：A

--连续3天登录：A
select
  username,
  longinstr,
  date_add(longinstr,2) as tomorrow,--这是登录日期后两天
  lead(longinstr,2,0) over (partition by username order by longinstr) as nextLogin --这是下下一次登录的时间
from
 pratics2;


+-----------+-------------+-------------+-------------+--+
| username  |  longinstr  |  tomorrow   |  nextlogin  |
+-----------+-------------+-------------+-------------+--+
| A         | 2019-08-22  | 2019-08-24  | 2019-08-24  |
| A         | 2019-08-23  | 2019-08-25  | 0           |
| A         | 2019-08-24  | 2019-08-26  | 0           |
| B         | 2019-08-22  | 2019-08-24  | 0           |
| B         | 2019-08-24  | 2019-08-26  | 0           |
| C         | 2019-08-22  | 2019-08-24  | 0           |
| C         | 2019-08-23  | 2019-08-25  | 0           |
+-----------+-------------+-------------+-------------+--+