求算法（求某个时间段的数据频度）

Working20000 2009-09-28 02:49:25

有这么一需求
表A（数据量在百万级）：
id 呼叫号码通话时长来电时间来电归属地
33661 86720396 106 2009-09-28 08:21:30.000 浙江金华
33662 13968084058 71 2009-09-28 08:21:40.000 浙江杭州中国移动
33663 85142063 110 2009-09-28 08:22:01.000 浙江杭州
33664 013905885657 29 2009-09-28 08:22:27.000 江苏无锡中国移动
33665 13957120681 209 2009-09-28 08:33:23.000 浙江绍兴
33666 13646713309 27 2009-09-28 08:37:48.000 上海
33669 13957120681 151 2009-09-28 08:37:44.000 浙江绍兴
33670 13905811614 36 2009-09-28 08:40:36.000 浙江金华
33672 13588476612 37 2009-09-28 08:41:52.000 江苏连云港
33673 61068668 54 2009-09-28 08:41:54.000 福建厦门
33675 85355937 20 2009-09-28 08:41:55.000 浙江金华
33676 56861756 122 2009-09-28 08:43:31.000 浙江杭州
33679 013905885657 523 2009-09-28 08:39:00.000 浙江杭州中国联通
33680 88126215 27 2009-09-28 08:47:35.000 上海

现在的问题是需要获得在哪个时间段的数据数（记录条数的峰值，比如30秒内的峰值，60秒内的峰值，半小时内的峰值）
比如以上数据中
30秒内的峰值就是:3（发生在2009-09-28 08:41:54.000与2009-09-28 08:41:55.000之间 id为 33673和33672，33675）；
60秒内的峰值就是：4（发生在2009-09-28 08:21:30.000与2009-09-28 08:22:27.000之间 id为 33661和33662，33663，33664）；

换言之：就是需要一个方法给出时长参数（30秒，60秒，90秒），求出这个时长内最大的数据条数，并得出发生在哪个时间段

...全文

325 17 打赏收藏转发到动态举报

写回复

用AI写文章

17 条回复

切换为时间正序

请发表友善的回复…

发表回复

pupstar 2010-01-06

打赏
举报

来顶过

叶子 2009-09-30

打赏
举报

速度问题给时间字段加上索引。

Working20000 2009-09-29

打赏
举报

但是现在就是有个效率问题，我现在在某个时间段内测试这个，运行所花的时间在10秒（总数据量才400条）;比较长。假如要在这个100万的表中找出需要的统计所花的时间岂不是一个要被人杀的数字。。。
可能在表设计的时候存在问题。需要需要很长时间

Working20000 2009-09-29

打赏
举报

谢谢！ 5楼7楼基本就是我要的答案了！

gsk09 2009-09-28

打赏
举报

[Quote=引用 7 楼 csdyyr 的回复:]
SQL codeDECLARE@TBTABLE([id]VARCHAR(5),[呼叫号码]VARCHAR(12),[通话时长]INT,[来电时间]DATETIME)INSERT@TBSELECT'33661','86720396',106,'2009-09-28 08:21:30.000'UNIONALLSELECT'33662','13968084058',71,'2009-09-28 08:2¡
[/Quote]



declare @A table ([id] int,[No] varchar(12),[tLength] int,[time] datetime,[tFrom] varchar(24))

insert into @A

select 33661,'86720396',106,'2009-09-28 08:21:30.000','浙江 金' union all

select 33662,'13968084058',71,'2009-09-28 08:21:40.000','浙江 杭州 中国移' union all

select 33663,'85142063',110,'2009-09-28 08:22:01.000','浙江 杭州' union all

select 33664,'013905885657',29,'2009-09-28 08:22:27.000','江 中国移' union all

select 33665,'13957120681',209,'2009-09-28 08:33:23.000','浙江' union all

select 33666,'13646713309',27,'2009-09-28 08:37:48.000','上海' union all

select 33669,'13957120681',151,'2009-09-28 08:37:44.000','浙江' union all

select 33670,'13905811614',36,'2009-09-28 08:40:36.000','浙江' union all

select 33672,'13588476612',37,'2009-09-28 08:41:52.000','江' union all

