Блог

Главная - Вопросы по программированию - Агрегирование данных по скользящим периодам времени

Агрегирование данных по скользящим периодам времени

#sql #sql-server #aggregate #datediff #sliding-window

#sql #sql-сервер #агрегировать #datediff #скользящее окно

Вопрос:

[Запрос и вопрос отредактированы и исправлены благодаря комментариям @Gordon Linoff и @shawnt00]

Недавно я унаследовал SQL-запрос, который вычисляет количество некоторых событий во временных окнах продолжительностью 30 дней из базы данных журналов. Он использует CTE (общее табличное выражение) для генерации 30-дневных диапазонов с момента «2019-01-01» по настоящее время. И затем он подсчитывает обращения в эти интервалы 30/60/90 дней. Я не уверен, что это лучший метод. Все, что я знаю, это то, что для запуска требуется много времени, и я не понимаю на 100%, как именно это работает. Поэтому я пытаюсь перестроить его эффективным способом (возможно, как сейчас, это самый эффективный способ, я не знаю).

У меня есть несколько вопросов:

  1. Одна из вещей, которые я замечаю, заключается в том, что вместо использования DATEDIFF запрос просто вычитает количество дней из даты.Это вообще хорошая практика?
  2. Есть ли лучший способ сравнения времени?
  3. Есть ли лучший способ сделать все это? Суть в том, что мне нужно агрегировать данные по количеству вхождений за периоды времени в 30, 60 и 90 дней.

Примечание: исходный формат LogDate похож на 2019-04-01 18:30:12.000.

 DECLARE @dt1 Datetime='2019-01-01'
DECLARE @dt2 Datetime=getDate();

WITH ctedaterange 
     AS (SELECT [Dates]=@dt1 
         UNION ALL
         SELECT [dates]   30 
         FROM   ctedaterange 
         WHERE  [dates]   30<= @dt2) 

SELECT 
[dates], 
lt.Activity, COUNT(*) as Total,
    SUM(CASE WHEN lt.LogDate <= dates and lt.LogDate > dates - 90 THEN 1 ELSE 0 END) AS Activity90days,
    SUM(CASE WHEN lt.LogDate <= dates and lt.LogDate > dates - 60 THEN 1 ELSE 0 END) AS Activity60days,
    SUM(CASE WHEN lt.LogDate <= dates and lt.LogDate > dates - 30 THEN 1 ELSE 0 END) AS Activity30days
FROM   ctedaterange AS cte
JOIN (SELECT Activity, CONVERT(DATE, LogDate) as LogDate FROM LogTable) AS lt
ON cte.[dates] = lt.LogDate
group by [dates], lt.Activity
OPTION (maxrecursion 0)

Образец набора данных (таблица журналов):

 LogDate, Activity
2020-02-25 01:10:10.000, Activity01
2020-04-14 01:12:10.000, Activity02
2020-08-18 02:03:53.000, Activity02
2019-10-29 12:25:55.000, Activity01
2019-12-24 18:11:11.000, Activity03
2019-04-02 03:33:09.000, Activity01

Ожидаемый результат (результат не отражает данные, указанные выше, поскольку мне потребуется слишком много строк в наборе примеров, которые будут показаны в этом сообщении)

Как я уже говорил выше, суть в следующем: мне нужно агрегировать данные по количеству вхождений в периоды времени 30, 60 и 90 дней.

 Activity, Activity90days, Activity60days, Activity30days
Activity01, 3, 0, 1
Activity02, 1, 10, 2
Activity03, 5, 1, 3

Спасибо за любое предложение.

Комментарии:

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

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

3. Кроме того, вы группируете по lt.Activity , но возвращаете lt.Event неагрегированный. Это должно вызвать ошибку.

4. Извините, Гордон и Шон, я вставил из разных запросов. Я исправил временные рамки. Я добавлю некоторые примеры данных.

5. Привет @shawnt00, какое соединение будет отсутствовать?

Ответ №1:

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

 sum(count(LogDate)) over (
    partition by Activity order by [dates]
    with rows between 89 preceding and current row
)