В настоящее время у меня есть таблица событий с отметкой времени и описанием «состояния» этого события. Распределение этих событий довольно случайно — у меня может быть сотня событий в день, за которыми следует несколько дней бездействия. Эти события генерируются отдельными машинами, которые можно идентифицировать по идентификатору машины.
Представление, отображающее эти события, определяется следующим образом:
create view V_Machine_State_Window as
select LOCAL_DATETIME,
MACHINE,
STATE,
datediff(second, LOCAL_DATETIME, lead(LOCAL_DATETIME,1) over(order by MACHINE, LOCAL_DATETIME)) as DURATION,
isnull(left(STATE_TEXT, 60), '') as STATE_TEXT,
cast(LOCAL_DATETIME as date) as STATE_DATE,
cast(LOCAL_DATETIME as time) as STATE_TIME,
lag(STATE,1) over(order by MACHINE, LOCAL_DATETIME) as PREV_STATE,
isnull(left(lag(STATE_TEXT,1) over(order by MACHINE, LOCAL_DATETIME), 60), '') as PREV_STATE_TEXT,
lag(LOCAL_DATETIME,1) over(order by MACHINE, LOCAL_DATETIME) as PREV_STATE_DATESTAMP,
lead(STATE,1) over(order by MACHINE, LOCAL_DATETIME) as NEXT_STATE,
isnull(left(lead(STATE_TEXT,1) over(order by MACHINE, LOCAL_DATETIME), 60), '') as NEXT_STATE_TEXT,
lead(LOCAL_DATETIME,1) over(order by MACHINE, LOCAL_DATETIME) as NEXT_STATE_DATESTAMP
from MACHINE_STATE_TABLE
where LOCAL_DATETIME > DATEADD(day, -400, getdate())
Для целей отчетности важно, чтобы у нас была продолжительность состояния, предыдущее состояние, в котором оно было, если оно было, и следующее состояние, в которое оно изменилось, если оно есть.
В качестве требования для анализа поведения машины мне нужно разделить эти данные по отдельным дням, включая добавление записи для начала каждого дня с «состоянием» самого последнего события, вызванного этой машиной. Поскольку таблица Day представляет собой просто список дней, поэтому я могу использовать перекрестное применение, я использую следующее, чтобы добавить ежедневную запись на каждую машину:
with m_in_scope
as (
select distinct MACHINE
from V_Machine_State_Window)
select d.Day as LOCAL_DATETIME,
m_in_scope.MACHINE as MACHINE,
NULL as STATE,
NULL as DURATION,
NULL as STATE_TEXT,
d.Day as STATE_DATE,
cast('00:00' as time) as STATE_TIME,
NULL as PREV_STATE,
NULL as PREV_STATE_TEXT,
NULL as PREV_STATE_DATESTAMP,
NULL as NEXT_STATE,
NULL as NEXT_STATE_TEXT,
NULL as NEXT_STATE_DATESTAMP
from Day d
cross join m_in_scope
where d.Day > DATEADD(day, -400, getdate())
and d.Day < getdate()
То, что я изо всех сил пытаюсь сделать, это выяснить, как заполнить эти нули. Состояние должно быть самым последним состоянием перед началом дня, а продолжительность должна быть количеством секунд между началом дня (00:00) и отметкой даты следующего события или конца дня. , что наступит раньше. Мне удобно делать здесь всю математику, пока я могу получить предыдущую и следующую записи в своем заявлении.
Я пробовал это как CTE, но не могу найти рационального способа разбиения.
Я мог бы просто вставить нули и запустить обновление, используя выражение окна, но, как я указал, у меня может быть много таких ежедневных записей в строке, поэтому отставание и опережение выражения окна будут корректно обновлять только следующую запись после каждого " реальное" событие, а не те, что после него. Выполнение этого в курсоре, чтобы перебрать его до тех пор, пока не останется нулей, кажется очень плохой идеей для времени обработки, поскольку я смотрю на таблицу, содержащую примерно 50 миллионов записей, разделенных на несколько тысяч машин.
Есть ли рациональный способ создать либо CTE, либо оконное выражение (или какую-то другую альтернативу, о которой я еще не подумал), которая даст мне предыдущую и следующую запись с заполненным состоянием из того, что я описал выше?
ПРИМЕЧАНИЕ. Сервер, выполняющий эту обработку, использует SQL 2014. Пожалуйста, дайте мне знать, если правильное решение этой проблемы ограничено версией.
@ThomA Спасибо. Нужно будет обсудить лицензирование с командой. Обновление может быть вариантом, но не быстрым. Есть ли другие способы сделать это эффективно без игнорирования нулей?
Не могли бы вы поместить некоторые примеры данных (включая крайние случаи) и желаемые результаты в скрипт ? Помочь будет намного проще.
@AaronBertrand Надеюсь, я делаю это правильно: ссылка
Взять то, что вы вложили в скрипку, и превратить это в DDL/DML:
DECLARE @V_Machine_State_Window TABLE (Local_Datetime DATETIME, Machine NVARCHAR(128), State BIT, Duration INT, State_Text NVARCHAR(20), State_Date DATE, State_time TIME, Prev_State BIT, Prev_State_Text NVARCHAR(20),
Prev_State_Datestamp DATETIME, Next_State BIT, Next_State_Text NVARCHAR(20), Next_State_Datestamp DATETIME);
INSERT INTO @V_Machine_State_Window (Local_Datetime, Machine, State, Duration, State_Text, State_Date, State_Time, Prev_State, Prev_State_Text, Prev_State_Datestamp, Next_State, Next_State_Text, Next_State_Datestamp) VALUES
('2023-06-01 00:00:00.000', 'PC1', 0, 300 , 'Idle', '2023-06-01', '00:00:00.0000000', null, null, null, 1, 'Active', '2023-06-01 00:05:00.000'),
('2023-06-01 00:05:00.000', 'PC1', 1, 600 , 'Active', '2023-06-01', '00:05:00.0000000', 0, 'Idle', '2023-06-01 00:00:00.000', 0, 'Idle', '2023-06-01 00:15:00.000'),
('2023-06-01 00:15:00.000', 'PC1', 0, 373500, 'Idle', '2023-06-01', '00:15:00.0000000', 1, 'Active', '2023-06-01 00:05:00.000', 1, 'Active', '2023-06-05 08:00:00.000'),
('2023-06-05 08:00:00.000', 'PC1', 1, 600 , 'Active', '2023-06-05', '08:00:00.0000000', 0, 'Idle', '2023-06-01 00:15:00.000', 0, 'Idle', '2023-06-05 08:10:00.000'),
('2023-06-01 21:32:00.000', 'PC2', 1, 600, 'Spin', '2023-06-04', '21:32:00.00000', NULL, NULL, NULL, NULL, NULL, NULL),
('2023-06-04 21:32:00.000', 'PC2', 1, 600, 'Spin', '2023-06-04', '21:32:00.00000', NULL, NULL, NULL, NULL, NULL, NULL);
(Я добавил пару строк для второго имени машины).
Состояния Prev/Next являются излишними, так как нам нужно будет вычислить их снова после добавления ваших ежедневных начальных строк, как мы будем делать со столбцом продолжительности, который, кажется, основан на разнице в секундах между этими строками Local_Datetime
и следующий.
Вы упоминаете, что у вас есть таблица Days. В этом примере эти данные генерируются с использованием rCTE
для заполнения ряда между минимальной и максимальной датами в таблице. Затем это CROSS APPLY
добавляется к уникальным именам машин, поэтому мы получаем день для каждой машины.
Наконец, набор результатов, основанный на MachineDays
, создается с помощью OUTER APPLY
в самой таблице, чтобы найти самую последнюю фактическую строку для сопоставления с полуночью каждого дня. Это UNION
с сгенерированными строками, и затем мы можем выполнить над ним функции LAG
и LEAD
, чтобы заполнить предыдущее, следующее и значения длительности.
;WITH Days AS (
SELECT MIN(State_Date) AS Datetime, MAX(State_Date) AS mxDatetime
FROM @V_Machine_State_Window
UNION ALL
SELECT DATEADD(DAY,1,Datetime), mxDatetime
FROM Days
WHERE Datetime < mxDatetime
), Machines AS (
SELECT Machine
FROM @V_Machine_State_Window
GROUP BY Machine
), MachineDays AS (
SELECT CAST(Datetime AS DATETIME) AS Datetime, Machine
FROM Days
CROSS APPLY Machines
)
SELECT Local_Datetime, Machine, State,
DATEDIFF(SECOND,Local_Datetime,LEAD(Local_DateTime, 1) OVER (PARTITION BY Machine ORDER BY Local_Datetime)) AS Duration,
State_Text, State_Date, State_Time,
LAG(State,1) OVER (PARTITION BY Machine ORDER BY Local_Datetime) AS Prev_State, LAG(State_Text,1) OVER (PARTITION BY Machine ORDER BY Local_Datetime) AS Prev_State_Text,
LAG(Local_Datetime,1) OVER (PARTITION BY Machine ORDER BY Local_Datetime) AS Prev_State_Datestamp,
LEAD(State,1) OVER (PARTITION BY Machine ORDER BY Local_Datetime) AS Next_State, LEAD(State_Text,1) OVER (PARTITION BY Machine ORDER BY Local_Datetime) AS Next_State_Text,
LEAD(Local_Datetime,1) OVER (PARTITION BY Machine ORDER BY Local_Datetime) AS Next_State_Datestamp
FROM (
SELECT md.Datetime AS Local_Datetime, a.Machine, a.State, a.Duration, a.State_Text, CAST(md.Datetime AS DATE) AS State_Date, '00:00:00' AS State_Time
FROM MachineDays md
OUTER APPLY (SELECT TOP 1 * FROM @V_Machine_State_Window WHERE Machine = md.Machine AND Local_Datetime < md.Datetime ORDER BY Local_Datetime DESC) a
WHERE a.Machine IS NOT NULL
UNION
SELECT a.Local_Datetime, a.Machine, a.State, a.Duration, a.State_Text, a.State_Date, a.State_Time
FROM @V_Machine_State_Window a
) a
Спасибо за ответ. Я сейчас проверяю функциональность, но я думаю, что это выглядит хорошо.
К сожалению, вы находитесь в 2014 году; 2022 добавляет предложение
IGNORE NULLS
кLAG
/LEAD
, что значительно упрощает получение последнего значения, отличного отNULL
.