月刊統計表の今月と先月、先々月の差から 先月、先々月の各ゲームのデータ数増加を測ってみました (先々月以前に発売されたゲームのみ)
select minc2 as 以前データ数,minc- minc2 as 先々月,maxc-minc as 先月データ数,t.median as 現中央値,sellday as 発売日,
'<A href="http://erogamescape.dyndns.org/~ap2/ero/toukei_kaiseki/game.php?game='||t.game_id||'">'||t.gamename||'</A>'
ゲーム名
,'<A href="http://erogamescape.dyndns.org/~ap2/ero/toukei_kaiseki/brand.php?brand='||t.brand_id||'">'||t.brandname||'</A>' ブランド名
from
(select game,max(count) as maxc,min(
case when rank<=1 then count else null end) as minc,min(count) as minc2,max(rank) as mrank from
(select date,game,count, RANK() OVER (PARTITION BY game ORDER BY date desc) -1 as rank
from
junirirekimedian where date>'2019-04-30'
)c
where rank<=2 group by game)d,
toukei_temp_table t
where t.game_id=d.game and maxc-minc>=1 and mrank=2
order by maxc-minc desc, minc-minc2 desc,t.median desc,sellday desc