Sqlite3: Need to Cartesian On date

Question

I have a table which is a list of games that have been played in a sqlite3 database. The field "datetime" is the a datetime of when game ended. The field "duration" is the number of seconds the game lasted. I want to know what percent of the past 24 hours had at least 5 games running simutaniously. I figured out to tell how many games running at a given time are:

select count(*)
from games
where strftime('%s',datetime)+0 >= 1257173442 and
      strftime('%s',datetime)-duration <= 1257173442

If I had a table that was simply a list of every second (or every 30 seconds or something) I could do an intentional cartisian product like this:

select count(*)
from (
  select count(*) as concurrent, d.second
  from games g, date d
  where strftime('%s',datetime)+0 >= d.second and
        strftime('%s',datetime)-duration <= d.second and
        d.second >= strftime('%s','now') - 24*60*60 and
        d.second <= strftime('%s','now')
  group by d.second) x
where concurrent >=5

Is there a way to create this date table on the fly? Or that I can get a similar effect to this without having to actually create a new table that is simply a list of all the seconds this week?

Thanks

Answer 1

First, I can't think of a way to approach your problem by creating a table on the fly or without the aid of an extra table. Sorry.

My suggestion is for you to rely on a static Numbers table.

Create a fixed table with the format:

CREATE TABLE Numbers (
    number INTEGER PRIMARY KEY
);

Populate it with the number of seconds in 24h (24*60*60 = 84600). I would use any scripting language to do that using the insert statement:

insert into numbers default values;

Now the Numbers table has the numbers 1 through 84600. Your query will them be modified to be:

select count(*)
  from (
        select count(*) as concurrent, strftime('%s','now') - 84601 + n.number second
          from games g, numbers n
         where strftime('%s',datetime)+0 >= strftime('%s','now') - 84601 + n.number and
               strftime('%s',datetime)-duration <= strftime('%s','now') - 84601 + n.number
         group by second) x
 where concurrent >=5

Without a procedural language in the mix, that is the best you'll be able to do, I think.

Answer 2

Great question!

Here's a query that I think gives you what you want without using a separate table. Note this is untested (so probably contains errors) and I've assumed datetime is an int column with # of seconds to avoid a ton of strftime's.

select sum(concurrent_period) from (
  select min(end_table.datetime - begin_table.begin_time) as concurrent_period
  from (
    select g1.datetime, g1.num_end, count(*) as concurrent
    from (
      select datetime, count(*) as num_end
             from games group by datetime
    ) g1, games g2
    where g2.datetime >= g1.datetime and
          g2.datetime-g2.duration < g1.datetime and
          g1.datetime >= strftime('%s','now') - 24*60*60 and
          g1.datetime <= strftime('%s','now')+0
  ) end_table, (
    select g3.begin_time, g1.num_begin, count(*) as concurrent
    from (
      select datetime-duration as begin_time,
             count(*) as num_begin
             from games group by datetime-duration
    ) g3, games g4
    where g4.datetime >= g3.begin_time and
          g4.datetime-g4.duration < g3.begin_time and
          g3.begin_time >= strftime('%s','now') - 24*60*60 and
          g3.begin_time >= strftime('%s','now')+0
  ) begin_table
  where end_table.datetime > begin_table.begin_time
        and begin_table.concurrent < 5
        and begin_table.concurrent+begin_table.num_begin >= 5
        and end_table.concurrent >= 5
        and end_table.concurrent-end_table.num_end < 5
  group by begin_table.begin_time
) aah

The basic idea is to make two tables: one with the # of concurrent games at the begin time of each game, and one with the # of concurrent games at the end time. Then join the tables together and only take rows at "critical points" where # of concurrent games crosses 5. For each critical begin time, take the critical end time that happened soonest and that hopefully gives all the periods where at least 5 games were running concurrently.

Hope that's not too convoluted to be helpful!