On Mon Jul 21, 2025 at 5:23 PM -03, Vik Fearing wrote:
>
> On 21/07/2025 14:47, Matheus Alcantara wrote:
>> 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.
>
>
> I took a very brief look at this, and I think your grammar is wrong.  
> The QUALIFY clause should go after the WINDOW clause, just like 
> FROM/WHERE and GROUP BY/HAVING.
>
>
> That is what I am proposing to the standards committee, and I already 
> have some buy-in for that.
>
Thank you for the brief review and for the comments!

I'm not sure if I fully understand but please see the new attached
version.

Thanks,

--
Matheus Alcantara
From 28a4d49c27ba039518d3272aa35cb0176bab7750 Mon Sep 17 00:00:00 2001
From: Matheus Alcantara <mths....@pm.me>
Date: Wed, 4 Jun 2025 15:56:59 -0300
Subject: [PATCH v1] QUALIFY clause

---
 src/backend/optimizer/plan/planner.c |   5 ++
 src/backend/parser/analyze.c         |   8 +-
 src/backend/parser/gram.y            |  13 ++-
 src/backend/parser/parse_agg.c       |   7 +-
 src/backend/parser/parse_clause.c    |  57 +++++++++++-
 src/backend/parser/parse_expr.c      |   4 +
 src/backend/parser/parse_func.c      |   3 +
 src/backend/parser/parse_relation.c  |  18 ++++
 src/include/nodes/parsenodes.h       |   5 ++
 src/include/parser/kwlist.h          |   1 +
 src/include/parser/parse_clause.h    |   5 +-
 src/include/parser/parse_node.h      |   3 +
 src/test/regress/expected/window.out | 125 +++++++++++++++++++++++++++
 src/test/regress/sql/window.sql      |  51 +++++++++++
 14 files changed, 296 insertions(+), 9 deletions(-)

diff --git a/src/backend/optimizer/plan/planner.c 
b/src/backend/optimizer/plan/planner.c
index 549aedcfa99..1a8fb387e47 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -4710,6 +4710,11 @@ create_one_window_path(PlannerInfo *root,
                                if (!topwindow)
                                        topqual = lappend(topqual, opexpr);
                        }
+
+                       /*  Add QUALIFY qual */
+                       if (wc->qualifyQual != NULL)
+                               topqual = lappend(topqual, (Expr *) 
wc->qualifyQual);
+
                }
 
                path = (Path *)
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 34f7c17f576..dd27fd3730b 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -1481,9 +1481,12 @@ transformSelectStmt(ParseState *pstate, SelectStmt *stmt)
        qry->limitOption = stmt->limitOption;
 
        /* transform window clauses after we have seen all window functions */
+       pstate->p_targetList = qry->targetList;
        qry->windowClause = transformWindowDefinitions(pstate,
                                                                                
                   pstate->p_windowdefs,
-                                                                               
                   &qry->targetList);
+                                                                               
                   &qry->targetList,
+                                                                               
                   stmt->qualifyClause);
+
 
        /* resolve any still-unresolved output columns as being type text */
        if (pstate->p_resolve_unknowns)
@@ -2975,7 +2978,8 @@ transformPLAssignStmt(ParseState *pstate, PLAssignStmt 
*stmt)
        /* transform window clauses after we have seen all window functions */
        qry->windowClause = transformWindowDefinitions(pstate,
                                                                                
                   pstate->p_windowdefs,
-                                                                               
                   &qry->targetList);
+                                                                               
                   &qry->targetList,
+                                                                               
                   NULL);       /* FIXME(matheus) */
 
        qry->rtable = pstate->p_rtable;
        qry->rteperminfos = pstate->p_rteperminfos;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 73345bb3c70..117f20c8fa2 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -520,7 +520,7 @@ static Node *makeRecursiveViewSelect(char *relname, List 
*aliases, Node *query);
 %type <node>   TableElement TypedTableElement ConstraintElem 
