有两表
user 表
userid name
一些数据如下
1 a
2 bdatas 表userid data rdate
一些数据如下
1 43 2009-10-26 16:10:00
2 34 2009-10-26 16:10:00
1 43 2009-10-26 16:12:00
2 34 2009-10-26 16:12:00
1 342 2009-10-26 16:22:00
2 233 2009-10-26 16:22:00
1 324 2009-10-26 16:25:00
2 32 2009-10-26 16:25:00
这个表数据更新很快,几乎每分钟都有数据
===============================
目前按这样
group by datepart(month,rdate),datepart(day,rdate),datepart(hour,rdate) ,datepart(minute,rdate)
可以统计每分,或者每小时。。但是现我想统计data ,是按5分钟一次的,应该怎么做呀。。
user 表
userid name
一些数据如下
1 a
2 bdatas 表userid data rdate
一些数据如下
1 43 2009-10-26 16:10:00
2 34 2009-10-26 16:10:00
1 43 2009-10-26 16:12:00
2 34 2009-10-26 16:12:00
1 342 2009-10-26 16:22:00
2 233 2009-10-26 16:22:00
1 324 2009-10-26 16:25:00
2 32 2009-10-26 16:25:00
这个表数据更新很快,几乎每分钟都有数据
===============================
目前按这样
group by datepart(month,rdate),datepart(day,rdate),datepart(hour,rdate) ,datepart(minute,rdate)
可以统计每分,或者每小时。。但是现我想统计data ,是按5分钟一次的,应该怎么做呀。。
解决方案 »
- 真心请教SQL2008问题
- SQLState:08001 and SQLState:HY000
- sqlite数据库保留两位小数
- 情况是这样的~!
- 安装问题!各位都来帮帮忙!
- 百万级数据量反应慢问题
- 如何用bulk insert导入不定列数的数据
- 在XP下安装SQLSERVER2000企业版的一个新方法(原创)
- 兄弟们,帮帮忙,急求答案,不胜感激!!! QQ:346185051
- 表對表更新的問題:A,B兩表均有ID,FIELD1,FIELD2..字段,現在以A.ID=B.ID為條件用B表更新A表中的FIELD1,FIELD2,如何寫存儲過程?
- 表的主键(user_id)为not null,如何写插入语句
- group by
参考这个吧:Declare @i int
Set @i=0
While @i<6
Begin
Insert into Cn_WorldLog02.dbo.OnlinePlayer
Select Top 1 Time,EnterWorldPlayer,SelectRolePlayer from Cn_WorldLog02.dbo.masterserverstatus
Where convert(char(13),Time,20)=convert(char(13),getdate(),20)
And right(convert(char(15),Time,20),1)=@i
And right(convert(char(16),Time,20),1)=0
Union
Select Top 1 Time,EnterWorldPlayer,SelectRolePlayer from Cn_WorldLog02.dbo.masterserverstatus
Where convert(char(13),Time,20)=convert(char(13),getdate(),20)
And right(convert(char(15),Time,20),1)=@i
And right(convert(char(16),Time,20),1)=5
Set @i=@i+1
End
00:58:00到23:59:59之间每小时执行一次就可以得到!
--> 测试数据:@table
declare @table table([dt] varchar(20),[in_count] decimal(12,1),[out_count] decimal(12,1))
insert @table
select '2009-11-15 00:00:00',10,1 union all
select '2009-11-15 00:05:00',10,1 union all
select '2009-11-15 00:10:00',3,2 union all
select '2009-11-15 00:15:00',10,1 union all
select '2009-11-15 00:20:00',5,7 union all
select '2009-11-15 00:25:00',10,5 union all
select '2009-11-15 00:30:00',10,1 union all
select '2009-11-15 00:35:00',10,4 union all
select '2009-11-15 00:40:00',10,1 union all
select '2009-11-15 00:45:00',10,8 union all
select '2009-11-15 00:50:00',10,1 union all
select '2009-11-15 00:55:00',10,1 union all
select '2009-11-15 01:00:00',10,11 union all
select '2009-11-15 01:05:00',6,12 union all
select '2009-11-15 01:10:00',10,1 union all
select '2009-11-15 01:15:00',10,1 union all
select '2009-11-15 01:20:00',10,14 union all
select '2009-11-15 01:25:00',10,1 union all
select '2009-11-15 01:30:00',10,1 union all
select '2009-11-15 01:35:00',10,1 union all
select '2009-11-15 01:40:00',10,1 union all
select '2009-11-15 01:45:00',10,1 union all
select '2009-11-15 01:50:00',10,1 union all
select '2009-11-15 01:55:00',10,1select case when substring(dt,15,2) <= '25' then
stuff(dt,15,2,'25')
else stuff(dt,15,2,'55')
end as dt,
round(avg(in_count),1) as in_count,
round(avg(out_count),1) as out_count
from @table
group by case when substring(dt,15,2) <= '25' then
stuff(dt,15,2,'25')
else stuff(dt,15,2,'55') end---------------------------------
2009-11-15 00:25:00 8.000000 2.800000
2009-11-15 00:55:00 10.000000 2.700000
2009-11-15 01:25:00 9.300000 6.700000
2009-11-15 01:55:00 10.000000 1.000000
rdate dates
2009-10-1 00:05:00 333
2009-10-1 00:10:00 3332
......................
select
r.name,
convert(varchar(10),t.[rdate],120) as [day],
convert(varchar(2),t.[rdate],108) as [hour],
sum(case when substring(convert(varchar(8),t.[rdate],108),4,2) between '00' and '05' then t.data else 0 end) as [0-5],
...........................
...........................from [datas] t join [user] r on t.userid=r.userid
group by r.name,
convert(varchar(10),t.[rdate],120) as [day],
convert(varchar(2),t.[rdate],108) as [hour]
如小F说的:
group by datepart(month,rdate),datepart(day,rdate),datepart(hour,rdate) ,datepart(minute,rdate) /5
但是这个假如的起点时间不是小时或者分钟不是被5整除的话,会存在问题,
解决是让datepart(minute,rdate)减去相应的时间便可.
如果您还要统计5分钟内并没有更新过数据的那一段也要显示出来的话,这可能要构造相关的时间段,然后关联统计了--表达可能不是很好...............
select
t.name,t.[rdate],t.[bhour],t.[ehour],
isnull(sum(r.data),0) as dates
from
(
select h.userid,h.name,
convert(varchar(10),dateadd(minute,f.number*5,'2009-10-26'),120) as [rdate],
convert(varchar(8),dateadd(minute,f.number*5,'2009-10-26'),108) as [bhour],
convert(varchar(8),dateadd(minute,(f.number+1)*5,'2009-10-26'),108) as [ehour]
from master..spt_values f,[user] h
where type = 'P'
and convert(varchar(10),dateadd(minute,f.number*5,'2009-10-26'),120) <= '2009-10-26'
) t left join [datas] r
on t.userid=r.userid and convert(varchar(10),r.rdate,120) = t.[rdate]
and convert(varchar(8),r.rdate,108) > t.bhour
and convert(varchar(8),r.rdate,108) <= t.ehour
group by t.name,t.[rdate],t.[bhour],t.[ehour]
解决是让datepart(minute,rdate)减去相应的时间便可.
请问一个 “相应的时间”如何确定是多少呢。谢谢