On Jul 16, 2012, at 10:37 PM, Luis Mochan wrote:
> Suppose I have a time series in a table such as
>
> where ... denotes some value. There are some missing values from the
> table, such as those corresponding to time=3,4,6 in my example. I need
> to insert the missing rows using 0 for the corresponding value. Is
> there a simple way to detect and insert missing rows like these for a
> large within sqlite, i.e., without writing a C/perl/etc. program?
Hmmm… analytics would be great for that… sadly SQLite doesn't provide any… oh,
well…
Here is a rather lame way to fill the gaps, by intersecting the existing set
with a set of possible new time values:
insert
into foo
(
time,
value
)
select DataSet.time,
0 as value
from (
select max( time - 1, 0 ) as time
from foo
except
select time
from foo
)
as DataSet
Repeat until no gaps are left…
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
