CODEWITHSUNDEEP
sqlsql-server
JosepB
JosepB

Reputation: 2315

Fastest way to calculate time periods weighted average in SQL Server

I have the following table

    timestamp                  parameter     value
    ---------------------------------------------------
    2015-09-04 10:00:00.000    par01          1
    2015-09-04 10:03:00.000    par02          2
    2015-09-04 10:06:00.000    par03          3
    2015-09-04 10:09:00.000    par04          4
    2015-09-04 10:12:00.000    par05          5
    2015-09-04 10:15:00.000    par06          6
    2015-09-04 10:18:00.000    par01          7
    2015-09-04 10:21:00.000    par02          8
    2015-09-04 10:24:00.000    par03          9
    2015-09-04 10:27:00.000    par04          10

I would like to calculate the weighted average every 15 minutes. The result must be like this:

    timestamp                  parameter     value
    ---------------------------------------------------
    2015-09-04 10:00:00.000    result1        3
    2015-09-04 10:15:00.000    result2        8

What's the fastest way? Is It possible avoiding loops?

Upvotes: 1

Views: 1022

Answers (3)

t-clausen.dk
t-clausen.dk

Reputation: 44326

This will give you the result you are requesting:

DECLARE @t table(timestamp datetime, parameter char(5),  value int)

INSERT @t values
('2015-09-04 10:00:00.000','par01',1),
('2015-09-04 10:03:00.000','par02',2),
('2015-09-04 10:06:00.000','par03',3),
('2015-09-04 10:09:00.000','par04',4),
('2015-09-04 10:12:00.000','par05',5),
('2015-09-04 10:15:00.000','par06',6),
('2015-09-04 10:18:00.000','par01',7),
('2015-09-04 10:21:00.000','par02',8),
('2015-09-04 10:24:00.000','par03',9),
('2015-09-04 10:27:00.000','par04',10)

SELECT
  dateadd(minute, datediff(minute, 0,timestamp)/15*15, 0) timestamp,
  'Result' + cast(row_number() over (order by datediff(minute, 0,timestamp)/15)
    as varchar(10)) parameter,
  avg(value) value
FROM @t
GROUP BY datediff(minute, 0,timestamp)/15

Result:

timestamp         parameter  value
2015-09-04 10:00  Result1    3
2015-09-04 10:15  Result2    8

EDIT here is a method to calculated weighted average. Been a learning experience for me:

;WITH CTE as
(
  SELECT
    cast(coalesce(lead(timestamp)  over (order by timestamp), 
      dateadd(minute, datediff(minute, 0,timestamp)/15*15, '00:15'))
      - timestamp as float)*24*60/15*value x,
    dateadd(minute, datediff(minute, 0,timestamp)/15*15, 0) truncatedtime
FROM @t
)
SELECT
  sum(x) weighted_average,
  truncatedtime
FROM
  cte
GROUP BY
  truncatedtime

Upvotes: 2

Zohar Peled
Zohar Peled

Reputation: 82474

try this:

SELECT  MIN(timestamp) as timestamp, 
        'result' + cast(count(*) over(order by min(timestamp)) as varchar(10)) as parameter,    
        AVG(value) as value
FROM tbl
GROUP BY CAST(timestamp as Date), 
         DATEPART(Hour, timestamp), 
         DATEPART(Minute, timestamp) / 15

see fiddle here

Upvotes: 2

Steve Ford
Steve Ford

Reputation: 7753

Try this:

CREATE TABLE #t1
(
    [Timestamp] datetime,
    Parameter VARCHAR(50),
    Value int
)

INSERT INTO #t1
VALUES ('2015-09-04 10:00:00.000', 'par01',1),
    ('2015-09-04 10:03:00.000', 'par02',2),
    ('2015-09-04 10:06:00.000', 'par03',3),
    ('2015-09-04 10:09:00.000', 'par04',4),
    ('2015-09-04 10:12:00.000', 'par05',5),
    ('2015-09-04 10:15:00.000', 'par06',6),
    ('2015-09-04 10:18:00.000', 'par01',7),
    ('2015-09-04 10:21:00.000', 'par02',8),
    ('2015-09-04 10:24:00.000', 'par03',9),
    ('2015-09-04 10:27:00.000', 'par04',10)


;WITH CTE
As
(
    SELECT
           DATEADD( minute, 
                    ( DATEDIFF(minute, CONVERT(char(8),[Timestamp],112), [Timestamp]) / 15 ) * 15,
                    CONVERT(char(8),[Timestamp],112) ) as Timestamp_truncated,
           [Timestamp],
           parameter,
           value
    FROM #t1
)
SELECT Timestamp_truncated AS [Timestamp], AVG(Value) as Value
FROM CTE 
GROUP BY Timestamp_truncated

Upvotes: 2

Related Questions