连续三天访问的用户情况-mysql

– mysql

select author_id,max(daycnt) as days_cnt
    from (
        select author_id,firstdt,count(*) as daycnt
        from (
             select distinct author_id,answer_date  , 
			 date_sub(answer_date, interval dense_rank() over(partition by author_id order by answer_date )-1 day) as firstdt
			 from answer_tb as a 
        )as b
        group by author_id,firstdt
    ) as c 
    group by author_id 

思路：使用dense_rank 窗口函数，根据author_id 进行分组，时间进行排序，
然后用时间-这个递增的数字，如果连续发布则 时间-对应的数字 产生的时间是一样的（连续的第一天）

– sql server

drop table if exists #answer_tb
create table #answer_tb
(
    author_id varchar(200)
    ,answer_date  date, 
)
insert into #answer_tb
select 'a','2022-07-01'
union all 
select 'a','2022-07-02'
union all 
select 'a','2022-07-03'
union all 
select 'b','2022-07-04'
union all 
select 'b','2022-07-05'


select distinct author_id,answer_date,dense_rank() over(partition by author_id order by answer_date ) as firstdtcnt,DATEADD(dd,1,DATEADD(dd,-1*dense_rank() over(partition by author_id order by answer_date ),answer_date)) as firstdt
from #answer_tb as a 
  

	select author_id,firstdt,count(*) as daycnt
	from (
	        select distinct author_id,answer_date,dense_rank() over(partition by author_id order by answer_date ) as firstdtcnt 
	                        ,DATEADD(dd,1,DATEADD(dd,-1*dense_rank() over(partition by author_id order by answer_date ),answer_date)) as firstdt
	from #answer_tb as a 
	)as b
	group by author_id,firstdt

# 求解 连续 多少天访问过的用户id，和天数
select author_id,max(daycnt) as days_cnt
from (
    select author_id,firstdt,count(*) as daycnt
    from (
            select distinct author_id,answer_date,dense_rank() over(partition by author_id order by answer_date ) as firstdtcnt 
                            ,DATEADD(dd,1,DATEADD(dd,-1*dense_rank() over(partition by author_id order by answer_date ),answer_date)) as firstdt
			from #answer_tb as a 
    )as b
    group by author_id,firstdt
) as c 
group by author_id