Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Suppose I have following table -

CREATE TABLE data_points (t DATETIME PRIMARY KEY, value INTEGER);

I want to aggregate the data by calculating average of every 10 points in the table.

i.e. If table has 20 data points the result is two aggregate points. 1st aggregate point the average of 1-10 data points, and 2nd of 11-20.

Is this possible using a SQL query?

share|improve this question
    
What RDBMS do you use? The specifics depend on it. And what should be the ordering? –  dezso Dec 15 '12 at 9:50
    
@dezso My DB is H2 but you can post a solution in Mysql or other RDBMS. I'll understand the query and try to port it. As of yet I have no idea how to approach the problem. The ordering is done by datetime. –  Kshitiz Sharma Dec 15 '12 at 10:17

1 Answer 1

Since you have not disclosed the RDBMS you intend to use (by the time of writing), I feel authorized to post a PostgreSQL-specific solution:

WITH rownums AS (
    SELECT *, row_number() OVER (ORDER BY col1) AS rownum
    FROM avg_test
), 
averages AS (
    SELECT avg(col2) OVER (ORDER BY rownum ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) AS average, rownum
    FROM rownums
)
SELECT average
FROM averages
WHERE rownum % 10 = 0
;

In the first CTE (called rownums) I simply added row numbers according to ordering by the timestamp. This was necessary for being able to find every 10th row later. (If there were an additional unique key on this table, this yould be unnecessary.) In the averages CTE I compute a moving average on every row. The window is set so that the current row and nine preceding give the numbers to be averaged. Lastly, I just simply return every 10th row.

Both row_number andavg` are window functions here. MySQL lacks this functionality, and, as far as I know, H2 too. So porting the abov query to these will involve a lot more trickery, I think.

Try it at SQLFiddle.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.