Amazon Redshift 將不再支援從修補程式 198 開始建立新的 Python UDFs。現有 Python UDF 將繼續正常運作至 2026 年 6 月 30 日。如需詳細資訊，請參閱[部落格文章](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/)。

本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。

# QUALIFY 子句
<a name="r_QUALIFY_clause"></a>

QUALIFY 子句會根據使用者指定的搜尋條件，篩選先前計算的視窗函數結果。您可以使用子句將篩選條件套用至視窗函數的結果，而不需要使用子查詢。

其類似於 [HAVING 子句](https://docs.aws.amazon.com/redshift/latest/dg/r_HAVING_clause.html)，該子句適用於從 WHERE 子句進一步篩選列的條件。QUALIFY 和 HAVING 之間的區別在於，從 QUALIFY 子句篩選的結果可以基於在資料上執行窗口函數的結果。您可以在一個查詢中同時使用 QUALIFY 和 HAVING 子句。

## 語法
<a name="r_QUALIFY-synopsis"></a>

```
QUALIFY condition
```

**注意**  
如果您直接在 FROM 子句之後使用 QUALIFY 子句，則 FROM 關係名稱必須在 QUALIFY 子句之前指定別名。

## 範例
<a name="r_QUALIFY-examples"></a>

本節中的範例使用以下範例資料。

```
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
```