sql-server - SQL Server 2008 R2 - 在 Lag / CTE 中考虑前一天 - 使用上一行或下一行的值

Question

我正在关注此人的教程“如何在 SQL Server 查询中使用上一行或下一行的值”。

这是他使用的主要查询：

;WITH UserActivityWRowNum(RowNumber, User, Activity, ActivityTime, ActivityDate)
AS
(
SELECT ROW_NUMBER()OVER(ORDER BY user, ActivityTime) RowNumber
,User
,Activity
,ActivityTime
,CAST(ActivityTime as DATE) as ActivityDate
FROM dbo.UserActivity

SELECT  LogOns.User, LogOns.ActivityDate
,LogOffs.ActivityTime LogOff
,LogOns.ActivityTime LogOn
, DATEDIFF(minute, LogOffs.ActivityTime, LogOns.ActivityTime) IdleTime
FROM (SELECT * FROM UserActivityWRowNum WHERE Activity='LogOn') LogOns
JOIN (SELECT * FROM UserActivityWRowNum WHERE Activity='LogOff') LogOffs
      ON LogOffs.RowNumber=LogOns.RowNumber - 1
      AND LogOffs.User - LogOns.User
      AND LogOffs.ActivityDate - LogOns.ActivityDate

我试图考虑用户在两天之间有登录/注销周期的天数。这使得计算每天的总空闲时间变得困难。本质上，我如何考虑跨越两天的注销/登录周期并给出特定日期的总空闲时间？每个用户每天的总空闲时间是链接中所述的预期输出

例如，下图显示了第 6 行的注销活动。因此从技术上讲，用户在当天剩余的 7 小时内处于空闲状态（2013-01-01）。2013-01-02 将有一个虚拟注销作为显示为 2013-01-02 00:00:00 的第一个条目

标签： sql-serversql-server-2008-r2common-table-expression