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