SQLite Forum

How to merge two or more tables that have the same structure?
Login

How to merge two or more tables that have the same structure?

(1) By anonymous on 2021-12-13 14:42:49 [link] [source]

I'm using sqlite to log real-time data periodically. In order to save disk space, I use one table for one data. The table has two columns : time and value, and the record will be inserted to the table only if the data is changed. 
My question is how to merge these tables to create a overview for all datas in one table?

Here's an example
Table 1

time  value
------------

12345  50
12350  70
12352  80

Table 2

time  value
------------

12346  49
12347  77
12348  88
12350  22


Table 3

time  value
-------------

12346  99
12349  23
12351  45


I want to get the following result by execute some sql query

time  value1  value2   value3
-------------------------------

12345    50      null       null
12346    50      49       99
12347    50      77       99
12348    50      88       99
12349    50      88       23
12350    70      22       23
12351    70      22       45
12352    80      22       45

(2) By ddevienne on 2021-12-13 15:16:35 in reply to 1 [link] [source]

Scatter:

C:\Users\ddevienne>sqlite3 db1
SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
sqlite> create table t (time integer primary key, value);
sqlite> insert into t values (12345,50), (12350, 70), (12352, 80);
sqlite> .exit

C:\Users\ddevienne>sqlite3 db2
SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
sqlite> create table t (time integer primary key, value);
sqlite> insert into t values (12346, 49), (12347, 77), (12348, 88), (12350, 22);
sqlite> .exit

C:\Users\ddevienne>sqlite3 db3
SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
sqlite> create table t (time integer primary key, value);
sqlite> insert into t values (12346, 99), (12349, 23), (12351, 45);
sqlite> .exit

Gather:

C:\Users\ddevienne>sqlite3 db-All
SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
sqlite> create table t (time integer primary key, value1, value2, value3);
sqlite> attach db1 as db1;
sqlite> attach db2 as db2;
sqlite> attach db3 as db3;
sqlite> with scale(time) as (
   ...>   select time from db1.t
   ...>   union
   ...>   select time from db2.t
   ...>   union
   ...>   select time from db3.t
   ...> )
   ...> insert into t
   ...> select time, t1.value, t2.value, t3.value
   ...>   from scale
   ...>   left join db1.t as t1 using (time)
   ...>   left join db2.t as t2 using (time)
   ...>   left join db3.t as t3 using (time);
sqlite> .mode box
sqlite> select * from t;
┌───────┬────────┬────────┬────────┐
│ time  │ value1 │ value2 │ value3 │
├───────┼────────┼────────┼────────┤
│ 12345 │ 50     │        │        │
│ 12346 │        │ 49     │ 99     │
│ 12347 │        │ 77     │        │
│ 12348 │        │ 88     │        │
│ 12349 │        │        │ 23     │
│ 12350 │ 70     │ 22     │        │
│ 12351 │        │        │ 45     │
│ 12352 │ 80     │        │        │
└───────┴────────┴────────┴────────┘
sqlite>

If time is not your primary key, the multi-way join risks being slow.

(3) By anonymous on 2021-12-13 15:28:46 in reply to 2 [link] [source]

thanks for your reply. Using join will reault some row being null. I'd like to fill them as their previous valid value because the null value means no data change at that moment. Is there any way to do it?

(4.1) By ddevienne on 2021-12-13 16:18:07 edited from 4.0 in reply to 3 [link] [source]

This is wrong:

sqlite> update t set value1 = (select value1 from t t2 where t2.time < t.time and t2.value1 is not null) where value1 is null;
sqlite> update t set value2 = (select value2 from t t2 where t2.time < t.time and t2.value2 is not null) where value2 is null;
sqlite> update t set value3 = (select value3 from t t2 where t2.time < t.time and t2.value3 is not null) where value3 is null;
sqlite> select * from t;
┌───────┬────────┬────────┬────────┐
│ time  │ value1 │ value2 │ value3 │
├───────┼────────┼────────┼────────┤
│ 12345 │ 50     │        │        │
│ 12346 │ 50     │ 49     │ 99     │
│ 12347 │ 50     │ 77     │ 99     │
│ 12348 │ 50     │ 88     │ 99     │
│ 12349 │ 50     │ 49     │ 23     │
│ 12350 │ 70     │ 22     │ 99     │
│ 12351 │ 50     │ 49     │ 45     │
│ 12352 │ 80     │ 49     │ 99     │
└───────┴────────┴────────┴────────┘

