文章/答案/技术大牛

发布

社区首页 >问答首页 >创建自定义的多参数Oracle分析函数

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

Stack Overflow用户

提问于 2015-08-25 18:11:45

回答 3查看 2.7K关注 0票数 6

背景

我知道，在Oracle中，可以创建处理值集合并返回单个结果的自定义聚合函数。docs.oracle.com/cd/B28359_01/appdev.111/b28425/aggr_functions.htm 编辑：我甚至读过的友好手册！

我还知道甲骨文提供了内建解析函数，比如DENSE_RANK和RATIO_TO_REPORT，它们为每个输入提供值，相对于输入位于其中的值的集合/窗口。

问题

我想知道的是，是否有一种方法可以创建我自己的解析函数，大概类似于我可以创建自己的聚合函数，特别是在我的自定义分析函数中创建一个带有附加参数的。

微妙的术语警告

当我提到“分析函数”时，请将它作为一个函数读取，该函数除了通过PARTITION关键字接受窗口参数外，还可以在给定窗口中返回不同的值。(如果有人对此有更好的说法，请告诉我！纯解析函数？DENSE_RANK-class解析函数？非聚集解析函数？)

Oracle文档指出，聚合函数可以用作分析(加窗)函数。不幸的是，这只意味着用于在解析函数中指定窗口的PARTITION关键字也可以应用于聚合函数。它不会将聚合函数提升到我梦寐以求的状态，即能够在固定窗口中返回不同的值。

用作分析的集合：

SELECT SUM(income) OVER (PARTITION BY first_initial) AS total FROM data;

会有和data一样多的记录，但只有第一个首字母有多少不同的total。

分析的用作分析的：

SELECT RATIO_TO_REPORT(income) OVER (PARTITION BY first_initial) AS ratio FROM data;

将拥有与data一样多的记录，而且，即使在给定的first_initial分区中，这些ratio也可能是不同的。

上下文

我已经获得了一个PL/SQL过程的调用权限，它接受一个数字集合作为一个IN参数，并且还有一些其他的IN配置参数。该过程以一种受配置参数影响的方式修改集合的值(将其想象为“大学的专有批准和要求的等级弯曲过程”)。

目前，使用该过程的过程是硬编码游标循环，该循环检测从一个数据分区到另一个数据分区的更改，然后在每个分区中获取数据到集合中，然后将数据传递给该过程，进行修改，并最终转储到一个单独的表中。我计划通过创建一个封装部分逻辑的PIPELINED PARALLEL_ENABLE表函数来改进这一点，但我更倾向于启用如下所示的查询：

SELECT G.Course_ID
     , G.Student_ID
     , G.Raw_Grade
     , analytic_wrapper(G.raw_grade, P.course_config_data)
                  OVER (PARTITION BY G.Course_ID) AS Adjusted_Grade
     , P.course_config_data
  FROM      grades   G
  LEFT JOIN policies P
  ON G.Course_ID = P.Course_ID;

但是，这需要能够创建一个自定义的分析函数，而且由于该过程在不同分区上需要不同的输入(例如，上面的Course_ID-specific P.course_config_data )，它不仅必须接受数据要聚合的参数，而且还要接受额外的输入。

这有可能吗?如果是的话，我在哪里可以找到文档？我的Google-fu让我失望了。

额外的褶皱

我所提供的PL/SQL过程实际上是不确定的，它的输出具有必须保留的统计属性。例如，如果A={A[0], A[1], A[3]}是一个特定类的原始等级，而B=f(A)是在1:00调用A上的过程的结果，而C=f(A)是在1:15调用A上的过程的结果，那么B={B[0],B[1],B[2]}和C={C[0],C[1],C[2]}都是可接受的输出，但是混合使用{C[0],B[1],C[2]}这样的元素是不可接受的。

其结果是，必须在每个分区上精确地调用该过程一次。(从技术上讲，它可以被浪费地调用很多次，但是分区的所有结果都必须来自同一个调用)。

例如，假设提供给我的过程如下操作:它接受一个年级集合作为IN OUT参数，然后将任意选择的其中一个等级设置为100。所有其他等级都设置为零。在下午1点运行这个程序可能会导致Alice拥有唯一的及格等级，而在下午1:01pm运行它可能会导致Bob拥有唯一的及格等级。无论如何，每班只有一名学生通过考试，而不是更多，也不应该更少。

