参考データ:元々のゲームの傾向別、おかず得点と得点の関係の実行結果

SQLの説明

※ おかず評価が同じ程度のゲームに登録してるデータ。例えば「4」はとても使える評価が過半数のゲーム数本に含まれるデータです。

検索結果

実行したSQL

      select om as ※,
 ot as おかず得点
 ,round(avg(t),1)  as 通常得点中央値 
 ,round(avg(r1+r2)-1) as データ数
from
(select om,
 okazu_tokuten as ot
 ,tokuten as t
 ,row_number() over(partition by okazu_tokuten,om order by tokuten,id) as r1
 ,row_number() over(partition by okazu_tokuten,om order by tokuten desc,id desc) as r2
from userreview ur,
(select 
 g
 ,max(ot) as om,min(ot) as om2
from
(select 
 game as g,
 okazu_tokuten as ot
 ,row_number() over(partition by game order by okazu_tokuten,id) as r1
 ,row_number() over(partition by game order by okazu_tokuten desc,id desc) as r2
from userreview ur
where tokuten is not null 
 and okazu_tokuten>-3

and game in(select id from gamelist where erogame ='t')
)r
where r1>=r2-1 
 and r1<=r2+1 
group by g)ot

where tokuten is not null and game=g
 and okazu_tokuten>-3 and om=om2
 and game in(select id from gamelist where erogame ='t')
)r2
where r1>=r2-1 
 and r1<=r2+1
group by ot,om
order by om desc,ot desc