QUALIFY 句

QUALIFY 句は、ユーザーが指定した検索条件に従って、以前に計算されたウィンドウ関数の結果をフィルタリングします。この句を使用すると、サブクエリを使用せずに、ウィンドウ関数の結果にフィルタリング条件を適用できます。

これは、WHERE 句から行をさらに絞り込む条件を適用する HAVING 句に似ています。QUALIFY と HAVING の違いは、QUALIFY 句からフィルタリングされた結果は、データに対してウィンドウ関数を実行した結果に基づいている可能性があることです。1 つのクエリで QUALIFY 句と HAVING 句の両方を使用できます。

構文

QUALIFY condition

例

このセクションの例では下のサンプルデータを使用します。

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 以降に販売される最も高価な 2 つの商品を検索する方法を示しています。

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