How to find total login times for a user?

debugcn 投稿 Dev

bds212

Every time a user logs in to my application, a new sessionID is created that is unique to the login. If a user logs in once, logs out, and then logs back in they will have a different sessionID.

How do I find the total time that each user has been logged in over the last two weeks?

My table has UserName, sessionID, and timestamp columns, and a row is inserted into the table for each action performed. I want to sum the difference between the first instance of the sessionID and the last instance of the sessionID for each session for a user during the last two weeks. Actions outside of the two week time period can be ignored.

Example data:

UserName sessionID timestamp 
user1    s1        '2019-07-01 08:00:00.000' 
user1    s1        '2019-07-01 10:00:00.000' 
user2    a1        '2019-07-01 16:00:00.000'
user1    s2        '2019-07-02 08:00:00.000' 
user2    a1        '2019-07-02 09:00:00.000'
user1    s2        '2019-07-02 10:00:00.000'

Example result:

UserName LoginTime
user1    04:00:00.000
user2    17:00:00.000

I was thinking of mapping a UserName to every sessionID that was associated with the UserName and then finding the max and min timestamp for each and summing them up, but that would require partitions in which I am hopelessly lost.

Ryan Prechel

Based on your example data:

Both the Username and SessionId columns have a character data type, which I will mimic using NVARCHAR(80).
The Timestamp column has a date and time data type, which I will mimic using DATETIME.

The following SQL creates an example table, populates the table with example data, and calculates the sum of the number of minutes for each session for each user from the last two weeks.

CREATE TABLE Session
(
    Username NVARCHAR(80) NOT NULL,
    SessionId NVARCHAR(80) NOT NULL,
    Timestamp DATETIME NOT NULL
);

DECLARE @date DATETIME = DATEADD(DAY, 15, DATEDIFF(DAY, 0, GETDATE()));

INSERT INTO Session(Username, SessionId, Timestamp) VALUES
('user1', 's1', DATEADD(HOUR, 8, @date)),
('user1', 's1', DATEADD(HOUR, 10, @date)),
('user1', 's2', DATEADD(HOUR, 32, @date)),
('user1', 's2', DATEADD(HOUR, 34, @date)),
('user1', 's3', DATEADD(HOUR, 56, @date)),
('user1', 's3', DATEADD(HOUR, 58, @date)),
('user1', 's4', DATEADD(HOUR, 80, @date)),
('user1', 's4', DATEADD(HOUR, 82, @date)),
('user2', 's5', DATEADD(HOUR, 81, @date)),
('user2', 's5', DATEADD(HOUR, 82, @date)),
('user2', 's6', DATEADD(HOUR, 86, @date)),
('user2', 's6', DATEADD(HOUR, 87, @date));


SELECT Username, SUM(SessionTime)
FROM
(
    SELECT Username, SessionId, DATEDIFF(MINUTE, MIN(Timestamp), MAX(Timestamp)) AS SessionTime
    FROM Session
    WHERE Timestamp > DATEADD(WEEK, -2, GETDATE())
    GROUP BY Username, SessionId
) AS S1
GROUP BY Username;

この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。

侵害の場合は、連絡してください[email protected]

編集2021-06-11

コメントを追加

サインイン

分類Dev

how to define a function that counts how many times the words from 'the LM word' list (all words in total) appear in the text?

分類Dev

Related 関連記事

記事