HQL:连续签到_三种解决方案

表结构

/*
table_name : user_log
fields:
1.uid
2.artical_id 视频id
3.in_time 进入时间
4.out_time 离开时间
5.sign_in 是否签到

几条表数据

考察到的知识点

1. date_sub()
2. datediff()
3. last_value(field,true/false) over()
4. lag(field) over()
5. sum(),count()
6. 对于开窗函数的界限问题 /* 1.不指定partition by 默认全表 2.不指定order by 默认全表,可用于sum()场景 3.不使用窗口句子 1.不适用order by 默认全表 2.使用order by,默认第一行到当前行

思路

第一步肯定是先找出所有签到的数据 等差法 然后我们以用户分组,in_time排序,然后进行排序,如果连续,那么in_time - 排序的值必定得到一个相等的值,然后我们再根据 差值+uid 分组即可得到连续的值 累加求和法 可以先获取每条数据上一条数据的in_time值,然后相减,等差1则置为0,否则置为1,然后我们在通过一个开窗函数进行累加 最后再通过 uid+累加值分组即可 填充法 填充法也是要先获取上一条记录然后做差，相同为null,因为第一天签到无法获取上一天，那么我们可以特殊处理，如果为null的话，值设置为in_time 然后通过last_value() over() 填充,

方案一:等差法

/*
select
	uid,
	flag,
	count(1)
from(
    select
        uid,
        date_format(in_time,yyyy-MM-dd),
        date_sub(date_format(in_time,yyyy-MM-dd),rank() over(partition by uid,order by in_time asc)) flag
    from user_log
    where sign_in=1
)t 
group by uid,flag

方案2 累加求和法

/*
select
	uid,
	mark,
	count(1)
from(
    select
        uid,
        sum(ck) over(partition by uid order by in_time) mark
    from(
        select
            uid,
            in_time,
            if(in_time = null ,1,if(datediff(in_time,flag)=1,0,1)) ck
        from(
            select
                uid,
                date_format(in_time,yyyy-MM-dd) in_time,
                lag(date_format(in_time,yyyy-MM-dd)) over(partition by uid,order by in_time asc)flag
            from user_log
            where sign_in=1
        )t
    )t2
)t3
group by uid,mark

方案三 填充法

select
	uid,
	mark,
	count(1)
from(
    select
        uid,
        last_value(ck,true) over(partition by uid,order by in_time) mark
    from(
        select
            uid,
            in_time,
            if(in_time = null ,in_time,if(datediff(in_time,flag)=1,null,in_time)) ck
        from(
            select
                uid,
                date_format(in_time,yyyy-MM-dd) in_time,
                lag(date_format(in_time,yyyy-MM-dd)) over(partition by uid,order by in_time asc)flag
            from user_log
            where sign_in=1
        )t
    )t2
)t3
group by uid,mark