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

Question

我正在从SQLite数据库中编写一个基于统计的应用程序。有一个表记录用户登录和注销的时间(SessionStart，SessionEnd DateTimes)。

我要找的是一个查询，它可以以行图的方式显示用户登录的时间--所以在12:00到1:00之间有60个用户登录(在任何时间点)，在凌晨1:00到2:00之间有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

我想知道有什么更好的方法来确定在一个特定的小时内是否看到了两个DateTimes (最好的情况场景，如果它被登录了好几天，但没有必要的话，它超过了一个小时多少次)？

我唯一的另一个想法是拥有一个特定于此的“小时”表，并只对用户在运行时看到的时间进行统计，但我觉得这比以前的SQL更像是一种黑客。

任何帮助都将不胜感激！

Answer 1

也许您可以有另一个表，在记录注销时间时，填充记录以确定用户登录的时间？

例如

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还支持位字段和位数学，因此您还可以用一个整数表示一天中的所有小时，并根据用户活动的时间翻转位数。这将允许您使用位掩码完成更快的查询，并将提供一种机制来将小时转换为julian (仅时间部分)表示和/或使用位计数例程来计算系统中花费的时数。

此外，如果您需要实时活动报告，并且您的系统允许您对登录的人进行集中表示，您可以触发一个每小时更新hourlyUseLog记录的批处理过程。

Answer 2

在Sybase ( this方言)上玩了一会儿，并提出了这个查询。

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 DD形式聚合的日期。注意，对于计数为零的小时，它不返回任何结果。

Answer 3

我会同意你的“黑客”想法，但我不认为它是一个黑客，真的--在一小时结束后，价值永远不会改变，那么为什么不计算它一次并完成它呢？Rollup表对此非常有效，不管您跟踪了多少用户，都会产生一致的查询时间。

您可以每小时计算这些值，也可以在登录/注销事件时增加每个小时的计数器，并避免预定的任务。