Re: Capturing the changes in columns in a table

Keith Medcalf, on Tuesday, January 14, 2020 09:04 AM, wrote...
>
>
> Assuming (a, idate) is indexed and unique, then give the following a
> whirl on your larger data. It does the same thing but does not use window
> functions to find the prior value -- it does a correlated subquery instead.
> I would expect that it is slower with real data than the window function
> version because it pushes the conditional into each union leg so that it
> does two correlated subqueries (one for the select and one for the where),
> but it might be interesting to see if it is actually slower. The window
> function version generates a huge internal VDBE program, but all the "bits"
> are co-routines.
>
>   select a,
>          idate,
>          variable,
>          oldv,
>          newv
>     from (
>             select a,
>                    idate,
>                    'b' as variable,
>                    (select b
>                       from t
>                      where a == o.a
>                        and idate < o.idate
>                   order by idate desc
>                      limit 1) as oldv,
>                    b as newv
>               from t as o
>          union
>             select a,
>                    idate,
>                    'c' as variable,
>                    (select c
>                       from t
>                      where a == o.a
>                        and idate < o.idate
>                   order by idate desc
>                      limit 1) as oldv,
>                    c as newv
>               from t as o
>          union
>             select a,
>                    idate,
>                    'd' as variable,
>                     (select d
>                        from t
>                       where a == o.a
>                         and idate < o.idate
>                    order by idate desc
>                       limit 1) as oldv,
>                     d as newv
>                from t as o
>          union
>              select a,
>                     idate,
>                     'e' as variable,
>                     (select e
>                        from t
>                       where a == o.a
>                         and idate < o.idate
>                    order by idate desc
>                       limit 1) as oldv,
>                     e as newv
>                from t as o
>          )
>    where oldv <> newv;

Thanks, Keith.  Will do.

josé
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users