Desafio SQL - O Relatório de Exceções do Limite do Sensor

Eu provavelmente usaria algo como o abaixo.

Ele é capaz de usar a ordem do índice e evitar uma classificação até chegar ao final GROUP BY(para o qual usa um agregado de fluxo, para mim)

Em princípio, esta operação de agrupamento final não é realmente necessária. Deve ser possível ler um fluxo de entrada ordenado por Sensor, MeasureTimee produzir os resultados desejados em um modo de streaming, mas acho que você precisaria escrever um procedimento SQLCLR para isso.

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] 

Uma implementação de função CLR SQL de streaming lendo linhas na Sensor, [Measure Time]ordem:

Fonte

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; }
    }
}

Script de implantação

Crie bits de montagem (um pouco longos demais para postar em linha)

Nota: devido a uma limitação, este assembly requer EXTERNAL_ACCESSpermissão, embora apenas leia do mesmo banco de dados. Para fins de teste, é suficiente tornar o banco de dados TRUSTWORTHY, embora existam boas razões para não fazer isso em produção - assine o assembly.

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;

Consulta

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];

Os parâmetros são necessários para que a função saiba como se conectar aos dados de origem.

Plano de execução

Resultados

╔══════════╦══════════════════════╦═══════════════ ══════╦══════════════════════════════╦════════════ ═════╦═════════════════╦═════════════════╦════════ ═════════╦═══════════════════════════════╗
║ Sensor ║ Hora de início da exceção ║ Hora de término da exceção ║ Duração da exceção (minutos) ║ Medição mínima ║ Medição máxima ║ Limiar inferior ║ Limiar_superior ║ Delta máximo dos limites ║
╠══════════╬══════════════════════╬═══════════════ ══════╬══════════════════════════════╬════════════ ═════╬═════════════════╬═════════════════╬════════ ═════════╬═══════════════════════════════╣
║ 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 ║ 01-01-2016 08:00:00 ║ 01-01-2016 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 ║
╚══════════╩══════════════════════╩═══════════════ ══════╩══════════════════════════════╩════════════ ═════╩═════════════════╩═════════════════╩════════ ═════════╩══════════════════════════════════╝══════

Escrevi minha tentativa de solução sem olhar para outras respostas, mas não estou surpreso ao ver que minha consulta é muito semelhante à de Martin. Parece que consigo os resultados certos com uma função de janela a menos, mas duvido que haja muita diferença no desempenho. Segue o código completo:

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);

Aqui está uma captura de tela do plano:

É difícil ver os detalhes, então também enviei para Paste The Plan .

Para ver como parte dele funciona, considere as linhas do Sensor B na q2tabela derivada:

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:

Eu decidi executar um benchmark real. Limpei o pool de buffers antes de cada execução e aqui estão os resultados no meu laptop:

• Minha solução - 9 minutos e 33 segundos
• Soluções de Martin e Joe - 4 segundos :-)

Agora esse é um resultado decisivo... Viva as funções da janela!!

Brinquei um pouco para tentar otimizá-lo ainda mais, mas adicionei essa solução principalmente para fins educacionais. Parece que o otimizador está longe de suas estimativas.

Você pode encontrar uma otimização para isso sem usar o Window Functions?, Isso seria interessante de ver.

Obrigado novamente por todas as suas soluções, aprendi com isso. E... desculpe novamente pela resposta (muito) atrasada.

Tenham todos um ótimo final de semana!