如何根据事件的日期，时间和持续时间检查SQL表中的平均并发事件？

Question

我有一组呼叫详细记录，从这些记录中，我可以确定每个系统每小时的平均并发活动呼叫（精确到一分钟）。如果查询从晚上7点到晚上8点，则应该看到该小时（对于每个系统）在该小时内的平均并发呼叫数（每分钟的并发呼叫数平均值）。

因此，我需要一种方法来检查7：00-7：01、7：01-7：02等的活动呼叫计数，然后对这些数字求平均值。如果呼叫的时间和持续时间在当前要检查的分钟内，则认为该呼叫处于活动状态。

更困难的是它需要跨越SQL 7.0和SQL
2000（7.0中的某些功能在7.0中不可用，例如GetUTCTime（）），如果我能使2000正常工作，我会很高兴的。

我可以采取什么方法来解决这个问题？

我考虑过要在检查的小时中循环浏览分钟（60），并添加介于该分钟之间的呼叫计数，然后以某种方式交叉引用持续时间，以确保从7:00
pm开始且持续时间为300秒显示在7:04处于活动状态，但我无法想象如何解决该问题。我试图找出一种方法，可以根据特定的分钟权重对每个呼叫进行加权，从而告诉我该分钟内该呼叫是否处于活动状态，但无法提出有效的解决方案。

这里的数据类型与我要查询的数据类型相同。我对架构没有任何控制权（除了可能转换数据并将其插入具有更适当数据类型的另一个表中之外）。我提供了一些示例数据，这些数据我知道它们具有并发活动调用。

CREATE TABLE Records(
  seconds char(10),
  time char(4),
  date char(8),
  dur int,
  system int,
  port int,
)

--seconds is an stime value. It's the difference of seconds from UTC 1/1/1970 00:00:00 to the current UTC time, we use it as an identifier (like epoch).
--time is the time the call was made.
--date is the day the call was made.
--dur is the duration of the call in seconds.
--system is the system number.
--port is the port on the system (not particularly relevant for this question).

INSERT INTO Records(seconds, time, date, dur, system, port) VALUES('1239924228','1923','20090416',105,2,2)
INSERT INTO Records(seconds, time, date, dur, system, port) VALUES('1239923455','1910','20090416',884,1,97)
INSERT INTO Records(seconds, time, date, dur, system, port) VALUES('1239924221','1923','20090416',116,2,15)
INSERT INTO Records(seconds, time, date, dur, system, port) VALUES('1239924259','1924','20090416',90,1,102)
INSERT INTO Records(seconds, time, date, dur, system, port) VALUES('1239923458','1910','20090416',891,2,1)
INSERT INTO Records(seconds, time, date, dur, system, port) VALUES('1239924255','1924','20090416',99,2,42)
INSERT INTO Records(seconds, time, date, dur, system, port) VALUES('1239924336','1925','20090416',20,2,58)
INSERT INTO Records(seconds, time, date, dur, system, port) VALUES('1239924293','1924','20090416',64,2,41)
INSERT INTO Records(seconds, time, date, dur, system, port) VALUES('1239923472','1911','20090416',888,2,27)
INSERT INTO Records(seconds, time, date, dur, system, port) VALUES('1239924347','1925','20090416',25,1,100)
INSERT INTO Records(seconds, time, date, dur, system, port) VALUES('1239924301','1925','20090416',77,2,55)
INSERT INTO Records(seconds, time, date, dur, system, port) VALUES('1239924332','1925','20090416',52,2,43)
INSERT INTO Records(seconds, time, date, dur, system, port) VALUES('1239924240','1924','20090416',151,1,17)
INSERT INTO Records(seconds, time, date, dur, system, port) VALUES('1239924313','1925','20090416',96,2,62)
INSERT INTO Records(seconds, time, date, dur, system, port) VALUES('1239924094','1921','20090416',315,2,16)
INSERT INTO Records(seconds, time, date, dur, system, port) VALUES('1239923643','1914','20090416',788,2,34)
INSERT INTO Records(seconds, time, date, dur, system, port) VALUES('1239924447','1927','20090416',6,2,27)
INSERT INTO Records(seconds, time, date, dur, system, port) VALUES('1239924342','1925','20090416',119,2,15)
INSERT INTO Records(seconds, time, date, dur, system, port) VALUES('1239924397','1926','20090416',76,2,41)
INSERT INTO Records(seconds, time, date, dur, system, port) VALUES('1239924457','1927','20090416',23,2,27)

Answer 1

我认为MarkusQ可以解决问题，但是让我开发一种可能会更易于使用的替代方法。我将使用惯用的方法将其开发为视图中的一系列简单转换，这类似于过程语言中的功能分解。

首先，让我们将所有内容都放在同一单元中。回想record的专栏s是因为秒 时代
，午夜1月1日1970年我们可以发现，因为呼叫的一天午夜的秒数，该呼叫发生后，通过只是走在一天的秒模量数：s % (60 * 60 * 24)。

