Geek Answers Handbook

Sqlite3: the need for Cartesian

I have a table that is a list of games that were played in the sqlite3 database. The "datetime" field is the date and time when the game ends. The "duration" field is the number of seconds during which the game lasted. I want to know what percentage of the past 24 hours there have been at least 5 games, simulated. I decided to tell you how many games are currently running:

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

If I had a table that was just a list of every second (or every 30 seconds or something else), I could make an intentional product similar to 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

? , , ?

+5
4

!

, , , , , . , (, ), , datetime int , strftime.

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

, : # # . " ", 5. , , , , , 5 .

, , !

+2

-, , " " . .

, Numbers.

:

CREATE TABLE Numbers (
    number INTEGER PRIMARY KEY
);

24h (24 * 60 * 60 = 84600). , insert:

insert into numbers default values;

Numbers 1 84600. :

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

, , , .

+3

(+1), , .

  • "" (= ).
  • , ( )
  • , ( ) 5 4
  • : 4- up-to-5s - .
  • , 5 .

sqllite, MySQL, , . .

, , , , !

- , , .

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 >= gb.event_time
GROUP BY ga.event_time
HAVING SUM( ga.event_type * gb.event_type ) IN ( -4, 5 )
) AS gr
+2

Why don’t you trim the date and save only the time if you filter your data for any date every day. Thus, you need a table with numbers from 1 to 86400 (or less if you make large spaces), you can create two columns “from” and “to” to define the intervals. I am not familiar with SQLite functions, but according to the manual you should use the strftime function in this format: HH: MM: SS.

0
source

More articles:


All Articles