时间日期合并处理

Bob66666 2020-07-07 05:42:28 
SELECT TOP 1000 pin,event_time,reader_state FROM acc_transaction

WHERE event_no=0







SELECT TOP 1000 用户ID=pin,开始时间='2020-07-01 10:01:00',结束时间='2020-07-01 10:05:00',间隔时间='1分钟',标志=reader_state from acc_transaction



如何将上面这种原始 刷卡记录表， 合并相同时间和连续时间， 比如 某个人 2分钟以内的标志为0的或者为1的， 记录合并为一条，并生成开始时间和结束时间列


...全文
175 6 打赏 收藏 转发到动态 举报
写回复
用AI写文章
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
XXXXn帅气 2020-07-09
  • 打赏
  • 举报
 回复 
if OBJECT_ID('tempdb..#test') is not null drop table #test
create table #test(
pin nvarchar(20),
event_time datetime,
reader_state nvarchar(1),
)
go
insert into #test
select '1' ,'2020-07-07 08:08:08','2' union all
select '1' ,'2020-07-07 08:08:08','1' union all
select '1' ,'2020-07-07 08:09:08','1' union all
select '1' ,'2020-07-07 08:09:09','1' union all
select '1' ,'2020-07-07 08:13:08','1' union all
select '1' ,'2020-07-07 08:14:08','1' union all
select '2' ,'2020-07-07 08:08:08','1' union all
select '2' ,'2020-07-07 08:11:08','1' union all
select '2' ,'2020-07-07 08:12:08','1' union all
select '2' ,'2020-07-07 08:13:08','1'
 
select * from #test
 
;with c_1 as (select 
ROW_NUMBER()over(partition by pin,reader_state order by event_time)as Num, 
pin,event_time,reader_state 
from #test group by pin,event_time,reader_state),
c_a as (select a.Pin,a.event_time as STime,b.event_time as ETime,DATEDIFF(MINUTE,a.event_time,b.event_time)as jg,a.reader_state,a.Num as lNum,b.Num as rNum from c_1 a left join c_1 b on a.Num=b.Num-1 and a.pin=b.pin and a.reader_state=b.reader_state and DATEDIFF(MINUTE,a.event_time,b.event_time)<3),
c_2 as(select a.Pin,a.event_time as STime,b.event_time as ETime,DATEDIFF(MINUTE,a.event_time,b.event_time)as jg,a.reader_state,a.Num as lNum,b.Num as rNum from c_1 as a left join c_1 as b on a.Num=b.Num-1 and a.pin=b.pin and a.reader_state=b.reader_state and DATEDIFF(MINUTE,a.event_time,b.event_time)<3 where a.Num=1 
union all 
select a.* from  c_2 as c join c_a as a on a.lNum=ISNULL(c.rNum,c.lNum)+1 and a.pin=c.pin and a.reader_state=c.reader_state )
select a.pin,a.STime,a.ETime ,a.jg,a.reader_state from c_2 as a
order  by Pin,STime
 
--result
/*
1	2020-07-07 08:08:08.000	2020-07-07 08:09:08.000	1	1
1	2020-07-07 08:08:08.000	NULL	NULL	2
1	2020-07-07 08:09:09.000	NULL	NULL	1
1	2020-07-07 08:13:08.000	2020-07-07 08:14:08.000	1	1
2	2020-07-07 08:08:08.000	NULL	NULL	1
2	2020-07-07 08:11:08.000	2020-07-07 08:12:08.000	1	1
2	2020-07-07 08:13:08.000	NULL	NULL	1
*/
二十分不好赚啊
Bob66666 2020-07-09
  • 打赏
  • 举报
 回复
引用 5 楼 XXXXn帅气 的回复:
if OBJECT_ID('tempdb..#test') is not null drop table #test
create table #test(
pin nvarchar(20),
event_time datetime,
reader_state nvarchar(1),
)
go
insert into #test
select '1' ,'2020-07-07 08:08:08','2' union all
select '1' ,'2020-07-07 08:08:08','1' union all
select '1' ,'2020-07-07 08:09:08','1' union all
select '1' ,'2020-07-07 08:09:09','1' union all
select '1' ,'2020-07-07 08:13:08','1' union all
select '1' ,'2020-07-07 08:14:08','1' union all
select '2' ,'2020-07-07 08:08:08','1' union all
select '2' ,'2020-07-07 08:11:08','1' union all
select '2' ,'2020-07-07 08:12:08','1' union all
select '2' ,'2020-07-07 08:13:08','1'
 
