SQL求用户多次打开文件的累计停留时长技术求助

这确实是个很常见的棘手场景——用户反复打开同一文件，中间还可能穿插多次保存操作，要准确算出总停留时长，核心是先把每个「打开-关闭」的独立会话对应清楚。我给你拆解下思路和具体的实现方案：

首先得明确每个会话的边界规则：

• 每个会话以**EventID=1（打开文件）**作为起始点
• 以该会话对应的**最后一个EventID=3（关闭时的自动保存）**作为结束点：
  • 如果当前打开操作之后还有下一次打开，那么当前会话的结束是「下一次打开之前的最后一个Event3」
  • 如果是该文件-用户组合的最后一次打开，那么结束就是整个组的最后一个Event3

方案1：SQL直接计算（适用于数据库内数据处理） #

假设你的表名为file_events，可以用窗口函数来匹配每个打开事件对应的结束时间：

WITH ranked_events AS (
    SELECT 
        *,
        -- 获取当前Event1之后的下一次打开时间
        LEAD(CASE WHEN EventID = 1 THEN Datetime END) OVER (
            PARTITION BY FileID, UserName 
            ORDER BY Datetime
        ) AS next_open_time,
        -- 获取当前文件-用户组合的最后一次关闭时间
        MAX(CASE WHEN EventID = 3 THEN Datetime END) OVER (
            PARTITION BY FileID, UserName
        ) AS final_close_time
    FROM file_events
),
session_starts AS (
    SELECT 
        FileID,
        UserName,
        Datetime AS open_time,
        -- 匹配当前会话的关闭时间：有下一次打开则取之前最后一个Event3，否则取最终关闭时间
        COALESCE(
            (SELECT MAX(Datetime) FROM ranked_events re 
             WHERE re.FileID = rs.FileID 
               AND re.UserName = rs.UserName 
               AND re.Datetime < rs.next_open_time 
               AND re.EventID = 3),
            final_close_time
        ) AS close_time
    FROM ranked_events rs
    WHERE EventID = 1
)
-- 计算总停留时长，这里用MySQL的TIMESTAMPDIFF，其他数据库需调整（比如PostgreSQL用EXTRACT）
SELECT 
    FileID,
    UserName,
    SUM(TIMESTAMPDIFF(MINUTE, open_time, close_time)) AS total_duration_minutes
FROM session_starts
GROUP BY FileID, UserName;

方案2：Python Pandas处理（适用于本地数据集） #

如果数据在Pandas DataFrame中，可以通过会话分组来计算：

import pandas as pd

# 假设数据已加载到df，先确保时间列是datetime类型
df['Datetime'] = pd.to_datetime(df['Datetime'])

# 按文件、用户分组，按时间排序
df_sorted = df.sort_values(['FileID', 'UserName', 'Datetime']).reset_index(drop=True)

# 给每个打开事件生成唯一会话ID（每出现一次Event1，会话ID+1）
df_sorted['session_id'] = df_sorted.groupby(['FileID', 'UserName'])['EventID'].apply(
    lambda x: x.eq(1).cumsum()
)

# 每个会话取最早的打开时间和最晚的关闭时间
session_summary = df_sorted.groupby(['FileID', 'UserName', 'session_id']).agg(
    open_time=('Datetime', lambda x: x[x['EventID'] == 1].iloc[0]),
    close_time=('Datetime', lambda x: x[x['EventID'] == 3].iloc[-1])
).reset_index()

# 计算每个会话时长并求和得到总时长
session_summary['duration'] = session_summary['close_time'] - session_summary['open_time']
total_duration = session_summary.groupby(['FileID', 'UserName'])['duration'].sum()

# 输出结果
print(total_duration)

• 要校验数据完整性：如果存在打开后无任何保存/关闭记录的会话，需根据业务规则处理（比如忽略该会话，或用当前时间作为结束时间）
• 时间函数适配：不同数据库的时间差计算函数有差异，比如PostgreSQL用EXTRACT(EPOCH FROM (close_time - open_time))转成秒，需根据你的数据库调整
• 会话分组逻辑：用cumsum()对Event1的出现次数计数，是给每个独立会话分组最直观的方式

内容的提问来源于stack exchange，提问作者Woest