0

I'm trying to write a query that returns a moving average from a large table (>1M rows).

The table has a column with a date, and and an other column with a numeric value. I need the last 10 days, with the 10 day moving average for every date.

Whatever I tried turned out to be painfully slow (and run only with infinidb_vtable_mode = 0 or 2).

Is there a proper "infinidb way" to do fast moving average (or similar window-function) queries?

Thank you.

netom
  • 3,322
  • 2
  • 21
  • 21

1 Answers1

0

In InfiniDB 4.0, Windowing functions are supported, using the AVERAGE as windowing function, you can get moving average over last 10 days with following query

SELECT date_column, 
      AVERAGE(numeric_column) OVER (PARTITION BY date_column RANGE INTERVAL 10 DAY PRECEDING) 
FROM table_name

Let me know what results you get

Logan Wayne
  • 6,001
  • 16
  • 31
  • 49
Dipti Joshi
  • 1
  • 1
  • I got this error: "Error Code: 122. IDB-9002: Missing ORDER BY expression in the window specification." This query "SELECT VERSION()" give me 5.1.39 value – kirugan May 22 '14 at 08:43
  • SELECT VERSION() gives you MySQL version - since InfiniDB is MySQL storage engine. – Dipti Joshi Jul 10 '14 at 14:36