Oracle分析函数学习笔记1.docx

Oracle分析函数学习笔记1.docx

《Oracle分析函数学习笔记1.docx》由会员分享，可在线阅读，更多相关《Oracle分析函数学习笔记1.docx（15页珍藏版）》请在冰豆网上搜索。

Oracle分析函数学习笔记1

环境：

windows2000server+Oracle8.1.7+sql*plus

目的：

以oracle自带的scott模式为测试环境，主要通过试验体会分析函数的用法。

1.row_number（）的使用

原表信息：

SQL>breakondeptnoskip1 --为效果更明显，把不同部门的数据隔段显示。

SQL>selectdeptno,ename,sal

 2 fromemp

 3 orderbydeptno,saldesc;

   DEPTNOENAME            SAL

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

       10KING            5000

          CLARK           2450

          MILLER          1300

       20SCOTT           3000

          FORD            3000

          JONES           2975

          ADAMS           1100

          SMITH            800

       30BLAKE           2850

          ALLEN           1600

          TURNER          1500

          WARD            1250

          MARTIN          1250

          JAMES            950

已选择14行。

使用row_number（）查出各部门薪水最高的三个员工姓名、薪水，多于三个的只取三个。

SQL>select*from（

 2 selectdeptno,row_number（）over（partitionbydeptnoorderbysaldesc）rw,ename,sal

 3 fromemp

 4 ）

 5 whererw<=3;

   DEPTNO        RWENAME            SAL

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

       10         1KING            5000

                   2CLARK           2450

                   3MILLER          1300

       20         1SCOTT           3000

                   2FORD            3000

                   3JONES           2975

       30         1BLAKE           2850

                   2ALLEN           1600

                   3TURNER          1500

已选择9行。

体会：

row_number（）返回的主要是“行”的信息，并没有按照sal排名，如

       20         1SCOTT           3000

                   2FORD            3000

scott与ford薪水一样多，ford前面的2仅仅是行数、记录条数的感念。

2.rank函数的介绍

介绍完rollup和cube函数的使用,下面我们来看看rank系列函数的使用方法.

问题2.我想查出这几个月份中各个地区的总话费的排名.

  Quote:

为了将rank,dense_rank,row_number函数的差别显示出来,我们对已有的基础数据做一些修改,将5763的数据改成与5761的数据相同.

  1  updatett1setlocal_fare=（

  2    selectlocal_farefromtt2

  3     wheret1.bill_month=t2.bill_month

  4     and_type=_type

  5     andt2.area_code='5761'

  6*）wherearea_code='5763'

07:

19:

18SQL>/

8rowsupdated.

Elapsed:

00:

00:

00.01

我们先使用rank函数来计算各个地区的话费排名.

07:

34:

19SQL>selectarea_code,sum（local_fare）local_fare,

07:

35:

25   2    rank（）over（orderbysum（local_fare）desc）fare_rank

07:

35:

44   3  fromt

07:

35:

45   4  groupbyarea_codee

07:

35:

50   5

07:

35:

52SQL>selectarea_code,sum（local_fare）local_fare,

07:

36:

02   2    rank（）over（orderbysum（local_fare）desc）fare_rank

07:

36:

20   3  fromt

07:

36:

21   4  groupbyarea_code

07:

36:

25   5  /

AREA_CODE      LOCAL_FARE  FARE_RANK

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

5765            104548.72          1

5761             54225.41          2

5763             54225.41          2

5764             53156.77          4

5762             52039.62          5

Elapsed:

00:

00:

00.01

我们可以看到红色标注的地方出现了,跳位,排名3没有出现

下面我们再看看dense_rank查询的结果.

07:

36:

26SQL>selectarea_code,sum（local_fare）local_fare,

07:

39:

16   2    dense_rank（）over（orderbysum（local_fare）desc）fare_rank

07:

39:

39   3  fromt

07:

39:

42   4  groupbyarea_code

07:

39:

46   5  /

AREA_CODE      LOCAL_FARE  FARE_RANK

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

5765            104548.72          1

5761             54225.41          2

5763             54225.41          2

5764             53156.77          3  这是这里出现了第三名

5762             52039.62          4

Elapsed:

00:

00:

00.00

在这个例子中,出现了一个第三名,这就是rank和dense_rank的差别,

rank如果出现两个相同的数据,那么后面的数据就会直接跳过这个排名,而dense_rank则不会,

差别更大的是,row_number哪怕是两个数据完全相同,排名也会不一样,这个特性在我们想找出对应没个条件的唯一记录的时候又很大用处

  1  selectarea_code,sum（local_fare）local_fare,

  2     row_number（）over（orderbysum（local_fare）desc）fare_rank

  3  fromt

  4*groupbyarea_code

07:

44:

50SQL>/

AREA_CODE      LOCAL_FARE  FARE_RANK

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

5765            104548.72          1

5761             54225.41          2

5763             54225.41          3

5764             53156.77          4

5762             52039.62          5

在row_nubmer函数中,我们发现,哪怕sum（local_fare）完全相同,我们还是得到了不一样排名,我们可以利用这个特性剔除数据库中的重复记录.

