我得到了一张表格,该表格每一分钟带有DateTime时间戳。我需要一个函数来返回总聚集15分钟。
分钟行包含以下列:
因此,如果我每分钟有60行,那么结果应该是4行,并带有:
我完全不知道该如何实现。有人可以指出SQL为此类任务提供的功能吗?
分钟行示例:
打开 | 高的 | 低的 | 关闭 | 时间戳记 |
---|---|---|---|---|
10 | 17 | 17 | 9 | 2021-02-18-15:00:00 |
9 | 13 | 4 | 12 | 2021-02-18-15:01:00 |
12 | 21 | 11 | 17 | 2021-02-18-15:02:00 |
... | ... | ... | ... | ... |
22 | 23 | 18岁 | 21 | 2021-02-18-15:15:00 |
所需输出行的示例
打开 | 高的 | 低的 | 关闭 | 时间戳记 |
---|---|---|---|---|
10 | 23 | 4 | 22 | 2021-02-18-15:00: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);
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句