Capturing the changes in columns in a table

classic Classic list List threaded Threaded
21 messages Options
12
Reply | Threaded
Open this post in threaded view
|

Capturing the changes in columns in a table

Jose Isaias Cabrera-4

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Capturing the changes in columns in a table

R Smith-2

On 2020/01/13 9:42 PM, Jose Isaias Cabrera wrote:

> 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');
>
> ...
>
> 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.


Of course there is :)

Many questions are not clear though.
I assumed you are only interested in day-on-day changes, because in many
days there can be many different values, if you measure them all against
the last day, it will look like many changes between day X and the last
day, when really you are probably interested only in the change from one
day to the next day.

I also assumed that the days you will give as the input, let's call that
min date and max date of the report, will refer to the days on which
changed happened - which seems obvious, but the query must change if it
is not the case.

Anyway, here is the SQL to achieve it. I made 2 queries, one in which it
just gets the min and max from your data, and the other in which you can
specify the min and max date. I guess the second one will be more what
you want - the nice t hing is your calling program only needs to adjust
those two values once, nowhere else:

(PS: I left the whole generated output in so you can see at the bottom,
those errors are not errors, but just informing us that SQLite is
creating indexes for those CTE tables, which is great and means this
should be relatively fast  on large datasets even.)


   -- SQLite version 3.30.1  [ Release: 2019-10-10 ]  on SQLitespeed
version 2.1.3.11.

   -- Script Items: 4          Parameter Count: 0
   -- 2020-01-14 00:08:17.875  |  [Info]       Script Initialized,
Started executing...
   --
================================================================================================

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')
,('p002', 2, 2, 'n', 4, '2019-02-11')
,('p003', 3, 2, 'n', 4, '2019-02-11')
,('p004', 4, 2, 'y', 4, '2019-02-11')
,('p005', 5, 2, 'y', 4, '2019-02-11')
,('p001',10, 2, 'n', 4, '2019-02-12')
,('p002', 2, 4, 'n', 4, '2019-02-12')
,('p003', 3, 2, 'y', 4, '2019-02-12')
,('p004', 4, 2, 'y', 4, '2019-02-12')
,('p005', 5, 2, 'y', 4, '2019-02-12')
,('p001',10, 2, 'n', 4, '2019-02-13')
,('p002', 2, 4, 'n', 4, '2019-02-13')
,('p003', 3, 2, 'y', 4, '2019-02-13')
,('p004', 5, 3, 'n', 4, '2019-02-13')
,('p005', 5, 3, 'y', 8, '2019-02-13')
;

