使用Oracle SQL查询获取用户登录与登出活动记录

表结构与数据 #

现有UserActivity表，字段包括Id、UserID、DateTime、Activity，表数据如下：

Id    UserID         DateTime             Activity
123    abc      2023-08-24 09:30:50        Login
125    abc      2023-08-24 09:34:23        Login
150    abc      2023-08-24 09:38:04        Logout
178    abc      2023-08-24 13:23:12        Login
200    abc      2023-08-24 15:50:34        Logout
300    abc      2023-08-24 16:43:02        Login
505    abc      2023-08-25 10:32:12        Login
507    abc      2023-08-25 12:36:44        Logout

期望输出 #

需要列出所有登录时间及其对应的登出时间，无对应登出时登出时间为空：

UserID        LoginDateTime            LogoutDateTime
 abc       2023-08-24 09:30:50
 abc       2023-08-24 09:34:23       2023-08-24 09:38:04
 abc       2023-08-24 13:23:12       2023-08-24 15:50:34
 abc       2023-08-24 16:43:02
 abc       2023-08-25 10:32:12       2023-08-25 12:36:44

尝试的错误SQL #

SELECT
login.UserID,
login.DateTime As Login,
LogOut.DateTime As Logout
from
  UserActivity login
Left JOIN
  UserActivity LogOut ON Login.UserID=Logout.UserID
  AND login.Activity='login.label'
  AND Logout.Activity='logout.label'
  AND TO_CHAR(Login.DateTime, 'YYYY-MM-DD') = TO_CHAR(Logout.DateTime, 'YYYY-MM-DD')
  AND Login.DateTime<Logout.DateTime
Where login.UserID='abc'
  Order by login.UserID,login.DateTime;

原SQL存在两个核心问题：

1. 活动类型匹配错误：表中实际活动值是Login/Logout，而非login.label/logout.label，导致匹配不到对应记录。
2. 关联逻辑不合理：直接左连接会将所有符合日期范围的登出都关联到登录，产生重复数据，且无法精准匹配当前登录之后的第一条登出。

以下提供两种可行的解决方法：

方法1：使用窗口函数LEAD()（推荐） #

利用窗口函数按用户分组、时间排序，直接获取每条登录之后的第一条登出时间：

SELECT
    UserID,
    DateTime AS LoginDateTime,
    LogoutDateTime
FROM (
    SELECT
        UserID,
        DateTime,
        Activity,
        -- 筛选出后续第一条Logout的时间
        LEAD(CASE WHEN Activity = 'Logout' THEN DateTime END) OVER (
            PARTITION BY UserID 
            ORDER BY DateTime
        ) AS LogoutDateTime
    FROM UserActivity
    WHERE UserID = 'abc'
) t
WHERE Activity = 'Login' -- 仅保留登录记录
ORDER BY LoginDateTime;

方法2：使用关联子查询 #

若数据库不支持窗口函数，可通过子查询找到每条登录之后的最小登出时间：

SELECT
    login.UserID,
    login.DateTime AS LoginDateTime,
    (
        SELECT MIN(logout.DateTime)
        FROM UserActivity logout
        WHERE logout.UserID = login.UserID
          AND logout.Activity = 'Logout'
          AND logout.DateTime > login.DateTime
    ) AS LogoutDateTime
FROM UserActivity login
WHERE login.UserID = 'abc'
  AND login.Activity = 'Login'
ORDER BY login.DateTime;

效果说明 #

两种方法都能正确匹配每个登录对应的最近登出记录，无后续登出的登录会返回NULL，完全符合期望输出要求。

内容的提问来源于stack exchange，提问作者Amrut Gaikwad