sql server - Using GROUP BY with FIRST_VALUE and LAST_VALUE

Question

I'm working with some data that is currently stored in 1 minute intervals that looks like this:

CREATE TABLE #MinuteData
    (
      [Id] INT ,
      [MinuteBar] DATETIME ,
      [Open] NUMERIC(12, 6) ,
      [High] NUMERIC(12, 6) ,
      [Low] NUMERIC(12, 6) ,
      [Close] NUMERIC(12, 6)
    );

INSERT  INTO #MinuteData
        ( [Id], [MinuteBar], [Open], [High], [Low], [Close] )
VALUES  ( 1, '2015-01-01 17:00:00', 1.557870, 1.557880, 1.557870, 1.557880 ),
        ( 2, '2015-01-01 17:01:00', 1.557900, 1.557900, 1.557880, 1.557880 ),
        ( 3, '2015-01-01 17:02:00', 1.557960, 1.558070, 1.557960, 1.558040 ),
        ( 4, '2015-01-01 17:03:00', 1.558080, 1.558100, 1.558040, 1.558050 ),
        ( 5, '2015-01-01 17:04:00', 1.558050, 1.558100, 1.558020, 1.558030 ),
        ( 6, '2015-01-01 17:05:00', 1.558580, 1.558710, 1.557870, 1.557950 ),
        ( 7, '2015-01-01 17:06:00', 1.557910, 1.558120, 1.557910, 1.557990 ),
        ( 8, '2015-01-01 17:07:00', 1.557940, 1.558250, 1.557940, 1.558170 ),
        ( 9, '2015-01-01 17:08:00', 1.558140, 1.558200, 1.558080, 1.558120 ),
        ( 10, '2015-01-01 17:09:00', 1.558110, 1.558140, 1.557970, 1.557970 );

SELECT  *
FROM    #MinuteData;

DROP TABLE #MinuteData;

The values track currency exchange rates, so for each minute interval (bar), there is the Open price as the minute started and a Close price for the minute end. The High and Low values represent the highest and lowest rate during each individual minute.

Desired Output

I'm looking to reformat this data in to 5 minute intervals to produce the following output:

MinuteBar                Open       Close       Low         High
2015-01-01 17:00:00.000  1.557870   1.558030    1.557870    1.558100
2015-01-01 17:05:00.000  1.558580   1.557970    1.557870    1.558710

This takes the Open value from the first minute of the 5, the Close value from the last minute of the 5. The High and Low values represent the highest high and lowest low rates across the 5 minute period.

Current Solution

I have a solution that does this (below), but it feels inelegant as it relies on id values and self joins. Also, I intend to run it on much larger datasets so I was looking to do it in a more efficient manner if possible:

-- Create a column to allow grouping in 5 minute Intervals
SELECT  Id, MinuteBar, [Open], High, Low, [Close], 
DATEDIFF(MINUTE, '2015-01-01T00:00:00', MinuteBar)/5 AS Interval
INTO    #5MinuteData
FROM    #MinuteData
ORDER BY minutebar

-- Group by inteval and aggregate prior to self join
SELECT  Interval ,
        MIN(MinuteBar) AS MinuteBar ,
        MIN(Id) AS OpenId ,
        MAX(Id) AS CloseId ,
        MIN(Low) AS Low ,
        MAX(High) AS High
INTO    #DataMinMax
FROM    #5MinuteData
GROUP BY Interval;

-- Self join to get the Open and Close values
SELECT  t1.Interval ,
        t1.MinuteBar ,
        tOpen.[Open] ,
        tClose.[Close] ,
        t1.Low ,
        t1.High
FROM    #DataMinMax t1
        INNER JOIN #5MinuteData tOpen ON tOpen.Id = OpenId
        INNER JOIN #5MinuteData tClose ON tClose.Id = CloseId;

DROP TABLE #DataMinMax
DROP TABLE #5MinuteData

Rework Attempt

Instead of the above queries, I've been looking at using FIRST_VALUE and LAST_VALUE, as it seems to be what I'm after, but I can't quite get it working with the grouping that I'm doing. There might be a better solution than what I'm trying to do, so I'm open to suggestions. Currently I'm trying to do this:

SELECT  MIN(MinuteBar) MinuteBar5 ,
        FIRST_VALUE([Open]) OVER (ORDER BY MinuteBar) AS Opening,
        MAX(High) AS High ,
        MIN(Low) AS Low ,
        LAST_VALUE([Close]) OVER (ORDER BY MinuteBar) AS Closing ,
        DATEDIFF(MINUTE, '2015-01-01 00:00:00', MinuteBar) / 5 AS Interval
FROM    #MinuteData
GROUP BY DATEDIFF(MINUTE, '2015-01-01 00:00:00', MinuteBar) / 5

This gives me the below error, which is related to the FIRST_VALUE and LAST_VALUE as the query runs if I remove those lines:

Column '#MinuteData.MinuteBar' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Answer 1

SELECT 
    MIN(MinuteBar) AS MinuteBar5,
    Opening,
    MAX(High) AS High,
    MIN(Low) AS Low,
    Closing,
    Interval
FROM 
(
    SELECT FIRST_VALUE([Open]) OVER (PARTITION BY DATEDIFF(MINUTE, '2015-01-01 00:00:00', MinuteBar) / 5 ORDER BY MinuteBar) AS Opening,
           FIRST_VALUE([Close]) OVER (PARTITION BY DATEDIFF(MINUTE, '2015-01-01 00:00:00', MinuteBar) / 5 ORDER BY MinuteBar DESC) AS Closing,
           DATEDIFF(MINUTE, '2015-01-01 00:00:00', MinuteBar) / 5 AS Interval,
           *
    FROM #MinuteData
) AS T
GROUP BY Interval, Opening, Closing

A solution close to your current one. There are two places you did wrong.

1. FIRST_VALUE AND LAST_VALUE are Analytic Functions, which work on a window or partition, instead of a group. You can run the nested query alone and see its result.

2. LAST_VALUE is the last value of current window, which is not specified in your query, and a default window is rows from the first row of current partition to current row. You can either use FIRST_VALUE with deseeding order or specify a window

   LAST_VALUE([Close]) OVER (PARTITION BY DATEDIFF(MINUTE, '2015-01-01 00:00:00', MinuteBar) / 5 
               ORDER BY MinuteBar 
               ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS Closing,