サブクエリを使わずに一つのクエリで作成した集約関数カラムなどを呼び出せるか

Question

以下のクエリを実行した時

SELECT id, date
, RANK() OVER(PARTITION BY id ORDER BY date) as ranking
FROM temp

このクエリから更にranking = 2のものだけを取り出したい時

SELECT id, date
, RANK() OVER(PARTITION BY id ORDER BY date) as ranking
FROM temp
WHERE ranking = 2

上記のようにサブクエリを使わずに実行できる方法はございますでしょうか。
(実際にはsql2のクエリはエラーを起こすはずです)

ご回答いただけると幸いです。よろしくお願い致します。

Comments

[hihijiji]

View, WITH句, ストアドなどもだめですか？

[ha_horse]

ご質問ありがとうございます。
サブクエリを使わないで出したいのでWITH句などは使わないことを想定しています。

[hihijiji]

SQLの構文上SELECT句は最初に書きますが、評価は最後に行います。
一般的にRANK関数はSELECT句でしか使えないので無理です。

[ha_horse]

ご回答ありがとうございます。
サブクエリ使わないと無理なこと、承知しました。

追加の質問で恐縮なのですが、ORDER BY やGROUP BY などでは作成したカラムを使えるのですがどういった構造になるのでしょうか。
ご存知であればご教授頂きたいです。

[hihijiji]

ごめんなさい
ORDER BY　などの全体集約句がある場合はそっちが最後ですね。

[ha_horse]

なるほどです！ありがとうございます。

そうなるとSQLでは評価構造は
FROM
↓
WHERE
↓
SELECT
↓
全体集約句

みたいなイメージですかね？

[hihijiji]

ここか詳しいですね
https://qiita.com/suzukito/items/edcd00e680186f2930a8

[ha_horse]

非常に助かりました！ありがとうございます！！！

Answer 1

RANKを含むWindow関数が利用できるのはSELECTもしくはORDER BYで、WHEREでは利用することはできません。理由についてはPostgreSQLのドキュメントがわかりやすいので抜粋します。

3.5. Window Functions

Window functions are permitted only in the SELECT list and the ORDER BY clause of the query. They are forbidden elsewhere, such as in GROUP BY, HAVING and WHERE clauses. This is because they logically execute after the processing of those clauses.

なお、サブクエリを利用せずに同じことをしたいのであれば、以下のようなクエリになるかと思います。

SELECT T1.id, T1.date, COUNT(*) as ranking
FROM temp T1
JOIN temp T2 ON T1.id = T2.id AND T1.date <= T2.date
GROUP BY T1.id, T1.date
HAVING ranking = 2

Answer 2

3.5. ウィンドウ関数
にあるサンプルを見ると、普通にサブクエリしているような。

SELECT depname, empno, salary, enroll_date
FROM
(SELECT depname, empno, salary, enroll_date,
rank() OVER (PARTITION BY depname ORDER BY salary DESC, empno) AS pos
FROM empsalary
) AS ss
WHERE pos < 3;