在 SQL Server 中使用 T-SQL 操作登录注销数据 [英] Manipulate Login Logout data with T-SQL in SQL Server
问题描述
有谁知道用表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 操作登录注销数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
