使用LogMiner分析Oracle
使用LogMiner分析Oracle8i_9i的重做日志和归档日志
收藏到：
发布时间：2007-5-14 21:31:54
LogMiner是集成在Oracle8i/Oracle9i数据库产品中的日志分析工具，通过该工具可以分析重做日志和归档日志中的所有事务变化，并能准确地确定各种DML和DDL操作的具体时间和
SCN值。对重做日志和归档日志进行分析的目的是为了恢复由于执行了误操作而丢失的数据。
使用LogMiner可实现：①确定数据逻辑损坏的时间。例如，用户执行了DROP TABLE 和
TRUNCATE TABLE命令等误操作后，使用LogMiner可以准确定位执行这些误操作的具体时间。注
意：Oracle8i只能还原DML操作，不能还原DDL操作；Oracle9i可以还原DDL以及DML 操作。②
跟踪用户执行的事务变化操作。使用LogMiner不仅可以跟踪用户所执行的各种DML操作和DDL操
作，而且还可以取得数据变化。③跟踪表的DML操作，使用LogMiner可以跟踪在表上所发生的
下面以Oracle8i(8.1.5)for Windows 2000/NT为例说明如何使用LogMiner对重做日志和归
档日志进行分析。本文进行的所有操作均在该环境下验证通过，文中使用的数据库名为phj，
一、日志操作模式
Oracle数据库对日志的管理模式有两种，即：非归档模式和归档模式。在建立或安装Oracle数据库时，如果没有指定日志操作模式，则默认为非归档模式（NOARCHIVELOG）。数据
库建立之后，用户可以根据需要修改数据库日志的操作模式。
1．非归档模式（NOARCHIVELOG）
非归档模式是指不保存重做日志的日志操作模式，这种日志操作模式只能用于保护实例失
败（如系统断电），而不能用于保护介质失败（数据库物理文件损坏）。
非归档模式的工作过程如下：假设数据库只有两个日志组，且当前日志组为日志组一，日
志序列号为1。当事务变化填满日志组一时，系统会切换到日志组二，并且LGWR进程将事务变
化写入该日志组，日志序列号变为2；而当事务变化填满日志组二时，系统又自动切换回日志
组一，此时日志序列号变为3，并且日志序列号3所对应的事务变化会覆盖日志序列号1所对应
2．归档模式（ARCHIVELOG）
归档模式是指将重做日志内容保存到归档日志中的日志操作模式。在这种日志操作模
，当进行日志切换时ARCH进程会将重做日志的内容复制到归档日志中。例如，假设数据库只包
含两个日志组，LGWR进程首先将事务变化写入日志组一，此时日志序列号为1；当事务变化填
满日志组一时，系统将自动切换到日志组二，并将事务变化写入日志组二，此时日志序列号变
为2，同时后台进程ARCn会将日志组一的内容保存到归档日志文件1中；而当事务变化填满日
志组二时，系统自动切换回日志组一，并将事务变化写入日志组一，此时日志序列号变为3，
同时后台进程ARCn会将日志组二的内容保存到归档日志文件2中，依此类推。
3．改变日志的操作模式为自动归档模式
①修改数据库的参数文件，在参数文件后添加如下参数：
log_archive_start = true
log_archive_dest_1 = "location=C:Oraclephjarchive"
log_archive_format = "T%TS%S.ARC"
说明：第一个参数在重启数据库时将自动启动ARCH进程；第二个参数指出归档日志存放的
位置；第三个参数说明归档日志的文件名格式，%T取重做线程号，%S取日志序列号；
②重启并装载数据库
sqlplus internal/oracle@phj
shutdown immediate
startup mount pfile=%Oracle_Home%databaseinitphj.ora
alter database archivelog
alter database open
4．生成归档日志
下面以分析TMP表的DDL和DML操作为例，介绍使用LogMiner分析重做日志和归档日志的全
过程。我们首先执行以下语句在表TMP上执行DDL以及DML操作，并生成归档日志：sqlplus internal/oracle@phj
create table tmp(name varchar2(10),no number(3));
alter system switch logfile;
insert into tmp values(…liming?,112);
update tmp set no=200;
commit;
alter system switch logfile;
delete from tmp;
alter system switch logfile;
通过上面的操作会产生三个连续的归档日志文件。
二、建立字典文件
字典文件用于存放表及对象ID号之间的对应关系。当使用字典文件时，它会在表名和对象
ID号之间建立一一对应的关系。因此需要注意，如果用户建立了新表之后，并且将来可能
该表进行日志分析，那么就需要重新建立字典文件，以将其对象名及对象ID号存放到字典文件
1．修改参数文件，添加参数：UTL_FILE_DIR=C:Oraclephjlogmnr
目录logmnr必须事先手工建立，该目录用于存放产生的字典文件，目录名称可以自定。
2．重新启动数据库
sqlplus internal/oracle@phj
shutdown immediate
startup pfile=%Oracle_Home%databaseinitphj.ora
3．建立字典文件
BEGIN
dbms_logmnr_d.build(
dictionary_filename=>?dict.ora?,
d ictionary_location=>?c:oraclephjlogmnr?);
END;
/
注意：参数dictionary_filename用以指定字典文件的文件名；参数dic
tionary_location用于指定存放字典文件所在的目录，该目录必须与初始化参数
UTL_FILE_DIR
的值一致。如果指定的字典文件名dict.ora已经存在，则应在执行此操作前将其彻底删除（从
三、建立日志分析列表
建立日志分析列表必须在MOUNT或NOMOUNT状态下完成。
1．停止并装载数据库
sqlplus internal/oracle@phj
shutdown immediate
startup mount pfile=%Oracle_Home%databaseinitphj.ora
2．建立日志分析列表
在“生成归档日志”的操作中已经生成了三个连续的归档日志文件，为便于分析我们重新
复制这三个归档日志文件并分别命名为：test1.arc、test2.arc、test3.arc。通过以下操作BEGIN
dbms_logmnr.add_logfile(
options=>dbms_logmnr.new,
logfilename=>?c:oraclephjarchive est1.arc?);
END;
/
3．增加其它欲进行分析的日志文件
BEGIN
dbms_logmnr.add_logfile(
options=>dbms_logmnr.addfile,
logfilename=>?c:oraclephj archive est2.arc?);
END;
/
用同样的方法将归档日志文件test3.arc增加到日志分析列表中。可以通过动态性能视图
v$logmnr_logs查看日志分析列表中有哪些待分析的日志文件。
FILENAME
----------------------------------------
c:oraclephjarchive est1.arc
c:oraclephjarchive est2.arc
c:oraclephjarchive est3.arc
四、启动LogMiner执行分析
1．分析重做日志和归档日志
BEGIN
dbms_logmnr.start_logmnr(
dictfilename=>?c:oraclephjlogmnrdict.ora?,
starttime=>to_date(?2003-02-10:10:05:00?,?YYYY-MM-DD:HH24:MI:SS?),
endtime=>to_date(?2003-02-10:10:17:00?,?YYYY-MM-DD:HH24:MI:SS?)
);
END;
/
注意：此过程能否执行成功的关键是给出的starttime（起始时间）和endtime（终止时
间）应在一个有效的范围内。特别是终止时间，应小于或等于归档日志的建立时间；如果大于
归档日志的建立时间，则不能执行分析过程。分析多个归档日志时，这些归档日志最好是连续
2．查看日志分析结果
上面对日志进行分析的结果存放在动态性能视图v$logmnr_contents中，通过查询该动态
性能视图可以取得所有DML以及其反操作。
2 from v$logmnr_contents
3 where seg_name='TMP';
OPERATION SQL_REDO SQL_UNDO
----------- --------------------- --------------------------
INSERT insert into SYS.TMP…… delete from SYS.TMP ……
UPDATE update SYS.TMP set NO …… update SYS.TMP set ……
DELETE delete from SYS.TMP …… insert into SYS.TMP ……
根据需要可以从动态性能视图v$logmnr_contents中获取更多的有用信息，限于篇幅此不
赘述。对重做日志进行分析的方法与对归档日志分析的方法一样，只是在建立日志分析列表时
，需将重做日志文件添加到日志分析列表中。
Oracle8i只能对DML操作进行分析，从Oracle9i开始不仅可以分析DML操作，而且也可以
分析DDL操作。在Oracle9i中可使用如下语句查询DDL操作及具体的操作时间：
2 from v$logmnr_contents
3 where sql_redo like '%create%' or sql_redo like '%CREATE%';
3．结束分析
结束LogMiner分析后，服务器进程会自动删除与LogMiner相关的动态性能视图所包含的事
务变化内容。命令如下：
Oracle中分析函数用法小结
Oracle中分析函数用法小结 一.分析函数适用场景: ○1需要对同样的数据进行不同级别的聚合操作 ○2需要在表内将多条数据和同一条数据进行多次的比较 ○3需要在排序完的结果集上进行额外的过滤操作 二.分析函数语法: FUNCTION_NAME(
Oracle 分析函数(Analytic Functions) 说明
Oracle 分析函数(Analytic Functions)说明一. Analytic Functions 说明 分析函数是oracle 8中引入的一个概念,为我们分析数据提供了一种简单高效的处理方式. 官方对分析函数的说明如下： Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. The groupof rows is called a window and is defined bythe analytic_clause. For each row, a sliding window of rows is defined.The window determines the range of rows used to perform the calculations forthe current row. Window sizes can be based on either a physical number of rowsor a logical interval such as time. Analytic functions are the last set of operations performed in a query except for thefinal ORDER BY clause. All joins and all WHERE, GROUP BY,and HAVING clauses are completed before the analytic functions areprocessed. Therefore, analytic functions can appear only in the select listor ORDER BY clause. Analytic functions are commonly used to compute cumulative, moving, centered, andreporting aggregates. From：Analytic Functions https://www.360docs.net/doc/1b8392591.html,/cd/E11882_01/server.112/e26088/functions004.htm#S QLRF06174 分析函数是对一组查询结果进行运算，然后获得结果，从这个意义上，分析函数非常类似于聚合函数（Aggregate Function）。区别是在调用分析函数时，后面加上了开窗子句over()。 聚合函数是对一个查询结果中的每个分组进行运算，并且对每个分组产生一个运算结果。分析函数也是对一个查询结果中的每个分组进行运算，但每个分组对应的结果可以有多个。产生这个不同的原因是分析函数中有一个窗口的概念，一个窗口对应于一个分组中的若干行，分析函数每次对一个窗口进行运算。运算时窗口在查询结果或分组中从顶到底移动，对每一行数据生成一个窗口。 Oracle 聚合函数（Aggregate Functions）说明 https://www.360docs.net/doc/1b8392591.html,/tianlesoftware/article/details/7057249
ORACLE排序与分析函数
--已知：两种排名方式(分区和不分区):使用和不使用partition --两种计算方式(连续,不连续),对应函数：dense_rank,rank 语法： rank()over(order by排序字段顺序) rank()over(partition by分组字段order by排序字段顺序) 1.顺序：asc|desc名次与业务相关： 示例：找求优秀学员：成绩：降序迟到次数：升序 2.分区字段：根据什么字段进行分区。 问题：分区与分组有什么区别? ·分区只是将原始数据进行名次排列(记录数不变), ·分组是对原始数据进行聚合统计(记录数变少,每组返回一条),注意：聚合。rank()与dense_rank()：非连续排名与连续排名(都是简单排名) ·查询原始数据：学号,姓名,科目名,成绩 select*from t_score; S_ID S_NAME SUB_NAME SCORE 1张三语文80.00 2李四数学80.00 1张三数学0.00 2李四语文50.00 3张三丰语文10.00 3张三丰数学 3张三丰体育120.00 4杨过JAVA90.00 5mike c++80.00 3张三丰Oracle0.00 4杨过Oracle77.00 2李四Oracle77.00 ·查询各学生科目为Oracle排名(简单排名) select sc.s_id,sc.s_name,sub_name,sc.score,rank()over(order by score desc)名次from t_score sc where sub_name='Oracle'; S_ID S_NAME SUB_NAME SCORE名次 4杨过Oracle77.001 2李四Oracle77.001 3张三丰Oracle0.003 ·对比：rank()与dense_rank()：非连续排名与连续排名(都是简单排名) S_ID S_NAME SUB_NAME SCORE名次
Oracle分析函数sum over介绍
分析函数sum over() 介绍 报送单位：审核人： 类型：业务应用 关键字：分析函数 1、引言 运维中，常常需要通过SQL语句对单行数据进行查询，同时又需要对结果集进行汇总，通常的方法是通过两个SQL语句分别进行查询汇总，这样效率低下。 2、现象描述 本节介绍一种ORACLE提供的全新的函数sum over()，该类函数称为分析函数，这类函数功能强大，可以通过一个SQL语句对数据进行遍历的同时又进行汇总，而且一张表只进行一次扫描，极大地提高SQL的执行效率。 3、处理过程 语法： FUNCTION_NAME(
执行计划： 下图说明分析函数只对表进行一次扫描 4、举例说明 下面分别举例来说明分析函数的使用，原始数据如下表
示例1：查询单行数据同时对所有数据工资进行汇总求和 示例2：查询所有数据，同时对第各部门工资进行汇总，汇总范围取值为第一行所在部门至当前部门所有数据。
示例3：查询所有数据，并且每一行汇总值按ENAME排序后取第一行至当前行ENAME所在行。 示例4：查询所有数据，并且每一行汇总值按EMPNO排序后取第一行至当前行EMPNO所在行
示例5：查询所有数据，同时每行对各部门分别进行按EMPNO排序后从各组第一行至当前行汇总求和。 示例6：查询所有数据，同时每行对从当前上两行数据范围的工资进行汇总求和。 示例7：查询所有数据，同时每行对从当前向前一行数据向后两行数据范围的工资进行汇总求和。
Oracle分析函数
Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是 对于每个组返回多行，而聚合函数对于每个组只返回一行。 一、开窗函数 开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。 例如： 1)over（order by salary）按照salary排序进行累计，order by是个默认的开窗函数 2)over（partition by department_id）按照部门分区 3)over（order by salary range between 50 preceding and 150 following） 每行对应的数据窗口是之前行幅度值不超过50，之后行幅度值不超过150 4)over（order by salary rows between 50 preceding and 150 following） 每行对应的数据窗口是之前50行，之后150行 5)over（order by salary rows between unbounded preceding and unbounded following） 每行对应的数据窗口是从第一行到最后一行，等效： 6) over（order by salary range between unbounded preceding and unbounded following） 其中： 第一行是unbounded preceding 当前行是current row 最后一行是unbounded following 二、分析函数的概念 分析函数是在整个SQL查询结束后(SQL语句中的ORDER BY的执行比较特殊)再进行的操作, 也就是说 SQL语句中的ORDER BY也会影响分析函数的执行结果。 分析函数中包含三个分析子句:分组(Partition By), 排序(Order By), 窗口(Window) 当省略窗口子句时: 1) 如果存在Order By则默认的窗口是unbounded preceding and current row 2) 如果同时省略Order By则默认的窗口是unbounded preceding and unbounded following 如果省略分组,则把全部记录当成一个组 a) 如果SQL语句中的Order By满足分析函数分析时要求的排序,那么SQL语句中的排序将先执行,分析 函数分析时就不必再排序 b) 如果SQL语句中的Order By不满足分析函数分析时要求的排序，那么SQL语句中的排序将最后在分 析函数分析结束后执行排序 三、分析函数 1）AVG
Oracle自定义聚合函数-分析函数
自定义聚合函数，分析函数 --from GTA Aaron 最近做一数据项目要用到连乘的功能，而Oracle数据库里没有这样的预定义聚合函数，当然利用数据库已有的函数进行数学运算也可以达到这个功能，如： selectexp(sum(ln(field_name))) from table_name; 不过今天的重点不是讲这个数学公式，而是如何自己创建聚合函数，实现自己想要的功能。很幸运Oracle 允许用户自定义聚合函数，提供了相关接口，LZ研究了下，留贴共享。 首先介绍聚合函数接口： 用户可以通过实现Oracle的Extensibility Framework中的ODCIAggregate interface 来创建自定义聚合函数，而且自定义的聚合函数跟内建的聚合函数用法上没有差别。 通过实现ODCIAggregaterountines来创建自定义的聚合函数。可以通过定义一个对象类型（Object Type），然后在这个类型内部实现ODCIAggregate 接口函数(routines)，可以用任何一种Oracle支持的语言来实现这些接口函数，比如C/C++, JAVA, PL/SQL等。在这个Object Type定义之后，相应的接口函数也都在该Object Type Body内部实现之后，就可以通过CREATE FUNCTION语句来创建自定义的聚合函数了。 每个自定义的聚合函数需要实现4个ODCIAggregate 接口函数，这些函数定义了任何一个聚合函数内部需要实现的操作： 1. 自定义聚合函数初始化操作,从这儿开始一个聚合函数。初始化的聚合环境(aggregation context)会以对象实例(object type instance)传回给oracle. static function ODCIAggregateInitialize(varIN OUTagg_type ) return number 2. 自定义聚合函数,最主要的步骤,这个函数定义我们的聚合函数具体做什么操作，self 为 当前聚合函数的指针,用来与前面的计算结果进行关联。这个函数用来遍历需要处理的
oracle高级分析函数使用实例
oracle高级分析函数使用实例 2014年11月26日10:26:55 ?标签： ?oracle ?1744 ORACLE的分析函数，发现大家写SQL的时候有些功能写的比较麻烦或者不知道复杂的功能怎么通过SQL实现，ORACLE自带的分析函数有很多相应的功能： 它是Oracle分析函数专门针对类似于"经营总额"、"找出一组中的百分之多少" 或"计算排名前几位"等问题设计的。 分析函数运行效率高，使用方便。 分析函数是基于一组行来计算的。这不同于聚集函数且广泛应用于OLAP环境中。 Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是 对于每个组返回多行，而聚合函数对于每个组只返回一行。 语法：
组。这里的"分区partition"和"组group" 都是同义词。 5 排序子句order-by-clause指定数据是如何存在分区内的。其格式为：order[siblings]by{expr|position|c_alias}[asc|desc][nulls first|nulls last] 其中： (1)asc|desc:指定了排列顺序。 (2)nulls first|nulls last:指定了包含空值的返回行应出现在有序序列中的第一个或最后一个位置。 6窗口子句windowing-clause 给出一个固定的或变化的数据窗口方法，分析函数将对这些数据进行操作。在一组基于任意变化或固定的窗口中， 可用该子句让分析函数计算出它的值。 格式： {rows|range} {between {unbounded preceding|current row |
Oracle高级查询总结
高级查询总结 A．层次查询：start with……connec by prior…….. select lpad(' ',3*level)||ename,job,level from emp start with mgr is null connect by prior empno=mgr; 从根结点向下查，lpad()为左添加，level为第几层，prior为查找的方向；此句若省掉start with….则表示要遍历整个树型结构；若出现level，则后面一定要跟connect by B.交叉报表(case when then end 与decode()) select ename,case when sal>=1500then sal*1.01 else sal*1.1 end工资 from emp; select姓名, max(case课程when'语文'then分数end) 语文, max(case课程when'数学'then分数end) 数学, max(case课程when'历史'then分数end) 历史 from学生group by姓名;------(交叉报表与函数一起使用) select ename,sum(decode(sal,'sal',comm,null)) 奖金from emp group by ename;--可实现分支 decode(条件,(值),(返回值),…………,(默认值)) 部门 select sal,decode(sign(sal-1500),-1,1.1*sal,0,1.1*sal,1,1.05*sal) from emp; C.连接查询 1．等值： select * from emp,dept where emp.deptno(+)=dept.deptno; ‘+’在左则以右表为主，反之亦然 2.非等值：where的运算符不是等号 3.自然连接: select * from emp natural join dept 4.99乘法: select * from emp e full join dept d using (deptno) where deptno=10; --where必须放在using(on)的后面 D集合查询： 1．A Union B:消除重复行，有all则可以重复,默认第一列升序select ename,sal from deptno=20 union select ename,sal from job=’CLERK’; 2．A intersect B:A与B的交集 select ename,sal from deptno=20 intersect select ename,sal from job=’CLERK’; 3.A minus B:在A中减去既属于A又属于B的一部分
Oracle 分析函数
--每一个值占总数的百分比 SELECT x, y, z,round(z/sum(z) over()*100,2)||'%' propn , sum(z) over() sum FROM t1; --每一个值占分组的百分比 SELECT x, y, z,round(z/sum(z) over(partition by x)*100,2)||'%' propn , sum(z) over(partition by x) sum FROM t1; --以x分区，按y排序累计取和 SELECT x, y, z, sum(z) over(partition by x order by y desc) sum FROM t1; --以x分区，按z降序，每个分区取前两个 select * from (select x, y, z, s, dense_rank() over(partition by x order by z desc) r1, rank() over(partition by x order by z desc) r2, count(*) over(partition by x order by z desc, y range unbounded preceding) r3 from (SELECT x, y, z, sum(z) over(partition by x) s FROM t1 order by 4 desc, z desc)) where r3 < 3 order by z desc, x /* 语法： function_name(
Oracle分析函数与分组关键字的用法
Oracle分析函数与分组关键字的用法 以下是我以前工作中做报表常用的几个函数，在此分享一下，希望对大家有帮助。 (一)分析函数 ●row_number Purpose ROW_NUMBER is an analytic function. It assigns a unique number to each row to which it is applied (either each row in the partition or each row returned by the query), in the ordered sequence of rows specified in the order_by_clause, beginning with 1. You cannot use ROW_NUMBER or any other analytic function for expr. That is, you can use other built-in function expressions for expr, but you cannot nest analytic functions. 按部门分组后根据工资排序，序号rn特征：连续、无并列 select t.*, row_number() over(partition by t.deptno order by sal desc) rn from emp t; ●rank
Purpose RANK calculates the rank of a value in a group of values. Rows with equal values for the ranking criteria receive the same rank. Oracle then adds the number of tied rows to the tied rank to calculate the next rank. Therefore, the ranks may not be consecutive numbers. ? As an aggregate function, RANK calculates the rank of a hypothetical row identified by the arguments of the function with respect to a given sort specification. The arguments of the function must all evaluate to constant expressions within each aggregate group, because they identify a single row within each group. The constant argument expressions and the expressions in the ORDER BY clause of the aggregate match by position. Therefore, the number of arguments must be the same and their types must be compatible. As an analytic function, RANK computes the rank of each row returned from a query with respect to the other rows returned by the query, based on the values of the value_exprs in the order_by_clause. 按部门分组后根据工资排序，序号rn特征：不连续、有并列 select t.*, rank() over(partition by t.deptno order by sal desc) rn from emp t; dense_rank
oracle之分析函数over及开窗函数
oracle之分析函数over及开窗函数 一：分析函数over Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，而聚合函数对于每个组只返回一行。 统计各班成绩第一名的同学信息 NAME CLASS S ----- ----- ---------------------- fda 1 80 ffd 1 78 dss 1 95 cfe 2 74 gds 2 92 gf 3 99 ddd 3 99 adf 3 45 asdf 3 55 3dd 3 78 通过： -- select * from ( select name,class,s,rank()over(partition by class order by s desc) mm from t2 ) where mm=1 ---- 得到结果：
NAME CLASS S MM ----- ----- ---------------------- ---------------------- dss 1 95 1 gds 2 92 1 gf 3 99 1 ddd 3 99 1 注意： 1.在求第一名成绩的时候，不能用row_number()，因为如果同班有两个并列第一， row_number()只返回一个结果 2.rank()和dense_rank()的区别是： --rank()是跳跃排序，有两个第二名时接下来就是第四名 --dense_rank()l是连续排序，有两个第二名时仍然跟着第三名 二：开窗函数 开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下： 1： over（order by salary）按照salary排序进行累计，order by是个默认的开窗函数over（partition by deptno）按照部门分区 2： over（order by salary range between 5 preceding and 5 following） 每行对应的数据窗口是之前行幅度值不超过5，之后行幅度值不超过5 例如:对于以下列 aa 1 2 2
Oracle统计学函数-大数据分析介绍
SQL Statistical Functions Make Big Data + Analytics Simple Charlie Berger, MS Engineering, MBA Sr. Director Product Management, Data Mining and Advanced Analytics charlie.berger@https://www.360docs.net/doc/1b8392591.html, https://www.360docs.net/doc/1b8392591.html,/CharlieDataMine
Data, data everywhere Data Analysis platforms requirements: ?Be extremely powerful and handle large data volumes ?Be easy to learn ?Be highly automated & enable deployment Growth of Data Exponentially Greater than Growth of Data Analysts! https://www.360docs.net/doc/1b8392591.html,/more-data-than-analysts-the-real-big-data-problem/
Analytics + Data Warehouse + Hadoop ?Platform Sprawl –More Duplicated Data –More Data Movement Latency –More Security challenges –More Duplicated Storage –More Duplicated Backups –More Duplicated Systems –More Space and Power
Oracle分析函数使用的总结(学练结合)
Oracle分析函数使用总结 1.使用评级函数 评级函数（ranking function）用于计算等级、百分点、n分片等等，下面是几个常用到的评级函数：RANK（）：返回数据项在分组中的排名。特点：在排名相等的情况下会在名次中留下空位DENSE_RANK():与RANK不同的是它在排名相等的情况下不会在名次中留下空位 CUME_DIST()：返回特定值相对于一组值的位置：他是“cumulative distribution”(累积分布)的简写PERCENT_RANK()：返回某个值相对于一组值的百分比排名 NTILE():返回n分片后的值，比如三分片、四分片等等 ROW_NUMBER():为每一条分组纪录返回一个数字 下面我们分别举例来说明这些函数的使用 1）RANK（）与DENSE-RANK（） 首先显示下我们的源表数据的结构及部分数据： SQL> desc all_sales; 名称是否为空? 类型 ----------------------------------------- -------- ----------- YEAR NOT NULL NUMBER(38) MONTH NOT NULL NUMBER(38) PRD_TYPE_ID NOT NULL NUMBER(38) EMP_ID NOT NULL NUMBER(38) AMOUNT NUMBER(8,2) SQL> select * from all_sales where rownum<11; YEAR MONTH PRD_TYPE_ID EMP_ID AMOUNT ---------- ---------- ----------- ---------- ---------- 2003 1 1 21 10034.84 2003 2 1 21 15144.65 2003 3 1 21 20137.83 2003 4 1 21 25057.45 2003 5 1 21 17214.56 2003 6 1 21 15564.64 2003 7 1 21 12654.84 2003 8 1 21 17434.82 2003 9 1 21 19854.57 2003 10 1 21 21754.19 已选择10行。 好接下来我们将举例来说明上述函数的使用：首先是RANK（）与DENSE-RANK（）的使用：SQL> select 2 prd_type_id,sum(amount), 3 RANK() OVER (ORDER BY SUM(amount) DESC) AS rank, 4 DENSE_RANK() OVER (ORDER BY SUM(amount) DESC) AS dense_rank
oracle分析函数
Oracle开发专题之：分析函数(OVER) (1) Oracle开发专题之：分析函数2(Rank, Dense_rank, row_number) (6) Oracle开发专题之：分析函数3(Top/Bottom N、First/Last、NTile) (10) Oracle开发专题之：窗口函数 (14) Oracle开发专题之：报表函数 (20) Oracle开发专题之：分析函数总结 (22) Oracle开发专题之：26个分析函数 (24) PLSQL开发笔记和小结 (28) 分析函数简述 (60) 说明: 1)Oracle开发专题99%收集自: https://www.360docs.net/doc/1b8392591.html,/pengpenglin/(偶补充了一点点1%); 2) PLSQL开发笔记和小结收集自https://www.360docs.net/doc/1b8392591.html,/cheneyfree/ 3)分析函数简述收集自https://www.360docs.net/doc/1b8392591.html,/7607759/ 昆明小虫https://www.360docs.net/doc/1b8392591.html,/ 收集,并补充了一点点1% Oracle开发专题之：分析函数(OVER) 目录： =============================================== 1.Oracle分析函数简介 2. Oracle分析函数简单实例 3.分析函数OVER解析 一、Oracle分析函数简介： 在日常的生产环境中，我们接触得比较多的是OLTP系统(即Online Transaction Process)，这些系统的特点是具备实时要求，或者至少说对响应的时间多长有一定的要求；其次这些系统的业务逻辑一般比较复杂，可能需要经过多次的运算。比如我们经常接触到的电子商城。 在这些系统之外，还有一种称之为OLAP的系统(即Online Aanalyse Process)，这些系统一般用于系统决策使用。通常和数据仓库、数据分析、数据挖掘等概念联系在一起。这些系统的特点是数据量大，对实时响应的要求不高或者根本不关注这方面的要求，以查询、统计操作为主。 我们来看看下面的几个典型例子： ①查找上一年度各个销售区域排名前10的员工 ②按区域查找上一年度订单总额占区域订单总额20%以上的客户 ③查找上一年度销售最差的部门所在的区域 ④查找上一年度销售最好和最差的产品 我们看看上面的几个例子就可以感觉到这几个查询和我们日常遇到的查询有些不同，具体有：
Oracle分析函数学习总结(完成版)
Oracle分析函数总结1.rollup函数和Cube函数 1.1. rollup函数事例1 1.1.1.事例功能说明 功能：完成XX市公司各县合同种植面积、合同量统计功能。 优点：SQL简单，减少合计列的平凑工作 1.1. 2.SQL语句 select nvl(substr(t.dept_code,1,6), 'Total') Dept_Code, sum(t.zzmj) , sum(t.amount1) , sum(t.amount2) , sum(t.amount_sum) from tb_yy_contract t where t.dept_code like'5304%' group by rollup(nvl(substr(t.dept_code,1,6), 'Total'))
1.1.3.输出结果 1.1.4.使用说明 1、在使用过程中先通过部门编码查出分析数据然后在和部门表关 联获得部门名称完成该功能； 2、部门和区域分别写SQL语句； 1.2. rollup函数和cube函数的另外用法 1.2.1.事例功能说明 功能：完成XX市公司各县烤烟小等级收购情况 优点：SQL简单，查询效率高，并能减少代码工作量，易维护。 1.2.2.rollup函数实现SQL语句 select t.ddj_code, t.DJ_CODE, sum(t.amount), sum(t.money),
round(case sum(t.amount) when0then else sum(t.money) / sum(t.amount) end, 2) price from TB_YY_PLANTER_SALE_SUM_D t where t.nd = 2008 and t.dept_code like'5304%' group by rollup(t.ddj_code, t.DJ_CODE) 1.2.3.rollUp函数输出结果 耗时22.985秒 说明：rollup函数只对DDJ_CODE做汇总而我们下面介绍cube函数则是分别对DDJ_CODE和DJ_CODE都进行汇总
Oracle开发专题之——Oracle 分析函数rollup的使用
Oracle 分析函数rollup的使用 分析函数的使用方法 1. 自动汇总函数rollup,cube, 2. rank 函数, rank,dense_rank,row_number 3. lag,lead函数 4. sum,avg,的移动增加,移动平均数 5. ratio_to_report报表处理函数 6. first,last取基数的分析函数 1、Oracle ROLLUP和CUBE 用法 Oracle的GROUP BY语句除了最基本的语法外，还支持ROLLUP和CUBE语句。如果是Group by ROLLUP(A, B, C)的话，首先会对(A、B、C)进行GROUP BY，然后对(A、B)进行GROUP BY，然后是(A)进行GROUP BY，最后对全表进行GROUP BY操作。 如果是GROUP BY CUBE(A, B, C)，则首先会对(A、B、C)进行GROUP BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后对全表进行GROUP BY操作。grouping_id()可以美化效果。除了使用GROUPING函数，还可以使用GROUPING_ID来标识GROUP BY的结果。 也可以Group by Rollup(A,(B,C)) ，Group by A Rollup(B,C)，……这样任意按自己想要的形式结合统计数据，非常方便。 2、Rank的用法 功能描述：根据ORDER BY子句中表达式的值，从查询返回的每一行，计算它们与其它行的相对位置。组内的数据按ORDER BY子句排序，然后给每一行赋一个号，从而形成一个序列，该序列从1开始，往后累加。每次ORDER BY表达式的值发生变化时，该序列也随之增加。有同样值的行得到同样的数字序号（认为null时相等的）。然而，如果两行的确得到同样的排序，则序数将随后跳跃。若两行序数为1，则没有序数2，序列将给组中的下一行分配值3，DENSE_RANK则没有任何跳跃。 rank()是跳跃排序，比如有两个第二名时接下来就是第四名（同样是在各个分组内） dense_rank()l是连续排序，比如有两个第二名时仍然跟着第三名。 3、First的用法 功能描述：从DENSE_RANK返回的集合中取出排在最前面的一个值的行（可能多行，因为值可能相等），因