select * from #test
 
;with c_1 as (select 
ROW_NUMBER()over(partition by pin,reader_state order by event_time)as Num, 
pin,event_time,reader_state 
from #test group by pin,event_time,reader_state),
c_a as (select a.Pin,a.event_time as STime,b.event_time as ETime,DATEDIFF(MINUTE,a.event_time,b.event_time)as jg,a.reader_state,a.Num as lNum,b.Num as rNum from c_1 a left join c_1 b on a.Num=b.Num-1 and a.pin=b.pin and a.reader_state=b.reader_state and DATEDIFF(MINUTE,a.event_time,b.event_time)<3),
c_2 as(select a.Pin,a.event_time as STime,b.event_time as ETime,DATEDIFF(MINUTE,a.event_time,b.event_time)as jg,a.reader_state,a.Num as lNum,b.Num as rNum from c_1 as a left join c_1 as b on a.Num=b.Num-1 and a.pin=b.pin and a.reader_state=b.reader_state and DATEDIFF(MINUTE,a.event_time,b.event_time)<3 where a.Num=1 
union all 
select a.* from  c_2 as c join c_a as a on a.lNum=ISNULL(c.rNum,c.lNum)+1 and a.pin=c.pin and a.reader_state=c.reader_state )
select a.pin,a.STime,a.ETime ,a.jg,a.reader_state from c_2 as a
order  by Pin,STime
 
--result
/*
1	2020-07-07 08:08:08.000	2020-07-07 08:09:08.000	1	1
1	2020-07-07 08:08:08.000	NULL	NULL	2
1	2020-07-07 08:09:09.000	NULL	NULL	1
1	2020-07-07 08:13:08.000	2020-07-07 08:14:08.000	1	1
2	2020-07-07 08:08:08.000	NULL	NULL	1
2	2020-07-07 08:11:08.000	2020-07-07 08:12:08.000	1	1
2	2020-07-07 08:13:08.000	NULL	NULL	1
*/
二十分不好赚啊
嗯，我不是经常逛论坛，所以能给出的分也不多，谢谢耐心的指导，这也是一个思路，辛苦了！
XXXXn帅气 2020-07-09
  • 打赏
  • 举报
 回复
引用 3 楼 Bob66666 的回复:
大佬，你好，这个 需要将 相同时间或者 间隔在2分钟以内的时间合并成一条，我看你上面这08:08:08.000 没有合并呢，你只是取了个 间隔时间
标志不一样啊 不过其实还有点问题
Bob66666 2020-07-09
  • 打赏
  • 举报
 回复
大佬，你好，这个 需要将 相同时间或者 间隔在2分钟以内的时间合并成一条，我看你上面这08:08:08.000 没有合并呢，你只是取了个 间隔时间
XXXXn帅气 2020-07-08
  • 打赏
  • 举报
 回复
引用 1 楼 XXXXn帅气 的回复:

select a.pin,a.STime,a.ETime ,a.jg,a.reader_state from c_2 as a
union all
select a.pin,a.event_time as STime,null as ETime ,null as jg,a.reader_state from c_1 as a where a.Num>1 and  not exists(select 1 from c_1 b where a.pin=b.pin and a.Num=b.Num-1 and a.reader_state=b.reader_state and DATEDIFF(MINUTE,a.event_time,b.event_time)<3)
order  by Pin,STime

select a.pin,a.STime,a.ETime ,a.jg,a.reader_state from c_2 as a
union all
select a.pin,a.event_time as STime,null as ETime ,null as jg,a.reader_state from c_1 as a where a.Num>1 and  not exists(select 1 from c_2 b where a.pin=b.pin and a.reader_state=b.reader_state and((a.event_time=b.STime and a.Num=b.lNum)or((a.event_time=b.ETime and a.Num=b.rNum))))
order  by Pin,STime
后边可能要改成这样
XXXXn帅气 2020-07-08
  • 打赏
  • 举报
 回复 
if OBJECT_ID('tempdb..#test') is not null drop table #test
create table #test(
pin nvarchar(20),
event_time datetime,
reader_state nvarchar(1),
)
go
insert into #test 
select '1' ,'2020-07-07 08:08:08','2' union all
select '1' ,'2020-07-07 08:08:08','1' union all
select '1' ,'2020-07-07 08:09:08','1' union all
select '1' ,'2020-07-07 08:10:08','1' union all
select '1' ,'2020-07-07 08:11:08','1' union all
select '2' ,'2020-07-07 08:08:08','1' union all
select '2' ,'2020-07-07 08:11:08','1' union all
select '2' ,'2020-07-07 08:12:08','1'union all
select '2' ,'2020-07-07 08:13:08','1'

