Присоединяйтесь к последнему событию в ежедневных сплитах

В настоящее время у меня есть таблица событий с отметкой времени и описанием «состояния» этого события. Распределение этих событий довольно случайно — у меня может быть сотня событий в день, за которыми следует несколько дней бездействия. Эти события генерируются отдельными машинами, которые можно идентифицировать по идентификатору машины.

Представление, отображающее эти события, определяется следующим образом:

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. Пожалуйста, дайте мне знать, если правильное решение этой проблемы ограничено версией.

К сожалению, вы находитесь в 2014 году; 2022 добавляет предложение IGNORE NULLS к LAG/LEAD, что значительно упрощает получение последнего значения, отличного от NULL.

Thom A 15.08.2023 13:05

@ThomA Спасибо. Нужно будет обсудить лицензирование с командой. Обновление может быть вариантом, но не быстрым. Есть ли другие способы сделать это эффективно без игнорирования нулей?

JFRApplications 15.08.2023 13:39

Не могли бы вы поместить некоторые примеры данных (включая крайние случаи) и желаемые результаты в скрипт ? Помочь будет намного проще.

Aaron Bertrand 15.08.2023 14:35

@AaronBertrand Надеюсь, я делаю это правильно: ссылка

JFRApplications 15.08.2023 15:25
Стоит ли изучать PHP в 2023-2024 годах?
Стоит ли изучать PHP в 2023-2024 годах?
Привет всем, сегодня я хочу высказать свои соображения по поводу вопроса, который я уже много раз получал в своем сообществе: "Стоит ли изучать PHP в...
Поведение ключевого слова "this" в стрелочной функции в сравнении с нормальной функцией
Поведение ключевого слова "this" в стрелочной функции в сравнении с нормальной функцией
В JavaScript одним из самых запутанных понятий является поведение ключевого слова "this" в стрелочной и обычной функциях.
Приемы CSS-макетирования - floats и Flexbox
Приемы CSS-макетирования - floats и Flexbox
Здравствуйте, друзья-студенты! Готовы совершенствовать свои навыки веб-дизайна? Сегодня в нашем путешествии мы рассмотрим приемы CSS-верстки - в...
Тестирование функциональных ngrx-эффектов в Angular 16 с помощью Jest
В системе управления состояниями ngrx, совместимой с Angular 16, появились функциональные эффекты. Это здорово и делает код определенно легче для...
Концепция локализации и ее применение в приложениях React ⚡️
Концепция локализации и ее применение в приложениях React ⚡️
Локализация - это процесс адаптации приложения к различным языкам и культурным требованиям. Это позволяет пользователям получить опыт, соответствующий...
Пользовательский скаляр GraphQL
Пользовательский скаляр GraphQL
Листовые узлы системы типов GraphQL называются скалярами. Достигнув скалярного типа, невозможно спуститься дальше по иерархии типов. Скалярный тип...
0
4
62
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Взять то, что вы вложили в скрипку, и превратить это в 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
Local_Datetime Машина Состояние Продолжительность State_Text State_Date Состояние_Время Prev_State Prev_State_Text Prev_State_Datestamp Next_State Next_State_Text Next_State_Datestamp 2023-06-01 00:00:00.000 ПК1 0 300 Праздный 2023-06-01 00:00:00.0000000 1 Активный 2023-06-01 00:05:00.000 2023-06-01 00:05:00.000 ПК1 1 600 Активный 2023-06-01 00:05:00.0000000 0 Праздный 2023-06-01 00:00:00.000 0 Праздный 2023-06-01 00:15:00.000 2023-06-01 00:15:00.000 ПК1 0 85500 Праздный 2023-06-01 00:15:00.0000000 1 Активный 2023-06-01 00:05:00.000 0 Праздный 2023-06-02 00:00:00.000 2023-06-02 00:00:00.000 ПК1 0 86400 Праздный 2023-06-02 00:00:00.0000000 0 Праздный 2023-06-01 00:15:00.000 0 Праздный 2023-06-03 00:00:00.000 2023-06-03 00:00:00.000 ПК1 0 86400 Праздный 2023-06-03 00:00:00.0000000 0 Праздный 2023-06-02 00:00:00.000 0 Праздный 2023-06-04 00:00:00.000 2023-06-04 00:00:00.000 ПК1 0 86400 Праздный 2023-06-04 00:00:00.0000000 0 Праздный 2023-06-03 00:00:00.000 0 Праздный 2023-06-05 00:00:00.000 2023-06-05 00:00:00.000 ПК1 0 28800 Праздный 2023-06-05 00:00:00.0000000 0 Праздный 2023-06-04 00:00:00.000 1 Активный 2023-06-05 08:00:00.000 2023-06-05 08:00:00.000 ПК1 1 Активный 2023-06-05 08:00:00.0000000 0 Праздный 2023-06-05 00:00:00.000 2023-06-01 21:32:00.000 ПК2 1 8880 Вращаться 2023-06-04 21:32:00.0000000 1 Вращаться 2023-06-02 00:00:00.000 2023-06-02 00:00:00.000 ПК2 1 86400 Вращаться 2023-06-02 00:00:00.0000000 1 Вращаться 2023-06-01 21:32:00.000 1 Вращаться 2023-06-03 00:00:00.000 2023-06-03 00:00:00.000 ПК2 1 86400 Вращаться 2023-06-03 00:00:00.0000000 1 Вращаться 2023-06-02 00:00:00.000 1 Вращаться 2023-06-04 00:00:00.000 2023-06-04 00:00:00.000 ПК2 1 77520 Вращаться 2023-06-04 00:00:00.0000000 1 Вращаться 2023-06-03 00:00:00.000 1 Вращаться 2023-06-04 21:32:00.000 2023-06-04 21:32:00.000 ПК2 1 8880 Вращаться 2023-06-04 21:32:00.0000000 1 Вращаться 2023-06-04 00:00:00.000 1 Вращаться 2023-06-05 00:00:00.000 2023-06-05 00:00:00.000 ПК2 1 Вращаться 2023-06-05 00:00:00.0000000 1 Вращаться 2023-06-04 21:32:00.000

Спасибо за ответ. Я сейчас проверяю функциональность, но я думаю, что это выглядит хорошо.

JFRApplications 16.08.2023 11:51

Другие вопросы по теме