DomainConstraintElem TableFuncElement
 %type <node>   columnDef columnOptions optionalPeriodName
 %type <defelt> def_elem reloption_elem old_aggr_elem operator_def_elem
-%type <node>   def_arg columnElem where_clause where_or_current_clause
+%type <node>   def_arg columnElem where_clause qualify_clause 
where_or_current_clause
                                a_expr b_expr c_expr AexprConst indirection_el 
opt_slice_bound
                                columnref having_clause func_table xmltable 
array_expr
                                OptWhereClause operator_def_arg
@@ -760,7 +760,7 @@ static Node *makeRecursiveViewSelect(char *relname, List 
*aliases, Node *query);
        POSITION PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY
        PRIOR PRIVILEGES PROCEDURAL PROCEDURE PROCEDURES PROGRAM PUBLICATION
 
-       QUOTE QUOTES
+       QUALIFY QUOTE QUOTES
 
        RANGE READ REAL REASSIGN RECURSIVE REF_P REFERENCES REFERENCING
        REFRESH REINDEX RELATIVE_P RELEASE RENAME REPEATABLE REPLACE REPLICA
@@ -12994,7 +12994,7 @@ select_clause:
 simple_select:
                        SELECT opt_all_clause opt_target_list
                        into_clause from_clause where_clause
-                       group_clause having_clause window_clause
+                       group_clause having_clause window_clause qualify_clause
                                {
                                        SelectStmt *n = makeNode(SelectStmt);
 
@@ -13006,6 +13006,7 @@ simple_select:
                                        n->groupDistinct = ($7)->distinct;
                                        n->havingClause = $8;
                                        n->windowClause = $9;
+                                       n->qualifyClause = $10;
                                        $$ = (Node *) n;
                                }
                        | SELECT distinct_clause target_list
@@ -14135,6 +14136,11 @@ where_clause:
                        | /*EMPTY*/                                             
                { $$ = NULL; }
                ;
 
+qualify_clause:
+                       QUALIFY a_expr                                          
        { $$ = $2; }
+                       | /*EMPTY*/                                             
                { $$ = NULL; }
+               ;
+
 /* variant for UPDATE and DELETE */
 where_or_current_clause:
                        WHERE a_expr                                            
        { $$ = $2; }
@@ -18250,6 +18256,7 @@ reserved_keyword:
                        | ORDER
                        | PLACING
                        | PRIMARY
+                       | QUALIFY
                        | REFERENCES
                        | RETURNING
                        | SELECT
diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c
index 0ac8966e30f..deeb3584f97 100644
--- a/src/backend/parser/parse_agg.c
+++ b/src/backend/parser/parse_agg.c
@@ -402,6 +402,9 @@ check_agglevels_and_constraints(ParseState *pstate, Node 
*expr)
                case EXPR_KIND_WHERE:
                        errkind = true;
                        break;
