Oracle聚合求和和聚合求积(顺便解决BOM展开的问题)

Oracle聚合求和和聚合求积(顺便解决BOM展开的问题)
强烈推介IDEA2021.1.3破解激活,IntelliJ IDEA 注册码,2021.1.3IDEA 激活码  

大家好,我是架构君,一个会写代码吟诗的架构师。今天说一说Oracle聚合求和和聚合求积(顺便解决BOM展开的问题),希望能够帮助大家进步!!!

本文参考网址:http://www.itpub.net/thread-1020772-1-1.html

       我们在日常的工作中,经常遇到了针对某一列的值,进行求和,求平均值,在一些特殊的业务场景下,我们需要对某一列进行求积操作,那我们该如何实现呢,下面先介绍,我

们对字符串的求和操作。

      针对字符串的求和操作:

     1、有分隔符的字符串:

1 SELECT STR, SUM(STR) OVER(ORDER BY LV ASC) AS RS
2   FROM (SELECT REGEXP_SUBSTR('1,2,3,4,5', '[^,]+', 1, LEVEL) AS STR,
3                LEVEL AS LV
4           FROM DUAL
5         CONNECT BY LEVEL <=
6                    LENGTH(REGEXP_REPLACE('1,2,3,4,5', '[^,]', '')) + 1) COLS

   结果:
  Oracle聚合求和和聚合求积(顺便解决BOM展开的问题)

2、没有分割符符号的:

1       SELECT STR, SUM(STR) OVER(ORDER BY LV ASC) AS RS
2         FROM (SELECT SUBSTR('12345', LEVEL, 1) STR, LEVEL AS LV
3                 FROM DUAL
4               CONNECT BY LEVEL <= LENGTH(12345)) COLS

结果:

Oracle聚合求和和聚合求积(顺便解决BOM展开的问题)

下面介绍连续求积的方法

SUM()是个求和的聚合函数,如何求积呢?我们可以想办法把乘法变成加法:

A*B*C = 10^(LOG(A)+LOG(B)+LOG(C))

1、直接使用对数和反对数来进行求积,即:LOG和POWER函数

1 SELECT STR, POWER(10, SUM(LOG(10, STR)) OVER(ORDER BY STR))
2   FROM (SELECT REGEXP_SUBSTR('1,2,3,4,5', '[^,]+', 1, LEVEL) AS STR,
3                LEVEL AS LV
4           FROM DUAL
5         CONNECT BY LEVEL <=
6                    LENGTH(REGEXP_REPLACE('1,2,3,4,5', '[^,]', '')) + 1) COLS

结果:

Oracle聚合求和和聚合求积(顺便解决BOM展开的问题)

2、使用PL/SQL的自定义函数来实现该功能

 1 CREATE OR REPLACE FUNCTION GET_EXPRESSION_RSLT(I_EXPRESSION VARCHAR2) RETURN VARCHAR2 IS
 2  /************************************************************
 3   * 函数名称:GET_EXPRESSION_RSLT
 4   * 功能描述:获取指定的表达式的结果
 5   * 参数:I_EXPRESSION :表达式  例如:1*2*3
 6   * 编 写 人:XXX
 7   * 编写时间:XXXX-XX-XX
 8   * 修改记录:
 9   *************************************************************/
10     RETURNSTR VARCHAR2(500) := '';
11     EXECSQL   VARCHAR2(4000) := '';
12   BEGIN
13     EXECSQL := ' SELECT ' || I_EXPRESSION || '  FROM  DUAL';
14     EXECUTE IMMEDIATE (EXECSQL)
15       INTO RETURNSTR;
16     RETURN RETURNSTR;
17   END;

SQL:

1 SELECT STR,
2        GET_EXPRESSION_RSLT(REPLACE(WM_CONCAT(STR) OVER(ORDER BY STR),
3                                    ',',
4                                    '*')) RS
5   FROM (SELECT REGEXP_SUBSTR('1,2,3,4,5', '[^,]+', 1, LEVEL) AS STR,
6                LEVEL AS LV
7           FROM DUAL
8         CONNECT BY LEVEL <=
9                    LENGTH(REGEXP_REPLACE('1,2,3,4,5', '[^,]', '')) + 1) COLS

