As Published In
Oracle Magazine
March/April 2005

TECHNOLOGY: DBA


Find Answers Faster

By Jonathan Gennick and Anthony Molinaro

Analytic functions provide powerful ways to view your data.

It's 5:00 p.m. You're tired, hungry, and ready to go home for the day, when your manager calls you into his office to request a new report he needs "right now."

"Find the highest salary in each department, and give me a list of employees earning that amount. Can you do that before you leave? Just that one thing?"

You've heard the "just that one thing" line before. You phone home to say you'll be late for dinner, and then you get back to your keyboard to think about that query. "Find the highest salary in each department" is easy enough. A simple GROUP BY provides that information: 

SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id;


But you also need to list the highest-paid employees in each department. Perhaps a subquery is what you need. Correlated or uncorrelated? It's late, and you really don't want to think through subqueries right now.

Fortunately, there is an easier way. Although you can solve this query problem by using a subquery, you can solve it much more easily by using the analytic function syntax that Oracle has supported since the release of Oracle8i Database. Listing 1 shows the analytic function solution and some of its output. (The examples in this article use the hr sample schema.) Not only is the solution in Listing 1 easier to think through than a subquery solution but verifying at a glance that it's correct is also easier, in our opinion.

Code Listing 1: Listing the highest salary and highest-paid employee 

SELECT department_id dept, first_name || ' ' || last_name name, salary
FROM ( 
SELECT department_id, first_name, last_name,
       MAX(salary) OVER (PARTITION BY department_id) dept_max_sal, salary 
FROM employees e
) WHERE salary = dept_max_sal;

      DEPT       NAME                       SALARY
  ---------     ------------------------    --------
        10      Jennifer Whalen             4400
        20      Michael Hartstein           13000
        30      Den Raphaely                11000
...


The query in Listing 1 uses a two-step process to solve our reporting problem:

1. The inner query—in boldface text in Listing 1—generates a list of employees. Each row returned by this query contains a salary column with the employee's salary and also a dept_max_sal column with the maximum salary paid in the employee's department (more on the dept_max_sal column in a moment).

2. The outer query simply compares salary with dept_max_sal , returning only those rows where the two amounts match.

The key to this solution is the analytic function call that computes the maximum salary for each employee's department: 

MAX(salary) OVER (
   PARTITION BY department_id
   )


MAX is an aggregate function, and aggregate functions serve double duty as analytic functions. When you see the OVER keyword, you know you are dealing with the analytic version of a function. In this case, the PARTITION BY department_id clause causes MAX to return the maximum salary for each department_id value. Think of department_id as defining a window of rows over which MAX is computed. The GROUP BY solution to the first part of your manager's request collapses the query results into one row per group, but the analytic solution returns the maximum salary value for each detail row. Thus, you have both detail and summary data available at the same time, making it easy to compare each employee's salary with the maximum for that person's department.

Analytic functions are powerful, and they make possible some elegant solutions to problems that previously were difficult to solve in SQL. Your manager's request highlights one problem scenario that can benefit from analytic syntax:

You need access to both detail and summary data at the same time.

Aside from being elegant, analytic function solutions tend to be less brittle and more changeable than their nonanalytic counterparts. Suppose you've taken the report from Listing 1 and handed it to your manager, only to be told, "You know, what I really need is for you to find the maximum salary for each job and then report on the employees in each position that are earning that maximum. Second, please include the maximum department salary for each employee you list."

The first part of this new request is quite easy to carry out. Simply begin with Listing 1 and change all occurrences of department_id to job_id . Also change the column aliases; for example, change dept_max_sal to job_max_sal . The revised query and solution to the first part of your manager's latest request are in Listing 2.

Code Listing 2: Listing the highest-paid employees in each job 

SELECT job_id job, first_name || ' ' || last_name name, salary
FROM ( 
SELECT job_id, first_name, last_name,
       MAX(salary) OVER (PARTITION BY job_id) job_max_sal, salary 
       FROM employees e
) WHERE salary = job_max_sal;


The second part of this new request is also straightforward. All you need to do is add the following column to the inner query and include it in the outer query's select list: 

 
 MAX(salary) OVER (
    PARTITION BY department_id
    ) dept_max_sal


Listing 3 shows the final query for this new request from your manager, along with some sample output. For good measure, Listing 3 also selects the department_id column for each employee. The query in Listing 3 highlights another problem scenario to which analytic syntax can be applied:

