导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

100分求一sql, 按时间段分组统计?

zhaixing0101 2007-11-30 10:12:13
把时间按5分钟分隔, 来统计数据, 求解

表结构:
时间 金额
2007-1-1 10:00:23 8
2007-1-1 10:01:24 4
2007-1-1 10:05:00 2
2007-1-1 10:06:12 3
2007-1-1 10:08:00 1
2007-1-1 10:12:11 5
......
统计后
时间段 行数 总金额
2007-1-1 10:05:00 3 14
2007-1-1 10:10:00 2 4
2007-1-1 10:15:00 1 5
......

...全文
846 点赞 收藏 11
写回复
11 条回复
切换为时间正序
请发表友善的回复…
发表回复
leo_lesley 2007-11-30
楼主的分真多啊,每回都开100分的贴
回复
wuyanbing 2007-11-30
先顶
回复
fwacky 2007-11-30


create table T (date datetime, value int)
insert into T select '2007-1-1 10:00:23',8
insert into T select '2007-1-1 10:01:24',4
insert into T select '2007-1-1 10:05:00',2
insert into T select '2007-1-1 10:06:12',3
insert into T select '2007-1-1 10:08:00',1
insert into T select '2007-1-1 10:12:11',5


declare @min datetime
select @min=convert(datetime,convert(varchar(16),min(date),120)) from T

select dateadd(minute,((datediff(minute ,@min, date)-1)/5+1)*5,@min) as [date] ,count(*) as cout ,sum(value) as sumary
from T
group by dateadd(minute,((datediff(minute ,@min, date)-1)/5+1)*5,@min)


回复
fa_ge 2007-11-30

create table # (
时间 datetime,
金额 int
)
insert # select
'2007-1-1 10:00:23', 8
union all select
'2007-1-1 10:01:24', 4
union all select
'2007-1-1 10:05:00', 2
union all select
'2007-1-1 10:06:12', 3
union all select
'2007-1-1 10:08:00', 1
union all select
'2007-1-1 10:12:11', 5



select
[time],
count(1)as 'line',
sum( 金额 )as ' 金额 '
from
(
select
*,
[time]=case when 时间<='2007-01-01 10:05:00'then '2007-01-01 10:05:00'
else
dateadd(minute,(datediff(minute,'2007-01-01 10:05:00',时间)/5+1) *5 ,'2007-01-01 10:05:00')
end

from #
)a
group by [time]

/*
time line 金额
------------------------------------------------------ ----------- -----------
2007-01-01 10:05:00.000 3 14
2007-01-01 10:10:00.000 2 4
2007-01-01 10:15:00.000 1 5

(所影响的行数为 3 行)
*/
回复
fa_ge 2007-11-30

create table # (
时间 datetime,
金额 int
)
insert # select
'2007-1-1 10:00:23', 8
union all select
'2007-1-1 10:01:24', 4
union all select
'2007-1-1 10:05:00', 2
union all select
'2007-1-1 10:06:12', 3
union all select
'2007-1-1 10:08:00', 1
union all select
'2007-1-1 10:12:11', 5



select time,count(1)as 'line',sum( 金额 )as ' 金额 '
from
(
select *, [time]=case when 时间<='2007-01-01 10:05:00'then '2007-01-01 10:05:00'
else
dateadd(minute,(abs(datediff(minute,时间,'2007-01-01 10:05:00'))/5+1) *5 ,'2007-01-01 10:05:00')
end

from #
)a
group by [time]

/*
time line 金额
------------------------------------------------------ ----------- -----------
2007-01-01 10:05:00.000 3 14
2007-01-01 10:10:00.000 2 4
2007-01-01 10:15:00.000 1 5

(所影响的行数为 3 行)

*/
回复
dawugui 2007-11-30
create table tb(时间 datetime , 金额 int)
insert into tb values('2007-1-1 10:00:23' , 8 )
insert into tb values('2007-1-1 10:01:24' , 4 )
insert into tb values('2007-1-1 10:05:00' , 2 )
insert into tb values('2007-1-1 10:06:12' , 3 )
insert into tb values('2007-1-1 10:08:00' , 1 )
insert into tb values('2007-1-1 10:12:11' , 5 )
go

--时间段>=10:00:00 and 时间段<10:05:00
select dateadd(mi,(datediff(mi,convert(varchar(10),dateadd(ss,-1,时间),120),dateadd(ss,-1,时间))/5)*5,convert(varchar(10),时间,120)) as 时间段,
count(*) as 行数,
sum(金额) as 总金额
from tb
group by dateadd(mi,(datediff(mi,convert(varchar(10),dateadd(ss,-1,时间),120),dateadd(ss,-1,时间))/5)*5,convert(varchar(10),时间,120))
/*
时间段 行数 总金额
------------------------------------------------------ ----------- -----------
2007-01-01 10:00:00.000 3 14
2007-01-01 10:05:00.000 2 4
2007-01-01 10:10:00.000 1 5
(所影响的行数为 3 行)
*/

--时间段>10:00:00 and 时间段<=10:05:00
select dateadd(mi,(datediff(mi,convert(varchar(10),dateadd(ss,1,时间),120),dateadd(ss,1,时间))/5)*5,convert(varchar(10),时间,120)) as 时间段,
count(*) as 行数,
sum(金额) as 总金额
from tb
group by dateadd(mi,(datediff(mi,convert(varchar(10),dateadd(ss,1,时间),120),dateadd(ss,1,时间))/5)*5,convert(varchar(10),时间,120))
/*
时间段 行数 总金额
------------------------------------------------------ ----------- -----------
2007-01-01 10:00:00.000 2 12
2007-01-01 10:05:00.000 3 6
2007-01-01 10:10:00.000 1 5

(所影响的行数为 3 行)
*/

