Analytical Functions

Find more oracle material at http://download.35mb.com/kvreddy83

Enter your search terms

Submit search form

Oracle Analytic Functions




Analytic functions were introduced in Release 2 of 8i and simplify greatly the means by which pivot reports and OLAP queries can be computed in straight, non-procedural SQL. Prior to the introduction of analytic functions, complex reports could be produced in SQL by complex self-joins, sub-queries and inline-views but these were resource-intensive and very inefficient. Furthermore, if a question to be answered was too complex, it could be written in PL/SQL, which
by its very nature is usually less efficient than a single SQL statement.

There are three types of SQL extensions that fall under the banner of "analytic functions" though the first could be said to provide "analytic functionality" rather than actually be analytic
functions:

new grouping of resultsets through extensions to the GROUP BY clause (ROLLUP and CUBE)

new analytic functions themselves; and TOP-N analysis (largely enabled by the analytic functions).

Practical Examples

AVG
Returns a running average AVG(DISTINCT | ALL <_expression>) OVER
(analytic clause)
CREATE TABLE vote_count (
submit_date DATE NOT NULL,
num_votes NUMBER NOT NULL);

INSERT INTO vote_count VALUES (TRUNC(SYSDATE)-4, 100);
INSERT INTO vote_count VALUES (TRUNC(SYSDATE)-3, 150);
INSERT INTO vote_count VALUES (TRUNC(SYSDATE)-2, 75);
INSERT INTO vote_count VALUES (TRUNC(SYSDATE)-3, 25);
INSERT INTO vote_count VALUES (TRUNC(SYSDATE)-1, 50);
COMMIT;

SELECT * FROM vote_count;

SELECT submit_date, num_votes, TRUNC(AVG(num_votes)
OVER(ORDER BY submit_date ROWS UNBOUNDED PRECEDING)) AVG_VOTE_PER_DAY
FROM vote_count
ORDER BY submit_date;

SELECT submit_date, num_votes, TRUNC(AVG(num_votes)
OVER(PARTITION BY submit_date ORDER BY submit_date ROWS UNBOUNDED
PRECEDING)) AVG_VOTE_PER_DAY
FROM vote_count
ORDER BY submit_date;

Analytic functions were introduced in Release 2 of 8i and simplify greatly the means by which pivot reports and OLAP queries can be computed in straight, non-procedural SQL. Prior to the introduction of analytic functions, complex reports could be produced in SQL by complex self-joins, sub-queries and inline-views but these were resource-intensive and very inefficient. Furthermore, if a question to be answered was too complex, it could be written in PL/SQL, which by its very nature is usually less efficient than a single SQL statement.

There are three types of SQL extensions that fall under the banner of "analytic functions" though the first could be said to provide "analytic functionality" rather than actually be analytic functions:
new grouping of resultsets through extensions to the GROUP BY clause (ROLLUP and CUBE)
new analytic functions themselves; and
TOP-N analysis (largely enabled by the analytic functions).
Each of these will be dealt with in turn.
"Business-Savvy SQL" by Ganesh Variar details some of the improvements made in Oracle 9i to analytic functions, including the ability to use analytic functions in PL/SQL, the introduction of the new GROUPING SETS feature, and the new ability to supply hypothetical arguments to the RANKing function, to provide 'what if?' analysis.
Lastly, with Oracle Database 10g, the analytical capabilities of SQL have been further extended through the introduction of the SQL MODEL clause. According to Jonathan Gennick in the OTN article "Announcing The New Model":
You're writing a query that returns computed column values. As input to your calculations, you need values from multiple rows. What do you do? This is actually a fairly common problem. Solutions to it often involve inscrutable combinations of self-joins and subqueries.
Oracle Database 10g enables a new approach. Using the SELECT statement's new MODEL clause, you can treat relational data as a multidimensional array to which you can apply spreadsheet-like calculations. The result is a query that is easier to develop, easier to understand, and easier to modify.


AVG
AVG(DISTINCT | ALL ) OVER (analytic clause)
CREATE TABLE vote_count (
submit_date DATE NOT NULL,
num_votes NUMBER NOT NULL);

INSERT INTO vote_count VALUES (TRUNC(SYSDATE)-4, 100);
INSERT INTO vote_count VALUES (TRUNC(SYSDATE)-3, 150);
INSERT INTO vote_count VALUES (TRUNC(SYSDATE)-2, 75);
INSERT INTO vote_count VALUES (TRUNC(SYSDATE)-3, 25);
INSERT INTO vote_count VALUES (TRUNC(SYSDATE)-1, 50);
COMMIT;

