average in different timeinterval

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

average in different timeinterval

Author

Topic

voyager838
Yak Posting Veteran

90 Posts

Posted - 2008-10-04 : 18:55:03

Hi
I have 100k data, measured in 2 minutes interval in my database, and want to take an average on every 10 minutes on that.

like this

Minutes | Value |
2 | 4
4 | 2
6 | 1
8 | 1
10 | 2
(take and average of these values between time 2 and 10)

12 | 1
14 | 3
16 | 1
18 | 10
20 | 10
(take and average of these values between 12 and 20)
...

So the result got

Time | Avg
10 | 2
20 | 5

...

How could you do that?

Most gratefuly

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-05 : 01:39:51

try this out

SELECT (((Minutes-1)/10)+1) * 10,AVG(Value*1.0)
FROM YourTable
GROUP BY ((Minutes-1)/10)

voyager838
Yak Posting Veteran

90 Posts

Posted - 2008-10-05 : 03:40:27

Thanks for answer.

Unfortunay i wasnt clear in my description, sry.
My fault.

But i realize that i still have datetimestamp like

DateTime | Value
2008-01-01 00:00:00 | 2
2008-01-01 00:02:00 | 1
2008-01-01 00:04:00 | 3
2008-01-01 00:06:00 | 1

...

So how could i use the minutes, or do the same thing
with this type?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-05 : 03:47:37

SELECT (((DATEPART(mi,DateTime)-1)/10)+1) * 10,AVG(Value*1.0)
FROM YourTable
GROUP BY ((DATEPART(mi,DateTime)-1)/10)

voyager838
Yak Posting Veteran

90 Posts

Posted - 2008-10-06 : 03:27:25

Thank you sir!

It seems that it didnt get right, some how.
An average from the 5 first values didnt give the
same answer as the formula you suggest there.

very strange

I dont know how that really calculating or work.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-06 : 03:59:06

not sure what you mean by not working. see the solution below. i've extended it to work for multiple days also

declare @test table
(
date DateTime ,
Val int
)
insert into @test
select '2008-01-01 00:00:00' , 2 union all
select '2008-01-01 00:02:00' , 1 union all
select '2008-01-01 00:04:00', 3 union all
select '2008-01-01 00:06:00' , 1 union all
select '2008-01-01 00:08:00' , 2 union all
select '2008-01-01 00:10:00' , 1 union all
select '2008-01-01 00:14:00', 3 union all
select '2008-01-01 00:16:00' , 1 union all
select '2008-01-01 00:18:00' , 2 union all
select '2008-01-01 00:20:00' , 1 union all
select '2008-01-01 00:24:00', 3 union all
select '2008-01-01 00:26:00' , 1 union all
select '2008-01-01 00:30:00' , 2 union all
select '2008-01-01 00:32:00' , 1 union all
select '2008-01-01 00:34:00', 3 union all
select '2008-01-01 00:36:00' , 1 union all
select '2008-01-01 00:40:00' , 1 union all
select '2008-01-02 00:00:00' , 2 union all
select '2008-01-02 00:02:00' , 1 union all
select '2008-01-02 00:04:00', 3 union all
select '2008-01-02 00:06:00' , 1 union all
select '2008-01-02 00:08:00' , 2 union all
select '2008-01-02 00:10:00' , 1 union all
select '2008-01-02 00:14:00', 3 union all
select '2008-01-02 00:16:00' , 1 union all
select '2008-01-02 00:18:00' , 2 union all
select '2008-01-03 00:20:00' , 1 union all
select '2008-01-03 00:24:00', 3 union all
select '2008-01-03 00:26:00' , 1 union all
select '2008-01-03 00:30:00' , 2 union all
select '2008-01-03 00:32:00' , 1 union all
select '2008-01-03 00:34:00', 3 union all
select '2008-01-03 00:36:00' , 1 union all
select '2008-01-03 00:40:00' , 1 



