Oracle分析函数使用总结

约定不等于承诺〃 2022-07-13 13:59 239阅读 0赞

**Oracle分析函数使用总结**  
**1.       使用评级函数**

评级函数（ranking function）用于计算等级、百分点、n分片等等，下面是几个常用到的评级函数：

RANK（）：返回数据项在分组中的排名。特点：在排名相等的情况下会在名次中留下空位

DENSE\_RANK():与RANK不同的是它在排名相等的情况下不会在名次中留下空位

CUME\_DIST()：返回特定值相对于一组值的位置：他是“cumulative distribution”(累积分布)的简写

PERCENT\_RANK()：返回某个值相对于一组值的百分比排名

NTILE():返回n分片后的值，比如三分片、四分片等等

ROW\_NUMBER():为每一条分组纪录返回一个数字

下面我们分别举例来说明这些函数的使用

1）RANK（）与DENSE-RANK（）

首先显示下我们的源表数据的结构及部分数据：

SQL> desc all\_sales;

名称                                      是否为空? 类型

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

YEAR                                      NOT NULL NUMBER(38)

MONTH                                     NOT NULL NUMBER(38)

PRD\_TYPE\_ID                               NOT NULL NUMBER(38)

EMP\_ID                                    NOT NULL NUMBER(38)

AMOUNT                                             NUMBER(8,2)

SQL> select \* from all\_sales where rownum<11;

YEAR      MONTH PRD\_TYPE\_ID     EMP\_ID     AMOUNT

\---------- ---------- ----------- ---------- ----------

2003          1           1         21   10034.84

2003          2           1         21   15144.65

2003          3           1         21   20137.83

2003          4           1         21   25057.45

2003          5           1         21   17214.56

2003          6           1         21   15564.64

2003          7           1         21   12654.84

2003          8           1         21   17434.82

2003          9           1         21   19854.57

2003         10           1         21   21754.19

已选择10行。

好接下来我们将举例来说明上述函数的使用：首先是RANK（）与DENSE-RANK（）的使用：

SQL> select

2   prd\_type\_id,sum(amount),

3   RANK() OVER (ORDER BY SUM(amount) DESC) AS rank,

4   DENSE\_RANK() OVER (ORDER BY SUM(amount) DESC) AS dense\_rank

5  from all\_sales

6  where year=2003

7  group by prd\_type\_id

8  order by rank;

PRD\_TYPE\_ID SUM(AMOUNT)       RANK DENSE\_RANK

\----------- ----------- ---------- ----------

5                      1          1

1   905081.84          2          2

3   478270.91          3          3

4   402751.16          4          4

2   186381.22          5          5

注意：这里PRD\_TYPE\_ID列为5的SUM(AMOUNT)的值为空，RANK（）和DENSE-RANK在这一行的返回值为1。因为默认状态下RANK（）和DENSE-RANK（）在递减排序中将空值指定为最高排名1，而在递增排序中则把它指定为最低排名。这里还有一个问题就是我们的例子中没有SUM(AMOUNT)相等的值，如果有的话RANK与DENSE-RANK将表现出区别比如上面的例子如果PRD\_TYPE\_ID为4的SUM（AMOUNT）的值也为：478270.91的话，那么上面语句的输出则为：

PRD\_TYPE\_ID SUM(AMOUNT)       RANK DENSE\_RANK

\----------- ----------- ---------- ----------

5                      1          1

1   905081.84          2          2

3   478270.91          3          3

4   478270.91          3          3

2   186381.22          5          4

此外这里还有两个参数来限制空值的排序即：NULLS FIRST和NULLS LAST

我们还以上面的例子来看：

SQL> select

2   prd\_type\_id,sum(amount),

3   RANK() OVER (ORDER BY SUM(amount) DESC NULLS LAST) AS rank,

4   DENSE\_RANK() OVER (ORDER BY SUM(amount) DESC NULLS LAST) AS dense\_rank

5  from all\_sales

6  where year=2003

7  group by prd\_type\_id

8\* order by rank

PRD\_TYPE\_ID SUM(AMOUNT)       RANK DENSE\_RANK

\----------- ----------- ---------- ----------

1   905081.84          1          1

3   478270.91          2          2

4   402751.16          3          3

2   186381.22          4          4

