PHP Laravel：计算两个时间戳间各小时的登录时长秒数

针对大数据量下的登录时长按小时分组统计需求，核心是避免逐行循环，利用批量/向量化计算能力拆分跨时段事件，以下是几种可行方案：

1. 数据库端SQL批量处理（推荐用于实时/存储在库的数据） #

直接在数据库层完成计算，利用内置的序列生成和聚合函数，无需导出数据：

• 生成事件覆盖的所有小时区间：通过递归CTE或时间序列生成函数，为每个登录登出事件生成其跨越的所有小时起始点
• 计算单小时有效时长：对每个事件-小时对，取「登录时间/小时起始」的最大值作为有效开始，「登出时间/下一小时起始」的最小值作为有效结束，两者时间差即为该小时的统计秒数
• 分组聚合求和：按小时起始点分组，累加所有事件在该小时的有效时长

示例PostgreSQL代码：

WITH event_hours AS (
    SELECT 
        user_id,
        login_time,
        logout_time,
        -- 生成事件覆盖的所有小时起始点
        generate_series(
            date_trunc('hour', login_time),
            date_trunc('hour', logout_time),
            interval '1 hour'
        ) AS hour_start
    FROM user_login_events
)
SELECT 
    hour_start,
    SUM(
        EXTRACT(EPOCH FROM 
            LEAST(logout_time, hour_start + interval '1 hour') 
            - GREATEST(login_time, hour_start)
        )
    ) AS total_seconds
FROM event_hours
GROUP BY hour_start
ORDER BY hour_start;

2. Python Pandas向量化处理（适合离线大数据量计算） #

利用Pandas的向量化操作替代循环，底层基于C实现，效率远高于逐行处理：

• 统一时间格式：将登录、登出时间转为datetime类型，确保时区一致
• 生成小时区间：为每个事件生成从登录小时到登出小时的所有小时点序列
• 展开数据行：将每个事件对应的多小时行展开，实现一对多映射
• 计算有效时长：通过向量运算直接计算每个小时的有效时长，再按小时分组求和

示例代码：

import pandas as pd
import numpy as np

# 示例数据
data = pd.DataFrame({
    'user_id': ['user1', 'user2'],
    'login_time': pd.to_datetime(['2024-05-20 17:55:00', '2024-05-20 19:30:00']),
    'logout_time': pd.to_datetime(['2024-05-20 18:10:00', '2024-05-20 20:45:00'])
})

# 生成每个事件覆盖的小时区间
data['hour_starts'] = data.apply(
    lambda x: pd.date_range(
        start=x['login_time'].floor('H'),
        end=x['logout_time'].floor('H'),
        freq='H'
    ), axis=1
)

# 展开数据为事件-小时的一对一记录
expanded_data = data.explode('hour_starts')

# 计算每个小时的有效秒数
expanded_data['duration_seconds'] = (
    np.minimum(expanded_data['logout_time'], expanded_data['hour_starts'] + pd.Timedelta(hours=1)) 
    - np.maximum(expanded_data['login_time'], expanded_data['hour_starts'])
).dt.total_seconds()

# 按小时分组统计总时长
result = expanded_data.groupby('hour_starts')['duration_seconds'].sum().reset_index()
print(result)

3. 数学公式直接拆分（适合轻量批量计算） #

通过时间戳的数学运算直接拆分跨时段事件，无需生成中间序列：

• 对单个事件的秒级时间戳login_ts和logout_ts：
  1. 计算登录小时的结束戳：login_hour_end = ((login_ts // 3600) + 1) * 3600
  2. 登录小时统计时长：min(login_hour_end, logout_ts) - login_ts
  3. 若跨多个小时，中间完整小时每个统计3600秒，数量为(logout_ts // 3600) - (login_hour_end // 3600)
  4. 登出小时统计时长：logout_ts - (logout_ts // 3600) * 3600

可将逻辑封装为向量化函数，利用Numpy批量处理，避免循环。

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