SELECT * FROM vote_count;

SELECT submit_date, num_votes, TRUNC(AVG(num_votes)
OVER(ORDER BY submit_date ROWS UNBOUNDED PRECEDING)) AVG_VOTE_PER_DAY
FROM vote_count
ORDER BY submit_date;

SELECT submit_date, num_votes, TRUNC(AVG(num_votes)
OVER(PARTITION BY submit_date ORDER BY submit_date ROWS UNBOUNDED PRECEDING)) AVG_VOTE_PER_DAY
FROM vote_count
ORDER BY submit_date;

CORR
CORR(, ) OVER ()
conn sh/sh

SELECT t.calendar_month_number,
CORR (SUM(s.amount_sold), SUM(s.quantity_sold))
OVER (ORDER BY t.calendar_month_number) AS CUM_CORR
FROM sales s, times t
WHERE s.time_id = t.time_id AND calendar_year = 1998
GROUP BY t.calendar_month_number;

COUNT
COUNT(<*, [DISTINCT | ALL] >) OVER ()
SELECT submit_date, num_votes, TRUNC(COUNT(num_votes)
OVER(ORDER BY submit_date ROWS UNBOUNDED PRECEDING)) AS DAY_COUNT
FROM vote_count
ORDER BY submit_date;

SELECT submit_date, COUNT(*)
OVER(PARTITION BY submit_date ORDER BY submit_date
ROWS UNBOUNDED PRECEDING) NUM_RECS
FROM vote_count;

COVAR_POP
COVAR_POP(, ) OVER ()
conn oe/oe

SELECT job_id,
COVAR_POP(SYSDATE-hire_date, salary) AS covar_pop,
COVAR_SAMP(SYSDATE-hire_date, salary) AS covar_samp
FROM employees
WHERE department_id in (50, 80)
GROUP BY job_id;

COVAR_SAMP
COVAR_SAMP(, ) OVER ()
See COVAR_POP Demo above

CUME_DIST
CUME_DIST() OVER ( )
conn oe/oe

SELECT job_id, last_name, salary, CUME_DIST()
OVER (PARTITION BY job_id ORDER BY salary) AS cume_dist
FROM employees
WHERE job_id LIKE 'PU%';

DENSE_RANK
DENSE_RANK() OVER ( )
conn oe/oe

SELECT d.department_name, e.last_name, e.salary, DENSE_RANK()
OVER (PARTITION BY e.department_id ORDER BY e.salary) AS DENSE_RANK
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND d.department_id IN (30, 60);

FIRST
SELECT KEEP
(DENSE_RANK FIRST ORDER BY [ NULLS )
OVER (PARTITION BY )
FROM
GROUP BY ;
conn oe/oe

SELECT last_name, department_id, salary,
MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct)
OVER (PARTITION BY department_id) "Worst",
MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct)
OVER (PARTITION BY department_id) "Best"
FROM employees
WHERE department_id IN (30, 60)
ORDER BY department_id, salary;

FIRST_VALUE
FIRST_VALUE( [IGNORE NULLS])
OVER ()
conn oe/oe

SELECT last_name, salary, hire_date, FIRST_VALUE(hire_date)
OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lv
FROM (SELECT * FROM employees WHERE department_id = 90
ORDER BY hire_date);

LAG
LAG(, , )
OVER ([] )
conn oe/oe

SELECT last_name, hire_date, salary,
LAG(salary, 1, 0) OVER (ORDER BY hire_date) AS PREV_SAL
FROM employees
WHERE job_id = 'PU_CLERK';

