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;
--
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 <sqlite-users-boun...@mailinglists.sqlite.org> On
>Behalf Of Jose Isaias Cabrera
>Sent: Tuesday, 14 January, 2020 06:19
>To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
>Subject: Re: [sqlite] Capturing the changes in columns in a table
>
>
>
>Keith Medcalf, on Monday, January 13, 2020 08:03 PM, wrote...
>>
>>
>> And this version is several times faster since only the changes are
>> union'd which minimizes the total number of records processed.
>> The index should be "create index i on t (a, idate);" Because of the
>way
>> indexes work, entries on the same a, idate will be ordered by n.
>(though
>> really idate should be unique within a, so the index should be a unique
>> index)
>>
>> select a, idate, variable, oldv, newv
>> from (
>> select a,
>> idate,
>> 'b' as variable,
>> lag(b, 1) over (partition by a order by idate) as
>oldv,
>> b as newv
>> from t
>> order by a, idate
>> )
>> where oldv <> newv
>> union all
>> select a, idate, variable, oldv, newv
>> from (
>> select a,
>> idate,
>> 'c' as variable,
>> lag(c, 1) over (partition by a order by idate) as
>oldv,
>> c as newv
>> from t
>> order by a, idate
>> )
>> where oldv <> newv
>> union all
>> select a, idate, variable, oldv, newv
>> from (
>> select a,
>> idate,
>> 'd' as variable,
>> lag(d, 1) over (partition by a order by idate) as
>oldv,
>> d as newv
>> from t
>> order by a, idate
>> )
>> where oldv <> newv
>> union all
>> select a, idate, variable, oldv, newv
>> from (
>> select a,
>> idate,
>> 'e' as variable,
>> lag(e, 1) over (partition by a order by idate) as
>oldv,
>> e as newv
>> from t
>> order by a, idate
>> )
>> where oldv <> newv
>> order by a, idate, variable;
>>
>
>Wow! Thanks, Keith. Yes, this one is much faster. As Ryan suggested, I
>will use this one. Thank so much. I really appreciate the help. I
>already have an index on (a,idate). I hope that I will be like you guys
>someday. ;-) Thanks again.
>
>josé
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
