[大数据]关于SQL查询出连续登录天数的问题（MSSQL 2017）

前言

今天碰见了一个有点意思的SQL查询问题,题目如下：

依据题意，我动手创建了一个表，并添加了些测试数据(环境事情· MSSQL 2017)

暴力解法

我先把表中的时间部分转成 “年月日” 格式，并且把时间字段给 去重 一下，保留一天的时间，过滤一天之内多次登录的情况

因为题意是“连续登录天数”,所以时分秒…部分可以忽略掉了

最终语句

拿出刚刚上面筛选好的语句再加个 ‘2022-01’月份的过滤条件作为T1，然后自连接二次，每一次连接条件都是，时间差为1天

   SELECT T1.uid, T1.login_time AS 'cons_day1',T2.login_time AS 'cons_day2',T3.login_time 'cons_day3'
   FROM (
       SELECT DISTINCT  uid,CONVERT(varchar(10),login_time,120) AS 'login_time'  
       FROM [dbo].[t_login]  WHERE CONVERT(varchar(7),login_time,126)='2022-01' ) T1
   INNER JOIN 
   (
       SELECT DISTINCT  uid,CONVERT(varchar(10),login_time,120) AS 'login_time'  
       FROM [dbo].[t_login]  WHERE CONVERT(varchar(7),login_time,126)='2022-01' ) T2
   ON T1.uid=T2.uid AND DATEDIFF(DAY,T1.login_time,T2.login_time)=1
   INNER JOIN 
   (
       SELECT DISTINCT  uid,CONVERT(varchar(10),login_time,120) AS 'login_time'  
       FROM [dbo].[t_login]  WHERE CONVERT(varchar(7),login_time,126)='2022-01' ) T3
   ON T2.uid=T3.uid AND DATEDIFF(DAY,T2.login_time,T3.login_time)=1

执行结果

问题的描述是查询出2022-01月份连续登录3天的用户。
虽然这条语句成功的解决了问题，但是并不灵活，因为语句是依据指定的N天数，然后固定添加N条自连接。 连续登录3天，就得扩展3条，10天就得扩展10条，显然是不合理的。。。。

灵活解法

我还是以这条语句作为基础，算出每个用户，在2022-01月份每天登陆的情况

  SELECT  DISTINCT uid,CONVERT(VARCHAR(10),login_time,126)  AS  'login_time'
  FROM [dbo].[t_login]  WHERE CONVERT(varchar(7),login_time,126)='2022-01'

然后对每个时间，进行一次排序，因为这里需要使用到排序，所以 DISTINCT 去重肯定是用不了，所以改造一下使用GROUP BY 对uid和年月日的时间进行分组吧过滤吧

  SELECT  
   uid,
   CONVERT(VARCHAR(10),login_time,126)  AS  'login_time',
   ROW_NUMBER() OVER( PARTITION BY uid ORDER BY CONVERT(VARCHAR(10),login_time,126) ) AS 'rows'
  FROM [dbo].[t_login]  WHERE CONVERT(varchar(7),login_time,126)='2022-01'
  GROUP BY uid,CONVERT(VARCHAR(10),login_time,126)

结果分析

先把这条作为一个基础的临时表,然后对这张临时表操作一下 login_time 减去rows 字段 看看是什么情况

WITH Temp AS(
 SELECT  
   uid,
   CONVERT(VARCHAR(10),login_time,126)  AS  'login_time',
   ROW_NUMBER() OVER( PARTITION BY uid ORDER BY CONVERT(VARCHAR(10),login_time,126) ) AS 'rows'
  FROM [dbo].[t_login]  WHERE CONVERT(varchar(7),login_time,126)='2022-01'
  GROUP BY uid,CONVERT(VARCHAR(10),login_time,126)
  )

  SELECT uid,login_time,rows,(day(login_time)-rows) diff FROM Temp

最终语句

WITH Temp AS(
 SELECT  
   uid,
   CONVERT(VARCHAR(10),login_time,126)  AS  'login_time',
   ROW_NUMBER() OVER( PARTITION BY uid ORDER BY CONVERT(VARCHAR(10),login_time,126) ) AS 'rows'
  FROM [dbo].[t_login]  WHERE CONVERT(varchar(7),login_time,126)='2022-01'
  GROUP BY uid,CONVERT(VARCHAR(10),login_time,126)
  )

  SELECT uid,MIN(login_time) 'min登录时间',MAX(login_time) 'max登录时间',count(1) AS '连续登陆天数'
  FROM Temp group by uid,(day(login_time)-rows) 