select dateadd(dd,datediff(dd,0,date),0),(((datepart(mi,date)-1)/10)+1) * 10,avg(val*1.0)
from @test
group by dateadd(dd,datediff(dd,0,date),0),(datepart(mi,date)-1)/10
order by dateadd(dd,datediff(dd,0,date),0),(datepart(mi,date)-1)/10


output
----------------------------------------------------
date                    minuteinterval valueavg
----------------------- -------------- ---------------------------------------
2008-01-01 00:00:00.000 10             1.666666
2008-01-01 00:00:00.000 20             1.750000
2008-01-01 00:00:00.000 30             2.000000
2008-01-01 00:00:00.000 40             1.500000
2008-01-02 00:00:00.000 10             1.666666
2008-01-02 00:00:00.000 20             2.000000
2008-01-03 00:00:00.000 20             1.000000
2008-01-03 00:00:00.000 30             2.000000
2008-01-03 00:00:00.000 40             1.500000

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-06 : 04:19:54

Using the same sample data as Visakh16, I get following result

(No column name)	Average
----------------	-------
2008-01-01 00:00	2.00
2008-01-01 00:10	1.60
2008-01-01 00:20	1.75
2008-01-01 00:30	2.00
2008-01-01 00:40	1.50
2008-01-02 00:00	2.00
2008-01-02 00:10	1.60
2008-01-02 00:20	2.00
2008-01-03 00:20	1.00
2008-01-03 00:30	2.00
2008-01-03 00:40	1.50

with this small piece of code

SELECT		DATEADD(MINUTE, DATEDIFF(MINUTE, '19000101 00:02', [Date]) / 10 * 10, '19000101 00:10'),
		AVG(1.0E0 * Val) AS Average
FROM		@Sample
GROUP BY	DATEADD(MINUTE, DATEDIFF(MINUTE, '19000101 00:02', [Date]) / 10 * 10, '19000101 00:10')

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-06 : 04:30:40

quote:

Originally posted by Peso

Using the same sample data as Visakh16, I get following result

(No column name)	Average
----------------	-------
2008-01-01 00:00	2.00
2008-01-01 00:10	1.60
2008-01-01 00:20	1.75
2008-01-01 00:30	2.00
2008-01-01 00:40	1.50
2008-01-02 00:00	2.00
2008-01-02 00:10	1.60
2008-01-02 00:20	2.00
2008-01-03 00:20	1.00
2008-01-03 00:30	2.00
2008-01-03 00:40	1.50

with this small piece of code

SELECT		DATEADD(MINUTE, DATEDIFF(MINUTE, '19000101 00:02', [Date]) / 10 * 10, '19000101 00:10'),
		AVG(1.0E0 * Val) AS Average
FROM		@Sample
GROUP BY	DATEADD(MINUTE, DATEDIFF(MINUTE, '19000101 00:02', [Date]) / 10 * 10, '19000101 00:10')

E 12°55'05.63"
N 56°04'39.26"

the difference b/w mine and your logic is that i've included the values from 0-10 mts to 10, 11-20 mts to 20 and so on while your logic takes intervals as 0-9 to 0,10-19 to 10 and so on. I used the logic seeing sample data OP posted where he has shown including values for 2,4,6,.. minutes to group for 10 min. I'm not sure what he's exactly looking for though.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-06 : 04:35:45

No. I take minutes 2-10 as 10, 12-20 as 20. Just as OP wanted.

-- First group			Sum	Count	Average		Group As
'2008-01-01 00:00:00', 2	2	1	2 / 1 = 2.00	'2008-01-01 00:00:00'

-- Second group
'2008-01-01 00:02:00', 1	8	5	8 / 5 = 1.60	'2008-01-01 00:10:00'
'2008-01-01 00:04:00', 3
'2008-01-01 00:06:00', 1
'2008-01-01 00:08:00', 2
'2008-01-01 00:10:00', 1

-- Third group
'2008-01-01 00:14:00', 3	7	4	7 / 4 = 1.75	'2008-01-01 00:20:00'
'2008-01-01 00:16:00', 1
'2008-01-01 00:18:00', 2
'2008-01-01 00:20:00', 1

