As other folks have mentioned, doing it in an external language is going to be 

That being said, CTE's are almost a full language in themselves.

I'm just gonna go with 1 value here for the example, but how about something 

with recursive foo (RowNumber, F1, sumF1, OverallAvgF1, avgF1, F1notNull)
  ifnull(F1, 0.0),
  ifnull(F1, 0.0),
  ifnull(F1, 0.0),
  F1 is not null
  from mytable where RowNumber = 1
  union all
  foo.sumF1 + ifnull(mytable.F1, 0.0),
  (foo.sumF1 + ifnull(mytable.F1, 0.0)) / mytable.RowNumber,
  (foo.sumF1 + ifnull(mytable.F1, 0.0)) / (foo.F1notNull + (mytable.F1 is not 
  foo.F1notNull + (mytable.F1 is not null)
  foo inner join mytable on mytable.RowNumber = foo.RowNumber + 1)

select RowNumber, F1, sumF1, OverallAvgF1, avgF1 from foo;

-----Original Message-----
From: sqlite-users [] On 
Behalf Of Balaji Ramanathan
Sent: Sunday, November 12, 2017 11:06 AM
Subject: [sqlite] Running sums and averages


    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,
(select sum(F1) from mytable where mytable.RowNumber <= T.RowNumber) as
(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
(select sum(F2) from mytable where mytable.RowNumber <= T.RowNumber) as
(select avg(F2) from mytable where mytable.RowNumber <= T.RowNumber) as
(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

    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 mailing list

Reply via email to