doris
doris copied to clipboard
[feature](Nereids) support qualify stmt
Proposed changes
like bigquery https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#qualify_clause
Examples
SELECT
item,
RANK() OVER (PARTITION BY category ORDER BY purchases DESC) as rank
FROM Produce
WHERE Produce.category = 'vegetable'
QUALIFY rank <= 3
/*---------+------*
| item | rank |
+---------+------+
| kale | 1 |
| lettuce | 2 |
| cabbage | 3 |
*---------+------*/
You don't have to include a window function in the SELECT list to use QUALIFY. The following query returns the most popular vegetables
SELECT item
FROM Produce
WHERE Produce.category = 'vegetable'
QUALIFY RANK() OVER (PARTITION BY category ORDER BY purchases DESC) <= 3
/*---------*
| item |
+---------+
| kale |
| lettuce |
| cabbage |
*---------*/
Issue Number: close #xxx