CODEWITHSUNDEEP
sqlsql-servertimeintervals
Bayo Alen
Bayo Alen

Reputation: 341

With SQL Server, I want to summarize data by averaging at given interval time

The data is as follow:

Time_Stamp(Datetime)     Value (real) 
---------                ----- 
12:01                      1.3 
12:02                      1.7 
12:04                      2.0 
12:08                      1.8 
12:11                      1.1 
12:12                      2.0

I would like to be able to summarize this data by averaging the value at regular intervals. So if I choose an interval of 5 minutes for instance the output would be 

Timestamp  Value 
---------  ------ 
12:00      1.66 
12:05      1.8 
12:10      1.55

Regards

Upvotes: 1

Views: 78

Answers (3)

David Rushton
David Rushton

Reputation: 5030

One technique is to create a time table. You can add whatever fields you need to this table, giving you something to group by.

This example uses recursion to return a simple time table, for illustration purposes. In your database, I would recommend creating a proper table.

-- Sample time table.
WITH DimTime AS
    (
        -- Returns one record for each minute of the day.
            SELECT
                CAST('00:00:00' AS TIME(0)) AS TimeKey,
                0                           AS [Hour],
                1                           AS AM

        UNION ALL

            SELECT
                c.NextMinute                                                    AS TimeKey,
                DATEPART(HOUR, c.NextMinute)                                    AS [Hour],
                CASE WHEN DATEPART(HOUR, c.NextMinute) < 12 THEN 1 ELSE 0 END   AS AM

            FROM
                DimTime AS t
                    CROSS APPLY
                        (
                            VALUES
                                (DATEADD(MINUTE, 1, TimeKey))
                        ) AS c(NextMinute)
            WHERE
                t.TimeKey < CAST('23:59:00' AS TIME(0))
    )
SELECT
    *
FROM
    DimTime
OPTION
    (MAXRECURSION 1440)
;

Returns

TimeKey     Hour    AM
00:00:00    0       1
00:01:00    0       1
...
11:59:00    11      1
12:00:00    12      0
...
23:59:00    23      0

Adding a column calling something along the lines of FiveMinuteInterval would simplify your final query.

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 81930

I often use a TVF to create dynamic Date/Time Ranges. A tally table would do the trick as well. The UDF is faster than a recursive cte, and offers a bit more functionality i.e. you define date range, DatePart and increment.

Declare @YourTable table (Timestamp time,Value money)
Insert Into @YourTable values
('12:01',1.3),
('12:02',1.7), 
('12:04',2.0),
('12:08',1.8), 
('12:11',1.1), 
('12:12',2.0)

Select TimeStamp=R1
      ,Average=Avg(Value)
 From  (Select RetSeq,R1=cast(RetVal as Time),R2=cast(DateAdd(MI,5,RetVal) as Time) from [dbo].[udf-Range-Date]('1900-01-01','1900-01-02','MI',5)) A
 Join  @YourTable B on B.TimeStamp>=A.R1 and B.TimeStamp<A.R2
 Group By R1
 Order By R1

Returns

TimeStamp           Average
12:00:00.0000000    1.6666
12:05:00.0000000    1.80
12:10:00.0000000    1.55

The UDF if needed

CREATE FUNCTION [dbo].[udf-Range-Date] (@R1 datetime,@R2 datetime,@Part varchar(10),@Incr int)
Returns Table
Return (
    with cte0(M)   As (Select 1+Case @Part When 'YY' then DateDiff(YY,@R1,@R2)/@Incr When 'QQ' then DateDiff(QQ,@R1,@R2)/@Incr When 'MM' then DateDiff(MM,@R1,@R2)/@Incr When 'WK' then DateDiff(WK,@R1,@R2)/@Incr When 'DD' then DateDiff(DD,@R1,@R2)/@Incr When 'HH' then DateDiff(HH,@R1,@R2)/@Incr When 'MI' then DateDiff(MI,@R1,@R2)/@Incr When 'SS' then DateDiff(SS,@R1,@R2)/@Incr End),
         cte1(N)   As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
         cte2(N)   As (Select Top (Select M from cte0) Row_Number() over (Order By (Select NULL)) From cte1 a, cte1 b, cte1 c, cte1 d, cte1 e, cte1 f, cte1 g, cte1 h ),
         cte3(N,D) As (Select 0,@R1 Union All Select N,Case @Part When 'YY' then DateAdd(YY, N*@Incr, @R1) When 'QQ' then DateAdd(QQ, N*@Incr, @R1) When 'MM' then DateAdd(MM, N*@Incr, @R1) When 'WK' then DateAdd(WK, N*@Incr, @R1) When 'DD' then DateAdd(DD, N*@Incr, @R1) When 'HH' then DateAdd(HH, N*@Incr, @R1) When 'MI' then DateAdd(MI, N*@Incr, @R1) When 'SS' then DateAdd(SS, N*@Incr, @R1) End From cte2 )

    Select RetSeq = N+1
          ,RetVal = D 
     From  cte3,cte0 
     Where D<=@R2
)
/*
Max 100 million observations -- Date Parts YY QQ MM WK DD HH MI SS
Syntax:
Select * from [dbo].[udf-Range-Date]('2016-10-01','2020-10-01','YY',1) 
Select * from [dbo].[udf-Range-Date]('2016-01-01','2017-01-01','MM',1)

Upvotes: 2

Teja
Teja

Reputation: 13524

SELECT Timestamp,
       AVG( VALUE ) AS Value
  FROM  
    (
        SELECT CASE WHEN Timestamp BETWEEN 12:00 AND 12:05 THEN 12:00
                    WHEN Timestamp BETWEEN 12:05 AND 12:10 THEN 12:05
                    WHEN Timestamp BETWEEN 12:10 AND 12:15 THEN 12:10 
                END AS Timestamp,
               VALUE
          FROM data
    )
GROUP BY Timestamp;

Upvotes: 1

Related Questions