sql - 卡在涉及时间段的空白和离岛查询上

我有一个悬而未决的请求，要为我编写的温度日志应用程序编写一个报告，但我似乎无法确定该查询。
应用程序存储温度日志。这些日志每天要在几个不同的地方做3次。每个日志包含一个或多个区域，其中包含一个或多个设备。
我的报告需要显示设备（EquipmentStoreID），在给定的报告日期范围内，设备（EquipmentStoreID）连续出现超过3个记录周期的不良温度。我需要显示“岛”，或者坏天气开始和结束的日期范围。一些注意事项：
如果错过一个时间段，就应该认为是一个坏温度。但是，失败的temps的“孤岛”不应该从丢失的日志开始。
在给定的时间段内，同一个位置可以完成多个日志，在这种情况下，好的temp将胜过坏的temp。
这是我的SQLFiddle。我的结果是不正确的，因为连续的失败开始于一个实际错过的时间段，并且我无法找出如何处理同一时间段的多个日志。
非常感谢您的帮助！

最佳答案

这是我想到的。
为了避免在丢失的日志上启动孤岛，我将该行的日期与我们在每个存储中看到的第一个记录日期（下面查询中的minlog）进行了比较。如果行的日期早于第一个日志日期，那么我们知道它发生在开始录制之前，并且我不会用错误标志来标记它。
为了处理同一时间段内的多个日志，我在stagedrows中添加了一个priority字段，我们可以使用它来获取每个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)

关于sql - 卡在涉及时间段的空白和离岛查询上，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/27992749/