求计算用户重叠与非重叠会话总时长的SQL查询语句

Hey there! Let's work through how to calculate overlapping and non-overlapping session durations for user xyz based on the provided data. First, let's clean up the session data to avoid ambiguity (fixing time format inconsistencies and 12-hour vs 24-hour clock issues):

Session Data (Cleaned) #

Approach #

The key challenge here is merging overlapping/contiguous sessions to calculate the actual covered duration (not just summing individual session lengths, which would double-count overlapping time). Then we can separate merged groups (overlapping) from single sessions (non-overlapping) to get our stats.

SQL Solution #

Here's a query that handles this using window functions (works in PostgreSQL, MySQL 8+, and most modern SQL databases):

WITH ranked_sessions AS (
    SELECT
        username,
        timein,
        timeout,
        -- Assign a group ID: increment when current session starts after the previous one ends
        SUM(CASE WHEN timein <= LAG(timeout) OVER (PARTITION BY username ORDER BY timein) THEN 0 ELSE 1 END) 
            OVER (PARTITION BY username ORDER BY timein) AS session_group
    FROM user_sessions
    WHERE username = 'xyz'
),
merged_sessions AS (
    SELECT
        username,
        session_group,
        MIN(timein) AS group_start,
        MAX(timeout) AS group_end,
        COUNT(*) AS session_count
    FROM ranked_sessions
    GROUP BY username, session_group
)
SELECT
    username,
    -- Calculate total overlapping duration (groups with >1 sessions)
    ROUND(SUM(CASE WHEN session_count > 1 THEN EXTRACT(EPOCH FROM (group_end - group_start)) ELSE 0 END) / 3600, 2) AS overlapping_total_hours,
    -- Calculate total non-overlapping duration (groups with 1 session)
    ROUND(SUM(CASE WHEN session_count = 1 THEN EXTRACT(EPOCH FROM (group_end - group_start)) ELSE 0 END) / 3600, 2) AS non_overlapping_total_hours,
    -- Calculate overall total duration
    ROUND(SUM(EXTRACT(EPOCH FROM (group_end - group_start))) / 3600, 2) AS total_hours
FROM merged_sessions
GROUP BY username;

How It Works #

1. ranked_sessions CTE: We sort sessions by start time, then assign a group ID. If a session starts before or when the previous session ends, it stays in the same group (overlapping). If it starts after the previous session ends, we create a new group.
2. merged_sessions CTE: For each group, we find the earliest start time and latest end time (combining all overlapping sessions into one continuous block). We also count how many sessions are in each group.
3. Final Aggregation: We sum the duration of groups with multiple sessions (overlapping total) and groups with single sessions (non-overlapping total), plus the overall total. The ROUND function just makes the hours more readable.

Note #

Make sure your time columns are stored as proper TIMESTAMP types in the database—fix any formatting issues (like colons instead of dots for milliseconds) before running the query to avoid parsing errors.

内容的提问来源于stack exchange，提问作者K.Neha