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

53 阅读 0 评论 35 点赞

我是靠谱客的博主重要月亮，最近开发中收集的这篇文章主要介绍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%"

在这里插入图片描述