QUALIFY 子句

QUALIFY 子句用于筛选 WINDOW 函数的结果。这种筛选方式类似于 HAVING 子句对基于 GROUP BY 子句应用的聚合函数结果进行筛选。

QUALIFY 子句可避免为执行此筛选而编写子查询或 WITH 子句（与 HAVING 可避免子查询的方式类似）。在 QUALIFY 示例后，还提供了一个使用 WITH 子句而非 QUALIFY 的等价示例。

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

在 SELECT 语句中，QUALIFY 子句应位于 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 goose_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 goose_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 goose_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 goose_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 goose_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

语法