select 33673,'61068668',54,'2009-09-28 08:41:54.000','福建' union all

select 33675,'85355937',20,'2009-09-28 08:41:55.000','浙江' union all

select 33676,'56861756',122,'2009-09-28 08:43:31.000','浙江' union all

select 33679,'013905885657',523,'2009-09-28 08:39:00.000','浙江' union all

select 33680,'88126215',27,'2009-09-28 08:47:35.000','上海'



declare @scd smallint

set @scd = 30



select

	a.*

from @a a

	,(

	select top 1 with ties id,time

	from @a a

	order by (select count(*) 

				from @a a1 

				where datediff(s,a.time,a1.time) between 0 and @scd

			) desc

	)a1

where datediff(s,a1.time,a.time) between 0 and @scd

order by a.time



id       No           tLength     time                    tFrom

-------- ------------ ----------- ----------------------- --------

33672    13588476612  37          2009-09-28 08:41:52.000 江

33673    61068668     54          2009-09-28 08:41:54.000 福建

33675    85355937     20          2009-09-28 08:41:55.000 浙江



(3 行)

华夏小卒 2009-09-28

打赏
举报



select *,px=identity(int,1,1) into #t from tb

select top 1000 number=identity(int,1,1) into #s from sysobjects a, sysobjects b  





select top 1 a.id,b.id,a.来电时间,b.来电时间, number=number+1 from #s s,#t a,#t b

where 

	s.number+a.px =b.px 

	and datediff(ss,a.来电时间,b.来电时间) between 0 and 60

order by number desc





id          id          来电时间                    来电时间                    number

----------- ----------- ----------------------- ----------------------- -----------

33661       33664       2009-09-28 08:21:30.000 2009-09-28 08:22:27.000 4



(1 行受影响)



drop table #t

drop table #s

soft_wsx 2009-09-28

打赏
举报

牛人们！

华夏小卒 2009-09-28

打赏
举报



if object_id('tb')is not null drop table tb

go

CREATE TABLE tb(id int,呼叫号码  varchar(15),通话时长 int,来电时间  datetime,来电归属地 varchar(30))

INSERT tb SELECT

33661, '86720396'    ,106, '2009-09-28 08:21:30.000', N'浙江 金华'			UNION ALL SELECT 

33662, '13968084058' , 71 ,'2009-09-28 08:21:40.000', N'浙江 杭州 中国移动' UNION ALL SELECT 

33663, '85142063'    ,110, '2009-09-28 08:22:01.000', N'浙江 杭州'			UNION ALL SELECT 

33664, '013905885657' ,29, '2009-09-28 08:22:27.000', N'江苏 无锡 中国移动' UNION ALL SELECT 

33665, '13957120681' ,209, '2009-09-28 08:33:23.000', N'浙江 绍兴'			UNION ALL SELECT 

33666, '13646713309'   ,27, '2009-09-28 08:37:48.000', N'上海 '				UNION ALL SELECT 

33669, '13957120681' ,151, '2009-09-28 08:37:44.000', N'浙江 绍兴'			UNION ALL SELECT 

33670, '13905811614'  ,36, '2009-09-28 08:40:36.000', N'浙江 金华'			uNION ALL SELECT 

33672, '13588476612'  ,37, '2009-09-28 08:41:52.000', N'江苏 连云港'			UNION ALL SELECT 

33673, '61068668'     ,54, '2009-09-28 08:41:54.000', N'福建 厦门'			uNION ALL SELECT 

33675, '85355937'     ,20, '2009-09-28 08:41:55.000', N'浙江 金华'			 UNION ALL SELECT 

33676, '56861756'    ,122, '2009-09-28 08:43:31.000', N'浙江 杭州'			 UNION ALL SELECT 

33679, '013905885657',523, '2009-09-28 08:39:00.000', N'浙江 杭州 中国联通'  UNION ALL SELECT 

33680, '88126215'    , 27, '2009-09-28 08:47:35.000', N'上海' 



select *,px=identity(int,1,1) into #t from tb

select top 1000 number=identity(int,1,1) into #s from sysobjects a, sysobjects b  



DECLARE @SJ INT

SET @SJ=30   ---30秒



