[MonetDB-users] Get average value for intervals
Hello, I've some time series data (time + a few value columns), now I want to generate average values for each hour. Currently I'm using the following query: SELECT (time - (time % 3600)) AS timegroup, AVG(value0) AS avgvalue0 FROM dbbench WHERE time > x AND time <= y GROUP BY timegroup; I've a few GB of data and the query is running for several seconds. Does anyone have any recommendations on how to improve the query for faster execution time? Greetings Gustav
Hi Gustav, it would be great if you could provide us with the detailed schema of your table (e.g., your complete CREATE TABLE statement) --- or at least the columns in voved in this query, and (if known) some basic properties of the data (e.g., number of tuples and whether attribute values are sorted or unique). Also, you could profile the query by prefixing it with TRACE and send us the result for analysis. Finally, whih version of MonetDB are you using and on what kind of platform (OS & HW) --- the output of `mserver5 --version` should answer these quetions. Thanks, Stefan On Fri, Jun 18, 2010 at 10:56:21AM +0200, Gustav Koller wrote:
Hello,
I've some time series data (time + a few value columns), now I want to generate average values for each hour.
Currently I'm using the following query:
SELECT (time - (time % 3600)) AS timegroup, AVG(value0) AS avgvalue0 FROM dbbench WHERE time > x AND time <= y GROUP BY timegroup;
I've a few GB of data and the query is running for several seconds. Does anyone have any recommendations on how to improve the query for faster execution time?
Greetings Gustav
------------------------------------------------------------------------------ ThinkGeek and WIRED's GeekDad team up for the Ultimate GeekDad Father's Day Giveaway. ONE MASSIVE PRIZE to the lucky parental unit. See the prize list and enter to win: http://p.sf.net/sfu/thinkgeek-promo _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4199 |
On 6/18/10, Stefan Manegold
it would be great if you could provide us with the detailed schema of your table (e.g., your complete CREATE TABLE statement) --- or at least the columns in voved in this query, and (if known) some basic properties of the data (e.g., number of tuples and whether attribute values are sorted or unique).
Current test table: CREATE TABLE test ( source INT, time BIGINT, value0 DOUBLE, value1 DOUBLE, value2 DOUBLE, value3 DOUBLE, value4 DOUBLE, value5 DOUBLE, value6 DOUBLE, value7 DOUBLE, value8 DOUBLE, value9 DOUBLE, CONSTRAINT pkey PRIMARY KEY (source, time) ); One unique tuple for each source/time combination. Currently testing with 30000 sources.
Also, you could profile the query by prefixing it with TRACE and send us the result for analysis.
http://pastebin.com/raw.php?i=6vSze9j7 The trace for the following query: TRACE SELECT (time - (time % 300)) AS timegroup, AVG(value0) AS value0 FROM dbbench WHERE source = 3000 GROUP BY timegroup;
Finally, whih version of MonetDB are you using and on what kind of platform (OS & HW) --- the output of `mserver5 --version` should answer these quetions.
MonetDB server v5.18.5 (64-bit), based on kernel v1.36.5 (64-bit oids) Copyright (c) 1993-July 2008 CWI Copyright (c) August 2008-2010 MonetDB B.V., all rights reserved Visit http://monetdb.cwi.nl/ for further information Found 2.9GiB available memory, 4 available cpu cores Configured for prefix: /usr Libraries: libpcre: 7.8 2008-09-05 (compiled with 7.8) openssl: OpenSSL 0.9.8g 19 Oct 2007 (compiled with OpenSSL 0.9.8g 19 Oct 2007) libxml2: 2.7.5 (compiled with 2.7.5) Compiled by: root@ubuntu-karmic-64 (x86_64-pc-linux-gnu) Compilation: gcc -O2 -Wall -O2 -O6 -fomit-frame-pointer -finline-functions -falign-loops=4 -falign-jumps=4 -falign-functions=4 -fexpensive-optimizations -funroll-loops -frerun-cse-after-loop -frerun-loop-opt -ftree-vectorize Linking : ld -IPA -m elf_x86_64 -Wl,-Bsymbolic-functions
participants (2)
-
Gustav Koller -
Stefan Manegold