时间戳

首页 > 解决方案 > 登录程序 SQL 的总时间

sql - 登录程序 SQL 的总时间

问题描述

我有一个表，其中在同一列中测量了用户的登录和注销时间。该表如下所示：

offset    eventtimeutc                userID     propertyname 
-18000    2021-05-15 12:00:00.000     Jane Doe     login
-18000    2021-05-15 12:10:00.000     John Doe     login
-18000    2021-05-15 13:19:00.000     Jane Doe     logout
-18000    2021-05-15 13:25:00.000     Jane Doe     login
-18000    2021-05-15 18:00:00.000     Jane Doe     logout
-18000    2021-05-15 18:05:00.000     John Doe     logout

我正在尝试计算用户在每次登录和注销之间在程序中花费的时间。例如，Jane Doe 在第一次登录和注销之间花费了 1 小时 19 分钟，然后又花费了 4 小时 40 分钟。我尝试使用一条语句将登录和注销转换为两个单独的列，CASE WHEN propertyname = 'login' then eventtimeutc else null end as Login并对注销重复相同的操作，但我得到的结果是登录时注销列中的 NULL 值和注销时登录中的 NULL 值。有没有更好更快的方法来计算用户在每个实例中登录程序所花费的时间？谢谢您的帮助

标签： sqltsql

解决方案

这实际上是一个差距和孤岛问题。您可以通过使用窗口条件聚合来计算到目前为止的登录次数，从而将数据分组；每次登录都会将数字加 1，将值分组。然后，您可以使用一些额外的条件聚合来旋转数据以获取不同的登录和注销时间。我同时添加了秒和时间的值（请注意，如果您可以设置 24 小时或更长时间的值，则会出错，我建议您只使用秒）：

CREATE TABLE dbo.YourTable(offset int,
                           eventtimeutc datetime,
                           userID sysname,
                           propertyname varchar(6));
GO
INSERT INTO dbo.YourTable
VALUES(-18000,'2021-05-15T12:00:00.000',N'Jane Doe','login'),
      (-18000,'2021-05-15T12:10:00.000',N'John Doe','login'),
      (-18000,'2021-05-15T13:19:00.000',N'Jane Doe','logout'),
      (-18000,'2021-05-15T13:25:00.000',N'Jane Doe','login'),
      (-18000,'2021-05-15T18:00:00.000',N'Jane Doe','logout'),
      (-18000,'2021-05-15T18:05:00.000',N'John Doe','logout');
GO
WITH Grps AS(
    SELECT eventtimeutc,
           userID,
           propertyname,
           COUNT(CASE propertyname WHEN 'login' THEN 1 END) OVER (PARTITION BY userID ORDER BY eventtimeutc
                                                                  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS PropertyGrp
    FROM dbo.YourTable)
SELECT userID,
       MIN(CASE propertyname WHEN 'login' THEN eventtimeutc END) AS LoginStart,
       DATEDIFF(SECOND,MIN(CASE propertyname WHEN 'login' THEN eventtimeutc END),MAX(CASE propertyname WHEN 'logout' THEN eventtimeutc END)) AS LoginSeconds,
       DATEADD(SECOND,DATEDIFF(SECOND,MIN(CASE propertyname WHEN 'login' THEN eventtimeutc END),MAX(CASE propertyname WHEN 'logout' THEN eventtimeutc END)),CONVERT(time,'00:00')) AS LoginDuration
FROM Grps
GROUP BY UserID,
         PropertyGrp
ORDER BY LoginStart ASC;

GO
DROP TABLE dbo.YourTable;
GO

推荐阅读