select top 1 a.id,b.id,a.来电时间,b.来电时间, number from #s s,#t a,#t b

where 

	s.number+a.px =b.px 

	and datediff(ss,a.来电时间,b.来电时间) between 0 and 30

order by number desc





id          id          来电时间                    来电时间                    number

----------- ----------- ----------------------- ----------------------- -----------

33672       33675       2009-09-28 08:41:52.000 2009-09-28 08:41:55.000 2



(1 行受影响)



drop table #t

drop table #s

bulesky_xshp 2009-09-28

打赏
举报

　5楼，7楼强

fanzhouqi 2009-09-28

打赏
举报



declare @a table(id int, 呼叫号码  varchar(20),   通话时长 int, 来电时间 datetime,  来电归属地 varchar(50))

insert @a

select 33661, '86720396',  106,   '2009-09-28 08:21:30.000', '浙江 金华 ' union all

select 33662, '13968084058', 71,  '2009-09-28 08:21:40.000', '浙江 杭州 中国移动 ' union all

select 33663, '85142063',    110, '2009-09-28 08:22:01.000', '浙江 杭州 ' union all

select 33664, '013905885657', 29, '2009-09-28 08:22:27.000', '江苏 无锡 中国移动 ' union all

select 33665, '13957120681', 209, '2009-09-28 08:33:23.000', '浙江 绍兴 ' union all

select 33666, '13646713309', 27,  '2009-09-28 08:37:48.000', '上海 ' union all

select 33669, '13957120681', 151, '2009-09-28 08:37:44.000', '浙江 绍兴 ' union all

select 33670, '13905811614', 36,  '2009-09-28 08:40:36.000', '浙江 金华 ' union all

select 33672, '13588476612', 37,  '2009-09-28 08:41:52.000', '江苏 连云港 ' union all

select 33673, '61068668',    54,  '2009-09-28 08:41:54.000', '福建 厦门 ' union all

select 33675, '85355937',    20,  '2009-09-28 08:41:55.000', '浙江 金华 ' union all

select 33676, '56861756',    122, '2009-09-28 08:43:31.000', '浙江 杭州 ' union all

select 33679, '013905885657', 523,'2009-09-28 08:39:00.000', '浙江 杭州 中国联通 ' union all

select 33680, '88126215',     27, '2009-09-28 08:47:35.000', '上海 '





if object_id('tempdb..#') is not null

	drop table #

declare @s int

set @s = 30





select count = count(1)

	,start = max(a.来电时间)

	,[end] = max(b.来电时间)

into #

from @a a

	, @a b 

where (datediff(ss,a.来电时间,b.来电时间)) <= @s

	and (datediff(ss,a.来电时间,b.来电时间))>=0

group by a.id



	

select * from #

