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

tom0311 2022-07-20 20:56:34

数据源如下:

idorder_typepickup_time
1AAA2022/6/7 6:42
2AAA2022/6/7 6:43
3AAA2022/6/7 6:55
4AAA2022/6/8 6:52
5BBB2022/6/7 6:55
6BBB2022/6/7 7:00
7BBB2022/6/7 7:00
8BBB2022/6/7 7:01
9BBB2022/6/7 7:08
10BBB2022/6/7 7:08
11BBB2022/6/7 7:08
12BBB2022/6/7 7:12
13BBB2022/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

 

idorder_typepickup_timelot_number 
1AAA2022/6/7 6:422022-06-07 06:42同一批次
2AAA2022/6/7 6:432022-06-07 06:42
3AAA2022/6/7 6:552022-06-07 06:55 
4AAA2022/6/8 6:522022-06-08 06:52 
5BBB2022/6/7 6:552022-06-07 06:55同一批次
6BBB2022/6/7 7:002022-06-07 06:56
7BBB2022/6/7 7:002022-06-07 06:55
8BBB2022/6/7 7:012022-06-07 06:55
9BBB2022/6/7 7:082022-06-07 07:08同一批次
10BBB2022/6/7 7:082022-06-07 07:08
11BBB2022/6/7 7:082022-06-07 07:08
12BBB2022/6/7 7:122022-06-07 07:08
13BBB2022/6/7 7:132022-06-07 07:08

 

...全文
89 4 打赏 收藏 举报
写回复
4 条回复
切换为时间正序
请发表友善的回复…
发表回复

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
  • 打赏
  • 举报
回复

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来分组

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

使用窗口函数,计算相邻记录时间差,和 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

  • 打赏
  • 举报
回复 1
发帖
MS-SQL Server

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
帖子事件
创建了帖子
2022-07-20 20:56
社区公告
暂无公告