5

I've added a solution without using window functions and a benchmark with a large data set below Martin's Answer

This is a followup thread to GROUP BY using columns not in the SELECT list - when is this practical, elegant or powerful?

In my solution to this challenge, I use a query that groups by an expression that is not part of the select list. This is frequently used with window functions, when the logical grouping element involves data from other rows.

Perhaps this is an overkill as an example, but I thought you may find the challenge interesting in its own right. I'll wait with posting my solution, maybe some of you can come up with better ones.

Challenge

We have a table of sensors that periodically logs reading values. There is no guarantee on sample times being in monotonous intervals.

You need to write a query that will report on the 'exceptions', meaning the times that the sensors reported out-of-threshold reading, either low or high. Each period of time the sensor was reporting over or under the threshold values, is considered an 'exception'. Once the reading got back to normal, the exception ends.

Sample tables and data

The script is in T-SQL, and is part of my training materials.

Here is a link to the SQLFiddle.

------------------------------------------
-- Sensor Thresholds - 1 - Setup Example --
------------------------------------------

CREATE TABLE [Sensors]
(
    [Sensor] NVARCHAR(10) NOT NULL,
    [Lower Threshold] DECIMAL(7,2) NOT NULL,
    [Upper Threshold] DECIMAL(7,2) NOT NULL,
    CONSTRAINT [PK Sensors] 
        PRIMARY KEY CLUSTERED ([Sensor]),
    CONSTRAINT [CK Value Range]
        CHECK ([Upper Threshold] > [Lower Threshold])
);
GO

INSERT INTO [Sensors]
( 
    [Sensor] ,
    [Lower Threshold] ,
    [Upper Threshold]
)
VALUES  (N'Sensor A', -50, 50 ),
        (N'Sensor B', 40, 80),
        (N'Sensor C', 0, 100);
GO

CREATE TABLE [Measurements]
(
    [Sensor] NVARCHAR(10) NOT NULL,
    [Measure Time] DATETIME2(0) NOT NULL,
    [Measurement] DECIMAL(7,2) NOT NULL,
    CONSTRAINT [PK Measurements] 
        PRIMARY KEY CLUSTERED ([Sensor], [Measure Time]),
    CONSTRAINT [FK Measurements Sensors] 
        FOREIGN KEY ([Sensor]) 
        REFERENCES [Sensors]([Sensor])
);
GO

INSERT INTO [Measurements]
( 
    [Sensor] ,
    [Measure Time] ,
    [Measurement]
)
VALUES  ( N'Sensor A', N'20160101 08:00', -9), 
        ( N'Sensor A', N'20160101 09:00', 30), 
        ( N'Sensor A', N'20160101 10:30', 59), 
        ( N'Sensor A', N'20160101 23:00', 66),  
        ( N'Sensor A', N'20160102 08:00', 48), 
        ( N'Sensor A', N'20160102 11:30', 08), 
        ( N'Sensor B', N'20160101 08:00', 39), -- Note that this exception range has both over and under....
        ( N'Sensor B', N'20160101 10:30', 88), 
        ( N'Sensor B', N'20160101 13:00', 75), 
        ( N'Sensor B', N'20160102 08:00', 95),  
        ( N'Sensor B', N'20160102 17:00', 75), 
        ( N'Sensor C', N'20160101 09:00', 01), 
        ( N'Sensor C', N'20160101 10:00', -1),  
        ( N'Sensor C', N'20160101 18:00', -2), 
        ( N'Sensor C', N'20160101 22:00', -2), 
        ( N'Sensor C', N'20160101 23:30', -1);
GO

Expected Result

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
*/
7

I'd probably use something like the below.

It is able to use index order and avoid a sort until it gets to the final GROUP BY (which it uses a stream aggregate for, for me)

In principle this final grouping operation isn't actually needed. It should be possible to read an input stream ordered by Sensor, MeasureTime and output the desired results in a streaming fashion but I think you would need to write a SQLCLR procedure for that.

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] 

enter image description here

6

A streaming SQL CLR function implementation reading rows in Sensor, [Measure Time] order:

Source

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

Deployment script

Create assembly bits (slightly too long to post inline)

Note: due to a limitation, this assembly requires EXTERNAL_ACCESS permission, though it only reads from the same database. For testing purposes, it is sufficient to make the database TRUSTWORTHY, though there are good reasons not to do this in production - sign the assembly instead.

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;

Query

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

The parameters are needed so the function knows how to connect to the source data.

Execution plan

CLR function plan

Results

╔══════════╦══════════════════════╦═════════════════════╦══════════════════════════════╦═════════════════╦═════════════════╦═════════════════╦═════════════════╦═══════════════════════════════╗
║  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                          ║
╚══════════╩══════════════════════╩═════════════════════╩══════════════════════════════╩═════════════════╩═════════════════╩═════════════════╩═════════════════╩═══════════════════════════════╝
2

I wrote my attempt at a solution without looking at other answers, but I'm not surprised to see that my query is very similar to Martin's. I seem to get the right results with one fewer window function but I doubt there will be much of a difference with performance. Here's the full code:

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

Here's a screenshot of the plan:

enter image description here

It's hard to see the details so I also uploaded it to Paste The Plan.

To walk through how part of it works, consider rows for Sensor B in the q2 derived table:

enter image description here

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.

1

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:

Execution Plan, no window function, small data set

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:

No window functions, large data set

I decided to run an actual benchmark. I cleared the buffer pool before each execution, and here are the results on my laptop:

  • My solution - 9 minutes and 33 seconds
  • Martin's and Joe's solutions - 4 Seconds :-)

Now that's a decisive result... Long live window functions!!

I played around a bit to try and optimize it further, but I added this solution mostly for educational purposes. It seems the optimizer is way off in it's estimations.

Can you find an optimization for it without using Window Functions?, That would be interesting to see.

Thanks again for all your solutions, I learned from it. And... sorry again for the (very) late response.

Have a wonderful weekend everyone!

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.