[アップデート]Amazon RedshiftでQUALIFY句がサポートされ、WINDOW関数を使った結果のフィルタが可能になりました

データアナリティクス事業本部インテグレーション部コンサルティングチーム・新納（にいの）です。

これまでRedshiftで長いクエリを書いていた方にとっては狂喜乱舞の朗報かもしれません。2023年7月17日、Amazon RedshiftがQUALIFY句をついにサポートしました。

QUALIFY句とは

QUALIFY句は、WINDOW関数を使った結果をフィルタリングするSQLステートメントです。これまではサブクエリを記述することで結果をフィルタしていましたが、QUALIFY句を使ってスッキリと簡潔にクエリを書き、冗長性を防いで可読性を高めることができるようになりました。

結果のフィルタができるという特徴がHAVING句とよく似ていますね。HAVING句はSUMなどの集計関数とGROUP BY句の結果をフィルタする一方、QUALIFY句では分析によく使われるRANKなどのWINDOW関数の結果をフィルタするという違いがあります。

使ってみた

AWSのドキュメントでQUALIFY句を使ったサンプルが記載されています。今回はこのサンプルを使って試してみます。

使用するデータは以下のような商品の売上データです。

ss_sold_date	ss_sold_time	ss_item	ss_sales_price
2022-1-1	9:00:00	Product 1	100
2022-1-1	11:00:00	Product 2	500
2022-1-1	15:00:00	Product 3	20
2022-1-1	17:00:00	Product 4	1000
2022-1-1	18:00:00	Product 5	30
2022-1-2	10:00:00	Product 6	5000
2022-1-2	16:00:00	Product 7	5

このデータを以下の条件に絞って結果を取得してみます。

• 12時以降に販売された商品
• 上位2件の高額の商品

結果は以下の通りです。

ss_sold_date	ss_sold_time	ss_item	ss_sales_price
2022-1-2	16:00:00	Product 7	5
2022-1-1	17:00:00	Product 4	1000
2022-1-1	18:00:00	Product 5	30

QUALIFY句が無い時

まずはQUALIFY句を使わず、従来通りの方法で結果を取得してみましょう。WHERE句では条件にWINDOW関数を使えないため、サブクエリの中でWINDOW関数を使って新たなカラムを作り、そのカラムをWHERE句にて条件指定します。

SELECT 
    ss_sold_date,
    ss_sold_time,
    ss_item,
    ss_sales_price
FROM (
  SELECT
    ss.*,
    ROW_NUMBER() OVER (PARTITION BY ss_sold_date ORDER BY ss_sales_price DESC) AS row_num
  FROM
    store_sales ss
  WHERE
    ss.ss_sold_time > TIME '12:00:00'
) subquery
WHERE
  subquery.row_num <= 2;

一時的にサブクエリで名前のあるテーブルを作成できるWITH句を使うことでも同様の結果を得られます。

WITH tmp_table AS (
  SELECT
    ss.*,
    ROW_NUMBER() OVER (PARTITION BY ss_sold_date ORDER BY ss_sales_price DESC) AS row_num
  FROM
    store_sales ss
  WHERE
    ss.ss_sold_time > TIME '12:00:00'
)
SELECT
  ss_sold_date,
  ss_sold_time,
  ss_item,
  ss_sales_price
FROM
  tmp_table
WHERE
  row_num <= 2;

どちらのクエリもどうしても冗長になりがちで、SQLに慣れていない人にとっては何をやっているクエリなのかがすぐに分かりにくいですよね。

QUALIFY句がある時

上述したAWSのドキュメントから引用すると、QUALIFYを使うことで以下のように簡潔にクエリを記述できます。記述量も減ってかなり可読性が高まりました。

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

最後に

WINDOW関数の結果でフィルタリング可能なQUALIFY句がRedshiftでも使えるようになったので試してみました。

冗長になりがちなクエリを簡潔に読みやすくしてくれるので、他の人でもすぐどんなロジックで結果を取得しているのかわかるというのは強力なメリットです。

かなり便利な機能ですのでぜひ試してみてください！