Oracle分析函数施用总结 转

Oracle分析函数使用总结 转

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   22876