Как я могу проверить среднее число одновременных событий в таблице SQL на основе даты, времени и продолжительности событий?
У меня есть набор подробных записей о вызовах, и из этих записей я должен определить среднее число одновременных активных вызовов на систему в час (с точностью до одной минуты). Если я сделаю запрос с 19:00 до 20:00, я должен увидеть среднее количество одновременных вызовов за час (усреднение одновременных вызовов за каждую минуту) в течение этого часа (для каждой системы).
Итак, мне нужен способ проверить количество активных вызовов за 7:00-7:01, 7:01-7:02 и т. Д., А затем усреднить эти числа. Вызов считается активным, если время и продолжительность вызова находятся в пределах текущей проверяемой минуты.
Что делает это еще более трудным, так это то, что он должен охватывать SQL 7.0 и SQL 2000 (некоторые функции в 2000 недоступны в 7.0, например, GetUTCTime()), если я смогу просто заставить работать 2000, я буду счастлив.
Какие подходы к этой проблеме я могу предпринять?
Я думал о циклическом переключении минут (60) в проверяемом часе и добавлении количества вызовов, приходящихся на эту минуту, а затем как-то перекрестно ссылающихся на продолжительность, чтобы убедиться, что вызов начинается в 19:00 и имеет продолжительность 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)
6 ответов
Я думаю, что у 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
;
Это просто скрещивает продукты / умножает строки записи, поэтому у нас есть одна строка записи на каждую целую минуту, в течение которой был активен вызов.
Обратите внимание, что я делаю это, определяя active как "(часть) вызов произошел в течение минуты". Таким образом, двухсекундный вызов, который начинается в 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 или же 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 это столбец в записи.
Как отмечал MarkusQ, ваше определение "одновременный" позволяет вам сократить математические вычисления.
- Вызов (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) можно рассчитать по-разному, и быстрее / проще:
- Call (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.
СУММИТЕ это значение для диапазона, который вы смотрите, затем разделите на количество минут в этом диапазоне, и у вас есть свой ответ.
Если вы ищете только одновременные вызовы, вы не можете использовать такие приемы, но это все же возможно (мне пришлось сделать что-то подобное). Но для вашего определения одновременной, это должно сделать это...
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
РЕДАКТИРОВАТЬ:
Я только что заметил, что ваши времена и даты хранятся в виде строк, вам нужно преобразовать их в DATETIME, чтобы мой код работал.
EDIT2:
Ошибка исправлена. Если вызов начался в 11:59:01 и закончился в 12:00:01, интервал 11:59 не должен учитываться. CASE заявления добавлены для компенсации.
Различные изменения макета
Мой первый совет: если вы когда-нибудь скажете (при использовании 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, тогда это становится действительно тривиальным с таблицей минут.
Если я вас правильно понимаю, вы хотите получить количество всех записей, для которых время начала меньше 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
Затем просто разделите это на количество минут в интересующем периоде.
Я подошел к проблеме, преобразовав данные в более простой формат. Я создал таблицу, где каждая строка представляет одну минуту разговора. Если у вас есть, что среднее значение в минуту за часом просто. Там есть несколько вариантов выбора, чтобы показать промежуточные результаты. До тех пор, пока запрашиваемый диапазон времени и длительности не очень велики, все должно быть в порядке...?
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'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)
/* 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
Я вижу только один подход, который извлекает данные, как указано в записях вызовов:
Создайте список событий, где событие определяется как начало или конец вызова. (Таким образом, каждая запись о вызове будет генерировать два события.) Каждый элемент события должен содержать: system, datetime и логическое начало / конец. Дата и время должны быть округлены до ближайшей минуты.
Сортируйте этот список по (system, datetime) и сканируйте его. Для каждого начала вызова увеличивайте CURCNT на единицу. Для каждого конца вызова уменьшите CURCNT на единицу.
Если значение даты и времени отличается от предыдущей записи, добавьте CURCNT в HOURSUM. Если значение даты и времени указывает на начало нового часа, разделите HOURSUM на 60, запишите новую запись результата (система, дата, час, среднее значение) и сбросьте значение HOURSUM на ноль.
Должно быть очевидно, когда инициализировать CURCNT и HOURSUM, что делать, если системное значение отличается от предыдущей записи и т. Д.
-Аль.