Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2016
    Posts
    7

    Question Answered: help with REGR_SLOPE

    Hi
    I have a problem using REGR_SLOPE function with OVER (if this is the right way to obtain my results)

    I have a table (MYTABLE) as
    MYDATE NUMBER1 NUMBER2
    2015-12-16 1 1
    2015-12-17 2 5
    2015-12-18 3 8
    2015-12-19 4 12


    I want have as results the MYDATE column + the SLOPE based on the current record and previous 2 records (based on column NUMBER1 and NUMBER2)
    So.. for the
    2015-12-16 because this is the first record
    2015-12-17 because this is the second record
    2015-12-18 xyz in general a value based on date 18, 17 and 16
    2015-12-19 xyz in general a value based on date 19, 18 and 17

    I want calculate the SLOPE based on "n" previous records

    So I'm try to
    Code:
       SELECT MYDATE, 
         REGR_SLOPE(NUMBER1, NUMBER2)
         OVER (
           ORDER BY MYDATE
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
         FROM MYTABLE 
          ORDER BY MYDATE
    But I recieve ad exception
    The statement or command was not processed because the following clause is not supported in the context where it is used: "OVER".. SQLCODE=-109, SQLSTATE=42601, DRIVER=4.19.26 SQL Code: -109, SQL State: 42601

    I also try using "PARTITION" but same results
    Code:
       SELECT MYDATE, 
         REGR_SLOPE(NUMBER1, NUMBER2)
         OVER ( PARTITION MYDATE
           ORDER BY MYDATE
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
         FROM MYTABLE 
          ORDER BY MYDATE
    I'm using DB2 UDB 10.5 latest fixpack (6)

    Thanks to everyone can help me
    Happy 2016

  2. Best Answer
    Posted by mark.b

    "Hi,

    It's strange enough. Some undocumented "feature" probably.
    Try this instead, according to the documentation it should be an equivalent expression:
    Code:
    select mydate, number1, number2, case when rn_>2 then slope end slope
    from (
    select mydate, number1, number2
    --, regr_slope(number1, number2)  over (order by mydate rows between 2 preceding and current row) slope
    , covariance(number1, number2) over (order by mydate rows between 2 preceding and current row) 
    / nullif(variance(number2) over (order by mydate rows between 2 preceding and current row), 0) slope
    , rownumber() over (order by mydate) rn_
    from table (values 
      ('2015-12-16', 1, 1)
    , ('2015-12-17', 2, 5)
    , ('2015-12-18', 3, 8)
    , ('2015-12-19', 4, 12)
    ) t (mydate, number1, number2)
    )
    order by mydate
    "


  3. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    It's strange enough. Some undocumented "feature" probably.
    Try this instead, according to the documentation it should be an equivalent expression:
    Code:
    select mydate, number1, number2, case when rn_>2 then slope end slope
    from (
    select mydate, number1, number2
    --, regr_slope(number1, number2)  over (order by mydate rows between 2 preceding and current row) slope
    , covariance(number1, number2) over (order by mydate rows between 2 preceding and current row) 
    / nullif(variance(number2) over (order by mydate rows between 2 preceding and current row), 0) slope
    , rownumber() over (order by mydate) rn_
    from table (values 
      ('2015-12-16', 1, 1)
    , ('2015-12-17', 2, 5)
    , ('2015-12-18', 3, 8)
    , ('2015-12-19', 4, 12)
    ) t (mydate, number1, number2)
    )
    order by mydate
    Regards,
    Mark.

  4. #3
    Join Date
    Jan 2016
    Posts
    7

    Thumbs up

    Thanks!
    it seems working (to check values.. but working)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •