我們透過這個文章來看一下分析函式LAST_VALUE的功效。
【Analytic】分析函式之MIN函式：http://space.itpub.net/519536/viewspace-624736
【Analytic】分析函式之MAX函式：http://space.itpub.net/519536/viewspace-624749
【Analytic】分析函式之AVG函式：http://space.itpub.net/519536/viewspace-624799
【Analytic】分析函式之ROW_NUMBER函式：http://space.itpub.net/519536/viewspace-624886
【Analytic】分析函式之RANK函式：http://space.itpub.net/519536/viewspace-624985
【Analytic】分析函式之DENSE_RANK函式：http://space.itpub.net/519536/viewspace-625115
【Analytic】分析函式之COUNT函式：http://space.itpub.net/519536/viewspace-625191
【Analytic】分析函式之FIRST_VALUE函式：http://space.itpub.net/519536/viewspace-625280

1.萬變不離其宗，先看LAST_VALUE分析函式的10g語法描述。

LAST_VALUE(expr [ IGNORE NULLS ])
   OVER (analytic_clause)

2.透過實驗看一下分析函式LAST_VALUE的使用方法
1）建立測試表T，並初始化9條資料。
sec@ora10g> create table t (group_id number(10), name varchar2(10), salary int);
sec@ora10g> insert into t values (1,'Tom',1200);
sec@ora10g> insert into t values (2,'Kary',2400);
sec@ora10g> insert into t values (2,'Joe',800);
sec@ora10g> insert into t values (3,'Andy',100);
sec@ora10g> insert into t values (3,'Ellen',200);
sec@ora10g> insert into t values (3,'Erick',300);
sec@ora10g> insert into t values (3,'Hou',400);
sec@ora10g> insert into t values (3,'Mary',500);
sec@ora10g> insert into t values (3,'Secooler',600);
sec@ora10g> commit;

2）T表全貌
sec@ora10g> select * from t order by group_id,name;

GROUP_ID NAME                               SALARY
---------- ------------------------------ ----------
         1 Tom                                  1200
         2 Joe                                   800
         2 Kary                                 2400
         3 Andy                                  100
         3 Ellen                                 200
         3 Erick                                 300
         3 Hou                                   400
         3 Mary                                  500
         3 Secooler                              600

9 rows selected.

共三組資料，group_id分別是1、2和3。第1組有一個人，第2組有兩個人，第3組有六個人。最後一列是每個人的薪水值。

3）LAST_VALUE分析函式的簡單用法
（1）在T表中新增一列，標識每一個資料分割槽中薪水最高的人名。
sec@ora10g> col highest_sal_name for a16
sec@ora10g> select group_id, name, salary, LAST_VALUE(name) OVER (partition by group_id order by salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as highest_sal_name from t order by group_id, name;

GROUP_ID NAME                               SALARY HIGHEST_SAL_NAME
---------- ------------------------------ ---------- ----------------
         1 Tom                                  1200 Tom
         2 Joe                                   800 Kary
         2 Kary                                 2400 Kary
         3 Andy                                  100 Secooler
         3 Ellen                                 200 Secooler
         3 Erick                                 300 Secooler
         3 Hou                                   400 Secooler
         3 Mary                                  500 Secooler
         3 Secooler                              600 Secooler

9 rows selected.

注意其中“ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING”的使用，若省略效果如下。
sec@ora10g> select group_id, name, salary, LAST_VALUE(name) OVER (partition by group_id order by salary) as highest_sal_name from t order by group_id,name;

GROUP_ID NAME                               SALARY HIGHEST_SAL_NAME
---------- ------------------------------ ---------- ----------------
         1 Tom                                  1200 Tom
         2 Joe                                   800 Joe
         2 Kary                                 2400 Kary
         3 Andy                                  100 Andy
         3 Ellen                                 200 Ellen
         3 Erick                                 300 Erick
         3 Hou                                   400 Hou
         3 Mary                                  500 Mary
         3 Secooler                              600 Secooler

9 rows selected.

顯然這不是我們想要的效果:(，這是為什麼呢~~~？給您一次思考和回答的機會。
如果對UNBOUNDED PRECEDING和UNBOUNDED FOLLOWING不熟悉，請參考Oracle官方文件“windowing_clause”。

（2）在T表中新增一列，標識每一個資料分割槽中薪水最高的薪水值。
sec@ora10g> col highest_sal_name for 9999
sec@ora10g> select group_id, name, salary, LAST_VALUE(SALARY) OVER (partition by group_id order by salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as highest_sal_name from t order by group_id, name;

GROUP_ID NAME                               SALARY HIGHEST_SAL_NAME
---------- ------------------------------ ---------- ----------------
         1 Tom                                  1200             1200
         2 Joe                                   800             2400
         2 Kary                                 2400             2400
         3 Andy                                  100              600
         3 Ellen                                 200              600
         3 Erick                                 300              600
         3 Hou                                   400              600
         3 Mary                                  500              600
         3 Secooler                              600              600

9 rows selected.

3.小結
分析函式LAST_VALUE可以非常便利並迅速的的得到排序後的最後一條資料的各種資訊。但，細節之處不容忽視，細心使用。
有關分析函式的擴充套件可以參考Oracle的官方文件中的“Analytic Functions”描述：

Good luck.

secooler
10.01.16

-- The End --

【Analytic】分析函式之LAST_VALUE函式

相關文章