先日の記事で、RANKウィンドウ関数を使って月ごとの店舗別売上ランキングを取得する方法を紹介した。
https://chusotsu-program.com/postgre-rank-window
この実行結果をもとにランキングTOP3を抽出しようとした場合、真っ先に思い浮かぶのがWHERE句による絞り込みだ。
01 02 03 04 05 06 07 08 09 10 11 12 13 | 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の処理は以下の順序で実行される。
- FROM(JOIN)
- WHERE
- GROUP BY
- SELECT
- HAVING
- ウィンドウ関数の計算
- ORDER BY
- LIMIT
ウィンドウ関数の計算はWHERE句の後に実行されるため、先ほどのようなエラーが発生するというわけだ。
ではウィンドウ関数の結果をもとに絞り込みをおこなう場合はどうすれば良いか?
冗長的なコードではあるが、次のようにサブクエリを使えば良い。
01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 | 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で自由に編集することができる。