在 SQL Server 中使用 T-SQL 操作登录注销数据

Manipulate Login Logout data with T-SQL in SQL Server(在 SQL Server 中使用 T-SQL 操作登录注销数据)

问题描述

有谁知道用表1中的信息构建表2的方法吗?使用 Python 很容易接近，因为我可以使用按行检查".但是，后面有一个大数据集，所以如果我可以在SQL Server中用SQL语言进行数据转换，那就太好了.请注意，这不是真正的登录和注销数据结构/问题，我只想知道如何将表 2 中的数据转换为表 1.它与我现在拥有的数据具有相似的结构，但用于其他用途.

Does anyone know the way of building table 2 with information in table 1? It is easy to approach with Python because I can use ‘checking by row’. However, there is a big dataset at the back so if I could conduct the data transformation with SQL language in SQL Server it will be nice. Notice that this is not a real login and logout data structure/problem, and I just want to know how to transform data in table 2 into table 1. It has the similar structure with the data I have right now but for other use.

详情:当用户第一次登录我的系统时，我在表2中用‘LoginTime’记下时间.他可能会多次登录我的系统，但我只会记录他第一次登录的时间.当他第一次注销我的系统时，我会将表 1 中的Eventtime"记录为表 2 中的LogoutTime".如果同一用户没有注销，我会将 LogoutTime 保留为NULL".

Details: When the user first logs in to my system, I write down the time with ‘LoginTime’ in table 2. He might login to my system for several times but I will only record the very first time when he login. When he first logout of my system, I will record the ‘Eventtime’ from table 1 as ‘LogoutTime’ in table 2. If the same user doesn’t logout, I will keep the LogoutTime as ‘NULL’.

表一

UserID  EventTime   Event
1   9/1/13 15:33    0
1   9/1/13 17:00    0
1   9/1/13 18:00    0
1   9/1/13 18:20    1
1   9/1/13 18:30    1
1   9/2/13 11:05    0
1   9/2/13 11:45    1
1   9/2/13 13:50    0
2   9/1/13 16:15    0
2   9/1/13 17:00    1
2   9/1/13 18:01    0
2   9/1/13 18:02    0
2   9/1/13 19:02    1
3   9/1/13 17:10    0
3   9/1/13 19:10    1
3   9/2/13 21:01    0

表 2

UserID  LoginTime   LogoutTime
1   9/1/13 15:33    9/1/13 18:20
1   9/2/13 11:05    9/2/13 11:45
1   9/2/13 13:50    NULL
2   9/1/13 16:15    9/1/13 17:00
2   9/1/13 18:02    9/1/13 19:02
3   9/1/13 17:10    9/1/13 19:10
3   9/1/13 21:01    NULL

推荐答案

您好，

-- I assume that your date and time data is in format "mm/dd/yy", which means style 1
-- For better aqurecy I am using datetime2(7) 
drop table if exists T;
create table T(UserID int, EventTime datetime2(7), [Event] bit)
GO
INSERT T(UserID,EventTime,Event)
values
(1,CONVERT(datetime2(7),'9/1/13 15:33', 1), 0),
(1,CONVERT(datetime2(7),'9/1/13 17:00', 1), 0),
(1,CONVERT(datetime2(7),'9/1/13 18:00', 1), 0),
(1,CONVERT(datetime2(7),'9/1/13 18:20', 1), 1),
(1,CONVERT(datetime2(7),'9/1/13 18:30', 1), 1),
(1,CONVERT(datetime2(7),'9/2/13 11:05', 1), 0),
(1,CONVERT(datetime2(7),'9/2/13 11:45', 1), 1),
(1,CONVERT(datetime2(7),'9/2/13 13:50', 1), 0),
(2,CONVERT(datetime2(7),'9/1/13 16:15', 1), 0),
(2,CONVERT(datetime2(7),'9/1/13 17:00', 1), 1),
(2,CONVERT(datetime2(7),'9/1/13 18:01', 1), 0),
(2,CONVERT(datetime2(7),'9/1/13 18:02', 1), 0),
(2,CONVERT(datetime2(7),'9/1/13 19:02', 1), 1),
(3,CONVERT(datetime2(7),'9/1/13 17:10', 1), 0),
(3,CONVERT(datetime2(7),'9/1/13 19:10', 1), 1),
(3,CONVERT(datetime2(7),'9/2/13 21:01', 1), 0)
GO
SELECT * FROM T
order by UserID, EventTime, Event
GO

解决方案初稿

请检查这是否满足您的需求

First draft of solution

Please check if this solve your needs

;with MyCTE as (
    SELECT UserID, EventTime, [Event]
        , [RN1-RN2] = ROW_NUMBER() over (order by UserID, EventTime, [Event]) - ROW_NUMBER() over (partition by UserID, [Event] order by UserID, EventTime, [Event])
    FROM T
),
MyCTE2 as (
    select distinct UserID, [Event] 
        , MIN(EventTime) OVER (partition by UserID,[Event], [RN1-RN2]) M
    from MyCTE
)
select UserID
  , [0] as LoginTime
  , [1] as LogoutTime
From (
    select UserID, [Event], M
        , ROW_NUMBER() OVER(partition by UserID,[Event] order by M) as GroupNum
    from MyCTE2
)x
pivot
(
  MIN(M)
  for [Event] in([0], [1])
)p
order by UserID, [LoginTime]
GO

如果此解决方案适合您，那么我们知道我满足了您的需求，我们可以进行下一步，即讨论性能.为此，我们需要获取您的真实标签；e 结构和更多示例数据(来自您的 DDL+DML)

If this solution fits you then we know that I got your needs, and we can move to the next step which is discuss about performance. For this we will need to get your real tab;e structure ans some more sample data (DDL+DML from you)

这篇关于在 SQL Server 中使用 T-SQL 操作登录注销数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！