怎么统计在一段连续时间的记录数量

2005-12-28 08:39:32 234 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

Java实现并发编程中的线程池管理！ 🏆本文收录于「编程与技术实战」专栏，此专栏涵盖了C/C++编程、人工智能、数据结构、机器学习等技术领域的内容，助你早日登顶实现财富自由🚀；同时，欢迎大家关注&&收藏&&订阅！持续更新中，up！up！up！！阅读详情

比如:
ID 时间记录号码
1 2005-12-26 12:00:00 1111
2 2005-12-26 12:00:01 2222
3 2005-12-26 12:00:02 1111
4 2005-12-26 12:10:03 2222
5 2005-12-26 12:10:04 1111
6 2005-12-26 12:10:05 1111
7 2005-12-26 12:20:06 2222
8 2005-12-26 12:20:07 1111
9 2005-12-26 12:20:08 2222
10 2005-12-26 12:20:09 1111

统计最后一批连续记录号为1111的记录数量,
假设间隔小于1分钟的为连续记录,那么查询结果就是

8 2005-12-26 12:20:07 1111
10 2005-12-26 12:20:09 1111

结果为2个记录
因为记录6的时间与8的时间间隔大于1分钟

不知道大家明白我的意思.

我希望创建一个过程,参数为连续间隔的时间和记录号
结果就是统计最后一批的连续数量或者记录

谢谢!!!

...全文

Java并发编程实战：深入探索线程池与Executor框架线程池是一种基于池化思想管理线程的资源管理技术。它预先创建并维护一定数量的线程，当有任务到来时，直接从线程池中获取空闲线程执行任务，而不是每次都创建新线程。降低资源消耗：减少了线程的创建和销毁开销。提高响应速度：由于线程池中的线程已经预先创建，可以快速响应任务请求。提高系统吞吐量：通过复用线程，系统能够同时处理更多任务。Java的线程池与Executor框架为高效并发编程提供了强有力的支持。通过合理配置线程池参数、正确提交和处理任务，可以显著提升系统的性能和稳定性。阅读详情

Thanks4Help

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

yingfeiqiyue 2005-12-29

打赏
举报

看了半天还没有明白！
该学习了

liujx_1999 2005-12-29

打赏
举报

create table t (id int,_datetime datetime,_count varchar(4))
insert t
select 1, '2005-12-26 12:00:00', '1111' union all
select 2, '2005-12-26 12:00:01', '2222' union all
select 3, '2005-12-26 12:00:02', '1111' union all
select 4, '2005-12-26 12:10:03', '2222' union all
select 5, '2005-12-26 12:10:04', '1111' union all
select 6, '2005-12-26 12:10:05', '1111' union all
select 7, '2005-12-26 12:20:06', '2222' union all
select 8, '2005-12-26 12:20:07', '1111' union all
select 9, '2005-12-26 12:20:08', '2222' union all
select 10, '2005-12-26 12:20:09', '1111'

alter proc sp_returncount
@count int
as

select Convert(varchar(16),_datetime,120) as _datetime , _count into #tt from t --where _datetime between @datetime_B and @datetime_E
group by Convert(varchar(16),_datetime,120),_count having count(*)>1

select top 1 * into #BB from #tt where _count=@count order by _datetime desc