-- Fourth group
'2008-01-01 00:24:00', 3	6	3	6 / 3 =	2.00	'2008-01-01 00:30:00'
'2008-01-01 00:26:00', 1
'2008-01-01 00:30:00', 2

-- Fifth group
'2008-01-01 00:32:00', 1	6	4	6 / 4 = 1.50	'2008-01-01 00:40:00'
'2008-01-01 00:34:00', 3
'2008-01-01 00:36:00', 1
'2008-01-01 00:40:00', 1

-- Sixth group
'2008-01-02 00:00:00', 2	2	1	2 / 1 = 2.00	'2008-01-02 00:00:00'

-- Seventh group
'2008-01-02 00:02:00', 1	8	5	8 / 5 = 1.60	'2008-01-02 00:10:00'
'2008-01-02 00:04:00', 3
'2008-01-02 00:06:00', 1
'2008-01-02 00:08:00', 2
'2008-01-02 00:10:00', 1

-- Eight group
'2008-01-02 00:14:00', 3	6	3	6 / 3 = 2.00	'2008-01-02 00:20:00'
'2008-01-02 00:16:00', 1
'2008-01-02 00:18:00', 2

-- Ninth group
'2008-01-03 00:20:00', 1	1	1	1 / 1 = 1.00	'2008-01-03 00:20:00'

-- Tenth group
'2008-01-03 00:24:00', 3	6	3	6 / 3 = 2.00	'2008-01-03 00:30:00'
'2008-01-03 00:26:00', 1
'2008-01-03 00:30:00', 2

-- Eleventh group
'2008-01-03 00:32:00', 1	6	4	6 / 4 = 1.50	'2008-01-03 00:40:00'
'2008-01-03 00:34:00', 3
'2008-01-03 00:36:00', 1
'2008-01-03 00:40:00', 1

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-06 : 04:47:06

yup i understood the difference. i was including 0 min values also to 10 min group.

voyager838
Yak Posting Veteran

90 Posts

Posted - 2008-10-06 : 04:50:30

Now i got another result, but before that
something with the suggest query was giving something else.

Look below you see what i mean

declare @test table
(
date DateTime ,
Val int
)
insert into @test
select '2008-01-01 00:00:00' , 1 union all
select '2008-01-01 00:02:00' , 2 union all
select '2008-01-01 00:04:00', 3 union all
select '2008-01-01 00:06:00' , 4 union all
select '2008-01-01 00:08:00' , 5 union all
select '2008-01-01 00:10:00' , 6 union all
select '2008-01-01 00:12:00' , 7 union all
select '2008-01-01 00:14:00', 8 union all
select '2008-01-01 00:16:00' , 9 union all
select '2008-01-01 00:18:00' , 10 union all
select '2008-01-01 00:20:00' , 11 union all
select '2008-01-01 00:24:00', 12 union all
select '2008-01-01 00:26:00' , 13 union all
select '2008-01-01 00:30:00' , 14 union all
select '2008-01-01 00:32:00' , 15 union all
select '2008-01-01 00:34:00', 16 union all
select '2008-01-01 00:36:00' , 17 union all
select '2008-01-01 00:40:00' , 18 union all
select '2008-01-02 00:00:00' , 19 union all
select '2008-01-02 00:02:00' , 20 union all
select '2008-01-02 00:04:00', 21 union all
select '2008-01-02 00:06:00' , 22 union all
select '2008-01-02 00:08:00' , 23 union all
select '2008-01-02 00:10:00' , 24 union all
select '2008-01-02 00:14:00', 25 union all
select '2008-01-02 00:18:00' , 26

select dateadd(dd,datediff(dd,0,date),0),(((datepart(mi,date)-1)/10)+1) * 10,avg(val*1.0)
from @test
group by dateadd(dd,datediff(dd,0,date),0),(datepart(mi,date)-1)/10
order by dateadd(dd,datediff(dd,0,date),0),(datepart(mi,date)-1)/10

We got the result:

