原創(chuàng)于2009年08月02日，2009年10月22日遷移至此。

Oracle 分析函數(shù)——函數(shù) RANK,DENSE_RANK,FIRST,LAST…

RANK

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

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

DENSE_RANK

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

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

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 返回的集合中取出排在最前面的一個值的行（可能多行，因?yàn)橹悼赡芟嗟龋虼送暾恼Z法需要在開始處加上一個集合函數(shù)以從中取出記錄

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

LAST

功能描述：從 DENSE_RANK 返回的集合中取出排在最后面的一個值的行（可能多行，因?yàn)橹悼赡芟嗟龋虼送暾恼Z法需要在開始處加上一個集合函數(shù)以從中取出記錄

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

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

功能描述：返回組中數(shù)據(jù)窗口的第一個值。

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

LAST_VALUE

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

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

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 salaryASC ) 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 的標(biāo)準(zhǔn)似乎有些不一樣，不過單獨(dú)執(zhí)行就很清楚了，呵呵

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

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

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

LEAD

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

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

功能描述：返回有序組中一行的偏移量，從而可用于按特定標(biāo)準(zhǔn)排序的行號。

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

Oracle分析函數(shù)四——函數(shù)RANK,DENSE_RANK,FIRST,LAST…