Oracle Analytic Function Learning Summary

In the company often use the Oracle analysis function, in statistics this piece of useful, especially complex SQL query, I am here to combine the network some information to summarize, hope can help everyone:

Oracle Analytic functions--List of functions

SUM: The function calculates the accumulation of expressions in a group and

Min: Find the minimum value of an expression in a data window in a group

Max: Find the maximum value of an expression in a data window in a group

AVG: Used to calculate the average of an expression within a group and data window.

Count: Accumulate a count of what is happening within a group

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

RANK: Calculates the relative position of the other rows from each row returned by the query, based on the value of the expression in the ORDER BY clause

Dense_rank: Calculates the relative position of each row that is returned from the query, based on the value of the expression in the ORDER BY clause

First: Remove the row from the first value in the collection returned from Dense_rank

Last: Takes a row from the collection returned by Dense_rank to a value in the final row

First_value: Returns the first value of the Data window in a group

Last_value: Returns the last value of the data window in the group.

LAG: You can access other rows in the result set without having to self-connect

Lead:lead In contrast to lag, leads can access rows after the current row in the group

Row_number: Returns the offset of a row in an ordered group so that it can be used for line numbers sorted by specific criteria

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

STDDEV: Calculates the current row about the standard deviation of the group

Stddev_pop: The function calculates the overall standard deviation and returns the square root of the population variable

Stddev_samp: The function calculates the cumulative sample standard deviation and returns the square root of the population variable

Var_pop: The function returns the population variable of a non-empty collection (ignoring null)

Var_samp: The function returns a sample variable for a non-empty collection (ignores null)

VARIANCE: Returns 0 if the number of rows in the expression is 1, or var_samp if the number of rows in the expression is greater than 1

Covar_pop: Returns the population covariance of a pair of expressions

Covar_samp: Returns the sample covariance of a pair of expressions

CORR: Returns the correlation coefficient of a pair of expressions

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

Cume_dist: Calculates the relative position of a row in a group

NTILE: A hash representation of a group into an "expression"

Percent_rank: Similar to the cume_dist (cumulative allocation) function

Percentile_disc: Returns a data value corresponding to the distribution percent value of the input

Percentile_cont: Returns a data value corresponding to the distribution percent value of the input

Ratio_to_report: The function calculates the value of expression/(expression), which gives a percentage relative to the total

Regr_ (Linear Regression) Functions: These linear regression functions are appropriate for the least squares regression line, with 9 different regression functions that can be used

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

Cube: According to the OLAP cube method of data statistics, that each dimension is required to statistics

ROLLUP:

SELECT

DEPARTMENT_ID,

MANAGER_ID,

EMPLOYEE_ID,

first_name| | ' ' | | Last_Name Employee_Name,

Hire_date,

Salary

job_id

From Employees

ORDER by Department_id,hire_date

650) this.width=650; "style=" border-bottom:0px;border-left:0px;border-top:0px;border-right:0px; "border=" 0 "src=" Http://blog.itpub.net/attachments/2009/08/6517_200908021217461.jpg "/>

Learn more about getting information

This article is from the "Java EE Architecture" blog, make sure to keep this source http://1440921.blog.51cto.com/1430921/1664264

Oracle Analytic Function Learning Summary