TSQL中如何查找连续登陆用户

--===========================================

由于长久未写此类SQL,有点手生，本着走一步算一步的精神，慢慢来。

首先查看日志表

SELECT [Uid]
      ,[loginDate]
  FROM [dbo].[Member_LoginLog]
  WHERE [UID]=268

由于按天计算连续登陆，表中时间精确到毫秒，很难肉眼看出数据是否连续，于是考虑转换数据

而又由于我们只关心最早登陆时间和最后登陆时间，因此我们可以先按照天来统计用户最早登陆时间和最后登陆时间，并将时间转换成对应天数

--==============================================
--统计出用户每天最早登陆时间和最后登陆时间
SELECT T1.[UID]
,DATEDIFF(DAY,'2014-01-01',LoginDate) AS DiffDays
,MAX(LoginDate) AS MaxLoginDate
,MIN(LoginDate) AS MinLoginDate
INTO [dbo].[Member_LoginLog_Status1]
FROM [dbo].[Member_LoginLog] T1
GROUP BY T1.[UID],DATEDIFF(DAY,'2014-01-01',LoginDate)
--======================================
--查看效果
SELECT [UID]
,[DiffDays]
,[MaxLoginDate]
,[MinLoginDate]
FROM [dbo].[Member_LoginLog_Status1]
WHERE UID=268

从上图很容易看出第二天没连续登陆，是不是很容易看啊

接下来就是查找联系的天数了，如果我们按照UID分组，然后对DiffDays来排序求出排名来，依据DiffDays的增长量和RID量便可以判断出天数是否连续

SELECT
ROW_NUMBER()OVER(PARTITION BY UID ORDER BY [DiffDays] ASC) AS RID,
T1.*
FROM [dbo].[Member_LoginLog_Status1] T1
WHERE [UID]=268

这样我们便可以使用表的自连接来查找连续的登录，由于需要按照用户和天数来算出排名，因此我们可以先建立索引

CREATE CLUSTERED INDEX CIX_UID_Days ON
[dbo].[Member_LoginLog_Status1]
(
    [UID],[DiffDays]
)

然后再求连续区间：

--==========================================
--查找连续的登录
;WITH Tem AS(
SELECT
ROW_NUMBER()OVER(PARTITION BY UID ORDER BY [DiffDays] ASC) AS RID,
T1.*
FROM [dbo].[Member_LoginLog_Status1] T1
)
,Tem1 AS(
SELECT ROW_NUMBER()OVER(
    PARTITION BY T1.[UID],T1.[DiffDays] 
    ORDER BY T2.[diffdays]-T1.[diffdays] DESC) AS RID,
T1.[UID],
T1.MinLoginDate,
T2.MaxLoginDate,
T1.[diffdays] AS MinDiffDays,
T2.[diffdays] AS MAXDiffDays
FROM Tem AS T1
INNER JOIN Tem AS T2
ON T1.UID=T2.UID
AND T1.[diffdays]<=T2.[diffdays]
AND T2.[diffdays]-T1.[diffdays]= T2.RID-T1.RID
)
SELECT
[UID],
MinLoginDate,
MaxLoginDate,
MinDiffDays,
MAXDiffDays
INTO [dbo].[Member_LoginLog_Status2]
FROM Tem1 AS T1
WHERE T1.RID=1
--=========================================
--检查结果
SELECT [UID]
,[MinLoginDate]
,[MaxLoginDate]
,[MinDiffDays]
,[MAXDiffDays]
FROM [dbo].[Member_LoginLog_Status2]
WHERE [UID]=268

（编辑：滁州站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!