oracle11g

customization

analytic-functions

oracle

plsql

回答 3

Stack Overflow用户

发布于 2015-09-30 17:37:51

这个版本并没有我先前的回答中的警告，尽管它会更慢一些，更难使用。在ODCIAggregateDelete中，循环的大部分速度都是缓慢的--您可能可以在那里找到一种改进，而不需要遍历整个集合。

无论如何，这个版本制作了一个自定义分析函数，它模仿Oracle的本机收集函数。因此，它不是试图创建一个计算我们想要的实际值的自定义分析函数，而是只计算窗口中的一组行。

然后，对于每一行，我们将行数据和自定义“收集”分析的结果传递给一个计算我们想要的值的正则函数。

这是密码。(注:您最初的问题也涉及多个参数。简单--只要把你想要的所有字段放到matt_ratio_to_report_rec中就行了。(同样，对对象名称表示遗憾-我在所有东西上都加上了我的名字，其他开发人员知道对象是否造成了问题。)

-- This is the input data to the analytic function
--DROP TYPE matt_ratio_to_report_rec;
CREATE OR REPLACE TYPE matt_ratio_to_report_rec AS OBJECT
  ( value   NUMBER );

-- This is a collection of input data  
--DROP TYPE matt_ratio_to_report_tab;
CREATE OR REPLACE TYPE matt_ratio_to_report_tab AS TABLE OF matt_ratio_to_report_rec;


-- This object type implements a custom analytic that acts as an analytic version of Oracle's COLLECT function
--DROP TYPE matt_ratio_to_report_col_impl;
CREATE OR REPLACE TYPE matt_ratio_to_report_col_impl AS OBJECT (
  analytics_window    matt_ratio_to_report_tab,
  CONSTRUCTOR FUNCTION matt_ratio_to_report_col_impl(SELF IN OUT NOCOPY matt_ratio_to_report_col_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_col_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_col_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_col_impl, returnValue OUT matt_ratio_to_report_tab, 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_col_impl, ctx2 IN matt_ratio_to_report_col_impl) RETURN NUMBER,
  -- ODCIAggregateDelete
  MEMBER FUNCTION ODCIAggregateDelete(self IN OUT matt_ratio_to_report_col_impl, value matt_ratio_to_report_rec) RETURN NUMBER  
);

CREATE OR REPLACE TYPE BODY matt_ratio_to_report_col_impl IS

CONSTRUCTOR FUNCTION matt_ratio_to_report_col_impl(SELF IN OUT NOCOPY matt_ratio_to_report_col_impl ) RETURN SELF AS RESULT IS
BEGIN
  SELF.analytics_window := new matt_ratio_to_report_tab();
  RETURN;
END;


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


MEMBER FUNCTION ODCIAggregateIterate (self IN OUT matt_ratio_to_report_col_impl, value IN matt_ratio_to_report_rec ) RETURN NUMBER IS
BEGIN
   DBMS_OUTPUT.PUT_LINE('ODCIAggregateIterate(' || self.analytics_window.COUNT || ')');

  -- Add record to collection
  self.analytics_window.extend();
  self.analytics_window(self.analytics_window.COUNT) :=  value;
  RETURN ODCIConst.Success;
END;

MEMBER FUNCTION ODCIAggregateTerminate (self IN matt_ratio_to_report_col_impl, returnValue OUT matt_ratio_to_report_tab, flags IN NUMBER) RETURN NUMBER IS
BEGIN
   DBMS_OUTPUT.PUT_LINE('ODCIAggregateTerminate(' || self.analytics_window.COUNT || ' - flags: ' || flags || ')');
  IF flags = 1 THEN
    returnValue := self.analytics_window;
  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_col_impl, ctx2 IN matt_ratio_to_report_col_impl) RETURN NUMBER IS
BEGIN
--   DBMS_OUTPUT.PUT_LINE('ODCIAggregateMerge(' || self.window_sum || ' - ' || ctx2.window_sum || ')');
  -- TODO: Add all elements from ctx2 window to self window
  RETURN ODCIConst.Success;
END;

