QUALIFY 子句用于过滤 WINDOW 函数的结果。这种结果过滤类似于 HAVING 子句如何过滤基于 GROUP BY 子句应用的聚合函数的结果。
QUALIFY 子句避免了使用子查询或 WITH 子句 来执行此过滤操作（类似于 HAVING 避免了子查询）。在 QUALIFY 示例下方包含了一个使用 WITH 子句而不是 QUALIFY 的示例。
请注意，这是基于WINDOW函数的过滤，而不一定是基于WINDOW子句。WINDOW子句是可选的，可以用来简化多个WINDOW函数表达式的创建。
在SELECT语句中，指定QUALIFY子句的位置是在WINDOW子句之后（WINDOW不需要指定），并且在ORDER BY之前。
Examples
以下每个示例都产生相同的输出，位于下方。
基于在QUALIFY子句中定义的窗口函数进行过滤：
SELECT
schema_name,
function_name,
-- In this example the function_rank column in the select clause is for reference
row_number() OVER (PARTITION BY schema_name ORDER BY function_name) AS function_rank
FROM duckdb_functions()
QUALIFY
row_number() OVER (PARTITION BY schema_name ORDER BY function_name) < 3;
基于在SELECT子句中定义的窗口函数进行过滤：
SELECT
schema_name,
function_name,
row_number() OVER (PARTITION BY schema_name ORDER BY function_name) AS function_rank
FROM duckdb_functions()
QUALIFY
function_rank < 3;
基于在QUALIFY子句中定义的窗口函数进行过滤，但使用WINDOW子句：
SELECT
schema_name,
function_name,
-- In this example the function_rank column in the select clause is for reference
row_number() OVER my_window AS function_rank
FROM duckdb_functions()
WINDOW
my_window AS (PARTITION BY schema_name ORDER BY function_name)
QUALIFY
row_number() OVER my_window < 3;
基于在SELECT子句中定义的窗口函数进行过滤，但使用WINDOW子句：
SELECT
schema_name,
function_name,
row_number() OVER my_window AS function_rank
FROM duckdb_functions()
WINDOW
my_window AS (PARTITION BY schema_name ORDER BY function_name)
QUALIFY
function_rank < 3;
基于WITH子句的等效查询（没有QUALIFY子句）：
WITH ranked_functions AS (
SELECT
schema_name,
function_name,
row_number() OVER (PARTITION BY schema_name ORDER BY function_name) AS function_rank
FROM duckdb_functions()
)
SELECT
*
FROM ranked_functions
WHERE
function_rank < 3;
| 模式名称 | 函数名称 | 函数排名 |
|---|---|---|
| main | !__postfix | 1 |
| main | !~~ | 2 |
| pg_catalog | col_description | 1 |
| pg_catalog | format_pg_type | 2 |