下面,我们来解析一下分析函数的语法格式(语法格式图请参照: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之间的记录,均参与统计运算。而如果使用rows between 2 preceding and current row,那么在与当前行的行偏移量为2的范围之内的记录参与统计运算。因为排序的非唯一性,行偏移量之内的记录是不确定的,但是某个取值范围(range)之内的记录时确定的。
4:如果窗口子句使用了range子句,那么排序子句,只可以使用一个排序字段(多个字段无法进行取值范围的确定),且排序字段的值必须为date或者数值类型。但是以下情况除外:
– RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. The short form of 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可以查阅(具有*号)。
CURRENT ROW:该窗口从当前行开始(并结束)
Numeric Expression PRECEDING:对该窗口从当前行之前的数字表达式(Numeric Expression)的行开始,对RANGE来说,从从行序值小于数字表达式的当前行的值开始.
Numeric Expression FOLLOWING:该窗口在当前行Numeric Expression行之后的行终止(或开始),且从行序值大于当前行Numeric Expression行的范围开始(或终止)
range between 100 preceding and 100 following:当前行100前,当前后100后