基于条件 SQL 创建新变量-解网

问：

我正在处理一个项目，并有一个包含以下数据的表：

日期	时间	用户名	类型	session_time
2023-08-28	07:56:50.0000000	阿弗里35	登录	零
2023-08-28	08:07:53.0000000	阿弗里35	视图	零
2023-08-28	08:08:48.0000000	阿弗里35	更新	零
2023-08-28	08:08:48.0000000	阿弗里35	更新	零
2023-08-28	08:08:48.0000000	阿弗里35	更新	零

我正在尝试找到一种方法来更新变量以查找日期 = 日期和用户名 = 用户名的位置，然后减去 logout：time - login：timesession_time

部分问题在于，每个用户不一定都有注销时间。在这种情况下，我想转到他们当天的最后一个操作，并为该日期、时间和用户名创建一个新行，并创建一个注销实例。

我认为所有“类型”变量都在同一列中是不可能的。因此，我创建了三个新变量login_time、logout_time session_time。然后我运行了以下代码：

Alter Table ##remote_users
Add session_time date;

Alter Table ##remote_users
Add login_time time;

Alter Table ##remote_users
Add logout_time time;

Update ##remote_users
set login_time = time
where username = username AND date = date AND Type = 'login';

Update ##remote_users
set logout_time = time
where username = username AND date = date AND Type = 'logout';

Update ##remote_users
set session_time = login_time - logout_time
where username = username AND date = date;

select date, time, username, type, login_time, logout_time
from ##remote_users
WHERE type = 'login' OR type = 'logout' OR type = 'sessiontimeout'
order by username

这很接近，因为它创建了我需要的变量，但session_time更新不起作用，它给出了以下错误：

消息 8117，级别 16，状态 1，第 57
行操作数数据类型时间对于减法运算符无效。

此外，没有注销时间的用户将获得变量 logout_time 的 null 值。

sql sql-server sql-update 变量赋值 datediff

select cast(date as date) as date, cast(time as time) as time, type, cast(session_time as datetime) as session_time, username
,   CAST(NULL AS TIME) AS login_time
,   CAST(NULL AS TIME) AS logout_time
into #remote_users
from 
(
    VALUES  (N'2023-08-28', N'07:56:50.0000000', N'aavery35', N'login', NULL)
    ,   (N'2023-08-28', N'08:07:53.0000000', N'aavery35', N'view', NULL)
    ,   (N'2023-08-28', N'08:08:48.0000000', N'aavery35', N'update', NULL)
    ,   (N'2023-08-28', N'08:08:48.0000000', N'aavery35', N'update', NULL)
    ,   (N'2023-08-28', N'17:08:48.0000000', N'aavery35', N'logout', NULL)
    ,   (N'2023-08-29', N'03:08:48.0000000', N'aavery35', N'login', NULL)
    ,   (N'2023-08-29', N'08:08:48.0000000', N'aavery35', N'update', NULL)
    ,   (N'2023-08-30', N'08:08:48.0000000', N'aavery35', N'login', NULL)
    ,   (N'2023-08-30', N'10:08:48.0000000', N'aavery35', N'logout', NULL)
    ,   (N'2023-08-30', N'13:08:48.0000000', N'aavery35', N'secret', NULL)
) t (date,time,username,type,session_time)

-- #1 Last time version
update  t
set login_time = loginTimeNew
,   logout_time = t.logoutTimeNew
,   session_time = CAST(dateadd(ms, datediff(ms, logintimenew, logouttimenew) , '19000101') AS TIME)
FROM    (
    select  min(time) OVER(PARTITION BY username, date) as loginTimeNew
    ,   Max(time) OVER(PARTITION BY username, date) as logoutTimeNew
    ,   *
    FROM    #remote_users t
    ) t


-- #2 "Real" times version
update  t
set login_time = loginTimeNew
,   logout_time = t.logoutTimeNew
,   session_time = CAST(dateadd(ms, datediff(ms, ISNULL(loginTimeRealNew,logintimenew), ISNULL(logoutRealTimeNew,logouttimenew)) , '19000101') AS TIME)
FROM    (
    select  min(time) OVER(PARTITION BY username,date) as loginTimeNew
    ,   Max(time) OVER(PARTITION BY username,date) as logoutTimeNew
    ,   min(case when type = 'login' then time end) OVER(PARTITION BY username, date) as loginTimeRealNew
    ,   Max(case when type = 'logout' then time end) OVER(PARTITION BY username, date) as logoutRealTimeNew
    ,   *
    FROM    #remote_users t
    ) t

我写了两个版本，#1 是一个简单的版本，它需要最小和最大时间来创建 logintimeNew 和 logoutTimeNew 列，然后执行时间差异操作以创建会话时间的“时间”部分。

这假设每天的第一个日期是登录，最后一天是注销。如果你真的想找到“会话时间”部分，你可以使用#2版本。该行执行条件聚合以查找登录/注销行，如果缺少这些行，则回退到第一行/最后一行

要解释详细信息：

min(time) OVER(PARTITION BY username, date)是一个窗口聚合，用于计算按用户和日期分组（分区）的最小时间。这有助于我们每天获得所需的日期值。

CAST(dateadd(ms, datediff(ms, logintimenew, logouttimenew) , '19000101') AS TIME)这个复杂的公式是根据“时间”数据类型转换两天的差值。可能有更好的方法，但此版本以毫秒为单位计算差异，然后通过向“SQL Server 中的零日期”添加毫秒来创建日期，这是19000101。然后，通过将其转换为 TIME，可以得到差值的时间部分。

基于条件 SQL 创建新变量

Creating a new variable based on condition SQL

评论

评论