doris icon indicating copy to clipboard operation
doris copied to clipboard

[feature](Nereids) support qualify stmt

Open qzsee opened this issue 6 months ago • 19 comments

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

qzsee avatar Aug 28 '24 08:08 qzsee