ORACLE分析函数(2)

最新推荐文章于 2024-07-23 17:52:34 发布

iteye_1485

最新推荐文章于 2024-07-23 17:52:34 发布

阅读量84

点赞数

文章标签：数据库

下面，我们来解析一下分析函数的语法格式（语法格式图请参照：http://blog.csdn.net/yidian815/article/details/12709223）。

函数名称

对it人士来时，再简单不过的东西了，不做描述。

函数参数：

分析函数通常会具有0-3个参数。

分区子句：

通过分区子句，可以对记录集进行分区，然后针对每个分区分别进行统计运算。在分析函数中，使用分区子句不必使用（）将子句包围起来。在一个查询当中，我们可以使用多个分析函数，每个分析函数可以使用独立的分区规则。如果没有指定分区子句，则默认将整个查询结果作为一个分区。

示例：

没有指定分区子句：

SQL> select rownum,prod_subcategory_id,sum(1) over( order by prod_subcategory_id  rows between unbounded preceding and current row) as fx from products where prod_subcategory_id > 2053; 

    ROWNUM PROD_SUBCATEGORY_ID         FX
---------- ------------------- ----------
         2                2054          1
         1                2054          2
         3                2054          3
         4                2054          4
         5                2054          5
         6                2054          6
         8                2055          7
         7                2055          8
         9                2055          9
        13                2055         10
        12                2055         11
        11                2055         12
        10                2055         13
        17                2056         14
        16                2056         15
        15                2056         16
        14                2056         17
        18                2056         18

指定分区子句：

SQL> select rownum,prod_subcategory_id,sum(1) over(partition by prod_subcategory_id order by prod_subcategory_id rows between unbounded preceding and current row) as fx from products where prod_subcategory_id > 2053; 

    ROWNUM PROD_SUBCATEGORY_ID         FX
---------- ------------------- ----------
         2                2054          1
         1                2054          2
         3                2054          3
         4                2054          4
         5                2054          5
         6                2054          6
         8                2055          1
         7                2055          2
         9                2055          3
        13                2055          4
        12                2055          5
        11                2055          6
        10                2055          7
        17                2056          1
        16                2056          2
        15                2056          3
        14                2056          4
        18                2056          5

排序子句：

排序子句用来对分区中的数据集进行排序。在排序子句部分，我们可以指定多个字段，如果排序字段不能唯一标识一条记录，那么排序的结果可能是非唯一的，因此对于分析函数产生的结果我们需要特别注意。

1：对于分区函数CUME_DIST DENSE_RANK NTILE PERCENT_RANK RANK,每一条具有相同标识的记录，其返回结果相同。

2：row_number对于每条记录返回唯一值，但是如果order by子句不能唯一标识一条记录，ROW_NUMBER处理的结果可能是不确定的，因为其依赖于排序结果。

3：其余分区函数的窗口子句，如果使用range子句，即逻辑范围，那么其计算结果是确定的；如果使用rows子句，及物理范围，那么计算结果有可能是不确定的，每条记录的统计结果都是不同的。例如，当前记录行的值为100，如果使用range between 2 preceding and current row，那么凡是行值在100-2和100之间的记录，均参与统计运算。而如果使用rowsbetween 2 preceding and current row,那么在与当前行的行偏移量为2的范围之内的记录参与统计运算。因为排序的非唯一性，行偏移量之内的记录是不确定的，但是某个取值范围（range）之内的记录时确定的。

4：如果窗口子句使用了range子句，那么排序子句，只可以使用一个排序字段（多个字段无法进行取值范围的确定），且排序字段的值必须为date或者数值类型。但是以下情况除外：

– RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. The short formof this is RANGE UNBOUNDED PRECEDING.
– RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
– RANGE BETWEEN CURRENT ROW AND CURRENT ROW
– RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

注意：排序子句不可以使用slibing关键字，也不可以使用别名和位置来指定需要排序的列。

示例：

SQL> select p.prod_subcategory_id,rank() over(order by prod_subcategory_id) from products p where p.prod_subcategory_id > 2053;

PROD_SUBCATEGORY_ID RANK()OVER(ORDERBYPROD_SUBCATEGORY_ID)
------------------- --------------------------------------
	       2054					 1
	       2054					 1
	       2054					 1
	       2054					 1
	       2054					 1
	       2054					 1
	       2055					 7
	       2055					 7
	       2055					 7
	       2055					 7
	       2055					 7

PROD_SUBCATEGORY_ID RANK()OVER(ORDERBYPROD_SUBCATEGORY_ID)
------------------- --------------------------------------
	       2055					 7
	       2055					 7
	       2056					14
	       2056					14
	       2056					14
	       2056					14
	       2056					14

order by 子句确定的每条具有相同值的记录，其分析函数结果是一样的。

SQL> select p.prod_subcategory_id,row_number() over(order by prod_subcategory_id) from products p where p.prod_subcategory_id > 2053;

PROD_SUBCATEGORY_ID ROW_NUMBER()OVER(ORDERBYPROD_SUBCATEGORY_ID)
------------------- --------------------------------------------
	       2054					       1
	       2054					       2
	       2054					       3
	       2054					       4
	       2054					       5
	       2054					       6
	       2055					       7
	       2055					       8
	       2055					       9
	       2055					      10
	       2055					      11

