Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Kevin rather beat me to the punchline there (+1), but I'll post this variation as it differs at least a little.</p> <p>The key ideas are </p> <ul> <li>Map the data in to a stream of events with attributes time and 'polarity' (=start or end of game)</li> <li>Keep a running total of how many games are open at the time of each event (this is done by forming a self-join on the event stream)</li> <li>Find the event times where the number of games (as Kevin says) transitions up to 5, or down to 4</li> <li>A little trick: add up all the down-to-4 times and take away the up-to-5s - the order is not important</li> <li>The result is the number of seconds spent with 5 or more games open</li> </ul> <p>I don't have sqllite, so I've been testing with MySQL, and I've not bothered to limit the time window to preserve some sanity. Shouldn't be difficult to revise.</p> <p>Also, and more importantly, I've not considered what to do if games are open at the beginning or end of the period!</p> <p>Something tells me there's a big simplification to be had here, but I've not spotted it yet.</p> <pre><code>SELECT SUM( event_time ) FROM ( SELECT -ga.event_type * ga.event_time AS event_time, SUM( ga.event_type * gb.event_type ) event_type FROM ( SELECT UNIX_TIMESTAMP( g1.endtime - g1.duration ) AS event_time , 1 event_type FROM games g1 UNION SELECT UNIX_TIMESTAMP( g1.endtime ) , -1 FROM games g1 ) AS ga, ( SELECT UNIX_TIMESTAMP( g1.endtime - g1.duration ) AS event_time , 1 event_type FROM games g1 UNION SELECT UNIX_TIMESTAMP( g1.endtime ) , -1 FROM games g1 ) AS gb WHERE ga.event_time &gt;= gb.event_time GROUP BY ga.event_time HAVING SUM( ga.event_type * gb.event_type ) IN ( -4, 5 ) ) AS gr </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POSqlite3: Need to Cartesian On date
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USmartin clayton
    1. USmartin clayton
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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