SQLite Forum

How can I fill a NULL with previous Data ?
Login

How can I fill a NULL with previous Data ?

(1) By Questuk on 2020-10-09 17:32:04 [link] [source]

HI,

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 ?

Example ...

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

...... etc

Thanks Gaz

(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 [link] [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 [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;