-- ODCIAggregateDelete
MEMBER FUNCTION ODCIAggregateDelete(self IN OUT matt_ratio_to_report_col_impl, value matt_ratio_to_report_rec) RETURN NUMBER IS
  l_ctr NUMBER;
BEGIN
   DBMS_OUTPUT.PUT_LINE('ODCIAggregateDelete(' || self.analytics_window.COUNT || ' - ' || value.value || ')');
   l_ctr := self.analytics_window.FIRST;
   <<window_loop>>
   WHILE l_ctr IS NOT NULL LOOP
     IF ( self.analytics_window(l_ctr).value = value.value ) THEN
       self.analytics_window.DELETE(l_ctr);
       DBMS_OUTPUT.PUT_LINE('... deleted slot ' || l_ctr);
       EXIT window_loop;
     END IF;
     l_ctr := self.analytics_window.NEXT(l_ctr);
   END LOOP;
  RETURN ODCIConst.Success;

END;  

END;
/

-- This function is the analytic version of Oracle's COLLECT function
--DROP FUNCTION matt_ratio_to_report;
CREATE OR REPLACE FUNCTION matt_ratio_to_report_col ( input matt_ratio_to_report_rec) RETURN matt_ratio_to_report_tab
PARALLEL_ENABLE AGGREGATE USING matt_ratio_to_report_col_impl;
/


-- This the actual function we want
CREATE OR REPLACE FUNCTION matt_ratio_to_report ( p_row_value NUMBER, p_report_window matt_ratio_to_report_tab ) RETURN NUMBER IS
  l_report_window_sum NUMBER := 0;
  l_counter NUMBER := NULL;
BEGIN
  IF p_row_value IS NULL or p_report_window IS NULL THEN
    RETURN NULL;
  END IF;

  -- Compute window sum
  l_counter := p_report_window.FIRST;
  WHILE l_counter IS NOT NULL LOOP
    l_report_window_sum := l_report_window_sum + NVL(p_report_window(l_counter).value,0);
    l_counter := p_report_window.NEXT(l_counter);
  END LOOP;

  RETURN p_row_value / NULLIF(l_report_window_sum,0);
END matt_ratio_to_report;  



-- Create some test data
--DROP TABLE matt_test_data;
CREATE TABLE matt_test_data ( x, group# ) PARALLEL 4
AS SELECT rownum, ceil(rownum / 10) group# FROM DUAL CONNECT BY ROWNUM <= 50000;


-- TESTER 9/30
with test as (
SELECT d.x,
       CEIL (d.x / 10) group#,
       ratio_to_report (d.x) OVER (PARTITION BY d.group#) oracle_rr,
       matt_ratio_to_report (
         d.x,
         matt_ratio_to_report_col (matt_ratio_to_report_rec (d.x)) OVER (PARTITION BY d.group#)) custom_rr
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;

票数 2

Stack Overflow用户

发布于 2015-09-29 18:47:47

我也有同样的需要。我正在发布一种似乎有效的方法(对于我迄今为止尝试过的所有情况，它都与本机Oracle ratio_to_report函数相一致)。

我担心的是，它依赖于这样一个“事实”，即ODCIIterate和ODCITerminate方法总是按相同的顺序调用。我没有理由相信情况总是如此。我可能会记录一个SR，因为我认为如果没有Oracle的澄清，我就不能使用这个版本。

尽管如此，我仍在张贴代码，因为它确实代表了问题的答案。

警告#1 --此代码将状态存储在PL/SQL包中。我讨厌这样做，但我别无选择，因为ODCITerminate只将自己传递为IN，而不是IN OUT。除了丑陋之外，这意味着您不能在同一个查询中多次使用自定义分析函数(因为它们的状态将混合在一起)。我确信人们可以围绕这一限制进行编码(例如，给每个ODCI上下文一个唯一的值，并为每个唯一的上下文保留单独的状态)。

警告2 --我的测试用例使用并行查询。我从explain plan中可以看出它是并行运行的。但是，它似乎并不是在实例化和合并多个上下文，我真的想测试它，因为如果有什么东西破坏了这种方法，就会是这样。

这是密码。

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
;

票数 1

Stack Overflow用户

发布于 2015-08-25 18:22:48

数据Cartridge开发者指南涵盖了这些主题。这一节讨论用户定义的解析函数。

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/32211146

复制

相似问题

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

回答 3

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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

回答 3

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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