SQL Server中按日期统计用户指定时间范围登录时长的查询需求

要实现按日统计用户在指定时间范围内的登录时长，核心是先生成查询区间内的所有日期，再关联用户登录记录计算每日的有效登录时长。以下是完整的SQL语句：

-- 定义查询的时间范围
DECLARE @StartDate DATE = '2023-05-01';
DECLARE @EndDate DATE = '2023-05-05';

-- 生成查询范围内的所有日期
WITH DateRange AS (
    SELECT @StartDate AS [date]
    UNION ALL
    SELECT DATEADD(DAY, 1, [date])
    FROM DateRange
    WHERE [date] < @EndDate
),
-- 准备用户登录记录的时间边界
UserSessions AS (
    SELECT
        user_id,
        login_time,
        logout_time
    FROM YourTableName -- 替换为实际的数据表名
)
SELECT
    dr.[date],
    users.user_id,
    -- 计算当日的登录时长（秒），无重叠则返回0
    ISNULL(DATEDIFF(SECOND,
        CASE WHEN us.login_time >= dr.[date] THEN us.login_time ELSE dr.[date] END,
        CASE WHEN us.logout_time < DATEADD(DAY, 1, dr.[date]) THEN us.logout_time ELSE DATEADD(DAY, 1, dr.[date]) END
    ), 0) AS login_duration_seconds
FROM DateRange dr
CROSS JOIN (SELECT DISTINCT user_id FROM UserSessions) users
LEFT JOIN UserSessions us
    ON users.user_id = us.user_id
    AND us.login_time < DATEADD(DAY, 1, dr.[date]) -- 登录时间早于次日0点
    AND us.logout_time > dr.[date] -- 登出时间晚于当日0点
ORDER BY dr.[date], users.user_id;

关键逻辑说明 #

1. 生成日期范围：通过递归CTE DateRange 生成从@StartDate到@EndDate的所有日期，确保每个日期都出现在结果中。
2. 关联用户与日期：用CROSS JOIN将所有用户与生成的日期配对，保证每个用户在查询范围内的每一天都有记录。
3. 计算每日有效时长：
   • 取登录时间和当日0点的较晚值作为当日有效登录起始点
   • 取登出时间和次日0点的较早值作为当日有效登录结束点
   • 用DATEDIFF(SECOND, ...)计算两个时间点的秒数差，无重叠时LEFT JOIN返回NULL，用ISNULL转为0。

注意事项 #

• 将YourTableName替换为实际存储登录记录的数据表名。
• 如果需要统计特定用户，可以在UserSessions的查询中添加WHERE user_id = 101条件，或在最终查询的WHERE子句中过滤。

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