将GROUP BY与FIRST_VALUE和LAST_VALUE一起使用

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

接近当前解决方案的解决方案.有两个地方你做错了.

FIRST_VALUE和LAST_VALUE是分析函数,它们在窗口或分区而不是组上工作.您可以单独运行嵌套查询并查看其结果.

LAST_VALUE是当前窗口的最后一个值,在查询中未指定,默认窗口是从当前分区的第一行到当前行的行.您可以将FIRST_VALUE与deseeding order一起使用,也可以指定一个窗口

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,