我試圖總結出用戶的登錄，注銷資訊。如果用戶具有事件型別 1 或 3 之一，則兩者都被視為登錄事件。如果用戶有 6,7 或 13，則將其視為注銷事件。每次用戶有這種事件型別時，我想考慮發生的第一個事件并計算他們登錄的時間。
這是示例資料：
DECLARE @T AS TABLE
(
ID INT,
access_time datetime,
EventId varchar(10),
EventType varchar(25)
)
insert into @T VALUES
(123,'2021-10-15 06:00:29', 1, 'Autheticated' ),
(123,'2021-10-15 06:00:39', 3, 'Loggedin' ),
(123,'2021-10-15 08:00:14', 6, 'Loggedout' ),
(123,'2021-10-15 13:00:39', 3, 'Loggedin' ),
(123,'2021-10-16 00:00:12', 6, 'Loggedout' ),
(123,'2021-10-16 00:00:39', 7, 'Timedout' ),
(123,'2021-10-16 00:15:40', 13, 'ApplicationClosed' ),
(123,'2021-10-17 04:32:16', 3, 'Loggedin' ),
(123,'2021-10-17 15:45:20', 7, 'Timedout' ),
(123,'2021-10-17 15:47:40', 13, 'ApplicationClosed' )
DECLARE @final AS TABLE
(
ID INT,
Login_time datetime,
Logout_time datetime
)
insert into @final VALUES
(123,'2021-10-15 06:00:29', '2021-10-15 08:00:14'),
(123,'2021-10-15 13:00:39', '2021-10-16 00:00:12'),
(123,'2021-10-17 04:32:16', '2021-10-17 15:45:20')
select * from @final
我嘗試使用該lead函式，但由于幾天內缺少事件，我沒有得到預期的結果。如何在@final不使用lead函式的情況下獲取格式化的資料？
uj5u.com熱心網友回復：
這是一種間隙和孤島問題。
有很多解決方案，這里是一個
- 使用計算每組行的起點
LAG - 使用運行條件計數，計算每個組的 ID
- 然后簡單地使用這些 ID 分組
WITH StartPoints AS (
SELECT *,
IsStart = CASE WHEN EventId IN (1,3) AND LAG(EventId, 1, -1) OVER (PARTITION BY ID ORDER BY access_time) NOT IN (1,3) THEN 1 END
FROM @T
),
Groupings AS (
SELECT *,
GroupId = COUNT(IsStart) OVER (PARTITION BY ID ORDER BY access_time)
FROM StartPoints
)
SELECT
ID,
Login_time = MIN(access_time),
Logout_time = MAX(CASE WHEN EventId IN (6,7,13) THEN access_time END)
FROM Groupings g
GROUP BY
ID,
GroupId;
資料庫<>小提琴
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/337061.html
標籤：sql sql-server 查询语句