基本转自 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函数
- /* Formattedon 2009/11/08 20:36 (Formatter Plus v4.8.8) */
- SELECT GROUPING_ID (student_name, subjects), student_name, subjects,
- SUM (score)
- FROM studentscore
- GROUP BY ROLLUP (student_name, subjects)
- ORDER BY 1;
- /* Formattedon 2009/11/08 20:36 (Formatter Plus v4.8.8) */
- SELECT GROUPING (student_name), GROUPING (subjects),
- CASE
- WHEN GROUPING (student_name) = 0
- AND GROUPING (subjects) = 1
- THEN ' 学生成绩合计 '
- WHEN GROUPING (student_name) = 1
- AND GROUPING (subjects) = 0
- THEN ' 课目成绩合计 '
- WHEN GROUPING (student_name) = 1
- AND GROUPING (subjects) = 1
- THEN ' 总 计 '
- ELSE ''
- END summary,
- student_name, subjects, SUM (score)
- FROM studentscore
- GROUP BY CUBE (student_name, subjects)
- 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))
- select regexp_substr('a,b,c,','[^,]+',1,rownum)
- from dual connect by rownum<=length(regexp_replace('a,b,c,', '[^,]', null))