Hive sql 七天内连续3天活跃sql查询

在正常生产或者练习中，会碰到要求统计在一段时间内有连续登录行为的用户数量，此要求的实现方式有多种， 现在整理一下自己会的和自己从其他大佬学到的方式 供共同学习使用，如果内容有错误的地方，请务必留言，给小弟改正的机会，不胜感激！（注：转载引用请注明出处）

实现方式1：
实现思想：计算每个用户本次活跃与下下次活跃相差天数 如果天数为2 则证明本次到下下为连续活跃三天
步骤：-- 1 将本次活跃与下下此活跃放在同一条数据中心
– 2 计算本次活跃与下下次活跃相差天数
– 3 筛选相差天数为2的用户 统计用户数量 可能会出现一个用户连续活跃3天的次数超过一次 故需要去重

select
	count(distinct user_id)
from
(
	select
		user_id, -- 用户id
		datediff(times,dt)  diff  --计算本次活跃与下下此活跃相差天数
	from
	(
		select
			user_id, -- 用户id
			dt,     -- 活跃时间
			lead(dt,2,'1970-01-01') over (partition by user_id order by dt)   times -- 	按照用户进行分区 然后按照时间进行排序
			                 -- 获取下下次活跃时间 如果没有下下次数据 则默认时间为计算机元年
		
		from dws_user_action_daycount
	)t1
)t2
where diff=2;

函数直达：lead()函数：函数值达 ；datefiff():函数直达；
实现方式2：
思想：将最近7天用户登录按照用户id进行分组 分组后对dt进行排序 然后对排序后的组内信息进行排名
– 计算登录时间和排名之间的差值 如果登录时间按着 则减去排名后的结果则相同 统计每个用户id 差值相等的个数
– 如果个数大于等于3则连续3天活跃
步骤：1 按照用户id分组 按照dt进行排序 后 使用rank() 函数进行排名
2 计算活跃时间dt 与排名之间的差值 将少于三次的数据进行过滤掉
3将用户id进行去重后统计数量

-- 将用户id进行去重后统计数量
select
	count(distinct user_id)
from
(
	    -- 按照用户和时间差值进行分组 统计相同时间的次数
	    select
	        user_id,
	        diff,
	        count(*)
	    from
	    (
	        -- 计算活跃时间dt 与排名之间的差值
	        select
	            user_id,
	            date_add(dt,-rk) diff
	        from
	        (
	            -- 按照用户id分组 按照dt进行排序 后 使用rank() 函数进行排名
	            select
	                user_id,
	                dt,
	                rank() over (partition by user_id order by dt) rk
	            from dws_user_action_daycount
	        )t1
	    )t2
	    group by user_id, diff
	    having count(*)>=3  -- 将少于三次的数据进行过滤掉
)t3

函数直达：rank()函数直达;date_add():函数直达；
实现方式3；
思想：七天按照天数先后顺序不同分别分为1-1000000 num 然后将同一用户的num组进行相加 如果相加结果出现连续三个1 则此用户三天内连续活跃
步骤：1 --增加一列数据 num 按照 dt分别赋值1-1000000
2 – 计算每个user_id 的num和
3 – 对num进行匹配 如果出现111 则证明此用户连续三天活跃

--  对num进行匹配 如果出现111 则证明此用户连续三天活跃
select
	count(*)
from
(
    -- 计算每个user_id 的num和
    select
        user_id,
        sum(num) count
    from
    (
       --增加一列数据 num 按照 dt分别赋值1-1000000 
        select
            user_id,
            dt,
            case
                when dt='2020-06-14' then 1000000
                when dt='2020-06-13' then 100000
                when dt='2020-06-12' then 10000
                when dt='2020-06-11' then 1000
                when dt='2020-06-10' then 100
                when dt='2020-06-09' then 10
                when dt='2020-06-08' then 1
            end num
        from dws_user_action_daycount
        where dt>=date_add('2020-06-14',-6)
    )t1
    group by user_id
)t2
where count like "%111%"