hive sql之：最大登录天数，获取连续登录指定天数

谁践踏了优雅 2022-09-02 00:49 214阅读 0赞

create table test2(
      id string,
      pday string
    );
    
    INSERT INTO test2(id,pday) values ('A','20190701');
    INSERT INTO test2(id,pday) values ('A','20190702');
    INSERT INTO test2(id,pday) values ('A','20190703');
    INSERT INTO test2(id,pday) values ('A','20190704');
    INSERT INTO test2(id,pday) values ('A','20190706');
    INSERT INTO test2(id,pday) values ('A','20190707');
    INSERT INTO test2(id,pday) values ('A','20190708');
    INSERT INTO test2(id,pday) values ('A','20190711');
    INSERT INTO test2(id,pday) values ('A','20190712');
    
    INSERT INTO test2(id,pday) values ('B','20190629');
    INSERT INTO test2(id,pday) values ('B','20190630');
    INSERT INTO test2(id,pday) values ('B','20190701');
    INSERT INTO test2(id,pday) values ('B','20190704');
    INSERT INTO test2(id,pday) values ('B','20190706');

**最大登录天数**

select 
      t2.id,
      max(t2.num)
    from 
    (
    	select 
    	t.id as id,
    	count(t.sub) num
    	from 
    	(
    		select 
    			id,
    			pday,
    			date_sub(
    				from_unixtime(unix_timestamp(pday,'yyyyMMdd'),'yyyy-MM-dd'),
    				row_number() over(partition by id order by pday)
    			) as sub
    		from test2
    	) as t
    	group by t.id,t.sub
    ) t2
    group by t2.id;

**获取连续登录指定天数的：**

select
    t.id as id,
    t.pday as pday,
    date_sub(t.pday,rn) as data_sub,
    t.rn as rn
    from 
    (
    	select 
    	id,
    	from_unixtime(unix_timestamp(pday,'yyyyMMdd'),'yyyy-MM-dd') as pday,
    	row_number() over(partition by id order by pday desc) as rn
    	from test2
    ) t
    where t.rn = 3;

===============================================  
**datediff的用法**

select *
    from 
    (
    select 
    id,
    from_unixtime(unix_timestamp(pday,'yyyyMMdd'),'yyyy-MM-dd') as pday,
    date_sub(
    from_unixtime(unix_timestamp(pday,'yyyyMMdd'),'yyyy-MM-dd'),
    row_number() over(partition by id order by pday)
    ) date_sub
    from test2
    ) t2 
    where datediff(t2.pday,t2.date_sub) > 2;