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;
--
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 Keith Medcalf
>Sent: Monday, 13 January, 2020 17:04
>To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
>Subject: Re: [sqlite] Capturing the changes in columns in a table
>
>
>Note this only requires that "idate" be a unique orderable sequence
>within "a" in order to work. It does not have to be particular (such as
>a date/datetime).
>It can be a date, a datetime, an integer (as in unixtime), a real (as in
>julianday number), or any old sequence number and it will still work
>exactly the same.
>
>Replace UNION with UNION ALL and add an ORDER BY clause at the end which
>may help performance depending on the amount of data.
>
>create unique index i on t (a, idate) to optimize retrieval.
>
>--
>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 Keith Medcalf
>>Sent: Monday, 13 January, 2020 16:51
>>To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
>>Subject: Re: [sqlite] Capturing the changes in columns in a table
>>
>>
>>create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);
>>insert into t (a, b, c, d, e, idate) values ('p001', 1, 2, 'n', 4,
>'2019-
>>02-11');
>>insert into t (a, b, c, d, e, idate) values ('p002', 2, 2, 'n', 4,
>'2019-
>>02-11');
>>insert into t (a, b, c, d, e, idate) values ('p003', 3, 2, 'n', 4,
>'2019-
>>02-11');
>>insert into t (a, b, c, d, e, idate) values ('p004', 4, 2, 'y', 4,
>'2019-
>>02-11');
>>insert into t (a, b, c, d, e, idate) values ('p005', 5, 2, 'y', 4,
>'2019-
>>02-11');
>>insert into t (a, b, c, d, e, idate) values ('p001', 10, 2, 'n', 4,
>>'2019-02-12');
>>insert into t (a, b, c, d, e, idate) values ('p002', 2, 4, 'n', 4,
>'2019-
>>02-12');
>>insert into t (a, b, c, d, e, idate) values ('p003', 3, 2, 'y', 4,
>'2019-
>>02-12');
>>insert into t (a, b, c, d, e, idate) values ('p004', 4, 2, 'y', 4,
>'2019-
>>02-12');
>>insert into t (a, b, c, d, e, idate) values ('p005', 5, 2, 'y', 4,
>'2019-
>>02-12');
>>insert into t (a, b, c, d, e, idate) values ('p001', 10, 2, 'n', 4,
>>'2019-02-13');
>>insert into t (a, b, c, d, e, idate) values ('p002', 2, 4, 'n', 4,
>'2019-
>>02-13');
>>insert into t (a, b, c, d, e, idate) values ('p003', 3, 2, 'y', 4,
>'2019-
>>02-13');
>>insert into t (a, b, c, d, e, idate) values ('p004', 5, 3, 'n', 4,
>'2019-
>>02-13');
>>insert into t (a, b, c, d, e, idate) values ('p005', 5, 3, 'y', 8,
>'2019-
>>02-13');
>>
>> select *
>> from (
>> select a,
>> idate,
>> 'b' as variable,
>> lag(b, 1) over (partition by a order by idate) as oldv,
>> b as newv
>> from t
>> union
>> select a,
>> idate,
>> 'c' as variable,
>> lag(c, 1) over (partition by a order by idate) as oldv,
>> c as newv
>> from t
>> union
>> select a,
>> idate,
>> 'd' as variable,
>> lag(d, 1) over (partition by a order by idate) as oldv,
>> d as newv
>> from t
>> union
>> select a,
>> idate,
>> 'e' as variable,
>> lag(e, 1) over (partition by a order by idate) as oldv,
>> e as newv
>> from t
>> )
>> where oldv <> newv;
>>
>>a idate variable oldv newv
>>---------- ---------- ---------- ---------- ----------
>>p001 2019-02-12 b 1 10
>>p002 2019-02-12 c 2 4
>>p003 2019-02-12 d n y
>>p004 2019-02-13 b 4 5
>>p004 2019-02-13 c 2 3
>>p004 2019-02-13 d y n
>>p005 2019-02-13 c 2 3
>>p005 2019-02-13 e 4 8
>>
>>Change "<>" to "IS NOT" to find out when their was no previous value
>>
>>--
>>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: Monday, 13 January, 2020 12:42
>>>To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
>>>Subject: [sqlite] Capturing the changes in columns in a table
>>>
>>>
>>>Greetings!
>>>
>>>Please observe the following,
>>>
>>>
>>>create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);
>>>
>>>insert into t (a, b, c, d, e, idate) values ('p001', 1, 2, 'n', 4,
>>'2019-
>>>02-11');
>>>
>>>insert into t (a, b, c, d, e, idate) values ('p002', 2, 2, 'n', 4,
>>'2019-
>>>02-11');
>>>
>>>insert into t (a, b, c, d, e, idate) values ('p003', 3, 2, 'n', 4,
>>'2019-
>>>02-11');
>>>
>>>insert into t (a, b, c, d, e, idate) values ('p004', 4, 2, 'y', 4,
>>'2019-
>>>02-11');
>>>
>>>insert into t (a, b, c, d, e, idate) values ('p005', 5, 2, 'y', 4,
>>'2019-
>>>02-11');
>>>
>>>insert into t (a, b, c, d, e, idate) values ('p001', 10, 2, 'n', 4,
>>>'2019-02-12');
>>>
>>>insert into t (a, b, c, d, e, idate) values ('p002', 2, 4, 'n', 4,
>>'2019-
>>>02-12');
>>>
>>>insert into t (a, b, c, d, e, idate) values ('p003', 3, 2, 'y', 4,
>>'2019-
>>>02-12');
>>>
>>>insert into t (a, b, c, d, e, idate) values ('p004', 4, 2, 'y', 4,
>>'2019-
>>>02-12');
>>>
>>>insert into t (a, b, c, d, e, idate) values ('p005', 5, 2, 'y', 4,
>>'2019-
>>>02-12');
>>>
>>>insert into t (a, b, c, d, e, idate) values ('p001', 10, 2, 'n', 4,
>>>'2019-02-13');
>>>
>>>insert into t (a, b, c, d, e, idate) values ('p002', 2, 4, 'n', 4,
>>'2019-
>>>02-13');
>>>
>>>insert into t (a, b, c, d, e, idate) values ('p003', 3, 2, 'y', 4,
>>'2019-
>>>02-13');
>>>
>>>insert into t (a, b, c, d, e, idate) values ('p004', 5, 3, 'n', 4,
>>'2019-
>>>02-13');
>>>
>>>insert into t (a, b, c, d, e, idate) values ('p005', 5, 3, 'y', 8,
>>'2019-
>>>02-13');
>>>
>>>
>>>The SQL below provide info only for two dates (2019-02-11 and 2019-02-
>>>12):
>>>
>>>
>>>select new.a,old.b,new.b, 'column b changed on ' || new.idate as info
>>>from t as new
>>>
>>> LEFT JOIN t as old ON
>>>
>>> new.idate = '2019-02-12'
>>>
>>>AND old.idate = '2019-02-11'
>>>
>>>AND new.a = old.a
>>>
>>>WHERE
>>>
>>> new.b != old.b
>>>
>>>UNION ALL
>>>
>>>select new.a,old.c,new.c, 'column c changed on ' || new.idate as info
>>>from t as new
>>>
>>> LEFT JOIN t as old ON
>>>
>>> new.idate = '2019-02-12'
>>>
>>>AND old.idate = '2019-02-11'
>>>
>>>AND new.a = old.a
>>>
>>>WHERE
>>>
>>> new.c != old.c
>>>
>>>UNION ALL
>>>
>>>select new.a,old.d,new.d, 'column d changed on ' || new.idate from t as
>>>new
>>>
>>> LEFT JOIN t as old ON
>>>
>>> new.idate = '2019-02-12'
>>>
>>>AND old.idate = '2019-02-11'
>>>
>>>AND new.a = old.a
>>>
>>>WHERE
>>>
>>> new.d != old.d
>>>
>>>UNION ALL
>>>
>>>select new.a,old.e,new.e, 'column e changed on ' || new.idate from t as
>>>new
>>>
>>> LEFT JOIN t as old ON
>>>
>>> new.idate = '2019-02-12'
>>>
>>>AND old.idate = '2019-02-11'
>>>
>>>AND new.a = old.a
>>>
>>>WHERE
>>>
>>> new.e != old.e
>>>
>>>ORDER by new.a;
>>>
>>>
>>>p001|1|10|column b changed on 2019-02-12
>>>p002|2|4|column c changed on 2019-02-12
>>>p003|n|y|column d changed on 2019-02-12
>>>sqlite>
>>>
>>>What I would like is to cover all of the dates in the same command, so
>>>that the output is this,
>>>
>>>p001|1|10|column b changed on 2019-02-12
>>>p002|2|4|column c changed on 2019-02-12
>>>p003|n|y|column d changed on 2019-02-12
>>>p004|4|5|column b changed on 2019-02-13
>>>p004|2|3|column c changed on 2019-02-13
>>>p004|y|n|column d changed on 2019-02-13
>>>p005|2|3|column c changed on 2019-02-13
>>>p005|4|8|column e changed on 2019-02-13
>>>
>>>Yes, I know I can get all the dates and build the SQL as I did above,
>>but
>>>is there a way to do this in one call? Thanks for the help.
>>>
>>>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
>
>
>
>_______________________________________________
>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