You need to summarize data over two or more dimensions at the same time.

Code Listing 3 Same as Listing 2, but with departmental maximun salary 

                               
SELECT department_id dept, job_id job, first_name || ' ' || last_name name, 
      salary, dept_max_sal
FROM (
SELECT department_id, job_id, first_name, last_name,
       MAX(salary) OVER (
          PARTITION BY job_id) job_max_sal, salary,
       MAX(salary) OVER (
          PARTITION BY department_id) dept_max_sal
FROM employees e
) WHERE salary = job_max_sal;

      DEPT     JOB             NAME             SALARY       DEPT_MAX_SAL
 ----------    --------------  --------------   --------     ------------
       110     AC_ACCOUNT      William Gietz        8300         12000
       110     AC_MGR          Shelley Higgins     12000         12000
        10     AD_ASST         Jennifer Whalen      4400          4400

                            


The amount of effort required to go from Listing 2 to Listing 3 is minimal.

Happy that it was so easy to get through "that one thing," you present the report to your manager, who looks it over and then (of course) asks, "Would it be possible to report on the employees with the top five salaries in each department? I really do need to look by department, not by job. And I'd like the salaries to be ranked."

Walking away and muttering under your breath, you stop and remember that there is a RANK function that assigns ranks to rows within a window. You write the query in Listing 4 and get ready to give the results to your manager. The part of Listing 4 that generates the ranking is the following function call: 

RANK() OVER (
   PARTITION BY department_id 
   ORDER BY salary DESC
) dept_rank


Code Listing 4: Listing the employees using RANK 

                               
SELECT department_id dept, job_id, first_name || ' ' || last_name name,
       salary, dept_rank
FROM (
SELECT department_id, job_id, first_name, last_name, salary,
       RANK() OVER (
         PARTITION BY department_id 
         ORDER BY salary DESC
       ) dept_rank
  FROM employees
) WHERE dept_rank <= 5
  ORDER BY dept, dept_rank DESC;

      DEPT     JOB_ID            NAME                 SALARY      DEPT_RANK
 ---------     ---------         ----------------   --------    ------------
        10     AD_ASST           Jennifer Whalen      4400               1
        20     MK_REP            Pat Fay              6000               2
        20     MK_MAN            Michael Hartstein    13000              1
...
        60     IT_PROG           Diana Lorentz        4200               5
        60     IT_PROG           David Austin         4800               3
        60     IT_PROG           Valli Pataballa      4800               3
        60     IT_PROG           Bruce Ernst          6000               2
        60     IT_PROG           Alexander Hunold     9000               1

                            


Here is, conceptually, how this RANK function call is evaluated:

1. Rows are grouped by department ( PARTITION BY department_id ).
2. Within each group, rows are sorted in descending order by salary ( ORDER BY salary DESC ).
3. Each row in each group is given a number to indicate its rank in the group.

There's no argument to RANK , because the value returned is a function of how the rows are ordered in preparation for ranking. RANK returns 1 for the topmost row, 2 for the next row, and so forth. The ORDER BY clause is mandatory for use of any of the analytic ranking functions, because you use it to specify the basis for ranking. Keep in mind that the ORDER BY for ranking the employees by salary is not the same as the final ORDER BY that ranks the entire result set. In Listing 4, the final result set is in ascending order by department (the dept column) and then by descending order of salary rank. The PARTITION BY clause in Listing 4 causes separate ranking of each department's employees.

Listing 4 highlights yet another problem scenario for which you should consider an analytic solution:

You need to assign a numerical rank to rows as a result of sorting them by one or more columns.

You put on your coat and present the report to your manager, who looks it over and seems quite pleased until ... "Hey, what's with the ranking for department 60?" Examining the report more closely, you notice that employees David Austin and Valli Pataballa have the same salary and are tied for the third-highest in the department. The next employee, Diana Lorentz, is ranked fifth, due to the tie of the previous two employees' salaries. That is the way RANK works. Whenever n rows are tied, n- 1 ranks are skipped, so that the next row's ranking will be what it would have been without the tie. Your manager then says, "So, I don't want to see any gaps in the rankings, OK?"

Standing there, you recall the DENSE_RANK function, for just such a request.

