問(wèn)題描述
有誰(shuí)知道用表1中的信息構(gòu)建表2的方法嗎?使用 Python 很容易接近，因?yàn)槲铱梢允褂冒葱袡z查".但是，后面有一個(gè)大數(shù)據(jù)集，所以如果我可以在SQL Server中用SQL語(yǔ)言進(jìn)行數(shù)據(jù)轉(zhuǎn)換，那就太好了.請(qǐng)注意，這不是真正的登錄和注銷數(shù)據(jù)結(jié)構(gòu)/問(wèn)題，我只想知道如何將表 2 中的數(shù)據(jù)轉(zhuǎn)換為表 1.它與我現(xiàn)在擁有的數(shù)據(jù)具有相似的結(jié)構(gòu)，但用于其他用途.
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.
詳情:當(dāng)用戶第一次登錄我的系統(tǒng)時(shí)，我在表2中用‘LoginTime’記下時(shí)間.他可能會(huì)多次登錄我的系統(tǒng)，但我只會(huì)記錄他第一次登錄的時(shí)間.當(dāng)他第一次注銷我的系統(tǒng)時(shí)，我會(huì)將表 1 中的Eventtime"記錄為表 2 中的LogoutTime".如果同一用戶沒(méi)有注銷，我會(huì)將 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
解決方案初稿
請(qǐng)檢查這是否滿足您的需求
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
如果此解決方案適合您，那么我們知道我滿足了您的需求，我們可以進(jìn)行下一步，即討論性能.為此，我們需要獲取您的真實(shí)標(biāo)簽；e 結(jié)構(gòu)和更多示例數(shù)據(jù)(來(lái)自您的 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)
這篇關(guān)于在 SQL Server 中使用 T-SQL 操作登錄注銷數(shù)據(jù)的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！