I assume that the first query was executed successfully in a separate transaction. The second query builds a huge intermediate result incrementally.
To understand such queries you might try a toy example first (e.g. a table of 10 elements and look at the intermediate results)
Consider a1.seq = 18 then there are 40M-18 combinations for which a1.seq <a2.seq+17, this is stored in the intermediate... and then add a1.seq= 19 .... roughly leading to a table of 40M^2
You probably mean a2.seq <= a1.seq <a2.seq+17, which should be translated by the SQL front-end in a bandjoin(). I am almost sure it doesnot.
The SQL standard contains WINDOWS functions to handle this case. They are not supported yet.
A hack would be to generate 18 statements, which accumulate the result along the lines: insert into accum(first,last,total) select seq,seq,num from tableA; update accum set last=last +1 set total = total + (select num from tableA where seq= last+1); ...17 times... ...sort/group...
regards, Martin George H wrote:
Hello again kind people.
This is an sql self join on TableA which contains a serial primary key column called 'seq'. The select is limited to less than 400 rows of output by 'a1.seq<=400'.
sql->select a1.*, sum(a2.num)/17 from TableA a1, TableA a2 where a1.seq<=400 and a1.seq>=17 and a1.seq<a2.seq+17 group by a1.seq, a1.num order by a1.seq asc;
This select statement results in 384 rows, as expected, with a newly calculated column for sum(a2.num)/17 which yields a running average of 'num' between any given value for 'seq' and 'seq+17'.
This next sql statement is exactly the same as above except the limitation of 384 rows of output imposed by 'a1.seq<=400' has been removed. Note that TableA contained 40 million plus rows.
sql->select a1.*, sum(a2.num)/17 from TableA a1, TableA a2 where and a1.seq>=17 and a1.seq<a2.seq+17 group by a1.seq, a1.num order by a1.seq asc;
Error: ERROR: GDKload failed: name=14/1457, ext=buns.priv ERROR: CMDthetajoin: operation failed.
Perhaps there is a better way to accomplish a running aggregate on 40 million rows?
Thanks.........George
Take Surveys. Earn Cash. Influence the Future of IT Join SourceForge.net's Techsay panel and you'll get the chance to share your opinions on IT & business topics through brief surveys -- and earn cash http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=D... _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users