oracle mode子句

发布时间:2014-10-22 14:12:01编辑:www.fx114.net 分享查询网我要评论
本篇文章主要介绍了"oracle mode子句",主要涉及到oracle mode子句方面的内容,对于oracle mode子句感兴趣的同学可以参考一下。

ORACLE 10G中新增的MODEL子句可以用来进行行间计算。MODEL子句允许像访问数组中元素那样访问记录中的某个列。这就提供了诸如电子表格计算之类的计算能力。   1、MODEL子句示例 下面这个查询获取2003年内由员工#21完成的产品类型为#1和#2的销量,并根据2003年的销售数据预测出2004年1月、2月、3月的销量。   select prd_type_id,year,month,sales_amount from all_sales where prd_type_id between 1 and 2 and emp_id=21 model partition by (prd_type_id) dimension by (month,year) measures (amount sales_amount) ( Sales_amount[1,2004]=sales_amount[1,2003], Sales_amount[2,2004]=sales_amount[2,2003] + sales_amount[3,2003], Sales_amount[3,2004]=ROUND(sales_amount[3,2003]*1.25,2) ) Order by prd_type_id,year,month;   现在小分析一下上面这个查询: partition by (prd_type_id)指定结果是根据prd_type_id分区的。 dimension by (month,year)定义数组的维度是month和year。这就意味着必须提供月份和年份才能访问数组中的单元。 measures (amount sales_amount)表明数组中的每个单元包含一个数量,同时表明数组名为sales_amount。 MEASURES之后的三行命令分别预测2004年1月、2月、3月的销量。 Order by prd_type_id,year,month仅仅是设置整个查询返回结果的顺序。 上面这个查询的输出结果如下: PRD_TYPE_ID       YEAR      MONTH SALES_AMOUNT ----------- ---------- ---------- ------------           1       2003          1     10034.84           1       2003          2     15144.65           1       2003          3     20137.83           1       2003          4     25057.45           1       2003          5     17214.56           1       2003          6     15564.64           1       2003          7     12654.84           1       2003          8     17434.82           1       2003          9     19854.57           1       2003         10     21754.19           1       2003         11     13029.73   PRD_TYPE_ID       YEAR      MONTH SALES_AMOUNT ----------- ---------- ---------- ------------           1       2003         12     10034.84           1       2004          1     10034.84           1       2004          2     35282.48           1       2004          3     25172.29           2       2003          1      1034.84           2       2003          2      1544.65           2       2003          3      2037.83           2       2003          4      2557.45           2       2003          5      1714.56           2       2003          6      1564.64           2       2003          7      1264.84   PRD_TYPE_ID       YEAR      MONTH SALES_AMOUNT ----------- ---------- ---------- ------------           2       2003          8      1734.82           2       2003          9      1854.57           2       2003         10      2754.19           2       2003         11      1329.73           2       2003         12      1034.84           2       2004          1      1034.84           2       2004          2      3582.48           2       2004          3      2547.29   30 rows selected.   2、用位置标记和符号标记访问数据单元 前面的例子已经介绍了如何使用位置标记来访问数组中的某个单元。还可以使用符号标记显式指定维度的含义。例如,sales_amount[month=1,year=2004]。下面这个查询用符号标记重写了前面的查询。   select prd_type_id,year,month,sales_amount from all_sales where prd_type_id between 1 and 2 and emp_id=21 model partition by (prd_type_id) dimension by (month,year) measures (amount sales_amount) ( Sales_amount[month=1,year=2004]=sales_amount[month=1, year=2003], Sales_amount[month=2, year=2004]=sales_amount[month=2, year=2003] + sales_amount[month=3, year=2003], Sales_amount[month=3, year=2004]=ROUND(sales_amount[month=3, year=2003]*1.25,2) ) Order by prd_type_id,year,month;   使用位置标记或符号标记之间有一个区别需要了解,即它们处理维度中空值的方式不同。例如,sales_amount[null,2003]返回月份为空值、年份为2003的销量,而sales_amount[month=null,year=2004]则不会访问任何有效的数据单元,因为null=null的返回值总是false。   3、用BETWEEN和AND返回特定范围内的数据单元 BETWEEN和AND关键字可用于访问一段范围内的数据单元。例如,下面这个表达式将2004年1月的销量设置为2003年1月至3月销量的平均值取整: Sales_amount[1,2004]=ROUND(AVG(sales_amount)[month between 1 and 3,2003],2) 下面这个查询展示了上述表达式的用法: select prd_type_id,year,month,sales_amount from all_sales where prd_type_id between 1 and 2 and emp_id=21 model partition by (prd_type_id) dimension by (month,year) measures (amount sales_amount) ( Sales_amount[1,2004]=ROUND(AVG(sales_amount)[month between 1 and 3,2003],2) ) Order by prd_type_id,year,month; 结果如下: PRD_TYPE_ID       YEAR      MONTH SALES_AMOUNT ----------- ---------- ---------- ------------           1       2003          1     10034.84           1       2003          2     15144.65           1       2003          3     20137.83           1       2003          4     25057.45           1       2003          5     17214.56           1       2003          6     15564.64           1       2003          7     12654.84           1       2003          8     17434.82           1       2003          9     19854.57           1       2003         10     21754.19           1       2003         11     13029.73   PRD_TYPE_ID       YEAR      MONTH SALES_AMOUNT ----------- ---------- ---------- ------------           1       2003         12     10034.84           1       2004          1     15105.77           2       2003          1      1034.84           2       2003          2      1544.65           2       2003          3      2037.83           2       2003          4      2557.45           2       2003          5      1714.56           2       2003          6      1564.64           2       2003          7      1264.84           2       2003          8      1734.82           2       2003          9      1854.57   PRD_TYPE_ID       YEAR      MONTH SALES_AMOUNT ----------- ---------- ---------- ------------           2       2003         10      2754.19           2       2003         11      1329.73           2       2003         12      1034.84           2       2004          1      1539.11   26 rows selected.   4、用ANY和IS ANY访问所有的数据单元 可以用ANY和IS ANY谓词访问数组中所有的数据单元。ANY和位置标记合用,IS ANY和符号标记合用。例如,下面这个表达式将2004年1月的销量设置为所有年份月份的销量之和取整: Sales_amount[1,2004]=ROUND(SUM(sales_amount)[ANY,year IS ANY],2) 下面这个查询展示了上述表达式的用法: select prd_type_id,year,month,sales_amount from all_sales where prd_type_id between 1 and 2 and emp_id=21 model partition by (prd_type_id) dimension by (month,year) measures (amount sales_amount) ( Sales_amount[1,2004]=ROUND(SUM(sales_amount)[ANY,year IS ANY],2) ) Order by prd_type_id,year,month;   结果如下: PRD_TYPE_ID       YEAR      MONTH SALES_AMOUNT ----------- ---------- ---------- ------------           1       2003          1     10034.84           1       2003          2     15144.65           1       2003          3     20137.83           1       2003          4     25057.45           1       2003          5     17214.56           1       2003          6     15564.64           1       2003          7     12654.84           1       2003          8     17434.82           1       2003          9     19854.57           1       2003         10     21754.19           1       2003         11     13029.73   PRD_TYPE_ID       YEAR      MONTH SALES_AMOUNT ----------- ---------- ---------- ------------           1       2003         12     10034.84           1       2004          1    197916.96           2       2003          1      1034.84           2       2003          2      1544.65           2       2003          3      2037.83           2       2003          4      2557.45           2       2003          5      1714.56           2       2003          6      1564.64           2       2003          7      1264.84           2       2003          8      1734.82           2       2003          9      1854.57   PRD_TYPE_ID       YEAR      MONTH SALES_AMOUNT ----------- ---------- ---------- ------------           2       2003         10      2754.19           2       2003         11      1329.73           2       2003         12      1034.84           2       2004          1     20426.96   26 rows selected.   5、用CURRENTV()获取某个维度的当前值 CURRENTV()函数用于获得某个维度的当前值。例如,下面的表达式将2004年第一个月的销量设置为2003年同月销量的1.25倍。注意此处用CURRENTV()获得当前月份,其值为1 Sales_amount[1,2004]=ROUND(sales_amount[CURRENTV(),2003]*1.25,2) 下面这个查询展示了上述表达式的用法: select prd_type_id,year,month,sales_amount from all_sales where prd_type_id between 1 and 2 and emp_id=21 model partition by (prd_type_id) dimension by (month,year) measures (amount sales_amount) ( Sales_amount[1,2004]=ROUND(sales_amount[CURRENTV(),2003]*1.25,2) ) Order by prd_type_id,year,month; 运行结果如下: PRD_TYPE_ID       YEAR      MONTH SALES_AMOUNT ----------- ---------- ---------- ------------           1       2003          1     10034.84           1       2003          2     15144.65           1       2003          3     20137.83           1       2003          4     25057.45           1       2003          5     17214.56           1       2003          6     15564.64           1       2003          7     12654.84           1       2003          8     17434.82           1       2003          9     19854.57           1       2003         10     21754.19           1       2003         11     13029.73   PRD_TYPE_ID       YEAR      MONTH SALES_AMOUNT ----------- ---------- ---------- ------------           1       2003         12     10034.84           1       2004          1     12543.55           2       2003          1      1034.84           2       2003          2      1544.65           2       2003          3      2037.83           2       2003          4      2557.45           2       2003          5      1714.56           2       2003          6      1564.64           2       2003          7      1264.84           2       2003          8      1734.82           2       2003          9      1854.57   PRD_TYPE_ID       YEAR      MONTH SALES_AMOUNT ----------- ---------- ---------- ------------           2       2003         10      2754.19           2       2003         11      1329.73           2       2003         12      1034.84           2       2004          1      1293.55   26 rows selected.   6、用FOR循环访问数据单元 可以通过FOR循环访问数据单元。例如,下面这个表达式将2004年前三个月的销量设置为2003年相应月份销量的1.25倍。注意其中使用了FOR循环,还通过INCREMENT关键字定义每一次循环迭代中month的增量: Sales_amount[FOR month from 1 TO 3 INCREMENT 1,2004]= ROUND(sales_amount[CURRENTV(),2003]*1.25,2) 下面这个和查询语句展示了上述表达式的用法: select prd_type_id,year,month,sales_amount from all_sales where prd_type_id between 1 and 2 and emp_id=21 model partition by (prd_type_id) dimension by (month,year) measures (amount sales_amount) ( Sales_amount[FOR month from 1 TO 3 INCREMENT 1,2004]= ROUND(sales_amount[CURRENTV(),2003]*1.25,2) ) Order by prd_type_id,year,month; 运行结果如下: PRD_TYPE_ID       YEAR      MONTH SALES_AMOUNT ----------- ---------- ---------- ------------           1       2003          1     10034.84           1       2003          2     15144.65           1       2003          3     20137.83           1       2003          4     25057.45           1       2003          5     17214.56           1       2003          6     15564.64           1       2003          7     12654.84           1       2003          8     17434.82           1       2003          9     19854.57           1       2003         10     21754.19           1       2003         11     13029.73   PRD_TYPE_ID       YEAR      MONTH SALES_AMOUNT ----------- ---------- ---------- ------------           1       2003         12     10034.84           1       2004          1     12543.55           1       2004          2     18930.81           1       2004          3     25172.29           2       2003          1      1034.84           2       2003          2      1544.65           2       2003          3      2037.83           2       2003          4      2557.45           2       2003          5      1714.56           2       2003          6      1564.64           2       2003          7      1264.84   PRD_TYPE_ID       YEAR      MONTH SALES_AMOUNT ----------- ---------- ---------- ------------           2       2003          8      1734.82           2       2003          9      1854.57           2       2003         10      2754.19           2       2003         11      1329.73           2       2003         12      1034.84           2       2004          1      1293.55           2       2004          2      1930.81           2