本文介绍了获取以秒为单位的平均值,以分钟为单位分组的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个包含三列的sql表:日期,时间和值.值随时可用,同一分钟内有很多值.我只需要获取每分钟的值,这些值应该是同一分钟内所有值的平均值.
如果表中的数据为:
I have a sql table having three columns: date, time, and value. Values are in any time and there are many values during the same minute. I would need to get out just values for each minute, and those should be an average of all values during the same minute.
If the data in the table is:
myDate myTime myValue
2012.01.12 10:51:33 100
2012.01.12 10:51:45 200
2012.01.12 10:52:28 300
2012.01.12 10:52:41 200
2012.01.12 10:52:58 100
输出应该是这样的:
the output should be something like:
myDate myTime myValue
2012.01.12 10:51:00 150
2012.01.12 10:52:00 200
任何建议使用哪种sql都会导致这种情况?
感谢您的帮助.
Any proposals of what kind of sql would result in this?
Thanks for any help.
推荐答案
if object_id('tempdb..#temp') is not null
begin
drop table #temp
end
create table #temp
(
myDate varchar(10),
myTime varchar(8),
myValue int
)
insert into #temp values('2012.01.12', '10:51:33', 100)
insert into #temp values('2012.01.12', '10:51:45', 200)
insert into #temp values('2012.01.12', '10:52:28', 300)
insert into #temp values('2012.01.12', '10:52:41', 200)
insert into #temp values('2012.01.12', '10:52:58', 100)
select myDate,
myTime,
avg(myValue) as myValue
from (
select myDate,
left(myTime,5)+':00' as myTime,
myValue
from #temp
) a
group
by myDate,
myTime
DECLARE @timedata as table (mydate datetime , mytime datetime , myvalues bigint)
DECLARE @timedata_Formated as table (mydate datetime , mytime NVARCHAR(255) , myvalues bigint)
insert into @timedata select '2012.01.12','10:51:33.000',100
insert into @timedata select '2012.01.12','10:51:45.000',200
insert into @timedata select '2012.01.12','10:52:28.000',300
insert into @timedata select '2012.01.12','10:52:41.000',200
insert into @timedata select '2012.01.12','10:52:58.000',100
INSERT INTO @timedata_Formated
SELECT mydate,CAST(DATEPART(hh,mytime) AS NVARCHAR(50)) + ':' + CAST(DATEPART(mi,mytime) AS NVARCHAR(50)) + ':00',myvalues FROM @timedata
SELECT mydate,mytime,AVG(myvalues) FROM @timedata_Formated Group by mydate,mytime
select myDate, left(myTime,2) + ':' + substring(myTime, 4,2) + ':00' , sum(cast(right(myTime,2) as int)), avg(myValue) from table_data group by myDate, left(myTime,2), substring(myTime, 4,2)
这篇关于获取以秒为单位的平均值,以分钟为单位分组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!