~~~
create table T1 (
id integer primary key,
ts real not null,
a char not null
);
insert into T1(id, ts, a)
values (1, 1.0, 'A'), (2, 1.2, 'B'), (3, 1.8, 'C'), (4, 2.5, 'D')
, (5, 3.6, 'E'), (6, 4.5, 'F'), (7, 4.6, 'G'), (8, 9.8, 'H');
create table T2 (
id integer primary key,
ts real not null,
b integer not null
);
insert into T2(id, ts, b)
values (10, 2.0, 50), (20, 2.5, 100), (30, 9.5, 200), (40, 4.5, 300);
drop view if exists NearestMatch;
create view if not exists NearestMatch as
with timeFrames as (
select *, lag(T1.ts) over win as tsLag
from T1
WINDOW win AS (order by T1.ts)
)
select TF.id,TF.a,TF.tsLag,TF.ts, T2.*
from timeFrames TF
left outer join T2 on T2.ts > TF.tsLag and T2.ts <= TF.ts;
select * from NearestMatch;
sqlite> .mode box
sqlite> select * from NearestMatch;
┌────┬───┬───────┬─────┬──────┬──────┬─────┐
│ id │ a │ tsLag │ ts │ id:1 │ ts:1 │ b │
├────┼───┼───────┼─────┼──────┼──────┼─────┤
│ 1 │ A │ │ 1.0 │ │ │ │
│ 2 │ B │ 1.0 │ 1.2 │ │ │ │
│ 3 │ C │ 1.2 │ 1.8 │ │ │ │
│ 4 │ D │ 1.8 │ 2.5 │ 10 │ 2.0 │ 50 │
│ 4 │ D │ 1.8 │ 2.5 │ 20 │ 2.5 │ 100 │
│ 5 │ E │ 2.5 │ 3.6 │ │ │ │
│ 6 │ F │ 3.6 │ 4.5 │ 40 │ 4.5 │ 300 │
│ 7 │ G │ 4.5 │ 4.6 │ │ │ │
│ 8 │ H │ 4.6 │ 9.8 │ 30 │ 9.5 │ 200 │
└────┴───┴───────┴─────┴──────┴──────┴─────┘
sqlite>
~~~
As you can see the left table may show repetitions but has no "holes"
while the right side may show "holes" but matches every time frame from the left table.
I did not try with huge data size but I guess this view is not the slowest.