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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