select * from #test

;with c_1 as (select 
ROW_NUMBER()over(partition by pin,reader_state order by event_time)as Num, 
pin,event_time,reader_state 
from #test group by pin,event_time,reader_state),
c_2 as(select a.Pin,a.event_time as STime,b.event_time as ETime,DATEDIFF(MINUTE,a.event_time,b.event_time)as jg,a.reader_state,a.Num as lNum,b.Num as rNum from c_1 as a left join c_1 as b on a.Num=b.Num-1 and a.pin=b.pin and a.reader_state=b.reader_state and DATEDIFF(MINUTE,a.event_time,b.event_time)<3 where a.Num=1 
union all 
select a.Pin,a.event_time as STime,b.event_time as ETime,DATEDIFF(MINUTE,a.event_time,b.event_time)as jg,a.reader_state,a.Num as lNum,b.Num as rNum from  c_2 as c join c_1 as a on a.Num=ISNULL(c.rNum,c.lNum)+1 and a.pin=c.pin and a.reader_state=c.reader_state join c_1 as b on a.Num=b.Num-1 and a.pin=b.pin and a.reader_state=b.reader_state and DATEDIFF(MINUTE,a.event_time,b.event_time)<3  
)
select a.pin,a.STime,a.ETime ,a.jg,a.reader_state from c_2 as a
union all
select a.pin,a.event_time as STime,null as ETime ,null as jg,a.reader_state from c_1 as a where a.Num>1 and  not exists(select 1 from c_1 b where a.pin=b.pin and a.Num=b.Num-1 and a.reader_state=b.reader_state and DATEDIFF(MINUTE,a.event_time,b.event_time)<3)

order  by Pin,STime

--result
/*
1	2020-07-07 08:08:08.000	2020-07-07 08:09:08.000	1		1
1	2020-07-07 08:08:08.000	NULL					NULL	2
1	2020-07-07 08:10:08.000	2020-07-07 08:11:08.000	1		1
1	2020-07-07 08:11:08.000	NULL					NULL	1
2	2020-07-07 08:08:08.000	NULL					NULL	1
2	2020-07-07 08:11:08.000	2020-07-07 08:12:08.000	1	1
2	2020-07-07 08:13:08.000	NULL					NULL	1
*/
java处理日期合并
java处理日期合并
python实现时间段的合并处理
python实现时间段的合并处理
用Python中的Pandas处理数据（2）
在pandas中的数据判断处理 关于数学运算的应用 统计函数应用 字符串处理技术 日期时间处理 数据分组处理 数据合并处理 数据合并处理
疑难问题

22,210

社区成员

121,726

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章