Moving average of a moving average in SQL

Question

I am trying to calculate stochastic oscillator values on a table of stock prices for stocks at a particular date.

The table I am working on is:

CREATE TABLE [dbo].[equityprice](
    [SecurityId] [int] NOT NULL,
    [Date] [date] NOT NULL,
    [Open] [numeric](19, 6) NULL,
    [High] [numeric](19, 6) NULL,
    [Low] [numeric](19, 6) NULL,
    [Close] [numeric](19, 6) NULL,
    [Volume] [bigint] NULL
    CONSTRAINT [PK_equityprice] PRIMARY KEY CLUSTERED 
    (
        [SecurityId] ASC,
        [Date] ASC,
        [DateAdded] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

The calculation is as follows:

Work out the lowest low for the last 14 periods (including the current one)
Calculate the raw value by: 100 * (CurrentClose-LowestLow) / (CurrentHigh-LowestLow)
Return field k as the average of the raw value for the last 3 periods (including the current one)
Return field d as the average of field k for the last 3 periods (including the current one)

There are potentially going to be 100 million plus price records over 50,000 SecurityIds.

I've come up with this as an initial try. It works, but it doesn't scale very well and I suspect there is a better way of doing it:

declare @dt date = '2010-11-12';

select * from 
    (select 
        SecurityId, [Date], [High], lowestLow, [Close], rawk, k, 
        avg(k) OVER(PARTITION BY SecurityId ORDER BY [Date] ROWS BETWEEN 2 PRECEDING  AND CURRENT ROW) as d
     from 
         (select 
              SecurityId, [Date], [Close], [High], [lowestLow], rawk, 
              avg(rawK) OVER(PARTITION BY SecurityId ORDER BY [Date] ROWS BETWEEN 2 PRECEDING  AND CURRENT ROW) as k
          from 
              (select 
                   SecurityId, [Date], [Close], [High], [lowestLow], 
                   iif(lowestLow is null, 0, iif([High]-lowestLow=0, 0, 100*([Close]-lowestLow)/([High]-lowestLow))) as rawK 
               from 
                   (select 
                        SecurityId, [Date], [Close], [High], 
                        Min([Low]) OVER(PARTITION BY SecurityId ORDER BY [Date] ROWS BETWEEN 13 PRECEDING AND CURRENT ROW) as lowestLow 
                    from 
                        equityprice 
                    where 
                        [Date]<=@dt) as base) 
               as raw) 
          as k) 
 as d
where
    [Date]=@dt

Any ideas?

Test data as follows:

High    Lowest  Close   rawK        K           D
7.39    7.251   7.33    56.83453237     
7.38    7.251   7.282   24.03100775     
7.432   7.251   7.3     27.0718232  35.97912111 
7.38    7.203   7.38    100         50.36761032 
7.35    7.203   7.214   7.482993197 44.85160547 43.73277897
7.349   7.203   7.349   100         69.16099773 54.79340451
7.399   7.203   7.3     49.48979592 52.32426304 55.44562208
7.501   7.203   7.501   100         83.16326531 68.21617536
7.549   7.203   7.49    82.94797688 77.4792576  70.98892865
7.649   7.203   7.649   100         94.31599229 84.98617173
7.609   7.203   7.609   100         94.31599229 88.70374739
7.504   7.203   7.473   89.70099668 96.56699889 95.06632783
7.688   7.203   7.483   57.73195876 82.47765181 91.12021433
7.558   7.203   7.558   100         82.47765181 87.17410084

You should add test data and result expected from that data. — Giorgi Nakeuri, Feb 23 '15 at 11:36
To simply understand this I would rewrite it using [Common Table Expressions](https://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx) It will not change the logic or speed of the query, but it will make it readable by humans. — Vladimir Baranov, Feb 23 '15 at 11:44
SQL Server is good at selecting a small subset of data from millions of records. It is not good at calculating moving averages or moving minimums. So, use SQL to quickly select only relevant small amount of data and do the rest of calculations using procedural language. Some links that came up first in the search: http://stackoverflow.com/questions/14823713/efficient-rolling-max-and-min-window http://citeseerx.ist.psu.edu/viewdoc/summary?doi=10.1.1.29.4547 — Vladimir Baranov, Feb 23 '15 at 12:02
Thanks - the reason for the question is that I am trying to work out if there is a more efficient way of doing the query itself with SQL? I want to select the data for a particular date and I suspect that the query is not optimised properly — Liam, Feb 23 '15 at 12:08
IMHO,your query is already very good and human readable.But because of lot of data and lot of calculation itself it can become slow.There is always some scope for improvement.Like outer most select * from is not required.Definitely index on security-id and date column will help.You just can't simply calculate million of records.You should apply paging. You can also fetch only inner most select resultset in front end like (asp.net),and do rest of the calculation there.This way only select is required,and it has to be fast.Hope I am clear . — KumarHarsh, Feb 23 '15 at 12:36
If you want to select data only for particular date, then write `WHERE [Date] = @dt` instead of `WHERE [Date] <= @dt`. To make such `WHERE` efficient make sure you have index on this column. I thought that it is obvious for a person who deals with 100M of rows. I don't see how you can make the calculation of moving minimums and averages more efficient in SQL. It is not efficient in SQL, because SQL recalculates the moving `MIN` and `AVG` of each window over and over again for each position of the window. My point was that there are algorithms to do it more efficiently (not in SQL). — Vladimir Baranov, Feb 23 '15 at 21:48
I'm not sure that you are using the word 'obvious' correctly... when you apply your Date]=@dt suggestion the query only returns one row within the window for each master record rather than the 14 the inner query requires. As for 'obvious for a person who deals with 100M of rows'? Surely somebody has told you that size doesn't matter... — Liam, Feb 24 '15 at 08:27
I'm sorry, @Liam, I didn't mean to offend you. I must have misunderstood the question. — Vladimir Baranov, Feb 25 '15 at 09:53

Moving average of a moving average in SQL

0 Answers0