QUALIFY 子句 – DuckDB - DuckDB 数据库

搜索快捷键 cmd + k | ctrl + k

文档 / SQL / 查询语法

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

语法

关于此页面

行为准则商标使用指南

示例

语法

关于此页面

本文中