SQL 挑战 - 传感器阈值异常报告

我可能会使用类似下面的东西。

它能够使用索引顺序并避免排序，直到它到达最终GROUP BY（对我来说，它使用流聚合）

原则上，实际上并不需要最后的分组操作。应该可以读取排序的输入流Sensor, MeasureTime并以流方式输出所需的结果，但我认为您需要为此编写一个 SQLCLR 过程。

WITH T1
     AS (SELECT m.*,
                s.[Lower Threshold],
                s.[Upper Threshold],
                within_threshold,
                start_group_flag = IIF(within_threshold = 0 AND LAG(within_threshold, 1, 1) OVER (PARTITION BY m.[Sensor] ORDER BY [Measure Time]) = 1, 1, 0),
                next_measure_time = LEAD([Measure Time]) OVER (PARTITION BY m.[Sensor] ORDER BY [Measure Time]),
                overage = IIF(Measurement > [Upper Threshold], Measurement - [Upper Threshold], 0),
                underage =IIF(Measurement < [Lower Threshold], Measurement - [Lower Threshold], 0)
         FROM   [Measurements] m
                JOIN [Sensors] s
                  ON m.Sensor = s.Sensor
                CROSS APPLY (SELECT IIF(m.[Measurement] BETWEEN s.[Lower Threshold] AND s.[Upper Threshold],1,0)) ca(within_threshold)),
     T2
     AS (SELECT *,
                group_number = SUM(start_group_flag) OVER (PARTITION BY [Sensor] ORDER BY [Measure Time] ROWS UNBOUNDED PRECEDING)
         FROM   T1
         WHERE  within_threshold = 0)
SELECT Sensor,
       [Exception Start Time] = MIN([Measure Time]),
       [Exception End Time] = MAX(ISNULL(next_measure_time, [Measure Time])),
       [Exception Duration (minutes)] = DATEDIFF(MINUTE, MIN([Measure Time]), MAX(ISNULL(next_measure_time, [Measure Time]))),
       [Min Measurement] = MIN(Measurement),
       [Max Measurement] = MAX(Measurement),
       [Lower Threshold],
       [Upper Threshold],
       [Maximal Delta From Thresholds] = IIF(MAX(overage) > -MIN(underage), MAX(overage), MIN(underage))
FROM   T2
GROUP  BY group_number,
          Sensor,
          [Lower Threshold],
          [Upper Threshold] 

流式 SQL CLR 函数实现按顺序读取行Sensor, [Measure Time]：

资源

using Microsoft.SqlServer.Server;
using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;

