EXPLAIN QUERY PLAN 

is the first step.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Balaji Ramanathan
>Sent: Sunday, 12 November, 2017 09:06
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] Running sums and averages
>
>Hi,
>
>    I have a table (mytable) with several numerical fields.  Let us
>call
>them F1, F2, etc., etc.  The table also has a row number field
>(RowNumber)
>that I make sure has incremental values from 1 through the number of
>rows
>in the table.
>
>    In addition to printing out the values of F1 through Fn, I also
>want to
>print out, for each row of the table, the running sums and averages
>of
>these fields.  There are two averages:  one that uses only the count
>of
>non-null values of each field as the denominator and another that
>uses the
>count of both null and non-null values of each field.  So, I wrote a
>query
>as below to calculate and print those values:
>
>select RowNumber,
>F1,
>(select sum(F1) from mytable where mytable.RowNumber <= T.RowNumber)
>as
>sumF1,
>(select avg(F1) from mytable where mytable.RowNumber <= T.RowNumber)
>as
>avgF1, --average excluding null values
>(select sum(F1) from mytable where mytable.RowNumber <=
>T.RowNumber)/T.RowNumber as OverallAvgF1, --average including null
>values
>F2,
>(select sum(F2) from mytable where mytable.RowNumber <= T.RowNumber)
>as
>sumF2,
>(select avg(F2) from mytable where mytable.RowNumber <= T.RowNumber)
>as
>avgF2,
>(select sum(F2) from mytable where mytable.RowNumber <=
>T.RowNumber)/T.RowNumber as OverallAvgF2,
>--Other fields, their running sums and averages go here
>from mytable as T order by RowNumber
>
>    I have about 3000 rows in my table and about 10 fields I am doing
>this
>for.  When I select F1 through F10 alone, the query runs in
>negligible
>time.  But when I run the above query with running sums and averages
>included (total of 40 columns instead of 10 columns), the time
>increases to
>well over 5 minutes.
>
>    When I run the query at the SQLite command line, the first 1000
>or so
>records are produced very fast, but as the row number increases, I
>can see
>SQLite struggling to produce outputs.  By the time I get to the end
>of the
>query, the records are being produced at about a rate of only 1 or 2
>per
>second.
>
>    Is there anything I can do to reduce the time taken?  Is this the
>most
>efficient way to query for running sums and averages?  Any other
>ideas to
>get what I need any quicker?
>
>    The added wrinkle in all this is that mytable is actually not a
>table,
>but a view.  F1 through F10 are computed from various tables and
>brought
>together in this view.  So, I don't think I can create indexes on any
>of
>these fields.  I have thought about creating a temp table out of this
>view
>so that I can index RowNumber (I don't know whether it will help) but
>it
>goes against my minimalist instincts.
>
>    Thank you.
>
>Balaji Ramanathan
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Reply via email to