PROD_SUBCATEGORY_ID ROW_NUMBER()OVER(ORDERBYPROD_SUBCATEGORY_ID)
------------------- --------------------------------------------
	       2055					      12
	       2055					      13
	       2056					      14
	       2056					      15
	       2056					      16
	       2056					      17
	       2056					      18

row_number针对每条记录返回不同的值

SQL> select p.prod_subcategory_id,prod_list_price,sum(prod_list_price) over(order by prod_subcategory_id rows between unbounded preceding and current row) fx from products p where p.prod_subcategory_id > 2053;

PROD_SUBCATEGORY_ID PROD_LIST_PRICE	    FX
------------------- --------------- ----------
	       2054	      44.99	 44.99
	       2054	      44.99	 89.98
	       2054	      44.99	134.97
	       2054	      44.99	179.96
	       2054	      44.99	224.95
	       2054	      44.99	269.94
	       2055	      18.99	288.93
	       2055	      22.99	311.92
	       2055	       8.99	320.91
	       2055	       6.99	 327.9
	       2055	       7.99	335.89

PROD_SUBCATEGORY_ID PROD_LIST_PRICE	    FX
------------------- --------------- ----------
	       2055	       8.99	344.88
	       2055	      11.99	356.87
	       2056	      49.99	406.86
	       2056	      18.99	425.85
	       2056	      10.99	436.84
	       2056	       6.99	443.83
	       2056	      18.99	462.82

其他的分析函数，如sum，针对每条记录进行一次统计，由于具有重复数据，例如prod_subcategory_id,因此每次排序的结果可能是不一样的，从而造成分析统计的结果不同。

SQL> select p.prod_subcategory_id,prod_list_price,sum(prod_list_price) over(order by prod_subcategory_id range between unbounded preceding and current row) fx from products p where p.prod_subcategory_id > 2053;

PROD_SUBCATEGORY_ID PROD_LIST_PRICE	    FX
------------------- --------------- ----------
	       2054	      44.99	269.94
	       2054	      44.99	269.94
	       2054	      44.99	269.94
	       2054	      44.99	269.94
	       2054	      44.99	269.94
	       2054	      44.99	269.94
	       2055	      18.99	356.87
	       2055	      22.99	356.87
	       2055	       8.99	356.87
	       2055	       6.99	356.87
	       2055	       7.99	356.87

PROD_SUBCATEGORY_ID PROD_LIST_PRICE	    FX
------------------- --------------- ----------
	       2055	       8.99	356.87
	       2055	      11.99	356.87
	       2056	      49.99	462.82
	       2056	      18.99	462.82
	       2056	      10.99	462.82
	       2056	       6.99	462.82
	       2056	      18.99	462.82

此时，sum函数，以range范围来计算，及所有具有相同prod_subcategory_id值的记录为一个范围，每次排序操作不同的PRO_SUBCATEGORY_ID其排序位置是固定的，因此每次计算结果是固定的。在range范围时，以每个范围为单位，及所有具有相同prod_subcategory_id的记录，其统计结果是相同的。

SQL> select p.prod_subcategory_id,prod_list_price,sum(prod_list_price) over(order by prod_subcategory_id,prod_list_price range between 2 preceding and current row) fx from products p where p.prod_subcategory_id > 2053;
select p.prod_subcategory_id,prod_list_price,sum(prod_list_price) over(order by prod_subcategory_id,prod_list_price range between 2 preceding and current row) fx from products p where p.prod_subcategory_id > 2053
                                             *
第 1 行出现错误:
ORA-30486: 在窗口说明中无效的窗口合计组

对于range窗口范围，排序子句不可有多个字段。

窗口子句：
通过前面的描述，相信大家对窗口子句的作用和语法已有所了解。

另外，并不是所有分析函数都可以使用窗口子句，可以只有窗口子句的分析函数，在http://blog.csdn.net/yidian815/article/details/12709223可以查阅（具有*号）。

UNBOUNDED PRECEDING:这个窗口从当前分区的每一行开始,并结束于正在处理的当前行
CURRENT ROW:该窗口从当前行开始(并结束)
Numeric Expression PRECEDING:对该窗口从当前行之前的数字表达式(Numeric Expression)的行开始,对RANGE来说,从从行序值小于数字表达式的当前行的值开始.
Numeric Expression FOLLOWING:该窗口在当前行Numeric Expression行之后的行终止(或开始),且从行序值大于当前行Numeric Expression行的范围开始(或终止)
range between 100 preceding and 100 following:当前行100前,当前后100后

iteye_1485

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
ORACLE分析函数(2)

下面，我们来解析一下分析函数的语法格式（语法格式图请参照：http://blog.csdn.net/yidian815/article/details/12709223）。函数名称对it人士来时，再简单不过的东西了，不做描述。函数参数：分析函数通常会具有0-3个参数。分区子句：通过分区子句，可以对记录集进行分区，然后针对每个分区分别进行统计运...
复制链接

扫一扫