转自：http://www.itpub.net/viewthread.php?tid=1308393&extra=page%3D1%26amp%3Bfilter%3Ddigest

总结笔记之【开发篇sql】

一些自己之前的总结，希望大家指出问题，错误之处，遗漏之处，可深入或者扩展的地方，希望大家支持！！

把文档传上来方便下载，太长了估计没几个人仔细看。。。不过记得，下载的兄弟要顶帖啊，呵呵！

这个视图列出了所有数据库中的所有锁（locks）和闩（latches）和所有在一个lock或者latch上的请求。简述如下：

处于lock状态的对象的地址

lock的地址

用户或者系统锁的类型，用户锁例如：TM-DML入队，

TX-事务入队，UL-用户提供的（User supplied）

系统类型的锁定诸如：SMON指派恢复进程，临时段入队，

分配新的块入队，redo线程全局入队，

写redo log的入队（media recovery）等等

例如：

与dba_lock中的lock_type对应，可以得到：

再来看看一般开发中涉及到比较重要的两种锁定类型：DML锁定和DDL锁定

DML锁定

DML锁定简单讲，用于保证一行在一段时间只有一个用户进行修改，并且其他人不能够删除这个表或者修改这个表的结构。Dml锁定指定了数据行的锁定，或者数据表的锁定，也即行锁或者表锁。

TX（事务）锁定

从一个事务开始，一直到该事务commit或者rollback，该事务拥有一个TX锁定，它是一个排队机制，使得其他会话等待这个事务的完成。简单的看一个TX例子：

