- 浏览: 93550 次
- 性别:
- 来自: 广州
-
最新评论
-
hywill：
spool e:\test\pmd_droptable.sql ...
powerdesigner 12.5 错误 -
yuanzhifei89：
很赞同，和周围朋友共同语言越来越少中...
写给程序员朋友们---你真的还想做程序员吗 -
cq.ronadlo：
不好用 依然不好用
【转】设定tomcat的默认字符集
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 2004 3 2547.29
30 rows selected.
7、处理空值和缺失值
1）使用IS PRESENT
当数据单元指定的记录在MODEL子句执行之前存在，则IS PRESENT返回TRUE。例如：
Sales_amount[CURRENTV(),2003] IS PRESENT
如果Sales_amount[CURRENTV(),2003]存在，则返回TRUE。
下面的表达式将2004年前三个月的销量设置为2003年同期销量的1.25倍：
Sales_amount[FOR month from 1 TO 3 INCREMENT 1,2004]=
CASE WHEN Sales_amount[CURRENTV(),2003] IS PRESENT THEN
ROUND(sales_amount[CURRENTV(),2003]*1.25,2)
ELSE
0
END
下面这个查询展示了上述表达式的用法：
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]=
CASE WHEN Sales_amount[CURRENTV(),2003] IS PRESENT THEN
ROUND(sales_amount[CURRENTV(),2003]*1.25,2)
ELSE
0
END
)
Order by prd_type_id,year,month;
2）使用PRESENTV()
如果cell引用的记录在MODEL子句执行以前就存在，那么PRESENTV(cell,expr1,expr2)返回表达式expr1。如果这条记录不存在，则返回表达式expr2。例如：
PRESENTV(sales_amount[CURRENTV(),2003],
ROUND(sales_amount[CURRENTV(),2003]*1.25,2),0)
如果sales_amount[CURRENTV(),2003]存在，上面的表达式返回取整后的销量；否则，返回0.下面这个查询展示了上述表达式的用法：
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]=
PRESENTV(sales_amount[CURRENTV(),2003],
ROUND(sales_amount[CURRENTV(),2003]*1.25,2),0)
)
Order by prd_type_id,year,month;
3）使用PRESENTNNV()
如果cell引用的单元在MODEL子句执行之前已经存在，并且该单元的值不为空，则PRESENTNNV(cell,expr1,expr2)返回表达式expr1。如果记录不存在，或单元值为空值，则返回表达式expr2。例如：
PRESENTNNV(sales_amount[CURRENTV(),2003],
ROUND(sales_amount[CURRENTV(),2003]*1.25,2),0)
如果sales_amount[CURRENTV(),2003]存在且为非空值，那么上面的表达式将返回取整后的销量；否则返回0。
4）使用IGNORE NAV和KEEP NAV
IGNORE NAV的返回值如下：
空值或缺失数字值时返回0。
空值或缺失字符串值时返回空字符串。
空值或缺失日期值时返回01-JAN-2000。
其他所有数据库类型时返回空值。
KEEP NAV对空值或缺失数字值返回空值。注意默认条件下使用KEEP NAV。
下面这个查询展示了IGNORE NAV的用法：
select prd_type_id,year,month,sales_amount
from all_sales
where prd_type_id between 1 and 2
and emp_id=21
model IGNORE NAV
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;
8、更新已有的单元
默认情况下，如果表达式左端的引用单元存在，则更新该单元。如果该单元不存在，就在数组中创建一条新的记录。可以用RULES UPDATE改变这种默认的行为，指出在单元不存在的情况下不创建新纪录。
下面这个查询展示了RULES UPDATE的用法：
select prd_type_id,year,month,sales_amount
from all_sales
where prd_type_id between 1 and 2
and emp_id=21
model IGNORE NAV
partition by (prd_type_id)
dimension by (month,year)
measures (amount sales_amount)
RULES UPDATE
(
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
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
2 2003 10 2754.19
PRD_TYPE_ID YEAR MONTH SALES_AMOUNT
----------- ---------- ---------- ------------
2 2003 11 1329.73
2 2003 12 1034.84
24 rows selected.
可以看到，虽然2004年的单元不存在，可是同时指定了RULES UPDATE，所以不会在数组中为2004年创建新纪录，因此这条查询语句不会返回2004年的记录。
发表评论
-
dbms_sql的使用
2010-10-26 10:19 1414dbms_sql的使用 http://space ... -
PLSQL中显示Cursor、隐示Cursor、动态Ref Cursor区别
2010-09-14 16:01 1085PLSQL中显示Cursor、隐示Cursor、动态Ref C ... -
Oracle的model用法简介
2010-09-13 16:55 1406Model语句是Oracle 10g的新功能之一。 ... -
imp/exp impdp/expdp
2010-09-03 13:21 2447EXP常规模式、EXP直接路径模式和EXPDP三种方式导出 ... -
Oracle数据字典概念 收藏
2010-09-02 15:17 1769Oracle数据字典概念 收藏 ... -
[转]OWB
2010-05-31 15:02 1597OWB 前提： 按照先后顺序分别安装oracl ... -
触发器
2010-04-07 16:43 731create or replace trigger TRG_t ... -
[cognos]Cognos入门
2010-03-23 21:13 1418Cognos BI 提供了报表（reporting）， ... -
windows下局域网内的实时数据收发
2009-09-23 23:17 1647最近做了一个 ... -
oracle.jdbc.driver.OracleDriver
2009-09-22 11:49 3503连接Oracle数据库必须先向java程序注册Oracle J ... -
SQL Server数据库备份和恢复措施
2009-09-07 11:05 1411一、备份数据库 1、打开SQL企业管理器，在控制台根目录中 ...
相关推荐
Oracle学习笔记 Oracle学习笔记是李兴华老师编写的Oracle从入门到精通的学习笔记，涵盖了 Oracle 的多表查询、连接、组函数和分组统计等知识点。在本篇笔记中，李兴华老师详细介绍了多表查询的基本语法、左右连接...
以下是对Oracle学习笔记中提到的一些关键知识点的详细解释： 1. **SQL执行顺序**： SQL语句的执行顺序是：`FROM` -> `WHERE` -> `SELECT` -> `GROUP BY` -> `HAVING` -> `ORDER BY`。首先从`FROM`子句开始，确定...
### Oracle超详细学习笔记 #### 一、基本查询与数据操作 ##### 1. 最简单的查询 - **命令示例**: ```sql SELECT * FROM employees; DESC employees; SET LINESIZE 600; SET PAGESIZE 50; ``` - **解释**: ...
以下是对Oracle数据库一些基础知识的详细解释，主要基于提供的个人学习笔记内容。 **第一节：数据库管理** 1. **创建用户**：`CREATE USER`命令用于创建新的数据库用户，如`CREATE USER username IDENTIFIED BY ...
标题中提到的“oracle pl的学习笔记”指的是对Oracle PL/SQL（Procedural Language/SQL）的学习笔记。PL/SQL是Oracle数据库中用于存储过程、函数、触发器和游标编程的一种过程化SQL语言的扩展。学习PL/SQL可以帮助...
在深入探讨《Mastering Oracle SQL学习笔记》的内容之前，我们先理解一下这门课程的核心目标：帮助初学者更好地掌握SQL语言中的关键概念和操作，尤其是针对Oracle数据库的特性。这包括了SQL语句的构建、数据筛选、...
最后，基于函数的索引用于处理在WHERE子句中包含函数或表达式的查询。这种索引允许对计算结果进行索引，例如基于算术表达式、PL/SQL函数等。这可以提高含有复杂计算的查询的性能，但需要注意的是，不是所有函数都能...
### Oracle管理员学习笔记知识点解析 #### 一、Where子句详解 在Oracle数据库管理与查询中，`WHERE`子句是SQL语句中至关重要的部分，用于筛选满足特定条件的数据行。以下是对`WHERE`子句中关键概念的深入解析： #...
以上是Oracle SQL学习笔记中涵盖的部分核心概念，这些知识对于有效管理Oracle数据库、提升查询效率和确保数据一致性至关重要。在实际应用中，还需要结合具体业务需求和数据库设计进行灵活运用。
### Oracle查询操作学习笔记知识点详解 #### 一、Oracle用户管理与权限分配 - **创建用户**：在Oracle中创建用户的基本语法为`CREATE USER username IDENTIFIED BY password`。例如，`CREATE USER xiaoming ...
Oracle 10g SQL 学习笔记涵盖了Oracle数据库的基础知识，包括版本信息、数据库系统特点、关系型数据库结构、SQL语言、操作环境以及常见的数据库操作。以下是对这些知识点的详细说明： 1. **Oracle 9i基础知识**： ...
这些是Oracle SQL学习中的一些基础和进阶知识点，它们对于高效地管理Oracle数据库至关重要。理解并熟练掌握这些概念，可以帮助你编写更高效、更稳定的SQL语句，优化数据库性能，并确保数据的一致性和完整性。
Oracle中的SQL MODEL子句是一种强大的工具，用于在数据库内执行复杂的行间计算，它...通过学习和熟练掌握MODEL子句，用户可以更高效地管理和分析数据，优化业务流程，同时避免了数据导入导出带来的额外开销和潜在错误。
这些只是Oracle 11g R2学习笔记中的部分内容，实际学习和使用中还会涉及更多如索引、触发器、存储过程、事务管理、性能优化等复杂主题。对于深入理解和掌握Oracle数据库，建议进一步阅读官方文档和其他专业资料，...
在学习Oracle数据库及SQL时，需要掌握以下几个关键知识点： 一、数据库介绍 1. 数据库是存储、管理和处理大量数据的系统，表是存储数据的基本单位。 2. 数据库标准语言主要指的是SQL（Structured Query Language）...
在学习Oracle SQL时，我们需要掌握各种关键概念和语法，以便高效地进行数据查询、更新和管理。以下是一些核心知识点： 1. **并行查询**：并行查询是提高大数据处理效率的一种策略。通过`ALTER SESSION ENABLE ...
从给定的文件信息来看，这是一份关于Oracle学习的手册，主要涵盖了Oracle的基础知识，包括OLAP与OLTP的概念，以及对索引的详细解释。以下是对这些知识点的深入探讨： ### ORACLE OLAP与OLTP介绍 #### 1.1.1 什么是...