这个帖子中的几个例子是为了说明这三个函数的基本用法的.下个帖子我们将详细介绍他们的一些用法.

2.rank函数的介绍

a.取出数据库中最后入网的n个用户

selectuser_id,tele_num,user_name,user_status,create_date

from（

   selectuser_id,tele_num,user_name,user_status,create_date,

      rank（）over（orderbycreate_datedesc）add_rank

   fromuser_info

）

whereadd_rank<=:

n;

b.根据object_name删除数据库中的重复记录

createtabletasselectobj#,namefromsys.obj$;

再insertintot1select*fromt1数次.

deletefromt1whererowidin（

   selectrow_idfrom（

      selectrowidrow_id,row_number（）over（partitionbyobj#orderbyrowid）rn

   ）wherern<>1

）;

c.取出各地区的话费收入在各个月份排名.

SQL>selectbill_month,area_code,sum（local_fare）local_fare,

  2     rank（）over（partitionbybill_monthorderbysum（local_fare）desc）area_rank

  3  fromt

  4  groupbybill_month,area_code

  5  /

BILL_MONTH      AREA_CODE           LOCAL_FARE  AREA_RANK

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

200405          5765                  25057.74          1

200405          5761                  13060.43          2

200405          5763                  13060.43          2

200405          5762                  12643.79          4

200405          5764                  12487.79          5

200406          5765                  26058.46          1

200406          5761                  13318.93          2

200406          5763                  13318.93          2

200406          5764                  13295.19          4

200406          5762                  12795.06          5

200407          5765                  26301.88          1

200407          5761                  13710.27          2

200407          5763                  13710.27          2

200407          5764                  13444.09          4

200407          5762                  13224.30          5

200408          5765                  27130.64          1

200408          5761                  14135.78          2

200408          5763                  14135.78          2

200408          5764                  13929.69          4

200408          5762                  13376.47          5

20rowsselected.

SQL>

3.lag和lead函数介绍

取出每个月的上个月和下个月的话费总额

  1  selectarea_code,bill_month,local_farecur_local_fare,

  2     lag（local_fare,2,0）over（partitionbyarea_codeorderbybill_month）pre_local_fare,

  3     lag（local_fare,1,0）over（partitionbyarea_codeorderbybill_month）last_local_fare,

  4     lead（local_fare,1,0）over（partitionbyarea_codeorderbybill_month）next_local_fare,

  5     lead（local_fare,2,0）over（partitionbyarea_codeorderbybill_month）post_local_fare

  6  from（

  7     selectarea_code,bill_month,sum（local_fare）local_fare

  8     fromt

  9     groupbyarea_code,bill_month

10*）

SQL>/

AREA_CODEBILL_MONTHCUR_LOCAL_FAREPRE_LOCAL_FARELAST_LOCAL_FARENEXT_LOCAL_FAREPOST_LOCAL_FARE

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

5761      200405          13060.433              0               0        13318.93       13710.265

5761      200406           13318.93              0       13060.433       13710.265       14135.781

5761      200407          13710.265      13060.433        13318.93       14135.781               0

5761      200408          14135.781       13318.93       13710.265               0               0

5762      200405          12643.791              0               0        12795.06       13224.297

5762      200406           12795.06              0       12643.791       13224.297       13376.468

5762      200407          13224.297      12643.791        12795.06       13376.468               0

5762      200408          13376.468       12795.06       13224.297               0               0

5763      200405          13060.433              0               0        13318.93       13710.265

5763      200406           13318.93              0       13060.433       13710.265       14135.781

5763      200407          13710.265      13060.433        13318.93       14135.781               0

5763      200408          14135.781       13318.93       13710.265               0               0

5764      200405          12487.791              0               0       13295.187       13444.093

5764      200406          13295.187              0       12487.791       13444.093       13929.694

5764      200407          13444.093      12487.791       13295.187       13929.694               0

5764      200408          13929.694      13295.187       13444.093               0               0

5765      200405          25057.736              0               0        26058.46       26301.881

5765      200406           26058.46              0       25057.736       26301.881       27130.638

5765      200407          26301.881      25057.736        26058.46       27130.638               0

5765      200408          27130.638       26058.46       26301.881               0               0

20rowsselected.

利用lag和lead函数,我们可以在同一行中显示前n行的数据,也可以显示后n行的数据.

4.sum,avg,max,min移动计算数据介绍

计算出各个连续3个月的通话费用的平均数

  1  selectarea_code,bill_month,local_fare,

  2     sum（local_fare）

  3             over（  partitionbyarea_code

  4                     orderbyto_number（bill_month）

  5                     rangebetween1precedingand1following）"3month_sum",

  6     avg（local_fare）

  7             over（  partitionbyarea_code

  8                     orderbyto_number（bill_month）

  9                     rangebetween1precedingand1following）"3month_avg",

10     max（local_fare）

11             over（  partitionbyarea_code

12                     orderbyto_number（bill_month）

13                     rangebetween1precedingand1following）"3month_max",

14     min（local_fare）