注意到上面的v$lock中id1,id2(或者dba_lock中的lock_id1,lock_id2）字段，

对于tm锁，id1就是object_id，id2为0，对于tx锁id1是以十进制数值表示事务占用的回滚段号和事务solt number,具体算法就是把id1除以2的16次方，余数就是solt number，结果的取整数值是回滚段号（参见asktom），那么改进一下上述查询：

SQL> select /*+ rule*/

2 a.TYPE,

3 c.lock_type lt,

4 trunc(a.ID1/power(2,16)) rollback_seq,

再看看事务和锁定对象的视图，通过锁定对象的地址和session id进行关联，再改进一下上述查询：

上述（ROLLBACK_SEQ，SLOT，SEQ）分别对应事务的（XIDUSN，XIDSLOT，XIDSQN）这就是事务id，lmode便是锁定的模式，见稍后的详述。

那么如果在一个另外的session中再执行对scott.dept中的相同的行进行修改，这样将会看到tx锁是如何工作的。按照此要求改动后的查询（本例中第一个事务和第二个事务id分别为10，12）：

对比一下session 10和session 12 的属性值：

10的block=1代表了阻塞了其他，request是请求的锁的类型，其值代表的意义与LMODE一样，为0说明没有请求，Lmode则是拥有6的锁定，事务id为（3 ，39，2527），请求或者锁定的对象id为30137，locked_mode说明事务请求或者锁定已经完成的锁定类型，为3，即是完成了对行的修改。（具体的LOMODE见后续详细说明）。

而12的block=0，说明了当前没有阻塞其他，request=6说明请求了一个6的锁定，当前的lmode为0，还没有获得锁定，事务并没有开始，故而还没有事务id。

接下来让10提交，看看又会发生什么：

查看session 12，发现已经完成了update语句：

再看看前面的查询语句：

可以很清楚的看到变化，session 12不在被block，请求也完成了，获得了6的锁定，事务id已经生成，而session 10的事务已经完成，已经释放掉了锁定和资源，v$transaction和v$lock中已经没有相应记录。

TM（dml入队）锁定

Tm锁用来保证修改表数据的时候，表结构不被修改。例如：

在一个session中(10)更新表scott.dept，而在另外一个session中（12）中修改表结构，则会出现下面的情况：

仍然继续修改在讲述TX锁时使用的查询：

注意到tm锁的id1就是tx锁的对象id。

DDL锁定

主要有三种：

独占的ddl锁定

共享ddl锁定

像前面示例中的：

就是独占的ddl锁。

DBA_DDL_LOCKS视图

锁定类型，ddl的锁定类型：

Mode_held和Mode_request分别是Lock mode和Lock request type，包括：

通常在重新编译包或者过程的时候，实际已经有用户在运行，则编译会被挂起，或者在grant某个正在执行的过程时，也会发生这样的情况，查询DBA_DDL_LOCKS视图可以找到问题的具体所在。

具体将专门在附录中分析锁定相关的问题集。

LMODE（锁定模式）和REQUEST（请求的锁定模式）

会话拥有的锁的模式：

1：null 空

2：row-s 行共享(RS):共享表锁，用于行的查询

3：row-x 行专用（RX）：用于行的修改，通常表明持有该锁的事务已经完成了对行的修改

4：share 共享锁（S）：阻止其他dml操作

5：s/row-x 共享行专用（SRX）：阻止其他事务操作

6：exclusive 专用（X）：独立访问使用，是表锁的最具限制性的形式。

当前的锁是否阻塞了其他的锁。

锁定相关的几个参数：

这个参数用来规定TM锁的总数，要获得表锁需要TM锁。

这个参数指定行锁的方式，ROW_LOCKING该参数指定行封锁方式。若设置为ALWAYS，则在修改表时只实施行封锁。若设置为INTENT时，则行封锁只适用于SELECT FOR UPDATE，而在修改时实施表封锁。

ADDR Address of the transaction state object 事务对象地址

XIDUSN Undo segment number 回滚段编号

XIDSLOT Slot number 槽（slot）编号

XIDSQN Sequence number 序列编号

UBAFIL Undo block address (UBA) filenum 回滚块地址在的文件序号

UBABLK UBA block number uba块数量

UBASQN UBA sequence number uba序列编号

UBAREC UBA record number uba记录数

STATUS Status 事务状态

SES_ADDR User session object address 用户会话对象地址

SPACE YES if a space transaction 是否是空间事务

RECURSIVE YES if a recursive transaction 是否是递归事务

NOUNDO YES if a no undo transaction 是否是非撤销事务

PTX YES if parallel transaction 是否是并行事务

NAME Name of a named transaction 是否命名事务

USED_UBLK Number of undo blocks used 使用的撤销块数

USED_UREC Number of undo records used 书用的撤销记录数

LOG_IO Logical I/O 逻辑io

PHY_IO Physical I/O 物理io

CR_GET Consistent gets 一致性读

CR_CHANGE Consistent changes 一致性更改

2，undo和redo

Oracle将所有的数据更改记录在undo，这些记录信息可以使oracle使用rollback来撤销更改操作。Undo的机制主要有两个目的：一是用来允许读不能阻塞写入，写入不能阻塞读，二是可以使事务能够rollback（回滚）。

事务能够产生undo（回滚），且产生的undo记录数量可以在动态性能视图V$transaction中得到。用一个例子来看看undo的产生：

上面的操作产生了1条undo记录，再看看一次插入多条记录的情况：

上面的语句也只产生了一条undo。

然后看看update的情形：

这个update语句也产生了1个undo记录，再看看delete的情形：

可以看出，delete的每一条记录都产生了一个undo记录。

由此可以大概对比一下不同的语句产生的undo大小。

一般来说，insert产生了最少的undo，因为insert行的反操作是delete行，只记录插入记录的rowid，delete因为需要把整行的前映像记录到undo，所以产生最多的undo，update需要记录行中被更新的字段部分的前映像，因此一般来说介于insert和delete之间。

回滚段可以说是用来保持数据变化前映像而提供一致性读和保证事务完整性的一段存储区域。当一个事务开始的时候，首先把变化前的数据和变化后的数据先写入日志缓冲区，然后吧变化前的数据写入回滚段，最后才在数据缓冲区中修改。

Undo segment头部包含记录了当前事务使用的undo segment信息的一张表。一系列事务只使用一个undo segment存储所有数据。许多并发的事务可以使用同一个undo segment。

再来看看与undo相关的ora-01555：

在相对稳定的数据库中，出现这个错误大多数时候是代码需要调整，或者由于业务需要在某一时段进行大批量的业务处理，而对一些较大的查询产生了影响。涉及到的undo的优化或者sql的优化在性能调整一章中详述。

Oracle的segment信息可以在dba_segments或者sys_dba_segs中查看，从下面的输出信息中可以看到几个比较重要的段对象，如table，index，rollback。

与undo不同，undo是产生使操作撤销的信息，而redo是产生使操作重做的信息。Oracle的redo有两种：online redo log(联机),archivelog(归档)。

Lgwr循环的写入联机日志，当前日志日满后，lgwr写下一个日志。Lgwr总是在一下条件之一满足之时将缓冲重做日志写入磁盘：

1，每三秒；2，每当满1/3或1MB；3碰到任何事务commit；

在事务commit或者rollback之前往往已经完成了一下工作：

已经在sga中产生了回滚段记录，用于撤销

已经在sga中产生了修改数据块

已经在sga中产生了回滚和修改的重做，是的以上2个操作可以重做

根据lgwr的刷新特点，一部分数据可能已经刷新到了磁盘

已经获得了所有的锁定

当commit时，要做的工作如下：

为事务产生scn，lgwr写磁盘，释放锁定，访问修改事务块，并清除之。

当rollback时，要做的工作如下：

使用回滚段的数据来撤销修改，释放锁定。

同样的，dml对redo产生的影响也是能够测定的。

下列语句依次执行：

得到的redo size数值如下表：

操作 Sid Redo size

初始 144 0

Insert 一行 144 548

Update 一行 144 1140

Delete 一行 144 1732

同样可以测试一次插入多行，更新多行和删除多行对redo的产生量，并且可以测试逐条提交和一次提交对redo的产生量。如果不结合起来看undo和redo的影响而来调整应用，很多时候结果往往是不确定的。值得注意的是触发器的不同类型对redo的产生也有影响，比如对update使用after触发器，将不会影响重做。开发过程中，可以大致了解一下重做的数量：估计事务的大小，需要修改的数据量，提交的频率，dml类型占比（考虑到update产生大约2倍的重做，insert和delete大约为1倍）。而以上的表中似乎看不到不同dml对redo的区别，这是因为b的字段类型为varchar2(20),本身的修改量很小，如果吧字段修改为char(2000),则数字规律很明显，有关字符类型的细节在数据库设计一章节中详述。

另外redo是备份和恢复的一个支撑原理。有关联机日志和归档日志的管理和使用在备份恢复一章中详解。

3，DDL和DML

Oracle的DDL主要有create，alter，drop，truncate等，DML主要有insert，update，select，delete，merge等。Oracle的DDL一���特点是自动提交，这与其他的一些数据库DDL不同。

对于oracle，ddl执行过程可以用一段伪码示意如下：

也即在执行ddl之前有一个提交动作，执行完成之后也有一个提交动作，而执行失败时，第一个提交动作已经完成，所以会提交当前事务。用一个例子来看：

ORA-00955: 名称已由现有对象使用

对于dml来说也可以设置为AUTOCOMMIT，比如sqlplus中的set autocommit：

或者java的jdbc连接中的SetAutoCommit(boolean isCommit)：

使用java.sql.Connection或者oracle.jdbc.OracleConnection建立的一个新的连接默认为auto-commit模式，代码如下：

如果对DDL进行trace，会发现ddl其实就是一系列的dml，这些dml操作了数据字典表。例子如下：

使用tkprof格式化trace文件，可以看到：

看一个具体的DDL create table的完整语句：

注意到以上代码中的一些参数，这些被称作common sql ddl clauses,分别包括了：

用来分配对象的extent，可以指定size子句，指定数据文件（datafile），指定确切的instance

用来定义完整性约束，比如not null ,unique,primary key,foreign key,check等，通常可以通过inline或者out-of-line 两种方式来定义约束（not null除外，只能使用inline），比如：

或者：

可以显示的释放在一个数据库段对象结束时未使用的空间，使得空间可以被其他段对象使用。要注意的是不能同时在一个语句中设置deallocate_unused_clause和allocate_extent_clause。

在创建数据库对象时，这个参数设置为logging或者nologging，用来控制是否记录redo log，要注意的是并不能完全nologging，并且在不同的数据库模式下，影响也不同（archived和unarchived）。DDL中允许nologging模式的语句主要有下列：

可以用来设置table,cluster,index或者materialized的pctfree,pctused,和initans参数。这些参数的原理和作用详见oracle管理基础一章。

Oracle应该如何储存数据库对象的设置。主要的参数如下：

INITIAL 对象的第一个extent的大小

NEXT 下一扩展的extent大小

MINEXTENTS 最小的extents数量

MAXEXTENTS 最大的extents数量

PCTINCREASE 指定extent较前一个的增长的百分比。

需要注意的是，pctincreate值大于0会造成extent不一致，如果next较多的话，可能会造成空间利用率低下，虽然smon会自动合并表空间中的碎片，但是这些空间也不能很好的利用。（oracle建议设置为0，可以减少碎片和避免产生非常大的临时段，而回滚段不能设置pctincrease，默认是0）

FREELISTS 一个空闲链表组内的空闲链表数

FREELIST GROUPS 空闲链表组的数量

以上两个参数只能在create table,cluster,index时使用

BUFFER_POOL 可以用来为对象设置一个默认的缓冲池，同样的，回滚段不能设置。

有关存储参数的详述见oracle管理基础一章。

再看看DML insert的大致语法 ：

涉及到的clause有插入路径，方式，schema，是否使用dblink，指定分区或者子分区，插入条件，返回值，错误日志记录，拒绝的限制等等，具体可以参见oracle官方文档《sql reference》。在oracle数据库特性一章中单独对分区，直接路径插入，物化视图等进行了详述。

由此也可以看到oracle的dml扩展相当强，可以满足很多数据操纵的需求，关注这些oracle提供的特性会使得问题的解决有更多的选择。

二，条件和表达式

1，数据类型

Varchar2，nvarchar2，char的简单比较

Varchar2与nvarchar2都是可变长度的字符类型，不同的是nvarchar2与国家字符集相关，而char是固定长度的类型。看看他们之间的区别：

可以看到对于字符来说三者的长度相同，但对于汉字来说就有区别了，而且不同的字符集也存在差异，一个很常见的问题是汉字乱码问题，这是由于数据库的字符集设置与客户端的差异造成的，一般对于开发人员来说这个不用考虑。另外要注意的，char可以不设置长度，默认为1，而varchar2需要设置长度。例如：

有关字符集的问题详见附录一章。有关在设计时数据类型的选择和特殊字符处理方法在数据库设计一章中详述，有关xmltype在oracle xml一章中详述。有关oracle的基本数据类型，可以参见oracle官方文档的《sql reference》的datatypes一节。

2，数据类型的比较规则

主要看几个常见且比较重要的例子。

Char字符的比较，是比较ascii值的大小：

字符与数值的比较，有一个隐身转换的过程：

但是下面这样的情况并不会将32隐身转换为’32’：

之所以这里特意提到隐式转换，是因为，往往在sql条件中，由于隐式转换可能会禁用相关的索引，这个也是sql性能的一大隐患，可以简单看一个例子，注意不能使用上面创建的表，drop后新建，插入下列数据：

可以���到由于隐式转换使得查询走了不同的执行路径，在很多时候这种变化不是开发者所期待的，所以需要留意数据类型。具体可以参加《sql reference》的Datatype Comparison Rules一节。

3，Null详解

一行的一个cell如果没有值，那么就是null的，注意并不是他的值是null，所以col=null与col is null是完全不同的，null有很多特性，可以简单看几个例子来说明：

可以看出，''与null都是没有值的，可以认为他们相同，但是：

同样的情况：

这说明null不等于null，其他任何值与null的比较，结果是未知的。

在一些关联问题中字段包含了null值，通常情况下可以使用外连接来解决null值的影响。

同时也可以利用null的特性来实现一些特定的需求，比如:

插入的两条数据如果是模拟某种成对的操作，现在要知道某个id下面的send和recv时间���并且在一条中显示（往往情况比这个复杂），那么就可以使用：

Rownum则是结果集的临时行标。

很多时候需要取表或者集合内的一部分数据来使用，使用rownum很容易达到这个目的。或者在需要对一部分临时数据编号时使用，如果需要对导入数据库的一部分数据按照导入的次序进行排序编号，那么可以使用rownum的特性：

或者要获得top n的数据：

5，集合运算

Union 与Union all

前者是获取每个查询的所有不重复的行，后者是所有行，包括重复的

选择所有不重复的相交行

第一个查询减去第二个查询所得的结果。

先来描述一下oracle的连接和子查询，在很多情况下，连接和子查询可以相互替代，这时候如何选择使用很多是凭个人习惯，不过二者在执行的效率问题上面确并不是完全一样的，往往需要根据具体的数据量的大小结合执行计划来调整。

in和exists同样可以相互替代，也需要根据具体的情况来选择使用。使用in的情况下，oracle有限查询子查询，然后匹配外层查询，exists则刚好相反。

一般来说，后面的子查询结果集如果比较小的话，一般使用in，如果子查询较大，而外层的查询相对较小，则使用exists。

Not in 和not exists则不能完全替换，他们有一些差异。具体先看一个例子：

而exists 和in分别的结果是：

这样区别就一目了然了。

7，decode,case when,connect by 语句

case when 条件1 then 值1

when 条件n then 值n

else 值n+1

很多时候这两个语句是类似的，比如：

不过一般来说decode用来处理匹配少量数值，且匹配条件比较简单，如果过于复杂的条件则应该使用case语句。

Connect by ..start with通常用来做树形查询。举个简单的树形结构例子，比如全国的机构组织架构，总部设为A，一级分部AXX（AAA，AAB），二级分部AXXXX（AAAAA，AAAAAB，AABAA，AABAB），创建数据如下：

1 A 总公司

2 AAA 分公司1

3 AAAAA 支公司1

3 AAAAB 支公司2

2 AAB 分公司2

3 AABAA 支公司3

3 AABAB 支公司4

8，10g 正则表达式

很多时候需要对数据做处理，10g之前通常会自定义一些通用函数来实现一些规则，10g开始提供了几个函数来支持正则表达式：

先来看看oracle10g正则表达式定义的运算符。

\ 反斜杠有四个不同意义：取本身的值，引用下一个字符，介绍一个运算符，什么也不做

示例：

* 匹配0个或者多个表达式

+ 匹配一个或者多个表达式

? 匹配0个或者一个表达式

| 指两项之中的选择

^ 匹配开头的字符

$ 匹配结尾的字符

. 匹配任何字符除了null

[] 括号表达式

() 子表达式的开始和结尾

{m} 精确的匹配次数

{m,} 至少m次匹配

{m,n} m到n次匹配

9，group by语句

Group by主要用来分组统计，这个是开发中经常被使用的语句，先来看几个语句的运行情况：

例如求各部门内的最大工钱值，根据部门来分组统计：

增加一个order by deptno子句

增加一个order by max(sal)子句

上述三个语句略有不同，不过都是从全表扫描emp表开始执行，然后在此基础上进行group by或者order by，具体方式有所不同，这里仅仅把执行计划和统计信息给出来，具体在性能调整一章中进行详细测试分析与叙述。

主要来看看group by的增强语句，例如：having 子句，grouping sets,roll up,cube,group_id等

Having 子句

改进上述查询语句，只需要工钱大于等于3000的记录：

Rollup语句

Rollup可以理解为一维多层的统计，往往在需要按层次统计的时候用到。

修改一下需求，现在需要按照部门，分工作职位来统计工钱的总和，并求全公司之和，可以简单的使用rollup来完成：

可以看到使用rollup使得sum增加了聚合的级别，即实现了对多层进行分组统计计算。

现在稍稍修改一下需求，不需要对全公司进行统计，只需要计算各部门的总和和部门下不同工种的求和，那么修改后的语句可以如下：

再增加一个工头的字段，需要知道不同的工头下面工人的情况：

可以这样理解上面rollup语句：

group by col1,rollup(col2,col3)会进行如下计算：

Cube语句

Cube的意思是立方，主要是用来进行多维度的统计的，Cube有时也可以代替rollup，比如group by col1,rollup(col2)就可以用group by col1,cube(col2)来代替，可以用下面的语句带实现前面rollup的实现2：

可以看到除了结果集的排序不同以外，其他均相同。这时候使用rollup和cube分别进行了如下的计算：

注意上述表达式的顺序

但是cube的group by col1,cube(col2,col3)与rollup差别较大，事实上它的计算如下：

而group by cube(col1,col2,col3)则会计算2的3次方次，即维度为3.计算如下：

Grouping sets语句

如果说rollup和cube是oracle预定义了的计算维度，那么grouping sets则可以理解为可以自己设置计算维度的一个表达式，用下面一个例子来看：

说明了group by a.deptno,cube( b.job,b.mgr)

与group by a.deptno,grouping sets((b.job,b.mgr),(b.job),(b.mgr),())的结果是完全一样的，根据对cube的理解则grouping sets也很容易理解，不在累述。

Grouping_id和group_id函数

对于使用cube或者rollup合作而后grouping sets的语句，可以使用group_id()或者grouping_id()来获得不同的分组：

10，dml扩展示例

先来看个例子，

可以看出with的一般用法，with子句只能用于select，一个比较好的用处是使得代码的逻辑比较清楚。

另外with语句使用系统临时表，一定程度上可以提升性能，因为oracle执行一次WITH子查询，会将结果放到临时表中，如果随后有对子查询的多次访问，那么会从临时表中直接读取数据。有关性能的分析具体给出yangtingkun老师的文章：http://yangtingkun.itpub.net/post/468/202694

可以使用insert all select 同时插入不同的表中，也可以使用带条件的insert all语句，增加条件判断语句：when ..then .into。

使用insert first 则下一个条件将自动不考虑上一个条件被选中的行。

带条件的update:

更新视图：

但要注意，不能同时update多个关联的基表：

三，分析函数

分析函数计算基于一组行的聚合值，与聚合函数不同的是它为每个分组返回多行值。行的分组被叫做一个窗口，在analytic_clause子句中定义。

具体的句法如下：

其中：

Analytic_function函数名，9i/10g中有26个分析函数，接下来会逐一分析。

使用over告知查询分析器函数为分析函数而不是聚合函数，且在随后的子句指出要在那些字段上做分析计算。

使用partition表示对数据进行分组，如果没有指定，则将全部的结果作为一个分组来对待。

Order by添加一个默认的开窗子句，告知当前分组内的计算顺序。后面添加nulls last或者nulls first是正对null值的处理。

定义分组内用于计算或操作的具体行的集合。

产生一个滑动窗口，在组中拥有指定的range的行，使用range时对order by限制为一列，使得其滑动窗口的范围为一维。

下面分几组来讲解分析函数的具体使用：

1, 评级函数row_number,rank dense_rank,percent_rank，cume_dist,ntile

先来看看row_number，现在要对scott用户下面的工人表emp按照薪水从小到大排序，看看哪些是困难户：

使用row_number()很容易为一个分组里面的行排序并加上序号（注意上面执行计划id为2的window sort。）

上述排序中12和13的工钱一样多，这样排序只是按照行的rowid来排的，默认是从小到大，可以具体看看：

通过看相同deptno的rowid就可以看出来这个规律。

现在需要对分不同部门来看部门内的工钱排名，且从大到小排列：

如果每个部门只要取前三名的话，则可以稍稍修改查询：

很容易想到用嵌套查询，在外层限定序号num的值不超过3，注意看看执行计划id为2的行，使用的是WINDOW SORT PUSHED RANK

下面就来看看rank是如何实现上面的这个求前三甲的需求的：

好像写法是一样的，但是结果稍稍有所不同，rank会把相同的sal当作并列处理。但看执行机会和统计信息则发现基本一样(这里猜测oracle内部实现row_number和rank的主要算法是一样的)

Dense_rank和rank稍有不同，不过是序号不会像rank一样跳跃。再看一个例子：

以上是分部门和工种来求工钱总和，并且使用了grouping sets根据需要获得分组的排名。

Percent_rank也很容易理解：

当前行占分组内行的百分比，比如要知道当前的员工的工钱水平占整个部门内的什么水平，可以这样来看：

显然KING和CLARK都是部门内50%的富人，但是他们相差却非常之大，贫富差距往往是数据看不出来的，前面还没有用avg来求平均值，求了则更是粉饰了真正的现实。想到最近10年或者20年（从记事开始算起）以来的变迁，我们的“涉会注意郭家”是相当优越的，对内喜欢讲人均，对外喜欢将总和，就是转移一下贫富差距的视线，现在的情况是大寡头占有了涉会绝大多数财富资源，很少数的人抢占了大多数人的幸福生活。不要觉得意外，其基本原理本来就是这样：

让一部分人先富起来，带动全民富裕。

这句看着很搞笑了。

下面继续来看看cume_dist,和percent_rank差不多，看一个例子就可以知道了：

而ntile是将行再分组，修改上述查询也很容易理解，ntile(2)将行分为2组，下面例子中4行的即为2，2，如果是3行，则2，1，其他雷同：

有关评级函数，再说一个表结构设计时候的问题，现在有一个log表，记录某些操作的具体信息（操作事件，操作人，操作信息，开始时间，结束时间，操作对象id，操作状态等等），现在要对操作人考核，看看这个人总共操作了多少次，在操作中花了多少时间，每个状态值停了多少时间，从第一次开始操作，到最终完成操作又话了多少时间。好像没有什么是sql完成不了的，rank,row_number,或者lag,lead可能都可以用的上，不过个人觉得，像经常性查询的一些关键字段，应该在基表内有一定的冗余字段，关联或者计算在大数据量和高响应要求时给系统带来了负面影响，严格来讲分析函数应该尽可能多用于数据仓库或者分析报表库，而不是oltp的生产系统，但是往往情况不是这样。

2,行筛选相关的函数keep,first,last,first_value,last_value,lag,lead

先来看两个查询，一个实现的是部门内最早雇佣的员工中的工资最多与最小的值，一个是实现工资最高和最低的员工中入职时间最早或最晚的值：

查询1：

查询2：

上面的例子中时间没有完全相同的，那么简单修改一下，则可以更好的理解keep中的first和last与min和max的意义：

正对上面的查询1来修改得到

查询3：

first和last很容易理解。Dense_rank的特点上面的keep中的dense_rank first或者dense_rank last都可能会取到多行。

在hiredate修改为to_char(hiredate,'yyyy')以后，有了在同一年份入职的员工，那么first其实会把第一组年��的员工行都取了，外层使用min(a.sal)或者max(a.sal)就是再对组中的员工求最大最小工钱值。

接下来再看看first_value和last_value,有四个查询例子：

这个查询按deptno开窗找第一个和最后一个sal：

下面的查询在上面的基础上增加了order by deptno语句：

下面的查询是按照sal升序排序后的，这样每个部门下面按照sal排序了，每增加一条数据，都会取当前的first和last，因此可以看到每行的last都在变化。

再修改一下，order by rowid，效果差不多：

再来看lag和lead，分别用两个sql来求部门内按照hiredate排序后的每个苦工上一个或者下一个的工钱值：

还有部分层次查询的，也是内容比较少，也放上来吧

1,层次查询的伪列

当前节点（行）的孩子节点（行）也是其父节点（行），则为1，否则为0

如果是叶子节点，则输出为1，否则为0

当前节点（行）的层号

用一个例子来直观的看看上述三个伪列的作用：

2,层次查询

稍稍修改一下上述查询：

上述2个查询均使用sys_connect_by_path，不同是prior的位置不同，前一个是从ename为KING开始，遍历mgr为KING或者间接的mgr为KING的所有路径，直到叶子节点。后面一个是从SMITH开始的所有SMITH的上司的路径，可以看出2者走的路径刚好相反。

修改上述2个查询，再来看看connect_by_root()

显然只是给出了root，而不是path。

通常需要获得层次关系，层号，或者路径等问题可以通过oracle提供的层次查询语法和相应的伪列来解决。