Как проверить средние параллельные события в таблице SQL на основе даты, времени и продолжительности событий?

Ответ 1

Я думаю, что у MarkusQ есть ответ, но позвольте мне разработать альтернативу, которую вам может показаться проще использовать. Я использую свой обычный метод для разработки этого как ряд простых преобразований в представлениях, аналог функционального разложения на процедурном языке.

Во-первых, пусть все в общих единицах. Напомним, что столбец record s - секунды с эпохи, полночь 1 января 1970 года. Мы можем найти количество секунд с полуночи дня вызова, этот вызов произошел, просто используя s модуль, количество секунд в день: 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 
 ;

Это просто перекрещивает продукты/умножает строки записей, поэтому у нас есть одна строка записей за каждую целую минуту, по которой был активен вызов.

Обратите внимание, что я делаю это, определяя активный как "(часть) вызова, произошедшего в течение минуты". То есть, второй вызов, который начинается в 12:00:59 и заканчивается в 12:01:01 этим определением, происходит в течение двух разных минут, но двухсекундный вызов, который начинается в 12:00:58 и заканчивается на 12: 00:59 происходит в течение одной минуты.

Я сделал это, потому что вы указали "Итак, мне нужен способ проверить количество активных вызовов за 7: 00-7: 01, 7: 01-7: 02". Если вы предпочитаете рассматривать только звонки продолжительностью более шестидесяти секунд, которые должны произойти в течение более одной минуты, вам необходимо настроить соединение.

Теперь, если мы хотим найти количество активных записей для любой гранулярности, равной или большей, чем минутная гранулярность, мы просто группируем это последнее представление. Чтобы найти средние вызовы в час, мы делимся на 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....

Мы также можем индексировать это с помощью где. К сожалению, соединение означает, что у нас будут нулевые значения для базовой таблицы 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) date в вашей таблице записей.

Что вызывает другую точку: как дата, так и время в вашей таблице записей являются излишними и денормализованными, так как каждый может быть получен из вашего столбца 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 - столбец в записи.

Ответ 2

Если я правильно вас понимаю, вы хотите получить счет всех записей, для которых время начала меньше t + 60 секунд, а время начала плюс продолжительность меньше или равно t, для каждого t в (например, t = 7: 00, 7:01, 7:02... и т.д.).

Тогда это просто вопрос усреднения этих показателей.

Но что среднее? Это просто сумма, деленная на количество предметов, не так ли? В этом случае количество элементов всегда будет равно временному диапазону в минутах, а сумма будет равна сумме длительностей-минут, которые попадают в интервал, который вы можете вычислить за один раз сдали данные.

Звук сейчас невозможен? В псевдо SQL:

select sum( 
     ((time+duration rounded up to next minute, capped at end of period)
    - (time rounded down, bottom-capped at start of period) - 1)
     /(1 minute) )
  from Records
  where date is right

Затем просто разделите это на количество минут в интересующий период.

Ответ 3

Как отмечал MarkusQ, ваше определение "concurrent" позволяет вам сократить математику.

• Вызов (A) начинается с "12:00:59" и заканчивается на "12:01:01"
• Звонок (B) начинается с "12:01:59" и заканчивается на "12:02:01"
  = > 1 вызов в интервале "12:00"
  = > 2 вызова в интервале "12:01"
  = > 1 вызов в интервале "12:02"

Средние одновременные вызовы тогда (1 + 2 + 1)/intervalCount

(1 + 2 + 1) можно рассчитать по-разному и быстрее/легко:

• Вызов (A) охватывает 2 разных минутных интервала (12:00 и 12:01).
• Вызов (B) охватывает 2 разных минутных интервала (12:01 и 12:02)
  = > общее количество закрытых минут = 4

Важный факт здесь (и почему я беспокоил ответ после публикации MarkusQ) заключается в том, что продолжительность самого вызова недостаточна для вычисления количества минутных интервалов. В моем примере оба вызова длится всего 2 секунды...

Вам нужна следующая информация:
- "время начала", округленное до минуты
- "время окончания", округленное до минуты
= > пройденные интервалы = количество минут разницы + 1

Чтобы округлить поле "время" до минуты, я бы использовал это...

DATEADD(minute, DATEDIFF(minute, 0, time), 0)