You go back to your desk and, making a small adjustment to the query, come up with Listing 5. You race back to your manager's office and present the report. Your manager looks it over and is pleased, "This is perfect; thank you very much." But as you are leaving, he asks, "For each employee in this report, do the rankings correspond to when the employee was hired? I assume that if one employee earns more than another, that employee has most likely been here longer. The report confirms that, right?"

Code Listing 5: Using DENSE_RANK instead of RANK 

                               
SELECT department_id dept, job_id, first_name || ' ' || last_name name,
       salary, dept_rank
  FROM (
SELECT department_id, job_id, first_name, last_name, salary,
       DENSE_RANK() OVER (
          PARTITION BY department_id ORDER BY salary DESC) dept_rank
  FROM employees
) WHERE dept_rank <= 5
  ORDER BY dept, dept_rank DESC;

      DEPT     JOB_ID           NAME                 SALARY      DEPT_RANK
 ----------    ----------       ----------------   --------    ------------
        10     AD_ASST          Jennifer Whalen        4400               1
        20     MK_REP           Pat Fay                6000               2
        20     MK_MAN           Michael Hartstein     13000               1
...
        60     IT_PROG          Diana Lorentz          4200               4
        60     IT_PROG          David Austin           4800               3
        60     IT_PROG          Valli Pataballa        4800               3
        60     IT_PROG          Bruce Ernst            6000               2
        60     IT_PROG          Alexander Hunold       9000               1

                            


Stomach growling, you go back to your cube one more time.

To answer your manager's query, you will need to be able to compare the salary of each employee with that of the employee hired immediately afterward. You could write a self-join for this purpose, but you're too tired and hungry to want to think through doing that. Fortunately, you recall that the LAG and LEAD functions let you look at the preceding and following rows. After giving the matter a few moments' thought, you come up with the query in Listing 6, which brings us to our final problem scenario for which analytic functions provide a solution:

You need to compare values that lie in separate rows.

Code Listing 6: Using LEAD 

                               
SELECT department_id dept, job_id, first_name || ' ' || last_name name,
       salary, dept_rank, hire_date,
       CASE WHEN salary < LEAD(salary) OVER(
                          PARTITION BY department_id ORDER BY hire_date) 
            THEN 'No'
            ELSE 'Yes'
       END seniority_salary 
  FROM (
SELECT department_id,
       job_id, first_name, last_name, hire_date, salary,
       DENSE_RANK() OVER(
          PARTITION BY department_id ORDER BY salary DESC) dept_rank
  FROM employees
) WHERE dept_rank <= 5
  ORDER BY dept, dept_rank DESC;

 DEPT     JOB_ID         NAME                SALARY    DEPT_RANK    HIRE_DATE     SEN
------    ----------     ------------------- --------  ----------   ------------  ----
    10      AD_ASST        Jennifer Whalen      4400        1          17-SEP-87   Yes
    20      MK_REP         Pat Fay              6000        2          17-AUG-97   Yes
    20      MK_MAN         Michael Hartstein   13000        1          17-FEB-96   Yes
    30      PU_CLERK       Guy Himuro           2600        5          15-NOV-98   Yes
    30      PU_CLERK       Sigal Tobias         2800        4          24-JUL-97    No
    30      PU_CLERK       Shelli Baida         2900        3          24-DEC-97   Yes
    30      PU_CLERK       Alexander Khoo       3100        2          18-MAY-95   Yes
    30      PU_MAN         Den Raphaely        11000        1          07-DEC-94   Yes
...

                            


Key to your solution is the following function in the outermost SELECT list in Listing 6: 

LEAD(salary) OVER(
   PARTITION BY department_id 
   ORDER BY hire_date) 


For the LAG and LEAD functions to work, you must impose order on the rows you are working with. Otherwise there's no sense of "preceding" or "following" among the results. Thus, as with RANK and DENSE_RANK , the ORDER BY clause is mandatory for the LAG and LEAD functions. What's great about analytic syntax is that the order you choose for one function is completely independent of the order you choose for another. Thus, your call to LEAD looks at the next row sorted by hire_date , whereas your call to DENSE_RANK still ranks rows by salary.

The CASE expression in Listing 6 compares each employee's salary with that of the next employee hired ( ORDER BY hire_date ) within the same department. Anytime the newer employee has a higher salary, the expression returns 'No' . Your manager can tell at a glance whenever salary progression in a department does not reflect strict seniority.

Anticipating your manager's likely next request, you decide to enhance your query even further to return the answer to the following question: "How does each employee's salary compare to the average salary of all employees hired within a one-year moving window centered on that one employee?

