Joining two tables using one to group another

join;sqlite

I have a sqlite database with 2 tables. Table a has a running list of events and the times they happened. Table b has the start and finish of time each event.

How can I find the minimum event from table a for each group defined in table b? Schema is described in my SQLFiddle.

I'm trying to get a result like:

id1  id2  time event
c1   d1   2    5
c1   d2   1.2  10
c1   d1   5    8.2

Best Answer

SELECT a.id1, a.id2, `time`, event
FROM a
LEFT JOIN b ON a.id1 = b.id1 AND a.id2 = b.id2
            AND a.`time` BETWEEN b.stime AND b.etime
Where not exists ( select null from a A1
                          Where a1.id1=a.id1 and a1.id2=a.id2
                          AND a1.`time` BETWEEN b.stime AND b.etime
                          And a1.`time` < a.`time` )

Cfr. http://sqlfiddle.com/#!5/284fb/4

Another way to obtain these results:

with e as (select rowid R, b.* from b)
select e.*,  a.* from (
 select min( a.`time` ) T, e.R  from a,  e
  where a.`time` between e.stime and e.etime
      and a.id1 = e.id1
      and a.id2 = e.id2
  group by e.R
 ) M, a, e
 where e.R = M.R
   and a.id1 = e.id1
   and a.id2 = e.id2
   and a.`time` = M.T;

With your current schema, that contains a serious flaw, there is no way to obtain your result in a timely fashion. With some simple modifications, you can get it in 21 seconds.

Modify your tables as follows:

CREATE TABLE a (
 `time`        REAL       NOT NULL,
  idA number,
  id1      CHAR        NOT NULL,
  id2       CHAR        NOT NULL,
  event    REAL);
CREATE TABLE IF NOT EXISTS "b"(
  id1 TEXT,
  id2 TEXT,
  stime REAL,
  etime REAL,
  idB number);
CREATE UNIQUE INDEX pk_b on b(idB);
CREATE INDEX i_b1 on b(id1,id2);
CREATE INDEX i_a1 on a(idA);

I've given b a primary key. a.idA contains a reference (foreign key) to b. This update took quite some time. Now the following query finishes in 21 seconds.

select b.*, a.event, a.`time`
  from b, a    
  where a.idA = b.idB
    and not exists (select null from a a1
                     where a1.idA = b.idB
                       and a1.`time` < a.`time`)

--EQP-- 0,0,1,SCAN TABLE a
--EQP-- 0,1,0,SEARCH TABLE b USING INDEX pk_b (idB=?)
--EQP-- 0,0,0,EXECUTE CORRELATED SCALAR SUBQUERY 1
--EQP-- 1,0,0,SEARCH TABLE a AS a1 USING INDEX i_a1 (idA=?)
c2|d2|834.0|1395.0|5|49.407297300706|834.0
c1|d2|1750.0|5332.0|1|49.9163756294503|1750.0
c1|d1|2088.0|5330.0|2|49.6802055002045|2088.0
....
c2|d1|9994707.0|9998630.0|5986|49.4294519703533|9994707.0
c2|d2|9998164.0|10000000.0|5988|48.9248418596976|9998164.0
c1|d1|9998324.0|9999377.0|5990|17.4151947643984|9998324.0
c1|d2|9998791.0|10000000.0|5987|48.9222722695804|9998791.0
c2|d1|9999744.0|10000000.0|5989|49.8495713740755|9999744.0
Run Time: real 21.612 user 21.377030 sys 0.207799
sqlite> select count(*) from a;
11313047
sqlite> select count(*) from b;
5990
Related Question