Laravel中如何筛选连续时间差在指定范围内的Log记录

一、最优单查询方案（窗口函数+CTE） #

利用SQL窗口函数LAG/LEAD计算每条记录与前后记录的时间差，通过CTE构建临时表后筛选符合条件的记录，支持MySQL 8.0+/PostgreSQL等现代数据库，效率最高。

代码实现（MySQL为例） #

$timeLimit = 2; // 指定时间限制，单位：小时

$logs = Log::withExpression('log_with_diffs', function ($query) use ($timeLimit) {
    $query->selectRaw('
        *,
        TIMESTAMPDIFF(HOUR, LAG(created_at) OVER (ORDER BY created_at), created_at) as diff_prev,
        TIMESTAMPDIFF(HOUR, created_at, LEAD(created_at) OVER (ORDER BY created_at)) as diff_next
    ')
    ->from('logs');
})
->from('log_with_diffs')
->where(function ($query) use ($timeLimit) {
    // 筛选条件：要么与后一条记录差符合限制，要么与前一条差符合限制
    $query->where('diff_next', '<=', $timeLimit)
          ->orWhere('diff_prev', '<=', $timeLimit)
          // 兼容最后一条记录：仅需与前一条差符合限制
          ->orWhere(function ($q) use ($timeLimit) {
              $q->whereNull('diff_next')
                ->where('diff_prev', '<=', $timeLimit);
          });
})
->orderBy('created_at')
->get();

适配PostgreSQL #

将时间差计算部分替换为PostgreSQL语法：

EXTRACT(EPOCH FROM (created_at - LAG(created_at) OVER (ORDER BY created_at))) / 3600 as diff_prev,
EXTRACT(EPOCH FROM (LEAD(created_at) OVER (ORDER BY created_at) - created_at)) / 3600 as diff_next

二、兼容老版本数据库（自连接查询） #

如果使用MySQL 5.7及以下不支持窗口函数的数据库，可通过自连接+子查询实现，效率略低但兼容性好。

$timeLimit = 2;

$logs = Log::query()
    // 当前记录存在符合时间限制的下一条记录
    ->whereExists(function ($query) use ($timeLimit) {
        $query->select(DB::raw(1))
            ->from('logs as next_log')
            ->whereRaw('next_log.created_at > logs.created_at')
            ->whereRaw('TIMESTAMPDIFF(HOUR, logs.created_at, next_log.created_at) <= ?', [$timeLimit])
            ->whereRaw('next_log.created_at = (SELECT MIN(created_at) FROM logs WHERE created_at > logs.created_at)');
    })
    // 或当前记录存在符合时间限制的上一条记录
    ->orWhereExists(function ($query) use ($timeLimit) {
        $query->select(DB::raw(1))
            ->from('logs as prev_log')
            ->whereRaw('prev_log.created_at < logs.created_at')
            ->whereRaw('TIMESTAMPDIFF(HOUR, prev_log.created_at, logs.created_at) <= ?', [$timeLimit])
            ->whereRaw('prev_log.created_at = (SELECT MAX(created_at) FROM logs WHERE created_at < logs.created_at)');
    })
    ->orderBy('created_at')
    ->get();

三、PHP层面处理（备选方案） #

若不想编写复杂SQL，可先加载所有记录到内存，再通过PHP逻辑筛选连续组。适合数据量较小的场景。

$timeLimit = 2 * 3600; // 转换为秒
$allLogs = Log::orderBy('created_at')->get();
$validLogs = collect();
$currentGroup = [];

foreach ($allLogs as $index => $log) {
    if ($index === 0) {
        $currentGroup[] = $log;
        continue;
    }
    $prevLog = $allLogs[$index - 1];
    $diff = $log->created_at->diffInSeconds($prevLog->created_at);
    
    if ($diff <= $timeLimit) {
        $currentGroup[] = $log;
    } else {
        // 若当前组有记录，加入有效列表
        if (!empty($currentGroup)) {
            $validLogs = $validLogs->merge($currentGroup);
        }
        $currentGroup = [$log];
    }
}

// 加入最后一组符合条件的记录
if (!empty($currentGroup)) {
    $validLogs = $validLogs->merge($currentGroup);
}

// 最终结果
$validLogs = $validLogs->unique('id')->values();

四、筛选特定连续组（可选） #

若需要选中某一个连续组（比如示例中的第1-4条），可通过窗口函数生成组ID后筛选：

$timeLimit = 2;

$logs = Log::withExpression('log_groups', function ($query) use ($timeLimit) {
    $query->selectRaw('
        *,
        SUM(CASE WHEN TIMESTAMPDIFF(HOUR, LAG(created_at) OVER (ORDER BY created_at), created_at) > ? THEN 1 ELSE 0 END) OVER (ORDER BY created_at) as group_id
    ', [$timeLimit])
    ->from('logs');
})
->from('log_groups')
->where('group_id', 0) // 筛选第一个连续组，group_id从0开始递增
->orderBy('created_at')
->get();

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