Таким образом, количество закрытых минут одним вызовом будет...

DATEDIFF(
   minute,
   DATEADD(minute, DATEDIFF(minute, 0, time), 0),
   DATEADD(second, dur, time)
) + 1

No need to round the "end time" down.
Using DATEDIFF(minute) gives rounding down anyway.

SUM это значение для диапазона, на который вы смотрите, затем делитесь на количество минут в этом диапазоне, и у вас есть свой ответ.

Если вы ищете только вызовы, которые действительно совместимы, вы не можете использовать такие трюки, но это все еще возможно (я должен был сделать что-то подобное). Но для вашего определения concurrent это должно сделать это...

DECLARE
   @date DATETIME, @start DATETIME, @end DATETIME
SELECT
   @date = '2009 Jan 01', @start = '12:00', @end = '13:00'

SELECT
   system,
   SUM(
       DATEDIFF(
          minute,
          CASE WHEN
             CAST(LEFT(time,2) + ':' + RIGHT(time,2) AS DATETIME) < @start
          THEN
             @start
          ELSE
             CAST(LEFT(time,2) + ':' + RIGHT(time,2) AS DATETIME)
          END,
          CASE WHEN
             DATEADD(second, dur, CAST(LEFT(time,2) + ':' + RIGHT(time,2) AS DATETIME)) > @end
          THEN
             @end
          ELSE
             DATEADD(second, dur, CAST(LEFT(time,2) + ':' + RIGHT(time,2) AS DATETIME))
          END
       ) + 1
   )
   /
   CAST(DATEDIFF(minute, @start, @end) AS FLOAT)
FROM
   records
WHERE
   date = @date
   AND CAST(LEFT(time,2) + ':' + RIGHT(time,2) AS DATETIME) >= @start
   AND DATEADD(second, dur, CAST(LEFT(time,2) + ':' + RIGHT(time,2) AS DATETIME)) < @end
GROUP BY
   system

Это намеренно не включает интервал 13: 00- > 13:01
Только интервалы длительностью 60 "1 минута" 12: 00- > 12:01 - 12: 59- > 13:00

EDIT:

Я только заметил, что ваши времена и даты хранятся как строки, вам нужно будет преобразовать их в DATETIME, чтобы мой код работал.

EDIT2:

Исправлена ​​ошибка. Если звонок начался с "11:59:01" и закончился в "12:00:01", интервал "11:59" не должен учитываться. Операции CASE добавлены для компенсации.

Различные макеты Редактирование

Ответ 4

Мой первый совет: если вы когда-нибудь захотите сказать (при использовании SQL) "я могу создать цикл...", тогда вы должны немедленно начать поиск по набору. Выйдите из процедурного мышления при использовании SQL.

В вашей логике есть еще несколько нечетких частей. Выполняется ли вызов в течение минуты, если у него просто есть какая-либо часть вызова в течение этой минуты? Например, если вызов начинается с 1923 года и длится 62 секунды, считается ли он совпадением со всеми вызовами, начинающимися с 1924 года? Я собираюсь предположить "да" на этом, но вы можете настроить код ниже, если это не так. Это должно быть небольшая настройка.

Для разбивки на минуты я обычно использовал таблицу с временными интервалами - время начала и окончания для каждого из фрагментов, в которых я заинтересован. В вашем случае, так как вы имеете дело с точными минутами, а время начала - через несколько минут (даже если тип данных прикручен), я просто собираюсь использовать один столбец с точной минутой.

Чтобы настроить эту таблицу:

CREATE TABLE dbo.Minutes (
    start_time  INT NOT NULL,
    CONSTRAINT PK_Minutes PRIMARY KEY CLUSTERED (start_time)
)

DECLARE
    @hour   TINYINT,
    @minute TINYINT

SET @hour = 19
SET @minute = 0

WHILE (@hour <= 20)
BEGIN
    INSERT INTO dbo.Minutes (start_time) VALUES (@hour * 100 + @minute)

    SET @minute = @minute + 1
    IF @minute = 60
    BEGIN
        SET @minute = 0
        SET @hour = @hour + 1
    END
END

Теперь мы можем выбрать средние значения и т.д.

SELECT
    M.start_time,
    COUNT(R.seconds)
FROM
    dbo.Minutes M
