时间戳

首页 > 解决方案 > SQL - 如果差异低于阈值，则合并两行

sql - SQL - 如果差异低于阈值，则合并两行

问题描述

我在 SQL Server 中有一个这样的表：

id  start_time  end_time
1   10:00:00    10:34:00
2   10:38:00    10:52:00
3   10:53:00    11:23:00
4   11:24:00    11:56:00
5   14:20:00    14:40:00
6   14:41:00    14:59:00
7   15:30:00    15:40:00

我想要的是一个查询，它根据两个连续记录之间的时间差（第 n 行的 end_time和第n+1行的 start_time ）输出合并记录。时间差小于2分钟的记录合并为一个时间条目，并保留第一条记录的ID。如果多个连续记录的时间差小于 2 分钟，这也应该合并两个以上的记录。

这将是预期的输出：

id  start_time  end_time
1   10:00:00    10:34:00
2   10:38:00    11:56:00
5   14:20:00    14:59:00
7   15:30:00    15:40:00

提前感谢有关如何构建查询的任何提示。

编辑： 我从以下代码开始计算lead_time 和时间差，但不知道如何分组和合并。

WITH rows AS
        (
        SELECT  *, ROW_NUMBER() OVER (ORDER BY Id) AS rn
        FROM #temp
        )
SELECT  mc.id, mc.start_time, mc.end_time, mp.start_time lead_time, DATEDIFF(MINUTE, mc.[end_time], mp.[start_time]) as DiffToNewSession
FROM    rows mc
LEFT JOIN    rows mp
ON      mc.rn = mp.rn - 1

标签： sqlsql-servertsql

解决方案

t-sql中的window函数可以实现很多数据统计，比如

create table #temp(id int identity(1,1), start_time time, end_time time)
insert into #temp(start_time, end_time)
values  ('10:00:00', '10:34:00')
      , ('10:38:00', '10:52:00')
      , ('10:53:00', '11:23:00')
      , ('11:24:00', '11:56:00')
      , ('14:20:00', '14:40:00')
      , ('14:41:00', '14:59:00')
      , ('15:30:00', '15:40:00')

;with c0 as(
select *, LAG(end_time,1,'00:00:00') over (order by id) as lag_time
from #temp
), c1 as(
select *, case when DATEDIFF(MI, lag_time, start_time) <= 2 then 1 else -0 end as gflag
from c0
), c2 as(
select *, SUM(case when gflag=0 then 1 else 0 end) over(order by id) as gid
from c1
)
select MIN(id) as id, MIN(start_time) as start_time, MAX(end_time) as end_time
from c2
group by gid

为了更好的描述数据构建的过程，我简单的用c0,c1,c2...来表示levels，可以合并一些levels并优化。如果不能使用 id 作为排序条件，则需要更改上述语句中的排序部分。

推荐阅读