5                      5          5

可以看出刚才我们不使用NULLS LAST时PRD\_TYPE\_ID为5的空值的排序位于第一，现在则位于第五。

接下来来看分析函数与PARTITION BY子句的结合使用：

当需要把分组划分为子分组时，那么我们便可以结合PRATITION BY子句和分析函数同时使用。如下例根据月份划分销量：

SQL> select

2   prd\_type\_id,month,SUM(amount),

3   RANK() OVER (PARTITION BY month ORDER BY SUM(amount) DESC) AS rank

4  from all\_sales

5  where year=2003

6  and amount IS NOT NULL

7  GROUP BY prd\_type\_id,month

8\* ORDER BY month,rank

PRD\_TYPE\_ID      MONTH SUM(AMOUNT)       RANK

\----------- ---------- ----------- ----------

1          1    38909.04          1

3          1    24909.04          2

4          1    17398.43          3

2          1    14309.04          4

1          2     70567.9          1

4          2     17267.9          2

3          2     15467.9          3

2          2     13367.9          4

1          3    91826.98          1

4          3    31026.98          2

3          3    20626.98          3

PRD\_TYPE\_ID      MONTH SUM(AMOUNT)       RANK

\----------- ---------- ----------- ----------

2          3    16826.98          4

1          4    120344.7          1

3          4     23844.7          2

4          4     16144.7          3

2          4     15664.7          4

1          5    97287.36          1

4          5    20087.36          2

3          5    18687.36          3

2          5    18287.36          4

1          6    57387.84          1

4          6    33087.84          2

PRD\_TYPE\_ID      MONTH SUM(AMOUNT)       RANK

\----------- ---------- ----------- ----------

3          6    19887.84          3

2          6    14587.84          4

3          7    81589.04          1

1          7    60929.04          2

2          7    15689.04          3

4          7    12089.04          4

1          8    75608.92          1

3          8    62408.92          2

4          8    58408.92          3

2          8    16308.92          4

1          9    85027.42          1

PRD\_TYPE\_ID      MONTH SUM(AMOUNT)       RANK

\----------- ---------- ----------- ----------

4          9    49327.42          2

3          9    46127.42          3

2          9    19127.42          4

1         10   105305.22          1

4         10    75325.14          2

3         10    70325.29          3

2         10    13525.14          4

1         11    55678.38          1

3         11    46187.38          2

4         11    42178.38          3

2         11    16177.84          4

PRD\_TYPE\_ID      MONTH SUM(AMOUNT)       RANK

\----------- ---------- ----------- ----------

3         12    48209.04          1

1         12    46209.04          2

4         12    30409.05          3

2         12    12509.04          4

已选择48行。

接下来我们再来看分析函数与我们上次学的ROLLUP、CUBE、GROUPING SETS的结合使用：

SELECT  
 prd\_type\_id,SUM(amount),  
 RANK() OVER (ORDER BY SUM(amount) DESC NULLS LAST) AS rank  
FROM all\_sales  
WHERE year=2003  
GROUP BY ROLLUP(prd\_type\_id)  
ORDER BY rank;

PRD\_TYPE\_ID  SUM(AMOUNT)        RANK

1972485.13         1                    （注：RULLUP的总计排在了最前）

1       905081.84  2

3       478270.91  3

4       402751.16  4

2       186381.22  5

5                      6

SELECT  
 prd\_type\_id,emp\_id,SUM(amount),  
 RANK() OVER (ORDER BY SUM(amount) DESC NULLS LAST) AS rank  
FROM all\_sales  
WHERE year=2003  
GROUP BY CUBE(prd\_type\_id,emp\_id)  
ORDER BY prd\_type\_id,emp\_id;

PRD\_TYPE\_ID     EMP\_ID SUM(AMOUNT)       RANK

\----------- ---------- ----------- ----------

1         21   197916.96         12

1         22   214216.96         10

1         23    98896.96         19

1         24   207216.96         11

1         25    93416.96         21

1         26    93417.04         20

1              905081.84          2

2         21    20426.96         33

2         22    19826.96         34

2         23    19726.96         35

2         24    43866.96         27

PRD\_TYPE\_ID     EMP\_ID SUM(AMOUNT)       RANK

