4

i have an table in sqlite using pysqlite:

create table t
(
    id integer primary key not null,
    time datetime not null,
    price decimal(5,2)
)

how can i from this data calculate moving average with window X seconds large with an sql statement?

microo8
  • 3,568
  • 5
  • 37
  • 67

2 Answers2

3

As far as I understand your question, You do not want the average over the last N items, but over the last x seconds, am I correct?

Well, this gives you the list of all prices recorded the last 720 seconds:

>>> cur.execute("SELECT price FROM t WHERE datetime(time) > datetime('now','-720 seconds')").fetchall()

of course, you can feed that to the AVG-SQL-Function, to get the average price in that window:

>>> cur.execute("SELECT AVG(price) FROM t WHERE datetime(time) > datetime('now','-720 seconds')").fetchall()

You can also use other time units, and even chain them. For example, to obtain the average price for the last one and a half hour, do:

>>> cur.execute("SELECT AVG(price) FROM t WHERE datetime(time) > datetime('now','-30 minutes','-1 hour')").fetchall()

Edit: SQLite datetime reference can be found here

ch3ka
  • 11,792
  • 4
  • 31
  • 28
1

The moving average with a window x units large is given at time i by:

(x[i] + x[i+1] + ... + x[i+x-1]) / x

To compute it, you want to make a LIFO stack (which you can implement as a queue) of size x, and compute its sum. You can then update the sum by adding the new value and subtracting the old one from the old sum; you get the new one from the database and the old one by popping the first element off the stack.

Does that make sense?

Katriel
  • 120,462
  • 19
  • 136
  • 170
  • 1
    Hmm, ok but I wanted an sql select that makes it. – microo8 Apr 17 '12 at 10:13
  • @microo8 There isn't one. (At least to the best of my knowledge.) The above is pretty straightforward, no? – Katriel Apr 17 '12 at 11:46
  • You could probably do it in an SQL statement if you were desperate, but it's really not something SQL was designed to do. Retrieve the data and then perform the mathematical operations in your favourite programming language. – Li-aung Yip Apr 17 '12 at 13:31
  • 1
    Yep, I think you could hack something together. The efficient way to do this only needs _one_ pass through the data, so you'd need to write that within SQL which is silly. Just read the data out and do it there – Katriel Apr 17 '12 at 14:16
  • 1
    You can compute the average for a single window as ch3ka proposed. But, as katrielalex and Li-aung Yip said, computing the sliding average over the whole table is too complex for a single SQL statement. I'd try with a queue. – Igor F. May 24 '12 at 11:42