QUALIFY clause #15485
Description
Is your feature request related to a problem or challenge?
The QUALIFY clause is used to filter the results of WINDOW functions. This filtering of results is similar to how a HAVING clause filters the results of aggregate functions applied based on the GROUP BY clause
I think this syntax helps us to have a simpler syntax without subquery or with clause, similar to the benefit of having clause to aggregation function.
statement count 0
create table t(id int, region int) as values (1, 2), (3, 4), (5, 6), (4, 4);
query I
select row_number() over (PARTITION BY region) as rk from t;
----
1
1
2
1
query I
with ranked as (
select row_number() over (PARTITION BY region) as rk from t
) select * from ranked where rk > 1;
----
2
query error DataFusion error: This feature is not implemented: QUALIFY
select row_number() over (PARTITION BY region) as rk from t qualify rk > 1;
Describe the solution you'd like
Support QUALIFY syntax for window function
Describe alternatives you've considered
Use subquery and with clause
Additional context
https://duckdb.org/docs/stable/sql/query_syntax/qualify.html