\----------- ---------- ----------- ----------

2         25    32266.96         31

2         26    50266.42         24

2              186381.22         14

3         21   140326.96         15

3         22   116826.96         16

3         23   112026.96         17

3         24    34829.96         29

3         25    29129.96         32

3         26    45130.11         26

3              478270.91          3

4         21   108326.96         18

PRD\_TYPE\_ID     EMP\_ID SUM(AMOUNT)       RANK

\----------- ---------- ----------- ----------

4         22    81426.96         23

4         23    92426.96         22

4         24    47456.96         25

4         25    33156.96         30

4         26    39956.36         28

4              402751.16          6

5         21                     36

5         22                     36

5         23                     36

5         24                     36

5         25                     36

PRD\_TYPE\_ID     EMP\_ID SUM(AMOUNT)       RANK

\----------- ---------- ----------- ----------

5         26                     36

5                                36

21   466997.84          4

22   432297.84          5

23   323077.84          8

24   333370.84          7

25   187970.84         13

26   228769.93          9

1972485.13          1

已选择42行。

SQL> SELECT

2   prd\_type\_id,emp\_id,SUM(amount),

3   RANK() OVER (ORDER BY SUM(amount) DESC NULLS LAST) AS rank

4  FROM all\_sales

5  WHERE year=2003

6  GROUP BY GROUPING SETS(prd\_type\_id,emp\_id)

7  ORDER BY prd\_type\_id,emp\_id;

PRD\_TYPE\_ID     EMP\_ID SUM(AMOUNT)       RANK

\----------- ---------- ----------- ----------

1              905081.84          1

2              186381.22         10

3              478270.91          2

4              402751.16          5

5                                11

21   466997.84          3

22   432297.84          4

23   323077.84          7

24   333370.84          6

25   187970.84          9

26   228769.93          8

已选择11行。

2）CUME-DIST（）和PERCENT-RANK（）函数

下面这个例子说明了CUME-DIST（）与PERCENT-RANK（）的使用，它得到的是销量的累积分布和百分比排名：

SQL> SELECT

2   prd\_type\_id,SUM(amount),

3   CUME\_DIST() OVER (ORDER BY SUM(amount) DESC) AS cume\_dist,

4   PERCENT\_RANK() OVER (ORDER BY SUM(amount) DESC) AS percent\_rank

5  FROM all\_sales

6  WHERE year=2003

7  GROUP BY prd\_type\_id

8  ORDER BY prd\_type\_id;

PRD\_TYPE\_ID SUM(AMOUNT)  CUME\_DIST PERCENT\_RANK

\----------- ----------- ---------- ------------

1   905081.84         .4          .25

2   186381.22          1            1

3   478270.91         .6           .5

4   402751.16         .8          .75

5                     .2            0

3）NTILE（）函数的使用

前面我们已经介绍了这个函数的作用就是把记录结果集分成N部分的意思，这个函数的参数为NTILE（buckets），这个bucket参数指定了分片的片数，下面我们看例子来说明

SQL> SELECT

2   prd\_type\_id,SUM(amount),

3   NTILE(2) OVER (ORDER BY SUM(amount) DESC) AS ntile

4  FROM all\_sales

5  WHERE year=2003

6  AND amount IS NOT NULL

7  GROUP BY prd\_type\_id

8  ORDER BY prd\_type\_id;

PRD\_TYPE\_ID SUM(AMOUNT)      NTILE

\----------- ----------- ----------

1   905081.84          1

2   186381.22          2

3   478270.91          1

4   402751.16          2

注意这里的N为2，因此分成了下面的1，2两片

SQL> SELECT

2   prd\_type\_id,SUM(amount),

3   NTILE(3) OVER (ORDER BY SUM(amount) DESC) AS ntile

4  FROM all\_sales

5  WHERE year=2003

6  AND amount IS NOT NULL

7  GROUP BY prd\_type\_id

8  ORDER BY prd\_type\_id;

PRD\_TYPE\_ID SUM(AMOUNT)      NTILE

\----------- ----------- ----------

1   905081.84          1

2   186381.22          3

3   478270.91          1

4   402751.16          2

注意这里的N为3，因此分成了下面的1，2，3三片，这里我的看法是当分片不均时，都是向上最加（即有两个1片）

