SQL篇- 最大连续登录天数

​

• 问题描述（sql插入代码在底部）
• 现有某乎问答创作者信息表author_tb如下(其中author_id表示创作者编号、author_level表示创作者级别，共1-6六个级别、sex表示创作者性别)：
  
• 创作者回答情况表answer_tb如下（其中answer_date表示创作日期、author_id指创作者编号、issue_id指回答问题编号、char_len表示回答字数）：

- 问题：求出最大连续登录天数

-一、求日期和序列之差

1. ：利用窗口函数，以author_id分区求出按照日期排名的 连续不间断序列
2. ： 利用日期与序列之差，得到的字符串
3. ！！必须要以用户ID和日期分组

	select answer_date,author_id,
			row_number() over(partition by author_id order by answer_date) 序列
		   , answer_date-(row_number() over(partition by author_id order by answer_date))
			as dt
		from answer_tb  
          GROUP BY answer_date,author_id

二、分组计数

1. ：以用户ID和 dt（日期和序列之差的字符串）分组计数（count(dt))

	select author_id,dt,count(dt) as cnt_dt
	from(
		select answer_date,author_id,
			row_number() over(partition by author_id order by answer_date) 序列
		   , answer_date-(row_number() over(partition by author_id order by answer_date))
			as dt
		   from answer_tb  
          GROUP BY answer_date,author_id
		) tt

三、获取最大值

select author_id,max(cnt_dt)
from(
	select author_id,dt,count(dt) as cnt_dt
	from(
		select answer_date,author_id,
			row_number() over(partition by author_id order by answer_date) 序列
		   , answer_date-(row_number() over(partition by author_id order by answer_date))
			as dt
		from answer_tb  
          GROUP BY answer_date,author_id
			) tt
	group by author_id,dt
    )t
group by author_id

​