Oracle分析函數——函數RANK,DENSE_RANK,FIRST,LAST…

RANK

功能描述：根據ORDER BY子句中表達式的值，從查詢返回的每一行，計算它們與其它行的相對位置。組內的數據按ORDER BY子句排序，然后給每一行賦一個號，從而形成一個序列，該序列從1開始，往后累加。每次ORDER BY表達式的值發生變化時，該序列也隨之增加。有同樣值的行得到同樣的數字序號（認為null時相等的）。然而，如果兩行的確得到同樣的排序，則序數將隨后跳躍。若兩行序數為1，則沒有序數2，序列將給組中的下一行分配值3，DENSE_RANK則沒有任何跳躍。

SAMPLE：下例中計算每個員工按部門分區再按薪水排序，依次出現的序列號（注意與DENSE_RANK函數的區別）

?

DENSE_RANK

功能描述：根據ORDER BY子句中表達式的值，從查詢返回的每一行，計算它們與其它行的相對位置。組內的數據按ORDER BY子句排序，然后給每一行賦一個號，從而形成一個序列，該序列從1開始，往后累加。每次ORDER BY表達式的值發生變化時，該序列也隨之增加。有同樣值的行得到同樣的數字序號（認為null時相等的）。密集的序列返回的時沒有間隔的數

SAMPLE：下例中計算每個員工按部門分區再按薪水排序，依次出現的序列號（注意與RANK函數的區別）

?select department_id, first_name || ' ' || last_name employee_name, salary, rank() over(order by salary) as rank_order, dense_rank() over(order by salary) as dense_rank_order from employees

?

select department_id, first_name || ' ' || last_name employee_name, salary, rank() over(partition by department_id order by salary) as rank_part_order, dense_rank() over(partition by department_id order by salary) as dense_rank_part_order from employees

FIRST

功能描述：從DENSE_RANK返回的集合中取出排在最前面的一個值的行（可能多行，因為值可能相等），因此完整的語法需要在開始處加上一個集合函數以從中取出記錄

SAMPLE：下面例子中DENSE_RANK按部門分區，再按傭金commission_pct排序，FIRST取出傭金最低的對應的所有行，然后前面的MAX函數從這個集合中取出薪水最低的值；LAST取出傭金最高的對應的所有行，然后前面的MIN函數從這個集合中取出薪水最高的值

?

LAST

功能描述：從DENSE_RANK返回的集合中取出排在最后面的一個值的行（可能多行，因為值可能相等），因此完整的語法需要在開始處加上一個集合函數以從中取出記錄

SAMPLE：下面例子中DENSE_RANK按雇用日期排序，FIRST取出salary最低的對應的所有行，然后前面的MAX函數從這個集合中取出薪水最低的值；LAST取出雇用日期最高的對應的所有行，然后前面的MIN函數從這個集合中取出薪水最高的值

select department_id, first_name || ' ' || last_name employee_name, hire_date, salary, min(salary) keep(dense_rank first order by hire_date) over(partition by department_id) "Worst", max(salary) keep(dense_rank last order by hire_date) over(partition by department_id) "Best" from employees

?

FIRST_VALUE

功能描述：返回組中數據窗口的第一個值。

SAMPLE：下面例子計算按部門分區按薪水排序的數據窗口的第一個值對應的名字，如果薪水的第一個值有多個，則從多個對應的名字中取缺省排序的第一個名字

?

LAST_VALUE

功能描述：返回組中數據窗口的最后一個值。

SAMPLE：下面例子計算按部門分區按薪水排序的數據窗口的最后一個值對應的名字，如果薪水的最后一個值有多個，則從多個對應的名字中取缺省排序的最后一個名字

select department_id, first_name || ' ' || last_name employee_name, hire_date, salary, first_value(first_name || ' ' || last_name) over(partition by department_id order by salary asc) as lowest_sal, last_value(first_name || ' ' || last_name) over(partition by department_id order by salary) as highest_sal from employees

?

看起來last_value和first_value的標準似乎有些不一樣，不過單獨執行就很清楚了，呵呵

select department_id, first_name || ' ' || last_name employee_name, hire_date, salary, first_value(first_name || ' ' || last_name) over(partition by department_id order by salary) as lowest_sal, first_value(first_name || ' ' || last_name) over(partition by department_id order by salary desc) as highest_sal, last_value(first_name || ' ' || last_name) over(partition by department_id order by salary) as last_sal, last_value(first_name || ' ' || last_name) over(partition by department_id order by salary desc) as last_sal_desc from employees

LAG

功能描述：可以訪問結果集中的其它行而不用進行自連接。它允許去處理游標，就好像游標是一個數組一樣。在給定組中可參考當前行之前的行，這樣就可以從組中與當前行一起選擇以前的行。Offset是一個正整數，其默認值為1，若索引超出窗口的范圍，就返回默認值（默認返回的是組中第一行），其相反的函數是LEAD

SAMPLE：下面的例子中列prev_sal返回按hire_date排序的前1行的salary值

?

LEAD

功能描述：LEAD與LAG相反，LEAD可以訪問組中當前行之后的行。Offset是一個正整數，其默認值為1，若索引超出窗口的范圍，就返回默認值（默認返回的是組中第一行）

SAMPLE：下面的例子中列prev_sal返回按hire_date排序的后1行的salary值

select first_name || ' ' || last_name employee_name, hire_date, salary, lag(salary, 1, 0) over(order by hire_date) as prev_sal, lead(salary, 1, 0) over(order by hire_date) as "next_sal" from employees

?

ROW_NUMBER

功能描述：返回有序組中一行的偏移量，從而可用于按特定標準排序的行號。

SAMPLE：下例返回每個員工再在每個部門中按員工號排序后的順序號

select department_id, first_name || ' ' || last_name employee_name, employee_id, row_number() over(partition by department_id order by employee_id) as emp_id from employees

轉載于:https://www.cnblogs.com/huozhicheng/archive/2010/09/03/2533174.html

總結

以上是生活随笔為你收集整理的Oracle分析函数四——函数RANK,DENSE_RANK,FIRST,LAST…的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。