获取以秒为单位的平均值，以分钟为单位分组 [英] Get an average of values in seconds, grouped as values in minutes

问题描述

我有一个包含三列的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.

推荐答案

尝试一下

Try this

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

尝试一下

Try this

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

这可能不是清洁工&最佳方法，但您可以通过以下查询来实现:

this may not be the cleaner & optimum way but you can acheive this by following query:

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) 

这篇关于获取以秒为单位的平均值，以分钟为单位分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！