セール中の作品の
POV「システムが斬新」+「ちょっとした工夫」(A判定のみ)の合計数順です。
同じユーザーが両方付けている場合は合わせて1カウント計算
※:まとめ買い対象 ○:1000円未満 ◎:1000〜1999円 ●:2000〜2999円
ゲーム名の後の数字はおかず指数(とても使えた=100).中央値
select u.count as 斬新＋工夫
,
string_agg(case when c.max=5 then '○' when
c.max=4 then '※'
when c.max=3 then '◎'
when c.max=2 then '●'
else '' end||
'<A href="http://erogamescape.dyndns.org/~ap2/ero/toukei_kaiseki/game.php?game='||game_id||'" target="_blank">'|| gamename||'</A>
'
||'('||round(ao)||'/'||median||')','.' order by c.max desc,sellday desc,ao desc,median desc)
as ゲーム名
from
(select brandname,gamename,game_id,ao,coterie,sellday,median
, rank() over(partition by brandname order by -ao)
, rank() over(partition by brandname order by -median,-ao) as r2
from
(select game, sum(o)/(count(o)+1.0) as ao
from
(select game, case when okazu_tokuten in (4,3,2,1)then okazu_tokuten*20+20
when okazu_tokuten in(-1,-2) then okazu_tokuten*20+40
else null end as o from userreview
where
okazu_tokuten>-3
)g
group by game
)go,
toukei_temp_table t
where go.game=t.game_id
and model='PC'
)t,(select game,max(case when
cg.content like'___円%' or cg.content like'__円%' or cg.content like'_円%' or
(cg.content like'%999円' and cg.content not like'%,999円' )
then 5
when cg.content like'%本%' then 4
when cg.content like '1,___円%' then 3
when cg.content like '2,___円%' then 2
else 1 end)
from campaign_game cg,campaignlist
where cg.campaign=campaignlist.id
and end_timestamp >current_timestamp
group by game
)c,
(select game, count(*)
from (select distinct game,uid FROM povgroups where (pov=131 or pov=270)and rank='A')t
group by game having count(*)>=2)u
where t.game_id=u.game and u.game=c.game
group by u.count
order by u.count desc