原創于2009年08月02日，2009年10月22日遷移至此。

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 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 的標準似乎有些不一樣，不過單獨執行就很清楚了，呵呵

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

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