SQL Server 查询连续月份的方法

       最近一个朋友那有这么一个需求：需要查询出连续6个月有登陆的用户，并且查询的时间段可能会有跨年的情况。今天就分享一下我帮朋友解决的查询连续月份的方法。

首先模拟一个用户登陆表

CREATE TABLE UserLogin (
	UserName VARCHAR(10), --用户名
	LoginTime DATETIME    --登陆日期
)

 

插入一些模拟数据，简单模拟一下用户的登陆信息，其中 A 用户连续每个月都有登陆，B 用户的登陆时间中间有间断。B用户有不连续6个月的情况。

 

INSERT INTO dbo.UserLogin ( UserName, LoginTime )
VALUES  ( 'A', '2018-01-01 09:23:32'),( 'B', '2018-01-01 12:34:34'),
	( 'A', '2018-02-04 14:43:21'),( 'B', '2018-02-15 21:56:23'),
	( 'A', '2018-03-25 08:54:12'),( 'B', '2018-03-01 09:42:42'),
	( 'A', '2018-04-05 10:02:41'),
	( 'A', '2018-05-06 07:35:52'),
	( 'A', '2018-06-20 11:23:12'),( 'B', '2018-06-06 10:15:36'),
	( 'A', '2018-07-01 14:50:56'),
	( 'A', '2018-08-20 09:21:32'),( 'B', '2018-08-15 10:15:36'),
	( 'A', '2018-09-12 10:14:21'),( 'B', '2018-09-02 12:30:45'),
	( 'A', '2018-10-02 11:50:58'),( 'B', '2018-10-10 13:20:54'),
	( 'A', '2018-11-26 08:05:41'),( 'B', '2018-11-26 10:25:40'),
	( 'A', '2018-12-16 11:26:35'),( 'B', '2018-12-18 14:15:26'),
	( 'A', '2019-01-01 12:21:32'),( 'B', '2019-01-15 12:35:36'),
	( 'A', '2019-02-05 08:19:26'),( 'B', '2019-02-08 10:45:34'),
	( 'A', '2019-03-20 10:34:38'),( 'B', '2019-03-06 11:15:48')

    其实实现查询连续月份的方法很简单，就是先 Group By 出来每个用户的登陆月份，使用 ROW_NUMBER() 函数给每个用户一个排序的序号。然后用用户的登陆月份与序号相减，如果月份连续，则相减后的数就是一样的。这样就能找到连续的月份了。

    如果查询时间跨年了怎么办呢？如果遇到跨年的情况，我就在取用户登陆月份的时候先判断 LoginTime 里面的年份是否小于查询的起始时间的年份，如果小于，则在获取的月份上加上12 。比如查询“2017-10-15”到 “2018-04-20”之间，我的处理方式获取2018年1月，则在1的基础上加上12，变成13 ，2月变成14，以此类推。这样就能保证月份数与排序数相减，月份连续值相等了。

看下面的图示，大家应该就能清楚是什么原理了。

得到上图的结果后，再根据“用户名”和“相减的值”来Group By，最后Count一下“相减的值”出现多少行，大于等于6就表明连续6个月都有登陆了。

    具体的实现语句如下：

-- 第三步：计算出现连续的月份数量。HAVING 筛选出出现6次以上的用户，出现6次以上即表示连续6月以上有登陆
SELECT aa.UserName,COUNT(aa.Con_M) AS '连续登录月份数量' 
FROM (
	-- 第二步：月份 减去 排序的序号 。如果是连续的月份，则相减的值是相同的。
	SELECT a.UserName 
	,( a.MM - ROW_NUMBER() OVER(PARTITION BY a.UserName ORDER BY a.MM) ) AS 'Con_M'
	FROM (
		--第一步：根据用户名，月份Group ，跨年的情况在月份上加12
		SELECT  UserName
		,CASE WHEN YEAR(@B_Time)<YEAR(LoginTime) THEN MONTH(LoginTime)+12 ELSE MONTH(LoginTime) END AS 'MM' --跨年的情况，月份+12
		FROM dbo.UserLogin 
		WHERE LoginTime >= @B_Time AND LoginTime <= @E_Time
		GROUP BY UserName
		,CASE WHEN YEAR(@B_Time)<YEAR(LoginTime) THEN MONTH(LoginTime)+12 ELSE MONTH(LoginTime) END
	) a 
) aa 
GROUP BY aa.UserName,Con_M
HAVING COUNT(aa.Con_M)>=6  --连续6个月有登陆，如果只要连续3个月，则此条件改为3

 

查询结果如下所示：

 

可以看到，在18年6月到19年3月之间，A用户连续登录了10个月，B用户连续的月份只有8个月。

如果查询 18年1月到6月，则只能查出A用户来，B用户18年1月到6月没有连续登录。