select * from t A where EXISTS (select * FROM #BB B WHERE Convert(varchar(16),a._datetime,120)=Convert(varchar(16),b._datetime,120) AND a._count=b._count) and a._count=@count
drop table #tt,#BB
Go
exec sp_returncount 1111
/*
id _datetime _count
----------- ---------------------- ------
8 2005-12-26 12:20:07.000 1111
10 2005-12-26 12:20:09.000 1111
*/

mislrb 2005-12-29

打赏
举报

我不想输入一个时间段.最好能够自己找到最后一批,没有时间段的限制.

---------------------------------------------------------------
自己不能改改用么，也太懒了兄弟

不要时间段限制，去掉就行了啊，加个时段限制也是为了更好的性能，如果记录到时疯长几百万条，不知道select into的执行时间你还能不能接受

liujx_1999 2005-12-29

打赏
举报

create table t (id int,_datetime datetime,_count varchar(4))
insert t
select 1, '2005-12-26 12:00:00', '1111' union all
select 2, '2005-12-26 12:00:01', '2222' union all
select 3, '2005-12-26 12:00:02', '1111' union all
select 4, '2005-12-26 12:10:03', '2222' union all
select 5, '2005-12-26 12:10:04', '1111' union all
select 6, '2005-12-26 12:10:05', '1111' union all
select 7, '2005-12-26 12:20:06', '2222' union all
select 8, '2005-12-26 12:20:07', '1111' union all
select 9, '2005-12-26 12:20:08', '2222' union all
select 10, '2005-12-26 12:20:09', '1111'

create proc sp_returncount
@datetime_B datetime,
@datetime_E datetime,
@count int
as

select Convert(varchar(16),_datetime,120) as _datetime , _count into #tt from t where _datetime between @datetime_B and @datetime_E
group by Convert(varchar(16),_datetime,120),_count having count(*)>1
select * from t A where EXISTS (select * FROM #tt B WHERE Convert(varchar(16),a._datetime,120)=Convert(varchar(16),b._datetime,120) AND a._count=b._count) and a._count=@count

drop table #tt
Go
exec sp_returncount '2005-12-26 12:00:00','2005-12-26 12:20:00',1111
/*
id _datetime _count
----------- ---------------------- ------
1 2005-12-26 12:00:00.000 1111
3 2005-12-26 12:00:02.000 1111
5 2005-12-26 12:10:04.000 1111
6 2005-12-26 12:10:05.000 1111
*/

Thanks4Help 2005-12-29

打赏
举报

to:cosio()
谢谢!能否不使用游标啊!游标是慢了点

to:mislrb(aben)
谢谢!我不想输入一个时间段.最好能够自己找到最后一批,没有时间段的限制.

mislrb 2005-12-28

打赏
举报

create table t (id int,rec_time datetime,rec_no varchar(4))
insert t
select 1, '2005-12-26 12:00:00', '1111' union all
select 2, '2005-12-26 12:00:01', '2222' union all
select 3, '2005-12-26 12:00:02', '1111' union all
select 4, '2005-12-26 12:10:03', '2222' union all
select 5, '2005-12-26 12:10:04', '1111' union all
select 6, '2005-12-26 12:10:05', '1111' union all
select 7, '2005-12-26 12:20:06', '2222' union all
select 8, '2005-12-26 12:20:07', '1111' union all
select 9, '2005-12-26 12:20:08', '2222' union all
select 10, '2005-12-26 12:20:09', '1111'

if exists(select 1 from sysobjects where id=object_id(N'p_getResult') and xtype='P')
drop procedure p_getResult
go
create procedure p_getResult
@stDate datetime ='2005-12-26 12:00:00',
@enDate datetime ='2005-12-26 12:20:09',
@interval int =1, --minute
@id int ='1111'
as
select nid=identity(int,1,1),*
into #t
from t
where rec_no=@id and
rec_time between @stDate and @enDate
order by rec_time desc

select distinct top 2 a.id,a.rec_time,a.rec_no
from #t a,#t b
where exists(select 1 from #t where nid=a.nid+1 and datediff(second,b.rec_time,a.rec_time)<@interval)
order by a.rec_time desc
drop table #t
go

exec dbo.p_getResult '2005-12-26 12:00:00','2005-12-26 12:20:09',60,'1111'

exec dbo.p_getResult

/*
id rec_time rec_no
----------- ------------------------------------------------------ ------
10 2005-12-26 12:20:09.000 1111
8 2005-12-26 12:20:07.000 1111

*/

cosio 2005-12-28

打赏
举报

create procedure sp_temp
(

@time datetime, ---相隔的秒数
@ID int

)

Declare @Time1 datetime
Declare @Time2 datetime
declare @number char(20)
declare @time3 datetime

select @Time1= Time , @mnumber =mnumber from @t where id=@id

begin

declare Tempcur cursor
for
select @time2= time from @t where id<@id

open tempcur
fetch next from tempcur into @time

while @@fetch_status=0
begin

select * from @T where datediff(second,@time3,@time1) <@time and mnumber =@number

end
end

没有写完，因过程要去调试比较麻烦，所以就不写了

cosio 2005-12-28

打赏
举报

用游标一条一条是可以，只是效率不高！

怎么统计在一段连续时间的记录数量

相关推荐