Oracle SQL作為標準SQL的一種有力補充，無論是在預定義函式還是語法語義，都有了很多強大的功能。我們日常工作中，經常遇到各種各樣的SQL報表和查詢需求，用好Oracle SQL語句和特性，可以幫助我們更好、更快的實現需求。

本篇介紹兩個在研究過程中使用的兩個特性，記錄下來，供有需要的朋友查詢。

1、空值排序位置控制——NULLS FIRST/LAST

在Oracle中，NULL值是一種非常特殊的型別。大多數的操作中如果涉及到NULL值，意味著操作結果是NULL。排序Order過程中，如果NULL值存在在資料列中，那麼NULL值是算最小還是最大值呢？

我們透過實驗來驗證，首先，選擇11gR2的環境。

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE 11.2.0.1.0 Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 – Production

構建一個小資料表T。

SQL> create table t as select object_id, owner from dba_objects where rownum<10;

Table created

SQL> desc t;

Name Type Nullable Default Comments

--------- ------------ -------- ------- --------

OBJECT_ID NUMBER Y

OWNER VARCHAR2(30) Y

SQL> update t set object_id=null where rownum<3;

2 rows updated

SQL> commit;

Commit complete

SQL> select * from t;

OBJECT_ID OWNER

---------- ------------------------------

SYS

28 SYS

15 SYS

29 SYS

3 SYS

25 SYS

41 SYS

54 SYS

9 rows selected

檢視預設的排序行為。

--數字型別排列

SQL> select * from t order by object_id;

OBJECT_ID OWNER

---------- ------------------------------

3 SYS

15 SYS

25 SYS

28 SYS

29 SYS

41 SYS

54 SYS

SYS

9 rows selected

--字串排列

SQL> update t set wner=null where rownum<3 and object_id is not null;

2 rows updated

SQL> commit;

Commit complete

SQL> select * from t order by owner;

OBJECT_ID OWNER

---------- ------------------------------

SYS

29 SYS

41 SYS

54 SYS

3 SYS

25 SYS

15

28

9 rows selected

預設情況下，數字和字串型別的排列過程中，null值是排列在後面，位於末尾。

在Oracle中，我們是可以控制空值的排列順序的。具體的方法就是使用NULLS FIRST和NULLS LAST。

NULLS FIRST顧名思義，就是將空值排列在結果集合前面。

SQL> select * from t order by object_id nulls first;

OBJECT_ID OWNER

---------- ------------------------------

SYS

3 SYS

15

25 SYS

28

29 SYS

41 SYS

54 SYS

9 rows selected

同樣道理，使用NULLS LAST就是將空值排列在後面。

SQL> select * from t order by object_id nulls last;

OBJECT_ID OWNER

---------- ------------------------------

3 SYS

15

25 SYS

28

29 SYS

41 SYS

54 SYS

SYS

9 rows selected

NULLS LAST是order by的預設選項。使用nulls first/last，就可以方便的控制空值出現的位置，應對不同的情景要求。

2、獲取前n月的平均值——AVG巧用

各種彙總報表中，有一種依託時間進行時間序列分析的報表。這種報表中經常包括“前n個月資料平均值”。我們首先構建出實驗資料表。

SQL> create table t_sample (bsp_code varchar2(10), bill_period varchar2(6), curr_value number, avg_value number);

Table created

Executed in 0.078 seconds

（輸入資料過程略……）

SQL> select * from t_sample;

BSP_CODE BILL_PERIOD CURR_VALUE AVG_VALUE

---------- ----------- ---------- ----------

CN 130301 103

CN 130302 144

CN 130303 43

CN 130304 344

CN 130401 444

AU 130102 444

AU 130103 344

AU 130104 34

8 rows selected

Executed in 0.047 seconds

不同BSP_CODE表示不同的組織地區，bill period表示時間的序號，從小到大排列。Curr_value表示當前地區在當前時間期間的銷售額度。

現在要求在顯示本期銷售資料的時候，還要顯示包括當前期在內的2期（一個BSP_CODE內）的銷售平均值。

這個需求的難點在於兩點：組內分析和有限數目求平均值操作。組內分組是指必須在相同的BSP_CODE內進行處理。有限數目求平均表示如何進行控制2期的平均值計算過程。

此時，我們需要使用avg平均值函式的擴充功能。Oracle對avg函式進行了擴充，使用over中的partition可以控制統計彙總的視窗範圍。此外rows between可以控制聚合函式的前後操作範圍。

SQL> select bsp_code, bill_period, curr_value,

2 avg(curr_value) over (partition by bsp_code order by bill_period

3 rows between 2 preceding and 0 following) as avg_value

4 from t_sample;

BSP_CODE BILL_PERIOD CURR_VALUE AVG_VALUE

---------- ----------- ---------- ----------

AU 130102 444 444

AU 130103 344 394

AU 130104 34 274

CN 130301 103 103

CN 130302 144 123.5

CN 130303 43 96.6666666

CN 130304 344 177

CN 130401 444 277

8 rows selected

Executed in 0.031 seconds

Rows between 2 preceding and 0 following，表示聚合函式處理範圍是向前2個處理期，向後0個處理期。

3、結論

Oracle SQL語句功能很強大。學習點滴，留待友人待查。

兩個SQL語句技巧——NULLS FIRST/LAST和分析函式版AVG

相關文章