Introduction
Analytic functions also known as window functions, compute an aggregate value over a group of related rows. Unlike traditional aggregate functions they return a result for each row in the result set and not a summary. Prior to 26ai, filtering on the results of an analytic function required the use of a CTE or subquery. With 26ai, the QUALIFY clause helps to filter data without the need for an inline view or subquery when using analytic functions. Consider the below example for illustration
Example
The example below was taken from Leetcode, database problem #185. You are tasked with finding the highest earners for a company’s departments. This is defined as ” an employee who has a salary in the top three unique salaries for that department.”
The tables and sample data for the test are created as follows
SQL> CREATE TABLE IF NOT EXISTS employee (
2 id NUMBER,
3 name VARCHAR2(255),
4 salary NUMBER,
5 departmentid NUMBER
6* );
Table EMPLOYEE created.
SQL>
SQL> CREATE TABLE IF NOT EXISTS department (
2 id NUMBER,
3 name VARCHAR2(255)
4* ) ;
Table DEPARTMENT created.
SQL> insert into Employee (id, name, salary, departmentId) values ('1', 'Joe', '85000', '1');
1 row inserted.
SQL> insert into Employee (id, name, salary, departmentId) values ('2', 'Henry', '80000', '2');
1 row inserted.
SQL> insert into Employee (id, name, salary, departmentId) values ('3', 'Sam', '60000', '2');
1 row inserted.
SQL> insert into Employee (id, name, salary, departmentId) values ('4', 'Max', '90000', '1');
1 row inserted.
SQL> insert into Employee (id, name, salary, departmentId) values ('5', 'Janet', '69000', '1');
1 row inserted.
SQL> insert into Employee (id, name, salary, departmentId) values ('6', 'Randy', '85000', '1');
1 row inserted.
SQL> insert into Employee (id, name, salary, departmentId) values ('7', 'Will', '70000', '1');
1 row inserted.
SQL> insert into Department (id, name) values ('1', 'IT');
1 row inserted.
SQL> INSERT INTO department (id, name) VALUES ('2','Sales');
1 row inserted.
SQL>
SQL> commit;
Commit complete.
Pre 26ai solution
The question expects ties to have the same “rank”, so I used the dense rank analytic function for my solution as below;
SQL> l
1 SELECT
2 department as "Department",
3 employee as "Employee",
4 salary as "Salary"
5 FROM
6 (
7 SELECT
8 d.name department
9 ,e.name employee
10 ,e.salary
11 ,dense_rank() OVER (PARTITION BY d.name ORDER BY e.salary DESC) as rnk
12 From
13 Employee e
14 INNER JOIN Department d on e.departmentid = d.id
15 )
16* WHERE rnk <= 3
SQL> /
Department Employee Salary
__________ ________ ______
IT Max 90000
IT Joe 85000
IT Randy 85000
IT Will 70000
Sales Henry 80000
Sales Sam 60000
6 rows selected.
As seen above, I am nesting my initial select with the dense_rank in a subquery and using an outer query to filter on the rnk column for values that are less than or equal to 3. Using previous oracle versions you needed to do something along these lines to filter on the results of an analytic function. It was not possible to filter directly on the analytic function.
26ai solution
With oracle AI database 26ai we can filter on the analytic function using the qualify clause. The cool thing about this is it also let’s us use the alias in the filter, ie “rnk” in this case instead of having to type out the entire expression. This is also possible for the group by and having clauses in this release as well. Using the qualify clause, my query would look something like this
SQL> SELECT
2 d.name department
3 ,e.name employee
4 ,e.salary
5 ,dense_rank() OVER (PARTITION BY d.name ORDER BY e.salary DESC) as rnk
6 From
7 Employee e
8 INNER JOIN Department d on e.departmentid = d.id
9 QUALIFY rnk <= 3
10* ;
DEPARTMENT EMPLOYEE SALARY RNK
__________ ________ ______ ___
IT Max 90000 1
IT Joe 85000 2
IT Randy 85000 2
IT Will 70000 3
Sales Henry 80000 1
Sales Sam 60000 2
This returns the results of the top earners per department correctly. However there is now an additional column in the results, which would make our solution wrong(on leetcode atleast). Luckily we can put the analytic function expression right in the qualify clause only
QL> l
1 SELECT
2 d.name department
3 ,e.name employee
4 ,e.salary
5 -- ,dense_rank() OVER (PARTITION BY d.name ORDER BY e.salary DESC) as rnk
6 From
7 Employee e
8 INNER JOIN Department d on e.departmentid = d.id
9* QUALIFY dense_rank() OVER (PARTITION BY d.name ORDER BY e.salary DESC) <= 3
SQL> /
DEPARTMENT EMPLOYEE SALARY
__________ ________ ______
IT Max 90000
IT Joe 85000
IT Randy 85000
IT Will 70000
Sales Henry 80000
Sales Sam 60000
6 rows selected.
We now get the same results as before without having to use a subquery or CTE.
Alternative to Where?
I wouldn’t quite say so myself (because I don’t know any better) but it is interesting that a query like this:
SQL> l
1 SELECT d.name, e.name
2 FROM department d
3 JOIN employee e ON d.id = e.departmentid
4* WHERE d.name = 'IT'
SQL> /
NAME NAME
____ _____
IT Joe
IT Max
IT Janet
IT Randy
IT Will
Can also be written like this:
SQL> ed
1 SELECT d.name, e.name
2 FROM department d
3 JOIN employee e ON d.id = e.departmentid
4* QUALIFY d.name = 'IT';
SQL> /
NAME NAME
____ _____
IT Joe
IT Max
IT Janet
IT Randy
IT Will
both queries returning the same result. It’s interesting but probably not its intended usage.
CONCLUSION
The qualify clause eases filtering on window functions in oracle database, a welcome feature I’m certain many a developer will appreciate. Apparently this has been available in some other RDBMSes like Teradata already, in any case it will reduce the effort to re-write code if you are moving over to oracle from a different vendor (or vice versa 🤷♂️). If you’re new to analytics there is a free course on the devgym that explains how to use them.
Thanks for reading, cheers 🎊
References:
https://medium.com/snowflake/how-qualify-works-with-in-depth-explanation-and-examples-bbde9fc742db