SQLでランキングを求め、順位に応じて4つにグルーピングしたい

Question

以下のようなデータをSQLでランキングし、ランクに応じてさらに4つにグルーピングして各平均値を求めたいです。(SCORE高位がRANK上位　同一SCOREは同一RANK扱い)

| NAME SCORE AGE
| hoge01 500 30
| hoge02 450 28
| hoge03 320 25
| hoge04 180 35
| hoge05 50 29
| hoge06 850 31
| hoge07 425 37
| hoge08 380 22
| hoge09 590 34
| hoge10 180 25

結果は以下のようなイメージです。

| RANK_GROUP AVG_SCORE AVG_AGE
| 1 720 32.5
| 2 475 29
| 3 402.5 29.5
| 4 182.5 29

RANK_GROUPについて、以下のように求めることも可能ですが
順位　÷　（最下位　÷　4）※少数点以下切り上げ

これをSQLに直すと集約関数を含んだSELECT句をGROUP BYに
指定する事となりエラー扱いされます。実際のSQLとエラーは下記です。

SELECT
RANK() OVER(ORDER BY SCORE DESC) / (MAX((SELECT RANK() OVER(ORDER BY SCORE DESC) FROM SCORE_TBL)) / 4) AS RANK_GROUP,
AVG(SCORE),
AVG(AGE)
FROM
SCORE_TBL
GROUP BY
RANK_GROUP

=================
ERROR: GROUP BYでは集約関数を使用できません
LINE 2: MAX((SELECT RANK() OVER(ORDER BY SCORE...
^
本件につきまして、SQLの標準機能でもっと賢いやり方があるでしょうか。
ご教示の程お願いいたします。

Comments

[退会済みユーザー]

サブクエリにしたら行けそうですね。

[ahakat]

ありがとうございます。サブクエリ化して解決できそうです。

Answer 1

手打ちで、未検証ですけど。

with base as (
  select * , RANK() OVER(ORDER BY SCORE DESC) rank
  from SCORE_TBL
), unit as (
  select trunc((max(rank) /4) + 0.9) as rank_unit from base
)
select  trunc(rank / rank_unit) as RANK_GROUP
      , AVG(SCORE), AVG(AGE)
FROM  base , unit
GROUP BY  trunc(rank / rank_unit)

件数が多いなら一時テーブル経由にした方が高速かもしれませんね。

Comments

[ahakat]

実際にクエリまでご教示いただきありがとうございました。
いただいたSQL文で本要件を満足できそうです。
ご親切にありがとうございました。