How can I fill a NULL with previous Data ?
(1) By Questuk on 2020-10-09 17:32:04 [link] [source]
New to SQlite, I have a SQlite single database with 2 data columns. The data comes in at various time and so has nulls.
I wish to read the 2 columns IP_24_Temperature and IP_24_Humidity I do not want any Nulls as I want to make a graph from this data.
What are the commands to do the above ?
IP_24_Temperature IP_24_Humidity 16.5 Null Null Null Null Null 16.8 58 Null Null Null Null Null Null Null 60 Null Null 16.5 61
What I require is ...
IP_24_Temperature IP_24_Humidity 16.5 0 16.5 0 16.5 0 16.8 58 16.8 58 16.8 58 16.8 58 16.8 60 16.8 60 16.5 61
(2) By David Raymond (dvdraymond) on 2020-10-09 19:18:29 in reply to 1 [link] [source]
You are of course going to need an ordering. Whether it's a datetime, id, one column, more, etc. Otherwise this doesn't make sense. For the sake of my example here I'm going to use a single column unique value ordering. We can adjust the answer when you let us know how you have it ordered. This might not be the best, but it seems to work. Of course, you could always just do this in your controlling application rather than figuring out how to get the query engine to do it. create table weather_table (ordering_column integer primary key, IP_24_Temperature real, IP_24_Humidity real); insert into weather_table values (1, 16.5, null), (2, null, null), (3, null, null), (4, 16.8, 58), (5, null, null), (6, null, null), (7, null, null), (8, null, 60), (9, null, null), (10, 16.5, 61); with recursive foo (ordering_column, IP_24_Temperature, IP_24_Humidity) as ( select min(ordering_column), coalesce(IP_24_Temperature, 0), --<value you want if the first temp is null> coalesce(IP_24_Humidity, 0) --<value you want if the first humidity is null> from weather_table union all select weather_table.ordering_column, coalesce(weather_table.IP_24_Temperature, foo.IP_24_Temperature), coalesce(weather_table.IP_24_Humidity, foo.IP_24_Humidity) from foo inner join weather_table on weather_table.ordering_column = ( select ordering_column from weather_table where ordering_column > foo.ordering_column order by weather_table.ordering_column limit 1 ) ) select * from foo order by ordering_column;
(3) By Keith Medcalf (kmedcalf) on 2020-10-09 20:22:04 in reply to 2 [link] [source]
Or, using the same table and data, the correlated form:
select ordering_column, coalesce(ip_24_temperature, ( select ip_24_temperature from weather_table where ordering_column < o.ordering_column and ip_24_temperature is not null order by ordering_column desc limit 1 ), 0 ) as ip_24_temperature, coalesce(ip_24_humidity, ( select ip_24_humidity from weather_table where ordering_column < o.ordering_column and ip_24_humidity is not null order by ordering_column desc limit 1 ), 0 ) as ip_24_humidity from weather_table as o order by ordering_column ;
For swiftest performance (at the expense, as always, of disk space), add the following indexes:
create index idx_weather_temperature on weather_table (ordering_column, ip_24_temperature) where ip_24_temperature is not null; create index idx_weather_humidity on weather_table (ordering_column, ip_24_humidity) where ip_24_humidity is not null;
(4) By Questuk on 2020-10-10 07:42:19 in reply to 3 [source]
Hi, Thanks for your help so far, here is format of my database, the name I have given the database is ... DATA I can see there is a lot to learn, but I am always ready to learn and understand. I am not sure how to write to both of you on this forum, but thanks to you both. Is there anyway to upload my actual small database file on here ? ID Unix_Time Day Date Time IP_24_Temperature IP_24_Humidity -- --------- --- ---- ---- ----------------- -------------- 1 1602091505 Wednesday 07 10 20 18:25 NULL 55 2 1602091545 Wednesday 07 10 20 18:25 15.6 NULL 3 1602091855 Wednesday 07 10 20 18:30 15.7 NULL 4 1602091885 Wednesday 07 10 20 18:31 16.8 NULL 5 1602091929 Wednesday 07 10 20 18:32 19 NULL 6 1602092105 Wednesday 07 10 20 18:35 NULL 54.5 7 1602092155 Wednesday 07 10 20 18:35 15.9 NULL 8 1602092405 Wednesday 07 10 20 18:40 NULL 54.4 9 1602092455 Wednesday 07 10 20 18:40 16 NULL
(5) By Keith Medcalf (kmedcalf) on 2020-10-10 09:28:41 in reply to 4 [link] [source]
You need to post the constraints and the CREATE TABLE/CREATE INDEX statements that you are using and what constitutes your unique ordering key (presumably Unix_Time).
(6) By Keith Medcalf (kmedcalf) on 2020-10-10 09:50:50 in reply to 4 [link] [source]
Note that you also have the choice to fix your data once and then create a trigger so that NULL values are replaced automatically at INSERT time rather than at retrieval time.
That course of action would probably be more efficient since you only insert a new row once and infrequently. If you only ever retrieve the data once, then the cost is equal but favours fixing the data at insert time the more times you intend to perform retrievals.
(7) By luuk on 2020-10-10 10:40:10 in reply to 6 [link] [source]
But that would, as winter is coming, make it hard (or impossible) to see then difference between 0 degrees and a 'not registered temerature'.
(8) By Keith Medcalf (kmedcalf) on 2020-10-10 12:28:52 in reply to 7 [link] [source]
That is only a problem if you set a value to 0 when there is no previous value. However, you could define the table and some triggers so that it is impossible for the contents of the table to be invalid:
create table data ( Unix_Time integer primary key, Day text not null, Date text not null, Time text not null, ip_24_temperature real, ip_24_humidity real ) without rowid; create trigger insert_data_validity before insert on data begin select raise(ABORT, 'Cannot insert invalid data') where (new.ip_24_temperature is null and new.ip_24_humidity is null) or new.Unix_Time <= ( select max(Unix_Time) from data ); end; create trigger update_data_validity_temperature before update of ip_24_temperature on data when old.ip_24_temperature is not null begin select raise(ABORT, 'Cannot update data.ip_24_temperature'); end; create trigger update_data_validity_humidity before update of ip_24_humidity on data when old.ip_24_humidity is not null begin select raise(ABORT, 'Cannot update data.ip_24_humidity'); end; create trigger update_data_validity_unixtime before update of unix_time on data begin select raise(ABORT, 'Cannot update data.unix_time'); end; create trigger insert_data_null_temperature after insert on data when new.ip_24_temperature is null begin update data set ip_24_temperature = ( select ip_24_temperature from data where Unix_Time < new.Unix_Time and ip_24_temperature is not null order by Unix_Time desc limit 1 ) where Unix_Time = new.Unix_Time; end; create trigger insert_data_null_humidity after insert on data when new.ip_24_humidity is null begin update data set ip_24_humidity = ( select ip_24_humidity from data where Unix_Time < new.Unix_Time and ip_24_humidity is not null order by Unix_Time desc limit 1 ) where Unix_Time = new.Unix_Time; end;