The Window functions calculates an aggregate value over a group of rows and return a single result for each row within that group. However, it is important to note that if you attempt to filter query results based on the window function in the WHERE clause of same query, it will result in an error.
2. Problem with Filtering Query Results of Window Functions
For example, let us consider we want to find the details of employee with highest salary in each department.
The below query will throw an error because filtering on window functions is not supported in the WHERE clause.
SELECT Employee_Id, Name, Department_Id, Salary, RANK() OVER(PARTITION BY Department_Id ORDER BY Salary DESC) AS SAL_RANK FROM EMPLOYEE WHERE SAL_RANK = 1;
The reason for the error is that the internal evaluation of Window Functions takes place only after the evaluation of the WHERE clause.
3. Two-Step Solution for Filtering Query Results of Window Functions
The Window functions are supported in SELECT and ORDER BY clauses. Therefore, one way to filter the results of a query based on a window function is to encapsulate the query within another statement and filter based on the field that uses the window function.
The following query finds the employee with highest salary in each department.
SELECT * FROM( SELECT Employee_Id, Name, Department_Id, Salary, RANK() OVER(PARTITION BY Department_Id ORDER BY Salary DESC) AS SAL_RANK FROM EMPLOYEE ) WHERE SAL_RANK =1;
This is a two-step process to filter the results of a query which uses a window function. To avoid this additional step, Snowflake introduced QUALIFY clause.
4. QUALIFY Clause in Snowflake
QUALIFY clause in a SELECT statement allows you to filter query results of Window functions within the same query. What QUALIFY does with window functions is analogous to what HAVING does with GROUP BY clause.
The QUALIFY clause requires at least one window function to be specified in at least one of the following clauses of the SELECT statement:
- The SELECT column list.
- The filter predicate of the QUALIFY clause.
4.1. Window Function in SELECT Column List
QUALIFY can be used to reference window functions that are in the SELECT column list to filter the records.
The following query uses QUALIFY to reference the window function SAL_RANK to find the employee details with highest salary in each department.
SELECT Employee_Id, Name, Department_Id, Salary, RANK() OVER(PARTITION BY Department_Id ORDER BY Salary DESC) AS SAL_RANK FROM EMPLOYEE QUALIFY SAL_RANK = 1;
4.2. Window Function in Filter Predicate of QUALIFY
The Window functions can be directly specified in the QUALIFY clause to filter records based on their result.
The following query uses Window function in the filter predicate of QUALIFY clause to find the employee details with highest salary in each department.
SELECT Employee_Id, Name, Department_Id, Salary FROM EMPLOYEE QUALIFY RANK() OVER(PARTITION BY Department_Id ORDER BY Salary DESC) = 1;
While it is observed that using QUALIFY does not necessarily guarantee a performance boost as it is evaluated only after a WINDOW function, it simplifies queries that require filtering on the result of window functions. So next time you are working with window functions, don’t forget to explore the QUALIFY clause to simply your queries.
Subscribe to our Newsletter !!
Snowflake provides GET_DDL Function using which DDL of database objects like tables, views, procedures etc., can be extracted.
Learn how to find and kill long running queries in Snowflake using the QUERY_HISTORY table functions available under Information Schema.
The GROUP BY ALL clause is similar to GROUP BY clause except that it automatically include all non-aggregate items in the SELECT clause for grouping.