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