#sql #sql-server #tsql #time #data-warehouse
#sql #sql-сервер #tsql #время #хранилище данных
Вопрос:
Контекст
Я использую Microsoft SQL Server 2016.
Существует таблица базы данных «Raw_data», которая содержит состояние компьютера вместе со временем его запуска. Существует несколько компьютеров, и каждый из них записывает свой статус в базу данных несколько раз в минуту.
Чтобы уменьшить объем данных, я пытаюсь агрегировать данные в 1-минутные фрагменты, чтобы сохранить их для дальнейшего анализа. Из-за ограничения емкости я хочу выполнять эту логику перехода каждые несколько минут (например, запланированное задание агента SQL Server), удалять необработанные данные и просто сохранять агрегированные данные.
Чтобы упростить пример, давайте предположим, что «Raw_data» выглядит примерно так:
╔════╦════════════╦════════╦═════════════════════╗
║ id ║ fk_machine ║ status ║ created_at ║
╠════╬════════════╬════════╬═════════════════════╣
║ 1 ║ 2222 ║ 0 ║ 2020-08-19 22:15:00 ║
║ 2 ║ 2222 ║ 3 ║ 2020-08-19 22:15:30 ║
║ 3 ║ 2222 ║ 5 ║ 2020-08-19 23:07:00 ║
║ 4 ║ 2222 ║ 1 ║ 2020-08-20 00:20:00 ║
║ 5 ║ 2222 ║ 0 ║ 2020-08-20 00:45:00 ║
║ 6 ║ 2222 ║ 5 ║ 2020-08-20 02:20:00 ║
╚════╩════════════╩════════╩═════════════════════╝
Также существуют таблицы базы данных «Dim_date» и «Dim_time», которые выглядят примерно так:
╔══════════╦══════════════╗
║ datekey ║ date_iso8601 ║
╠══════════╬══════════════╣
║ 20200101 ║ 2020-01-01 ║
║ 20200102 ║ 2020-01-02 ║
║ ... ║ ... ║
║ 20351231 ║ 2035-12-31 ║
╚══════════╩══════════════╝
╔═════════╦══════════╦═════════════════╗
║ timekey ║ time_iso ║ min_lower_bound ║
╠═════════╬══════════╬═════════════════╣
║ 1 ║ 00:00:01 ║ 00:00:00 ║
║ 2 ║ 00:00:02 ║ 00:00:00 ║
║ ... ║ ... ║ ... ║
║ 80345 ║ 08:03:45 ║ 08:03:00 ║
║ ... ║ ... ║ ... ║
║ 134504 ║ 13:45:04 ║ 13:45:00 ║
║ 134505 ║ 14:45:05 ║ 13:45:00 ║
║ ... ║ ... ║ ... ║
║ 235959 ║ 23:59:59 ║ 23:59:59 ║
╚═════════╩══════════╩═════════════════╝
Результат должен выглядеть следующим образом:
╔══════════════╦═════════════════╦════════════╦════════╦═══════════════╗
║ date_iso8601 ║ min_lower_bound ║ fk_machine ║ status ║ total_seconds ║
╠══════════════╬═════════════════╬════════════╬════════╬═══════════════╣
║ 2020-08-19 ║ 22:15:00 ║ 2222 ║ 0 ║ 30 ║
║ 2020-08-19 ║ 20:15:00 ║ 2222 ║ 3 ║ 30 ║
║ 2020-08-19 ║ 20:16:00 ║ 2222 ║ 3 ║ 60 ║
║ 2020-08-19 ║ 20:17:00 ║ 2222 ║ 3 ║ 60 ║
║ ... ║ ... ║ ... ║ ... ║ ... ║
║ 2020-08-19 ║ 23:06:00 ║ 2222 ║ 3 ║ 60 ║
║ 2020-08-19 ║ 23:07:00 ║ 2222 ║ 5 ║ 60 ║
║ 2020-08-19 ║ 23:08:00 ║ 2222 ║ 5 ║ 60 ║
║ ... ║ ... ║ ... ║ ... ║ ... ║
║ 2020-08-20 ║ 00:19:00 ║ 2222 ║ 5 ║ 60 ║
║ 2020-08-20 ║ 00:20:00 ║ 2222 ║ 1 ║ 60 ║
║ 2020-08-20 ║ 00:21:00 ║ 2222 ║ 1 ║ 60 ║
║ ... ║ ... ║ ... ║ ... ║ ... ║
║ 2020-08-20 ║ 00:44:00 ║ 2222 ║ 1 ║ 60 ║
║ 2020-08-20 ║ 00:45:00 ║ 2222 ║ 0 ║ 60 ║
╚══════════════╩═════════════════╩════════════╩════════╩═══════════════╝
Попытка
Чтобы вычислить продолжительность каждого состояния в минуту, я использовал CTE и LEAD для извлечения начальной даты и времени из следующего состояния в таблице базы данных, затем объединил с таблицами измерений и объединил результат.
WITH CTE_MACHINE_STATES(START_DATEKEY,
START_TIMEKEY,
FK_MACHINE,
END_DATEKEY,
END_TIMEKEY)
AS (SELECT CAST(CONVERT(CHAR(8), CREATED_AT, 112) AS INT), -- ISO: yyyymmdd
CONVERT(INT, REPLACE(CONVERT(CHAR(8), READING_TIME, 108), ':', '')),
FK_MACHINE,
STATUS,
CAST(CONVERT(CHAR(8), LEAD(CREATED_AT, 1) OVER(PARTITION BY FK_MACHINE
ORDER BY CREATED_AT), 112) AS INT),
CONVERT(INT, REPLACE(CONVERT(CHAR(8), LEAD(CREATED_AT, 1) OVER(PARTITION BY FK_MACHINE
ORDER BY CREATED_AT), 108), ':', ''))
FROM RAW_DATA)
SELECT DATE_ISO8601,
MIN_LOWER_BOUND,
FK_MACHINE,
STATUS,
SUM(1) AS TOTAL_SECONDS -- Duration
FROM CTE_MACHINE_STATES
CROSS JOIN DIM_DATE
CROSS JOIN DIM_TIME
WHERE TIMEKEY >= START_TIMEKEY AND
TIMEKEY < END_TIMEKEY AND
END_TIMEKEY IS NOT NULL AND -- last entry per machine and status
DATEKEY BETWEEN START_DATEKEY AND END_DATEKEY
GROUP BY FK_MACHINE,
STATUS,
DATE_ISO8610,
MIN_LOWER_BOUND
ORDER BY DATE_ISO8610,
MIN_LOWER_BOUND;
Проблема
Если состояние сохраняется после полуночи, оно не будет агрегировано правильно. Например, статус с идентификатором = 3 в «Raw_data» начинается в 23:07 и заканчивается в 00:20 на следующий день. Здесь значение timekey больше значения end_timekey, поэтому полученный статус исключается из результирующей таблицы фильтром TIMEKEY < END_TIMEKEY . Я не нашел решения о том, как изменить условие соединения, чтобы включить такие длительные состояния, но получить ожидаемый результат.
PS: Я уже писал, что обычно обновления статуса происходят каждые несколько секунд. Таким образом, проблема возникает только в крайних случаях, например, если компьютер get выключен.
Решение
К сожалению, я не получил ответа о том, как получить ожидаемый результат, используя таблицы измерений даты и времени. Но подход dnoeth с использованием рекурсивного CTE хорош, поэтому я пошел с ним:
WITH cte_outer AS (
SELECT fk_machine,
status,
created_at,
DATEADD(minute, DATEDIFF(minute, '2000', created_at), '2000') AS min_lower_bound, --truncates seconds from start time
LEAD(created_at) OVER(PARTITION BY fk_machine ORDER BY created_at) AS end_time
FROM raw_data
),
cte_recursive AS (
SELECT fk_machine,
status,
min_lower_bound,
end_time,
CASE
WHEN end_time > DATEADD(minute, 1, min_lower_bound)
THEN DATEDIFF(s, created_at, DATEADD(minute, 1, min_lower_bound))
ELSE DATEDIFF(s, created_at, end_time)
END AS total_seconds
FROM cte_outer
UNION ALL
SELECT fk_machine,
status,
DATEADD(minute, 1, min_lower_bound), -- next time segment (minute)
end_time,
CASE
WHEN end_time >= DATEADD(minute, 2, min_lower_bound)
THEN 60
ELSE DATEDIFF(s, DATEADD(minute, 1, min_lower_bound), end_time)
END
FROM cte_recursive
WHERE end_time > DATEADD(minute, 1, min_lower_bound)
)
SELECT min_lower_bound,
fk_machine,
status,
total_seconds
FROM cte_recursive
ORDER BY fk_machine,
min_lower_bound
Комментарии:
1. Я много лет проработал в промышленности, регулярно сталкиваясь с такого рода проблемами (кроме того, я француз, поэтому у нас проблема перехода на летнее время дважды в год …) Если это возможно для вас в зависимости от конфигурации вашей работы / политик, взгляните на базу данных временных рядов, такую как InfluxDB, вы будете поражены.
2. @Romka, спасибо за твой ответ и совет. К сожалению, изменение СУБД на данный момент не представляется возможным.
3. Пожалуйста, более четко опишите вашу цель. Представленный пример «агрегирует» шесть строк примерно в 240 (минутных) строках, что на самом деле не является сокращением объема . Что не так с тривиальной агрегацией общего количества секунд, сгруппированных по статусу, компьютеру и (начальной) минуте?
4. @Parfait: Извините, я забыл добавить объединения во фрагменте кода. Теперь я отредактировал свой вопрос.
5. Могу ли я использовать таблицу подсчета для этого? Это проще, и для этого не потребуются ваши таблицы DIM
Ответ №1:
Это вариант использования рекурсивного CTE, увеличивающегося created_at на одну минуту за рекурсию:
with cte as
(
select fk_machine
,status
,start_minute
,end_time
,case
when end_time > dateadd(minute, 1,start_minute)
then datediff(s, created_at, dateadd(minute, 1,start_minute))
else datediff(s, created_at, end_time )
end as seconds
from
(
select fk_machine
,status
,created_at
,dateadd(minute, datediff(minute, 0, created_at), 0) as start_minute
,lead(created_at)
over (PARTITION BY fk_machine
order by created_at) as end_time
from tab
) as dt
union all
select fk_machine
,status
,dateadd(minute, 1,start_minute)
,end_time
,case
when end_time >= dateadd(minute, 2,start_minute)
then 60
else datediff(s, dateadd(minute, 1,start_minute), end_time)
end
from cte
where end_time > dateadd(minute, 1,start_minute)
)
select * from cte
order by 1,3,4;
Смотрите скрипку
Комментарии:
1. Большое спасибо! Я воспользуюсь этим решением. Добавил это как принятый ответ, хотя в нем не используются таблицы измерений (которые я не указывал в качестве требования).
partition by-Часть при получении времени окончания текущего состояния get важна, когда имеется более одной машины.2. Конечно, без
PARTITION BY fk_machineэто не будет работать должным образом, я забыл это добавить. Если вам нужны FKS для ваших таблиц измерений, вы можете просто использовать свои первоначальные приведения.3. Да, я знаю. Спасибо! 🙂
Ответ №2:
Для чего-то подобного объединение ключей к одному datetime не так дорого, как может показаться. Затем вы можете вызвать DATEDIFF(), чтобы проверить наличие положительных, отрицательных, абсолютных значений для сравнения. Я запускал нечто подобное, переводя мгновенные данные в минутные агрегаты за несколько десятилетий, и datediff действительно имеет значение. Однако это было бы намного эффективнее, если бы вы просто извлекали необработанные данные и выполняли вычисления на языке с хорошей библиотекой datetime. SQL всегда является ответом, пока это не так.
Вероятно, причиной одной из проблем здесь является следующее утверждение:
WHERE TIMEKEY >= START_TIMEKEY AND
TIMEKEY < END_TIMEKEY AND
END_TIMEKEY IS NOT NULL AND
DATEKEY BETWEEN START_DATEKEY AND END_DATEKEY
Если дата и время не разделены, вы можете сказать:
WHERE DateTimeKey >= START_DateTimeKey AND
DateTimeKey < END_DateTimeKey AND
END_TIME-KEY IS NOT NULL
Если вы пытаетесь агрегировать по значению времени, было бы полезно исключить любую таблицу временных ключей, которая может быть еще одним источником проблем. Может быть хорошей идеей заменить таблицу timekey рекурсией и продолжительностью периода. Вам также необходимо будет учитывать эти условия:
Время окончания события всегда должно быть после времени начала агрегированного периода время начала:
DateDiff(second, Period_Start_Time, Event_End) > 0
Время начала события всегда должно быть до окончания времени окончания агрегированного периода:
DateDiff(second, Period_Start_Time, Event_Start) <= @Period_Duration
Существует несколько способов распределения данных о событиях по периодам, но datediff помогает и при линейном распределении.
Комментарии:
1. Спасибо за ваш ответ. Нет необходимости объединять клавиши даты и времени, поскольку время начала статуса («created_at» в Raw_data) указано в формате datetime. Не могли бы вы, пожалуйста, быть более точным или опубликовать пример кода о том, как вы будете агрегировать по минутам, после того, как мы рассчитали продолжительность каждого статуса через Datediff ?