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;