Help with debugging query (Rolling Average)

  • Thread starter Thread starter googlinggoogler
  • Start date Start date
G

googlinggoogler

Hi

I'm trying to do a rolling average on data that is ordered by a date
time column.

I'm after adivce about how best to do this, I've tried this -

SELECT Time, ValueX, (HOUR(Time) * 3600 + Minute(Time) * 60 +
Second(Time)) As TickTock
(SELECT Avg(ValueX)
FROM My_Table
WHERE TickTock Between (TickTock) AND (TickTock) + 30) AS
RollingAverage
FROM My_Table;

Basically my thinking was that it would be easier to work on this
datetime data as Seconds, So that my rolling average can be based on
10, at 3 second intervals

When I try this I get nothing in my RollingAverage box and also a pop
up asking for parameters... I'm very confused by this as ultimatly I
will be coding this into an application

Can anyone offer any pointers?

Thanks

David
 
SELECT Time, ValueX, (HOUR(Time) * 3600 + Minute(Time) * 60 +
Second(Time)) As TickTock
(SELECT Avg(ValueX)
FROM My_Table
WHERE TickTock Between (TickTock) AND (TickTock) + 30) AS
RollingAverage
FROM My_Table;
Click to expand...
Maybe try it like this:

SELECT a.[time],
a.valuex,
a.ticktock,
AVG(a.valuex) AS rollingaverage
FROM (SELECT a.[time],
a.valuex,
(HOUR(a.[time]) * 3600 + MINUTE(a.[time]) * 60 + SECOND(a.
[time])) AS ticktock
FROM my_table) AS a
WHERE a.ticktock BETWEEN a.ticktock
AND a.ticktock + 30;
 
SELECT Time, ValueX, (HOUR(Time) * 3600 + Minute(Time) * 60 +
Second(Time)) As TickTock
(SELECT Avg(ValueX)
FROM My_Table
WHERE TickTock Between (TickTock) AND (TickTock) + 30) AS
RollingAverage
FROM My_Table;
Click to expand...
Maybe try it like this:

SELECT a.[time],
a.valuex,
a.ticktock,
AVG(a.valuex) AS rollingaverage
FROM (SELECT a.[time],
a.valuex,
(HOUR(a.[time]) * 3600 + MINUTE(a.[time]) * 60 + SECOND(a.
[time])) AS ticktock
FROM my_table) AS a
WHERE a.ticktock BETWEEN a.ticktock
AND a.ticktock + 30;
Click to expand...
or this
SELECT a.[time],
a.valuex,
a.ticktock,
AVG(a.valuex) AS rollingaverage
FROM (SELECT my_table.[time],
my_table.valuex,
(HOUR(my_table.[time]) * 3600 + MINUTE(my_table.[time]) * 60
+ SECOND(my_table.[time])) AS ticktock
FROM my_table) AS a
WHERE a.ticktock BETWEEN a.ticktock
AND a.ticktock + 30;
 
I can't get either of your solutions to work -

I get the error "You tried to execute a query that does not include
the specified expression, "Time" as part of the aggregate function"

Any ideas?
 
I can't get either of your solutions to work -

I get the error "You tried to execute a query that does not include
the specified expression, "Time" as part of the aggregate function"

Any ideas?
Click to expand...
My thought is that I overlooked the Group By clause, and the non-aggregated
columns in the Select clause need to be in a Group By clause.
 
I can't get either of your solutions to work -

I get the error "You tried to execute a query that does not include
the specified expression, "Time" as part of the aggregate function"

Any ideas?
Click to expand...
OK, this is my final try:

?time()
6:14:56 PM
?datediff("s",0, #6:14:56 PM#)
65696
?(HOUR(#6:14:56 PM#) * 3600 + Minute(#6:14:56 PM#) * 60 +Second(#6:14:56
PM#))
65696

SELECT my_table.time,
my_table.valuex,
DATEDIFF("s",0,my_table.time) AS ticktock,
SUM(b.valuex) / COUNT(b.valuex) AS rollingaverage
FROM my_table,
my_table AS b
WHERE DATEDIFF("s",0,b.time) BETWEEN DATEDIFF("s",0,my_table.time)
AND DATEDIFF("s",0,my_table.time) +
30
GROUP BY my_table.TIME,my_table.valuex,DATEDIFF("s",0,my_table.time);

time valuex ticktock rollingaverage
6:12:45 PM 34.5 65565 34.7125
6:12:48 PM 34.6 65568 34.7833333333333333333333
6:12:54 PM 34.65 65574 34.875
6:12:57 PM 35.1 65577 35.1

SELECT my_table.TIME,
my_table.valuex,
DATEDIFF("s",0,my_table.TIME) AS ticktock,
(SELECT AVG(b.valuex)
FROM my_table AS b
WHERE DATEDIFF("s",0,b.TIME) BETWEEN DATEDIFF("s",0,my_table.TIME)
AND DATEDIFF
("s",0,my_table.TIME) + 30) AS rollingaverage
FROM my_table;

time ValueX ticktock RollingAverage
6:12:45 PM 34.5 65565 34.7125
6:12:48 PM 34.6 65568 34.7833333333
6:12:54 PM 34.65 65574 34.875
6:12:57 PM 35.1 65577 35.1
 
I can't get either of your solutions to work -

I get the error "You tried to execute a query that does not include
the specified expression, "Time" as part of the aggregate function"

Any ideas?
Click to expand...
Perhaps it is best to start a new thread so new people can jump in.


SELECT my_table.time,
my_table.valuex,
DATEDIFF("s",0,my_table.time) AS ticktock,
SUM(b.valuex) / COUNT(* ) AS runningaverage
FROM my_table
INNER JOIN my_table AS b
ON my_table.time >= b.time
GROUP BY my_table.time,my_table.valuex,DATEDIFF("s",0,my_table.time);
 
I can't get either of your solutions to work -

I get the error "You tried to execute a query that does not include
the specified expression, "Time" as part of the aggregate function"

Any ideas?
Click to expand...

SELECT my_table.time,
my_table.valuex,
DATEDIFF("s",0,my_table.time) AS ticktock,
AVG(b.valuex) AS rollingaverage
FROM my_table,
my_table AS b
WHERE my_table.time <= b.time
AND DATEDIFF("s",my_table.time,b.time) <= 30
GROUP BY my_table.TIME,my_table.valuex,DATEDIFF("s",0,my_table.time);
 
Back
Top