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

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

[img]http://dl.iteye.com/upload/attachment/0075/4501/cd75cfd0-56a4-3215-a1d8-78d96cf26fb4.jpg[/img]

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

[img]http://dl.iteye.com/upload/attachment/0075/4504/b5454688-b3fd-3573-af1a-bf5c22c85ff7.jpg[/img]

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

[img]http://dl.iteye.com/upload/attachment/0075/4506/8f36dddc-d27a-387b-81cf-1571352a5536.jpg[/img]

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

[img]http://dl.iteye.com/upload/attachment/0075/4508/84a524e3-4a59-3b56-8eeb-b24244c3f856.jpg[/img]

看起來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

[img]http://dl.iteye.com/upload/attachment/0075/4510/f1dfcf22-9780-36fa-821b-4057d64e5c6b.jpg[/img]

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

[img]http://dl.iteye.com/upload/attachment/0075/4513/80614329-5c22-3a2e-906b-259036e3f19e.jpg[/img]

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

[img]http://dl.iteye.com/upload/attachment/0075/4515/97af1976-f965-33d2-acf3-e2567979c0b0.jpg[/img]