Oracle SQL - Analytic Function Processing

Introduction

There are three phases in which statements containing analytic functions are processed.

All of the data retrieval for the query occurs before the analytic functions are executed. It restricts what you can do with analytic functions in a single query.

Phase1: Execute the query clauses, except

ORDER BY
SELECT
WHERE/joins
GROUP BY/HAVING

Phase 2: Execute the analytic function, occurs once for every function in the statement.

Define the partition(s)
Order the data within each partition
Define the window
Apply function

Phase3: Sort query results per the statement's ORDER BY clause.

Analytic functions cannot be used in the WHERE clause. And you cannot apply analytic functions in a HAVING clause.

The following code has error resulting from Analytic Function Placed in a WHERE Clause

SELECT     ename
      ,    job
      ,    mgr
      ,    msal
      ,    DENSE_RANK() OVER (ORDER BY msal DESC) sal_rank

FROM       emp
WHERE      (DENSE_RANK() OVER (ORDER BY msal DESC)) <= 3
ORDER BY   msal DESC;

WHERE      (DENSE_RANK() OVER (ORDER BY msal DESC)) <= 3
            *
ERROR at line 7:
ORA-30483: window  functions are not allowed here

To filter records based on an analytic function, create a subquery that uses the function and then use the resulting value to filter on.

WITH ranked_salaries AS
( SELECT     ename
        ,    job
        ,    mgr
        ,    msal
        ,    DENSE_RANK() OVER (ORDER BY msal DESC) sal_rank
  FROM       emp
 )
SELECT     ename
      ,    job
      ,    mgr
      ,    msal
      ,    sal_rank
FROM       ranked_salaries
WHERE      sal_rank <= 3
ORDER BY   msal DESC;

Demo

SQL>
SQL> drop table emp;

Table dropped.-- from  ww  w .  j av  a2s . co m

SQL> create table emp(
  2  empno      NUMBER(4)    primary key,
  3  ename      VARCHAR2(8)  not null   ,
  4  init       VARCHAR2(5)  not null   ,
  5  job        VARCHAR2(8)             ,
  6  mgr        NUMBER(4)               ,
  7  bdate      DATE         not null   ,
  8  msal       NUMBER(6,2)  not null   ,
  9  comm       NUMBER(6,2)             ,
 10  deptno     NUMBER(2)    default 10) ;
SQL>
SQL> insert into emp values(7001,'SMITH','N',  'TRAINER', 7902,date '1975-12-17',  1800 , NULL, 20);
SQL> insert into emp values(7002,'ALLEN','JAM','SALESREP',7006,date '1971-05-20',  1600, 300,   30);
SQL> insert into emp values(7003,'WARD', 'TF' ,'SALESREP',7006,date '1972-03-02',  1250, 500,   10);
SQL> insert into emp values(7004,'JACK', 'JM', 'MANAGER', 7009,date '1977-04-02',  2975, NULL,  20);
SQL> insert into emp values(7005,'BROWN','P',  'SALESREP',7006,date '1976-09-28',  1250, 1400,  30);
SQL> insert into emp values(7006,'BLAKE','R',  'MANAGER', 7009,date '1973-11-01',  2850, NULL,  10);
SQL> insert into emp values(7007,'CLARK','AB', 'MANAGER', 7009,date '1975-06-09',  2450, NULL,  10);
SQL> insert into emp values(7008,'SCOTT','DEF','TRAINER', 7004,date '1979-11-26',  3000, NULL,  20);
SQL> insert into emp values(7009,'KING', 'CC', 'DIRECTOR',NULL,date '1972-10-17',  5000, NULL,  10);
SQL> insert into emp values(7010,'BREAD','JJ', 'SALESREP',7006,date '1978-09-28',  1500, 0,     30);
SQL> insert into emp values(7011,'ADAMS','AA', 'TRAINER', 7008,date '1976-12-30',  1100, NULL,  20);
SQL> insert into emp values(7012,'JONES','R',  'ADMIN',   7006,date '1979-10-03',  8000, NULL,  30);
SQL> insert into emp values(7902,'FORD', 'MG', 'TRAINER', 7004,date '1979-02-13',  3000, NULL,  20);
SQL> insert into emp values(7934,'MARY', 'ABC','ADMIN',   7007,date '1972-01-23',  1300, NULL,  10);
SQL>
SQL>
SQL> WITH ranked_salaries AS
  2  ( SELECT     ename
  3          ,    job
  4          ,    mgr
  5          ,    msal
  6          ,    DENSE_RANK() OVER (ORDER BY msal DESC) sal_rank
  7    FROM       emp
  8   )
  9  SELECT     ename
 10        ,    job
 11        ,    mgr
 12        ,    msal
 13        ,    sal_rank
 14  FROM       ranked_salaries
 15  WHERE      sal_rank <= 3
 16  ORDER BY   msal DESC;

ENAME    JOB             MGR       MSAL   SAL_RANK
-------- -------- ---------- ---------- ----------
JONES    ADMIN          7006       8000          1
KING     DIRECTOR                  5000          2
SCOTT    TRAINER        7004       3000          3
FORD     TRAINER        7004       3000          3

SQL>

Oracle SQL - Analytic Function Processing

Introduction

Demo

Related Topic