(No column name) (No column name) (No column name)
2008-01-01 00:00:00.000 10 3.500000
2008-01-01 00:00:00.000 20 9.000000
2008-01-01 00:00:00.000 30 13.000000
2008-01-01 00:00:00.000 40 16.500000
2008-01-02 00:00:00.000 10 21.500000
2008-01-02 00:00:00.000 20 25.500000

This seems right, but that was the latest answer you gave.

Before that if we use instead

SELECT (((DATEPART(mi,[Date])-1)/10)+1) * 10,AVG(Val*1.0)
FROM @test
GROUP BY ((DATEPART(mi,[Date])-1)/10)

We got the result

(No column name) (No column name)
10 12.500000
20 13.714285
30 13.000000
40 16.500000

Which give another result. That was the strange part i didnt understand how it could be like that.

The only part that is still left now and is also something i need to solved is the right datetime stamp

now we got

2008-01-01 00:00:00.000 10 3.500000
2008-01-01 00:00:00.000 20 9.000000
2008-01-01 00:00:00.000 30 13.000000
2008-01-01 00:00:00.000 40 16.500000
2008-01-02 00:00:00.000 10 21.500000
2008-01-02 00:00:00.000 20 25.500000

we can see that the datetime and minutes are not the same,
i should atleast be written

2008-01-01 00:00:00.000 10 3.500000
2008-01-01 00:20:00.000 20 9.000000
2008-01-01 00:30:00.000 30 13.000000
2008-01-01 00:40:00.000 40 16.500000
2008-01-02 01:10:00.000 10 21.500000
2008-01-02 01:20:00.000 20 25.500000

or somehting

Thanks anyway

voyager838
Yak Posting Veteran

90 Posts

Posted - 2008-10-06 : 05:00:22

Ah, you where fast with answers :)

Thanks [peso] and [visakh16]
That has help me solved this problem now.

Have a god day (or night)
/V

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-06 : 05:08:15

[code]select dateadd(mi,mininterval,date) as dateval,mininterval,avgval
from
(
select dateadd(dd,datediff(dd,0,date),0) as date,(((datepart(mi,date)-1)/10)+1) * 10 as mininterval,avg(val*1.0) as avgval
from @test
group by dateadd(dd,datediff(dd,0,date),0),(datepart(mi,date)-1)/10
)t
order by date,mininterval[/code]

voyager838
Yak Posting Veteran

90 Posts

Posted - 2008-10-06 : 05:32:05

You almost solved it, its still missing two values

from

2008-01-01 00:00:00.000
2008-01-02 00:00:00.000

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-06 : 05:39:49

quote:
Originally posted by voyager838

You almost solved it, its still missing two values

from

2008-01-01 00:00:00.000
2008-01-02 00:00:00.000

oh so you want 0 min grouping also. then use Peso's solution.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-06 : 06:57:00

[code]SELECT DATEADD(MINUTE, DATEDIFF(MINUTE, '19000101 00:02', [Date]) / 10 * 10, '19000101 00:10'),
AVG(1.0E0 * Val) AS Average
FROM @Sample
GROUP BY DATEADD(MINUTE, DATEDIFF(MINUTE, '19000101 00:02', [Date]) / 10 * 10, '19000101 00:10')[/code]

E 12°55'05.63"
N 56°04'39.26"

voyager838
Yak Posting Veteran

90 Posts

Posted - 2008-12-13 : 09:26:18

Thats work fine , but how do i take control of it?
I mean, im little confused how it really works.
What should i change if we want to, lets say take the average on every hours for instance?

How could i use this nice formula so it can be reused on differents intervals?

SELECT DATEADD(MINUTE, DATEDIFF(MINUTE, '19000101 00:02', [Date]) / 10 * 10, '19000101 00:10'),
AVG(1.0E0 * Val) AS Average
FROM @Sample
GROUP BY DATEADD(MINUTE, DATEDIFF(MINUTE, '19000101 00:02', [Date]) / 10 * 10, '19000101 00:10')

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-12-13 : 11:55:12

First you have to define what is an interval.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources