I have a table with 3 columns.
id bigint,
rate decimal(18,2),
dateCreated datetime
I need the results to be grouped by 10 second interval.
Something like :
2019-12-15:00 13:20:10 avg(rate)
2019-12-15:00 13:20:20 avg(rate)
2019-12-15:00 13:20:30 avg(rate)
etc....
I am using SQL server 2017.
How do I accomplish that ?
Thank you in advance.
Perhaps truncating the datetime
Example
Select TrimTime = convert(varchar(18),dateCreated,120)+'0'
,AvgRate = avg(Rate)
From YourTable
Group By convert(varchar(18),dateCreated,120)
Imagine you have a time table... let's call it TimeHacks...and it's divided into 10 second time spans. Maybe it looks something like
create table TimeHacks
(
IntervalNumber bigint,
DateTimeAtInterval datetime
)
Then it would be easy to join it to your table:
select
t.IntervalNumber,
avg( y.rate )
from
dbo.TimeHacks i
inner join
dbo.YourTable y
on
datediff( s, y.DateCreated, i.DateTimeAtInterval ) between 0 and 9
group by
i.IntervalNumber
...and so the question is...do you make a table that has all those time hacks in it, or is there a better way?
So...now let's say that TimeHacks table isn't a table at all, but a SQL Server table valued function that takes 3 arguments, a start datetime, an end datetime and a number of seconds between hacks.
You could say something like:
select
i.IntervalNumber,
avg( y.rate )
from
dbo.TimeHacks( '2019-1-15 10:10:10', '2019-1-16 10:10:10', 10 )
inner join
dbo.YourTable y
on
datediff( s, y.DateCreated, i.DateTimeAtInterval ) between 0 and 9
group by
i.id
Hmmm...but how to make that TimeHacks function? Let's imagine that we also have a table valued function that can generate arbitrary sequences of integers from any start to any end... like dbo.FromTo( start bigint, end bigint ). Then, our TimeHacks could be something like:
create function dbo.TimeHacks( @start datetime, @end datetime, @seconds int ) returns table as return
select
ft.x as IntervalNumber,
dateadd( s, ft.x * @seconds, @start ) DateTimeAtInterval
from
dbo.FromTo( 0, datediff( s, @start, @end ) / @seconds ) ft
...and finally...how would we generate a sequence of numbers that forms the basis for our dbo.TimeHacks function? There's a number of answers to that question...some good ones here. I learned a lot from those answers. You might, too. Anyway, I've got a favorite version I've tweaked out of those answers. It makes a sequence of numbers without doing any I/O and can produce any sequence in order in the range of integers...and it's screaming fast:
create function dbo.FromTo( @start bigint, @end bigint ) returns table as return
with
x0 as (select x from (values (0),(0x00000001),(0x00000002),(0x00000003),(0x00000004),(0x00000005),(0x00000006),(0x00000007),(0x00000008),(0x00000009),(0x0000000A),(0x0000000B),(0x0000000C),(0x0000000D),(0x0000000E),(0x0000000F)) as x0(x)),
x1 as (select x from (values (0),(0x00000010),(0x00000020),(0x00000030),(0x00000040),(0x00000050),(0x00000060),(0x00000070),(0x00000080),(0x00000090),(0x000000A0),(0x000000B0),(0x000000C0),(0x000000D0),(0x000000E0),(0x000000F0)) as x1(x)),
x2 as (select x from (values (0),(0x00000100),(0x00000200),(0x00000300),(0x00000400),(0x00000500),(0x00000600),(0x00000700),(0x00000800),(0x00000900),(0x00000A00),(0x00000B00),(0x00000C00),(0x00000D00),(0x00000E00),(0x00000F00)) as x2(x)),
x3 as (select x from (values (0),(0x00001000),(0x00002000),(0x00003000),(0x00004000),(0x00005000),(0x00006000),(0x00007000),(0x00008000),(0x00009000),(0x0000A000),(0x0000B000),(0x0000C000),(0x0000D000),(0x0000E000),(0x0000F000)) as x3(x)),
x4 as (select x from (values (0),(0x00010000),(0x00020000),(0x00030000),(0x00040000),(0x00050000),(0x00060000),(0x00070000),(0x00080000),(0x00090000),(0x000A0000),(0x000B0000),(0x000C0000),(0x000D0000),(0x000E0000),(0x000F0000)) as x4(x)),
x5 as (select x from (values (0),(0x00100000),(0x00200000),(0x00300000),(0x00400000),(0x00500000),(0x00600000),(0x00700000),(0x00800000),(0x00900000),(0x00A00000),(0x00B00000),(0x00C00000),(0x00D00000),(0x00E00000),(0x00F00000)) as x5(x)),
x6 as (select x from (values (0),(0x01000000),(0x02000000),(0x03000000),(0x04000000),(0x05000000),(0x06000000),(0x07000000),(0x08000000),(0x09000000),(0x0A000000),(0x0B000000),(0x0C000000),(0x0D000000),(0x0E000000),(0x0F000000)) as x6(x)),
x7 as (select x from (values (0),(0x10000000),(0x20000000),(0x30000000),(0x40000000),(0x50000000),(0x60000000),(0x70000000)) as x7(x))
select s.x
from
(
select
x7.x
| x6.x
| x5.x
| x4.x
| x3.x
| x2.x
| x1.x
| x0.x
+ @start
x
from
x7
inner remote join x6 on x6.x <= @end - @start and x7.x <= @end - @start
inner remote join x5 on x5.x <= @end - @start
inner remote join x4 on x4.x <= @end - @start
inner remote join x3 on x3.x <= @end - @start
inner remote join x2 on x2.x <= @end - @start
inner remote join x1 on x1.x <= @end - @start
inner remote join x0 on x0.x <= @end - @start
) s
where @end >= s.x
The way this function works borders on magic. It's based on the question I referenced, and in particular it builds on the answers by Brian Pressler and mechoid.
This is actually a very good and somewhat complex question. I'm sorry you were treated so. Take a look at this Stack Overflow post for a possible way of attacking it and good luck.
