OK, thanks for the short and sweet answer! I just wanted to make sure I wasn't missing something.
Balaji Ramanathan > ---------- Forwarded message ---------- > From: Simon Slavin <slav...@bigfraud.org> > To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> > Cc: > Bcc: > Date: Sun, 12 Nov 2017 16:37:24 +0000 > Subject: Re: [sqlite] Running sums and averages > > > On 12 Nov 2017, at 4:05pm, Balaji Ramanathan <balaji.ramanat...@gmail.com> > wrote: > > > Is there anything I can do to reduce the time taken? > > Do it in your favourite programming language rather than SQL. > Congratulations on finding a way of doing it in SQL, but running sums and > averages are trivial in a programming language and will be far faster and > you can be as fussy as you like about whether nulls are counted and such > things. > > Simon. > > > ---------- Forwarded message ---------- > From: "Joseph R. Justice" <jayare...@gmail.com> > > I am *FAR* from a SQLite (or SQL) guru, but it seems obvious to me that > your calculations for sumF1, avgF1, OverallAvgF1, etc as written are > running in quadratic time based on the number of rows in table T. Worse > yet, you're recalculating all these values entirely from scratch every time > you're recalculating them, rather than use the previously calculated value. > > Is there any way you can calculate table T with just RowNumber, F1, F2, > ..., F10, and then as you subsequently step through T row by row calculate > all the sums and averages as you step, saving the calculations for each row > as you calculate them in temporary variables for use in calculating the > values for the next row? This might be doable in pure SQL code, or you > might have to do it in whatever language you're making calls to SQLite > from. That should be linear time, at worst 2 times the number of rows in T > (once to construct T, once to step through it). > > Be well. > > > > Joseph > > > > ---------- Forwarded message ---------- > From: Dennis Clarke <dcla...@blastwave.org> > > < Simon correctly advised > > > Do it in your favourite programming language rather than SQL. > > Let me be even more clear : > > Memory is cheap and most servers have plenty. > > Processors are fast and most servers have multiple with many cores. > > Select the entire table of columns you need into memory. > Write a little code. > > No it won't scale very well into millions of rows but I could easily run > a test and I will bet many mnay many dollars that processing the sums in > memory is orders of magnitude faster than SQL. > > > Dennis > > ps: if your db is MySQL or Oracle db then the problem is trivial with > the C API > > > > ---------- Forwarded message ---------- > From: Keith Medcalf <kmedc...@dessus.com> > Subject: Re: [sqlite] Running sums and averages > > EXPLAIN QUERY PLAN > > is the first step. > > ---------- Forwarded message ---------- > From: Graham Holden <sql...@aldurslair.com> > > You shouldn't even need to read the entire table (or view) into > memory: just read row-by-row, and for each field, keep a running > total and the count of non-NULL values. From these you can calculate > your total and both types of average. > > Graham > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
