ORACLE分析函数

合集下载

Oracle中分析函数用法小结

Oracle中分析函数用法小结一.分析函数适用场景:○1需要对同样的数据进行不同级别的聚合操作○2需要在表内将多条数据和同一条数据进行多次的比较○3需要在排序完的结果集上进行额外的过滤操作二.分析函数语法:FUNCTION_NAME(<argument>,<argument>...)OVER(<Partition-Clause><Order-by-Clause><Windowing Clause>)例:sum(sal) over (partition by deptno order by ename) new_aliassum就是函数名(sal)是分析函数的参数,每个函数有0~3个参数,参数可以是表达式,例如:sum(sal+comm) over 是一个关键字,用于标识分析函数,否则查询分析器不能区别sum()聚集函数和sum()分析函数partition by deptno 是可选的分区子句,如果不存在任何分区子句,则全部的结果集可看作一个单一的大区order by ename 是可选的order by 子句,有些函数需要它,有些则不需要.依靠已排序数据的那些函数,如:用于访问结果集中前一行和后一行的LAG和LEAD,必须使用,其它函数,如AVG,则不需要.在使用了任何排序的开窗函数时,该子句是强制性的,它指定了在计算分析函数时一组内的数据是如何排序的.1)FUNCTION子句ORACLE提供了26个分析函数,按功能分5类分析函数分类等级(ranking)函数:用于寻找前N种查询开窗(windowing)函数:用于计算不同的累计,如SUM,COUNT,AVG,MIN,MAX等,作用于数据的一个窗口上例:sum(t.sal) over (order by t.deptno,t.ename) running_total,sum(t.sal) over (partition by t.deptno order by t.ename) department_total制表(reporting)函数:与开窗函数同名,作用于一个分区或一组上的所有列例:sum(t.sal) over () running_total2,sum(t.sal) over (partition by t.deptno) department_total2制表函数与开窗函数的关键不同之处在于OVER语句上缺少一个ORDER BY子句!LAG,LEAD函数:这类函数允许在结果集中向前或向后检索值,为了避免数据的自连接,它们是非常有用的.VAR_POP,VAR_SAMP,STDEV_POPE及线性的衰减函数:计算任何未排序分区的统计值2)PARTITION子句按照表达式分区(就是分组),如果省略了分区子句,则全部的结果集被看作是一个单一的组3)ORDER BY子句分析函数中ORDER BY的存在将添加一个默认的开窗子句,这意味着计算中所使用的行的集合是当前分区中当前行和前面所有行,没有ORDER BY时,默认的窗口是全部的分区在Order by 子句后可以添加nulls last,如:order by comm desc nulls last 表示排序时忽略comm列为空的行.4)WINDOWING子句用于定义分析函数将在其上操作的行的集合Windowing子句给出了一个定义变化或固定的数据窗口的方法,分析函数将对这些数据进行操作默认的窗口是一个固定的窗口,仅仅在一组的第一行开始,一直继续到当前行,要使用窗口,必须使用ORDER BY子句根据2个标准可以建立窗口:数据值的范围(RANGES)或与当前行的行偏移量.5)Rang窗口Range 5 preceding:将产生一个滑动窗口,他在组中拥有当前行以前5行的集合ANGE窗口仅对NUMBERS和DATES起作用,因为不可能从VARCHAR2中增加或减去N个单元另外的限制是ORDER BY中只能有一列,因而范围实际上是一维的,不能在N维空间中例:avg(t.sal) over(order by t.hiredate asc range 100 preceding) 统计前100天平均工资6)Row窗口利用ROW分区,就没有RANGE分区那样的限制了,数据可以是任何类型,且ORDER BY 可以包括很多列7)Specifying窗口UNBOUNDED PRECEDING:这个窗口从当前分区的每一行开始,并结束于正在处理的当前行CURRENT ROW:该窗口从当前行开始(并结束)Numeric Expression PRECEDING:对该窗口从当前行之前的数字表达式(Numeric Expression)的行开始,对RANGE来说,从行序值小于数字表达式的当前行的值开始. Numeric Expression FOLLOWING:该窗口在当前行Numeric Expression行之后的行终止(或开始),且从行序值大于当前行Numeric Expression行的范围开始(或终止)range between 100 preceding and 100 following:当前行100前, 当前行100后注意:分析函数允许你对一个数据集进排序和筛选,这是SQL从来不能实现的.除了最后的Order by子句之外,分析函数是在查询中执行的最后的操作集,这样的话,就不能直接在谓词中使用分析函数,即不能在上面使用where或having子句!!!下面我们通过一个实际的例子：按区域查找上一年度订单总额占区域订单总额20%以上的客户，来看看分析函数的应用。

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从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，之后行幅度值不超过1504)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 row2) 如果同时省略Order By则默认的窗口是unbounded preceding and unbounded following如果省略分组,则把全部记录当成一个组a) 如果SQL语句中的Order By满足分析函数分析时要求的排序,那么SQL语句中的排序将先执行,分析函数分析时就不必再排序b) 如果SQL语句中的Order By不满足分析函数分析时要求的排序，那么SQL语句中的排序将最后在分析函数分析结束后执行排序三、分析函数1）AVG功能描述：用于计算一个组和数据窗口内表达式的平均值。

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.842003 2 1 21 15144.652003 3 1 21 20137.832003 4 1 21 25057.452003 5 1 21 17214.562003 6 1 21 15564.642003 7 1 21 12654.842003 8 1 21 17434.822003 9 1 21 19854.572003 10 1 21 21754.19已选择10行。

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

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值都会⼀样，即整个组的总和。

