StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I like @Chris Cunningham's answer, but here's another take on it.</p> <p>First, my understanding of your problem statement (correct me if I'm wrong):</p> <blockquote> <p>You want to look at your event log as a sequence, ordered by the time of the event, and partitition it into groups, defining the boundary as being an interval of more than 3 seconds between two adjacent rows in the sequence.</p> </blockquote> <p>I work mostly in SQL Server, so I'm using SQL Server syntax. It shouldn't be too difficult to translate into MySQL SQL.</p> <p>So, first our event log table:</p> <pre><code>-- -- our event log table -- create table dbo.eventLog ( id int not null , dtLogged datetime not null , title varchar(200) not null , primary key nonclustered ( id ) , unique clustered ( dtLogged , id ) , ) </code></pre> <p>Given the above understanding of the problem statement, the following query should give you the upper and lower bounds your groups. It's a simple, nested select statement with 2 <code>group by</code> to collapse things:</p> <ul> <li>The innermost <code>select</code> defines the upper bound of each group. That upper boundary defines a group.</li> <li>The outer <code>select</code> defines the lower bound of each group.</li> </ul> <p>Every row in the table should fall into one of the groups so defined, and any given group may well consist of a single date/time value.</p> <p>[edited: the upper bound is the lowest date/time value where the interval is more than 3 seconds]</p> <pre><code>select dtFrom = min( t.dtFrom ) , dtThru = t.dtThru from ( select dtFrom = t1.dtLogged , dtThru = min( t2.dtLogged ) from dbo.EventLog t1 left join dbo.EventLog t2 on t2.dtLogged >= t1.dtLogged and datediff(second,t1.dtLogged,t2.dtLogged) > 3 group by t1.dtLogged ) t group by t.dtThru </code></pre> <p>You could then pull rows from the event log and tag them with the group to which they belong thus:</p> <pre><code>select * from ( select dtFrom = min( t.dtFrom ) , dtThru = t.dtThru from ( select dtFrom = t1.dtLogged , dtThru = min( t2.dtLogged ) from dbo.EventLog t1 left join dbo.EventLog t2 on t2.dtLogged >= t1.dtLogged and datediff(second,t1.dtLogged,t2.dtLogged) > 3 group by t1.dtLogged ) t group by t.dtThru ) period join dbo.EventLog t on t.dtLogged >= period.dtFrom and t.dtLogged <= coalesce( period.dtThru , t.dtLogged ) order by period.dtFrom , period.dtThru , t.dtLogged </code></pre> <p>Each row is tagged with its group via the <code>dtFrom</code> and <code>dtThru</code> columns returned. You could get fancy and assign an integral row number to each group if you want.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload