当前位置：文档之家 › oracle分析函数

oracle分析函数

  • 格式：ppt
  • 大小：586.00 KB
  • 文档页数：90

下载文档原格式

  / 90

合集下载

ORACLE_分析函数大全

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分析函数用法详解

Oracle分析函数用法详解

Oracle分析函数Oracle分析函数实际上操作对象是查询出的数据集，也就是说不需二次查询数据库，实际上就是oracle实现了一些我们自身需要编码实现的统计功能，对于简化开发工作量有很大的帮助，特别在开发第三方报表软件时是非常有帮助的。

Oracle从8.1.6开始提供分析函数。

一、基本语法oracle分析函数的语法：function_name(arg1,arg2,...)over(<partition-clause> <order-by-clause ><windowing clause>)说明：1.partition-clause 数据记录集分组2.order-by-clause 数据记录集排序3.windowing clause 功能非常强大、比较复杂，定义分析函数在操作行的集合。

有三种开窗方式: range、row、specifying。

二、常用分析函数1. avg(distinct|all expression) 计算组内平均值，distinct 可去除组内重复数据select deptno,empno,sal,avg(sal) over (partition by deptno) avg_sal from t;DEPTNO EMPNO SAL AVG_SAL---------- ---------- ---------- ----------10 7782 2450 2916.666677839 5000 2916.666677934 1300 2916.6666720 7566 2975 21757902 3000 21757876 1100 21757369 800 21757788 3000 217530 7521 1250 1566.666677844 1500 1566.666677499 1600 1566.666677900 950 1566.666677698 2850 1566.666677654 1250 1566.666672.count(<distinct><*><expression>) 对组内数据进行计数3．rank() 和dense_rank()dense_rank()根据 order by 子句表达式的值，从查询返回的每一行，计算和其他行的相对位置，序号从 1 开始，有重复值时序号不跳号。

ORACLE中的ROW_NUMBEROVER分析函数的用法

ORACLE中的ROW_NUMBEROVER分析函数的用法

ORACLE中的ROW_NUMBEROVER分析函数的用法ROW_NUMBER(OVER(是ORACLE数据库中的一个分析函数，用来为结果集中的每一行分配一个唯一的序号。

ROW_NUMBER(OVER(的语法是：ROW_NUMBER( OVER ( [ PARTITION BY expr1 [, expr2, ...] ]ORDER BY clause )其中，PARTITIONBY子句可选，用来指定分区依据的列或表达式；ORDERBY子句用来指定排序的列或表达式。

ROW_NUMBER(OVER(常用在查询结果需要进行分页或者进行排序后获取前几行的场景中。

以下是ROW_NUMBER(OVER(的用法示例：示例1：查询员工表中每个部门的员工数，并按照员工数降序排序。

SELECT department_id, count(*) as employee_count,ROW_NUMBER( OVER (ORDER BY count(*) DESC) as rankFROM employeesGROUP BY department_idORDER BY count(*) DESC;在这个示例中，ROW_NUMBER(OVER(函数根据部门中的员工数进行降序排序，并为每个部门分配一个唯一的序号。

示例2：查询员工表中每个部门的员工数，并按照员工数降序排序，并且只返回前三名。

SELECT department_id, count(*) as employee_count,ROW_NUMBER( OVER (ORDER BY count(*) DESC) as rankFROM employeesGROUP BY department_idWHERE rank <= 3ORDER BY count(*) DESC;在这个示例中，ROW_NUMBER(OVER(函数的结果用于限制查询结果只返回前三名。

示例3：查询员工表中每个部门的员工信息，并按照部门和薪水进行排序。

oracle之分析函数

oracle之分析函数

Oracle 分析函数的使用Oracle 分析函数使用介绍分析函数是oracle816引入的一个全新的概念,为我们分析数据提供了一种简单高效的处理方式.在分析函数出现以前,我们必须使用自联查询,子查询或者内联视图,甚至复杂的存储过程实现的语句,现在只要一条简单的sql语句就可以实现了,而且在执行效率方面也有相当大的提高.下面我将针对分析函数做一些具体的说明.今天我主要给大家介绍一下以下几个函数的使用方法1. 自动汇总函数rollup,cube,2. rank 函数, rank,dense_rank,row_number3. lag,lead函数4. sum,avg,的移动增加,移动平均数5. ratio_to_report报表处理函数6. first,last取基数的分析函数基础数据1. 使用rollup函数的介绍select area_code,sum(local_fare) local_farefrom ttgroup by area_codeunion allselect'合计' area_code,sum(local_fare) local_fare from tt;1 0 UNION-ALL2 1 SORT (GROUP BY) (Cost=5 Card=1309 Bytes=24871)3 2 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1309 Bytes=24871)4 1 SORT (AGGREGATE)5 4 TABLE ACCESS (FULL) OF 'T' (Cost=2 Card=1309 Bytes=17017)Statistics----------------------------------------------------------0 recursive calls0 db block gets6 consistent gets0 physical reads0 redo size561 bytes sent via SQL*Net to client503 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client1 sorts (memory)0 sorts (disk)6 rows processed下面是使用分析函数rollup得出的汇总数据的例子SQL> select nvl(area_code,'合计') area_code,sum(local_fare) local_fare2 from tt3 group by rollup(area_code);4 /AREA_CODE LOCAL_FARE---------- --------------5761 54225413.045762 52039619.605763 69186545.025764 53156768.465765 104548719.19合计333157065.31从上面的结果中我们很容易发现,每个统计数据所对应的行都会出现null,我们如何来区分到底是根据那个字段做的汇总呢,这时候,oracle的grouping函数就粉墨登场了.如果当前的汇总记录是利用该字段得出的,grouping函数就会返回1,否则返回01 select decode(grouping(area_code),1,'all area',to_char(area_code))area_code,2 decode(grouping(bill_month),1,'all month',bill_month) bill_month,3 sum(local_fare) local_fare4 from t5 group by cube(area_code,bill_month)6* order by area_code,bill_month nulls last07:07:29 SQL> /AREA_CODE BILL_MONTH LOCAL_FARE---------- --------------- --------------5761 200405 13060.435761 200406 13318.935761 200407 13710.275761 200408 14135.785761 all month 54225.415762 200405 12643.795762 200406 12795.065762 200407 13224.305762 200408 13376.475762 all month 52039.625763 200405 16649.785763 200406 17120.525763 200407 17487.495763 200408 17928.765763 all month 69186.545764 200405 12487.795764 200406 13295.195764 200407 13444.095764 200408 13929.695764 all month 53156.775765 200405 25057.745765 200406 26058.465765 200407 26301.885765 200408 27130.645765 all month 104548.72all area 200405 79899.532. rank函数的介绍问题2.我想查出这几个月份中各个地区的总话费的排名.为了将rank,dense_rank,row_number函数的差别显示出来,我们对已有的基础数据做一些修改,将5763的数据改成与5761的数据相同.1 update t t1 set local_fare = (2 select local_fare from t t23 where t1.bill_month = t2.bill_month4 and _type = _type5 and t2.area_code = '5761'6) where area_code = '5763'SQL> /update tt t1 set local_fare =(select local_fare from tt t2where t1.bill_month = t2.bill_monthand _type = _typeand t2.area_code ='5761')where area_code ='5763';8 rows updated.Elapsed: 00:00:00.01我们先使用rank函数来计算各个地区的话费排名.SQL> select area_code,sum(local_fare) local_fare,2 rank() over (order by sum(local_fare) desc) fare_rank3 from t4 group by area_codeselect area_code,sum(local_fare) local_fare,rank() over (order by sum(local_fare)desc) fare_rankfrom ttgroup by area_codeAREA_CODE LOCAL_FARE FARE_RANK---------- -------------- ----------5765 104548.72 15761 54225.41 25763 54225.41 25764 53156.77 45762 52039.62 5Elapsed: 00:00:00.01我们可以看到红色标注的地方出现了,跳位,排名3没有出现下面我们再看看dense_rank查询的结果.select area_code,sum(local_fare) local_fare,dense_rank() over (order by sum(local_fare)desc) fare_rankfrom ttgroup by area_code/AREA_CODE LOCAL_FARE FARE_RANK---------- -------------- ----------5765 104548.72 15761 54225.41 25763 54225.41 25764 53156.77 3 这是这里出现了第三名5762 52039.62 4Elapsed: 00:00:00.00在这个例子中,出现了一个第三名,这就是rank和dense_rank的差别,rank如果出现两个相同的数据,那么后面的数据就会直接跳过这个排名,而dense_rank则不会,差别更大的是,row_number哪怕是两个数据完全相同,排名也会不一样,这个特性在我们想找出对应没个条件的唯一记录的时候又很大用处select area_code,sum(local_fare) local_fare,row_number() over (order by sum(local_fare)desc) fare_rankfrom ttgroup by area_code/AREA_CODE LOCAL_FARE FARE_RANK---------- -------------- ----------5765 104548.72 15761 54225.41 25763 54225.41 35764 53156.77 45762 52039.62 5在row_nubmer函数中,我们发现,哪怕sum(local_fare)完全相同,我们还是得到了不一样排名,我们可以利用这个特性剔除数据库中的重复记录.这里的几个例子是为了说明这三个函数的基本用法。

oracle常用的分析函数

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）

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之分析函数

Oracle之分析函数⼀、分析函数 1、分析函数 分析函数是Oracle专门⽤于解决复杂报表统计需求的功能强⼤的函数，它可以在数据中进⾏分组然后计算基于组的某种统计值，并且每⼀组的每⼀⾏都可以返回⼀个统计值。

2、分析函数和聚合函数的区别 普通的聚合函数⽤group by分组，每个分组返回⼀个统计值，⽽分析函数采⽤partition by分组，并且每组每⾏都可以返回⼀个统计值。

3、分析函数的形式 分析函数带有⼀个开窗函数over()，包含分析⼦句。

分析⼦句⼜由下⾯三部分组成： partition by ：分组⼦句，表⽰分析函数的计算范围，不同的组互不相⼲； ORDER BY：排序⼦句，表⽰分组后，组内的排序⽅式； ROWS/RANGE：窗⼝⼦句，是在分组(PARTITION BY)后，组内的⼦分组（也称窗⼝），此时分析函数的计算范围窗⼝，⽽不是PARTITON。

窗⼝有两种，ROWS和RANGE； 使⽤形式如下：OVER(PARTITION BY xxx PORDER BY yyy ROWS BETWEEN rowStart AND rowEnd) 注：窗⼝⼦句在这⾥我只说rows⽅式的窗⼝,range⽅式和滑动窗⼝也不提。

⼆、OVER() 函数 1、sql 查询语句的 order by 和 OVER() 函数中的 ORDER BY 的执⾏顺序 分析函数是在整个sql查询结束后(sql语句中的order by的执⾏⽐较特殊)再进⾏的操作, 也就是说sql语句中的order by也会影响分析函数的执⾏结果： [1] 两者⼀致：如果sql语句中的order by满⾜分析函数分析时要求的排序，那么sql语句中的排序将先执⾏,分析函数在分析时就不必再排序； [2] 两者不⼀致：如果sql语句中的order by不满⾜分析函数分析时要求的排序，那么sql语句中的排序将最后在分析函数分析结束后执⾏排序。

2、分析函数中的分组/排序/窗⼝分析函数包含三个分析⼦句：分组(partition by)，排序(order by)，窗⼝(rows/range)窗⼝就是分析函数分析时要处理的数据范围，就拿sum来说,它是sum窗⼝中的记录⽽不是整个分组中的记录，因此我们在想得到某个栏位的累计值时，我们需要把窗⼝指定到该分组中的第⼀⾏数据到当前⾏, 如果你指定该窗⼝从该分组中的第⼀⾏到最后⼀⾏,那么该组中的每⼀个sum值都会⼀样，即整个组的总和。

Oracle11gr2分析函数新特性简介（二）分析函数LISTAGG

Oracle 11g r2分析函数新特性简介（二）分析函数LISTAGG在11gr2中，Oracle分析函数的功能进一步增强。

这篇介绍新增的分析函数LISTAGG。

11gr2还新增了一个分析函数LISTAGG，这个函数的功能实现字符串的连接在11gr2中，Oracle终于实现了这个分析函数：SQL> select * from v$version;BANNER--------------------------------------------------------------------------------Oracle Database11gEnterprise Edition Release11.2.0.1.0 - 64bit ProductionPL/SQL Release 11.2.0.1.0 - ProductionCORE 11.2.0.1.0 ProductionTNS for Linux: Version 11.2.0.1.0 - ProductionNLSRTL Version 11.2.0.1.0 - ProductionSQL> create table t (id number, name varchar2(30), type varchar2(20));表已创建。

SQL> insert into t select rownum, object_name, object_type from dba_objects;已创建71968行。

SQL> commit;提交完成。

SQL> select listagg(name, ',') within group (order by id)2 from t3 where rownum < 10;LISTAGG(NAME,',')WITHINGROUP(ORDERBYID)-------------------------------------------------------------------------------------------SYS_C00644,SYS_LOB0000000528C00002$$,KOTTB$,SYS_C00645,SYS_LOB0000000532C00002$$,KOTAD$,SYS_C00646,SYS_L OB0000000536C00002$$,KOTMD$SQL> select type, listagg(name, ',') within group (order by id) name2 from t3 where type in ('DIRECTORY', 'JAVA SOURCE', 'SCHEDULE')4 group by type;TYPE NAME-------------------- ---------------------------------------------------------------------DIRECTORY ORACLE_OCM_CONFIG_DIR,DATA_PUMP_DIR,XMLDIRJAVA SOURCE dbFWTrace,schedFileWatcherJavaSCHEDULE DAILY_PURGE_SCHEDULE,FILE_WATCHER_SCHEDULE,BSLN_MAINTAIN_STATS_SCHEDSQL> select name,2 listagg(name, ',') within group (order by id) over(partition by type) s_name3 from t4 where type in ('DIRECTORY', 'JAVA SOURCE', 'SCHEDULE');NAME S_NAME本文URL地址：/database/Oracle/201410/45457.htm------------------------- -----------------------------------------------------------ORACLE_OCM_CONFIG_DIR ORACLE_OCM_CONFIG_DIR,DATA_PUMP_DIR,XMLDIRDATA_PUMP_DIR ORACLE_OCM_CONFIG_DIR,DATA_PUMP_DIR,XMLDIRXMLDIR ORACLE_OCM_CONFIG_DIR,DATA_PUMP_DIR,XMLDIRdbFWTrace dbFWTrace,schedFileWatcherJavaschedFileWatcherJava dbFWTrace,schedFileWatcherJavaDAILY_PURGE_SCHEDULE DAILY_PURGE_SCHEDULE,FILE_WATCHER_SCHEDULE,BSLN_MAINTAIN_STATS_SCHEDFILE_WATCHER_SCHEDULE DAILY_PURGE_SCHEDULE,FILE_WATCHER_SCHEDULE,BSLN_MAINTAIN_STATS_SCHED BSLN_MAINTAIN_STATS_SCHED DAILY_PURGE_SCHEDULE,FILE_WATCHER_SCHEDULE,BSLN_MAINTAIN_STATS_SCHED 已选择8行。

Oracle分析函数Over（）

Oracle分析函数Over（）阅读⽬录⼀、Over()分析函数1、rank()/dense_rank over(partition by ... order by ...)2、min()/max() over(partition by ...)3、lead()/lag() over(partition by ... order by ...) 取前⾯/后⾯第n⾏记录4、FIRST_VALUE/LAST_VALUE() OVER(PARTITION BY ...) 取⾸尾记录5、ROW_NUMBER() OVER(PARTITION BY.. ORDER BY ..) 排序（应⽤：分页）6、sum/avg/count() over(partition by ..)7、 rows/range between … preceding and … following 上下范围内求值rows between … preceding and … following⼆、其他1、NULLS FIRST/LAST 将空值字段记录放到最前或最后显⽰2、NTILE(n)3、keep(dense_rank first/last)回到顶部⼀、Over()分析函数说明：聚合函数（如sum()、max()等）可以计算基于组的某种聚合值，但是聚合函数对于某个组只能返回⼀⾏记录。

若想对于某组返回多⾏记录，则需要使⽤分析函数。

1、rank()/dense_rank over(partition by ... order by ...)说明：over()在什么条件之上; partition by 按哪个字段划分组； order by 按哪个字段排序；注意： （1）使⽤rank()/dense_rank() 时，必须要带order by否则⾮法 （2）rank()/dense_rank()分级的区别： rank(): 跳跃排序，如果有两个第⼀级时，接下来就是第三级。

Oracle数据库分析函数用法

Oracle数据库分析函数⽤法⽬录1、什么是窗⼝函数？2、窗⼝函数——开窗3、⼀些分析函数的使⽤⽅法4、OVER()参数——分组函数5、OVER()参数——排序函数1、什么是窗⼝函数？窗⼝函数也属于分析函数。

Oracle从8.1.6开始提供窗⼝函数，窗⼝函数⽤于计算基于组的某种聚合值，窗⼝函数指定了分析函数⼯作的数据窗⼝⼤⼩，这个数据窗⼝⼤⼩可能会随着⾏的变化⽽变化。

与聚合函数的不同之处是：对于每个组返回多⾏，⽽聚合函数对于每个组只返回⼀⾏基本语法: ‹分析函数› over (partition by ‹⽤于分组的列名› order by ‹⽤于排序的列名›)。

语法中的‹分析函数›主要由序列函数（rank、dense_rank和row_number等组成）与聚合函数（sum、avg、count、max和min等）作为窗⼝函数组成。

从窗⼝函数组成上看，它是group by 和 order by的功能组合，group by分组汇总后改变了表的⾏数，⼀⾏只有⼀个类别，⽽partiition by则不会减少原表中的⾏数。

恰如窗⼝函数的组成，它同时具有分组和排序的功能，且不减少原表的⾏数。

OVER 关键字表⽰把函数当成窗⼝函数⽽不是聚合函数。

SQL 标准允许将所有聚合函数⽤做窗⼝函数，使⽤ OVER 关键字来区分这两种⽤法。

2、窗⼝函数——开窗OVER 关键字后的括号中经常添加选项⽤以改变进⾏聚合运算的窗⼝范围。

如果 OVER 关键字后的括号中的选项为空，则窗⼝函数会对结果集中的所有⾏进⾏聚合运算。

分析函数 over(partition by 列名 order by 列名 rows between 开始位置 and 结束位置)为什么叫开窗呢？因为在over()括号中的，partition() 函数可以将查询到的数据进⾏单独开⼀个窗⼝处理。

譬如，查询每个班级的学⽣的排名情况，查询每个国家的历年⼈⼝等，诸如此类，都是在查询到的每⼀个班级、每⼀个国家中都开⼀个窗⼝，单独去执⾏命令。

  1. 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
  2. 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
  3. 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。

• grouping_id
• 为什么需要分析函数？
– 直接在SQL中做以下操作是比较难的：
• 计算运行行的总数：逐行的显示一个部门的累计工 资。每行包括前面各行工资的总和。 • 查找一组内的百分数：显示在某些部门中付给个人 的总工资的百分数。将他们的工资与该部门的工资 总和相除 • 查询前/最后N个
• WINDOWING子句
– 用于定义分析函数将在其上操作的行的集合 – 默认的窗口是一个固定的窗口,仅仅在一组的第 一行开始,一直继续到当前行 – 要使用窗口,必须使用ORDER BY子句 – window可以指定是逻辑窗口还是物理窗口
• 数据值的范围(range between) • 与当前行的行偏移量(rows between)
• ORDER BY子句
– 没有ORDER BY时,默认的窗口是全部的分区 – 分析函数中ORDER BY的存在将添加一个默认 的开窗子句：RANGE BETWEEN unbounded preceding AND CURRENT ROW，即第1行到 当前行（根据order by顺序指定） – order by默认是range窗口，对应逻辑窗口 – 在Order by 子句后可以添加nulls last/first，指 定NULLS是出现在开始还是最后。
• WINDOWING子句
– 有的分析函数不能有显式window,如 row_number,rank,dense_rank等
• range是逻辑窗口
– 数值范围
• 指定当前行对应值的范围取值 • 只要行值在范围内，对应列都包含在内
– 日期范围
• 指定时间范围进行统计 • interval '1' day/month/year
– 对于N个参数，有2^N次的分组
• 范例:按月份，地区统计收入
select earnmonth,area,sum(personincome) from earnings group by cube(earnmonth,area)
• grouping sets
– 对参数中的每个参数做分组
• group by grouping sets(a,b,c)，则对(a)(b)(c)进行 group by • group by grouping sets((a,b),c)，则对(a,b)(c)进行 group by
• • • • • • 聚合分析函数 排名分析函数 行比较分析函数 统计分析函数 行连接分析函数 其他分析函数
分类
排名分析函数
内容
ROW_NUMBER、RANK、 DENSE_RANK、FIRST、 LAST、 LAST_VALUE,FIRST_VALUE AVG、COUNT、 MAX,MIN,SUM LEAD、LAG RATIO_TO_REPORT LISTAGG CORR、CORVAR_POP、 CORVAR_SAMP、 CUME_DIST、NTH_VALUE、 NTILE、PERCENT_RANK、 PERCENTILE_CONT、 PERCENTILE_DISC、REGR_ (Linear Regression) Functions、 STDDEV、STDDEV_POP、 STDDEV_SAMP 、VAR_POP,VAR_SAMP、 VARIANCE
• grouping
– 作用：
• 用rollup和cube函数统计的结果中，被统计字段会 产生NULL，用grouping函数来确定，该记录是由 哪个字段得出来的 grouping(字段名)，如果是用该字段统计的返回1， 反之返回0 该列为空时是1，不为空时是0
– 语法：
• •
• 范例
select decode(grouping(earnmonth),1,'所有月份',earnmonth) 月份, decode(grouping(area),1,'所有地区',area) 地区, sum(personincome) 总金额 from earnings group by rollup(earnmonth,area)
• 使用 GROUP BY子句
– 在SELECT语句中，没有使用分组函数的列必 须在GROUP By子句中.
SQL> SELECT deptno, AVG(sal) 2 FROM emp 3 GROUP BY deptno;
– GROUP BY后面的列可以不出现在 SELECT链 中.
SQL> SELECT AVG(sal) 2 FROM emp 3 GROUP BY deptno;
说明
FIRST,LAST不应该算 做函数，它主要与聚合 函数联合使用 强
使用频率
聚合分析函数 行比较分析函数 统计分析函数 行连接分析函数 其他分析函数
强 解决当前行与前后行之 间的关系 解决报表占比问题 11g新特性 中 中 中 弱• 分析子句源自• PARTITION子句
– 按照表达式分区(就是分组)，将相同的行聚合 到一起成为一组。 – 如果省略了分区子句,则全部的结果集被看作是 一个单一的组
select area,sname,ratio ( select area,sname, ratio_to_report(sum(personincome)) over(partition by area) ratio from earnings group by area,sname ) where ratio > 0.2;
• 例如：order by times range between 1 preceing and 3 following
– 当times=8时，是sum为8-1<=times<=8+3 的和，即 sum=7+7+7+8+11+11+11=5(取times为7,8,11); – 当times=4时，是sum为4-1<=times<=4+3的和，即 sum=4+6+7+7+7（取times为4,6,7）； – 当times=21时，是sum为21-1<=times<=21+3 的和， 即sum=21（取times为21）;
• 使用 GROUP BY子句
– 在group by后面使用多个字段
SQL> SELECT deptno, job, sum(sal) 2 FROM emp 3 GROUP BY deptno, job;
• 注意：
– 不能在 WHERE子句中对列做出限定. – 使用 HAVING 子句来限定分组.
• 分组函数的嵌套使用
SQL> SELECT max(avg(sal)) 2 FROM emp 3 GROUP BY deptno;
SELECT owner,object_type FROM demo2 WHERE brbh='xxx' AND created = (SELECT MAX(created) FROM demo2 WHERE brbh='xxx')
– Oracle 从 8.1.6 开始提供分析函数
• 按区域，每个员工工资总额占区域内工资 总额20%以上的员工，以及他们在各个区 域内的排名
– select ... group by area; – select ... group by area,sname; – 两表关联 – 排名？
• select area,sname,per / ptm ratio from • ( • select area,sname, sum(personincome) per,sum(sum(personincome)) over(partition by area) ptm from earnings group by area,sname • ) where per / ptm > 0.2;
• 范例：按月份，地区统计收入
select earnmonth,area,sum(personincome) from earnings group by rollup(earnmonth,area)
• cube
– cube(a,b,c)的group by顺序
• [a,b,c],[a,b],[a,c],[a],[b,c],[b],[c] • 全表group by
• 注意事项
– 不管排序键是否是唯一，都能保证诸如聚合分 析函数值的唯一 – 反应的是行与行之间的逻辑关系（当前行的排 序键的value加或减逻辑偏移量得到当前行对应 的逻辑窗口的范围） – 升序range窗口preceding含义是比当前行小xx 值，following含义是比当前行大xx值，降序 range窗口preceding含义是比当前行大xx值， following含义是比当前行小xx值。
分析函数
• 语法结构：
– 分析函数(参数,参数...) OVER (<PARTITION BY子句> <ORDER BY子句> <WINDOWING 子句>)
• 分析函数 • 分析子句
– PARTITION子句 – ORDER BY子句 – WINDOWING子句
• 分析函数
– 按照各种分析函数使用频率以及业务特点：
• 列分组函数
– group by – rollup – cube – grouping sets – grouping
• group by
– 按月份，统计每个地区的总收入 – 范例 select earnmonth,area,sum(personincome) from earnings group by earnmonth,area
• COUNT(expr) 返回expr值非空的行的数.
SQL> SELECT 2 FROM 3 WHERE COUNT(comm) emp deptno = 30;