Застрял на запросе пробелов и островов, включающем периоды времени

У меня есть ожидающий запрос на написание отчета для приложения журнала температуры, которое я написал, и я просто не могу выполнить запрос.

Приложение хранит журналы температуры. Эти журналы должны быть сделаны 3 раза в день в нескольких разных местах. Каждый журнал содержит одну или несколько областей, которые содержат одну или несколько единиц оборудования.

В моем отчете необходимо показать оборудование (EquipmentStoreID), у которого была плохая температура в течение более 3 периодов журнала подряд для заданного диапазона дат отчета. Мне нужно показать «остров» или диапазон дат, когда начались и закончились плохие температуры. Некоторые предостережения:

  • Если период времени пропущен, это следует считать плохой температурой. Однако «остров» неудачных временных файлов не должен начинаться с пропущенного журнала.
  • Несколько журналов могут быть сделаны в одном и том же месте за определенный период времени, в этом случае хорошая временная температура превзойдет любые плохие.

Вот мой SQLFiddle. Мои результаты неверны, потому что последовательные сбои начинаются с периода времени, который фактически был пропущен, и я не могу понять, как обрабатывать несколько журналов за один и тот же период времени.

Любая помощь высоко ценится!


sql database sql-server gaps-and-islands
person koolaide    schedule 16.01.2015    source источник
comment
Что для вас означает плохая температура?   -  person Mike Sherrill 'Cat Recall'    schedule 17.01.2015
comment
Это когда столбец LogEquipment.CorrectiveAction не равен NULL   -  person koolaide    schedule 17.01.2015

Ответы (1)


arrow_upward
1
arrow_downward

Вот что я придумал.

Чтобы избежать запуска острова в пропущенном журнале, я сравнил дату строки с первой зарегистрированной датой, которую мы видим для каждого хранилища (minlog в запросе ниже). Если дата строки приходится на дату первого журнала, то мы знаем, что это произошло до того, как мы начали запись, и я не отмечаю это флажком ошибки.

Чтобы обрабатывать несколько журналов за один и тот же период времени, я добавил поле Priority в StagedRows, которое мы можем использовать для получения только первой записи для Store/Datetime, отдавая приоритет успешному журналу.

WITH TargetLogs 
     AS (SELECT le.*, 
                CONVERT(DATETIME, CONVERT(DATE, lh.StartDateTime)) as Date,
                lh.TimePeriodID, 
                lh.StartDateTime 
         FROM   [dbo].[LogEquipment] le 
                JOIN [dbo].[LogArea] la 
                  ON le.LogAreaID = la.LogAreaID 
                JOIN [dbo].[LogHeader] lh 
                  ON lh.LogHeaderID = la.LogHeaderID 
         WHERE  lh.StartDateTime Between CAST('2015-01-14' AS DateTime)
         AND CAST('2015-01-16' AS DateTime)
        ), 

     Dates --Generate date range
     AS (SELECT CAST('2015-01-14' AS DateTime) 'date' 
         UNION ALL 
         SELECT Dateadd(dd, 1, t.date) 
         FROM   Dates t 
         WHERE  Dateadd(dd, 1, t.date) <= CAST('2015-01-16' AS DateTime)), 

     DesiredDatesAndTimePeriods --Generate datetimes for all timeperiods between date range
     AS (SELECT DISTINCT tp.TimePeriodID, 
                tp.TimeDescription, 
                tp.StartTime,
                d.Date,
                d.Date + CONVERT(DATETIME, tp.StartTime) AS LogStartDateTime,
                le.EquipmentStoreID
         FROM   dbo.TimePeriod tp 
                CROSS JOIN Dates d 
                CROSS JOIN LogEquipment le 
         WHERE  tp.IsActive = 1), 

     StagedRows 
     AS (SELECT * FROM
     (
     SELECT d.LogStartDateTime, 
            d.EquipmentStoreID, 
            t.LogEquipmentID, 
            t.CorrectiveAction, 
            CASE WHEN minlog.MinStartDateTime <= d.LogStartDateTime 
                AND (t.LogEquipmentID IS NULL OR CorrectiveAction IS NOT NULL)  
                THEN 1 ELSE 0 END AS FailedFlag,
            ROW_NUMBER() OVER (PARTITION BY d.Date, d.TimePeriodID 
                 ORDER BY CASE WHEN CorrectiveAction IS NULL THEN 0 ELSE 1 END) 
                 AS Priority
     FROM DesiredDatesAndTimePeriods d
         LEFT OUTER JOIN TargetLogs t 
             on d.Date = t.Date AND d.TimePeriodId = t.TimePeriodId
         LEFT OUTER JOIN (SELECT EquipmentStoreId, MIN(StartDateTime) as MinStartDateTime FROM TargetLogs GROUP BY EquipmentStoreId) minlog 
             on d.EquipmentStoreID = minlog.EquipmentStoreID 
    ) dt WHERE Priority = 1) 

SELECT EquipmentStoreID,
          Count(*) AS ConsecutiveFails, 
       Start_date = Min(LogStartDateTime), 
       Stop_date = Max(LogStartDateTime) 
FROM   (SELECT EquipmentStoreID,
                        FailedFlag, 
               LogStartDateTime, 
               ROW_NUMBER() 
                 OVER ( 
                   ORDER BY EquipmentStoreID, LogStartDateTime) - ROW_NUMBER() 
                                                  OVER ( 
                                                    PARTITION BY EquipmentStoreID, FailedFlag 
                                                    ORDER BY EquipmentStoreID, LogStartDateTime) 
               grp 
        FROM   StagedRows) A 
GROUP  BY EquipmentStoreID, FailedFlag, 
          grp 
HAVING FailedFlag = 1 
       AND Count(*) > 3 
ORDER  BY Min(LogStartDateTime)
person jlee-tessik    schedule 11.02.2015
comment
Это было очень близко к тому, что мне было нужно, с несколькими настройками он работает, как и ожидалось. Спасибо! - person koolaide; 14.02.2015