先日の記事で、RANKウィンドウ関数を使って月ごとの店舗別売上ランキングを取得する方法を紹介した。

https://chusotsu-program.com/postgre-rank-window

この実行結果をもとにランキングTOP3を抽出しようとした場合、真っ先に思い浮かぶのがWHERE句による絞り込みだ。

SELECT
	sales_month
	, shop_id
	, sales_amount
	, RANK() OVER (
		partition by sales_month
		order by sales_amount desc
	) as monthly_sales_rank
FROM
	monthly_sales
WHERE
	monthly_sales_rank <= 3
;

しかしこのクエリを実行すると以下のエラーが返ってきてしまう。

ERROR: column “monthly_sales_rank” does not exist
LINE 12: monthly_sales_rank <= 3

これはウィンドウ関数で取得したはずのmonthly_sales_rankカラムが見つからないというエラーなのだが、なぜこのようなことが起きているのかを理解するためにはウィンドウ関数の実行順序についてまず理解しなければならない。

ウィンドウ関数の実行順序

SQLの処理は以下の順序で実行される。

  1. FROM（JOIN）
  2. WHERE
  3. GROUP BY
  4. SELECT
  5. HAVING
  6. ウィンドウ関数の計算
  7. ORDER BY
  8. LIMIT

ウィンドウ関数の計算はWHERE句の後に実行されるため、先ほどのようなエラーが発生するというわけだ。

ではウィンドウ関数の結果をもとに絞り込みをおこなう場合はどうすれば良いか？

冗長的なコードではあるが、次のようにサブクエリを使えば良い。

SELECT
	*
FROM (
	SELECT
		sales_month
		, shop_id
		, sales_amount
		, RANK() OVER (
			partition by sales_month
			order by sales_amount desc
		) as monthly_sales_rank
	FROM
		monthly_sales	
) tmp
WHERE
	monthly_sales_rank <= 3
;

これでウィンドウ関数の結果をWHEREやGROUP BYで自由に編集することができる。