public partial class UserDefinedFunctions
{
    [SqlFunction(
        DataAccess = DataAccessKind.Read,
        FillRowMethodName = "GetExceptions_FillRow",
        IsDeterministic = true,
        IsPrecise = true,
        Name = "GetExceptions",
        SystemDataAccess = SystemDataAccessKind.None,
        TableDefinition =
            @"
            Sensor nvarchar(10) NULL,
            Exception_Start_Time datetime2(0) NULL,
            Exception_End_Time datetime2(0) NULL,
            Exception_Duration_Minutes integer NULL,
            Min_Measurement decimal (7,2) NULL,
            Max_Measurement decimal (7,2) NULL,
            Lower_Threshold decimal (7,2) NULL,
            Upper_Threshold decimal (7,2) NULL,
            Maximal_Delta_From_Thresholds decimal (7,2) NULL
            ")]
    public static IEnumerator GetExceptions
    (
        [SqlFacet(MaxSize = 256)] SqlString Instance,
        [SqlFacet(MaxSize = 128)] SqlString Database
    )
    {
        const string query =
            @"
            SELECT
                S.Sensor,
                S.[Lower Threshold],
                S.[Upper Threshold],
                M.[Measure Time],
                M.Measurement
            FROM dbo.Sensors AS S
            JOIN dbo.Measurements AS M
                ON M.Sensor = S.Sensor
            ORDER BY
                S.Sensor ASC,
                M.[Measure Time] ASC;
            ";

        var csb = new SqlConnectionStringBuilder
        {
            ApplicationName = "Thresholds.GetExceptions",
            ContextConnection = false,
            DataSource = Instance.Value,
            Enlist = false,
            InitialCatalog = Database.Value,
            IntegratedSecurity = true
        };

        using (var con = new SqlConnection(csb.ConnectionString))
        {
            con.Open();
            using (var cmd = new SqlCommand(query, con))
            {
                var reader = cmd.ExecuteReader(CommandBehavior.SingleResult | CommandBehavior.SequentialAccess);

                Record record = null;
                SensorException sensorException = null;

                while (reader.Read())
                {
                    record = new Record
                    {
                        Sensor = reader.GetSqlString(0),
                        LowerThreshold = reader.GetSqlDecimal(1),
                        UpperThreshold = reader.GetSqlDecimal(2),
                        MeasureTime = reader.GetDateTime(3),
                        Measurement = reader.GetSqlDecimal(4)
                    };

                    if (record.Measurement < record.LowerThreshold || record.Measurement > record.UpperThreshold)
                    {
                        if (sensorException == null)
                        {
                            sensorException = new SensorException
                            {
                                Sensor = record.Sensor,
                                Exception_Start_Time = record.MeasureTime,
                                Min_Measurement = record.Measurement,
                                Max_Measurement = record.Measurement,
                                Lower_Threshold = record.LowerThreshold,
                                Upper_Threshold = record.UpperThreshold
                            };
                        }
                        else
                        {
                            if (record.Measurement < sensorException.Min_Measurement)
                            {
                                sensorException.Min_Measurement = record.Measurement;
                            }

                            if (record.Measurement > sensorException.Max_Measurement)
                            {
                                sensorException.Max_Measurement = record.Measurement;
                            }
                        }
                    }
                    else
                    {
                        if (sensorException != null)
                        {
                            sensorException.Exception_End_Time = record.MeasureTime;
                            yield return sensorException;
                            sensorException = null;
                        }
                    }
                }

                // Final row
                if (sensorException != null)
                {
                    sensorException.Exception_End_Time = record.MeasureTime;
                    yield return sensorException;
                    sensorException = null;
                }
            }
        }
    }

    public static void GetExceptions_FillRow
    (
        Object obj,
        out SqlString Sensor,
        out DateTime Exception_Start_Time,
        out DateTime Exception_End_Time,
        out SqlInt32 Exception_Duration_Minutes,
        out SqlDecimal Min_Measurement,
        out SqlDecimal Max_Measurement,
        out SqlDecimal Lower_Threshold,
        out SqlDecimal Upper_Threshold,
        out SqlDecimal Maximal_Delta_From_Thresholds
    )
    {
        var sensorException = (SensorException)obj;
        Sensor = sensorException.Sensor;
        Exception_Start_Time = sensorException.Exception_Start_Time;
        Exception_End_Time = sensorException.Exception_End_Time;
        Exception_Duration_Minutes = Convert.ToInt32(Exception_End_Time.Subtract(Exception_Start_Time).TotalMinutes);
        Min_Measurement = sensorException.Min_Measurement;
        Max_Measurement = sensorException.Max_Measurement;
        Lower_Threshold = sensorException.Lower_Threshold;
        Upper_Threshold = sensorException.Upper_Threshold;

        var upperDiff = Max_Measurement > Upper_Threshold ? Max_Measurement - Upper_Threshold : 0;
        var lowerDiff = Min_Measurement < Lower_Threshold ? Lower_Threshold - Min_Measurement : 0;

        Maximal_Delta_From_Thresholds = upperDiff > lowerDiff ? upperDiff : lowerDiff;
    }

    internal class Record
    {
        internal SqlString Sensor { get; set; }
        internal SqlDecimal LowerThreshold { get; set; }
        internal SqlDecimal UpperThreshold { get; set; }
        internal DateTime MeasureTime { get; set; }
        internal SqlDecimal Measurement { get; set; }
    }

    internal class SensorException
    {
        internal SqlString Sensor { get; set; }
        internal DateTime Exception_Start_Time { get; set; }
        internal DateTime Exception_End_Time { get; set; }
        internal SqlDecimal Min_Measurement { get; set; }
        internal SqlDecimal Max_Measurement { get; set; }
        internal SqlDecimal Lower_Threshold { get; set; }
        internal SqlDecimal Upper_Threshold { get; set; }
    }
}

部署脚本

创建装配位（稍微太长以内联发布）

注意：由于限制，此程序集需要EXTERNAL_ACCESS权限，但它只能从同一数据库读取。出于测试目的，制作数据库就足够了TRUSTWORTHY，尽管有充分的理由不在生产中这样做——而是对程序集进行签名。

CREATE OR ALTER FUNCTION dbo.GetExceptions 
(
    @Instance nvarchar(256), 
    @Database nvarchar(128)
)
RETURNS TABLE 
(
    Sensor nvarchar(10) NULL,
    [Exception Start Time] datetime2(0) NULL,
    [Exception End Time] datetime2(0) NULL,
    [Exception Duration (minutes)] integer NULL,
    [Min Measurement] decimal (7,2) NULL,
    [Max Measurement] decimal (7,2) NULL,
    [Lower Threshold] decimal (7,2) NULL,
    [Upper_Threshold] decimal (7,2) NULL,
    [Maximal Delta From Thresholds] decimal (7,2) NULL
)
ORDER (Sensor, [Exception Start Time])
AS EXTERNAL NAME Thresholds.UserDefinedFunctions.GetExceptions;

询问

SELECT
    GE.Sensor,
    GE.[Exception Start Time],
    GE.[Exception End Time],
    GE.[Exception Duration (minutes)],
    GE.[Min Measurement],
    GE.[Max Measurement],
    GE.[Lower Threshold],
    GE.Upper_Threshold,
    GE.[Maximal Delta From Thresholds]
FROM dbo.GetExceptions(@@SERVERNAME, DB_NAME()) AS GE
ORDER BY
    GE.Sensor,
    GE.[Exception Start Time];

需要参数以便函数知道如何连接到源数据。

执行计划

结果

╔══════════╦══════════════════════╦═════════════════════╦══════════════════════════════╦═════════════════╦═════════════════╦═════════════════╦═════════════════╦═══════════════════════════════╗
║  Sensor  ║ Exception Start Time ║ Exception End Time  ║ Exception Duration (minutes) ║ Min Measurement ║ Max Measurement ║ Lower Threshold ║ Upper_Threshold ║ Maximal Delta From Thresholds ║
╠══════════╬══════════════════════╬═════════════════════╬══════════════════════════════╬═════════════════╬═════════════════╬═════════════════╬═════════════════╬═══════════════════════════════╣
║ Sensor A ║ 2016-01-01 10:30:00  ║ 2016-01-02 08:00:00 ║                         1290 ║ 59.00           ║ 66.00           ║ -50.00          ║ 50.00           ║ 16.00                         ║
║ Sensor B ║ 2016-01-01 08:00:00  ║ 2016-01-01 13:00:00 ║                          300 ║ 39.00           ║ 88.00           ║ 40.00           ║ 80.00           ║ 8.00                          ║
║ Sensor B ║ 2016-01-02 08:00:00  ║ 2016-01-02 17:00:00 ║                          540 ║ 95.00           ║ 95.00           ║ 40.00           ║ 80.00           ║ 15.00                         ║
║ Sensor C ║ 2016-01-01 10:00:00  ║ 2016-01-01 23:30:00 ║                          810 ║ -2.00           ║ -1.00           ║ 0.00            ║ 100.00          ║ 2.00                          ║
╚══════════╩══════════════════════╩═════════════════════╩══════════════════════════════╩═════════════════╩═════════════════╩═════════════════╩═════════════════╩═══════════════════════════════╝

我在没有查看其他答案的情况下写下了我对解决方案的尝试，但看到我的查询与 Martin 的查询非常相似，我并不感到惊讶。我似乎用少一个窗口函数得到了正确的结果，但我怀疑性能会有很大差异。这是完整的代码：

SELECT q4.*
, CASE WHEN ca.lower_delta IS NULL OR ABS(ca.upper_delta) > ABS(ca.lower_delta) THEN ca.upper_delta ELSE ca.lower_delta END [Maximal Delta From Thresholds]
FROM
(
    SELECT q3.Sensor
    , MIN(q3.[Measure Time]) [Exception Start Time]
    , ISNULL(MIN(CASE WHEN q3.IsException = 0 THEN q3.[Measure Time] ELSE NULL END), MAX(q3.[Measure Time])) [Exception End Time]
    , DATEDIFF(MINUTE, MIN(q3.[Measure Time]), ISNULL(MIN(CASE WHEN q3.IsException = 0 THEN q3.[Measure Time] ELSE NULL END), MAX(q3.[Measure Time]))) [Exception Duration (minutes)]
    , MIN(CASE WHEN q3.IsException = 1 THEN q3.Measurement ELSE NULL END) [Min Measurement]
    , MAX(CASE WHEN q3.IsException = 1 THEN q3.Measurement ELSE NULL END) [Max Measurement]
    , MIN(q3.[Lower Threshold]) [Lower Threshold]
    , MAX(q3.[Upper Threshold]) [Upper Threshold]
    FROM
    (
        SELECT q2.*
        , SUM(StartOfExceptionPartition) OVER (PARTITION BY Sensor ORDER BY [Measure Time] ROWS UNBOUNDED PRECEDING) ExceptionPartition
        FROM
        (
            SELECT q.*
            , CASE WHEN IsException = 0 OR LAG(IsException) OVER (PARTITION BY Sensor ORDER BY [Measure Time]) = 1
            THEN 0 ELSE 1 END StartOfExceptionPartition
            FROM
            (
                SELECT m.Sensor, m.[Measure Time], m.Measurement, s.[Lower Threshold], s.[Upper Threshold]
                , CASE WHEN m.Measurement NOT BETWEEN s.[Lower Threshold] AND s.[Upper Threshold] THEN 1 ELSE 0 END IsException
                FROM [Measurements] m
                INNER JOIN [Sensors] s ON s.Sensor = m.Sensor
            ) q
        ) q2
    ) q3
    GROUP BY q3.Sensor, q3.ExceptionPartition
    HAVING SUM(IsException) > 0
) q4
CROSS APPLY (
    SELECT CASE WHEN [Max Measurement] > [Upper Threshold] THEN [Max Measurement] - [Upper Threshold] ELSE NULL END,
    CASE WHEN [Min Measurement] < [Lower Threshold] THEN [Min Measurement] - [Lower Threshold] ELSE NULL END
) ca (upper_delta, lower_delta);

这是该计划的屏幕截图：

很难看到细节所以我也把它上传到粘贴计划。

要了解其部分工作原理，请考虑q2派生表中传感器 B 的行：

For that sensor there are two exception periods. The first row of an exception period must be an exception by definition. If the exception period contains a row that isn't an exception then it must be after all of the exception rows. Therefore, I can get the minimum exception time by taking the minimum time value and I can get the maximum exception time by taking the minimum time value for a row that isn't an exception, or if that doesn't exist, taking the maximum time value.

Better late than never...

I promised to provide my solution to this challenge a few months ago, but since both Martin and Joe came up with very similar solutions to my original one, I decided to look for another. :-) For extra challenge, I decided to try and find one without window functions, so that it will be valid for other RDBMS as well that don't yet support window functions.

Time went by, and I honestly just forgot about this challenge, but this morning I had an hour to spare, and I happened to recall this challenge, so here is an alternative solution, without using window functions. The general idea is to find the 'nearest next normal measurements' for each exception row, and use that as a grouping expression for the GROUP BY in the outer query. More details in the code comments.

;WITH [Measurements With Thresholds and Exceptions]
AS
(
SELECT  M.*, 
        S.[Lower Threshold], 
        S.[Upper Threshold],
        CASE 
            WHEN [M].[Measurement] > [S].[Upper Threshold]
            THEN 'Upper Exception'
            WHEN [M].[Measurement] < [S].[Lower Threshold]
            THEN 'Lower Excpetion'
            ELSE NULL
        END AS Exception,
        (   SELECT  MAX([Measure Time]) 
            FROM    [Measurements] AS [M1]
            WHERE   [M1].[Sensor] = [M].[Sensor]
        ) AS [Last Measurement Time] -- Needed to simplify code for end time for ongoing exceptions
FROM    [Measurements] AS [M]
        INNER JOIN
        [Sensors] AS [S]
        ON [S].[Sensor] = [M].[Sensor] -- Save joining multiple times later
)
SELECT  [E].[Sensor],
        MIN([E].[Exception Start Time]) AS [Exception Start Time],
        [E].[Exception End Time],
        DATEDIFF(MINUTE, MIN([E].[Exception Start Time]), [E].[Exception End Time]) AS [Exception Duration (minutes)],
        MIN([E].[Measurement]) AS [Min Measurement],
        MAX([E].[Measurement]) AS [Max Measurement],
        MAX([E].[Lower Threshold]) AS [Lower Threshold],    -- Dummy aggregate, functionally dependent
        MAX([E].[Upper Threshold]) AS [Upper Threshold],    -- Dummy aggregate, functionally dependent
        CASE                                                
            WHEN    MAX([E].[Exception]) <> MIN([E].[Exception]) -- If Exceptions for period Are both over and under
            THEN    CASE
                        WHEN    (MAX([E].[Measurement]) - MAX([E].[Upper Threshold])) 
                                > 
                                (MAX([E].[Lower Threshold]) - MIN([E].[Measurement])) -- If upper Exception is larger
                        THEN    MAX([E].[Measurement]) - MAX([E].[Upper Threshold]) 
                        ELSE    MAX([E].[Lower Threshold]) - MIN([E].[Measurement])
                    END
            ELSE    CASE
                        WHEN    MAX(E.Exception) = 'Upper Exception'
                        THEN    MAX([E].[Measurement]) - MAX([E].[Upper Threshold])
                        ELSE    MIN([E].[Measurement]) - MAX([E].[Lower Threshold])
                    END
            END AS [Maximal Delta From Thresholds]
FROM    (
            SELECT  [M1].[Sensor],
                    [M1].[Lower Threshold],
                    [M1].[Upper Threshold],
                    [Measure Time] as [Exception Start Time],
                    [M1].[Exception],
                    ISNULL  (
                                (
                                SELECT  MIN([M2].[Measure Time])    -- Nearest next normal measurement
                                FROM    [Measurements With Thresholds and Exceptions] AS [M2]
                                WHERE   [M1].[Sensor] = [M2].[Sensor]
                                        AND
                                        [M2].[Measure Time] > [M1].[Measure Time] -- Next
                                        AND 
                                        [M2].[Exception] IS NULL -- And normal
                                ),
                            [M1].[Last Measurement Time] -- In case there is no next normal, i.e. ongoing exception 
                            )   AS [Exception End Time], 
                    [M1].[Measurement] 
            FROM    [Measurements With Thresholds and Exceptions] AS [M1] 
            WHERE   [M1].[Exception] IS NOT NULL -- Only exceptions
        ) AS [E]
GROUP BY    [E].[Sensor], 
            [E].[Exception End Time] -- Group all rows with the same 'normal' end time
ORDER BY    [E].[Sensor], 
            [Exception Start Time];

While Paul's CLR solution is unique and might be very efficient (I didn't test it), I was really looking for SQL solutions. Still, Kudos to Paul, and if you have a case where the benefit of using CLR outweighs the challenges it introduces, you should consider it. I usually advise avoiding CLR in SQL Server like the plague, and only use as a last resort... Also, it is not portable to other platforms.