-- This version guesses the min and max dates...
WITH DSpan(minDate, maxDate) AS (
   SELECT MIN(idate), MAX(idate) FROM t GROUP BY idate
), DDays(dayOldDate, dayNewDate) AS (
   SELECT minDate, date(minDate,'+1 day') FROM DSpan
   UNION ALL
   SELECT dayNewDate, date(dayNewDate,'+1 day') FROM DDays, DSpan WHERE
DDays.dayNewDate < DSpan.maxDate
), Chg(a, idate, col, oldVal, newVal) AS (
   SELECT DISTINCT tNew.a, tNew.idate, 'b', tOld.b,tNew.b
     FROM DDays
     JOIN t AS tOld ON tOld.idate = DDays.dayoldDate
     JOIN t AS tNew ON tNew.idate = DDays.dayNewDate
    WHERE tNew.a = tOld.a AND tNew.b != tOld.b
UNION ALL
   SELECT DISTINCT tNew.a, tNew.idate, 'c', tOld.c,tNew.c
     FROM DDays
     JOIN t AS tOld ON tOld.idate = DDays.dayoldDate
     JOIN t AS tNew ON tNew.idate = DDays.dayNewDate
    WHERE tNew.a = tOld.a AND tNew.c != tOld.c
UNION ALL
   SELECT DISTINCT tNew.a, tNew.idate, 'd', tOld.d,tNew.d
     FROM DDays
     JOIN t AS tOld ON tOld.idate = DDays.dayoldDate
     JOIN t AS tNew ON tNew.idate = DDays.dayNewDate
    WHERE tNew.a = tOld.a AND tNew.d != tOld.d
UNION ALL
   SELECT DISTINCT tNew.a, tNew.idate, 'e', tOld.e,tNew.e
     FROM DDays
     JOIN t AS tOld ON tOld.idate = DDays.dayoldDate
     JOIN t AS tNew ON tNew.idate = DDays.dayNewDate
    WHERE tNew.a = tOld.a AND tNew.e != tOld.e
)
SELECT Chg.a, Chg.oldVal, Chg.newVal,
        ('Column '||Chg.col||' changed on '||Chg.idate||' from
'||Chg.oldVal||' to '||Chg.newVal||'.') AS Change
   FROM Chg
  ORDER BY Chg.a, Chg.idate
;


   -- a    | oldVal | newVal | Change
   -- ---- | ------ | ------ | --------------------------------------------
   -- p001 |    1   |   10   | Column b changed on 2019-02-12 from 1 to 10.
   -- p002 |    2   |    4   | Column c changed on 2019-02-12 from 2 to 4.
   -- p003 |    n   |    y   | Column d changed on 2019-02-12 from n to y.
   -- p004 |    4   |    5   | Column b changed on 2019-02-13 from 4 to 5.
   -- p004 |    2   |    3   | Column c changed on 2019-02-13 from 2 to 3.
   -- p004 |    y   |    n   | Column d changed on 2019-02-13 from y to n.
   -- p005 |    2   |    3   | Column c changed on 2019-02-13 from 2 to 3.
   -- p005 |    4   |    8   | Column e changed on 2019-02-13 from 4 to 8.


-- This version let's you set it, I chose the last two of the days here.
WITH DSpan(minDate, maxDate) AS (
   SELECT '2019-02-12', '2019-02-13'
), DDays(dayOldDate, dayNewDate) AS (
   SELECT minDate, date(minDate,'+1 day') FROM DSpan
   UNION ALL
   SELECT dayNewDate, date(dayNewDate,'+1 day') FROM DDays, DSpan WHERE
DDays.dayNewDate < DSpan.maxDate
), Chg(a, idate, col, oldVal, newVal) AS (
   SELECT DISTINCT tNew.a, tNew.idate, 'b', tOld.b,tNew.b
     FROM DDays
     JOIN t AS tOld ON tOld.idate = DDays.dayoldDate
     JOIN t AS tNew ON tNew.idate = DDays.dayNewDate
    WHERE tNew.a = tOld.a AND tNew.b != tOld.b
UNION ALL
   SELECT DISTINCT tNew.a, tNew.idate, 'c', tOld.c,tNew.c
     FROM DDays
     JOIN t AS tOld ON tOld.idate = DDays.dayoldDate
     JOIN t AS tNew ON tNew.idate = DDays.dayNewDate
    WHERE tNew.a = tOld.a AND tNew.c != tOld.c
UNION ALL
   SELECT DISTINCT tNew.a, tNew.idate, 'd', tOld.d,tNew.d
     FROM DDays
     JOIN t AS tOld ON tOld.idate = DDays.dayoldDate
     JOIN t AS tNew ON tNew.idate = DDays.dayNewDate
    WHERE tNew.a = tOld.a AND tNew.d != tOld.d
UNION ALL
   SELECT DISTINCT tNew.a, tNew.idate, 'e', tOld.e,tNew.e
     FROM DDays
     JOIN t AS tOld ON tOld.idate = DDays.dayoldDate
     JOIN t AS tNew ON tNew.idate = DDays.dayNewDate
    WHERE tNew.a = tOld.a AND tNew.e != tOld.e
)
SELECT Chg.a, Chg.oldVal, Chg.newVal,
        ('Column '||Chg.col||' changed on '||Chg.idate||' from
'||Chg.oldVal||' to '||Chg.newVal||'.') AS Change
   FROM Chg
  ORDER BY Chg.a, Chg.idate
;


   -- a    | oldVal | newVal | Change
   -- ---- | ------ | ------ | -------------------------------------------
   -- p004 |    4   |    5   | Column b changed on 2019-02-13 from 4 to 5.
   -- p004 |    2   |    3   | Column c changed on 2019-02-13 from 2 to 3.
   -- p004 |    y   |    n   | Column d changed on 2019-02-13 from y to n.
   -- p005 |    2   |    3   | Column c changed on 2019-02-13 from 2 to 3.
   -- p005 |    4   |    8   | Column e changed on 2019-02-13 from 4 to 8.

   --   Script Stats: Total Script Execution Time:     0d 00h 00m and
00.050s
   --                 Total Script Query Time:         0d 00h 00m and
00.009s
   --                 Total Database Rows Changed:     15
   --                 Total Virtual-Machine Steps: 6990
   --                 Last executed Item Index:        4
   --                 Last Script Error:
   --
------------------------------------------------------------------------------------------------

   -- 2020-01-14 00:08:17.900  |  [Success]    Script Success.
   -- 2020-01-14 00:08:17.901  |  [Success]    Transaction Rolled back.
   -- -------  DB-Engine Logs (Contains logged information from all DB
connections during run)  ------
   -- [2020-01-14 00:08:17.826] APPLICATION : Script
E:\Documents\SQLiteAutoScript.sql started at 00:08:17.826 on 14 January.
   -- [2020-01-14 00:08:17.890] ERROR (284) : automatic index on
DDays(dayOldDate)
   -- [2020-01-14 00:08:17.890] ERROR (284) : automatic index on t(a)
   -- [2020-01-14 00:08:17.890] ERROR (284) : automatic index on
DDays(dayOldDate)
   -- [2020-01-14 00:08:17.890] ERROR (284) : automatic index on t(a)
   -- [2020-01-14 00:08:17.890] ERROR (284) : automatic index on
DDays(dayOldDate)
   -- [2020-01-14 00:08:17.891] ERROR (284) : automatic index on t(a)
   -- [2020-01-14 00:08:17.891] ERROR (284) : automatic index on
DDays(dayOldDate)
   -- [2020-01-14 00:08:17.891] ERROR (284) : automatic index on t(a)
   -- [2020-01-14 00:08:17.896] ERROR (284) : automatic index on
DDays(dayOldDate)
   -- [2020-01-14 00:08:17.896] ERROR (284) : automatic index on t(a)
   -- [2020-01-14 00:08:17.896] ERROR (284) : automatic index on
DDays(dayOldDate)
   -- [2020-01-14 00:08:17.896] ERROR (284) : automatic index on t(a)
   -- [2020-01-14 00:08:17.896] ERROR (284) : automatic index on
DDays(dayOldDate)
   -- [2020-01-14 00:08:17.896] ERROR (284) : automatic index on t(a)
   -- [2020-01-14 00:08:17.896] ERROR (284) : automatic index on
DDays(dayOldDate)
   -- [2020-01-14 00:08:17.896] ERROR (284) : automatic index on t(a)
   --
================================================================================================



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Capturing the changes in columns in a table

Jose Isaias Cabrera-4

R Smith, on Monday, January 13, 2020 05:25 PM, wrote...​

> On 2020/01/13 9:42 PM, Jose Isaias Cabrera wrote:​
> > 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');​
> >​
> > ...​
> >​
> > 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.​
> ​
> ​
> Of course there is :)​
> ​
> Many questions are not clear though.​
> I assumed you are only interested in day-on-day changes, because in many ​
> days there can be many different values, if you measure them all against ​
> the last day, it will look like many changes between day X and the last ​
> day, when really you are probably interested only in the change from one ​
> day to the next day.​
> ​
> I also assumed that the days you will give as the input, let's call that ​
> min date and max date of the report, will refer to the days on which ​
> changed happened - which seems obvious, but the query must change if it ​
> is not the case.​
> ​
> Anyway, here is the SQL to achieve it. I made 2 queries, one in which it ​
> just gets the min and max from your data, and the other in which you can ​
> specify the min and max date. I guess the second one will be more what ​
> you want - the nice t hing is your calling program only needs to adjust ​
> those two values once, nowhere else:​
> ​
> (PS: I left the whole generated output in so you can see at the bottom, ​
> those errors are not errors, but just informing us that SQLite is ​
> creating indexes for those CTE tables, which is great and means this ​
> should be relatively fast  on large datasets even.)​
> ​
> ​
>    -- SQLite version 3.30.1  [ Release: 2019-10-10 ]  on SQLitespeed ​
> version 2.1.3.11.​
> ​
>    -- Script Items: 4          Parameter Count: 0​
>    -- 2020-01-14 00:08:17.875  |  [Info]       Script Initialized, ​
> Started executing...​
>    -- ​
> ================================================================================================​
> ​
> 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')​
> ,('p002', 2, 2, 'n', 4, '2019-02-11')​
> ,('p003', 3, 2, 'n', 4, '2019-02-11')​
> ,('p004', 4, 2, 'y', 4, '2019-02-11')​
> ,('p005', 5, 2, 'y', 4, '2019-02-11')​
> ,('p001',10, 2, 'n', 4, '2019-02-12')​
> ,('p002', 2, 4, 'n', 4, '2019-02-12')​
> ,('p003', 3, 2, 'y', 4, '2019-02-12')​
> ,('p004', 4, 2, 'y', 4, '2019-02-12')​
> ,('p005', 5, 2, 'y', 4, '2019-02-12')​
> ,('p001',10, 2, 'n', 4, '2019-02-13')​
> ,('p002', 2, 4, 'n', 4, '2019-02-13')​
> ,('p003', 3, 2, 'y', 4, '2019-02-13')​
> ,('p004', 5, 3, 'n', 4, '2019-02-13')​
> ,('p005', 5, 3, 'y', 8, '2019-02-13')​
> ;​
> ​
> -- This version guesses the min and max dates...​
> WITH DSpan(minDate, maxDate) AS (​
>    SELECT MIN(idate), MAX(idate) FROM t GROUP BY idate​
> ), DDays(dayOldDate, dayNewDate) AS (​
>    SELECT minDate, date(minDate,'+1 day') FROM DSpan​
>    UNION ALL​
>    SELECT dayNewDate, date(dayNewDate,'+1 day') FROM DDays, DSpan WHERE ​
> DDays.dayNewDate, on ​
> ), Chg(a, idate, col, oldVal, newVal) AS (​
>    SELECT DISTINCT tNew.a, tNew.idate, 'b', tOld.b,tNew.b​
>      FROM DDays​
>      JOIN t AS tOld ON tOld.idate = DDays.dayoldDate​
>      JOIN t AS tNew ON tNew.idate = DDays.dayNewDate​
>     WHERE tNew.a = tOld.a AND tNew.b != tOld.b​
> UNION ALL​
>    SELECT DISTINCT tNew.a, tNew.idate, 'c', tOld.c,tNew.c​
>      FROM DDays​
>      JOIN t AS tOld ON tOld.idate = DDays.dayoldDate​
>      JOIN t AS tNew ON tNew.idate = DDays.dayNewDate​
>     WHERE tNew.a = tOld.a AND tNew.c != tOld.c​
> UNION ALL​
>    SELECT DISTINCT tNew.a, tNew.idate, 'd', tOld.d,tNew.d​
>      FROM DDays​
>      JOIN t AS tOld ON tOld.idate = DDays.dayoldDate​
>      JOIN t AS tNew ON tNew.idate = DDays.dayNewDate​
>     WHERE tNew.a = tOld.a AND tNew.d != tOld.d​
> UNION ALL​
>    SELECT DISTINCT tNew.a, tNew.idate, 'e', tOld.e,tNew.e​
>      FROM DDays​
>      JOIN t AS tOld ON tOld.idate = DDays.dayoldDate​
>      JOIN t AS tNew ON tNew.idate = DDays.dayNewDate​
>     WHERE tNew.a = tOld.a AND tNew.e != tOld.e​
> )​
> SELECT Chg.a, Chg.oldVal, Chg.newVal,​
>         ('Column '||Chg.col||' changed on '||Chg.idate||' from ​
> '||Chg.oldVal||' to '||Chg.newVal||'.') AS Change​
>    FROM Chg​
>   ORDER BY Chg.a, Chg.idate​
> ;​
> ​
> ​
>    -- a    | oldVal | newVal | Change​
>    -- ---- | ------ | ------ | --------------------------------------------​
>    -- p001 |    1   |   10   | Column b changed on 2019-02-12 from 1 to 10.​
>    -- p002 |    2   |    4   | Column c changed on 2019-02-12 from 2 to 4.​
>    -- p003 |    n   |    y   | Column d changed on 2019-02-12 from n to y.​
>    -- p004 |    4   |    5   | Column b changed on 2019-02-13 from 4 to 5.​
>    -- p004 |    2   |    3   | Column c changed on 2019-02-13 from 2 to 3.​
>    -- p004 |    y   |    n   | Column d changed on 2019-02-13 from y to n.​
>    -- p005 |    2   |    3   | Column c changed on 2019-02-13 from 2 to 3.​
>    -- p005 |    4   |    8   | Column e changed on 2019-02-13 from 4 to 8.​
> ​
> ​
> -- This version let's you set it, I chose the last two of the days here.​
> WITH DSpan(minDate, maxDate) AS (​
>    SELECT '2019-02-12', '2019-02-13'​
> ), DDays(dayOldDate, dayNewDate) AS (​
>    SELECT minDate, date(minDate,'+1 day') FROM DSpan​
>    UNION ALL​
>    SELECT dayNewDate, date(dayNewDate,'+1 day') FROM DDays, DSpan WHERE ​
> DDays.dayNewDate, on ​
> ), Chg(a, idate, col, oldVal, newVal) AS (​
>    SELECT DISTINCT tNew.a, tNew.idate, 'b', tOld.b,tNew.b​
>      FROM DDays​
>      JOIN t AS tOld ON tOld.idate = DDays.dayoldDate​
>      JOIN t AS tNew ON tNew.idate = DDays.dayNewDate​
>     WHERE tNew.a = tOld.a AND tNew.b != tOld.b​
> UNION ALL​
>    SELECT DISTINCT tNew.a, tNew.idate, 'c', tOld.c,tNew.c​
>      FROM DDays​
>      JOIN t AS tOld ON tOld.idate = DDays.dayoldDate​
>      JOIN t AS tNew ON tNew.idate = DDays.dayNewDate​
>     WHERE tNew.a = tOld.a AND tNew.c != tOld.c​
> UNION ALL​
>    SELECT DISTINCT tNew.a, tNew.idate, 'd', tOld.d,tNew.d​
>      FROM DDays​
>      JOIN t AS tOld ON tOld.idate = DDays.dayoldDate​
>      JOIN t AS tNew ON tNew.idate = DDays.dayNewDate​
>     WHERE tNew.a = tOld.a AND tNew.d != tOld.d​
> UNION ALL​
>    SELECT DISTINCT tNew.a, tNew.idate, 'e', tOld.e,tNew.e​
>      FROM DDays​
>      JOIN t AS tOld ON tOld.idate = DDays.dayoldDate​
>      JOIN t AS tNew ON tNew.idate = DDays.dayNewDate​
>     WHERE tNew.a = tOld.a AND tNew.e != tOld.e​
> )​
> SELECT Chg.a, Chg.oldVal, Chg.newVal,​
>         ('Column '||Chg.col||' changed on '||Chg.idate||' from ​
> '||Chg.oldVal||' to '||Chg.newVal||'.') AS Change​
>    FROM Chg​
>   ORDER BY Chg.a, Chg.idate​
> ;​
> ​
> ​
>    -- a    | oldVal | newVal | Change​
>    -- ---- | ------ | ------ | -------------------------------------------​
>    -- p004 |    4   |    5   | Column b changed on 2019-02-13 from 4 to 5.​
>    -- p004 |    2   |    3   | Column c changed on 2019-02-13 from 2 to 3.​
>    -- p004 |    y   |    n   | Column d changed on 2019-02-13 from y to n.​
>    -- p005 |    2   |    3   | Column c changed on 2019-02-13 from 2 to 3.​
>    -- p005 |    4   |    8   | Column e changed on 2019-02-13 from 4 to 8.​
> ​
>    --   Script Stats: Total Script Execution Time:     0d 00h 00m and ​
> 00.050s​
>    --                 Total Script Query Time:         0d 00h 00m and ​
> 00.009s​
>    --                 Total Database Rows Changed:     15​
>    --                 Total Virtual-Machine Steps: 6990​
>    --                 Last executed Item Index:        4​
>    --                 Last Script Error:​
>    -- ​
> ------------------------------------------------------------------------------------------------​
> ​
>    -- 2020-01-14 00:08:17.900  |  [Success]    Script Success.​
>    -- 2020-01-14 00:08:17.901  |  [Success]    Transaction Rolled back.​
>    -- -------  DB-Engine Logs (Contains logged information from all DB ​
> connections during run)  ------​
>    -- [2020-01-14 00:08:17.826] APPLICATION : Script ​
> E:\Documents\SQLiteAutoScript.sql started at 00:08:17.826 on 14 January.​
>    -- [2020-01-14 00:08:17.890] ERROR (284) : automatic index on ​
> DDays(dayOldDate)​
>    -- [2020-01-14 00:08:17.890] ERROR (284) : automatic index on t(a)​
>    -- [2020-01-14 00:08:17.890] ERROR (284) : automatic index on ​
> DDays(dayOldDate)​
>    -- [2020-01-14 00:08:17.890] ERROR (284) : automatic index on t(a)​
>    -- [2020-01-14 00:08:17.890] ERROR (284) : automatic index on ​
> DDays(dayOldDate)​
>    -- [2020-01-14 00:08:17.891] ERROR (284) : automatic index on t(a)​
>    -- [2020-01-14 00:08:17.891] ERROR (284) : automatic index on ​
> DDays(dayOldDate)​
>    -- [2020-01-14 00:08:17.891] ERROR (284) : automatic index on t(a)​
>    -- [2020-01-14 00:08:17.896] ERROR (284) : automatic index on ​
> DDays(dayOldDate)​
>    -- [2020-01-14 00:08:17.896] ERROR (284) : automatic index on t(a)​
>    -- [2020-01-14 00:08:17.896] ERROR (284) : automatic index on ​
> DDays(dayOldDate)​
>    -- [2020-01-14 00:08:17.896] ERROR (284) : automatic index on t(a)​
>    -- [2020-01-14 00:08:17.896] ERROR (284) : automatic index on ​
> DDays(dayOldDate)​
>    -- [2020-01-14 00:08:17.896] ERROR (284) : automatic index on t(a)​
>    -- [2020-01-14 00:08:17.896] ERROR (284) : automatic index on ​
> DDays(dayOldDate)​
>    -- [2020-01-14 00:08:17.896] ERROR (284) : automatic index on t(a)​
>    -- ​

Wow!  Thanks for this.  I had not thought about your questions.  My boss said, I need to know all the changes per project whenever it happened. So,... I will have to revise my thinking, but I have enough with your help to continue. I am going to have to reload SQLitespeed, and try it again. :-)  Thanks.​

josé
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Capturing the changes in columns in a table

R Smith-2

On 2020/01/14 1:11 AM, Jose Isaias Cabrera wrote:
> R Smith, on Monday, January 13, 2020 05:25 PM, wrote...​
>> ....
> ​
> Wow!  Thanks for this.  I had not thought about your questions.  My boss said, I need to know all the changes per project whenever it happened. So,... I will have to revise my thinking, but I have enough with your help to continue. I am going to have to reload SQLitespeed, and try it again. :-)  Thanks.​

A - Es un placer,

B - It's important to really understand how they want to see changes.
Also I'm simply assuming (thanks to your example) that changes do not
happen more frequently than once a day, and that the time of it is not
important. If it is, the query will need to be adjusted.

C - I know you probably know this, but just in case it isn't 100% clear:
there is nothing about the SQL I posted that requires SQLitespeed. It is
simply the easiest for me to use and it outputs SQL+Results the way I
like it (so feel free), but that query will work in any SQLite platform
for any version of SQLite - after 3.8 that is (or 3.7... or whatever
version introduced CTE's, my memory is suddenly failing).


Good luck with your quest!
Ryan


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Capturing the changes in columns in a table

Keith Medcalf
In reply to this post by Jose Isaias Cabrera-4

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 <[hidden email]> On
>Behalf Of Jose Isaias Cabrera
>Sent: Monday, 13 January, 2020 12:42
>To: SQLite mailing list <[hidden email]>
>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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Capturing the changes in columns in a table

Keith Medcalf

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 <[hidden email]> On
>Behalf Of Keith Medcalf
>Sent: Monday, 13 January, 2020 16:51
>To: SQLite mailing list <[hidden email]>
>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 <[hidden email]> On
>>Behalf Of Jose Isaias Cabrera
>>Sent: Monday, 13 January, 2020 12:42
>>To: SQLite mailing list <[hidden email]>
>>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
>>[hidden email]
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Capturing the changes in columns in a table

R Smith-2
In reply to this post by Keith Medcalf
Jose, I like Keith's version better using the Windowing functions
assuming your version of SQLite is newer than 3.27 (or whenever Window
functions were introduced, again my memory fails...)

Most importantly, the CTE query /requires/ changes be day-on-day to be
seen, which is the case in your example, but might not be the real life
case, whereas this window-function query of Keith will spot changes even
if updates are logged days apart, or indeed happened on the same day.

You still need to make sure of a few things, but I would definitely use
this query.


On 2020/01/14 1:51 AM, Keith Medcalf wrote:

> 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
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Capturing the changes in columns in a table

Keith Medcalf
In reply to this post by Keith Medcalf

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 <[hidden email]> On
>Behalf Of Keith Medcalf
>Sent: Monday, 13 January, 2020 17:04
>To: SQLite mailing list <[hidden email]>
>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 <[hidden email]> On
>>Behalf Of Keith Medcalf
>>Sent: Monday, 13 January, 2020 16:51
>>To: SQLite mailing list <[hidden email]>
>>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 <[hidden email]> On
>>>Behalf Of Jose Isaias Cabrera
>>>Sent: Monday, 13 January, 2020 12:42
>>>To: SQLite mailing list <[hidden email]>
>>>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
>>>[hidden email]
>>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>>
>>_______________________________________________
>>sqlite-users mailing list
>>[hidden email]
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Capturing the changes in columns in a table

Jean-Luc Hainaut
In reply to this post by Jose Isaias Cabrera-4

Another version that doesn't use CTE nor window functions:

select t1.a as Proj, t2.idate as "On", 'b' as Var, t1.b as oldVal, t2.b
as newVal
from   t t1,t t2
where  t1.a = t2.a
and    t2.idate = date(t1.idate,'+1 day')
and    t1.b <> t2.b
    union all
select t1.a as Proj, t2.idate as "On", 'c' as Var, t1.c as oldVal, t2.c
as newVal
from   t t1,t t2
where  t1.a = t2.a
and    t2.idate = date(t1.idate,'+1 day')
and    t1.c <> t2.c
    union all
select t1.a as Proj, t2.idate as "On", 'd' as Var, t1.d as oldVal, t2.d
as newVal
from   t t1,t t2
where  t1.a = t2.a
and    t2.idate = date(t1.idate,'+1 day')
and    t1.d <> t2.d
    union all
select t1.a as Proj, t2.idate as "On", 'e' as Var, t1.e as oldVal, t2.e
as newVal
from   t t1,t t2
where  t1.a = t2.a
and    t2.idate = date(t1.idate,'+1 day')
and    t1.e <> t2.e
order by Proj,"On";

Valid if there is one state for each project on each date.

Jean-Luc Hainaut


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Capturing the changes in columns in a table

Jose Isaias Cabrera-4
In reply to this post by R Smith-2


R Smith, on Monday, January 13, 2020 06:49 PM, wrote...

>
>
> On 2020/01/14 1:11 AM, Jose Isaias Cabrera wrote:
> > R Smith, on Monday, January 13, 2020 05:25 PM, wrote...​
> >> ....
> > ​
> > Wow! Thanks for this. I had not thought about your questions. My boss
> said, I need to know all the changes per project whenever it happened.
> So,... I will have to revise my thinking, but I have enough with your help
> to continue. I am going to have to reload SQLitespeed, and try it again.
> :-) Thanks.​
>
> A - Es un placer,
Wow!  Spanish speaker also. :-)

