Sqlite3：需要按日期进行笛卡尔积

Question

Sqlite3：需要按日期进行笛卡尔积

5

我有一张表格，列出了在sqlite3数据库中玩过的游戏列表。"datetime"字段是游戏结束的日期时间字段。"duration"字段是游戏持续时间的秒数。我想知道过去24小时中至少有5个游戏同时运行的百分比是多少。我已经找出了如何确定某个时刻有多少正在运行的游戏：

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

如果我有一个简单的列表，列出每秒（或每30秒等）一次的时间，我可以像这样有意识地做一个笛卡尔积：

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

有没有一种方法可以动态地创建这个日期表？或者我可以得到一个类似于这样的效果，而不必实际创建一个新表，只需创建本周所有秒数的列表即可？

谢谢。

- Dan

4个回答

2

好问题！

这里有一个查询语句，我认为它可以给出你想要的结果，而不需要使用单独的表。请注意，这个查询没有经过测试（可能包含错误），而且我假设datetime是一个整数列，其值为秒数，以避免大量使用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场游戏同时运行的所有时间段。

希望这不会太复杂，以至于无法帮助您！

- J. Kevin Corcoran

2

Kevin已经提出了这个点子(+1)，但我会发表这个变化，因为它至少有一点不同。

关键思想是

将数据映射到具有时间和“极性”属性（=游戏开始或结束）的事件流中
在每个事件发生时保持正在运行的游戏数量的累计总数（通过在事件流上形成自连接来完成此操作）
查找游戏数量（如Kevin所说）从4个转换到5个或从5个转换到4个的事件时间
一个小技巧：将所有降至4的时间相加并减去升至5的时间-顺序不重要
结果是花费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

- martin clayton

0

为什么不将日期截取并只保留时间，如果您每次过滤数据都是针对特定日期的唯一时间。这样，您只需要一个包含1到86400（或更大间隔）数字的表格，您可以创建两列“从”和“到”来定义间隔。我不熟悉SQLite函数，但根据手册，您必须使用strftime函数以此格式：HH:MM:SS。

- Ariel Popovsky

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- cethegeek · Accepted Answer

首先，我无法想到一种在没有额外表格的情况下或通过动态创建表格来解决您的问题的方法。抱歉。

我的建议是依靠一个静态的数字表格。

创建一个固定格式的表格：

CREATE TABLE Numbers (
    number INTEGER PRIMARY KEY
);

将其填充为24小时的秒数（24*60*60 = 84600）。我会使用任何脚本语言，使用插入语句来完成：

insert into numbers default values;

现在数字表格包含了从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

如果没有过程式语言的参与，我认为这就是你所能做到的最好了。