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
v0-0001-QUALIFY-clause.patch
Description: Binary data