LAST
KEEP (DENSE_RANK LAST ORDER BY
( NULLS )
See FIRST Demo above

LAST_VALUE
LAST_VALUE ( IGNORE NULLS) OVER ()
conn oe/oe

SELECT last_name, salary, hire_date, FIRST_VALUE(hire_date)
OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lv
FROM (SELECT * FROM employees WHERE department_id = 90
ORDER BY hire_date);

LEAD
LEAD()
[()]
OVER ()
SELECT submit_date, num_votes,
LEAD(num_votes, 1, 0) OVER (ORDER BY submit_date) AS NEXT_VAL
FROM vote_count;

MAX
MAX ( expression) OVER ()
conn oe/oe

SELECT manager_id, last_name, salary
FROM (
SELECT manager_id, last_name, salary,
MAX(salary) OVER (PARTITION BY manager_id) AS rmax_sal
FROM employees)
WHERE salary = rmax_sal;

MIN
MIN ( expression) OVER ()
conn oe/oe

SELECT manager_id, last_name, salary
FROM (
SELECT manager_id, last_name, salary,
MAX(salary) OVER (PARTITION BY manager_id) AS rmax_sal
FROM employees)
WHERE salary = rmax_sal;

NTILE
NTILE () OVER ([query_partition_clause] )
conn oe/oe

SELECT last_name, salary,
NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees
WHERE department_id = 100;

PERCENT_RANK
PERCENT_RANK() OVER ( )
conn oe/oe

SELECT department_id, last_name, salary, PERCENT_RANK()
OVER (PARTITION BY department_id ORDER BY salary DESC) AS pr
FROM employees
ORDER BY pr, salary;

PERCENTILE_CONT
PERCENTILE_CONT() WITHIN GROUP (ORDER BY [ASC | DESC]) OVER ()
conn oe/oe

SELECT last_name, salary, department_id,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary DESC)
OVER (PARTITION BY department_id) PCT_CONT, PERCENT_RANK()
OVER (PARTITION BY department_id ORDER BY salary DESC) PCT_RANK
FROM employees
WHERE department_id IN (30, 60);

PERCENTILE_DISC
PERCENTILE_DISC() WITHIN GROUP (ORDER BY )
conn oe/oe

col cume_dist format 9.999

SELECT last_name, salary, department_id,
PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salary DESC)
OVER (PARTITION BY department_id) PCT_DISC,
CUME_DIST() OVER (PARTITION BY department_id
ORDER BY salary DESC) CUME_DIST
FROM employees
WHERE department_id IN (30, 60);

RANK
RANK() OVER ( ORDER BY )
conn oe/oe

SELECT department_id, last_name, salary, commission_pct,
RANK() OVER (PARTITION BY department_id
ORDER BY salary DESC, commission_pct) "Rank"
FROM employees
WHERE department_id = 80;

RATIO_TO_REPORT
RATIO_TO_REPORT() OVER ()
conn oe/oe

SELECT last_name, salary, RATIO_TO_REPORT(salary) OVER () AS RR
FROM employees
WHERE job_id = 'PU_CLERK';

REGR_ (Linear Regression) Functions
Generic Syntax FUNCTION_NAME (,) OVER ()
REGR_AVGX See REGR_AVGY Demo

SELECT job_id, employee_id ID, salary,
REGR_SLOPE(SYSDATE-hire_date, salary)
OVER (PARTITION BY job_id) slope,
REGR_INTERCEPT(SYSDATE-hire_date, salary)
OVER (PARTITION BY job_id) intcpt,
REGR_R2(SYSDATE-hire_date, salary)
OVER (PARTITION BY job_id) rsqr,
REGR_COUNT(SYSDATE-hire_date, salary)
OVER (PARTITION BY job_id) count,
REGR_AVGX(SYSDATE-hire_date, salary)
OVER (PARTITION BY job_id) avgx,
REGR_AVGY(SYSDATE-hire_date, salary)
OVER (PARTITION BY job_id) avgy
FROM employees
WHERE department_id in (50, 80)
ORDER BY job_id, employee_id;
REGR_AVGY See REGR_AVGX Demo

SELECT job_id,
REGR_AVGY(SYSDATE-hire_date, salary) avgy,
REGR_AVGX(SYSDATE-hire_date, salary) avgx
FROM employees
WHERE department_id in (30, 50)
GROUP BY job_id;
REGR_COUNT See REGR_AVGX Demo

SELECT job_id,
REGR_COUNT(SYSDATE-hire_date, salary) count
FROM employees
WHERE department_id in (30, 50)
GROUP BY job_id;
REGR_INTERCEPT See REGR_AVGX Demo

SELECT job_id,
REGR_SLOPE(SYSDATE-hire_date, salary) slope,
REGR_INTERCEPT(SYSDATE-hire_date, salary) intercept
FROM employees
WHERE department_id in (50,80)
GROUP BY job_id
ORDER BY job_id;
REGR_R2 See REGR_AVGX Demo

SELECT job_id, REGR_R2(SYSDATE-hire_date, salary) Regr_R2
FROM employees
WHERE department_id IN (50, 80)
GROUP by job_id;
REGR_SLOPE See REGR_AVGX Demo
See REGR_INTERCEPT Demo
REGR_SXX SELECT job_id,
REGR_SXY(SYSDATE-hire_date, salary) regr_sxy,
REGR_SXX(SYSDATE-hire_date, salary) regr_sxx,
REGR_SYY(SYSDATE-hire_date, salary) regr_syy
FROM employees
WHERE department_id in (50, 80)
GROUP BY job_id
ORDER BY job_id;
REGR_SXY See REGR_SXX Demo
REGR_SYY See REGR_SXX Demo

