2

My table looks like this:

CREATE TABLE [dbo].[TimeSeries](
[LOCID] [int] NOT NULL,
[Date] DateTime,
[YEAR] int,
[MONTH] tinyint, 
[RTT] [int]
PRIMARY KEY ( [LOCID] ASC,[Date] ASC)
 )

It contains 12 rows(or MONTHS) per LOCID, per YEAR.

I have the following query:

SELECT 
    LOCID,
    [Year],
    [Date],
    AVG(cast(RTT as float)) OVER (
         PARTITION BY LOCID ORDER BY Date 
             ROWS BETWEEN CURRENT ROW AND 4 FOLLOWING ) AS avgRTT
FROM TimeSeries

example data:

LOCID   Date    Year    Month   RTT
1   01/01/1982  1982    1   58
1   01/02/1982  1982    2   63
1   01/03/1982  1982    3   34
1   01/04/1982  1982    4   27
1   01/05/1982  1982    5   6
1   01/06/1982  1982    6   4
1   01/07/1982  1982    7   3
1   01/08/1982  1982    8   14
1   01/09/1982  1982    9   22
1   01/10/1982  1982    10  16
1   01/11/1982  1982    11  17
1   01/12/1982  1982    12  44
1   01/01/1983  1983    1   58
1   01/02/1983  1983    2   63
1   01/03/1983  1983    3   33
1   01/04/1983  1983    4   27
1   01/05/1983  1983    5   9
1   01/06/1983  1983    6   0
1   01/07/1983  1983    7   3
1   01/08/1983  1983    8   0
1   01/09/1983  1983    9   6
1   01/10/1983  1983    10  27
1   01/11/1983  1983    11  11
1   01/12/1983  1983    12  48

Now I am trying to execute the above query for a specific MONTH in every year only, calculating the average for the 5 following dates/months/rows. But a WHERE clause to specify the MONTH value will not work because that affects which numbers are averaged.

I need to do this for tens of thousands of LOCID's and millions of rows.

My question is if there is a way to execute the correct moving average for only a specific value for 'MONTH', thus hopefully reducing the processing time a number of times...

example desired output:

LOCID   Date    Year    Month   avgRTT
1   29952   1982    1   37.6
1   30317   1983    1   38

Any pointers will be greatly appreciated.

  • Can you add some more details, example data and expected result? – dnoeth Oct 6 '15 at 15:29
  • done, have changed the table a bit to accommodate a small sample table. – Joost vdW Oct 8 '15 at 11:41
1

You can apply your final filter on @dekade after computing the running averages.

In order to reduce the number of rows that need to be processed for the running averages, you can apply an earlier filter on [dekade] IN (@dekade, (@dekade+1)%36, (@dekade+2)%36) to ensure that you are processing the minimal amount of rows but still including all the rows that are necessary to including the following 11 rows in the running average. (The only reason for the % 36 is to handle values at @dekade that fall at the end of the year.)

This will still result in a table scan given your current table structure, but at least the rows can be filtered out earlier on in the query plan.

DECLARE @dekade TINYINT = 1
SELECT *
FROM (
    SELECT 
        LOCID,
        [Year],
        [Date],
        [dekade],
        AVG(cast(RTT as float)) OVER 
            (PARTITION BY LOCID ORDER BY Date
                ROWS BETWEEN CURRENT ROW AND 11 FOLLOWING) AS avgRTT
    FROM TimeSeries
    -- If you want to limit the rows that are use when computing the running average,
    -- you can make sure that only the desired @dekade plus the following two @dekades
    -- (which may be needed to get the following 11 rows) are used for each year
    WHERE [dekade] (@dekade, (@dekade+1)%36, (@dekade+2)%36)
) x
-- Filter your results be @dekade after computing the running average
WHERE x.[dekade] = @dekade

It would be helpful to post some sample data so that we can actually run the query though.

  • Did you mean (@dekade+1)%36? – Aaron Bertrand Oct 6 '15 at 22:21
  • @AaronBertrand Yes, thanks for the correction! The dangers of answering without a test script. Updated now. – Geoff Patterson Oct 6 '15 at 22:53
  • Thank you for the answer above, will try it now. In the meantime I have adapted the question with some sample data. Sorry to change the table, but otherwise a lot of sample data was required, taking up a lot of space. This is essentially the same table and query problem. – Joost vdW Oct 8 '15 at 10:51
  • ok, have done it using WHERE [dekad] IN (...). Hope that is what you meant. BETWEEN also works well. It is a definite improvement. however logically it still seems to require computing a lot of averages that I ultimately don't need. Ideal would be to somehow find the correct rows and only then apply the windowed average.... – Joost vdW Oct 8 '15 at 11:42
1

You might try to put the AVG into a CASE, if you got luck the optimizer is smart enough to calculate the moving average only once per year:

select *
from
 (
   SELECT 
       LOCID,
       [Year],
       [Date],
       [Month],
       case when Month = 1 then
          AVG(cast(RTT as float)) OVER (
               PARTITION BY LOCID ORDER BY Date 
                   ROWS BETWEEN CURRENT ROW AND 4 FOLLOWING ) 
       end AS avgRTT
   FROM TimeSeries
 ) as dt
where month = 1 -- or avgRTT is not null

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.