SQL查询：如何确定“在N小时内查看”如果给定两个DateTime时间戳？

Question

我正在编写一个基于SQLite数据库的统计应用程序。有一张记录用户登入和登出时间（SessionStart，SessionEnd DateTimes）的表格。

我需要查询显示用户在哪些时段已经登陆过，并以折线图的方式展示。例如，在12:00至1:00 AM之间，有60个用户（随时）登陆了，在1:00至2:00 AM之间，有54个用户登陆了等等。

我也想能够运行一个总计数功能，这就是为什么我不能将记录带入.NET并通过迭代进行处理的原因。

我想出了一个相对简单的方法，即每天的每个小时都做一个子查询，但是这种方法被证明速度非常慢。 我需要能够在瞬间计算出几十万条记录的结果。

  SELECT
        case
        when (strftime('%s',datetime(date(sessionstart), '+0 hours')) > strftime('%s',sessionstart)
        AND strftime('%s',datetime(date(sessionstart), '+0 hours')) < strftime('%s',sessionend))
        OR (strftime('%s',datetime(date(sessionstart), '+1 hours')) > strftime('%s',sessionstart)
        AND strftime('%s',datetime(date(sessionstart), '+1 hours')) < strftime('%s',sessionend))
        OR (strftime('%s',datetime(date(sessionstart), '+0 hours')) < strftime('%s',sessionstart)
        AND strftime('%s',datetime(date(sessionstart), '+1 hours')) > strftime('%s',sessionend))
        then 1 else 0 end as hour_zero,
... hour_one, 
... hour_two, 
........ hour_twentythree
FROM UserSession

我想知道有什么更好的方法来确定两个日期时间是否在某个小时内出现过（最好的情况是，如果它在多个日期中被记录，它已经穿过了几次小时，但这不是必要的）？

我唯一想到的另一个想法是创建一个针对此特定目的的“小时”表，并在运行时累加用户出现的小时数，但我觉得这比先前的SQL更像是一种hack。

任何帮助都将不胜感激！

- efess

---

你是否完全不关心日期部分？你需要整个历史记录中每小时的聚合，某个日期范围，还是每天单独的聚合？ - van

唯一的问题是去掉日期部分，有些用户可能登录超过一天，所以最好的情况是能够在25小时会话期间考虑到那个额外的小时。我将为每个特定用户或表上的另一个标识符聚合每个小时。这可能包括几十万个会话。 - efess

Answer 1

我在Sybase（T-SQL方言）上尝试了一些操作，并得出了这个查询。

SELECT
    StartHour AS Hour, COUNT(*) AS SessionCount
FROM
    (SELECT
        CONVERT(DATETIME, '2001-01-01 ' + Hour + ':00:00') as StartHour,
        DATEADD(HH, 1, CONVERT(DATETIME, '2001-01-01 ' + Hour + ':00:00')) as EndHour
    FROM
        (SELECT '00' AS Hour UNION ALL SELECT '01' AS Hour UNION ALL
        SELECT '02' AS Hour UNION ALL SELECT '03' AS Hour UNION ALL
        SELECT '04' AS Hour UNION ALL SELECT '05' AS Hour UNION ALL
        SELECT '06' AS Hour UNION ALL SELECT '07' AS Hour UNION ALL
        SELECT '08' AS Hour UNION ALL SELECT '09' AS Hour UNION ALL
        SELECT '10' AS Hour UNION ALL SELECT '11' AS Hour UNION ALL
        SELECT '12' AS Hour UNION ALL SELECT '13' AS Hour UNION ALL
        SELECT '14' AS Hour UNION ALL SELECT '15' AS Hour UNION ALL
        SELECT '16' AS Hour UNION ALL SELECT '17' AS Hour UNION ALL
        SELECT '18' AS Hour UNION ALL SELECT '19' AS Hour UNION ALL
        SELECT '20' AS Hour UNION ALL SELECT '21' AS Hour UNION ALL
        SELECT '22' AS Hour UNION ALL SELECT '23' AS Hour) AS Hours
    ) AS T1,
    UserSession AS T2
WHERE
    -- Logged on during, logged off during
    (T2.SessionStart >= T1.StartHour AND T2.SessionEnd < T1.EndHour)
    -- Logged on before, logged off during
    OR (T2.SessionStart < T1.StartHour AND T2.SessionEnd >= StartHour AND T2.SessionEnd < T1.EndHour)
    -- Logged on during, logged off after
    OR (T2.SessionStart >= T1.StartHour AND T2.SessionStart < T1.EndHour AND T2.SessionEnd >= T1.EndHour)
    -- Logged on before, logged off after
    OR (T2.SessionStart < T1.StartHour AND T2.SessionEnd >= T1.EndHour)
GROUP BY
    T1.StartHour
ORDER BY
    T1.StartHour

需要输入的是以YYYY-MM-DD形式聚合的日期。请注意，它不会返回计数为零的小时的任何结果。

- Martin

Answer 2

也许你可以再创建一张表，当记录用户退出登录时间时，填充记录以确定用户登录时长的小时数？

例如：

create table hourlyUseLog (
    userID text not null,
    date float, // julian Day
    hour0 integer default 0,
    hour1 integer default 0,

etc...

    hour23 integer default 0,
);

如果您有像这样的结构，您可以在任何给定的时间/日期非常快速地查询谁已经登录（或有多少用户已经登录）。

SQLite 还支持位字段和位运算，因此您也可以用单个整数表示一天中的所有小时，并根据用户活动翻转位。这将允许您使用位掩码进行更快的查询，并提供一种将小时转换为儒略日（仅限时间部分）表示和/或使用位计数例程计算在系统中消耗的小时数的机制。

此外，如果您需要实时活动报告并且您的系统允许您拥有一个集中的表示形式来记录谁已经登录，那么您可以启动一个每小时批处理过程来更新 hourlyUseLog 记录。

- xyzzycoder

---

我认为这是最好的方法。马丁的答案比我的更简洁，但它执行与我的相同的And/Or操作，并且同样缓慢。我想我会选择这个方案，一个按小时记录或汇总表，我认为这是最好的解决方案。谢谢大家。 - efess

Answer 3

我会采用你的“hack”想法，但我并不认为它是一个hack - 在一小时结束后，该值将永远不会改变，所以为什么不计算一次并完成呢？Rollup表对于此非常有效，并且无论您跟踪多少用户，都将产生一致的查询时间。

您可以每小时计算这些，或者在登录/注销事件中递增每小时的计数器并避免定期任务。

- jasondoucette