QUALIFY 子句 - Amazon Redshift

QUALIFY 子句

QUALIFY 子句根据用户指定的搜索条件,筛选先前计算的窗口函数的结果。您可以使用此子句将筛选条件应用于窗口函数的结果,而无需使用子查询。

此子句与 HAVING 子句类似,后者应用条件以进一步从 WHERE 子句筛选行。QUALIFY 和 HAVING 的区别在于,QUALIFY 子句的筛选结果可以基于对数据运行窗口函数的结果。在一个查询中可以同时使用 QUALIFY 和 HAVING 子句。

语法

QUALIFY condition
注意

如果您在 FROM 子句之后直接使用 QUALIFY 子句,则 FROM 关系名称必须在 QUALIFY 子句之前指定了别名。

示例

本节中的示例使用下面的示例数据。

create table store_sales (ss_sold_date date, ss_sold_time time, ss_item text, ss_sales_price float); insert into store_sales values ('2022-01-01', '09:00:00', 'Product 1', 100.0), ('2022-01-01', '11:00:00', 'Product 2', 500.0), ('2022-01-01', '15:00:00', 'Product 3', 20.0), ('2022-01-01', '17:00:00', 'Product 4', 1000.0), ('2022-01-01', '18:00:00', 'Product 5', 30.0), ('2022-01-02', '10:00:00', 'Product 6', 5000.0), ('2022-01-02', '16:00:00', 'Product 7', 5.0);

以下示例演示了如何查找每天 12:00 之后售出的两件最昂贵的商品。

SELECT * FROM store_sales ss WHERE ss_sold_time > time '12:00:00' QUALIFY row_number() OVER (PARTITION BY ss_sold_date ORDER BY ss_sales_price DESC) <= 2 ss_sold_date | ss_sold_time | ss_item | ss_sales_price --------------+--------------+-----------+---------------- 2022-01-01 | 17:00:00 | Product 4 | 1000 2022-01-01 | 18:00:00 | Product 5 | 30 2022-01-02 | 16:00:00 | Product 7 | 5

然后,您可以找到每天售出的最后一件商品。

SELECT * FROM store_sales ss QUALIFY last_value(ss_item) OVER (PARTITION BY ss_sold_date ORDER BY ss_sold_time ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) = ss_item; ss_sold_date | ss_sold_time | ss_item | ss_sales_price --------------+--------------+-----------+---------------- 2022-01-01 | 18:00:00 | Product 5 | 30 2022-01-02 | 16:00:00 | Product 7 | 5

以下示例返回与前一个查询相同的记录,即每天售出的最后一件商品,但它没有使用 QUALIFY 子句。

SELECT * FROM ( SELECT *, last_value(ss_item) OVER (PARTITION BY ss_sold_date ORDER BY ss_sold_time ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) ss_last_item FROM store_sales ss ) WHERE ss_last_item = ss_item; ss_sold_date | ss_sold_time | ss_item | ss_sales_price | ss_last_item --------------+--------------+-----------+----------------+-------------- 2022-01-02 | 16:00:00 | Product 7 | 5 | Product 7 2022-01-01 | 18:00:00 | Product 5 | 30 | Product 5