oracle lists函数,Oracle 聚合函数（Aggregate Functions）说明

Oracle Aggregate Functions用过很多，官网的说明如下：https://www.cndba.cn/Dave/article/1486

Aggregate Functions

Aggregatefunctions return a single result row based on groups of rows, rather than onsingle rows. Aggregate functions can appear in select lists and in ORDER BY andHAVING clauses. They are commonly used with the GROUP BY clausein a SELECT statement, where Oracle Database divides the rows of aqueried table or view into groups. In a query containing a GROUP BY clause,the elements of the select list can be aggregate functions, GROUP BY expressions,constants, or expressions involving one of these. Oracle applies the aggregatefunctions to each group of rows and returns a single result row for each group.

--聚合函数根据group的情况，返回每个groups里的一个result。 聚合函数可以使用orderby 和 having 的子句中使用。

If you omit the GROUP BY clause,then Oracle applies aggregate functions in the select list to all the rows inthe queried table or view. You use aggregate functions in the HAVING clauseto eliminate groups from the output based on the results of the aggregatefunctions, rather than on the values of the individual rows of the queriedtable or view.

https://www.cndba.cn/Dave/article/1486

--如果省略group by 子句，那么聚合函数针对查询表或视图的所有记录生效。

Many (but notall) aggregate functions that take a single argument accept these clauses:

一些聚合函数可以在子句中带一个参数，如：

(1)DISTINCT and UNIQUE,which are synonymous, cause an aggregate function to consider only distinctvalues of the argument expression. The syntax diagrams for aggregate functionsin this chapter use the keyword DISTINCT for simplicity.

https://www.cndba.cn/Dave/article/1486

(2)ALLcauses an aggregatefunction to consider all values, including all duplicates.

For example, the DISTINCT averageof 1, 1, 1, and 3 is 2. The ALL average is 1.5. If you specifyneither, then the default is ALL.https://www.cndba.cn/Dave/article/1486

Some aggregatefunctions allow the windowing_clause, which is part of the syntax ofanalytic functions. Refer to windowing_clause forinformation about this clause. In the listing of aggregate functions at the endof this section, the functions that allow the windowing_clause arefollowed by an asterisk (*)

--一些aggregrate 函数允许使用开窗函数，开窗函数是分析函数的组成部分，在下面列出的aggregate functions 中，后面加星号的就是可以使用开窗子句的。

有关分析函数参考：

Oracle 分析函数(Analytic Functions) 说明

All aggregate functions except COUNT(*), GROUPING,and GROUPING_ID ignore nulls. You can use the NVL functionin the argument to an aggregate function to substitute a value for a null. COUNT and REGR_COUNT neverreturn null, but return either a number or zero. For all the remainingaggregate functions, if the data set contains no rows, or contains only rowswith nulls as arguments to the aggregate function, then the function returnsnull.

--除了count(*),grouping,和grouping_id外，所有的聚合函数都会忽略nulls。 当我们遇到Null 时，可以在聚合函数中使用NVL 函数来处理null。

Count 和Regr_count 聚合函数不会返回null，而是返回0或者某个数字。 其他的聚合函数当没有rows 则返回null。

NVL 用法：

NVL(eExpression1,eExpression2)

如果 eExpression1 的计算结果为 null 值，则 NVL( ) 返回 eExpression2。如果 eExpression1 的计算结果不是 null 值，则返回 eExpression1。eExpression1 和 eExpression2 可以是任意一种数据类型。如果 eExpression1 与 eExpression2 的结果皆为 null 值，则 NVL( ) 返回 NULL。

因此使用NVL可以过滤NULL值。

The aggregate functions MIN, MAX, SUM, AVG, COUNT, VARIANCE,and STDDEV, when followed by the KEEP keyword, can be used inconjunction with the FIRST or LAST function to operate on a setof values from a set of rows that rank as the FIRST or LAST withrespect to a given sorting specification. Refer to FIRST formore information.

You can nestaggregate functions.

--聚合函数可以嵌套使用，示例如下：

For example, thefollowing example calculates the average of the maximum salaries of all thedepartments in the sample schema hr:

SELECT AVG(MAX(salary))

GROUP BY department_id;

https://www.cndba.cn/Dave/article/1486

AVG(MAX(SALARY))

https://www.cndba.cn/Dave/article/1486

----------------

10926.3333

This calculation evaluates the inner aggregate (MAX(salary)) for each group defined by the GROUP BY clause(department_id), and aggregates the results again.

In the list ofaggregate functions that follows, functions followed by an asterisk (*) allowthe windowing_clause.

--聚合函数的列表如下，加星号的可以使用开窗子句，具体每个聚合函数的用法参考官方文档。https://www.cndba.cn/Dave/article/1486

-------------------------------------------------------------------------------------------------------

版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!

Blog:     http://blog.csdn.net/tianlesoftware

Weibo: http://weibo.com/tianlesoftware

Skype: tianlesoftware

-------加群需要在备注说明Oracle表空间和数据文件的关系，否则拒绝申请----

DBA1 群：62697716(满);   DBA2 群：62697977(满)  DBA3 群：62697850(满)

DBA 超级群：63306533(满);  DBA4 群：83829929   DBA5群： 142216823

DBA6 群：158654907    DBA7 群：172855474  DBA8群：102954821

版权声明：本文为博主原创文章，未经博主允许不得转载。