- HubPages»
- Technology»
- Computers & Software»
- Computer Software
Analytic Functions in Oracle (10g)
Oracle Analytic Functions
Analyctic functions in Oracle are set of functions and clauses used for arriving statistical informations like sum, average. These functions are more flexible to use and efficient.
Examples: Calculate a running total and moving average, Top-N queries
Earlier, these problems were solved using PL/SQL. But wehn we look for better performance, PL/SQL does not benefit much. Analytical functions provides more benifits with no questions on performance.Though this feature has been intrduced from Oracle 8, many developers does not aware of this. Especially for a pivot table query most of the developers still going for the old approach.
Key benefits
- Easier to code
- Faster than a equivalent SQL or PL/SQL
- No need to test or tune for performance as in the case of custom SQL/PL-SQL
Syntax
<Analytic-Function>(parameter1,parameter2,...)
OVER (
<Partition By column1, column2, .. [Order By column1, column2, .. [ASC/DESC] [NULLS FIRST/LAST] ] >
Order ByOrder By column1, column2, .. [ASC/DESC] [NULLS FIRST/LAST]
[ROWS [UNBOUNDED] v_rows PRECEDING/FOLLOWING] / [RANGE [UNBOUNDED] v_range PRECEDING/FOLLOWING]
)
Note: The ROWS/RANGE keywords are called windowing clauses/windowing functions.
List of Analytic-Functions
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.
Example-1: Top-n Query using Oracle Analytic Functions
Below query pulls out top-3 ranked sales figures department wise. Query uses a partition by clause over DENSE_RANK function.
SELECT * FROM
(SELECT sale_date, dept_code, sales_amount,
DENSE_RANK() OVER(PARTITION BY DEPT_CODE ORDER BY SALES_AMOUNT DESC) sale_rank
FROM sales_summary_daily)
WHERE sale_rank <=3;
Below is the resuts of the query.
SALE_DATE DEPT_CODE SALES_AMOUNT SALE_RANK
--------- --------- -------------- ----------------------
28-OCT-03 East 99998.63 1
19-SEP-95 East 99996.64 2
08-OCT-85 East 99942.49 3
06-NOV-98 NE 49995.44 1
15-FEB-06 NE 49991.96 2
28-DEC-98 NE 49988.92 3
09-FEB-05 North 99983.69 1
24-DEC-86 North 99980.99 2
22-OCT-02 North 99973.49 3
16-APR-82 SE 49998.3 1
27-JAN-95 SE 49996.16 2
14-SEP-83 SE 49990.52 3
27-JUL-96 South 69996.2 1
15-JAN-06 South 69993.7 2
17-AUG-97 South 69991.29 3
10-OCT-96 West 9999.91 1
23-AUG-94 West 9998.76 2
06-SEP-02 West 9998.51 3
Above query pulls top-3 sales figures for all sales region
using Oracle analytic functions. Deriving this using
conventional SQL/PLSQL will be a night mare.
Example-2 Pivot Table Query using Oracle Analytic Functions
SELECT SALE_MONTH,
MAX(DECODE(sale_rank, 1, dept_code, null)) First,
MAX(DECODE(sale_rank, 2, dept_code, null)) Second,
MAX(DECODE(sale_rank, 3, dept_code, null)) Third
FROM
(SELECT LAST_DAY(SALE_DATE) SALE_MONTH, dept_code, sales_amount,
DENSE_RANK() OVER(PARTITION BY LAST_DAY(SALE_DATE)
ORDER BY SALES_AMOUNT DESC) sale_rank
FROM sales_summary_daily
WHERE SALE_DATE >= '01-JAN-2008'
)
WHERE SALE_RANK <= 3
GROUP BY SALE_MONTH
SALE_MONTH FIRST SECOND THIRD
--------------- ----- ------ -----
31-JAN-08 North North North
29-FEB-08 North East North
30-APR-08 East North East
30-JUN-08 North North North
31-JUL-08 East North North
31-AUG-08 North East North
31-MAR-08 North North East
31-MAY-08 North North East
30-SEP-08 East North North
Again using conventional SQL/PLSQL needs more effort to make it efficient. With Oracle 11g, this is even more simplified using the pivot operator.
