我们在日常的工作中,经常遇到了针对某一列的值,进行求和,求平均值,在一些特殊的业务场景下,我们需要对某一列进行求积操作,那我们该如何实现呢,下面先介绍,我
们对字符串的求和操作。
针对字符串的求和操作:
1、有分隔符的字符串:
1 SELECT STR, SUM(STR) OVER(ORDER BY LV ASC) ASRS2 FROM (SELECT REGEXP_SUBSTR('1,2,3,4,5', '[^,]+', 1, LEVEL) AS STR,3 LEVEL ASLV4 FROMDUAL5 CONNECT BY LEVEL <=
6 LENGTH(REGEXP_REPLACE('1,2,3,4,5', '[^,]', '')) + 1) COLS
结果:
2、没有分割符符号的:
1 SELECT STR, SUM(STR) OVER(ORDER BY LV ASC) ASRS2 FROM (SELECT SUBSTR('12345', LEVEL, 1) STR, LEVEL ASLV3 FROMDUAL4 CONNECT BY LEVEL <= LENGTH(12345)) COLS
结果:
下面介绍连续求积的方法
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 ASLV4 FROMDUAL5 CONNECT BY LEVEL <=
6 LENGTH(REGEXP_REPLACE('1,2,3,4,5', '[^,]', '')) + 1) COLS
结果:
2、使用PL/SQL的自定义函数来实现该功能
1 CREATE OR REPLACE FUNCTION GET_EXPRESSION_RSLT(I_EXPRESSION VARCHAR2) RETURN VARCHAR2 IS
2 /************************************************************3 * 函数名称:GET_EXPRESSION_RSLT4 * 功能描述:获取指定的表达式的结果5 * 参数:I_EXPRESSION :表达式 例如:1*2*36 * 编 写 人:XXX7 * 编写时间:XXXX-XX-XX8 * 修改记录:9 *************************************************************/
10 RETURNSTR VARCHAR2(500) := '';11 EXECSQL VARCHAR2(4000) := '';12 BEGIN
13 EXECSQL := 'SELECT' || I_EXPRESSION || 'FROM DUAL';14 EXECUTEIMMEDIATE (EXECSQL)15 INTORETURNSTR;16 RETURNRETURNSTR;17 END;
SQL:
1 SELECT STR,2 GET_EXPRESSION_RSLT(REPLACE(WM_CONCAT(STR) OVER(ORDER BY STR),3 ',',4 '*')) RS5 FROM (SELECT REGEXP_SUBSTR('1,2,3,4,5', '[^,]+', 1, LEVEL) AS STR,6 LEVEL ASLV7 FROMDUAL8 CONNECT BY LEVEL <=
9 LENGTH(REGEXP_REPLACE('1,2,3,4,5', '[^,]', '')) + 1) COLS
结果:
但是,使用这种方法: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 ASOBJECT2 (3 TOTAL NUMBER,4
5 STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX INOUT PROD_AGG_TYPE)6 RETURN NUMBER,7
8 MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF INOUT PROD_AGG_TYPE,9 VALUE IN NUMBER) RETURN NUMBER,10
11 MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF INPROD_AGG_TYPE,12 RETURNVALUE OUT NUMBER,13 FLAGS IN NUMBER)14 RETURN NUMBER,15
16 MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF INOUT 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 INOUT PROD_AGG_TYPE)23 RETURN NUMBER IS
24 BEGIN
25 SCTX := PROD_AGG_TYPE(NULL);26 SCTX.TOTAL := 1;27 RETURNODCICONST.SUCCESS;28 END;29
30 MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF INOUT PROD_AGG_TYPE,31 VALUE IN NUMBER) RETURN NUMBER IS
32 BEGIN
33 SELF.TOTAL := SELF.TOTAL *VALUE;34 RETURNODCICONST.SUCCESS;35 END;36
37 MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF INPROD_AGG_TYPE,38 RETURNVALUE OUT NUMBER,39 FLAGS IN NUMBER) RETURN NUMBER IS
40 BEGIN
41 RETURNVALUE :=SELF.TOTAL;42 RETURNODCICONST.SUCCESS;43 END;44
45 MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF INOUT PROD_AGG_TYPE,46 CTX2 IN PROD_AGG_TYPE) RETURN NUMBER IS
47 BEGIN
48 SELF.TOTAL := SELF.TOTAL *CTX2.TOTAL;49 RETURNODCICONST.SUCCESS;50 END;51
52 END;53 /
函数:
CREATE OR REPLACE FUNCTION prod_agg(input NUMBER)RETURN NUMBERPARALLEL_ENABLE AGGREGATE USING prod_agg_type;
SQL:
1 SELECT STR, prod_agg(STR) OVER(ORDER BY LV ASC) ASRS2 FROM (SELECT SUBSTR('12345', LEVEL, 1) STR, LEVEL ASLV3 FROMDUAL4 CONNECT BY LEVEL <= LENGTH(12345)) COLS
结果: