QUALIFY Clause

QUALIFY Clause

The QUALIFY clause is used to filter the results of WINDOW functions. This filtering of results is similar to how a HAVING clause filters the results of aggregate functions applied based on the GROUP BY clause.

The QUALIFY clause avoids the need for a subquery or WITH clause to perform this filtering (much like HAVING avoids a subquery). An example using a WITH clause instead of QUALIFY is included below the QUALIFY examples.

Note that this is filtering based on WINDOW functions, not necessarily based on the WINDOW clause. The WINDOW clause is optional and can be used to simplify the creation of multiple WINDOW function expressions.

The position of where to specify a QUALIFY clause is following the WINDOW clause in a SELECT statement (WINDOW does not need to be specified), and before the ORDER BY.

Examples

Each of the following examples produce the same output, located below.

Filter based on a window function defined in the QUALIFY clause:

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;

Filter based on a window function defined in the SELECT clause:

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;

Filter based on a window function defined in the QUALIFY clause, but using the WINDOW clause:

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;

Filter based on a window function defined in the SELECT clause, but using the WINDOW clause:

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;

Equivalent query based on a WITH clause (without a QUALIFY clause):

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

Syntax

网址：QUALIFY Clause https://www.yuejiaxmz.com/news/view/1237760

相关内容

Amazon Renewed
Arc
全谷物
项目管理与时间的艺术
【东易分享】11种生活方式，造就了你的庸碌人生！
Dew./盲人出行辅助系统
电子产品维修合同
太阳村
揭秘“pinch meaning”：从日常生活到科技应用
厂家直销 DIY仿真植物相框 家居酒店装饰框 仿真多肉类摆件

随便看看