CODEWITHSUNDEEP
sqldatabasesqlitedate
StackUser
StackUser

Reputation: 145

Sqlite moving average every minute

I have a sqlite3 database table with columns: date, points, age. Example of a row of data: 2019-09-26 12:30:22 | 40 | 44

When doing a SELECT query, how can I return a moving average of all data points within the same minute, rather than returning every single data point?

I've tried SELECT date, AVG(points), AVG(age) FROM table GROUP BY strftime(date, %m); but the query only returns one entry with all the values averaged. I want to return one entry per minute, where the returned date can be either the first date found, or an average of them all.

Here are some example dates from entries in my database:

2019-09-26 12:36:22
2019-09-26 12:36:40
2019-09-26 13:14:31
2019-09-26 13:14:46
2019-09-26 13:14:59
2019-09-28 13:19:40
2019-09-28 13:20:19

In this case I would like to have 4 rows returned (one for 12:36, one for 13:14, one for 13:19 and one for 13:20 averages)

Upvotes: 0

Views: 442

Answers (1)

GMB
GMB

Reputation: 222632

You want a query that aggregates by minute. Consider:

select
    strftime('%Y-%m-%d %H:%M', date),
    avg(age) avg_age,
    avg(points) avg_points
from mytable
group by strftime('%Y-%m-%d %H:%M', date)

Expression strftime('%Y-%m-%d %H:%M', date) truncates the seconds from the date: then you can apply aggregate functions to records in each group.

Upvotes: 2

Related Questions