> B - It's important to really understand how they want to see changes.
> Also I'm simply assuming (thanks to your example) that changes do not
> happen more frequently than once a day, and that the time of it is not
> important. If it is, the query will need to be adjusted.
Yes, a dumb of a system is provided daily and even if it happens more than once a day, the date contains hour also, which will also work with your example.  Thanks.

> C - I know you probably know this, but just in case it isn't 100% clear:
> there is nothing about the SQL I posted that requires SQLitespeed. It is
> simply the easiest for me to use and it outputs SQL+Results the way I
> like it (so feel free), but that query will work in any SQLite platform
> for any version of SQLite - after 3.8 that is (or 3.7... or whatever
> version introduced CTE's, my memory is suddenly failing).
Yes.  I know.  But I like those stats. :-)  Thanks.

josé

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Capturing the changes in columns in a table

Jose Isaias Cabrera-4
In reply to this post by Keith Medcalf


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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Capturing the changes in columns in a table

Jose Isaias Cabrera-4
In reply to this post by Jean-Luc Hainaut


Jean-Luc Hainaut, on Tuesday, January 14, 2020 07:25 AM, wrote...

>
>
> Another version that doesn't use CTE nor window functions:
>
> select t1.a as Proj, t2.idate as "On", 'b' as Var, t1.b as oldVal, t2.b
> as newVal
> from   t t1,t t2
> where  t1.a = t2.a
> and    t2.idate = date(t1.idate,'+1 day')
> and    t1.b <> t2.b
>     union all
> select t1.a as Proj, t2.idate as "On", 'c' as Var, t1.c as oldVal, t2.c
> as newVal
> from   t t1,t t2
> where  t1.a = t2.a
> and    t2.idate = date(t1.idate,'+1 day')
> and    t1.c <> t2.c
>     union all
> select t1.a as Proj, t2.idate as "On", 'd' as Var, t1.d as oldVal, t2.d
> as newVal
> from   t t1,t t2
> where  t1.a = t2.a
> and    t2.idate = date(t1.idate,'+1 day')
> and    t1.d <> t2.d
>     union all
> select t1.a as Proj, t2.idate as "On", 'e' as Var, t1.e as oldVal, t2.e
> as newVal
> from   t t1,t t2
> where  t1.a = t2.a
> and    t2.idate = date(t1.idate,'+1 day')
> and    t1.e <> t2.e
> order by Proj,"On";
>
> Valid if there is one state for each project on each date.

