Oracle分析函数与分组关键字的用法

Oracle分析函数与分组关键字的用法
以下是我以前工作中做报表常用的几个函数，在此分享一下，希望对大家有帮助。

(一)分析函数
●row_number
Purpose
ROW_NUMBER is an analytic function. It assigns a unique number to each row to which it is applied (either each row in the partition or each row returned by the query), in the ordered sequence of rows specified in the order_by_clause, beginning with 1.
You cannot use ROW_NUMBER or any other analytic function for expr. That is, you can use other built-in function expressions for expr, but you cannot nest analytic functions.
按部门分组后根据工资排序，序号rn特征：连续、无并列
select t.*, row_number() over(partition
by t.deptno order
by sal desc) rn from emp t;
●rank
Purpose
RANK calculates the rank of a value in a group of values. Rows with equal values for the ranking criteria receive the same rank. Oracle then adds the number of tied rows to the tied rank to calculate the next rank. Therefore, the ranks may not be consecutive numbers.
•
As an aggregate function, RANK calculates the rank of a hypothetical row identified by the arguments of the function with respect to a given sort specification. The arguments of the function must all evaluate to constant expressions within each aggregate group, because they identify a single row within each group. The constant argument expressions and the expressions in the ORDER BY clause of the aggregate match by position. Therefore, the number of arguments must be the same and their types must be compatible.
As an analytic function, RANK computes the rank of each row returned from a query with respect to the other rows returned by the query, based on the values of the value_exprs in the order_by_clause.
按部门分组后根据工资排序，序号rn特征：不连续、有并列
select t.*, rank() over(partition
by t.deptno order
by sal desc) rn from emp t;
dense_rank
Purpose
DENSE_RANK computes the rank of a row in an ordered group of rows. The ranks are consecutive integers beginning with 1. The largest rank value is the number of unique values returned by the query. Rank values are not skipped in the event of ties. Rows with equal values for the ranking criteria receive the same rank.
As an aggregate function, DENSE_RANK calculates the dense rank of a hypothetical row identified by the arguments of the function with respect to a given sort specification. The arguments of the function must all evaluate to constant expressions within each aggregate group, because they identify a single row within each group. The constant argument expressions and the expressions in the order_by_clause of the aggregate match by position. Therefore, the number of arguments must be the same and types must be compatible.
As an analytic function, DENSE_RANK computes the rank of each row returned from a query with respect to the other rows, based on the values of the value_exprs in the order_by_clause.
按部门分组后根据工资排序，序号rn特征：连续、有并列
select t.*, dense_rank() over(partition
by t.deptno order
by sal desc) rn from emp t;
(二)分组函数
根据查询结果观察三者的区别，grouping sets用起来更灵活。

友情提示：grouping（expr）函数仅用于分组中，如果expr为null 返回1
●Rollup
select decode(grouping(dept.dname), 1, '总计', dept.dname) dname, decode(grouping(emp.job), 1, '小计', emp.job) job,
sum(sal),
avg(sal)
from emp, dept
where dept.deptno = emp.deptno
group by rollup(dept.dname, emp.job)
order by dname, job;
●Cube
select decode(grouping(dept.dname), 1, '所有部门', dept.dname) dname, nvl(emp.job, '小计') job,
sum(sal),
avg(sal)
from emp, dept
where dept.deptno = emp.deptno
group by cube(dept.dname, emp.job)
order by dname, job;
grouping sets
select dept.dname, emp. job, sum(sal), avg(sal)
from emp, dept
where dept.deptno = emp.deptno
group by grouping sets((dept.dname, emp.job), dept.dname, emp.job) order by dname, job;。