Python计算员工拜访间隔空闲时长（按员工与日期分组）

需求说明 #

通过PyODBC连接MSSQL数据库获取员工拜访数据（字段包含EMP_ID、VISIT_DATE、VISITSTART、VISITSTOP），需计算同一员工同一日期内，上一次拜访结束时间到下一次拜访开始时间的分钟差总和，最终按EMP_ID和VISIT_DATE分组输出，处理量级约15万条数据，要求避免循环，采用高效的窗口/分组方法实现。

原查询语句：

select EMP_ID, VISIT_DATE, VISITSTART, VISITSTOP 
from VISITS 
where 
EMP_ID in (<List of Emps>) and SORTDATE between '2023-01-16' and '2023-01-29'
order by EMP_ID, VISIT_DATE

数据示例：

EMP_ID      VISIT_DATE              VISITSTART              VISITSTOP
S0000000163 2023-01-16 00:00:00.000 2023-01-16 08:59:00.000 2023-01-16 10:32:00.000
S0000000163 2023-01-16 00:00:00.000 2023-01-16 10:58:00.000 2023-01-16 12:35:00.000
S0000000163 2023-01-16 00:00:00.000 2023-01-16 13:06:00.000 2023-01-16 14:06:00.000
S0000000166 2023-01-16 00:00:00.000 2023-01-16 07:21:00.000 2023-01-16 08:15:00.000
S0000000166 2023-01-16 00:00:00.000 2023-01-16 08:45:00.000 2023-01-16 08:52:00.000
S0000000166 2023-01-16 00:00:00.000 2023-01-16 09:56:00.000 2023-01-16 10:46:00.000

期望输出：

EMP_ID      VISITDATE  IDLE_TIME
S0000000163 2023-01-16 56
S0000000163 2023-01-17 48
S0000000166 2023-01-16 92
S0000000166 2023-01-17 54

方案一：数据库端直接计算（推荐，最高效） #

利用MSSQL的LAG()窗口函数，直接在查询阶段完成计算，减少数据传输量，适配大数据量场景：

SELECT 
    EMP_ID,
    CAST(VISIT_DATE AS DATE) AS VISITDATE,
    SUM(DATEDIFF(MINUTE, prev_stop, VISITSTART)) AS IDLE_TIME
FROM (
    SELECT 
        EMP_ID,
        VISIT_DATE,
        VISITSTART,
        -- 获取同一员工同一日期的上一次拜访结束时间
        LAG(VISITSTOP) OVER (PARTITION BY EMP_ID, VISIT_DATE ORDER BY VISITSTART) AS prev_stop
    FROM VISITS
    WHERE 
        EMP_ID IN (<List of Emps>) 
        AND SORTDATE BETWEEN '2023-01-16' AND '2023-01-29'
) AS sub
-- 过滤无前置结束时间的首次拜访记录
WHERE prev_stop IS NOT NULL
GROUP BY EMP_ID, CAST(VISIT_DATE AS DATE)
ORDER BY EMP_ID, VISITDATE

逻辑说明： #

1. 子查询通过LAG(VISITSTOP) OVER (PARTITION BY EMP_ID, VISIT_DATE ORDER BY VISITSTART)，按员工+日期分组、拜访开始时间排序，获取当前拜访的上一次结束时间。
2. 用DATEDIFF(MINUTE, prev_stop, VISITSTART)计算两次拜访的空闲分钟数。
3. 过滤掉无前置结束时间的记录后，分组求和得到每日总空闲时长。

此方案依托数据库优化引擎处理数据，15万条记录的计算效率远高于Python端处理。

方案二：Python Pandas端计算 #

若需在Python中处理数据，可利用Pandas的分组和shift()函数实现：

import pyodbc
import pandas as pd

# 1. 连接数据库并读取数据
conn = pyodbc.connect('DRIVER={SQL Server};SERVER=your_server;DATABASE=your_db;UID=user;PWD=password')
query = """
select EMP_ID, VISIT_DATE, VISITSTART, VISITSTOP 
from VISITS 
where 
EMP_ID in (<List of Emps>) and SORTDATE between '2023-01-16' and '2023-01-29'
order by EMP_ID, VISIT_DATE
"""
df = pd.read_sql(query, conn)
conn.close()

# 2. 转换日期格式与时间类型
df['VISITDATE'] = df['VISIT_DATE'].dt.date
df['VISITSTART'] = pd.to_datetime(df['VISITSTART'])
df['VISITSTOP'] = pd.to_datetime(df['VISITSTOP'])

# 3. 分组计算空闲时长
df['prev_stop'] = df.groupby(['EMP_ID', 'VISITDATE'])['VISITSTOP'].shift(1)
# 计算分钟差并分组求和
result = df.dropna(subset=['prev_stop']).assign(
    idle_minutes=lambda x: (x['VISITSTART'] - x['prev_stop']).dt.total_seconds() / 60
).groupby(['EMP_ID', 'VISITDATE'], as_index=False)['idle_minutes'].sum().round(0).astype(int)

result.columns = ['EMP_ID', 'VISITDATE', 'IDLE_TIME']
print(result)

逻辑说明： #

1. 通过groupby(['EMP_ID', 'VISITDATE'])['VISITSTOP'].shift(1)获取同一员工同日的上一次拜访结束时间。
2. 计算当前拜访开始时间与上一次结束时间的差值，转换为分钟数。
3. 分组求和后整理格式，得到最终结果。

此方案适合需在Python中进行后续数据处理的场景，但数据传输与计算效率略低于数据库端方案。

内容的提问来源于stack exchange，提问作者Gazman Development Gosford