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

最新推荐文章于 2024-09-16 12:18:21 发布

涂作权的博客

最新推荐文章于 2024-09-16 12:18:21 发布

阅读量539

点赞数

分类专栏： # HIVE（数据库仓库工具）

本文链接：https://blog.csdn.net/tototuzuoquan/article/details/119206211

版权

HIVE（数据库仓库工具）专栏收录该内容

24 篇文章 5 订阅

订阅专栏

这篇博客探讨了如何使用SQL进行数据查询，包括创建表`test2`并插入数据，然后通过子查询和窗口函数计算最大登录天数和连续登录指定天数的记录。还展示了`DATEDIFF`函数的用法，筛选日期间隔大于特定值的记录。

摘要由CSDN通过智能技术生成

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;