+               case EXPR_KIND_QUALIFY:
+                       errkind = true;
+                       break;
                case EXPR_KIND_POLICY:
                        if (isAgg)
                                err = _("aggregate functions are not allowed in 
policy expressions");
@@ -878,8 +881,6 @@ transformWindowFuncCall(ParseState *pstate, WindowFunc 
*wfunc,
                case EXPR_KIND_WHERE:
                        errkind = true;
                        break;
-               case EXPR_KIND_POLICY:
-                       err = _("window functions are not allowed in policy 
expressions");
                        break;
                case EXPR_KIND_HAVING:
                        errkind = true;
@@ -895,6 +896,8 @@ transformWindowFuncCall(ParseState *pstate, WindowFunc 
*wfunc,
                        err = _("window functions are not allowed in window 
definitions");
                        break;
                case EXPR_KIND_SELECT_TARGET:
+               case EXPR_KIND_POLICY:
+               case EXPR_KIND_QUALIFY:
                        /* okay */
                        break;
                case EXPR_KIND_INSERT_TARGET:
diff --git a/src/backend/parser/parse_clause.c 
b/src/backend/parser/parse_clause.c
index 9f20a70ce13..75f5ab727f9 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -1842,6 +1842,51 @@ setNamespaceLateralState(List *namespace, bool 
lateral_only, bool lateral_ok)
        }
 }
 
+Node *
+transformQualifyClause(ParseState *pstate, List *targetlist, Node *qualify)
+{
+       Node       *where;
+
+       if (qualify == NULL)
+               return NULL;
+
+       where = transformWhereClause(pstate,
+                                                                qualify,
+                                                                
EXPR_KIND_QUALIFY,
+                                                                "QUALIFY");
+
+       /*
+        *  Transform any Var referencing a WindowFunc into a real WindowFunc de
+        * fact.
+        */
+       if (IsA(where, OpExpr))
+       {
+               ListCell   *lc;
+               OpExpr     *op = (OpExpr *) where;
+               List       *newArgs = NIL;
+
+               foreach(lc, op->args)
+               {
+                       Node       *node = lfirst(lc);
+
+                       if (IsA(node, Var))
+                       {
+                               Var                *var = (Var *) node;
+                               TargetEntry *tle = (TargetEntry *) 
lfirst(&targetlist->elements[var->varattno - 1]);
+
+
+                               Assert(IsA(tle->expr, WindowFunc));
+                               newArgs = lappend(newArgs, (Node *) tle->expr);
+                       }
+                       else
+                               newArgs = lappend(newArgs, node);
+               }
+               op->args = newArgs;
+       }
+
+       return where;
+}
+
 
 /*
  * transformWhereClause -
@@ -2764,7 +2809,8 @@ transformSortClause(ParseState *pstate,
 List *
 transformWindowDefinitions(ParseState *pstate,
                                                   List *windowdefs,
-                                                  List **targetlist)
+                                                  List **targetlist,
+                                                  Node *qualify)
 {
        List       *result = NIL;
        Index           winref = 0;
@@ -2776,6 +2822,7 @@ transformWindowDefinitions(ParseState *pstate,
                WindowClause *refwc = NULL;
                List       *partitionClause;
                List       *orderClause;
+               Node       *qualifyClause;
                Oid                     rangeopfamily = InvalidOid;
                Oid                     rangeopcintype = InvalidOid;
                WindowClause *wc;
@@ -2824,12 +2871,20 @@ transformWindowDefinitions(ParseState *pstate,
                                                                                
           EXPR_KIND_WINDOW_PARTITION,
                                                                                
           true /* force SQL99 rules */ );
 
+               /*
+                * transform QUALIFY. targetlist is used find the window 
function
+                * reference.
+                *
+                */
+               qualifyClause = transformQualifyClause(pstate, *targetlist, 
qualify);
+
                /*
                 * And prepare the new WindowClause.
                 */
                wc = makeNode(WindowClause);
                wc->name = windef->name;
                wc->refname = windef->refname;
+               wc->qualifyQual = qualifyClause;
 
                /*
                 * Per spec, a windowdef that references a previous one copies 
the
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index d66276801c6..d30661e8da5 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -575,6 +575,7 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref)
                case EXPR_KIND_COPY_WHERE:
                case EXPR_KIND_GENERATED_COLUMN:
                case EXPR_KIND_CYCLE_MARK:
+               case EXPR_KIND_QUALIFY:
                        /* okay */
                        break;
 
@@ -1794,6 +1795,7 @@ transformSubLink(ParseState *pstate, SubLink *sublink)
                case EXPR_KIND_FROM_SUBSELECT:
                case EXPR_KIND_FROM_FUNCTION:
                case EXPR_KIND_WHERE:
+               case EXPR_KIND_QUALIFY:
                case EXPR_KIND_POLICY:
                case EXPR_KIND_HAVING:
                case EXPR_KIND_FILTER:
@@ -3219,6 +3221,8 @@ ParseExprKindName(ParseExprKind exprKind)
                        return "GENERATED AS";
                case EXPR_KIND_CYCLE_MARK:
                        return "CYCLE";
+               case EXPR_KIND_QUALIFY:
+                       return "QUALIFY";
 
                        /*
                         * There is intentionally no default: case here, so 
that the
diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c
index 583bbbf232f..4e202949bfd 100644
--- a/src/backend/parser/parse_func.c
+++ b/src/backend/parser/parse_func.c
@@ -2557,6 +2557,9 @@ check_srf_call_placement(ParseState *pstate, Node 
*last_srf, int location)
                case EXPR_KIND_WHERE:
                        errkind = true;
                        break;
+               case EXPR_KIND_QUALIFY:
+                       errkind = true;
+                       break;
                case EXPR_KIND_POLICY:
                        err = _("set-returning functions are not allowed in 
policy expressions");
                        break;
diff --git a/src/backend/parser/parse_relation.c 
b/src/backend/parser/parse_relation.c
index 04ecf64b1fc..d3bb9346dec 100644
--- a/src/backend/parser/parse_relation.c
+++ b/src/backend/parser/parse_relation.c
@@ -922,6 +922,24 @@ colNameToVar(ParseState *pstate, const char *colname, bool 
localonly,
                        newresult = scanNSItemForColumn(orig_pstate, nsitem, 
sublevels_up,
                                                                                
        colname, location);
 
+                       /*
+                        * If we are parsing a QUALIFY expression try to search 
the window
+                        * function reference on target list
+                        */
+                       if (newresult == NULL && orig_pstate->p_hasWindowFuncs
+                               && pstate->p_expr_kind == EXPR_KIND_QUALIFY)
+                       {
+                               ListCell   *lc;
+
+                               foreach(lc, pstate->p_targetList)
+                               {
+                                       TargetEntry *tle = (TargetEntry *) 
lfirst(lc);
+
+                                       if (strcmp(tle->resname, colname) == 0)
+                                               newresult = (Node *) 
makeVarFromTargetEntry(OUTER_VAR, tle);
+                               }
+                       }
+
                        if (newresult)
                        {
                                if (result)
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 86a236bd58b..824b7238ca9 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -220,6 +220,8 @@ typedef struct Query
 
        Node       *havingQual;         /* qualifications applied to groups */
 
+       Node            *qualifyQual;   /* qualifications applied to window 
functions */
+
        List       *windowClause;       /* a list of WindowClause's */
 
        List       *distinctClause; /* a list of SortGroupClause's */
@@ -1575,6 +1577,8 @@ typedef struct WindowClause
        int                     frameOptions;   /* frame_clause options, see 
WindowDef */
        Node       *startOffset;        /* expression for starting bound, if 
any */
        Node       *endOffset;          /* expression for ending bound, if any 
*/
+       /* QUALIFY clause */
+       Node       *qualifyQual;
        /* in_range function for startOffset */
        Oid                     startInRangeFunc 
pg_node_attr(query_jumble_ignore);
        /* in_range function for endOffset */
@@ -2190,6 +2194,7 @@ typedef struct SelectStmt
        List       *targetList;         /* the target list (of ResTarget) */
        List       *fromClause;         /* the FROM clause */
        Node       *whereClause;        /* WHERE qualification */
+       Node       *qualifyClause;      /* QUALIFY qualification */
        List       *groupClause;        /* GROUP BY clauses */
        bool            groupDistinct;  /* Is this GROUP BY DISTINCT? */
        Node       *havingClause;       /* HAVING conditional-expression */
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index a4af3f717a1..f7e267dd241 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -359,6 +359,7 @@ PG_KEYWORD("procedure", PROCEDURE, UNRESERVED_KEYWORD, 
BARE_LABEL)
 PG_KEYWORD("procedures", PROCEDURES, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("program", PROGRAM, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("publication", PUBLICATION, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("qualify", QUALIFY, RESERVED_KEYWORD, AS_LABEL)
 PG_KEYWORD("quote", QUOTE, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("quotes", QUOTES, UNRESERVED_KEYWORD, BARE_LABEL)
 PG_KEYWORD("range", RANGE, UNRESERVED_KEYWORD, BARE_LABEL)
diff --git a/src/include/parser/parse_clause.h 
b/src/include/parser/parse_clause.h
index 3e9894926de..5580842ee33 100644
--- a/src/include/parser/parse_clause.h
+++ b/src/include/parser/parse_clause.h
@@ -20,6 +20,8 @@ extern void transformFromClause(ParseState *pstate, List 
*frmList);
 extern int     setTargetTable(ParseState *pstate, RangeVar *relation,
                                                   bool inh, bool alsoSource, 
AclMode requiredPerms);
 
+
+extern Node * transformQualifyClause(ParseState *pstate, List *targetlist, 
Node *qualify);
 extern Node *transformWhereClause(ParseState *pstate, Node *clause,
                                                                  ParseExprKind 
exprKind, const char *constructName);
 extern Node *transformLimitClause(ParseState *pstate, Node *clause,
@@ -35,7 +37,8 @@ extern List *transformSortClause(ParseState *pstate, List 
*orderlist,
 
 extern List *transformWindowDefinitions(ParseState *pstate,
                                                                                
List *windowdefs,
-                                                                               
List **targetlist);
+                                                                               
List **targetlist,
+                                                                               
Node *qualify);
 
 extern List *transformDistinctClause(ParseState *pstate,
                                                                         List 
**targetlist, List *sortClause, bool is_agg);
diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h
index f7d07c84542..05f0f17ba3d 100644
--- a/src/include/parser/parse_node.h
+++ b/src/include/parser/parse_node.h
@@ -45,6 +45,7 @@ typedef enum ParseExprKind
        EXPR_KIND_FROM_FUNCTION,        /* function in FROM clause */
        EXPR_KIND_WHERE,                        /* WHERE */
        EXPR_KIND_HAVING,                       /* HAVING */
+       EXPR_KIND_QUALIFY,                      /* QUALIFY */
        EXPR_KIND_FILTER,                       /* FILTER */
        EXPR_KIND_WINDOW_PARTITION, /* window definition PARTITION BY */
        EXPR_KIND_WINDOW_ORDER,         /* window definition ORDER BY */
@@ -231,6 +232,8 @@ struct ParseState
 
        Node       *p_last_srf;         /* most recent set-returning func/op 
found */
 
+       List       *p_targetList;               /* target list (of TargetEntry) 
*/
+
        /*
         * Optional hook functions for parser callbacks.  These are null unless
         * set up by the caller of make_parsestate.
diff --git a/src/test/regress/expected/window.out 
b/src/test/regress/expected/window.out
index b86b668f433..5c886c06141 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -4537,6 +4537,131 @@ WHERE first_emp = 1 OR last_emp = 1;
  sales     |     4 |   4800 | 08-08-2007  |         3 |        1
 (6 rows)
 
+-- Test QUALIFY clause
+SELECT *,
+       RANK() OVER (PARTITION BY depname ORDER BY salary DESC)
+FROM empsalary
+QUALIFY RANK() OVER (PARTITION BY depname ORDER BY salary DESC) <= 2;
+  depname  | empno | salary | enroll_date | rank 
+-----------+-------+--------+-------------+------
+ develop   |     8 |   6000 | 10-01-2006  |    1
+ develop   |    10 |   5200 | 08-01-2007  |    2
+ develop   |    11 |   5200 | 08-15-2007  |    2
+ personnel |     2 |   3900 | 12-23-2006  |    1
+ personnel |     5 |   3500 | 12-10-2007  |    2
+ sales     |     1 |   5000 | 10-01-2006  |    1
+ sales     |     4 |   4800 | 08-08-2007  |    2
+ sales     |     3 |   4800 | 08-01-2007  |    2
+(8 rows)
+
+SELECT *,
+       ROW_NUMBER() OVER (PARTITION BY depname ORDER BY enroll_date DESC)
+FROM empsalary
+QUALIFY ROW_NUMBER() OVER (PARTITION BY depname ORDER BY enroll_date DESC) = 1;
+  depname  | empno | salary | enroll_date | row_number 
+-----------+-------+--------+-------------+------------
+ develop   |     7 |   4200 | 01-01-2008  |          1
+ personnel |     5 |   3500 | 12-10-2007  |          1
+ sales     |     4 |   4800 | 08-08-2007  |          1
+(3 rows)
+
+SELECT *,
+       AVG(salary) OVER (PARTITION BY depname) AS avg_salary
+FROM empsalary
+QUALIFY salary > avg_salary;
+  depname  | empno | salary | enroll_date |      avg_salary       
+-----------+-------+--------+-------------+-----------------------
+ develop   |    11 |   5200 | 08-15-2007  | 5020.0000000000000000
+ develop   |     8 |   6000 | 10-01-2006  | 5020.0000000000000000
+ develop   |    10 |   5200 | 08-01-2007  | 5020.0000000000000000
+ personnel |     2 |   3900 | 12-23-2006  | 3700.0000000000000000
+ sales     |     1 |   5000 | 10-01-2006  | 4866.6666666666666667
+(5 rows)
+
+SELECT *,
+       COUNT(*) OVER (PARTITION BY depname, salary)
+FROM empsalary
+QUALIFY COUNT(*) OVER (PARTITION BY depname, salary) = 1;
+  depname  | empno | salary | enroll_date | count 
+-----------+-------+--------+-------------+-------
+ develop   |     7 |   4200 | 01-01-2008  |     1
+ develop   |     9 |   4500 | 01-01-2008  |     1
+ develop   |     8 |   6000 | 10-01-2006  |     1
+ personnel |     5 |   3500 | 12-10-2007  |     1
+ personnel |     2 |   3900 | 12-23-2006  |     1
+ sales     |     1 |   5000 | 10-01-2006  |     1
+(6 rows)
+
+SELECT *,
+       RANK() OVER (ORDER BY salary DESC) as rank
+FROM empsalary
+QUALIFY rank = 2;
+ depname | empno | salary | enroll_date | rank 
+---------+-------+--------+-------------+------
+ develop |    10 |   5200 | 08-01-2007  |    2
+ develop |    11 |   5200 | 08-15-2007  |    2
+(2 rows)
+
+SELECT *,
+       ROW_NUMBER() OVER (PARTITION BY depname ORDER BY enroll_date)
+FROM empsalary
+QUALIFY ROW_NUMBER() OVER (PARTITION BY depname ORDER BY enroll_date) <= 3;
+  depname  | empno | salary | enroll_date | row_number 
+-----------+-------+--------+-------------+------------
+ develop   |     8 |   6000 | 10-01-2006  |          1
+ develop   |    10 |   5200 | 08-01-2007  |          2
+ develop   |    11 |   5200 | 08-15-2007  |          3
+ personnel |     2 |   3900 | 12-23-2006  |          1
+ personnel |     5 |   3500 | 12-10-2007  |          2
+ sales     |     1 |   5000 | 10-01-2006  |          1
+ sales     |     3 |   4800 | 08-01-2007  |          2
+ sales     |     4 |   4800 | 08-08-2007  |          3
+(8 rows)
+
+SELECT *,
+       ROW_NUMBER() OVER (PARTITION BY depname ORDER BY salary DESC)
+FROM empsalary
+QUALIFY ROW_NUMBER() OVER (PARTITION BY depname ORDER BY salary DESC) <= 2
+ORDER BY depname, salary DESC;
+  depname  | empno | salary | enroll_date | row_number 
+-----------+-------+--------+-------------+------------
+ develop   |     8 |   6000 | 10-01-2006  |          1
+ develop   |    10 |   5200 | 08-01-2007  |          2
+ personnel |     2 |   3900 | 12-23-2006  |          1
+ personnel |     5 |   3500 | 12-10-2007  |          2
+ sales     |     1 |   5000 | 10-01-2006  |          1
+ sales     |     4 |   4800 | 08-08-2007  |          2
+(6 rows)
+
+SELECT *,
+       RANK() OVER (PARTITION BY depname ORDER BY salary DESC)
+FROM empsalary
+QUALIFY
+    RANK() OVER (PARTITION BY depname ORDER BY salary DESC) <= 2
+    AND enroll_date > DATE '2007-01-01';
+  depname  | empno | salary | enroll_date | rank 
+-----------+-------+--------+-------------+------
+ develop   |    10 |   5200 | 08-01-2007  |    2
+ develop   |    11 |   5200 | 08-15-2007  |    2
+ personnel |     5 |   3500 | 12-10-2007  |    2
+ sales     |     4 |   4800 | 08-08-2007  |    2
+ sales     |     3 |   4800 | 08-01-2007  |    2
+(5 rows)
+
+SELECT *,
+       RANK() OVER (PARTITION BY depname ORDER BY salary DESC)
+FROM empsalary
+QUALIFY
+    RANK() OVER (PARTITION BY depname ORDER BY salary DESC) = 1
+    OR salary < 4000;
+  depname  | empno | salary | enroll_date | rank 
+-----------+-------+--------+-------------+------
+ develop   |     8 |   6000 | 10-01-2006  |    1
+ personnel |     2 |   3900 | 12-23-2006  |    1
+ personnel |     5 |   3500 | 12-10-2007  |    2
+ sales     |     1 |   5000 | 10-01-2006  |    1
+(4 rows)
+
 -- cleanup
 DROP TABLE empsalary;
 -- test user-defined window function with named args and default args
diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql
index 02f105f070e..4f376d1b459 100644
--- a/src/test/regress/sql/window.sql
+++ b/src/test/regress/sql/window.sql
@@ -1522,6 +1522,57 @@ SELECT * FROM
    FROM empsalary) emp
 WHERE first_emp = 1 OR last_emp = 1;
 
+-- Test QUALIFY clause
+SELECT *,
+       RANK() OVER (PARTITION BY depname ORDER BY salary DESC)
+FROM empsalary
+QUALIFY RANK() OVER (PARTITION BY depname ORDER BY salary DESC) <= 2;
+
+SELECT *,
+       ROW_NUMBER() OVER (PARTITION BY depname ORDER BY enroll_date DESC)
+FROM empsalary
+QUALIFY ROW_NUMBER() OVER (PARTITION BY depname ORDER BY enroll_date DESC) = 1;
+
+SELECT *,
+       AVG(salary) OVER (PARTITION BY depname) AS avg_salary
+FROM empsalary
+QUALIFY salary > avg_salary;
+
+SELECT *,
+       COUNT(*) OVER (PARTITION BY depname, salary)
+FROM empsalary
+QUALIFY COUNT(*) OVER (PARTITION BY depname, salary) = 1;
+
+SELECT *,
+       RANK() OVER (ORDER BY salary DESC) as rank
+FROM empsalary
+QUALIFY rank = 2;
+
+SELECT *,
+       ROW_NUMBER() OVER (PARTITION BY depname ORDER BY enroll_date)
+FROM empsalary
+QUALIFY ROW_NUMBER() OVER (PARTITION BY depname ORDER BY enroll_date) <= 3;
+
+SELECT *,
+       ROW_NUMBER() OVER (PARTITION BY depname ORDER BY salary DESC)
+FROM empsalary
+QUALIFY ROW_NUMBER() OVER (PARTITION BY depname ORDER BY salary DESC) <= 2
+ORDER BY depname, salary DESC;
+
+SELECT *,
+       RANK() OVER (PARTITION BY depname ORDER BY salary DESC)
+FROM empsalary
+QUALIFY
+    RANK() OVER (PARTITION BY depname ORDER BY salary DESC) <= 2
+    AND enroll_date > DATE '2007-01-01';
+
+SELECT *,
+       RANK() OVER (PARTITION BY depname ORDER BY salary DESC)
+FROM empsalary
+QUALIFY
+    RANK() OVER (PARTITION BY depname ORDER BY salary DESC) = 1
+    OR salary < 4000;
+
 -- cleanup
 DROP TABLE empsalary;
 
-- 
2.39.5 (Apple Git-154)

Reply via email to