oracle分析函数

oracle分析函数
Oracle分析函数是一组可用于处理数据集的函数。

它们可以帮助您计算数据的统计值，如求和、平均值、最小值和最大值。

此外，还可以对数据进行细粒度的分析，如数据分析、排序、汇总和聚合。

Oracle分析函数可以提高您的应用程序的性能，并使您能够缩短查询时间。

Oracle分析函数可以分为三类：统计函数、数据分析函数和排序函数。

统计函数可以帮助您计算数据集的基本统计信息，如求和、平均值、最小值和最大值。

数据分析函数可以帮助您对数据进行更细粒度的分析，如数据分组、汇总和聚合。

排序函数可以帮助您对数据进行排序，以便更好地分析数据。

Oracle分析函数的优点非常明显，它们可以提高您的应用程序的性能，而且可以缩短查询时间。

此外，它们还可以提供准确和可靠的数据。

因此，当您需要对数据进行分析时，Oracle分析函数是一个有用的工具。

Oracle分析函数是一个强大的工具，可以帮助您更有效地处理数据集，从而获得更准确、更可靠的结果。

它们可以帮助您更快地执行复杂的查询，并使您能够更准确地分析和理解数据。

因此，Oracle 分析函数可以极大地提高您的业务分析效率，从而给您带来更大的利润。

oracle分析函数

oracle分析函数Oracle数据库提供了强大的分析函数，这些函数可以用于执行高级数据分析，并得出有关数据集的统计信息。

