sql 语句问题, 求计算相差10分钟以内的数据作为同一批次数据显示

[tom0311]

数据源如下：

id	order_type	pickup_time
1	AAA	2022/6/7 6:42
2	AAA	2022/6/7 6:43
3	AAA	2022/6/7 6:55
4	AAA	2022/6/8 6:52
5	BBB	2022/6/7 6:55
6	BBB	2022/6/7 7:00
7	BBB	2022/6/7 7:00
8	BBB	2022/6/7 7:01
9	BBB	2022/6/7 7:08
10	BBB	2022/6/7 7:08
11	BBB	2022/6/7 7:08
12	BBB	2022/6/7 7:12
13	BBB	2022/6/7 7:13

根据order_type 和 pickup_time排序后，需要得到如下的lot_number列数据， 算法是：相同order_type 的情况下按pickup_time升序排序从第一条开始，时间相差在10分钟以内的为同一批次数据，后条的lot_number用前条相同的时间戳来标示；相差超过10分钟的用另外一个时间戳批次标示，以此类推；需要注意的是：

第9行开始，它的时间7：08 不是和第8行的 7：01分比较，而是和 5，6，7，8行计算的批次号06:55分比比较，这2个时间一比较就大于10分钟了； 因为7：08分和 6：55分 是没法划为10分钟以内同一批次的，必须重新划入下一个批次。

分别用select 和 update 更新到lot_number 列写一下SQL

 

id	order_type	pickup_time	lot_number
1	AAA	2022/6/7 6:42	2022-06-07 06:42	同一批次
2	AAA	2022/6/7 6:43	2022-06-07 06:42
3	AAA	2022/6/7 6:55	2022-06-07 06:55
4	AAA	2022/6/8 6:52	2022-06-08 06:52
5	BBB	2022/6/7 6:55	2022-06-07 06:55	同一批次
6	BBB	2022/6/7 7:00	2022-06-07 06:56
7	BBB	2022/6/7 7:00	2022-06-07 06:55
8	BBB	2022/6/7 7:01	2022-06-07 06:55
9	BBB	2022/6/7 7:08	2022-06-07 07:08	同一批次
10	BBB	2022/6/7 7:08	2022-06-07 07:08
11	BBB	2022/6/7 7:08	2022-06-07 07:08
12	BBB	2022/6/7 7:12	2022-06-07 07:08
13	BBB	2022/6/7 7:13	2022-06-07 07:08

 

[幸福感奇差]

使用窗口函数，计算相邻记录时间差，和 10 分钟做一个除法后 ceiling 一下作为分组的标签即可
select id, order_type, pickup_time,
ceiling(datediff(minute, pickup_time, lead(pickup_time) over(partiton by order_type order by pickup_time)) / 10.) groupby_tag
from tablename

[shinger126]

@shinger126 当然，你也可以直接用一个固定的日期，来算这个日期到你的表中的日期字段的分钟数作为分组依据 declare @date datetime='2022-06-07 06:42:03' select DATEDIFF(minute,convert(varchar(10),'2000-01-01',120),@date)/10

[shinger126]

declare @date datetime='2022-06-07 06:42:03'
select cast(@date as date),DATEDIFF(minute,convert(varchar(10),@date,120),@date)/10
用日期，和当日0点到该时间的分钟数除以10来分组

[锟斤拷锟斤拷]

CREATE TABLE #T (
id INT,
order_type VARCHAR(20),
pickup_time DATETIME
)
INSERT INTO #T VALUES 
(1,'AAA','2022/6/7 6:42'),(2,'AAA','2022/6/7 6:43'),(3,'AAA','2022/6/7 6:55'),(4,'AAA','2022/6/8 6:52'),
(5,'BBB','2022/6/7 6:55'),(6,'BBB','2022/6/7 7:00'),(7,'BBB','2022/6/7 7:00'),(8,'BBB','2022/6/7 7:01'),
(9,'BBB','2022/6/7 7:08'),(10,'BBB','2022/6/7 7:08'),(11,'BBB','2022/6/7 7:08'),(12,'BBB','2022/6/7 7:12'),
(13,'BBB','2022/6/7 7:13')

;WITH CTE AS (
    SELECT MIN(id) id,A.order_type,MIN(A.pickup_time) pickup_time,1 GROUPBY FROM #T A GROUP BY A.order_type 
    UNION ALL
    SELECT A.id,A.order_type,
    CASE WHEN DATEDIFF(MI,B.pickup_time,A.pickup_time)<=10 THEN B.pickup_time ELSE A.pickup_time END, 
    CASE WHEN DATEDIFF(MI,B.pickup_time,A.pickup_time)<=10 THEN B.GROUPBY ELSE B.GROUPBY+1 END
    FROM #T A,CTE B WHERE A.order_type = B.order_type AND A.id = B.id+1 
)
SELECT * FROM CTE ORDER BY ID  ----这里自己加个聚合就行

DROP TABLE #T