问创建自定义的多参数Oracle分析函数

EN

CREATE OR REPLACE TYPE matt_ratio_to_report_rec AS OBJECT
  ( key     VARCHAR2(80),
    value   NUMBER );

CREATE OR REPLACE PACKAGE matt_ratio_to_report_state AS
  TYPE values_tab_t IS TABLE OF matt_ratio_to_report_rec INDEX BY BINARY_INTEGER;
  TYPE index_tab_t IS TABLE OF NUMBER INDEX BY VARCHAR2(80);
  G_VALUES_TAB values_tab_t;
  G_INDEX_TAB index_tab_t;
  G_ITERATOR_POSITION NUMBER;
  G_TERMINATOR_POSITION NUMBER;
END matt_ratio_to_report_state;
/


CREATE OR REPLACE TYPE matt_ratio_to_report_impl AS OBJECT
(
  window_sum      NUMBER,
  CONSTRUCTOR FUNCTION matt_ratio_to_report_impl(SELF IN OUT NOCOPY matt_ratio_to_report_impl ) RETURN SELF AS RESULT,  
-- Called to initialize a new aggregation context
-- For analytic functions, the aggregation context of the *previous* window is passed in, so we only need to adjust as needed instead 
-- of creating the new aggregation context from scratch
  STATIC FUNCTION ODCIAggregateInitialize (sctx IN OUT matt_ratio_to_report_impl) RETURN NUMBER,
-- Called when a new data point is added to an aggregation context  
  MEMBER FUNCTION ODCIAggregateIterate (self IN OUT matt_ratio_to_report_impl, value IN matt_ratio_to_report_rec ) RETURN NUMBER,
-- Called to return the computed aggragate from an aggregation context
  MEMBER FUNCTION ODCIAggregateTerminate (self IN matt_ratio_to_report_impl, returnValue OUT NUMBER, flags IN NUMBER) RETURN NUMBER,
-- Called to merge to two aggregation contexts into one (e.g., merging results of parallel slaves) 
  MEMBER FUNCTION ODCIAggregateMerge (self IN OUT matt_ratio_to_report_impl, ctx2 IN matt_ratio_to_report_impl) RETURN NUMBER,
  -- ODCIAggregateDelete
  MEMBER FUNCTION ODCIAggregateDelete(self IN OUT matt_ratio_to_report_impl, value matt_ratio_to_report_rec) RETURN NUMBER  
);

/

CREATE OR REPLACE TYPE BODY matt_ratio_to_report_impl IS

CONSTRUCTOR FUNCTION matt_ratio_to_report_impl(SELF IN OUT NOCOPY matt_ratio_to_report_impl ) RETURN SELF AS RESULT IS
BEGIN
  SELF.window_sum := 0;
  matt_ratio_to_report_state.G_VALUES_TAB.DELETE;
  matt_ratio_to_report_state.G_INDEX_TAB.DELETE;
  matt_ratio_to_report_state.G_ITERATOR_POSITION := 0;
  matt_ratio_to_report_state.G_TERMINATOR_POSITION := 0;  
  RETURN;
END;


STATIC FUNCTION ODCIAggregateInitialize (sctx IN OUT matt_ratio_to_report_impl) RETURN NUMBER IS
BEGIN
  DBMS_OUTPUT.PUT_LINE('ODCIAggregateInitialize(' || sctx.window_sum);
  sctx := matt_ratio_to_report_impl ();
  RETURN ODCIConst.Success;
END;


MEMBER FUNCTION ODCIAggregateIterate (self IN OUT matt_ratio_to_report_impl, value IN matt_ratio_to_report_rec ) RETURN NUMBER IS
BEGIN
   DBMS_OUTPUT.PUT_LINE('ODCIAggregateIterate(' || self.window_sum || ' - ' || value.key || ', ' || value.value || ')');

  -- Increment sum
  self.window_sum := self.window_sum + value.value;

  matt_ratio_to_report_state.G_ITERATOR_POSITION := matt_ratio_to_report_state.G_ITERATOR_POSITION + 1;
  matt_ratio_to_report_state.G_VALUES_TAB(matt_ratio_to_report_state.G_ITERATOR_POSITION) := value;
  matt_ratio_to_report_state.G_INDEX_TAB(value.key) := matt_ratio_to_report_state.G_ITERATOR_POSITION;

  RETURN ODCIConst.Success;
END;

MEMBER FUNCTION ODCIAggregateTerminate (self IN matt_ratio_to_report_impl, returnValue OUT NUMBER, flags IN NUMBER) RETURN NUMBER IS
BEGIN
   DBMS_OUTPUT.PUT_LINE('ODCIAggregateTerminate(' || self.window_sum || ' - flags: ' || flags || ')');
  IF flags = 1 THEN
    matt_ratio_to_report_state.G_TERMINATOR_POSITION := matt_ratio_to_report_state.G_TERMINATOR_POSITION + 1;
    returnValue := matt_ratio_to_report_state.G_VALUES_TAB( matt_ratio_to_report_state.G_TERMINATOR_POSITION).value / self.window_sum; --self.x_list;
  END IF;
  RETURN ODCIConst.Success;
EXCEPTION
  WHEN others THEN 
    DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK || ' ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    RETURN ODCIConst.Success;
END;

MEMBER FUNCTION ODCIAggregateMerge (self IN OUT matt_ratio_to_report_impl, ctx2 IN matt_ratio_to_report_impl) RETURN NUMBER IS
BEGIN
   DBMS_OUTPUT.PUT_LINE('ODCIAggregateMerge(' || self.window_sum || ' - ' || ctx2.window_sum || ')');
  -- Increment sums
  self.window_sum := self.window_sum + ctx2.window_sum;

  RETURN ODCIConst.Success;
END;

-- ODCIAggregateDelete
MEMBER FUNCTION ODCIAggregateDelete(self IN OUT matt_ratio_to_report_impl, value matt_ratio_to_report_rec) RETURN NUMBER IS
BEGIN
   DBMS_OUTPUT.PUT_LINE('ODCIAggregateDelete(' || self.window_sum || ' - ' || value.key || ', ' || value.value || ')');
  -- Decrement sums
  matt_ratio_to_report_state.G_VALUES_TAB.DELETE(matt_ratio_to_report_state.G_INDEX_TAB(value.key));
  matt_ratio_to_report_state.G_INDEX_TAB.DELETE(value.key);

  self.window_sum := self.window_sum - value.value;
END;  

END;
/

CREATE OR REPLACE FUNCTION matt_ratio_to_report ( input matt_ratio_to_report_rec) RETURN NUMBER
PARALLEL_ENABLE AGGREGATE USING matt_ratio_to_report_impl;
/


CREATE TABLE matt_test_data ( x ) PARALLEL 4
AS SELECT rownum FROM DUAL CONNECT BY ROWNUM <= 50000;

with test as (
select d.x, sum(d.x) over ( partition by mod(d.x,5) order by d.x desc ) running_sum,
ratio_to_report(d.x) over ( partition by mod(d.x,500) ) oracle_rr,
matt_ratio_to_report( matt_ratio_to_report_rec(to_char(d.x), d.x) ) over ( partition by mod(d.x,500) ) custom_rr
--matt_ratio_to_report( matt_ratio_to_report_rec(to_char(d.x), d.x) ) over ( partition by mod(d.x,500) ORDER BY d.x ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) custom_rr_windowed 
from matt_test_data d )
SELECT /*+ PARALLEL */ test.*, case when test.oracle_rr != test.custom_rr then 'Mismatch!' Else null END test_results from test 
--where oracle_rr != custom_rr
ORDER BY test_results nulls last, x
;