以下是一些常用的Oracle分析函数：1.ROW_NUMBER(函数ROW_NUMBER(函数返回一个表示每行排序位置的数字。

它通常与ORDERBY子句一起使用，以确定每行的位置。

例如，以下查询将为每个销售订单返回一个唯一的数字，并按订单日期进行排序：```SELECT order_id, order_date, ROW_NUMBER( OVER (ORDER BY order_date) as row_numFROM sales_orders;```2.RANK(函数RANK(函数用于计算一组值的排名。

它将返回一个表示每个值排名的数字，如果有重复的值，则会跳过下一个排名。

例如，以下查询将为每个销售订单返回一个唯一的数字，并按订单总额进行排名：```SELECT order_id, order_total, RANK( OVER (ORDER BYorder_total DESC) as rank_numFROM sales_orders;```3.DENSE_RANK(函数DENSE_RANK(函数类似于RANK(函数，但它不会跳过下一个排名。

如果有两个值具有相同的排名，则它们都将返回相同的排名。

例如，以下查询将为每个销售订单返回一个唯一的数字，并按订单总额进行排名：```SELECT order_id, order_total, DENSE_RANK( OVER (ORDER BY order_total DESC) as dense_rank_numFROM sales_orders;```G(函数和LEAD(函数LAG(函数和LEAD(函数用于访问当前行前一个或后一个行的数据。

例如，以下查询将为每个销售订单返回一个唯一的数字，并显示前一个订单的订单日期和后一个订单的订单日期：```SELECT order_id, order_date, LAG(order_date) OVER (ORDER BY order_date) as prev_order_date,LEAD(order_date) OVER (ORDER BY order_date) asnext_order_dateFROM sales_orders;```5.SUM(函数和AVG(函数SUM(函数和AVG(函数用于计算一组值的总和和平均值。

分析函数

1.1背景知识分析函数是oracle8.1.6加入的，oracle后续版本扩展了分析函数的数目。

分析函数可以分为四类：等级函数(ranking)，聚合函数(aggregate)，行比较函数(row comparison)，统计函数(statistical)。

本节内容主要围绕这四个方面讲解分析函数的原理，特点，使用场合，注意点等。

对常用分析函数重点分析，一般的分析函数作为了解简单介绍。

使用分析函数的好处很多,可以解决复杂问题的分析，代替复杂的查询操作，比如没有分析函数之前，我们要解决一个复杂的问题，可能需要用到自连接，子查询或内嵌视图，甚至可能要使用存储过程，但是使用了分析函数之后，一句简单的sql就可以解决这些问题，而且oracle对分析函数做了内部的优化处理，性能比使用复杂查询有很大的提升。

1.2 分析函数基础1.2.1 分析函数基本原理Oracle分析函数(Analytic Function)依赖于对行的分组，是对行进行分组之后，计算这些分组的值。

所以，分析函数是对查询的结果集按照分析子句和分析函数的规则来进行进一步的操作。

首先按照分析子句对行进行分组(大组)，然后扫描各个分组，被扫描到的当前行会对应于一个分组内的滑动的窗口(小组)之内，那么当前行对应的这个滑动的窗口范围由分析子句决定，这个范围可以是物理度量的也可以是逻辑度量的，最后对当前行对应的窗口使用分析函数，计算组的值。

所以分析函数对于各个组可以产生多个分组的值，而组函数对于各个分组只能产生唯一值，这是两者之间的典型区别。

名词：分析子句(analytic clause),最多由三个部分组成，依次顺序是partition子句，order by 子句，window 子句。

Partition 子句确定如何对行进行分组，如果没有partition 子句，那么所有行为一组。

Order by子句确定组内的排序规则。

Window子句，确定当前行对应的窗口范围，从而用分析函数计算当前行对应的窗口的值，如果没有window子句，而有order by那么默认窗口范围是组的首行到当前行，如果没有order by，那么默认是组的首行到组的末行。

Oracle分析函数

Oracle分析函数Oracle分析函数一——函数列表SUM ：该函数计算组中表达式的累积和MIN ：在一个组中的数据窗口中查找表达式的最小值MAX ：在一个组中的数据窗口中查找表达式的最大值AVG：用于计算一个组和数据窗口内表达式的平均值。

COUNT ：对一组内发生的事情进行累积计数-------------------------------------------------------------------------------------------------RANK ：根据ORDER BY子句中表达式的值，从查询返回的每一行，计算它们与其它行的相对位置DENSE_RANK ：根据ORDER BY子句中表达式的值，从查询返回的每一行，计算它们与其它行的相对位置FIRST ：从DENSE_RANK返回的集合中取出排在最前面的一个值的行LAST ：从DENSE_RANK返回的集合中取出排在最后面的一个值的行FIRST_VALUE ：返回组中数据窗口的第一个值LAST_VALUE ：返回组中数据窗口的最后一个值。

LAG ：可以访问结果集中的其它行而不用进行自连接LEAD ：LEAD与LAG相反，LEAD可以访问组中当前行之后的行ROW_NUMBER：返回有序组中一行的偏移量，从而可用于按特定标准排序的行号----------------------------STDDEV ：计算当前行关于组的标准偏离STDDEV_POP：该函数计算总体标准偏离，并返回总体变量的平方根STDDEV_SAMP：该函数计算累积样本标准偏离，并返回总体变量的平方根VAR_POP ：该函数返回非空集合的总体变量（忽略null）VAR_SAMP ：该函数返回非空集合的样本变量（忽略null）VARIANCE ：如果表达式中行数为1，则返回0，如果表达式中行数大于1，则返回VAR_SAMPCOVAR_POP ：返回一对表达式的总体协方差COVAR_SAMP：返回一对表达式的样本协方差CORR ：返回一对表达式的相关系数-------------------------------------------------------------------------------------------------CUME_DIST ：计算一行在组中的相对位置NTILE ：将一个组分为"表达式"的散列表示PERCENT_RANK：和CUME_DIST（累积分配）函数类似PERCENTILE_DISC：返回一个与输入的分布百分比值相对应的数据值PERCENTILE_CONT：返回一个与输入的分布百分比值相对应的数据值RATIO_TO_REPORT：该函数计算expression/(sum(expression))的值，它给出相对于总数的百分比REGR_ (Linear Regression) Functions：这些线性回归函数适合最小二乘法回归线，有9个不同的回归函数可使用----------------------------CUBE :按照OLAP的CUBE方式进行数据统计,即各个维度均需统计ROLLUP :SELECTdepartment_id,manager_id,employee_id,first_name||' '||last_name employee_name,hire_date,salary,job_idFROM employeesORDER BY department_id,hire_dateOracle分析函数二——函数用法Oracle分析函数实际上操作对象是查询出的数据集，也就是说不需二次查询数据库，实际上就是oracle实现了一些我们自身需要编码实现的统计功能，对于简化开发工作量有很大的帮助，特别在开发第三方报表软件时是非常有帮助的。

oracle分析函数汇总

oracle分析函数--SQL*PLUS环境--1、GROUP BY子句--CREATE TEST TABLE AND INSERT TEST DATA. create table students(id number(15,0),area varchar2(10),stu_type varchar2(2),score number(20,2));insert into students values(1, '111', 'g', 80 );insert into students values(1, '111', 'j', 80 );insert into students values(1, '222', 'g', 89 );insert into students values(1, '222', 'g', 68 );insert into students values(2, '111', 'g', 80 );insert into students values(2, '111', 'j', 70 );insert into students values(2, '222', 'g', 60 );insert into students values(2, '222', 'j', 65 );insert into students values(3, '111', 'g', 75 );insert into students values(3, '111', 'j', 58 );insert into students values(3, '222', 'g', 58 );insert into students values(3, '222', 'j', 90 );insert into students values(4, '111', 'g', 89 );insert into students values(4, '111', 'j', 90 );insert into students values(4, '222', 'g', 90 );insert into students values(4, '222', 'j', 89 ); commit;col score format 999999999999.99--A、GROUPING SETSselect id,area,stu_type,sum(score) scorefrom studentsgroup by grouping sets((id,area,stu_type),(id,area),id) order by id,area,stu_type;/*--------理解grouping setsselect a, b, c, sum( d ) from tgroup by grouping sets ( a, b, c )等效于select * from (select a, null, null, sum( d ) from t group by a union allselect null, b, null, sum( d ) from t group by b union allselect null, null, c, sum( d ) from t group by c )*/--B、ROLLUPselect id,area,stu_type,sum(score) score from studentsgroup by rollup(id,area,stu_type)order by id,area,stu_type;/*--------理解rollupselect a, b, c, sum( d )from tgroup by rollup(a, b, c);等效于select * from (select a, b, c, sum( d ) from t group by a, b, c union allselect a, b, null, sum( d ) from t group by a, b union allselect a, null, null, sum( d ) from t group by a union allselect null, null, null, sum( d ) from t)*/--C、CUBEselect id,area,stu_type,sum(score) score from studentsgroup by cube(id,area,stu_type)order by id,area,stu_type;/*--------理解cubeselect a, b, c, sum( d ) from tgroup by cube( a, b, c)等效于select a, b, c, sum( d ) from tgroup by grouping sets(( a, b, c ),( a, b ), ( a ), ( b, c ),( b ), ( a, c ), ( c ),() )*/--D、GROUPING/*从上面的结果中我们很容易发现,每个统计数据所对应的行都会出现null,如何来区分到底是根据那个字段做的汇总呢,grouping函数判断是否合计列!*/select decode(grouping(id),1,'all id',id) id,decode(grouping(area),1,'all area',to_char(area)) area,decode(grouping(stu_type),1,'all_stu_type',stu_type) stu_type,sum(score) scorefrom studentsgroup by cube(id,area,stu_type)order by id,area,stu_type;--2、OVER()函数的使用--1、RANK()、DENSE_RANK() 的、ROW_NUMBER()、CUME_DIST()、MAX()、AVG()break on id skip 1select id,area,score from students order by id,area,score desc;select id,rank() over(partition by id order by score desc) rk,score from students;--允许并列名次、名次不间断select id,dense_rank() over(partition by id order by score desc) rk,score from students;--即使SCORE相同，ROW_NUMBER()结果也是不同select id,row_number() over(partition by ID order by SCORE desc) rn,score from students;select cume_dist() over(order by id) a, --该组最大row_number/所有记录row_numberrow_number() over (order by id) rn,id,area,score from students;select id,max(score) over(partition by id order by score desc) as mx,score from students; select id,area,avg(score) over(partition by id order by area) as avg,score from students; --注意有无order by的区别--按照ID求AVGselect id,avg(score) over(partition by id order by score desc rows between unbounded precedingand unbounded following ) as ag,score from students;--2、SUM()select id,area,score from students order by id,area,score desc;select id,area,score,sum(score) over (order by id,area) 连续求和, --按照OVER后边内容汇总求和sum(score) over () 总和, -- 此处sum(score) over () 等同于sum(score)100*round(score/sum(score) over (),4) "份额(%)"from students;select id,area,score,sum(score) over (partition by id order by area ) 连id续求和, --按照id内容汇总求和sum(score) over (partition by id) id总和, --各id的分数总和100*round(score/sum(score) over (partition by id),4) "id份额(%)",sum(score) over () 总和, -- 此处sum(score) over () 等同于sum(score)100*round(score/sum(score) over (),4) "份额(%)"from students;--4、LAG(COL,n,default)、LEAD(OL,n,default) --取前后边N条数据select id,lag(score,1,0) over(order by id) lg,score from students;select id,lead(score,1,0) over(order by id) lg,score from students;--5、FIRST_VALUE()、LAST_VALUE()select id,first_value(score) over(order by id) fv,score from students;select id,last_value(score) over(order by id) fv,score from students;。

Oracle分析函数

FROM XTBILL2011 A
GROUP BY GROUPING SETS(ROLLUP(A.VPD_COMPID),ROLLUP(TO_CHAR(A.TTIME,'MM')));
--这条语句产生了两个合计行，因为rollup或cube作为grouping sets的参数，相当于对每个
--group by grouping sets(a,b,c)相当于group by a,group by b,group by c
--这三组的union all结果
--1)
SELECT A.VPD_COMPID,TO_CHAR(A.TTIME,'MM'),COUNT(*)
FROM XTBILL2011 A
--为了区别哪些是小计，grouping函数派上用场了！
SELECT A.VPD_COMPID,TO_CHAR(A.TTIME,'MM'),COUNT(*),GROUPING(A.VPD_COMPID),GROUPING(TO_CHAR(A.TTIME,'MM')),
DECODE(GROUPING(A.VPD_COMPID),1,'所有单位',A.VPD_COMPID) VPD_COMPID,
CREATE TABLE t(
ORDER_DATE DATE, --订购日期
ORDER_NO NUMBER, --订购号
ORDER_BOOK VARCHAR2(10), --订购书籍
ORDER_FEE NUMBER, --订单总金额
ORDER_NUM NUMBER
);
INSERT INTO T
SELECT TO_DATE('2010-05-01','YYYY-MM-DD')+LEVEL,

ORACLE_分析函数大全

ORACLE_分析函数大全1.SUM：计算指定列的总和。

用法：SUM(column) OVER (PARTITION BY expression ORDER BY expression)2.AVG：计算指定列的平均值。

用法：AVG(column) OVER (PARTITION BY expression ORDER BY expression)3.COUNT：计算指定列的记录数。

用法：COUNT(column) OVER (PARTITION BY expression ORDER BY expression)4.MAX：计算指定列的最大值。

用法：MAX(column) OVER (PARTITION BY expression ORDER BY expression)5.MIN：计算指定列的最小值。

用法：MIN(column) OVER (PARTITION BY expression ORDER BY expression)6.FIRST_VALUE：计算指定列的第一个值。

用法：FIRST_VALUE(column) OVER (PARTITION BY expression ORDER BY expression)ST_VALUE：计算指定列的最后一个值。

用法：LAST_VALUE(column) OVER (PARTITION BY expression ORDER BY expression)8.LEAD：返回指定行后的值。

用法：LEAD(column, offset, default) OVER (PARTITION BY expression ORDER BY expression)G：返回指定行前的值。

用法：LAG(column, offset, default) OVER (PARTITION BY expression ORDER BY expression)10.RANK：计算指定列的排名（相同值有相同的排名，相同排名后续排名跳过）。

Oracle开发专题之：分析函数总结

【原】Oracle开发专题之：分析函数总结这一篇是对前面所有关于分析函数的文章的总结：一、统计方面：Sum() Over ([Partition by ][Order by ])Sum() Over ([Partition by ][Order by ]Rows Between Preceding And Following)Sum() Over ([Partition by ][Order by ]Rows Between Preceding And Current Row)Sum() Over ([Partition by ][Order by ]Range Between Interval '''Day' PrecedingAnd Interval '''Day' Following )具体请参考《Oracle开发专题之：分析函数(OVER)》和《Oracle开发专题之：窗口函数》二、排列方面：Rank() Over ([Partition by ][Order by ][Nulls First/Last])Dense_rank() Over ([Patition by ][Order by ][Nulls First/Last])Row_number() Over ([Partitionby ][Order by ][Nulls First/Last])Ntile() Over ([Partition by ][Order by ])具体请参考《Oracle开发专题之：分析函数2》三、最大值/最小值查找方面：Min()/Max() Keep (Dense_rank First/Last [Partition by ][Order by ]) 具体请参考《Oracle开发专题之：分析函数3》四、首记录/末记录查找方面：First_value / Last_value(Sum() Over ([Patition by ][Order by ]Rows Between Preceding And Following ))具体请参考《Oracle开发专题之：窗口函数》五、相邻记录之间比较方面：Lag(Sum(), 1) Over([Patition by ][Order by ])。

oracle的分析函数和开窗函数over（）

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

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

3 开窗函数开窗函数指定了函数所能影响的窗⼝范围，也就是说在这个窗⼝范围中都可以受到函数的影响，有些分析函数就是开窗函数。

4 分析函数语法function_name (<argument>,<argument>...)OVER(<PARTITION-Clause><ORDER-BY-Clause><Windowing-Clause>)语法解释：1. function_name：对窗⼝中的数据进⾏操作，Oracle常⽤的分析函数有（这⾥就列举了⼀些常⽤的，其实有很多）①聚合函数sum：⼀个组中数据累积和min：⼀个组中数据最⼩值max：⼀个组中数据最⼤值avg：⼀个组中数据平均值count：⼀个组中数据累积计数②排名函数 row_number( )：返回⼀个唯⼀的值，当碰到相同数据时，排名按照记录集中记录的顺序依次递增。

rank( )：返回⼀个唯⼀的值，当碰到相同的数据时，此时所有相同数据的排名是⼀样的，同时会在最后⼀条相同记录和下⼀条不同记录的排名之间空出排名。

dense_rank( )：返回⼀个唯⼀的值，当碰到相同数据时，此时所有相同数据的排名都是⼀样的，同时会在最后⼀条相同记录和下⼀条不同记录的排名之间紧邻递增。

2. over：关键字，⽤于标识分析函数3. Partition-Clause：分区⼦句，根据分区表达式的条件逻辑将单个结果集分成N组格式： partition by......　4. Order-by-Clause：排序⼦句，⽤于对分区中的数据进⾏排序格式：order by......5. Windowing-Clause：窗⼝⼦句，⽤于定义function在其上操作的⾏的集合，即function所影响的范围格式：order by字段名 range|rows between边界规则1 AND边界规则2边界规则的取值如下表所⽰：可取值说明CURRENT ROW当前⾏N PRECEDING前N⾏UNBOUNDED PRECEDING⼀直到第⼀条记录N FOLLOWING后N⾏UNBOUNDED FOLLOWING⼀直到最后⼀条记录注意：RANGE表⽰按照值的范围进⾏范围的定义，⽽ROWS表⽰按照⾏的范围进⾏范围的定义⼆分析函数和开窗函数实例1 创建表格并插⼊数据--创建表格create table student(name varchar2(20),city varchar2(20),age int,salary int)--插⼊数据INSERT INTO student(name,city,age,salary) VALUES('Kebi','JiangSu',20,3000);INSERT INTO student(name,city,age,salary) VALUES('James','ChengDu',21,4000);INSERT INTO student(name,city,age,salary) VALUES('Denglun','BeiJing',22,3500);INSERT INTO student(name,city,age,salary) VALUES('Yangmi','London',21,2500);INSERT INTO student(name,city,age,salary) VALUES('Nana','NewYork',22,1000);INSERT INTO student(name,city,age,salary) VALUES('Sunli','BeiJing',20,3000);INSERT INTO student(name,city,age,salary) VALUES('Dengchao','London',22,1500);INSERT INTO student(name,city,age,salary) VALUES('Huge','JiangSu',20,2800);INSERT INTO student(name,city,age,salary) VALUES('Pengyuyan','BeiJing',24,4500);INSERT INTO student(name,city,age,salary) VALUES('Baoluo','London',25,8500);INSERT INTO student(name,city,age,salary) VALUES('Huting','ChengDu',25,3000);INSERT INTO student(name,city,age,salary) VALUES('Hurenxiang','JiangSu',23,2500);表格创建完后，查看表格中的内容2 聚合函数和开窗函数①单⼀的聚合函数count　案例：如果要求出student表中⼀共多少⼈select count(name) from student得到的结果从上表中看出，得到的结果是⼀个值，即为student表中⼀共12个⼈②聚合函数count和开窗函数over( )的联合使⽤　案例：如果查询每个⼯资⼩于4000元的员⼯信息（姓名，城市以及⼯资），并在每⾏中都显⽰所有⼯资⼩于4000元的员⼯个数　第⼀种实现⽅式：通过⼦查询实现select name,city ,salary,(select count(salary) from student where salary <4000) ⼯资⼩于4000⼈数from studentwhere salary <4000第⼆种实现⽅式：开窗函数over( )实现select name, city, salary,count(*) over()from studentwhere salary <4000解释⼀下：开窗函数count(*)over( )是对查询结果的每⼀⾏都返回所有符合条件⾏的条数；over关键字后的括号中的选项为空，则开窗函数会对结果集中的所有⾏进⾏聚合运算；over关键字后的括号中的选项为不为空，则按照括号中的范围进⾏聚合运算。

分析函数总结(oracle技术交流群)

分析函数的基础语法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。

分析函数的四大要点Analytic FunctionPARTITION BY ORDER BYROWS Vs RANGEUNBOUNDED PRECEDING FOLLOWING CURRENT ROW1.有的分析函数能够带window,有的不能带window子句,不能带window子句的比如rank,dense_rank,row_number,FIRST,LAST,lead,lag能够带windows的比如count,SUM,AVG,MIN,MAX,first_value,last_value。

FIRST,LAST里的分析函数部份order BY都是不许诺的有的分析函数，比如row_number,dense_rank,rank是必需要有order BY的。

BY 是按字段值将对应的行分组(不能带括号，带括号的是model和PARTITIONED outer JOIN利用的)，ORDER BY 是组内行的顺序，window子句决定每行对应的窗口范围BY ,ORDER BY ,window子句一起决定了当前行对应的窗口范围，当前行分析函数值确实是基于那个窗口计算的4.注意partition BY,ORDER BY,window子句的关系window子句是在partition by和order BY前提下设定当前行对应的窗口范围的,因此必需有order by才能写window子句。

Oracle开发之分析函数（TopBottomN、FirstLast、NTile）

Oracle开发之分析函数（TopBottomN、FirstLast、NTile）⼀、带空值的排列：在前⾯《》⼀⽂中，我们已经知道了如何为⼀批记录进⾏全排列、分组排列。

假如被排列的数据中含有空值呢？复制代码代码如下:SQL> select region_id, customer_id,sum(customer_sales) cust_sales,sum(sum(customer_sales)) over(partition by region_id) ran_total,rank() over(partition by region_idorder by sum(customer_sales) desc) rankfrom user_ordergroup by region_id, customer_id;REGION_ID CUSTOMER_ID CUST_SALES RAN_TOTAL RANK---------- ----------- ---------- ---------- ----------10 31 6238901 110 26 1808949 6238901 210 27 1322747 6238901 310 30 1216858 6238901 410 28 986964 6238901 510 29 903383 6238901 6我们看到这⾥有⼀条记录的CUST_TOTAL字段值为NULL，但居然排在第⼀名了！显然这不符合情理。

所以我们重新调整完善⼀下我们的排名策略，看看下⾯的语句：复制代码代码如下:SQL> select region_id, customer_id,sum(customer_sales) cust_total,sum(sum(customer_sales)) over(partition by region_id) reg_total,rank() over(partition by region_idorder by sum(customer_sales) desc NULLS LAST) rankfrom user_ordergroup by region_id, customer_id;REGION_ID CUSTOMER_ID CUST_TOTAL REG_TOTAL RANK---------- ----------- ---------- ---------- ----------10 26 1808949 6238901 110 27 1322747 6238901 210 30 1216858 6238901 310 28 986964 6238901 410 29 903383 6238901 510 31 6238901 6绿⾊⾼亮处，NULLS LAST/FIRST告诉Oracle让空值排名最后后第⼀。

oracle函数介绍_非著名函数之分析函数

ORACLE函数介绍非著名函数之分析函数1、CUME_DIST() OVER([partition_clause] order_by_clause) 返回该行在分组序列中的相对位置，返回值介于0到1之间。

注意哟，如果order by的列是desc，则该分组内最大的行返回列值1，如果order by为asc，则该分组内最小的行返回列值1。

例如：SELECT col, value, CUME_DIST() OVER(ORDER BY value DESC) FROM tmp1;2、NTILE(n) OVER([partition_clause] order_by_clause)ntile是个很有意思的统计函数。

它会按照你指定的组数(n)对记录做分组例如：SELECT t.*,ntile(5) over(order by value desc) FROM tmp1 t;3、PERCENT_RANK() OVER([partition_clause] order_by_clause) 与CUME_DIST类似，本函数返回分组序列中各行在分组序列的相对位置。

其返回值也是介于0到1之间，不过其起始值始终为0而终结值始终为1。

例如：SELECT col, value, PERCENT_RANK() OVER(ORDER BY value) FROM tmp1;4、PERCENTILE_CONT(n) WITHIN GROUP (ORDER BY col [DESC|ASC]) OVER(partition_clause)本函数功能与前面聚合函数处介绍的完全相同，只是一个是聚合函数，一个是分析函数。

例如：--聚合函数SELECT col, max(value), min(value), sum(value),PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY value) a,PERCENTILE_CONT(0.8) WITHIN GROUP(ORDER BY value) bFROM TMP1group by col;--分析函数SELECT col,value,sum(value) over(partition by col) "Sum",PERCENTILE_CONT(0.5) WITHIN GROUP( ORDER BY value) OVER(PARTITION BY col) "CONTa",PERCENTILE_CONT(0.8) WITHIN GROUP( ORDER BY value) OVER(PARTITION BY col) "CONTb"FROM TMP1;5、PERCENTILE_DISC(n) WITHIN GROUP (ORDER BY col [DESC|ASC]) OVER(partition_clause)本函数功能与前面聚合函数处介绍的完全相同，只是一个是聚合函数，一个是分析函数。

常用Oracle分析函数详解

常⽤Oracle分析函数详解学习步骤：1. 拥有Oracle EBS demo 环境或者 PROD 环境2. copy以下代码进 PL/SQL3. 配合解释分析结果4. 如果⽹页有点乱请复制到TXT中查看/*假设⼀个经理代表了⼀个部门*/SELECT emp.full_name,emp.salary,emp.manager_id,row_number() over(PARTITION BY emp.manager_id ORDER BY emp.salary DESC) row_number_dept, --部门排⾏rownum row_number, --⾏号round((rownum + 1) / 4) page_number, --每4⾏⼀页ntile(2) over(ORDER BY emp.salary DESC) page_number_nt, --平均分成两类AVG(emp.salary) over(PARTITION BY emp.manager_id) avg_salary_department, --该部门薪⽔均值SUM(emp.salary) over(PARTITION BY emp.manager_id) sum_salary_department, --该部门薪⽔总额COUNT(emp.salary) over(PARTITION BY emp.manager_id) count_emp_department, --部门所有的员⼯dense_rank() over(PARTITION BY emp.manager_id ORDER BY emp.salary DESC) rank_salary_dept, --该⼈员的部门薪⽔排⾏dense_rank() over(ORDER BY emp.salary DESC) rank_salary_company, --该⼈员的全公司排⾏MIN(emp.salary) over(PARTITION BY emp.manager_id) min_salary_dept, --部门的最低薪⽔MIN(emp.salary) keep(dense_rank FIRST ORDER BY emp.salary) over(PARTITION BY emp.manager_id) min_salary_dept_first, --部门的最低薪⽔first_value(emp.salary) over(PARTITION BY emp.manager_id ORDER BY emp.salary) min_salary_dept_firstv, --部门的最低薪⽔MAX(emp.salary) over(PARTITION BY emp.manager_id) max_salary_dept, --部门的最⾼薪⽔MAX(emp.salary) keep(dense_rank LAST ORDER BY emp.salary) over(PARTITION BY emp.manager_id) max_salary_dept_last, --部门的最⾼薪⽔last_value(emp.salary) over(PARTITION BY emp.manager_id ORDER BY emp.salary) max_salary_dept_lastv, --部门的最⾼薪⽔lag(emp.full_name, 1, '00') over(ORDER BY emp.salary DESC) last_persion, --薪⽔在⾃⼰前⼀位的⼈lead(emp.full_name, 1, '00') over(ORDER BY emp.salary DESC) next_persion --薪⽔在⾃⼰后⼀位的⼈FROM fwk_tbx_employees empORDER BY emp.salary DESC1. 基本概念理解分析函数1. 顾名思义，分析函数是在主查询结果的基础上进⾏⼀定的分析，如分部门汇总，分部门求均值等等。