根据时间统计的问题

yiyi0518 2009-11-16 01:56:32

表A (
dt varchar(20) --时间格式‘yyyy-mm-dd hh:nn:ss’每5分钟一个时间点，每小时从00分开始到 55分共12个点
in_count decimal(12,1) --每5分钟取一个值
out_count decimal(12,1) --每5分钟取一个值

)

要将数据倒入表B
(
dt varchar(20) --时间格式‘yyyy-mm-dd hh:nn:ss’每30分钟一个时间点
in_count decimal(12,1) --每30分钟取一个值，即取6个5分钟的平均值
out_count decimal(12,1) --每30分钟取一个值，即取6个5分钟的平均值

)

请教各位DB 达人。。。怎么写sql 语句实现之，万分感激~~

...全文

144 7 打赏收藏转发到动态举报

写回复

用AI写文章

7 条回复

切换为时间正序

请发表友善的回复…

发表回复

nianran520 2009-11-16

打赏
举报



--或者

select dt,

round(avg(in_count),1) as in_count,

round(avg(out_count),1) as out_count

from (

select case when substring(dt,15,2) <= '25' then 

convert(varchar(20),dateadd(mi,25-cast(substring(dt,15,2) as int),dt),120)

else convert(varchar(20),dateadd(mi,55-cast(substring(dt,15,2) as int),dt),120)

end as dt,

in_count,out_count 

from @table t )h

group by dt

nianran520 2009-11-16

打赏
举报

--> 测试数据：@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,1

--查询

select dt,

round(avg(in_count),1) as in_count,

round(avg(out_count),1) as out_count

from (

select case when substring(dt,15,2) = '00' then convert(varchar(20),dateadd(mi,25,dt),120)

when substring(dt,15,2) <= '25'

then stuff(dt,15,2,'25')

else stuff(dt,15,2,'55') 

end as dt,

in_count,out_count 

from @table t )h

group by dt

--结果

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

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

david0927cs2006 2009-11-16

打赏
举报

学习学习

yiyi0518 2009-11-16

打赏
举报

楼上的B表日期要改为每半小时计数
比如
2009-11-16 13:00:00 统计 2009-11-16 13:00:00 - 2009-11-16 13:25:00

2009-11-16 13:30:00 统计 2009-11-16 13:30:00 - 2009-11-16 13:55:00

的数据

-狙击手- 2009-11-16

打赏
举报

insert into tb 

select dt,sum(in_count),sum(out_count) 

from (select convert(char(14),dt,120)+case when datepart(mi,dt) < 30 then '00' else '30' end+':00' as dt,in_count,out_count from ta) a 

group by dt

-狙击手- 2009-11-16

打赏
举报

insert into tb
select dt,sum(in_count),sum(out_count)
from (select convert(char(14),dt,120)+case when datepart(mi,dt) < 30 then '00' else '30' end+':oo' as dt,in_count,out_count from ta) a
group by dt