oracle 函数（聚合函数等等）精华

基本转自 tianlesoftware  ，使用过新的便更新。

--聚合函数根据group的情况，返回每个groups里的一个result。 聚合函数可以使用orderby 和 having 的子句中使用。

分析函数的over()部分的分析字句有3部分构成，分区语句，排序语句和窗口语句。
        （1）分区语句（partition by）：将查询结果分为不同的组，功能类似于group by语句,是分析函数工作的基础。默认是将所有结果作为一个分组。
        （2）排序语句(order  by)：将每个分区进行排序。
        （3）窗口语句：定义当前窗口，具体说是对每一个分组，按照给定的排序规则排序后，从分组的顶部到底部依次迭代，每次针对当前的行可以定义一个包含若干行的窗口。如果省略了窗口语句，默认使用从分组第一行到当前行的分组。

1.  Over()开窗函数

2.  Nvl()函数
3.  Rollup,Cube自动汇总函数
4.  Rank,Dense_rank,Row_number函数
5.  Lag , Lead函数
6.  Sum,Avg, Count, Max函数
7.  Ratio_to_report报表处理函数
8.  First,Last，First_value，Last_value取基数的分析函数

9.  Greatest,Least 函数

10. Trunc, Round,Decode, Substr函数

1. /* Formattedon 2009/11/08 20:36 (Formatter Plus v4.8.8) */  
2. SELECT  GROUPING_ID (student_name, subjects), student_name, subjects,  
3.          SUM (score)  
4.     FROM studentscore  
5. GROUP BY ROLLUP (student_name, subjects)  
6. ORDER BY 1;  
7.    
8.    
9. /* Formattedon 2009/11/08 20:36 (Formatter Plus v4.8.8) */  
10. SELECT  GROUPING (student_name), GROUPING (subjects),  
11.          CASE  
12.             WHEN GROUPING (student_name) = 0  
13.                  AND GROUPING (subjects) = 1  
14.                THEN ' 学生成绩合计 '  
15.             WHEN GROUPING (student_name) = 1  
16.                  AND GROUPING (subjects) = 0  
17.                THEN ' 课目成绩合计 '  
18.             WHEN GROUPING (student_name) = 1  
19.                  AND GROUPING (subjects) = 1  
20.                THEN ' 总                 计 '  
21.             ELSE ''  
22.          END summary,  
23.          student_name, subjects, SUM (score)  
24.     FROM studentscore  
25. GROUP BY CUBE (student_name, subjects)  
26. ORDER BY 1, 2;  

 （1） ROW_NUMBER：

Row_number函数返回一个唯一的值，当碰到相同数据时，排名按照记录集中记录的顺序依次递增。

 （2）DENSE_RANK：

Dense_rank函数返回一个唯一的值，除非当碰到相同数据时，此时所有相同数据的排名都是一样的。

 （3） RANK：

Rank函数返回一个唯一的值，除非遇到相同的数据时，此时所有相同数据的排名是一样的，同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。

2.5 lag,lead函数

lag(expression,<offset>,<default>)  函数可以访问组内当前行之前的行，

lead(expression,<offset>,<default>) 函数则正相反，可以反问组内当前行之后的行．
其中，offset是正整数，默认为1．因组内第一个条记录没有之前的行，最后一行没有之后的行，它表示要取列第N行之前或者之后的值，default就是用于处理这样的信息，默认为空．它用于当之前或者之后第N行不存在时的值。

分析函数RATIO_TO_REPORT 用来计算当前记录的指标expr占开窗函数over中包含记录的所有同一指标的百分比. 这里如果开窗函数的统计结果为null或者为0,就是说占用比率的被除数为0或者为null, 则得到的结果也为0.

行转列函数   pivot 

c4 as (select * from b4 pivot (sum(money) tm for (ap) in (('a') a,('b') b,('c') c,('d') d,('e') e)))  

select * from t pivot (sum(a) aa,sum(b) bb for ( c,d) in (('啊',3) ss,('a',5) sss))

create table test(id number, name varchar2(20));

 

insert into test values (1, 'a' );

insert into test values (1, 'b' );

insert into test values (1, 'c' );

insert into test values (2, 'd' );

insert into test values (2, 'e' );

1	select wm_concat( name ) name from test;

to_char(substr(mw.context_,1,1000))

1. select regexp_substr('a,b,c,','[^,]+',1,rownum)   
2.  from dual connect by rownum<=length(regexp_replace('a,b,c,', '[^,]', null))   

SELECT REGEXP_SUBSTR (‘first field, second field , third field’, ‘[^,]+’, 1,rownum) 

    FROM DUAL 

    CONNECT BY ROWNUM <= 

    LENGTH (‘first field, second field , third field’) – LENGTH (REPLACE (‘first field, second field , third field’, ‘,’, ”)) 

    +1