使用SQL优雅的计算日期连续性问题

一、造数据

DROP TABLE IF EXISTS CSDN_TEST;
CREATE TABLE CSDN_TEST (
	user_id CHAR(4) COMMENT '用户id',
	access_year INT(4) COMMENT '访问年'
);

INSERT INTO CSDN_TEST VALUES 
('0001',1999),
('0002',1999),
('0001',2000),
('0002',2001),
('0001',2001),
('0002',2002),
('0001',2002),
('0002',2003),
('0001',2005),
('0001',2006),
('0001',2007);

二、查看数据结构

SELECT * FROM CSDN_TEST;

三、需求（计算出数据中每个用户的最长连续访问年数）

• 1.使用开窗函数 row_number() 计算出每位用户访问记录中每一年在组内中的序号。

  SELECT
  	user_id,
  	access_year,
  	row_number() over(partition by user_id order by access_year) as rn
  FROM
  	CSDN_TEST;	
  

  

• 2.核心步骤：在上一次计算基础之上使用access_year 减去 rn

  SELECT 
  	user_id,
  	access_year,
  	row_number() over(partition by user_id order by access_year) as rn, 
  	access_year - row_number() over(partition by user_id order by access_year) as diff 
  FROM CSDN_TEST
  

  
  到此，细心的朋友就已经返现图中的规律了。
  规律就是在一个用户的所有访问年当中，如果年是连续的那么它们的 diff 值是相同的

• 3.求出每个用户的所有连续登录年数的情况

  SELECT *
  	user_id,
  	count(*) AS years
  FROM tmp
  GROUP BY user_id,diff
  

  

注意：这里使用的 tmp 表就是前面一步计算出来的表，为了减少SQL 的复杂性，所以笔者简化了SQL,还请读者自行做一下改变

可以看出我们现在已经求出每个用户所有的连续访问年数，现在最后一步要做的就是利用上一步得出的数据，计算每个用户的最大访问年数 （还是以一样的我们把现在这一步骤得出的数据又命名为 tmp2 临时表）

• 4.求出每个用户的最大连续访问年数

  SELECT 
  	user_id,
  	max(years) as max_years
  FROM tmp2
  GROUP BY user_id