ROW_NUMBER

ROW_NUMBER() OVER ( ORDER BY )
CREATE TABLE test (
id NUMBER(1),
degrees NUMBER(3));

INSERT INTO test VALUES (0,235);
INSERT INTO test VALUES (0,276);
INSERT INTO test VALUES (1,211);
INSERT INTO test VALUES (1,250);
INSERT INTO test VALUES (1,255);
INSERT INTO test VALUES (2,55);
INSERT INTO test VALUES (2,277);
INSERT INTO test VALUES (2,69);
INSERT INTO test VALUES (3,25);
INSERT INTO test VALUES (3,166);
INSERT INTO test VALUES (3,262);
INSERT INTO test VALUES (4,47);
INSERT INTO test VALUES (4,238);
INSERT INTO test VALUES (4,40);
COMMIT;

SELECT * FROM test;

-- choose the starting cell
SELECT id, degrees
FROM (
SELECT id, degrees, (360 - degrees) d360,
row_number() OVER(PARTITION BY id
ORDER BY CASE
WHEN (degrees < 360 - degrees) THEN degrees
ELSE 360 - degrees
END) rn
FROM test) t
WHERE rn = 1;

-- order the rest clockwise
SELECT *
FROM (
SELECT t.id, t.degrees,
row_number() over(partition by t.id
ORDER BY CASE
WHEN (t.degrees < starting_cell.degrees) THEN t.degrees + 360
ELSE t.degrees
END) rn
FROM test t
JOIN (
SELECT id, degrees, (360 - degrees) d360,
row_number() over(partition by id
ORDER BY CASE
WHEN (degrees < 360 - degrees) THEN degrees
ELSE 360 - degrees
END) rn
FROM test) starting_cell
ON t.id = starting_cell.id
WHERE starting_cell.rn=1)t
ORDER BY id, rn

STDDEV
STDDEV([DISTINCT | ALL] ) OVER ()
conn oe/oe

col stddev format 99999.999

SELECT last_name, salary,
STDDEV(salary) OVER (ORDER BY hire_date) "StdDev"
FROM employees
WHERE department_id = 30;

STDDEV_POP
STDDEV_POP() OVER ()
conn oe/oe

SELECT department_id, last_name, salary,
STDDEV_POP(salary) OVER (PARTITION BY department_id) AS pop_std
FROM employees;

STDDEV_SAMP
STDDEV_SAMP() OVER ()
conn oe/oe

SELECT department_id, last_name, hire_date, salary,
STDDEV_SAMP(salary) OVER (PARTITION BY department_id
ORDER BY hire_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_sdev
FROM employees;

SUM

CREATE TABLE vote_count (
submit_date DATE NOT NULL,
vote_count NUMBER NOT NULL);

INSERT INTO vote_count VALUES (TRUNC(SYSDATE)-4, 100);
INSERT INTO vote_count VALUES (TRUNC(SYSDATE)-3, 150);
INSERT INTO vote_count VALUES (TRUNC(SYSDATE)-2, 75);
INSERT INTO vote_count VALUES (TRUNC(SYSDATE)-3, 25);
INSERT INTO vote_count VALUES (TRUNC(SYSDATE)-1, 50);
COMMIT;

SELECT * FROM vote_count;

SELECT submit_date, vote_count, SUM(vote_count)
OVER(ORDER BY submit_date ROWS UNBOUNDED PRECEDING) TOT_VOTE
FROM vote_count
ORDER BY submit_date;

VAR_POP
VAR_POP() OVER ()
conn sh/sh

SELECT t.calendar_month_desc,
VAR_POP(SUM(s.amount_sold))
OVER (ORDER BY t.calendar_month_desc) "Var_Pop",
VAR_SAMP(SUM(s.amount_sold))
OVER (ORDER BY t.calendar_month_desc) "Var_Samp"
FROM sales s, times t
WHERE s.time_id = t.time_id AND t.calendar_year = 2001
GROUP BY t.calendar_month_desc;

VAR_SAMP
VAR_SAMP() OVER ()
See VAR_POP Demo above

VARIANCE
VARIANCE([DISTINCT | ALL] ) OVER ()
SELECT last_name, salary,
VARIANCE(salary) OVER (ORDER BY hire_date) AS VARIANCE
FROM employees
WHERE department_id = 30;