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