ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 两个SQL语句技巧——NULLS FIRST/LAST和分析函数版AVG

两个SQL语句技巧——NULLS FIRST/LAST和分析函数版AVG

原创 Linux操作系统 作者:realkid4 时间:2013-07-28 21:23:33 0 删除 编辑

 

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

           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

           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

           SYS

        29 SYS

        41 SYS

        54 SYS

         3 SYS

        25 SYS

        15

        28

 

9 rows selected

 

 

默认情况下,数字和字符串类型的排列过程中,null值是排列在后面,位于末尾。

 

Oracle中,我们是可以控制空值的排列顺序的。具体的方法就是使用NULLS FIRSTNULLS LAST

 

NULLS FIRST顾名思义,就是将空值排列在结果集合前面。

 

 

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

 

 OBJECT_ID OWNER

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

           SYS

           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

           SYS

 

9 rows selected

 

 

NULLS LASTorder 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平均值函数的拓展功能。Oracleavg函数进行了拓展,使用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语句功能很强大。学习点滴,留待友人待查。

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17203031/viewspace-767309/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7673592