再回过头来，看看题意，查询出每个用户连续登录3天的信息，那这个就好办了，再加个having 筛选一下

WITH Temp AS(
 SELECT  
   uid,
   CONVERT(VARCHAR(10),login_time,126)  AS  'login_time',
   ROW_NUMBER() OVER( PARTITION BY uid ORDER BY CONVERT(VARCHAR(10),login_time,126) ) AS 'rows'
  FROM [dbo].[t_login]  WHERE CONVERT(varchar(7),login_time,126)='2022-01'
  GROUP BY uid,CONVERT(VARCHAR(10),login_time,126)
  )

  SELECT uid,MIN(login_time) 'min登录时间',MAX(login_time) 'max登录时间',count(1) AS '连续登陆天数'
  FROM Temp GROUP BY  uid,(day(login_time)-rows)  HAVING count(1)>3

这样就完美解决喽！

测试语句

方便大家快速的测试，我把表结构脚本发给大家

USE [School]
GO
/****** Object:  Table [dbo].[t_login]    Script Date: 2022-05-08 18:52:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[t_login](
	[uid] [int] NULL,
	[login_time] [datetime] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[t_login] ([uid], [login_time]) VALUES (1, CAST(N'2022-01-01T08:05:11.000' AS DateTime))
GO
INSERT [dbo].[t_login] ([uid], [login_time]) VALUES (2, CAST(N'2022-01-01T08:05:11.000' AS DateTime))
GO
INSERT [dbo].[t_login] ([uid], [login_time]) VALUES (3, CAST(N'2022-01-01T11:05:11.000' AS DateTime))
GO
INSERT [dbo].[t_login] ([uid], [login_time]) VALUES (1, CAST(N'2022-01-01T09:05:11.000' AS DateTime))
GO
INSERT [dbo].[t_login] ([uid], [login_time]) VALUES (1, CAST(N'2022-01-01T10:05:11.000' AS DateTime))
GO
INSERT [dbo].[t_login] ([uid], [login_time]) VALUES (1, CAST(N'2022-01-01T11:05:11.000' AS DateTime))
GO
INSERT [dbo].[t_login] ([uid], [login_time]) VALUES (1, CAST(N'2022-01-02T08:05:11.000' AS DateTime))
GO
INSERT [dbo].[t_login] ([uid], [login_time]) VALUES (1, CAST(N'2022-01-03T09:05:11.000' AS DateTime))
GO
INSERT [dbo].[t_login] ([uid], [login_time]) VALUES (1, CAST(N'2022-01-04T10:05:11.000' AS DateTime))
GO
INSERT [dbo].[t_login] ([uid], [login_time]) VALUES (1, CAST(N'2022-01-05T11:05:11.000' AS DateTime))
GO
INSERT [dbo].[t_login] ([uid], [login_time]) VALUES (1, CAST(N'2022-01-06T08:05:11.000' AS DateTime))
GO
INSERT [dbo].[t_login] ([uid], [login_time]) VALUES (1, CAST(N'2022-01-07T08:05:11.000' AS DateTime))
GO
INSERT [dbo].[t_login] ([uid], [login_time]) VALUES (2, CAST(N'2022-01-01T09:05:11.000' AS DateTime))
GO
INSERT [dbo].[t_login] ([uid], [login_time]) VALUES (2, CAST(N'2022-01-02T08:05:11.000' AS DateTime))
GO
INSERT [dbo].[t_login] ([uid], [login_time]) VALUES (3, CAST(N'2022-01-02T08:05:11.000' AS DateTime))
GO
INSERT [dbo].[t_login] ([uid], [login_time]) VALUES (3, CAST(N'2022-01-03T09:05:11.000' AS DateTime))
GO
INSERT [dbo].[t_login] ([uid], [login_time]) VALUES (3, CAST(N'2022-01-04T10:05:11.000' AS DateTime))
GO
INSERT [dbo].[t_login] ([uid], [login_time]) VALUES (3, CAST(N'2022-01-05T11:05:11.000' AS DateTime))
GO
INSERT [dbo].[t_login] ([uid], [login_time]) VALUES (1, CAST(N'2022-01-11T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[t_login] ([uid], [login_time]) VALUES (2, CAST(N'2022-01-11T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[t_login] ([uid], [login_time]) VALUES (3, CAST(N'2022-01-11T00:00:00.000' AS DateTime))
GO

创作打卡挑战赛

赢取流量/现金/CSDN周边激励大奖