where count = (select max(count) from #)



count       start                                                  end                                                    

----------- ------------------------------------------------------ ------------------------------------------------------ 

3           2009-09-28 08:41:52.000                                2009-09-28 08:41:55.000

蹭分

csdyyr 2009-09-28

打赏
举报

DECLARE @TB TABLE([id] VARCHAR(5), [呼叫号码] VARCHAR(12), [通话时长] INT, [来电时间] DATETIME)

INSERT @TB 

SELECT '33661', '86720396', 106, '2009-09-28 08:21:30.000' UNION ALL 

SELECT '33662', '13968084058', 71, '2009-09-28 08:21:40.000' UNION ALL 

SELECT '33663', '85142063', 110, '2009-09-28 08:22:01.000' UNION ALL 

SELECT '33664', '013905885657', 29, '2009-09-28 08:22:27.000' UNION ALL 

SELECT '33665', '13957120681', 209, '2009-09-28 08:33:23.000' UNION ALL 

SELECT '33666', '13646713309', 27, '2009-09-28 08:37:48.000' UNION ALL 

SELECT '33669', '13957120681', 151, '2009-09-28 08:37:44.000' UNION ALL 

SELECT '33670', '13905811614', 36, '2009-09-28 08:40:36.000' UNION ALL 

SELECT '33672', '13588476612', 37, '2009-09-28 08:41:52.000' UNION ALL 

SELECT '33673', '61068668', 54, '2009-09-28 08:41:54.000' UNION ALL 

SELECT '33675', '85355937', 20, '2009-09-28 08:41:55.000' UNION ALL 

SELECT '33676', '56861756', 122, '2009-09-28 08:43:31.000' UNION ALL 

SELECT '33679', '013905885657', 523, '2009-09-28 08:39:00.000' UNION ALL 

SELECT '33680', '88126215', 27, '2009-09-28 08:47:35.000'





SELECT TOP 1  WITH TIES  id,来电时间

INTO #

FROM (

SELECT id,来电时间,(SELECT COUNT(*) FROM @TB AS TB WHERE DATEDIFF(S,TA.来电时间,TB.来电时间) BETWEEN 0 AND 29) AS CNT

FROM @TB AS TA

) TC

ORDER BY CNT DESC



SELECT TA.id,TA.来电时间

FROM @TB AS TA , # AS TB

WHERE  TA.id>=TB.id

        AND DATEDIFF(S,TB.来电时间,TA.来电时间) BETWEEN 0 AND 29



DROP TABLE #

/*

id    来电时间                                                   

----- ------------------------------------------------------ 

33672 2009-09-28 08:41:52.000

33673 2009-09-28 08:41:54.000

33675 2009-09-28 08:41:55.000

*/

--小F-- 2009-09-28

打赏
举报

5楼的理解能力强佩服

风_雨_晴 2009-09-28

打赏
举报

60秒时间段

declare @a table(id int, 呼叫号码  varchar(20),   通话时长 int, 来电时间 datetime,  来电归属地 varchar(50))

insert @a

select 33661, '86720396',  106,   '2009-09-28 08:21:30.000', '浙江 金华 ' union all

select 33662, '13968084058', 71,  '2009-09-28 08:21:40.000', '浙江 杭州 中国移动 ' union all

select 33663, '85142063',    110, '2009-09-28 08:22:01.000', '浙江 杭州 ' union all

select 33664, '013905885657', 29, '2009-09-28 08:22:27.000', '江苏 无锡 中国移动 ' union all

select 33665, '13957120681', 209, '2009-09-28 08:33:23.000', '浙江 绍兴 ' union all

select 33666, '13646713309', 27,  '2009-09-28 08:37:48.000', '上海 ' union all

select 33669, '13957120681', 151, '2009-09-28 08:37:44.000', '浙江 绍兴 ' union all

select 33670, '13905811614', 36,  '2009-09-28 08:40:36.000', '浙江 金华 ' union all

select 33672, '13588476612', 37,  '2009-09-28 08:41:52.000', '江苏 连云港 ' union all

select 33673, '61068668',    54,  '2009-09-28 08:41:54.000', '福建 厦门 ' union all

select 33675, '85355937',    20,  '2009-09-28 08:41:55.000', '浙江 金华 ' union all

select 33676, '56861756',    122, '2009-09-28 08:43:31.000', '浙江 杭州 ' union all

select 33679, '013905885657', 523,'2009-09-28 08:39:00.000', '浙江 杭州 中国联通 ' union all

select 33680, '88126215',     27, '2009-09-28 08:47:35.000', '上海 '



declare @s int

set @s = 60



select count(1) as 记录数, a.id, a.来电时间 as 开始时间, max(t.来电时间) as 结束时间 from @a a join @a t on t.来电时间 > = a.来电时间 and t.来电时间 < = dateadd(ss, @s, a.来电时间)

  group by a.id, a.来电时间

/*

记录数         id          开始时间                                                   结束时间                                                   

----------- ----------- ------------------------------------------------------ ------------------------------------------------------ 

4           33661       2009-09-28 08:21:30.000                                2009-09-28 08:22:27.000

3           33662       2009-09-28 08:21:40.000                                2009-09-28 08:22:27.000

2           33663       2009-09-28 08:22:01.000                                2009-09-28 08:22:27.000

1           33664       2009-09-28 08:22:27.000                                2009-09-28 08:22:27.000

1           33665       2009-09-28 08:33:23.000                                2009-09-28 08:33:23.000

2           33669       2009-09-28 08:37:44.000                                2009-09-28 08:37:48.000

1           33666       2009-09-28 08:37:48.000                                2009-09-28 08:37:48.000

1           33679       2009-09-28 08:39:00.000                                2009-09-28 08:39:00.000

1           33670       2009-09-28 08:40:36.000                                2009-09-28 08:40:36.000

3           33672       2009-09-28 08:41:52.000                                2009-09-28 08:41:55.000

2           33673       2009-09-28 08:41:54.000                                2009-09-28 08:41:55.000

1           33675       2009-09-28 08:41:55.000                                2009-09-28 08:41:55.000

1           33676       2009-09-28 08:43:31.000                                2009-09-28 08:43:31.000

1           33680       2009-09-28 08:47:35.000                                2009-09-28 08:47:35.000

*/

风_雨_晴 2009-09-28

打赏
举报

declare @a table(id int, 呼叫号码  varchar(20),   通话时长 int, 来电时间 datetime,  来电归属地 varchar(50))

insert @a

select 33661, '86720396',  106,   '2009-09-28 08:21:30.000', '浙江 金华 ' union all

select 33662, '13968084058', 71,  '2009-09-28 08:21:40.000', '浙江 杭州 中国移动 ' union all

select 33663, '85142063',    110, '2009-09-28 08:22:01.000', '浙江 杭州 ' union all

select 33664, '013905885657', 29, '2009-09-28 08:22:27.000', '江苏 无锡 中国移动 ' union all

select 33665, '13957120681', 209, '2009-09-28 08:33:23.000', '浙江 绍兴 ' union all

select 33666, '13646713309', 27,  '2009-09-28 08:37:48.000', '上海 ' union all

select 33669, '13957120681', 151, '2009-09-28 08:37:44.000', '浙江 绍兴 ' union all

select 33670, '13905811614', 36,  '2009-09-28 08:40:36.000', '浙江 金华 ' union all

select 33672, '13588476612', 37,  '2009-09-28 08:41:52.000', '江苏 连云港 ' union all

select 33673, '61068668',    54,  '2009-09-28 08:41:54.000', '福建 厦门 ' union all

select 33675, '85355937',    20,  '2009-09-28 08:41:55.000', '浙江 金华 ' union all

select 33676, '56861756',    122, '2009-09-28 08:43:31.000', '浙江 杭州 ' union all

select 33679, '013905885657', 523,'2009-09-28 08:39:00.000', '浙江 杭州 中国联通 ' union all

select 33680, '88126215',     27, '2009-09-28 08:47:35.000', '上海 '



declare @s int

set @s = 30



select count(1) as 记录数, a.id, a.来电时间 as 开始时间, max(t.来电时间) as 结束时间 from @a a join @a t on t.来电时间 > = a.来电时间 and t.来电时间 < = dateadd(ss, @s, a.来电时间)

  group by a.id, a.来电时间

/*

记录数         id          开始时间                                                   结束时间                                                   

----------- ----------- ------------------------------------------------------ ------------------------------------------------------ 

2           33661       2009-09-28 08:21:30.000                                2009-09-28 08:21:40.000

2           33662       2009-09-28 08:21:40.000                                2009-09-28 08:22:01.000

2           33663       2009-09-28 08:22:01.000                                2009-09-28 08:22:27.000

1           33664       2009-09-28 08:22:27.000                                2009-09-28 08:22:27.000

1           33665       2009-09-28 08:33:23.000                                2009-09-28 08:33:23.000

2           33669       2009-09-28 08:37:44.000                                2009-09-28 08:37:48.000

1           33666       2009-09-28 08:37:48.000                                2009-09-28 08:37:48.000

1           33679       2009-09-28 08:39:00.000                                2009-09-28 08:39:00.000

1           33670       2009-09-28 08:40:36.000                                2009-09-28 08:40:36.000

3           33672       2009-09-28 08:41:52.000                                2009-09-28 08:41:55.000

2           33673       2009-09-28 08:41:54.000                                2009-09-28 08:41:55.000

1           33675       2009-09-28 08:41:55.000                                2009-09-28 08:41:55.000

1           33676       2009-09-28 08:43:31.000                                2009-09-28 08:43:31.000

1           33680       2009-09-28 08:47:35.000                                2009-09-28 08:47:35.000

*/