CODEWITHSUNDEEP
mysqlsql
Beau Gosse
Beau Gosse

Reputation: 49

SQL- Find time between earliest A event and Nth B event where A event is after N-1th B event

Using SQL, I'm looking for the largest times between the first 'A' event and the first 'B' event, until a 'B' event is found at which point I want to find a new set of 'A'-'B' events. I only have 2 events, 'A' and 'B'. 'A' will fire at least once before each 'B', but can fire an unlimited number of times before 'B'. This can repeat over and over.

Time | Event
1    | A   <-This should be A1
3    | A
6    | A
7    | B   <-This should be B1
9    | A   <-This should be A2
10   | A
12   | B   <-This should be B2
14   | A   <-This should be A3
15   | B   <-This should be B3  
19   | A   <-This A event has no ending B event yet

Time results I'm looking for:

(A1-B1): 6 
(A2-B2): 3
(A3-B3): 1

Then I can find the median and average of these times.

I thought about using a for loop to save A until B is found and then start over, but I'm not sure how to even start that loop. Is that the right idea?

I can't use Min(Time) and then match up row numbers because I would have to throw out the A events at 3,6,and 10 for that to work.

Any help would be greatly appreciated.

Upvotes: 2

Views: 134

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270523

I would recommend the following, assuming the time is unique. For every "A" event, just include the time of the next "B" event. Then, for those times, aggregate:

select (@rn := @rn + 1),
       min(time) as minAtime,
       max(time) as maxAtime,
       (next_btime - min(time)) as diff
from (select t.*,
             (select t2.time
              from table t2
              where t2.event = 'B' and
                    t2.time > t.time
              order by t2.time
             ) as next_Btime
      from table t
      where event = 'A'
     ) t cross join
     (select @rn := 0) vars
group by next_Btime;

Upvotes: 2

Yura Ivanov
Yura Ivanov

Reputation: 242

  1. For every A event you need to find B event after.
  2. For found next B event you need to find earliest corresponding A event.
  3. Profit. You can subtract times and so on...

Here is minimal example:

  select min(aatime) atime, bbtime as btime
  from
  (
    select aa._time as aatime, min(bb._time) as bbtime 
    from events aa left join events bb
      on bb._time>aa._time and bb.event='B'
    where aa.event='A'
    group by aa._time
  ) bmin
  group by bbtime

See SQLFiddle demo

Upvotes: 1

Paul Griffin
Paul Griffin

Reputation: 2436

This is actually very similar to another question I answered earlier today (my local time), but with a bit of a twist. At least I had the other solution fresh in my brain, so this popped out quick:

SELECT 
  Event,
  MIN(startTime) AS startTime,
  endTime,
  endTime - StartTime AS totalTime
FROM  
  (SELECT 
    t1.Time AS startTime,
    t1.Event,
    MIN(t2.Time) AS endTime
  FROM test_table t1
  INNER JOIN test_table t2
    ON t2.Time > t1.Time
    AND t2.Event = 'B'
  WHERE t1.Event = 'A'
  GROUP BY t1.Time, t1.Event) AS eventSpread
GROUP BY Event, endTime

With the sample data you provided, this gives the following result:

Event   startTime   endTime totalTime
A       1           7       6 
A       9           12      3
A       14          15      1

Here's a SQLFiddle in MySQL 5.6

I feel like this could be improved (I don't like the nested GROUP BY clauses), but I am exhausted for today, so I'll leave that as a potential exercise for the reader :]

Upvotes: 1

Related Questions