Many times I have thought it would be nice if there was a QUALIFY clause in Postgres!
Just would like to add that including your list, Teradata, Redshift, SAP HANA, HP Vertica, and Trino all support the QUALIFY clause.
Also it seems Postgres would be the first leading RDBMS - meaning like traditional, multipurpose RDMBS - to support QUALIFY, which would be pretty cool.
Hi all,
I'm sending a proof-of-concept patch to add support for the QUALIFY
clause in Postgres. This feature allows filtering rows after window
functions are computed, using a syntax similar to the WHERE or HAVING
clauses.
The idea for this came from a discussion and suggestion by Peter
Eisentraut (thanks, Peter!).
The `QUALIFY` clause is not part of the SQL standard, but it is
implemented by some major DBMSs, including Snowflake [1], BigQuery
[2] and DuckDB [3].
The goal is to provide a more ergonomic way to filter on window function
results without needing to nest subqueries or CTEs.
Simple example (see window.sql for more):
SELECT depname,
empno,
salary,
RANK() OVER (PARTITION BY depname ORDER BY salary DESC) AS rnk
FROM empsalary
QUALIFY rnk = 1;
Please note that this is a proof-of-concept patch, I’m still working on
determining the best locations in the code to implement each part of the
logic for QUALIFY. I'm just sending this WIP to collect feedback and then
continue to work on the feature. Additionally, the current patch does not
handle yet expressions using AND/OR when referencing multiple window
function aliases (e.g., QUALIFY rnk = 1 AND rnk2 = 2).
Thoughts?
[1] https://docs.snowflake.com/en/sql-reference/constructs/qualify
[2] https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#qualify_clause
[3] https://duckdb.org/docs/stable/sql/query_syntax/qualify.html
--
Matheus Alcantara
