SQL练习题之统计连续登录七天的用户（开窗实现）

4. （困难）统计连续登录七天（含七天）以上的用户（开窗和不开窗都要求实现）。

SQL文件：user_sign.sql

实现思路：

#对日期排序，并用denserank 开窗排序
#对排序编号或日期的天去重，排除一天登录两次的情况
#让日期与排序序号做差，如若连续，则差会相等
#分组计数相同的差值
#差值相等的大于等于7则表示连续登录七天
#合并为最终语句

#对每个用户的登录日期排序，并进行开窗denserank非跳步排序
SELECT
	user_id,
	sign_date,
	DENSE_RANK() OVER (
		PARTITION BY user_id 
	ORDER BY
	DATE_FORMAT( sign_date, '%y%m%d' )) AS rn 
FROM
	user_sign;

由user_sign表可知，user_id=1的用户在28号登录的两次，只能算一天的登录，则需要对其去重：

#去重-按日期
SELECT DISTINCT
	rn,
	user_id,
	DATE_FORMAT( sign_date, '%y-%m-%d' ) sign_date 
FROM
	(
 SELECT
 user_id,
 sign_date, 
 DENSE_RANK() 
 OVER ( PARTITION BY user_id ORDER BY DATE_FORMAT( sign_date, '%y%m%d' )) AS rn 
FROM user_sign
 ) t1

#如果连续则每一行的日期与ranknumber的差值相等
SELECT
	user_id,
	sign_date,
	DATE_FORMAT( date_sub( sign_date, INTERVAL rn DAY ), '%d' ) AS diff_date 
FROM
	(
	SELECT DISTINCT
		rn,
		user_id,
		DATE_FORMAT( sign_date, '%y-%m-%d' ) sign_date 
	FROM
		(
 SELECT
 user_id,
 sign_date, 
 DENSE_RANK()
 OVER ( PARTITION BY user_id ORDER BY DATE_FORMAT( sign_date, '%y%m%d' )) AS rn FROM user_sign 
) t1 
) t2;

#id进行分组,统计次数
select user_id,count(diff_date) from(
select 
	user_id,
	sign_date,
	DATE_FORMAT(date_sub(sign_date,INTERVAL rn  day),'%d') as diff_date
FROM
	(
	select 
 DISTINCT rn,
 user_id,
 DATE_FORMAT(sign_date,'%y-%m-%d') sign_date from 
(
select
 user_id,
 sign_date,
 DENSE_RANK()
 OVER(PARTITION by user_id ORDER BY DATE_FORMAT(sign_date,'%y%m%d'))as rn from user_sign
) t1
) t2
) t3
GROUP BY user_id,diff_date;

#差值相等的大于等于7则表示连续登录七天
#合并为最终语句
with cds as(
select 
 user_id,
 count(diff_date) total 
from(
select 
	user_id,
	sign_date,
	DATE_FORMAT(date_sub(sign_date,INTERVAL rn  day),'%d') as diff_date
FROM
	(
 select 
 DISTINCT rn,
 user_id,
 DATE_FORMAT(sign_date,'%y-%m-%d') sign_date from 
(
 select
 user_id,
 sign_date,DENSE_RANK()
 OVER(PARTITION by user_id ORDER BY 
 DATE_FORMAT(sign_date,'%y%m%d'))as rn from user_sign
) t1
) t2
) t3
GROUP BY user_id,diff_date)
select user_id from cds where total>=7;