Description of Oracle Aggregate functions

 

Oracle Aggregate functions has been used a lot. The official website is described as follows:

 

Aggregate functions

Http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions003.htm#SQLRF20035

 

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 clses. 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.

-- The aggregate function returns a result in each group based on the group situation. Aggregate functions can be used in orderby and having clauses.

 

If you omit the group by clause, then oracle applies aggregate fungate 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.

-- If the group by clause is omitted, the aggregate function takes effect for all records in the query table or view.

 

Aggregate (but notall) Aggregate functions that take a single argument accept these clses:

Some Aggregate functions can include a parameter in the clause, such:

(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.

(2) All causes 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.

 

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

-- Some aggregrate functions allow the window opening function. The window opening function is an integral part of the analysis function. In the aggregate functions listed below, you can use the window opening clause to add a star number.

 

For more information about analysis functions, see:

Analytic Functions

Http://blog.csdn.net/tianlesoftware/article/details/4795632

 

All Aggregate fungate counters t 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 fungate, if the data set contains no rows, or contains only rowswith nulls as arguments to the aggregate function, then the function returnsnull.

-- Except count (*), grouping, and grouping_id, All Aggregate functions ignore nulls. When we encounter null, we can use the nvl function in the aggregate function to process null.

The count and regr_count Aggregate functions do not return NULL, but return 0 or a number. If no rows is available for other Aggregate functions, null is returned.

 

Nvl usage:

Nvl (eexpression1, eexpression2)

If the calculation result of eexpression1 is null, nvl () returns eexpression2. If the calculation result of eexpression1 is not null, eexpression1 is returned. Eexpression1 and eexpression2 can be any data type. If both eexpression1 and eexpression2 are null values, nvl () returns NULL.

Therefore, nvl can be used to filter NULL values.

 

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.

-- Aggregate functions can be nested, for example:

For example, thefollowing example calculates the average of the maximum salaries of all theexecutions in the sample schema HR:

Select AVG (max (salary ))

From employees

Group by department_id;

 

AVG (max (salary ))

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

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 fungate that follows, functions followed by an asterisk (*) allowthe specified wing_clause.

-- The list of Aggregate functions is as follows. The window clause can be used for Adding star numbers. For details about the usage of each aggregate function, refer to the official documentation.

 

AVG
Collect
Corr
Corr _*
Count
Covar_pop
Covar_samp
Cume_dist
Dense_rank
First
Group_id
Grouping
Grouping_id
Last
Listparts
Max
Median
Min
Percent_rank
Percentile_cont
Percentile_disc
Rank
Regr _ (linear regression) Functions
Stats_binomial_test
Stats_crosstab
Stats_f_test
Stats_ks_test
Stats_mode
Stats_mw_test
Stats_one_way_anova
Stats_t_test _*
Stats_wsr_test
Stddev
Stddev_pop
Stddev_samp
Sum
Sys_xmlmetadata
Var_pop
Var_samp
Variance
Xmlparts

 

 

 

 

 

 

 

Bytes -------------------------------------------------------------------------------------------------------

All rights reserved. reprinted articles are allowed, but source addresses must be indicated by links. Otherwise, the documents will be held legally responsible!

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

WEAVER: http://weibo.com/tianlesoftware

Email: tianlesoftware@gmail.com

Skype: tianlesoftware

 

------- Add a group to describe the relationship between Oracle tablespace and data files in the remarks section. Otherwise, reject the application ----

Dba1 group: 62697716 (full); dba2 group: 62697977 (full) dba3 group: 62697850 (full)

Super DBA group: 63306533 (full); dba4 group: 83829929 dba5 group: 142216823

Dba6 group: 158654907 dba7 group: 172855474 dba8 group: 102954821