Oracle Analytic Functions




Oracle Analytic Functions compute an aggregate value based on a group of rows. It opens up a whole new way of looking at the data. They differ from aggregate functions in that they return multiple rows for each group. The group of rows is called a window and is defined by the analytic clause. For each row, a sliding window of rows is defined. The window determines the range of rows used to perform the calculations for the current row.

Oracle provides many Analytic Functions such as
AVG, CORR, COVAR_POP, COVAR_SAMP, COUNT, CUME_DIST, DENSE_RANK, FIRST, FIRST_VALUE, LAG, LAST, LAST_VALUE, LEAD, MAX, MIN, NTILE, PERCENT_RANK, PERCENTILE_CONT, PERCENTILE_DISC, RANK, RATIO_TO_REPORT, STDDEV, STDDEV_POP, STDDEV_SAMP, SUM, VAR_POP, VAR_SAMP, VARIANCE.

The Syntax of analytic functions:
Analytic-Function(Column1,Column2,...)
OVER (
[Query-Partition-Clause]
[Order-By-Clause]
[Windowing-Clause]
)

Analytic functions take 0 to 3 arguments.

An Example:
SELECT ename, deptno, sal,
SUM(sal)
OVER (ORDER BY deptno, ename) AS Running_Total,
SUM(sal)
OVER ( PARTITION BY deptno
ORDER BY ename) AS Dept_Total,
ROW_NUMBER()
OVER (PARTITION BY deptno
ORDER BY ename) As Sequence_No
FROM emp
ORDER BY deptno, ename;



For more details visit:

www.dwbiconcepts.com

4 comments:

  1. Data Warehouse Studio is a powerful software development platform that enables enterprises to drastically reduce costs, risks, and development times of enterprise data warehouse

    ReplyDelete
  2. Useful Information and please also post some new updates .Thank you.Want to learn
    SAP HANA Online Training

    ReplyDelete