drop table tb

回复
DengXingJie 2007-11-30
頂了
回复
好像有点差别,改下:


--环境
declare @t table (
时间 datetime,
金额 int
)
insert @t select
'2007-1-1 10:00:23', 8
union all select
'2007-1-1 10:01:24', 4
union all select
'2007-1-1 10:05:00', 2
union all select
'2007-1-1 10:06:12', 3
union all select
'2007-1-1 10:08:00', 1
union all select
'2007-1-1 10:12:11', 5

select dateadd(ss,(cast(datediff(ss,convert(varchar(10),时间,112),时间)-0.5 as int)/300+1)*300,convert(varchar(10),时间,112)) as 时间段,
count(*) as 行数,sum(金额) as 总金额
from @t
group by dateadd(ss,(cast(datediff(ss,convert(varchar(10),时间,112),时间)-0.5 as int)/300+1)*300,convert(varchar(10),时间,112))

--结果
时间段 行数 总金额
------------------------------------------------------ ----------- -----------
2007-01-01 10:05:00.000 3 14
2007-01-01 10:10:00.000 2 4
2007-01-01 10:15:00.000 1 5

(所影响的行数为 3 行)


回复
playwarcraft 2007-11-30

create table T (date datetime, value int)
insert into T select '2007-1-1 10:00:23',8
insert into T select '2007-1-1 10:01:24',4
insert into T select '2007-1-1 10:05:00',2
insert into T select '2007-1-1 10:06:12',3
insert into T select '2007-1-1 10:08:00',1
insert into T select '2007-1-1 10:12:11',5


declare @min datetime
select @min=convert(datetime,convert(varchar(16),min(date),120)) from T

select dateadd(minute,((datediff(minute ,@min, date)-1)/5+1)*5,@min) as [date] ,count(*) as cout ,sum(value) as sumary
from T
group by dateadd(minute,((datediff(minute ,@min, date)-1)/5+1)*5,@min)

/*
date cout sumary
------------------------------------------------------ ----------- -----------
2007-01-01 10:05:00.000 3 14
2007-01-01 10:10:00.000 2 4
2007-01-01 10:15:00.000 1 5
*/

drop table T


回复
dawugui 2007-11-30
--先给个按小时的分组.
比如有一个表timeTable,有字段id,score,inputDate.
数据如下
id score inputDate
1 2 '2007-4-5 3:33:33'
2 1 '2007-4-5 4:33:33'
3 4 '2007-3-5 3:33:33'
4 2 '2007-4-2 2:33:33'
我要按时间分组统计score,结果如下
0:00-1:00 0
1:00-2:00 0
2:00-3:00 2
3:00-4:00 6
4:00-5:00 1
5:00-6:00 0
....
23:00-24:00 0

declare @a table(id int, score int, inputDate smalldatetime)
insert @a select 1, 2, '2007-4-5 3:33:33'
union all select 2, 1, '2007-4-5 4:33:33'
union all select 3, 4, '2007-3-5 3:33:33'
union all select 4, 2, '2007-4-2 2:33:33'

select right(100+a,2)+':00-'+right(100+b,2)+':00',
sum(case when datepart(hour,inputdate) >=a and datepart(hour,inputdate)<b then score else 0 end)
from @a a ,
(select 0 a,1 b
union all select 1,2
union all select 2,3
union all select 3,4
union all select 4,5
union all select 5,6
union all select 6,7
union all select 7,8
union all select 8,9
union all select 9,10
union all select 10,11
union all select 11,12
union all select 12,13
union all select 13,14
union all select 14,15
union all select 15,16
union all select 16,17
union all select 17,18
union all select 18,19
union all select 19,20
union all select 20,21
union all select 21,22
union all select 22,23
union all select 23,24
)aa
group by right(100+a,2)+':00-'+right(100+b,2)+':00'



--------------- -----------
00:00-01:00 0
01:00-02:00 0
02:00-03:00 2
03:00-04:00 6
04:00-05:00 1
05:00-06:00 0
06:00-07:00 0
07:00-08:00 0
08:00-09:00 0
09:00-10:00 0
10:00-11:00 0
11:00-12:00 0
12:00-13:00 0
13:00-14:00 0
14:00-15:00 0
15:00-16:00 0
16:00-17:00 0
17:00-18:00 0
18:00-19:00 0
19:00-20:00 0
20:00-21:00 0
21:00-22:00 0
22:00-23:00 0
23:00-24:00 0

(所影响的行数为 24 行)
回复

--环境
declare @t table (
时间 datetime,
金额 int
)
insert @t select
'2007-1-1 10:00:23', 8
union all select
'2007-1-1 10:01:24', 4
union all select
'2007-1-1 10:05:00', 2
union all select
'2007-1-1 10:06:12', 3
union all select
'2007-1-1 10:08:00', 1
union all select
'2007-1-1 10:12:11', 5

select dateadd(mi,(datediff(mi,convert(varchar(10),时间,112),时间)/5+1)*5,convert(varchar(10),时间,112)) as 时间段,
count(*) as 行数,sum(金额) as 总金额
from @t
group by dateadd(mi,(datediff(mi,convert(varchar(10),时间,112),时间)/5+1)*5,convert(varchar(10),时间,112))

--结果
时间段 行数 总金额
------------------------------------------------------ ----------- -----------
2007-01-01 10:05:00.000 2 12
2007-01-01 10:10:00.000 3 6
2007-01-01 10:15:00.000 1 5

(所影响的行数为 3 行)
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告