Both Martin's and Joe's solutions come up with nearly identical execution plans, only minor operation order difference. I also find them similar in terms of clarity, so I'm granting the correct answer to Martin, but only because he published his first.

Both Martin and Joe's solutions seem to be more efficient than mine when looking at the estimated query cost. For the small sample data, the optimizer came up with this plan for my solution:

You can see that there are 5 table access operators vs. only 2 for Joe's and Martin's solutions, but on the other hand, no spooling vs. the 2 spools...

The optimizer estimated that my solution will be about twice as expensive as Joe's and Martin's; 0.056 vs. 0.032 total estimated plan cost.

So, being curious, I decided to test all solutions with a larger set:

BEGIN TRAN
INSERT INTO Measurements
SELECT  Sensor,
        DATEADD(MINUTE, ROW_NUMBER() OVER (ORDER BY column_id DESC), DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY column_id), [Measure Time])),
        Measurement
FROM    Measurements
        CROSS JOIN
        sys.all_columns
--ROLLBACK TRAN

This resulted in ~160,000 rows in the table. The estimated plan cost now increased to 30501 for my solution, vs only 3.8 for Joe's and Martin's... Here is the plan for my solution, with the large data set:

我决定运行一个实际的基准测试。我在每次执行之前清除了缓冲池，这是我笔记本电脑上的结果：

• 我的解决方案 - 9 分 33 秒
• Martin 和 Joe 的解决方案 - 4 秒 :-)

现在这是一个决定性的结果……窗口功能万岁！

我试了一下以尝试进一步优化它，但我添加此解决方案主要是出于教育目的。看来优化器的估计还差得远。

你能在不使用窗口函数的情况下找到它的优化吗？那会很有趣。

再次感谢您提供的所有解决方案，我从中学到了东西。并且......再次对（非常）迟到的回复表示抱歉。

祝大家周末愉快！