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
>    I have a table (mytable) with several numerical fields.  Let us
>them F1, F2, etc., etc.  The table also has a row number field
>that I make sure has incremental values from 1 through the number of
>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
>these fields.  There are two averages:  one that uses only the count
>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
>as below to calculate and print those values:
>select RowNumber,
>(select sum(F1) from mytable where mytable.RowNumber <= T.RowNumber)
>(select avg(F1) from mytable where mytable.RowNumber <= T.RowNumber)
>avgF1, --average excluding null values
>(select sum(F1) from mytable where mytable.RowNumber <=
>T.RowNumber)/T.RowNumber as OverallAvgF1, --average including null
>(select sum(F2) from mytable where mytable.RowNumber <= T.RowNumber)
>(select avg(F2) from mytable where mytable.RowNumber <= T.RowNumber)
>(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
>for.  When I select F1 through F10 alone, the query runs in
>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
>    Is there anything I can do to reduce the time taken?  Is this the
>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
>but a view.  F1 through F10 are computed from various tables and
>together in this view.  So, I don't think I can create indexes on any
>these fields.  I have thought about creating a temp table out of this
>so that I can index RowNumber (I don't know whether it will help) but
>goes against my minimalist instincts.
>    Thank you.
>Balaji Ramanathan
>sqlite-users mailing list

sqlite-users mailing list

Reply via email to