oracle rownum rowid rank()
ORACLE_分析函数大全
ORACLE_分析函数大全Oracle分析函数是一种高级SQL函数，它可以在查询中实现一系列复杂的分析操作。
这些函数可以帮助我们在数据库中执行各种数据分析和报表生成任务。
本文将介绍Oracle数据库中的一些常用分析函数。
1.ROW_NUMBER函数：该函数为查询结果中的每一行分配一个唯一的数字。
可以用它对结果进行排序或分组。
例如，可以使用ROW_NUMBER函数在结果集中为每个员工计算唯一的编号。
2.RANK和DENSE_RANK函数：这两个函数用于计算结果集中每个行的排名。
RANK函数返回相同值的行具有相同的排名，并且下一个排名值将被跳过。
DENSE_RANK函数类似，但是下一个排名值不会被跳过。
G和LEAD函数：LAG函数返回结果集中指定列的前一个（上一个）行的值，而LEAD函数返回后一个（下一个）行的值。
这些函数通常用于计算增长率或发现趋势。
4.FIRST和LAST函数：这两个函数用于返回结果集中分组的第一个和最后一个行的值。
可以与GROUPBY子句一起使用。
5.CUME_DIST函数：该函数用于计算给定值的累积分布。
它返回值的累积分布在结果集中的位置（百分比）。
6.PERCENT_RANK函数：该函数用于计算结果集中每个行的百分位数排名。
它返回值的百分位数排名（0到1之间的小数）。
7. NTILE函数：该函数用于将结果集分成指定数量的桶（Bucket），并为每个行分配一个桶号。
通常用于将数据分组为更小的块。
8.LISTAGG函数：该函数将指定列的值连接成一个字符串，并使用指定的分隔符分隔每个值。
可以用它将多个值合并在一起形成一个字符串。
9.AVG、SUM、COUNT和MAX/MIN函数：这些是常见的聚合函数，可以在分析函数中使用。
它们用于计算结果集中的平均值、总和、计数和最大/最小值。
以上只是Oracle数据库中的一些常用分析函数。
还有其他一些分析函数，如PERCENTILE_CONT、PERCENTILE_DISC等可以用于更高级的分析计算。
Oracle查询 rownum和rowid的区别
Oracle查询rownum和rowid的区别在Oracle中，有一个很有趣的东西，那就是rownum。
当你从某个表中查询数据的时候，返回的结果集中都会带有rownum这个字段，而且有时候也可以使用rownum进行一些条件查询。
在查询中，我们可以注意到，类似于“select xx from table where rownum < n”(n>1)这样的查询是有正确含义的，而“select xx from table where rownum = n”这样的查询只在n=1的时候成立，“select xx from table where rownum > n”(n>1)这样的查询只能得到一个空集。
另外“select xx from table where rownum > 0”这个查询会返回所有的记录。
这是为什么呢?原因就在于Oracle对rownum 的处理上，rownum是在得到结果集的时候产生的，用于标记结果集中结果顺序的一个字段，这个字段被称为“伪数列”，也就是事实上不存在的一个数列。
它的特点是按顺序标记，而且是逐次递加的，换句话说就是只有有rownum=1的记录，才可能有rownum=2的记录。
让我们回头来分析一下在where中使用rownum作为查询条件的情况。
在rownum取=1，或者rownum <= n (n>1)的时候，没有问题。
那么为什么当条件为rownum = n或者rownum >= n时明明有数据却只能得到一个空集呢?假设我们的查询条件为rownum = 2，那么在查询出的第一条记录的时候，oracle标记此条记录rownum为1，结果发现和rownum=2的条件不符，于是结果集为空。
写到这里，我忽然有一个有趣的想法：假如有一条查询语句为select xx,yy from table where zz > 20 and rownum < 10，那么在执行的时候，是先按照zz>20的条件查询出一个结果集，然后按照rownum取出前10条返回?还是在按照zz>20的条件先查询，然后有一个记录就标记一个rownum，到rownum<10的时候就停止查询?我觉得应该是后者，也就是在执行语句的时候，不是做full scan，而是取够数据就停止查询。
Oracle中row_number（）、rank（）、dense_rank（）的区别
Oracle中row_number（）、rank（）、dense_rank（）的区别row_number的⽤途⾮常⼴泛，排序最好⽤它，它会为查询出来的每⼀⾏记录⽣成⼀个序号，依次排序且不会重复，注意使⽤row_number函数时必须要⽤over⼦句选择对某⼀列进⾏排序才能⽣成序号。
rank函数⽤于返回结果集的分区内每⾏的排名，⾏的排名是相关⾏之前的排名数加⼀。
简单来说rank函数就是对查询出来的记录进⾏排名，与row_number函数不同的是，rank函数考虑到了over⼦句中排序字段值相同的情况，如果使⽤rank函数来⽣成序号，over⼦句中排序字段值相同的序号是⼀样的，后⾯字段值不相同的序号将跳过相同的排名号排下⼀个，也就是相关⾏之前的排名数加⼀，可以理解为根据当前的记录数⽣成序号，后⾯的记录依此类推。
dense_rank函数的功能与rank函数类似，dense_rank函数在⽣成序号时是连续的，⽽rank函数⽣成的序号有可能不连续。
dense_rank函数出现相同排名时，将不跳过相同排名号，rank值紧接上⼀次的rank值。
在各个分组内，rank()是跳跃排序，有两个第⼀名时接下来就是第三名，dense_rank()是连续排序，有两个第⼀名时仍然跟着第⼆名。
借助实例能更直观地理解：假设现在有⼀张学⽣表student，学⽣表中有姓名、分数、课程编号。
select*from student;现在需要按照课程对学⽣的成绩进⾏排序：--row_number() 顺序排序select name,course,row_number() over(partition by course order by score desc) rank from student;--rank() 跳跃排序，如果有两个第⼀级别时，接下来是第三级别select name,course,rank() over(partition by course order by score desc) rank from student;--dense_rank() 连续排序，如果有两个第⼀级别时，接下来是第⼆级别select name,course,dense_rank() over(partition by course order by score desc) rank from student;取得每门课程的第⼀名：--每门课程第⼀名只取⼀个：select*from (select name,course,row_number() over(partition by course order by score desc) rank from student) where rank=1;--每门课程第⼀名取所有：select*from (select name,course,dense_rank() over(partition by course order by score desc) rank from student) where rank=1;--每门课程第⼀名取所有：select*from (select name,course,rank() over(partition by course order by score desc) rank from student) where rank=1; 附：每门课程第⼀名取所有的其他⽅法（使⽤group by ⽽不是partition by）：select s.*from student s inner join(select course,max(score) as score from student group by course) c on s.course=c.course and s.score=c.score; --或者使⽤using关键字简化连接select*from student s inner join(select course,max(score) as score from student group by course) c using(course,score);关于Parttion by： Parttion by关键字是Oracle中分析性函数的⼀部分，⽤于给结果集进⾏分区。
oracle之伪列（ruwnum、ruwid）
oracle之伪列（ruwnum、ruwid）伪列：•伪列是在oracle中的一个虚拟的列。
•列的数据是由ORACLE进行维护和管理的，用户不能对这个列修改，只能查看。
•所有的伪列要得到值必须要显式的指定。
最常用的两个伪列：rownum和rowid。
•ROWNUM:•rownum（行号）：是在查询操作时由ORACLE为每一行记录自动生成的一个编号。
•每一次查询ROWNUM都会重新生成。
（查询的结果中Oracle 给你增加的一个编号，根据结果来重新生成）•rownum永远按照默认的顺序生成。
（不受order by的影响）•rownum只能使用< <=，不能使用> >=符号，原因是：Oracle 是基于行的数据库，行号永远是从1开始，即必须有第一行，才有第二行。
行号的产生：1.ROWNUM是由数据库自己产生的。
2.ROWNUM查询的时候自动产生的。
行号的排序：示例：--需求：查询出所有员工信息，按部门号正序排列，并且显示默认的行号列信息。
SELECT ROWNUM,t.* FROM emp t ORDER BY deptno;--order by 的原理：将查询结果（此时行号已经有了，已经和每一行数据绑定了）进行排序。
- --order by是查询语句出来的结果之后再排序的，rownum是在查询出来结果的时候产生。
order by不会影响到行号--先排序，再查询SELECT ROWNUM,t.* FROM(SELECT * FROM emp ORDER BY deptno) t结论：•order by排序，不会影响到rownum的顺序。
rownum永远按照默认的顺序生成。
•所谓的“默认的顺序”，是指系统按照记录插入时的顺序（其实是rowid）。
利用行号进行数据分页：mysql如何分页？select * from table limit m,n其中m是指记录开始的index，从0开始，表示第一条记录n是指从第m+1条开始，取n条。
oracle常用的分析函数
oracle常⽤的分析函数常⽤的分析函数如下所列:row_number() over(partition by ... order by ...)rank() over(partition by ... order by ...)dense_rank() over(partition by ... order by ...)count() over(partition by ... order by ...)max() over(partition by ... order by ...)min() over(partition by ... order by ...)sum() over(partition by ... order by ...)avg() over(partition by ... order by ...)first_value() over(partition by ... order by ...)last_value() over(partition by ... order by ...)lag() over(partition by ... order by ...)lead() over(partition by ... order by ...)⼀、Oracle分析函数简介：在⽇常的⽣产环境中，我们接触得⽐较多的是OLTP系统(即Online Transaction Process)，这些系统的特点是具备实时要求，或者⾄少说对响应的时间多长有⼀定的要求；其次这些系统的业务逻辑⼀般⽐较复杂，可能需要经过多次的运算。
⽐如我们经常接触到的电⼦商城。
在这些系统之外，还有⼀种称之为OLAP的系统(即Online Aanalyse Process)，这些系统⼀般⽤于系统决策使⽤。
通常和数据仓库、数据分析、数据挖掘等概念联系在⼀起。
这些系统的特点是数据量⼤，对实时响应的要求不⾼或者根本不关注这⽅⾯的要求，以查询、统计操作为主。
Oracle分析函数-排序排列（rank、dense_rank、row_number、ntile）
Oracle分析函数-排序排列（rank、dense_rank、row_number、ntile）(1)rank函数返回⼀个唯⼀的值，除⾮遇到相同的数据时，此时所有相同数据的排名是⼀样的，同时会在最后⼀条相同记录和下⼀条不同记录的排名之间空出排名。
(2)dense_rank函数返回⼀个唯⼀的值，除⾮当碰到相同数据时，此时所有相同数据的排名都是⼀样的。
(3)row_number函数返回⼀个唯⼀的值，当碰到相同数据时，排名按照记录集中记录的顺序依次递增。
(4)ntile是要把查询得到的结果平均分为⼏组，如果不平均则分给第⼀组。
例如：create table s_score( s_id number(6),score number(4,2));insert into s_score values(001,98);insert into s_score values(002,66.5);insert into s_score values(003,99);insert into s_score values(004,98);insert into s_score values(005,98);insert into s_score values(006,80);selects_id,score,rank() over(order by score desc) rank --按照成绩排名，纯排名,dense_rank() over(order by score desc) dense_rank --按照成绩排名，相同成绩排名⼀致,row_number() over(order by score desc) row_number --按照成绩依次排名,ntile(3) over (order by score desc) group_s --按照分数划分成绩梯队from s_score;排名/排序的时候，有时候，我们会想到利⽤伪列row_num，利⽤row_num确实可以解决某些场景下的问题(但是相对也⽐较复杂)，⽽且有些场景下的问题却很难解决。
oracle中的rownum详解
oracle中的rownum详解1. 概述rownum是Oracle引⼊的虚列。
在物理上这个虚列并不存在，只是在查询时才构造出来。
伪列通常是⾃由分配的，⽤户⽆法执⾏修改等操作。
2. 特点关于rownum有以下主要特点：1）rownum不属于任何表。
2）rownum存在的前提，先有结果表。
3）rownum总是从1开始。
4）rownum⼀般只和<(<=)⼀起⽤。
5）使⽤rownum进⾏分页查询需要把rownum转化为实列，并针对rownum查询。
2. 机制原理rownum的⽤法看似奇怪，其实如果明⽩其机制原理就会很简单。
⾸先我们来做个简单的实验：从dual表中取出所有的字段，并取出rownum。
如果我们采⽤如下的写法：t.rownum这样运⾏就会报01747错：因为实际上，dual表就不存在rownum这个字段，所以我们⽆法使⽤t.rownum的格式。
正确的写法，应该是：所以，rownum是⼀个虚列，不属于任何表。
那么这虚列是怎么来的。
我们在做个简单的实验，便于理解：如下，我们有⼀个简单的表：test_ljb，共有⼗条记录。
我们加上rownum。
结果如下，很好理解，选出⼗条记录，rownum从1到10我们加上⼀个salary的筛选条件：结果如下：选出三条记录，rownum从1到3需要注意的是，第⼆个结果表的rownum对应的employee和第⼀张并不对应。
如：在第⼀张表rownum为1时，对应的时Arvin，⽽第⼆张对应的是Oracle。
原因如下：因为rownum是对结果集加的⼀个伪列，即先查到结果集之后再加上去的⼀个列。
简单的说，rownum是对符合条件结果的序列号。
它总是从1开始排起的，所以选出的结果不可能跳过1，⽽有其他⼤于1的值。
或者说，rownum是⼀个动态的，根据新的结果集实时变化的。
⽐如，如下语句：select t.*, rownum from test_ljb t where rownum >1; --⼤于2、3或者其他任何⼤于1的值，结果相同。
oracle rowid排序规则
一、什么是Oracle RowidOracle中的Rowid是一种用于唯一标识数据库表中行的物理位置区域。
每一行都有一个唯一的Rowid，可以通过该Rowid来直接访问该行，而不需要进行索引查找。
二、Rowid的结构Rowid是Oracle中的一个伪列，它由以下几部分组成：1. 数据对象号（Data Object Number）：每个表在数据库中都有一个唯一的数据对象号，它由数据文件号和数据块号组成。
2. 行号（Row Number）：每条记录在数据块中的相对位置。
3. 数据文件号（Data File Number）：数据库中的每个数据文件都有唯一的编号。
4. 数据块号（Data Block Number）：数据文件中的每个数据块都有唯一的编号。
三、Rowid的排序规则Rowid可以作为一种排序规则，可以通过Rowid来对表中的数据进行排序。
在Oracle中，Rowid有三种不同的格式：Extended Rowid、Urowid和Restricted Rowid。
每种格式都有对应的排序规则。
1. Extended Rowid排序规则：Extended Rowid是在大表中使用的Rowid格式，它的排序规则是按照数据对象号、数据块号和行号的顺序进行排序。
这种格式的Rowid可以唯一标识表中的每一条记录。
2. Urowid排序规则：Urowid是在索引中使用的Rowid格式，它的排序规则是按照数据对象号、数据文件号、数据块号和行号的顺序进行排序。
这种格式的Rowid可以唯一标识索引中的每一条记录。
3. Restricted Rowid排序规则：Restricted Rowid是在分区表中使用的Rowid格式，它的排序规则是按照数据对象号、数据文件号、数据块号、行号和分区号的顺序进行排序。
这种格式的Rowid可以唯一标识分区表中的每一条记录。
四、Rowid排序的应用场景Rowid排序可以用于优化查询性能。
oracle SQL中rowid与rownum的使用
1.ROWNUM的使用——TOP-N分析使用SELECT语句返回的结果集，若希望按特定条件查询前N条记录，可以使用伪列ROWNUM。
ROWNUM是对结果集加的一个伪列，即先查到结果集之后再加上去的一个列(强调：先要有结果集)。
简单的说ROWNUM是符合条件结果的序列号。
它总是从1开始排起的。
使用ROWNUM时，只能使用<、<=、!=符号。
举例：student(学生)表，表结构为：ID char(6)--学号name V ARCHAR2(10)--姓名--建表create table student (ID char(6), name V ARCHAR2(10));--添加测试记录insert into student values('200001','张一');insert into student values('200002','王二');insert into student values('200003','李三');insert into student values('200004','赵四');commit;--测试SQL> select * from student;ID NAME------ ------------------------200001 张一200002 王二200003 李三200004 赵四⑴rownum 对于等于某值的查询条件如果希望找到学生表中第一条学生的信息，可以使用rownum=1作为条件。
但是想找到学生表中第二条学生的信息，使用rownum=2结果查不到数据。
因为rownum都是从1开始，但是1以上的自然数在rownum做等于判断时认为都是false条件，所以无法查到rownum = n（n>1的自然数）。
oracle中的rownum和rowid详解
综上几种情况，可能有时候需要查询rownum在某区间的数据，从上可以看出rownum对小于某值的查询条件是人为true的，rownum对于大于某值的查询条件直接认为是false的，但是可以间接的
让它转为认为是true的。那就必须使用子查询。例如要查询rownum在第二行到第三行之间的数据，包括第二行和第三行数据，那么我们只能写以下语句，先让它返回小于等于三的记录行，然
3 200003 22 CCC
4 200004 22 DDD
5 200005 22 EEE
6 200006 22 AAA
SQL>select rownum ,id,age,name from loaddata order by name;
minus
select rownum,id,age,name from loaddata where rownum < 2
ROWNUM ID AGE NAME
------- ------ --- ------
2 200002 22 BBB
3 200003 22 CCC
ROWNUM ID AGE NAME
------- ------ --- ------
1 200001 22 AAA
2 200006 22 AAA
话，无法知道rownum是子查询的列还是主查询的列。
SQL>select rownum,id,age,name from(select rownum no ,id,age,name from loaddata) where no > 2;
ROWNUM ID AGE NAME
ROWNUM ID AGE NAME
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
首先来说一下rownum与rowid含义：
顾名思义rownum就是行数/行号，而rowid就是编码/编号/唯一识别号，所以他是类似“AAAR8gAAEAAAAErAAK”的编号，注意他是没有先后顺序的，也就是说他和数据入库时间没有任何关系，打个比方：他就像磁盘、内存存储数据用的是16进制的地址一样。
他们都是伪列，可以理解成表中的一个列只是他们并不是你创建的。
同样是伪列区别是什么呢？
rowid是你录入数据时有数据库自动为这条记录添加的唯一的18位编号是一个物理编号用于找到这条记录（顺便说一句这也是为什么数据优调的时候强调尽量使用rowid的原因），他是不会随着查询而改变的除非在表发生移动(比如表空间变化，数据导入/导出以后)，才会发生变化。
rownum是根据sql查询后得到的结果自动加上去的，但是他却不受到sql中order by排序的影响，因为他和rowid的顺序一样是系统按照记录插入时的顺序给记录排的号（顺序的、无跳跃）。
但是如果你想让rownum和order by一样的顺序那么可以使用子查询，形如：select rownum,t.* from (select * from 表空间名order by 字段名) t 这样的话rownum就是根据该字段进行排序的编号了，为什么会这样呢，本人理解：rownum是根据表记录输出的行号，与筛选语句、排序语句都无关所以当用子查询时等于生成了一个表于是就按照这张表从1开始排序了。
同样，也可以用下面要提得到的分析函数中的row_number() over(order by 需要排序的字段名)。
值得一提的是MSSQL是没有rownum和rowid的。
下面说说分析函数row_number()、rank()、dense_rank()
ROW_NUMBER()：
Row_number函数返回一个唯一的值，当碰到相同数据时，排名按照记录集中记录的顺序依次递增。
row_number()和rownum差不多，功能更强一点（可以在各个分组内从1开时排序），因为row_number()是分析函数而rownum是伪列所以row_number()一定要over而rownum 不能over。
RANK()：
Rank函数返回一个唯一的值，除非遇到相同的数据，此时所有相同数据的排名是一样的，同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。
rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）。
DENSE_RANK()：
Dense_rank函数返回一个唯一的值，除非当碰到相同数据，此时所有相同数据的排名都是一样的。
dense_rank()是连续排序，有两个第二名时仍然跟着第三名。
他和row_number的区别在于row_number是没有重复值的。
row_number()、rank()、dense_rank()这三个分析函数的区别实例
SQL>select region_id, customer_id, sum(customer_sales) total,
2 rank() over(order by sum(customer_sales) desc) rank,
3 dense_rank() over(order by sum(customer_sales) desc) dense_rank,
4 row_number() over(order by sum(customer_sales) desc) row_number
5from user_order
6group by region_id, customer_id;
REGION_ID CUSTOMER_ID TOTAL RANK DENSE_RANK ROW_NUMBER
---------- ----------- ---------- ---------- ---------- ----------
8181253840111111
521224992121212
9231224992121213
9241224992121214
10301216858151315
30 rows selected.
请注意上面的绿色高亮部分，这里生动的演示了3种不同的排名策略：
①对于第一条相同的记录，3种函数的排名都是一样的：12
②当出现第二条相同的记录时，Rank和Dense_rank依然给出同样的排名12；而row_number 则顺延递增为13，依次类推至第三条相同的记录
③当排名进行到下一条不同的记录时，可以看到Rank函数在12和15之间空出了13,14的排名，因为这2个排名实际上已经被第二、三条相同的记录占了。
而Dense_rank则顺序递增。
row_number函数也是顺序递增
比较上面3种不同的策略，我们在选择的时候就要根据客户的需求来定夺了：
①假如客户就只需要指定数目的记录，那么采用row_number是最简单的，但有漏掉的记录的危险
②假如客户需要所有达到排名水平的记录，那么采用rank或dense_rank是不错的选择。
至于选择哪一种则看客户的需要，选择dense_rank或得到最大的记录。