⌘+k ctrl+k
1.3 (稳定版)
搜索快捷键 cmd + k | ctrl + k
QUALIFY 子句

QUALIFY 子句用于过滤 WINDOW 函数的结果。这种结果过滤类似于 HAVING 子句如何根据 GROUP BY 子句过滤聚合函数的结果。

QUALIFY 子句避免了为执行此过滤而需要子查询或 WITH 子句（很像 HAVING 避免了子查询）。在 QUALIFY 示例下方包含一个使用 WITH 子句而非 QUALIFY 的示例。

请注意，这是基于 WINDOW 函数进行过滤，不一定是基于 WINDOW 子句。 WINDOW 子句是可选的，可用于简化多个 WINDOW 函数表达式的创建。

指定 QUALIFY 子句的位置在 SELECT 语句中的 WINDOW 子句之后（不需要指定 WINDOW），以及在 ORDER BY 之前。

示例

以下每个示例都生成相同的输出，位于下方。

基于 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;
schema_name function_name function_rank
main !__postfix 1
main !~~ 2
pg_catalog col_description 1
pg_catalog format_pg_type 2

语法

关于此页面