`
雨中伞
  • 浏览: 43899 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

Oracle分析函数使用总结 转

OracleSQL数据结构 
阅读更多

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

分享到：
| ORACLE PL/SQL AND analyse fuction
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    Oracle分析函数使用总结

    Oracle分析函数使用总结Oracle分析函数使用总结Oracle分析函数使用总结Oracle分析函数使用总结

    ORACLE分析函数教程

    ### Oracle分析函数详解 #### 一、Oracle分析函数概述 Oracle分析函数是在处理大量数据时极为有用的一套工具，主要用于在线分析处理（OLAP）场景。这类函数可以在多个级别上进行数据聚合，并支持复杂的排序、分组...

    Oracle中的分析函数详解

    其中，Oracle的分析函数是其强大的特性之一，它允许用户在单个SQL查询中执行复杂的分析操作，而无需使用子查询或者自连接。这篇文档将深入探讨Oracle中的分析函数，帮助你更好地理解和利用这一功能。 一、什么是...

    oracle分析函数文档

    ### Oracle分析函数详解 #### 一、概述 Oracle分析函数是一种强大的工具，它允许用户对分组数据执行复杂的计算，并且结果可以根据特定条件进行动态调整。这种灵活性使得Oracle分析函数在处理复杂的数据集时非常...

    oracle 分析函数总结

    Oracle分析函数包括许多内置函数，如AVG、COUNT、MAX、MIN、SUM等，但作为分析函数使用时，它们会在OVER()子句中指定特定的行为。例如，AVG()分析函数可以计算每个分组或窗口内的平均值，而不只是整个数据集的平均值...

    oracle分析函数大全

    ### Oracle分析函数详解 #### 一、概述 Oracle分析函数是一种强大的工具，它自Oracle 8.1.6版本开始引入，并在后续版本中不断完善和发展。这类函数的主要用途在于能够针对一组数据执行复杂的聚合计算，并且不同于...

    Oracle分析函数使用总结[定义].pdf

    Oracle分析函数是数据库查询中非常强大的工具，它们用于在数据集上执行聚合操作，并返回每个行的上下文信息。在Oracle数据库中，分析函数能够帮助我们处理复杂的分组和排序需求，尤其在报告和数据分析中非常有用。...

    ORACLE 常用分析函数

    分析函数总结;26个分析函数;PLSQL开发笔记和小结;分析函数简述  ROW_NUMBER () OVER([partition_clause] order_by_clause) dense_rank在做排序时如果遇到列有重复值，则重复值所在行的序列值相同，而其后的序列值...

    Oracle分析函数

    ### Oracle分析函数详解 #### 一、概述 Oracle分析函数是一种强大的工具，旨在处理复杂的查询需求，特别是当需要对查询结果进行进一步分析时。通过使用分析函数，可以在单次查询中执行复杂的聚合操作，无需进行...

    oracle分析函数参考手册

    ### Oracle分析函数详解 #### 一、概述 Oracle分析函数是一种强大的工具，它允许用户对分组数据执行复杂的计算，并且能够返回多个结果行。这与传统的聚合函数（如`SUM`、`COUNT`等）形成鲜明对比，后者通常只针对...

    oracle 分析函数详解（有例子）

    6 Oracle开发专题之：分析函数总结 7 Oracle开发专题之：26个分析函数 8 分析函数简述"&gt;1 Oracle开发专题之：分析函数 OVER 2 Oracle开发专题之：分析函数 Rank Dense rank row number 3 Oracle开发专题之：分析...

    oracle分析函数及开窗函数

    ### Oracle分析函数及开窗函数详解 #### 一、Oracle分析函数概述 Oracle自8.1.6版本开始引入了分析函数，这类函数主要用于计算基于组的聚合值，并且与传统的聚合函数不同的是，分析函数可以针对每个组返回多行结果...

    Oracle分析函数使用的总结.doc

    Oracle分析函数是数据库查询中强大的工具，它们允许对数据集进行复杂的聚合操作，提供更深入的洞察力。本文将详细介绍Oracle分析函数中的评级函数，包括RANK()、DENSE_RANK()、CUME_DIST()、PERCENT_RANK()和NTILE()...

    Oracle分析函数基本概念和语法总结及Regexp_***用法

    Oracle分析函数是数据库管理系统Oracle中的一种高级查询工具，它们用于处理行集，计算基于特定窗口内的数据的聚合值。分析函数的主要特点是返回的结果不仅仅是一行，而是多行，这与传统的聚合函数（如SUM, AVG等）...

    oracle常用函数总结

    Oracle数据库是一个广泛使用的关系型数据库管理系统，它提供了丰富的内置函数来帮助用户处理和操作数据。以下是对标题和描述中提到的Oracle常用函数的详细总结： 1. ASCII 函数：这个函数返回一个字符对应的ASCII码...

    Oracle_详解分析函数

    ### Oracle分析函数详解 #### 一、Oracle分析函数概述 Oracle分析函数是在处理大量数据时进行高级数据分析的强大工具，主要用于在线分析处理（OLAP）场景...希望本文能够帮助您更好地掌握Oracle分析函数的使用方法。

Magicbox
Global site tag (gtag.js) - Google Analytics