oracle 分析函数 第二,oracle分析函数：二、秩运算（DENSE_RANK函数，RANK函数）

/************************

*标题：DENSE_RANK 函数

*时间：2016-07-07

*作者：clark

**目的：

计算一组有序行组，每一个行的秩,并且返回数字；

秩：是从1开始的连续整数，

–注：

(1)Rank values are not skipped in the event of ties,

等级值不会跳过

Rows with equal values for the ranking criteria receive the same rank

相同值相同的rank

(推理得出最大的秩是查询结果集去重之后的个数)

(2)This function is useful for top-N and bottom-N reporting.

*************************/

–Analytic Syntax

–分析函数 语法

–dense_rank_analytic::=

dense_rank() over( partition by expr order by expr )

–Aggregate Syntax

–聚合函数 语法

–dense_rank_aggregate::=

dense_rank(expr) within group (order by expr desc nulls last )

–聚合函数 使用

select out_row

from j1_dw.etl_exdw_log

where unit_id=’100.02’

order by out_row desc nulls last;

–result:

23632 –1

23161 –2

23161 –2

23161 –2

22997 –3

22519 –4

22283 –5

22061 –6

22061 –6

22039 –7

–dense_rank

select dense_rank(22997) within group (order by out_row desc nulls last )

from j1_dw.etl_exdw_log

where unit_id=’100.02’ –3

–rank

select rank(22997) within group (order by out_row desc nulls last )

from j1_dw.etl_exdw_log

where unit_id=’100.02’ –5

–分析函数的使用

SELECT unit_id,

tjrq_q,

out_row,

dense_rank() over(partition by unit_id order by out_row)

FROM etl_exdw_log

ORDER BY unit_id,out_row

–result:

100.02 20150401 -1 1

100.02 20150401 -1 1

100.02 20150401 -1 1

100.02 20150401 -1 1

100.02 20150101 22039 2

100.02 20150101 22061 3

100.02 20150101 22061 3

100.02 20150201 22283 4

100.02 20150301 22519 5

100.02 20150401 22997 6

/************************

*标题：RANK 函数

*时间：2016-07-07

*作者：clark

**目的：

计算一组有序行组，每一个行的秩,并且返回数字；

–注：

(1)Rows with equal values for the ranking criteria receive the same rank.

Oracle Database then adds the number of tied rows to the tied rank to calculate the next rank.

值相同的列的数量，添加到相同的秩来计算下一个秩

Therefore,the ranks may not be consecutive numbers.

排名可能不是连续的数字

(2)This function is useful for top-N and bottom-N reporting.

*************************/

–分析函数的使用

SELECT unit_id,tjrq_q,out_row,rank() over(partition by unit_id order by out_row) FROM etl_exdw_log ORDER BY unit_id,out_row –result: 100.02 20150401 -1 1 100.02 20150401 -1 1 100.02 20150401 -1 1 100.02 20150401 -1 1 100.02 20150101 22039 5 100.02 20150101 22061 6 100.02 20150101 22061 6 100.02 20150201 22283 8 100.02 20150301 22519 9 100.02 20150401 22997 10 100.02 20150401 23161 11 100.02 20150401 23161 11 100.02 20150401 23161 11