LEFT OUTER JOIN dbo.Records R ON
    M.start_time BETWEEN CAST(R.time AS INT) AND
        (CAST(SUBSTRING(time, 1, 2) AS INT) * 100) +    -- hours
        (FLOOR((CAST(SUBSTRING(time, 3, 2) AS INT) + FLOOR(dur/60))/60)) +  -- carryover to hours
        (CAST(SUBSTRING(time, 3, 2) AS INT) + dur/60) % 60  -- minutes
GROUP BY
    M.start_time

Вам нужно будет либо использовать это как подзапрос, чтобы получить средние значения за определенное время. Поскольку это в пятницу вечером, я оставлю этот шаг до вас;)

РЕДАКТИРОВАТЬ: Одна из последних предостережений: я не учитывал промежутки времени, которые пересекают границы дня (т.е. проходят за полночь). Надеемся, что код указывает вам в правильном направлении. Лучшим подходом может быть создание представления, которое превращает все эти неприятные строки в фактические значения DATETIME, тогда это становится действительно тривиальным с таблицей Minutes.

Ответ 5

Я подошел к проблеме, преобразовывая данные в более простой формат. Я создал таблицу, где каждая строка представляет одну минуту вызова. Как только вы это сделаете, средний за минуту в час прост. В нем есть несколько выборок, чтобы показать промежуточные результаты. Если запрашиваемый временной интервал и длительность не очень велики, это должно быть нормально...?

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

/*
seconds is an s[time] value. It 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)

/* convert date + [time] into datetimes */
select 
    seconds,
    system,
    cast(date + ' ' + left([time], 2) + ':' + right([time], 2) as datetime) as start_date,
    /* end date to the minute */
    dateadd(mi, datediff(mi, 0, dateadd(s, dur, cast(date + ' ' + left([time], 2) + ':' + right([time], 2) as datetime))), 0) as end_date
into 
    #r
from
    #Records

select * from #r order by system, seconds, start_date, end_date;

/* create a row for each minute of each call */
create table #r_min(rnd int, seconds char(10), system int, minute datetime)

declare @maxrnd int;
select @maxrnd = max(datediff(mi, r.start_date, r.end_date)) from #r r
declare @i int;
set @i = 0;

while @i < @maxrnd begin

    insert into #r_min
    select @i, r.seconds, r.system, dateadd(mi, @i, r.start_date)
    from #r r
    where dateadd(mi, @i, r.start_date) <= r.end_date

set @i = @i + 1
end

select * from #r_min order by system, seconds, minute

/* concurrent per minute */
select  
    system, minute, count(*) as cnt
from 
    #r_min 
group by
    system, minute
order by 
    system, minute

/* avg concurrent per minute by hour */
select
    m.system,
    dateadd(hh, datediff(hh, 0, m.minute), 0) as hour,
    avg(m.cnt) as average_concurrent_per_minute
from
    (select  
        system, minute, count(*) as cnt
    from 
        #r_min 
    group by
        system, minute
    ) m
group by
    m.system,
    dateadd(hh, datediff(hh, 0, m.minute), 0)


drop table #Records
drop table #r
drop table #r_min

последний выбор дает...

system  hour    average_concurrent_per_minute
1   2009-04-16 19:00:00.000 1
2   2009-04-16 19:00:00.000 3

Ответ 6

Я вижу только один подход, который извлекает данные, указанные в записях вызовов:

Создайте список событий, где событие определяется как начало вызова или конец вызова. (Таким образом, каждая запись вызова будет генерировать два события.) Каждый элемент события должен содержать: system, datetime и boolean begin/end. Datetime следует округлить до ближайшей минуты.

Отсортируйте этот список по (system, datetime) и сканируйте его. Для каждого вызова начинайте увеличивать CURCNT на единицу. Для каждого конца вызова уменьшите CURCNT на единицу.

Если значение datetime отличается от предыдущей записи, добавьте CURCNT в HOURSUM. Если значение datetime указывает на начало нового часа, разделите HOURSUM на 60, напишите новую запись результата (система, дата, час, среднее значение) и reset HOURSUM до нуля.

Должно быть очевидно, когда инициализировать CURCNT и HOURSUM, что делать, когда системное значение отличается от предыдущей записи и т.д.

-Аль.