Re: sqlite-users Digest, Vol 119, Issue 14

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Re: sqlite-users Digest, Vol 119, Issue 14

Balaji Ramanathan
Very interesting.  Thank you David.  I have never used a recursive CTE
before, so I am still trying to understand exactly how it works.  But it
does work, so I will definitely take a closer look.  Thank you.

Balaji Ramanathan

On Tue, Nov 14, 2017 at 6:00 AM, <
[hidden email]> wrote:

> ---------- Forwarded message ----------
> From: David Raymond <[hidden email]>
> To: SQLite mailing list <[hidden email]>
> Cc:
> Bcc:
> Date: Mon, 13 Nov 2017 17:20:58 +0000
> Subject: Re: [sqlite] Running sums and averages
> As other folks have mentioned, doing it in an external language is going
> to be easiest.
>
> 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 like...
>
> with recursive foo (RowNumber, F1, sumF1, OverallAvgF1, avgF1, F1notNull)
> as(
>   select
>   rowNumber,
>   F1,
>   ifnull(F1, 0.0),
>   ifnull(F1, 0.0),
>   ifnull(F1, 0.0),
>   F1 is not null
>   from mytable where RowNumber = 1
>
>   union all
>
>   select
>   mytable.RowNumber,
>   mytable.F1,
>   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 null)),
>   foo.F1notNull + (mytable.F1 is not null)
>
>   from
>
>   foo inner join mytable on mytable.RowNumber = foo.RowNumber + 1)
>
> select RowNumber, F1, sumF1, OverallAvgF1, avgF1 from foo;
>
>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users