Thanks, Jean-Luc.  This one is also pretty fast. Interesting.

josé
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Capturing the changes in columns in a table

Keith Medcalf
In reply to this post by Jose Isaias Cabrera-4

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 <[hidden email]> On
>Behalf Of Jose Isaias Cabrera
>Sent: Tuesday, 14 January, 2020 06:19
>To: SQLite mailing list <[hidden email]>
>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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Capturing the changes in columns in a table

Jose Isaias Cabrera-4

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Capturing the changes in columns in a table

sub sk79
In reply to this post by Jose Isaias Cabrera-4
> Yes, a dumb of a system is provided daily


There are some great solutions already here for offline processing as
stated in your question. However, something you might also want to consider
(if you have requisite access to the system) is to use ‘after insert’
trigger(s) to online capture the change log  into a table and dump that
table along with other tables. This would be easiest and fastest solution
while not needing any indexes to be added or queries to be optimized.

- Neal

>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Capturing the changes in columns in a table

Jose Isaias Cabrera-4

sub sk79, on Tuesday, January 14, 2020 10:54 AM, wrote...

>
> > Yes, a dumb of a system is provided daily
>
>
> There are some great solutions already here for offline processing as
> stated in your question. However, something you might also want to
> consider
> (if you have requisite access to the system) is to use ‘after insert’
> trigger(s) to online capture the change log  into a table and dump that
> table along with other tables. This would be easiest and fastest solution
> while not needing any indexes to be added or queries to be optimized.
That is an idea I have not thought about, Neal.  Thanks.  The boss comes up with lots of requests, and these have to be done yesterday. I will have to look into triggers. Have not used them yet. :-(

josé
<http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Capturing the changes in columns in a table

Keith Medcalf

On Tuesday, 14 January, 2020 09:03, Jose Isaias Cabrera <[hidden email]> wrote:

>That is an idea I have not thought about, Neal.  Thanks.  The boss comes
>up with lots of requests, and these have to be done yesterday. I will
>have to look into triggers. Have not used them yet. :-(

Here is some sample triggers that records the changes as they happen ...
create table t
(
    n integer primary key,
    a not null,
    b,
    c,
    d,
    e,
    idate not null,
    unique (a, idate)
);
create table audit_t
(
    a not null,
    changed not null,
    idate not null,
    variable not null,
    oldv,
    newv
);

create trigger t_upd_1 after insert on t begin
    insert into audit_t
    select new.a,
           julianday(),
           new.idate,
           'b',
           (select b from t where a == new.a and idate < new.idate order by idate desc) as oldv,
           new.b as newv
     where newv <> oldv;
    insert into audit_t
    select new.a,
           julianday(),
           new.idate,
           'c',
           (select c from t where a == new.a and idate < new.idate order by idate desc) as oldv,
           new.c as newv
     where newv <> oldv;
    insert into audit_t
    select new.a,
           julianday(),
           new.idate,
           'd',
           (select d from t where a == new.a and idate < new.idate order by idate desc) as oldv,
           new.d as newv
     where newv <> oldv;
    insert into audit_t
    select new.a,
           julianday(),
           new.idate,
           'e',
           (select e from t where a == new.a and idate < new.idate order by idate desc) as oldv,
           new.e as newv
     where newv <> oldv;
end;

create trigger t_upd_nocando before update of a, idate on t begin
  select raise(ABORT, 'You cannot update columns a or idate');
end;

create trigger t_upd_b after update of b on t begin
  insert into audit_t
  select old.a,
         julianday(),
         old.idate,
         'b',
         old.b,
         new.b;
end;
create trigger t_upd_c after update of c on t begin
  insert into audit_t
  select old.a,
         julianday(),
         old.idate,
         'c',
         old.c,
         new.c;
end;
create trigger t_upd_d after update of d on t begin
  insert into audit_t
  select old.a,
         julianday(),
         old.idate,
         'd',
         old.d,
         new.d;
end;
create trigger t_upd_e after update of e on t begin
  insert into audit_t
  select old.a,
         julianday(),
         old.idate,
         'e',
         old.e,
         new.e;
end;


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 audit_t;
a           changed           idate       variable    oldv        newv
----------  ----------------  ----------  ----------  ----------  ----------
p001        2458863.19728274  2019-02-12  b           1           10
p002        2458863.19728274  2019-02-12  c           2           4
p003        2458863.19728275  2019-02-12  d           n           y
p004        2458863.19728282  2019-02-13  b           4           5
p004        2458863.19728282  2019-02-13  c           2           3
p004        2458863.19728282  2019-02-13  d           y           n
p005        2458863.19728284  2019-02-13  c           2           3
p005        2458863.19728284  2019-02-13  e           4           8

update t set b=32 where a='p002' and idate=='2019-02-12';

select * from audit_t;
a           changed           idate       variable    oldv        newv
----------  ----------------  ----------  ----------  ----------  ----------
p001        2458863.19728274  2019-02-12  b           1           10
p002        2458863.19728274  2019-02-12  c           2           4
p003        2458863.19728275  2019-02-12  d           n           y
p004        2458863.19728282  2019-02-13  b           4           5
p004        2458863.19728282  2019-02-13  c           2           3
p004        2458863.19728282  2019-02-13  d           y           n
p005        2458863.19728284  2019-02-13  c           2           3
p005        2458863.19728284  2019-02-13  e           4           8
p002        2458863.19728295  2019-02-12  b           2           32

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.




_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Capturing the changes in columns in a table

David Raymond
A note and a question on subqueries. On reading:

select
...
(select b from t where a == new.a and idate < new.idate order by idate desc) as oldv,
...

My brain started yelling that that needed a "limit 1" on the subquery so that it would only return 1 row.

I looked in the SQLite docs though and it seems like I'm wrong.
https://www.sqlite.org/lang_expr.html
"The value of a subquery expression is the first row of the result from the enclosed SELECT statement."

Then to make sure my brain was remembering correctly I checked Postgres and found this.
https://www.postgresql.org/docs/current/sql-expressions.html#SQL-SYNTAX-SCALAR-SUBQUERIES
"It is an error to use a query that returns more than one row or more than one column as a scalar subquery."


So my questions then are:

How is that handled by other databases?

Is that something worthy of being on the quirks page (https://www.sqlite.org/quirks.html), or is it just in the realm of non-quirky normal-level differences?


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Capturing the changes in columns in a table

Jose Isaias Cabrera-4
In reply to this post by Keith Medcalf

Keith Medcalf, on Tuesday, January 14, 2020 11:49 AM, wrote...

>
>
> On Tuesday, 14 January, 2020 09:03, Jose Isaias Cabrera
> <[hidden email]> wrote:
>
> >That is an idea I have not thought about, Neal.  Thanks.  The boss comes
> >up with lots of requests, and these have to be done yesterday. I will
> >have to look into triggers. Have not used them yet. :-(
>
> Here is some sample triggers that records the changes as they happen ...
> create table t
> (
>     n integer primary key,
>     a not null,
>     b,
>     c,
>     d,
>     e,
>     idate not null,
>     unique (a, idate)
> );
> create table audit_t
> (
>     a not null,
>     changed not null,
>     idate not null,
>     variable not null,
>     oldv,
>     newv
> );
>
> create trigger t_upd_1 after insert on t begin
>     insert into audit_t
>     select new.a,
>            julianday(),
>            new.idate,
>            'b',
> (select b from t where a == new.a and idate < new.idate order by idate
> desc) as oldv,
>            new.b as newv
>      where newv <> oldv;
>     insert into audit_t
>     select new.a,
>            julianday(),
>            new.idate,
>            'c',
> (select c from t where a == new.a and idate < new.idate order by idate
> desc) as oldv,
>            new.c as newv
>      where newv <> oldv;
>     insert into audit_t
>     select new.a,
>            julianday(),
>            new.idate,
>            'd',
> (select d from t where a == new.a and idate < new.idate order by idate
> desc) as oldv,
>            new.d as newv
>      where newv <> oldv;
>     insert into audit_t
>     select new.a,
>            julianday(),
>            new.idate,
>            'e',
> (select e from t where a == new.a and idate < new.idate order by idate
> desc) as oldv,
>            new.e as newv
>      where newv <> oldv;
> end;
>
> create trigger t_upd_nocando before update of a, idate on t begin
>   select raise(ABORT, 'You cannot update columns a or idate');
> end;
>
> create trigger t_upd_b after update of b on t begin
>   insert into audit_t
>   select old.a,
>          julianday(),
>          old.idate,
>          'b',
>          old.b,
>          new.b;
> end;
> create trigger t_upd_c after update of c on t begin
>   insert into audit_t
>   select old.a,
>          julianday(),
>          old.idate,
>          'c',
>          old.c,
>          new.c;
> end;
> create trigger t_upd_d after update of d on t begin
>   insert into audit_t
>   select old.a,
>          julianday(),
>          old.idate,
>          'd',
>          old.d,
>          new.d;
> end;
> create trigger t_upd_e after update of e on t begin
>   insert into audit_t
>   select old.a,
>          julianday(),
>          old.idate,
>          'e',
>          old.e,
>          new.e;
> end;
>
>
> 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 audit_t;
> a           changed           idate       variable    oldv        newv
> ---------- ---------------- ---------- ---------- ---------- ----------
> p001        2458863.19728274  2019-02-12  b           1           10
> p002        2458863.19728274  2019-02-12  c           2           4
> p003        2458863.19728275  2019-02-12  d           n           y
> p004        2458863.19728282  2019-02-13  b           4           5
> p004        2458863.19728282  2019-02-13  c           2           3
> p004        2458863.19728282  2019-02-13  d           y           n
> p005        2458863.19728284  2019-02-13  c           2           3
> p005        2458863.19728284  2019-02-13  e           4           8
>
> update t set b=32 where a='p002' and idate=='2019-02-12';
>
> select * from audit_t;
> a           changed           idate       variable    oldv        newv
> ---------- ---------------- ---------- ---------- ---------- ----------
> p001        2458863.19728274  2019-02-12  b           1           10
> p002        2458863.19728274  2019-02-12  c           2           4
> p003        2458863.19728275  2019-02-12  d           n           y
> p004        2458863.19728282  2019-02-13  b           4           5
> p004        2458863.19728282  2019-02-13  c           2           3
> p004        2458863.19728282  2019-02-13  d           y           n
> p005        2458863.19728284  2019-02-13  c           2           3
> p005        2458863.19728284  2019-02-13  e           4           8
> p002        2458863.19728295  2019-02-12  b           2           32

Wow! Thanks Keith.  I will have to study these and understand them before getting into it.  Thanks again.

josé
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Capturing the changes in columns in a table

R Smith-2
In reply to this post by David Raymond

On 2020/01/14 7:10 PM, David Raymond wrote:

> A note and a question on subqueries. On reading:
>
> select
> ...
> (select b from t where a == new.a and idate < new.idate order by idate desc) as oldv,
> ...
>
> My brain started yelling that that needed a "limit 1" on the subquery so that it would only return 1 row.
>
> I looked in the SQLite docs though and it seems like I'm wrong.
> https://www.sqlite.org/lang_expr.html
> "The value of a subquery expression is the first row of the result from the enclosed SELECT statement."
>
> Then to make sure my brain was remembering correctly I checked Postgres and found this.
> https://www.postgresql.org/docs/current/sql-expressions.html#SQL-SYNTAX-SCALAR-SUBQUERIES
> "It is an error to use a query that returns more than one row or more than one column as a scalar subquery."
>
>
> So my questions then are:
>
> How is that handled by other databases?
>
> Is that something worthy of being on the quirks page (https://www.sqlite.org/quirks.html), or is it just in the realm of non-quirky normal-level differences?


I cannot speak for ALL databases, but mostly PostGres, MySQL and MSSQL
fails if violated only.  (I think MSSQL warns, depending on the version,
when you prepare a non-limited sub-query).

Mostly though, they assume the Query programmer knows what (s)he is
doing and will run without problem - that is, until the sub-query
actually hits more than 1 row, at which point the current operation will
fail with an error (something like: "Scalar Sub-Query may only return 1
row").

SQLite seems to regard that if you ask for a scalar sub-query, you
obviously mean to have just one row... so it returns just 1.

Both approaches have merit.
I always lean more towards "It's better to error out", and if it is a
case of you just forgetting to place the LIMIT or TOP clause in your
sub-query, then it's great that it errors out to help you rectify it
NOW, like the client-server clade.

But,

I'm slightly more a fan of "what happens in dev must happen in
production" - and in this case I'm leaning the sqlite way since, with
the other servers, the disadvantage is that your dev server may well
never produce more than one row, so always seem to be working, and then
it fails once in production with more real data.

I'd rather have A - the Query error out on Prepare when it realizes the
LIMIT/TOP clause is missing - or B - auto-recognizes the specific
sub-query type and only returns one row (a-la-sqlite), but I don't think
the "Accept it until the shape of the data makes it fail" is a great
solution.


/2c

Ryan

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
12