SQL按日期聚合

Question

在SQL查询中查找某些价格值时遇到问题。我在错误的地方放了一条注释。它不应该是平均值，但可以找到打开和关闭的第一个和最后一个日期值。

SELECT
DATEADD(MINUTE, DATEDIFF(MINUTE, 0, aa.date) / 5 * 5, 0) AS [Date],     
AVG(aa.[open]) AS [Open], /** this is wrong it should be open from minimal date of the group*/
MAX(aa.hight) AS [High],
MIN(aa.low) AS Low,
AVG(aa.[close]) AS [Close],   /** this is wrong it should be close from maximal date of the group date */
SUM(aa.[volume]) AS [Volume]
FROM
(select top (10000) * from [dbo].[kraken_btc] ) AS aa 
GROUP BY
DATEADD(MINUTE, DATEDIFF(MINUTE, 0, aa.[date]) / 5 * 5, 0) 
ORDER BY
Date 

我删除了所有无用的添加内容，并为其他人发布了本应给出的回复，这个其他问题帮助了我如何在OHLC数据中汇总价格

Answer 1

所以解决方案是：

-- SET 
DECLARE @Period int ;
SET @Period = 5 ; 
select  
DATEADD(MINUTE, DATEDIFF(MINUTE, 0,[Date]) / @Period * @Period, 0) AS AGG_DATE,
min([Date]) Min_Date,
max([Date]) Max_Date,
max(high) as Highest,
min(low) as Lowest,
min(case when rn_asc = 1 then [open] end) as [Open],
min(case when rn_desc = 1 then [close] end) as [Close]
from    (
select  row_number() over (partition by  DATEADD(MINUTE, DATEDIFF(MINUTE, 0,[Date]) / @Period * @Period, 0)
order by [Date]) as rn_asc,
row_number() over (partition by DATEADD(MINUTE, DATEDIFF(MINUTE, 0,[Date]) / @Period * @Period, 0)
order by [Date] desc) as rn_desc,               *
from   OHLC
) as SubQueryAlias
group by
DATEADD(MINUTE, DATEDIFF(MINUTE, 0,[Date]) / @Period * @Period, 0)