当N=4时就与RANK相同了

PRD\_TYPE\_ID SUM(AMOUNT)      NTILE

\----------- ----------- ----------

1   905081.84          1

2   186381.22          4

3   478270.91          2

4   402751.16          3\`

4）ROW-NUMBER（）函数

SQL> SELECT

2   prd\_type\_id,SUM(amount),

3   ROW\_NUMBER() OVER (ORDER BY SUM(amount) DESC) AS row\_number

4  FROM all\_sales

5  WHERE year=2003

6  GROUP BY prd\_type\_id

7  ORDER BY prd\_type\_id;

PRD\_TYPE\_ID SUM(AMOUNT) ROW\_NUMBER

\----------- ----------- ----------

1   905081.84          2

2   186381.22          5

3   478270.91          3

4   402751.16          4

5                      1

这里ROW-NUMBER（）函数就相当于RANK（）函数。

总结：在上面介绍的这些评级函数中其中RANK（）、DENSE-RANK（）、PERCENT-RANK（）函数是比较常用的（相对于其他几个而言），因此我们最好要掌握而其他几个大家只要知道了解就可以了。

2.       反百分点函数的使用

PERCENTILE-DISC（X）函数与CUME-DIST相反，它在每一个分组中检查累积分布的数值，直到找到大于或等于X的值。

PERCENTILE-CONT（X）函数与PERCENT-RANK（）相反，在每一个分组中检查百分比排名的值，直到

找到大于或等于X的值。

下面我们来看个例子获取百分点大于等于0.6的销售总量：

SQL> SELECT

2   PERCENTILE\_CONT(0.6) WITHIN GROUP (ORDER BY SUM(amount) DESC) AS percentile\_cont,

3   PERCENTILE\_DISC(0.6) WITHIN GROUP (ORDER BY SUM(amount) DESC) AS percentile\_disc

4  FROM all\_sales

5  WHERE year=2003

6  GROUP BY prd\_type\_id;

PERCENTILE\_CONT PERCENTILE\_DISC

\--------------- ---------------

417855.11       402751.16

**3.       窗口函数**

**窗口函数主要用来计算一定的记录范围内、一定的值域内、或一段时间内的累积和及移动平均值等。之所以叫“窗口”因为处理结果中使用了一个滑动的查询结果集范围。**

1）.计算累积和

下面这个例子是计算出2003年从1月到12月的累积销量。

SQL> SELECT

2   month 月份,SUM(amount) AS 月总销量,

3   SUM(SUM(amount)) OVER

4    (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS 月累积销量  \--定义了窗口的起点和终点

5  FROM all\_sales

6  WHERE year=2003

7  GROUP BY month

8  ORDER BY month;

月份   月总销量 月累积销量

\---------- ---------- ----------

1   95525.55   95525.55

2   116671.6  212197.15

3  160307.92  372505.07

4   175998.8  548503.87

5  154349.44  702853.31

6  124951.36  827804.67

7  170296.16  998100.83

8  212735.68 1210836.51

9  199609.68 1410446.19

10  264480.79 1674926.98

11  160221.98 1835148.96

12  137336.17 1972485.13

已选择12行。

那如果是计算6月到12月的累积销量呢！！！

SQL> SELECT

2   month 月份,SUM(amount) AS 月总销量,

3   SUM(SUM(amount)) OVER

4    (ORDER BY month ROWS UNBOUNDED PRECEDING) AS 月累积销量

5  FROM all\_sales

6  WHERE year=2003

7  AND month BETWEEN 6 AND 12       \--6和12换为相应的月就可以了

8  GROUP BY month

9  ORDER BY month;

月份   月总销量 月累积销量

\---------- ---------- ----------

6  124951.36  124951.36

7  170296.16  295247.52

8  212735.68   507983.2

9  199609.68  707592.88

10  264480.79  972073.67

11  160221.98 1132295.65

12  137336.17 1269631.82

已选择7行。

2）.计算移动平均值

计算本月与前三个月之间销量的移动平均值

SQL> SELECT

2   month 月份,SUM(amount) AS 月总销量,

3   AVG(SUM(amount)) OVER

4    (ORDER BY month ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS 三月平均累积销量

5  FROM all\_sales

6  WHERE year=2003

7  GROUP BY month

8  ORDER BY month;

月份   月总销量      三月平均累积销量

\---------- ----------       ----------------

1   95525.55         95525.55

2   116671.6       106098.575       \--前两月的平均销量

3  160307.92       124168.357      \--三月

4   175998.8       137125.968       \--本月加前三月

5  154349.44        151831.94

6  124951.36        153901.88

7  170296.16        156398.94

8  212735.68        165583.16

9  199609.68        176898.22

10  264480.79       211780.578

11  160221.98       209262.033

12  137336.17       190412.155

已选择12行。

3）.计算中心平均值

计算当前月份前、后各一个月内的销量移动平均值：

SQL> SELECT

2   month 月份,SUM(amount) AS 月总销量,

3   AVG(SUM(amount)) OVER

4    (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS 平均累积销量

5  FROM all\_sales

6  WHERE year=2003

7  GROUP BY month

8  ORDER BY month;

月份   月总销量 平均累积销量

\---------- ---------- ------------

1   95525.55   106098.575

2   116671.6   124168.357

3  160307.92   150992.773

4   175998.8   163552.053

5  154349.44   151766.533

6  124951.36   149865.653

7  170296.16   169327.733

8  212735.68    194213.84

9  199609.68   225608.717

10  264480.79    208104.15

11  160221.98   187346.313

12  137336.17   148779.075

4.FIRST-VALUE（）和LAST-VALUE（）函数的使用：

下面这个例子是用FIRST-VALUE（）和LAST-VALUE（）来获得前一个月和后一个月的销量：

SQL> SELECT

2   month 月份,SUM(amount) AS 月总销量,

3   FIRST\_VALUE(SUM(amount)) OVER

4    (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS 前月销量,

5   LAST\_VALUE(SUM(amount)) OVER

6    (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS 后月销量

7  FROM all\_sales

8  WHERE year=2003

9  GROUP BY month

10  ORDER BY month;

月份   月总销量     前月销量     后月销量

\---------- ----------      ----------      ----------

1   95525.55   95525.55   116671.6

2   116671.6   95525.55  160307.92

3  160307.92   116671.6   175998.8

4   175998.8  160307.92  154349.44

5  154349.44   175998.8  124951.36

6  124951.36  154349.44  170296.16

7  170296.16  124951.36  212735.68

8  212735.68  170296.16  199609.68

9  199609.68  212735.68  264480.79

10  264480.79  199609.68  160221.98

11  160221.98  264480.79  137336.17

12  137336.17  160221.98  137336.17

已选择12行。

还有一个例子是计算当前月与前、后各一个月的比率

SQL> SELECT

2   month 月份,SUM(amount) AS 月总销量,

3   SUM(amount)/FIRST\_VALUE(SUM(amount)) OVER

4    (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS 当月与前月比率,

5   SUM(amount)/LAST\_VALUE(SUM(amount)) OVER

6    (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS 当月与后月比率

7  FROM all\_sales

8  WHERE year=2003

9  GROUP BY month

10  ORDER BY month;

月份   月总销量    当月与前月比率    当月与后月比率

\---------- ----------     --------------          --------------

1   95525.55              1            .818755807

2   116671.6     1.22136538     .727796855

3  160307.92     1.37400978     .910846665

4   175998.8     1.09787963     1.14026199

5  154349.44     .876991434     1.23527619

6  124951.36     .809535558     .733729756

7  170296.16     1.36289961     .800505867

8  212735.68     1.24921008     1.06575833

9  199609.68      .93829902     .754722791

10  264480.79      1.3249898     1.65071478

11  160221.98     .605798175     1.16664081

12  137336.17     .857161858              1

已选择12行。

该文资料来源:Oracle Database 10g SQL开发指南

Oracle分析函数使用总结

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 ORACLE 实用函数总结,Oracle中常用的函数总结

相关 Oracle分析函数

相关 oracle 分析函数第二,Oracle分析函数(二)

相关 oracle分析函数row_number() over()使用

相关 Oracle分析函数使用总结

相关 Oracle分析函数——统计分析函数

相关 [Oracle]分析函数——函数列表

相关 ORACLE统计分析函数

相关 Oracle 统计分析函数使用示例

相关 oracle分析函数——keep