回答編集履歴

推敲

2021/03/30 08:43

投稿

スコア25327

test CHANGED Viewed

@@ -10,17 +10,17 @@
 ), unit as (
-  select max(rank) /4 as rank_unit from base
+  select trunc((max(rank) /4) + 0.9) as rank_unit from base
 )
-select  rank / rank_unit as RANK_GROUP
+select  trunc(rank / rank_unit) as RANK_GROUP
       , AVG(SCORE), AVG(AGE)
 FROM  base , unit
-GROUP BY  rank / rank_unit
+GROUP BY  trunc(rank / rank_unit)
 ```

推敲

2021/03/30 08:43

投稿

スコア25327

test CHANGED Viewed

@@ -8,19 +8,19 @@
   from SCORE_TBL
-), max_base as (
+), unit as (
-  select max(rank) as max_rank from base
+  select max(rank) /4 as rank_unit from base
 )
-select  rank / (max_rank / 4) as RANK_GROUP
+select  rank / rank_unit as RANK_GROUP
       , AVG(SCORE), AVG(AGE)
-FROM  base , max_base
+FROM  base , unit
-GROUP BY  rank / (max_rank / 4)
+GROUP BY  rank / rank_unit
 ```

追記

2021/03/30 08:37

投稿

スコア25327

test CHANGED Viewed

@@ -23,3 +23,5 @@
 GROUP BY  rank / (max_rank / 4)
 ```
+件数が多いなら一時テーブル経由にした方が高速かもしれませんね。