select *, 
s % (60 * 60 * 24) as start_secs_from_midnight,
s % (60 * 60 * 24) + dur - 1 as end_secs_from_midnight,
;

我们减去一个是s + dur因为从12:00:00开始的一秒钟呼叫也于12:00:00结束。

通过将这些结果除以60或仅乘以floor( s / 60 ) % (60 * 24)：，我们可以找到自午夜以来的分钟数：

create view record_mins_from_midnight as
select *, 
floor( s / 60 ) % (60 * 24) as start_mins_fm,
floor( ( s + dur - 1) / 60 ) % (60 * 24) as end_mins_fm 
from record
;

现在我们创建一个分钟表。我们需要1440个，编号从0到1439。在不支持任意序列的数据库中，我 创建了一个人工范围或序列， 如下所示：

  create table artificial_range ( 
   id int not null primary key auto_increment, idz int) ;
  insert into artificial_range(idz) values (0);
  -- repeat next line to double rows
  insert into artificial_range(idz) select idz from artificial_range;

因此要创建一个minute表：

  create view minute as 
   select id - 1 as active_minute 
   from artificial_range 
   where id <= 1440
   ;

现在我们minute进入记录视图

create view record_active_minutes as
select * from minutes a 
join record_mins_from_midnight b
on (a.active_minute >= b.start_mins_fm 
and a.active_minute <= b.end_mins_fm 
 ;

这只是跨乘积/乘以记录行，因此通话进行的每一分钟都有一个记录行。

请注意，我这样做是通过将active定义为“呼叫在一分钟内发生的（一部分）”。也就是说，根据此定义，从12:00:59开始并在12:01:01结束的两秒钟呼叫发生在两个不同的分钟内，但是从12:00:58开始并在12：结束的两秒钟呼叫一分钟内发生00:59。

我这样做是因为您指定了“因此，我需要一种方法来检查7：00-7：01、7：01-7：02的活动呼叫计数”。如果您希望仅在超过一分钟的时间内进行持续超过60秒的通话，则需要调整联接。

现在，如果要查找等于或大于分钟粒度的任何粒度的活动记录数，则只需对最后一个视图进行分组。要查找每小时的平均通话次数，我们用60除以将分钟数转换为小时数：

 select floor( active_minute / 60 ) as hour, 
 count(*) / 60 as avg_concurent_calls_per_minute_for_hour
 from record_active_minutes
 group by floor( active_minute / 60 ) ;

请注意，这是一整天 所有呼叫 每小时的平均值；如果我们希望将其限制为特定的一天或几天的范围，则可以添加一个where子句。

但是，等等，还有更多！

如果我们创建一个record_active_minutes左外部联接的版本，则可以得到一个报告，该报告显示一天中所有小时的平均值：

 create view record_active_minutes_all as
 select * 
 from 
 minutes a 
 left outer join record_mins_from_midnight b
   on (a.active_minute >= b.start_mins_fm 
       and a.active_minute <= b.end_mins_fm) 
 ;

然后，我们再次进行选择，但针对新视图：

 select floor( active_minute / 60 ) as hour, 
 count(*) / 60 as avg_concurent_calls_per_min
 from record_active_minutes_all
 group by floor( active_minute / 60 ) ;


+------+------------------------------+
| hour | avg_concurrent_calls_per_min |
+------+------------------------------+
|    0 |                       0.0000 |
|    1 |                       0.0000 |
|    2 |                       0.0000 |
|    3 |                       0.0000 |
   etc....

我们也可以使用where对此进行索引。不幸的是，联接意味着record在特定时间段内不存在任何调用的基础表将具有空值，例如，

 select floor( active_minute / 60 ) as hour, 
 count(*) / 60 as avg_concurent_calls_per_min
 from record_active_minutes_all
 where month(date) = 1 and year(date) = 2008 
 group by floor( active_minute / 60 ) ;

在没有通话的时间段内，将不带回任何行。如果我们仍然希望显示所有小时的“类似于报告”的视图，请确保我们也包括没有记录的那些小时：

 select floor( active_minute / 60 ) as hour, 
 count(*) / 60 as avg_concurent_calls_per_minute_for_hour
 from record_active_minutes_all
 where (month(date) = 1 and year(date) = 2008) 
 or date is null 
 group by floor( active_minute / 60 ) ;

请注意，在最后两个示例中，我使用的是SQL日期（可以对其应用函数month和功能year），而不是记录表中的char（4）日期。

这就引出了另一点：记录表中的日期和时间都是多余的并且是非规范化的，因为每个日期和时间都可以从列s导出。将它们保留在表中可能会导致行不一致，其中date(s) <> date或time(s) <> time。我更喜欢这样：

   create table record ( id int not null primary key, s, duration) ;

   create view record_date as 
   select *, dateadd( ss, s, '1970-01-01') as call_date
   from record
  ;

在dateadd函数中，ss是枚举类型，它告诉函数增加秒；s是记录中的列。