代码之家 › 专栏 › 技术社区 › Kitson

如何在sqlite中生成时间间隔查询?

sqlite sql

Kitson · 技术社区 · 17 年前

我有一个基于事件的表,我想按分钟为正在发生的事件数生成一个查询。

例如,我有一个事件表,如下所示:

CREATE TABLE events (
     session_id  TEXT,
     event       TEXT,
     time_stamp  DATETIME
)

我将其转换为以下类型的表:

CREATE TABLE sessions (
     session_id    TEXT,
     start_ts      DATETIME,
     end_ts        DATETIME,
     duration      INTEGER
);

现在,我想创建一个查询,将会话分组为特定时间内活动会话的计数。在那里我基本上会得到一些东西,比如:

TIME_INTERVAL ACTIVE_SESSIONS
------------- ---------------
18:00         1
18:01         5
18:02         3
18:03         0
18:04         2

3 回复 | 直到 15 年前

Kitson 17 年前

好吧,我想我得到了更多我想要的。它不能解释空的时间间隔,但它足以满足我的需要。

select strftime('%Y-%m-%dT%H:%M:00.000',start_ts) TIME_INTERVAL, 
    (select count(session_id) 
      from sessions s2 
      where strftime('%Y-%m-%dT%H:%M:00.000',s1.start_ts) between s2.start_ts and s2.end_ts) ACTIVE_SESSIONS
   from sessions s1
   group by strftime('%Y-%m-%dT%H:%M:00.000',start_ts);

这将在数据覆盖的时间段内每分钟生成一行,并对已启动(开始)但尚未完成(结束)的会话数进行计数。

vog 17 年前

PostgreSQL允许以下查询。

与您的示例不同,这将返回一天的附加列,并省略未发生任何事件的分钟数(count=0)。

select
    day, hour, minute, count(*)
from
    (values ( 0),( 1),( 2),( 3),( 4),( 5),( 6),( 7),( 8),( 9),
            (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
            (20),(21),(22),(23),(24),(25),(26),(27),(28),(29),
            (30),(31),(32),(33),(34),(35),(36),(37),(38),(39),
            (40),(41),(42),(43),(44),(45),(46),(47),(48),(49),
            (50),(51),(52),(53),(54),(55),(56),(57),(58),(59))
     as minutes (minute),
    (values ( 0),( 1),( 2),( 3),( 4),( 5),( 6),( 7),( 8),( 9),
            (10),(11),(12),(13),(14),(15),(16),(17),(18),(19),
            (20),(21),(22),(23))
     as hours (hour),
    (select distinct cast(start_ts as date) from sessions
     union
     select distinct cast(end_ts as date) from sessions)
     as days (day),
    sessions
where
    (day,hour,minute)
    between (cast(start_ts as date),extract(hour from start_ts),extract(minute from start_ts))
        and (cast(end_ts as date),  extract(hour from end_ts),  extract(minute from end_ts))
group by
    day, hour, minute
order by
    day, hour, minute;

Brett Thomas 15 年前

这不完全是你的问题,但我认为这会有所帮助。你查过sqlite吗? R-Tree module ?这将允许您在开始/停止时间创建虚拟索引:

CREATE VIRTUAL TABLE sessions_index USING rtree (id, start, end);

然后您可以通过以下方式进行搜索:

SELECT * FROM sessions_index WHERE end >= <first minute> AND start <= <last minute>;