CODEWITHSUNDEEP
sqlsql-server
Shintaro
Shintaro

Reputation: 69

Aggregate always 15 rows to 1 row

I got a table with one row for each minute with a DateTime timestamp. I need a function to return always 15 minutes aggregated.

The minute rows have the following columns:

So if I have 60 rows for each one minute, the result should be 4 rows with:

I do not have the slightest idea how this could be achievable. Can somebody point to the functions SQL provides for such a task?

Example minute rows:

Open High Low Close Timestamp
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

Example of the desired output row

Open High Low Close Timestamp
10 23 4 22 2021-02-18-15:00:00

Upvotes: 0

Views: 83

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270503

This assumes -- contrary to the provided data -- that the OP really wants exactly 15 rows per group rather than 16. That is minutes 00-14 are in one group, 15-29 in another, and so on.

You can use window functions and aggregation. One method is:

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);

Here is a db<>fiddle.

Upvotes: 1

Steve Lovell
Steve Lovell

Reputation: 2564

Borrowing heavily from @GordonLinoff's answer, but with numerous corrections:

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);

Upvotes: 1

Related Questions