一个月的访问数据,每天大概是1000W左右,我想按每5分钟来查看次数人数,请高手给支个语句。比如:logtime 字段2 字段3
2009-05-25 15:04:34.143 WAP 13703008260
2009-05-25 16:37:27.547 WAP 15017646007
2009-05-25 10:36:34.027 WAP 13726015199
2009-05-25 05:46:28.203 WAP 13790441485
2009-05-25 00:57:53.863 WAP 15878633245
2009-04-26 13:15:22.913 WAP 13723748918
2009-04-26 15:25:51.767 WAP 15813434631
2009-04-26 15:26:37.040 WAP 15813434631结果:每5分钟 人数 次数
2009-05-25 15:04:34.143 WAP 13703008260
2009-05-25 16:37:27.547 WAP 15017646007
2009-05-25 10:36:34.027 WAP 13726015199
2009-05-25 05:46:28.203 WAP 13790441485
2009-05-25 00:57:53.863 WAP 15878633245
2009-04-26 13:15:22.913 WAP 13723748918
2009-04-26 15:25:51.767 WAP 15813434631
2009-04-26 15:26:37.040 WAP 15813434631结果:每5分钟 人数 次数
select sum(字段3)
from tb where logintime=(datediff(Minute,logintime,logintime))
group by datediff(Minute,logintime,logintime)
SELECT
dateadd(mi,-datepart(mi,logtime)%5,cast(dateadd(s,-30,logtime) as smalldatetime)),
dateadd(mi,5-datepart(mi,logtime)%5,cast(dateadd(s,-30,logtime) as smalldatetime)),
count(distinct 字段3) as '人数',
count(1) as '次数'
FROM t2
GROUP BY dateadd(mi,-datepart(mi,logtime)%5,cast(dateadd(s,-30,logtime) as smalldatetime)),dateadd(mi,5-datepart(mi,logtime)%5,cast(dateadd(s,-30,logtime) as smalldatetime))--转换成smalldatetime是为了把后面的“秒”和“毫秒”去掉
select t1,t2,count(distinct 字段3) as '人数',
count(1) as '次数'
FROM (select dateadd(mi,-datepart(mi,logtime)%5,cast(dateadd(s,-30,logtime) as smalldatetime)) as t1, dateadd(mi,5-datepart(mi,logtime)%5,cast(dateadd(s,-30,logtime) as smalldatetime)) as t2 ,* from tb) as a
group by t1,t2
2009-04-26 13:15:00 2009-04-26 13:20:00 是5分钟啊,怎么是6分钟呢?因为在转成smalldatetime时,会将秒的部分四舍五入,减去30秒,可以保证“分”的部分不会变。
05-09 .. ..
。
55-59 .. ..
是不是这样:SELECT
convert(varchar(8),dateadd(mi,-datepart(mi,logtime)%5,cast(dateadd(s,-30,logtime) as smalldatetime)),108),
convert(varchar(8),dateadd(mi,5-datepart(mi,logtime)%5,cast(dateadd(s,-30,logtime) as smalldatetime)),108),
count(distinct 字段3) as '人数',
count(1) as '次数'
FROM t2
GROUP BY convert(varchar(8),dateadd(mi,-datepart(mi,logtime)%5,cast(dateadd(s,-30,logtime) as smalldatetime)),108) ,convert(varchar(8),dateadd(mi,5-datepart(mi,logtime)%5,cast(dateadd(s,-30,logtime) as smalldatetime)),108)
use tampdb;
--创建统计表
create table dbo.test
(
quj varchar(50),
cishu int,
rshu int
);
--创建存储过程 等待5分钟
create procedure usp_waittime
as
begin
waitfor delay '00:05:00'
end;
--定义检测时间(次数)一天则@n<=288 ,一周则@n<=2016,一月则@n<=8640
declare @n int
set @n=0
while @n<5
begin
exec usp_waittime;
insert into dbo.test
select CAST(@n*5 as varchar(10))+'-'+CAST((@n+1)*5 as varchar(10)),
@n+1,
select count(1) from dbo.test_table;
set @n=@n+1;
end
/**
quj cishu rshu
-------------------------------------------------- ----------- -----------
0-5 1 15
5-10 2 15
10-15 3 15
15-20 4 15
20-25 5 15(5 行受影响)
**/'搞定!'
datepart(mi,logtime)-datepart(mi,logtime)%5,datepart(mi,logtime)+5-datepart(mi,logtime)%5,
count(distinct 字段3) as '人数',
count(1) as '次数'
FROM t2
GROUP BY datepart(mi,logtime)-datepart(mi,logtime)%5,datepart(mi,logtime)+5-datepart(mi,logtime)%5