Standard deviation over current day and previous 3 days?

• John Mihalko

  SSC Veteran

  Points: 298

  More actions

  December 6, 2018 at 3:57 pm

  Go to Answer

  #382427

  Is there a way to get the STDEVP in column 3 (the Price standard deviation of the current and previous 3 rows) using windows functions or LAG? Can this be done without complicated JOINs?
  
Day             Price       STDEVP
  2018-01-01      1.21        NULL
  2018-01-02      2.21        NULL
  2018-01-03      3.31        NULL
  2018-01-04      4.32        1.16630559888907
  2018-01-05      1.56        1.05430071611471
  2018-01-06      1.23        1.26697474323682
  2018-01-07      5.21        1.71809487514514
  2018-01-08      3.87        1.64645642213816
  2018-01-09      0.23        1.99461149099267
  2018-01-10      9.01        3.1403980639403
  2018-01-11      6.43        3.24429884566758
  2018-01-12      7.44        3.33217177678462
  

• pietlinden

  SSC Guru

  Points: 63503

  More actions

  December 6, 2018 at 4:37 pm

  Answer

  #2015713

  How about this? (Thanks for the data, but if can provide CREATE TABLE scripts, you'll get better answers, because people can just copy & paste to create your setup.
  CREATE TABLE Prices (TheDate DATE, Price SMALLMONEY, stdvp decimal(10,2));
GO

  INSERT INTO Prices VALUES
('2018-01-01',  1.21,   NULL),
('2018-01-02',  2.21,   NULL),
('2018-01-03',  3.31,   NULL),
('2018-01-04',  4.32,   1.16630559888907),
('2018-01-05',  1.56,   1.05430071611471),
('2018-01-06',  1.23,   1.26697474323682),
('2018-01-07',  5.21,   1.71809487514514),
('2018-01-08',  3.87,   1.64645642213816),
('2018-01-09',  0.23,   1.99461149099267),
('2018-01-10',  9.01,   3.1403980639403),
('2018-01-11',  6.43,   3.24429884566758),
('2018-01-12',  7.44,   3.33217177678462);

  SELECT TheDate
    , Price
    , rn
    , CASE WHEN rn < 4 THEN NULL ELSE
                         STDEVP(Price) OVER (ORDER BY TheDate
                         ROWS BETWEEN 3 PRECEDING
                         AND CURRENT ROW) END AS stdv
FROM (SELECT TheDate
            , Price
            , ROW_NUMBER() OVER (ORDER BY TheDate) rn
     FROM Prices) x;