Because the query picks any preceding non-null value, but not the closest one as you want.

This probably needs Window Functions, but I'm not familiar enough with them,
and don't have enough time to play with this more.

(5) By David Raymond (dvdraymond) on 2021-12-13 16:33:34 in reply to 4.1 [source]

You should be able to order the sub query then, right?

update t set value1 = (select value1 from t as t2 where t2.time < t.time and t2.value1 is not null order by t2.time desc limit 1) where value1 is null;

(7) By ddevienne on 2021-12-13 17:21:08 in reply to 5 [link] [source]

Very nice David!

So much so that my horrible Window+JSON1 (incomplete) soup!!!

sqlite> select * from t;
┌───────┬────────┬────────┬────────┐
│ time  │ value1 │ value2 │ value3 │
├───────┼────────┼────────┼────────┤
│ 12345 │ 50     │        │        │
│ 12346 │        │ 49     │ 99     │
│ 12347 │        │ 77     │        │
│ 12348 │        │ 88     │        │
│ 12349 │        │        │ 23     │
│ 12350 │ 70     │ 22     │        │
│ 12351 │        │        │ 45     │
│ 12352 │ 80     │        │        │
└───────┴────────┴────────┴────────┘
sqlite> update t set value1 = (select value1 from t as t2 where t2.time < t.time and t2.value1 is not null order by t2.time desc limit 1) where value1 is null;
sqlite> update t set value2 = (select value2 from t as t2 where t2.time < t.time and t2.value2 is not null order by t2.time desc limit 1) where value2 is null;
sqlite> update t set value3 = (select value3 from t as t2 where t2.time < t.time and t2.value3 is not null order by t2.time desc limit 1) where value3 is null;
sqlite> select * from t;
┌───────┬────────┬────────┬────────┐
│ time  │ value1 │ value2 │ value3 │
├───────┼────────┼────────┼────────┤
│ 12345 │ 50     │        │        │
│ 12346 │ 50     │ 49     │ 99     │
│ 12347 │ 50     │ 77     │ 99     │
│ 12348 │ 50     │ 88     │ 99     │
│ 12349 │ 50     │ 88     │ 23     │
│ 12350 │ 70     │ 22     │ 23     │
│ 12351 │ 70     │ 22     │ 45     │
│ 12352 │ 80     │ 22     │ 45     │
└───────┴────────┴────────┴────────┘
sqlite>

(6) By ddevienne on 2021-12-13 17:16:46 in reply to 4.1 [link] [source]

Well, I have found a horrible way to select the proper value of a single column :)

sqlite> with filler(time, prec_values_as_json) as (
   ...>   select time, json_group_array(value1) FILTER (WHERE value1 is not null) over win
   ...>   from t WINDOW win as (
   ...>     order by time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
   ...>   )
   ...> )
   ...> select time, json_extract(prec_values_as_json, '$[#-1]') "value1 no holes" from filler;
┌───────┬─────────────────┐
│ time  │ value1 no holes │
├───────┼─────────────────┤
│ 12345 │ 50              │
│ 12346 │ 50              │
│ 12347 │ 50              │
│ 12348 │ 50              │
│ 12349 │ 50              │
│ 12350 │ 70              │
│ 12351 │ 70              │
│ 12352 │ 80              │
└───────┴─────────────────┘

I used JSON1 to accumulate the preceding non-null values into an array,
so I could use the tail of that array later (thanks for # Richard!).

Updating t for all 3 value columns left as an exercise! :) --DD

PS: Dan, why can't we use FILTER for non-aggregate Window functions?

Error: FILTER clause may only be used with aggregate window functions

I was trying to use lag(), using FILTER to ignore the NULLs, to reach the first preceding value that is NOT NULL.
It seemed logical to me to use filtering, to make rows I didn't want disappear from the window.
What is wrong with that thinking?

(8) By anonymous on 2021-12-13 18:07:50 in reply to 3 [link] [source]

with alltimes(time) as
    (select time from t1
        union select time from t2
        union select time from t3
        order by time)                 -- strange things happen to the query plan if you remove this order by
select time,
    (select value from t1
        where time<=alltimes.time
        order by time desc) as value1,
    (select value from t2
        where time<=alltimes.time
        order by time desc) as value2,
    (select value from t3
        where time<=alltimes.time
        order by time desc) as value3
    from alltimes;