To answer this question, you need a moving average. In effect, you need a one-year-wide partition that is redefined for each employee row the query returns. This is another problem scenario to which you can apply analytic syntax:

You need the results of an aggregate function computed over a moving window.

To generate the one-year moving average you seek, you add the following function call to your query: 

ROUND(AVG(salary) OVER (
   ORDER BY hire_date
   RANGE BETWEEN 
      hire_date - ADD_MONTHS(
         hire_date,-6) PRECEDING
   AND 
      ADD_MONTHS(hire_date,6) 
         - hire_date FOLLOWING)
   ) moving_average


The key aspects of this analytic function call are as follows:

1. The RANGE BETWEEN clause defines a moving window that is computed anew for each row the query returns.

2. The ORDER BY hire_date clause specifies that hire_date is to be the basis for the moving window.

Next Steps


READ
more about analytics
Oracle Database Data Warehousing Guide

 "Analytic Magic"

3. The two expressions in the RANGE BETWEEN clause, one for PRECEDING and one for FOLLOWING , will both yield values of approximately 180. The numbers will vary somewhat, depending on the current hire_date , because the number of days in a month varies.
When the query is executed, the database engine subtracts the PRECEDING value from hire_date to determine the beginning of the moving window. Likewise, the database adds the FOLLOWING value to hire_date to determine the end of the moving window. This subtraction and addition is done for each row returned by the query.

4. There is no PARTITION BY clause, because you want the moving average to consider all salaries within the window. You could add PARTITION BY department_id if you wanted each moving average to consider only employees in the current department.

5. The ROUND function rounds the result to an integer.

Listing 7 shows the final query, with some sample results. Confident that this is the last time, you present the report to your manager. He is pleased with the report and your initiative. Happily, it's only 5:30 p.m. You've done all this work in a mere 30 minutes. You'll be home for dinner after all.

Code Listing 7: Adding a 12-month, moving-average 

                               
SELECT department_id dept, job_id, first_name || ' ' || last_name name,
       salary, dept_rank, hire_date,
       CASE WHEN salary < LEAD(salary) OVER(
                          PARTITION BY department_id ORDER BY hire_date) 
            THEN 'No'
            ELSE 'Yes'
       END seniority_salary, moving_average
  FROM (
SELECT department_id, job_id, first_name, last_name, hire_date, salary,
       DENSE_RANK() OVER(PARTITION BY department_id ORDER BY salary DESC) dept_rank,
       ROUND(AVG(salary) OVER (
         ORDER BY hire_date
         RANGE BETWEEN hire_date - ADD_MONTHS(hire_date,-6) PRECEDING
         AND ADD_MONTHS(hire_date,6) - hire_date FOLLOWING)
         ) moving_average
  FROM employees
) WHERE dept_rank <= 5
  ORDER BY dept,dept_rank DESC;

 DEPT   JOB_ID       NAME              SALARY   DEPT_RANK   HIRE_DATE   SEN    MOVING_AVE
 ----   ----------   ----------------- -------- ---------- ------------ ----  - ---------
  10    AD_ASST      Jennifer Whalen    4400            1  17-SEP-87   Yes         14200
  20    MK_REP       Pat Fay            6000            2  17-AUG-97   Yes          6146
  20    MK_MAN       Michael Hartstein 13000            1  17-FEB-96   Yes          7550
  30    PU_CLERK     Guy Himuro         2600            5  15-NOV-98   Yes          4244
  30    PU_CLERK     Sigal Tobias       2800            4  24-JUL-97    No          6214
  30    PU_CLERK     Shelli Baida       2900            3  24-DEC-97   Yes          5427
  30    PU_CLERK     Alexander Khoo     3100            2  18-MAY-95   Yes          5820
  30    PU_MAN       Den Raphaely      11000            1  07-DEC-94   Yes          8867
...

                            

Jonathan Gennick (Jonathan@Gennick.com) is an experienced Oracle DBA and Oracle Certified Professional living on Michigan's Upper Peninsula. Gennick wrote SQL Pocket Reference and recently revised his two books on Oracle SQL*Plus, all published by O'Reilly Media, Inc. Anthony Molinaro is a developer, certified DBA, and SQL aficionado who works for Wireless Generation. He is also author of O'Reilly Media, Inc.'s upcoming SQL Cookbook.


Send us your comments