Как я могу проверить на средние параллельные события в таблице SQL на основе даты, время и продолжительность событий?
Мы могли бы использовать mapply и создать последовательность между двумя столбцами
unlist(mapply(`:`, m$V1, m$V2))
#[1] 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37..
#[29] 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65..
#[57] 76 77 78 79 80 81 82 83 84 32 33 34 35 36 37 38 39 40..
#[85] 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68..
#[113] 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96..
#[141] 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 ..
#[169] 88 89 90 91 92 93 94 95 96 97 98 99 100 101
6 ответов
Я думаю, что у MarkusQ есть ответ, но позвольте мне разработать альтернативу, которая может оказаться более простой в использовании. Я буду использовать свой обычный метод для его разработки в виде серии простых преобразований в представлениях, аналог функциональной декомпозиции на процедурном языке.
Во-первых, давайте разберем все в общих единицах. Напомним, что запись в столбце с является секундами с эпохи , полуночи 1 января 1970 года. Мы можем найти количество секунд с полуночи дня дня. call, этот вызов произошел, просто взяв модуль 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;
Итак, чтобы создать минуту таблица:
create view minute as
select id - 1 as active_minute
from artificial_range
where id <= 1440
;
Теперь мы просто присоединяем минуты к нашему представлению записи
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 ) ;
Обратите внимание, что это среднее значение за час для всех вызовов за все дни; если мы хотим ограничить его конкретным днем или диапазоном дней, мы добавим предложение где .
Но подождите, это еще не все!
Если мы создадим версию 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 (до которой можно использовать функции месяц и год ), а не дату char (4) в вашей таблице записей.
В связи с этим возникает еще один момент: и дата, и время в вашей таблице записей являются излишними и денормализованными, поскольку каждая из них может быть получена из ваших столбцов s. Оставляя их в таблице, допускается возможность несовместимости строк, в которых дата (с) <> дата или время (с) <> время . Я бы предпочел сделать это так:
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 является перечисляемым типом, который сообщает функции добавлять секунды; с - это столбец в записи.
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 является перечислимым типом, который сообщает функции добавлять секунды; с - это столбец в записи.
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 является перечислимым типом, который сообщает функции добавлять секунды; с - это столбец в записи.
Если я вас правильно понимаю, Вы хотите получить количество всех записей, для которых время начала меньше 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
Затем просто разделите это на количество минут в интересующем периоде.
Мой первый совет будет, если вы когда-нибудь обнаружите, что говорите (при использовании 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, тогда это становится действительно тривиальным с таблицей минут.
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, тогда это становится действительно тривиальным с таблицей минут.
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 учитывает промежутки времени, которые пересекают дневные границы (т. е. проходят после полуночи). Надеюсь, код указывает вам правильное направление для этого. Лучшим подходом может быть создание представления, которое превращает все эти неприятные строки в реальные значения DATETIME, тогда это становится действительно тривиальным с таблицей минут. t учитывает промежутки времени, которые пересекают дневные границы (т. е. проходят после полуночи). Надеюсь, код указывает вам правильное направление для этого. Лучшим подходом может быть создание представления, которое превращает все эти неприятные строки в реальные значения DATETIME, тогда это становится действительно тривиальным с таблицей минут.Я вижу только один подход, который извлекает данные, как указано в записях вызовов:
Создать список события, где событие определяется как начало вызова или конец вызова. (Таким образом, каждая запись вызова будет генерировать два события.) Каждый элемент события должен содержать: system, datetime и логическое начало / конец. Дата и время должны быть округлены до ближайшей минуты.
Сортируйте этот список по (системе, дате и времени) и сканируйте его. Для каждого начала вызова увеличивайте CURCNT на единицу. Для каждого конца вызова уменьшите CURCNT на 1.
Если значение datetime отличается от предыдущей записи, добавьте CURCNT в HOURSUM. Если значение datetime указывает начало нового часа, разделите HOURSUM на 60, запишите новую запись результата (система, дата, час, среднее значение) и сбросьте значение HOURSUM на ноль.
Я подошел к проблеме, преобразовав данные в более простой формат. Я создал таблицу, где каждая строка представляет одну минуту разговора. Если у вас есть, что среднее значение в минуту за часом просто. Там есть несколько вариантов выбора, чтобы показать промежуточные результаты. Пока запрашиваемый диапазон времени и длительности не очень велики, все должно быть в порядке ...?
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
Как сказал 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) можно вычислить по-другому и быстрее / легко:
- Звонок (A) охватывает 2 разных минутных интервала (12:00 и 12:01)
- Звонок (B) охватывает 2 разных минутных интервала (12:01 и 12:02)
{{1 }} => Всего охваченных минут = 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, чтобы мой код работал.
РЕДАКТИРОВАТЬ2:
Ошибка исправлена. Если вызов начался в «11:59:01» и закончился в «12:00:01», интервал «11:59» не должен учитываться. Операторы CASE добавлены для компенсации.
Различные правки макета