Date (nin-unique) Log
15-Jan-2023 18:45:53 Whatever
...
Нужно написать функцию, принимающую @DateFrom + @DateTo, определющие интервал выборки и возвращающую fill factor % заполненности лога поминутно, почасово, подневно. Пусть будет только поминутно. Процент вычисляется как % наличие записей на каждую из минут внутри интервала. то есть если в логе 1 запись как вверху, и интервал 15-Jan-2023 18:00:00 — 15-Jan-2023 19:00:00 (для педантов — "с" включаем, "по" — исключаем), то % будет 1/60 = 1.(6)%, если будет 500 записей датированных той же самой минутой, то результат не поменяется, если будет запись, например, 15-Jan-2023 18:46:53, то результат увеличится вдвое.
должно работать быстро, решение необходимо для MSSQL, оконные функции недоступны.
мой подход был конвертировать в то, по чему можно делать дистинкт, что работает, но, интересно, есть ли решение лучше.
Здравствуйте, 尿컙拋㕪⬎⤇Ǥ꧃푙刾ꄔ, Вы писали:
尿Ǥ푙>должно работать быстро, решение необходимо для MSSQL, оконные функции недоступны.
尿Ǥ푙>мой подход был конвертировать в то, по чему можно делать дистинкт, что работает, но, интересно, есть ли решение лучше.
Рассматриваем только поминутный вариант. Считаем, что даты @DateFrom и @DateTo, содержат время кратное часу, иначе эти параметры нужно пред обрабатывать.
Альтернатив реализации, честно говоря, я не вижу. Выбираем данные за период, отрезаем секунды и находим количество уникальных записей по дате.
drop table if exists #Log
create table #Log(TimeStamp datetime)
insert into #Log(TimeStamp) select '20230115 18:45:53'
insert into #Log(TimeStamp) select '20230115 18:40:53'
insert into #Log(TimeStamp) select '20230115 18:41:53'
insert into #Log(TimeStamp) select '20230115 18:42:53'
insert into #Log(TimeStamp) select '20230115 18:44:53'
insert into #Log(TimeStamp) select '20230115 18:43:53'
insert into #Log(TimeStamp) select '20230115 18:43:53'
insert into #Log(TimeStamp) select '20230115 18:43:53'
insert into #Log(TimeStamp) select '20230115 18:43:53'
insert into #Log(TimeStamp) select '20230115 18:43:53'
insert into #Log(TimeStamp) select '20230115 18:43:53'
insert into #Log(TimeStamp) select '20230115 18:43:53'
declare @DtFrom datetime = '20230115 18:00:00'
declare @DtTo datetime = '20230115 18:00:00'
select
100 * count
(
distinct dateadd(minute, datediff(minute, cast(0 as datetime), l.TimeStamp), cast(0 as datetime))
) /
(
nullif(datediff(minute, @DtFrom, @DtTo), 0) * 1.0
) as LogFillFactor
from
#Log l
where
l.TimeStamp between @DtFrom and @DtTo
P.S. Зачем ввели ограничение на оконные функции – не понял.
есть идея зайти с другой стороны — создать Range для искомого интервала дат с bit флагом и апдейтить его по exists данных из лога, не нужно конвертировать и дистинктить ничего — нет времени пробовать, думаешь, есть шанс?
---
Я делал через select count(distinct(datediff(minute, ..., ...)) как и ты, на больших логах работает не быстро.
O>P.S. Зачем ввели ограничение на оконные функции – не понял.
compatibility level, у нас функиции типа stuff самописные.
Здравствуйте, 尿컙拋㕪⬎⤇Ǥ꧃푙刾ꄔ, Вы писали: 尿Ǥ푙>UPDATE 尿Ǥ푙>есть идея зайти с другой стороны — создать Range для искомого интервала дат с bit флагом и апдейтить его по exists данных из лога, не нужно конвертировать и дистинктить ничего — нет времени пробовать, думаешь, есть шанс? 尿Ǥ푙>--- 尿Ǥ푙>Я делал через select count(distinct(datediff(minute, ..., ...)) как и ты, на больших логах работает не быстро. O>>P.S. Зачем ввели ограничение на оконные функции – не понял. 尿Ǥ푙>compatibility level, у нас функиции типа stuff самописные.
Прежде всего нужно понять почему исходный вариант работает медленно. На таблице должен быть индекс, возможно покрывающий.
Пробовать вариант с созданием набора данных в интервале дат, можно, но полагаю выигрыш будет незначительный. В логе как я понял, хранится время с секундами, поэтому перед выбором данных эти секунды нужно обрезать. Соответственно поиск по индексу будет работать только в интервале заданных дат l.TimeStamp between @DtFrom and @DtTo, а не по конкретному значению l.TimeStamp = c.Dt1 Важным фактором так же будет являться распределение данных в этом интервале дат за час, т.е. общее количество записей и количество дубликатов.
Как вариант я набросал пример с созданием набора данных в интервале. Но нужен сервер 2012+ для использования рекурсии в CTE. У меня выигрыш по стоимости плана запроса составил 4%. Данные представляют собой 4 млн. записей с посекундным интервалом.
Запрос
declare @DtFrom datetime = '20230814 18:00:00'
declare @DtTo datetime = '20230814 19:00:00'
;with Intervals as
(
select @DtFrom as Dt1
union all
select
dateadd(minute, 1, Dt1)
from
Intervals c
where
dateadd(minute, 1, Dt1) < @DtTo
)
select
100 * count
(
dateadd(minute, datediff(minute, 0, c.Dt1), 0)
) /
(
nullif(datediff(minute, @DtFrom, @DtTo), 0) * 1.0
) as LogFillFactor
from
Intervals c
where
exists
(
select
1
from
#Log l
where
l.TimeStamp between @DtFrom and @DtTo
and dateadd(minute, datediff(minute, cast(0 as datetime), l.TimeStamp), cast(0 as datetime)) = c.Dt1
)
option (maxrecursion 0)
я не спец по базам если что, но вот вариант запроса:
-- пишу в notepad'e
fill_factor = 100 * (select count(distinct(datediff(minute, '1-jan-1970', log_time))) from log) / datediff(minute, @date_from, @date_to)
у меня 130 уровень поэтому APPROX_COUNT_DISTINCT недоступен, было бы интересно посмотреть, сколько он дает в замен на то что просит, прогонишь если не сложно?
