Laravel查询：筛选满足时间差条件的object_id

方法1：自连接匹配关联记录 #

基于现有筛选逻辑，通过自连接关联同一object_id下的不同类型记录，校验时间差条件：

SELECT DISTINCT t1.object_id
FROM your_table t1
JOIN your_table t2 ON t1.object_id = t2.object_id
WHERE 
  -- 匹配一组Text1和Text2的组合记录
  (t1.extended_data LIKE 'Text1%' AND t2.extended_data LIKE 'Text2%')
  OR (t1.extended_data LIKE 'Text2%' AND t2.extended_data LIKE 'Text1%')
  -- 时间差小于2秒
  AND ABS(TIMESTAMPDIFF(SECOND, t1.record_time, t2.record_time)) < 2;

方法2：窗口函数分组校验 #

先筛选目标记录并打标签，再通过窗口函数获取同组内的关联记录，计算时间差：

WITH filtered_records AS (
  SELECT 
    object_id,
    record_time,
    CASE 
      WHEN extended_data LIKE 'Text1%' THEN 'T1'
      WHEN extended_data LIKE 'Text2%' THEN 'T2'
    END AS type_tag
  FROM your_table
  WHERE extended_data LIKE 'Text1%' OR extended_data LIKE 'Text2%'
),
pair_records AS (
  SELECT 
    object_id,
    record_time,
    type_tag,
    LAG(record_time) OVER (PARTITION BY object_id ORDER BY record_time) AS prev_time,
    LAG(type_tag) OVER (PARTITION BY object_id ORDER BY record_time) AS prev_type
  FROM filtered_records
)
SELECT DISTINCT object_id
FROM pair_records
WHERE 
  type_tag != prev_type
  AND ABS(TIMESTAMPDIFF(SECOND, record_time, prev_time)) < 2;

注意事项 #

• 替换your_table为实际表名，record_time为实际时间字段名
• 两种方法均可筛选出满足条件的object_id，最终返回结果为[3,7]

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