SQL问题:在任何给定时间内发生次数大于N的计数

Question

我正在查看登录日志(在Netezza中)，并试图找到自12月1日起在任何1小时时间内(任何连续60分钟的时间，而不是严格的时钟小时)拥有超过一定数量登录次数的用户。我看过以下文章，但大多数似乎都是在特定的时间范围内搜索，而不是任何给定的时间段。谢谢。https://dba.stackexchange.com/questions/137660/counting-number-of-occurences-in-a-time-period https://dba.stackexchange.com/questions/67881/calculating-the-maximum-seen-so-far-for-each-point-in-time Count records per hour within a time span

Answer 1

您可以使用解析函数lag回顾按顺序排列的时间戳，以查看前面19个条目的记录是否在一小时内不同：

with cte as (
    select   user_id,
             login_time,
             lag(login_time, 19) over (partition by user_id order by login_time) as lag_time
    from     userlog
    order by user_id,
             login_time
)
select   user_id,
         min(login_time) as login_time
from     cte
where    extract(epoch from (login_time - lag_time)) < 3600
group by user_id

输出将显示匹配的用户，当他们在一小时内记录第二十次时，第一次出现。

Answer 2

我认为您可能会这样做(为了简单起见，我将使用一个登录表，将user、datetime作为单一列)：

with connections as (
  select ua.user
       , ua.datetime
  from user_logons  ua
  where ua.datetime >= timestamp'2018-12-01 00:00:00'
)
  select ua.user
       , ua.datetime
       , (select count(*) 
          from connections  ut 
          where ut.user = ua.user 
            and ut.datetime between ua.datetime and (ua.datetime + 1 hour)
         ) as consecutive_logons
       from connections ua

1. 由您来完成列(用户、日期时间)。
2. 应该由您来查找dateadd工具(ua.datetime +1小时不能工作)；这或多或少取决于DB实现，例如，它是DATE_ADD in mySQL (add.asp)
3. 由于子查询(select count(*) ...)，整个查询不会是最快的，因为它是一个相对子查询--需要对每一行进行重新计算。
4. with只是计算user_logons的一个子集，以最小化它的成本。这可能没有用，但是这将减少查询的复杂性。

使用存储函数或语言驱动可能会有更好的性能(例如: java、php、.)函数。