结果:
Oracle聚合求和和聚合求积(顺便解决BOM展开的问题)

但是,使用这种方法:newid这个大拿给出了不使用该种方法的建议:

SELECT A*B*C... FROM DUAL;

都是常量且个数不定,每次都需要硬解析,所以不推荐。

下面是tom对使用wm_concat函数的看法:

http://asktom.oracle.com/pls/ask ... #548923200346634568

Hi Tom,

I saw wm_concat on a couple of forums (otn and orafaq), apparently a new, undocumented function as
shown below.  Since it is undocumented, is it safe to use?

Regards,
Barbara

Followup   November 20, 2007 - 2pm US/Eastern:

my suggestion is going to be consistent....

Never use undocumented stuff, it is subject to change, removal, broken-ness without recourse.

either

a) use stragg
b) write your own
c) use the connect by trick.

3、使用自定义的聚合函数

 安德森

 1 CREATE OR REPLACE TYPE PROD_AGG_TYPE AS OBJECT
 2 (
 3   TOTAL NUMBER,
 4 
 5   STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT PROD_AGG_TYPE)
 6     RETURN NUMBER,
 7 
 8   MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF  IN OUT PROD_AGG_TYPE,
 9                                        VALUE IN NUMBER) RETURN NUMBER,
10 
11   MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF        IN PROD_AGG_TYPE,
12                                          RETURNVALUE OUT NUMBER,
13                                          FLAGS       IN NUMBER)
14     RETURN NUMBER,
15 
16   MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT PROD_AGG_TYPE,
17                                      CTX2 IN PROD_AGG_TYPE) RETURN NUMBER
18 )
19 /
20 CREATE OR REPLACE TYPE BODY PROD_AGG_TYPE IS
21 
22   STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT PROD_AGG_TYPE)
23     RETURN NUMBER IS
24   BEGIN
25     SCTX       := PROD_AGG_TYPE(NULL);
26     SCTX.TOTAL := 1;
27     RETURN ODCICONST.SUCCESS;
28   END;
29 
30   MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF  IN OUT PROD_AGG_TYPE,
31                                        VALUE IN NUMBER) RETURN NUMBER IS
32   BEGIN
33     SELF.TOTAL := SELF.TOTAL * VALUE;
34     RETURN ODCICONST.SUCCESS;
35   END;
36 
37   MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF        IN PROD_AGG_TYPE,
38                                          RETURNVALUE OUT NUMBER,
39                                          FLAGS       IN NUMBER) RETURN NUMBER IS
40   BEGIN
41     RETURNVALUE := SELF.TOTAL;
42     RETURN ODCICONST.SUCCESS;
43   END;
44 
45   MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT PROD_AGG_TYPE,
46                                      CTX2 IN PROD_AGG_TYPE) RETURN NUMBER IS
47   BEGIN
48     SELF.TOTAL := SELF.TOTAL * CTX2.TOTAL;
49     RETURN ODCICONST.SUCCESS;
50   END;
51 
52 END;
53 /

函数:

CREATE OR REPLACE FUNCTION prod_agg(input NUMBER )
RETURN NUMBER
PARALLEL_ENABLE AGGREGATE USING prod_agg_type;

SQL:

1  SELECT STR, prod_agg(STR) OVER(ORDER BY LV ASC) AS RS
2         FROM (SELECT SUBSTR('12345', LEVEL, 1) STR, LEVEL AS LV
3                 FROM DUAL
4               CONNECT BY LEVEL <= LENGTH(12345)) COLS

结果:

Oracle聚合求和和聚合求积(顺便解决BOM展开的问题)

 

转载于:https://www.cnblogs.com/caroline/p/3428324.html

本文来源weixin_30595035,由架构君转载发布,观点不代表Java架构师必看的立场,转载请标明来源出处:https://javajgs.com/archives/29656

发表评论