聚合总是15行到1行

Question

我得到了一个表，每一分钟有一行，时间戳为DateTime。我需要一个函数总是返回15分钟累计。

分钟行包含以下列:

• 打开，高，低关闭，时间戳

所以如果我每分钟有60行，结果应该是4行

• 第一分钟打开
• High -所有High
中最高的
• Low -所有Low
中最低的
• 最后一分钟结束
• 最后一分钟的时间戳

我一点也不知道这是怎么实现的。有人能指出SQL为这样的任务提供的函数吗?

分钟行示例:

<表类> 打开 高 低 关闭 时间戳 tbody><<tr>10171792021 - 02 - 18 - 15:00:009134122021 - 02 - 18 - 15:01:00122111172021 - 02 - 18 - 15:02:00……………222318212021 - 02 - 18 - 15:15:00

大量借用@GordonLinoff的答案，但做了大量更正:

select
max(case when seqnum_asc = 1 then [open] end) as [open],
max(high) as high,
min(low) as low,
max(case when seqnum_desc = 1 then [close] end) as [close],
min(timestamp) EarliestTimeStamp,
max(timestamp) LatestTimeStamp,
count(1) RowsSummarised
from 
(select MyTable.*,
row_number() over (partition by convert(date, timestamp), datepart(hour, timestamp), datepart(minute, timestamp) / 15 order by timestamp) as seqnum_asc,
row_number() over (partition by convert(date, timestamp), datepart(hour, timestamp), datepart(minute, timestamp) / 15 order by timestamp desc) as seqnum_desc
from MyTable t
) t
group by convert(date, timestamp), datepart(hour, timestamp), datepart(minute, timestamp) / 15
order by min(timestamp);

这假设——与提供的数据相反——OP实际上希望每组正好有15行，而不是16行。也就是说，00-14分属于一组，15-29分属于另一组，以此类推。

可以使用窗口函数和聚合。一种方法是:

select min(timestamp), max(timestamp),
max(case when seqnum_asc = 1 then open end) as open,
max(case when seqnum_desc = 1 then close end) as close,
max(high), min(low)
from (select t.*,
row_number() over (partition by convert(date, timestamp), datepart(hour, timestamp), datepart(minute, timestamp) / 15 order by timestamp) as seqnum_asc,
row_number() over (partition by convert(date, timestamp), datepart(hour, timestamp), datepart(minute, timestamp) / 15 order by timestamp) as seqnum_desc
from t
) t
group by convert(date, timestamp), datepart(hour, timestamp), datepart(minute, timestamp) / 15
order by min(timestamp);

这是一个db<>小提琴