mult - PL/SQL

mult.pls

CREATE OR REPLACE TYPE Multiplication
AS OBJECT (
  runningProduct NUMBER,

  STATIC FUNCTION ODCIAggregateInitialize
    ( actx IN OUT Multiplication
    ) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateIterate
    ( self  IN OUT Multiplication,
      val   IN     NUMBER
    ) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateTerminate
    ( self        IN  Multiplication,
      returnValue OUT NUMBER,
      flags       IN  NUMBER
    ) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateMerge
    ( self IN OUT Multiplication,
      ctx2 IN     Multiplication
    ) RETURN NUMBER
);

CREATE OR REPLACE TYPE BODY Multiplication AS

  STATIC FUNCTION ODCIAggregateInitialize
    ( actx IN OUT Multiplication
    ) RETURN NUMBER IS
  BEGIN
    IF actx IS NULL THEN
      actx := Multiplication (NULL);
    ELSE
      actx.runningProduct := NULL;
    END IF;
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateIterate
    ( self IN OUT Multiplication,
      val IN NUMBER
    ) RETURN NUMBER IS
  BEGIN
    self.runningProduct := NVL(self.runningProduct, 1) * val;
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateTerminate
    ( self IN Multiplication,
    returnValue OUT NUMBER,
    flags IN NUMBER ) RETURN NUMBER IS
  BEGIN
    returnValue := self.runningProduct;
    RETURN ODCIConst.Success;
  END;

  MEMBER FUNCTION ODCIAggregateMerge
    ( self IN OUT Multiplication,
    ctx2 IN Multiplication
    ) RETURN NUMBER IS
  BEGIN
    self.runningProduct := self.runningProduct + ctx2.runningProduct;
    RETURN ODCIConst.Success;
  END;

END;

CREATE OR REPLACE FUNCTION mult ( x NUMBER ) RETURN NUMBER PARALLEL_ENABLE AGGREGATE USING Multiplication;

solution.sql

-- with model clause
-- as of today I have no idea what this is, but probably not a solution
CREATE TABLE Quotes (quoteDate, someRate)
AS
SELECT to_date(c1), to_number(c2) FROM dual
MODEL
DIMENSION BY (0 key)
MEASURES (sysdate c1, 1 c2)
RULES UPSERT ALL ITERATE(100) (
  c1[ITERATION_NUMBER] = c1[0] + ITERATION_NUMBER,
  c2[ITERATION_NUMBER] = NVL(c2[ITERATION_NUMBER - 1], 1.0) * 1.1
)

-- with the aggregate function used as a window/analytic function
SELECT quoteDate, mult(someRate) OVER(ORDER BY quoteDate) AS c2 FROM Quotes