# 根据时间统计的问题

yiyi0518 2009-11-16 01:56:32

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分钟取一个值

)

(
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分钟的平均值

)

...全文
124 7 打赏 收藏 举报

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

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
• 打赏
• 举报

sdnwjd 2009-11-16

• 打赏
• 举报

3.3w+

MS-SQL Server相关内容讨论专区

2009-11-16 01:56