How to aggregate datapoints in a table?

Question

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?

András Váczi · Accepted Answer · 2017-12-20T13:50:36.670

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() and avg() 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.

How to aggregate datapoints in a table?

1 Answers1