リタイア指数ベスト100の実行結果

SQLの説明

リタイア指数の高いゲームベスト100です。
長い度は「ひたすら長い。長すぎるよ、このゲーム。」に登録された数、作業度は「作業プレイなゲーム」に登録された数、期待外れ度は「期待を裏切られたゲーム　＆　話と違う！！」に登録された数、リタイア人数は「放棄」にチェックされた数 ＆「途中でリタイア」に登録された数、リタイア率はリタイア人数をデータ数で割った値、リタイア指数はリタイア人数にリタイア率を加味して求めた値です。
購入時には要検討・・・かも。（2005/4/16改訂）

検索結果

実行したSQL

      SELECT '<A href="http://erogamescape.dyndns.org/~ap2/ero/toukei_kaiseki/game.php?game='||g.id||'">'||gamename||'</A><A>'||CASE WHEN g.model='PC' AND g.erogame='f' THEN '(非18禁)' WHEN g.model='PC' THEN '' ELSE '('||g.model||')' END||'</A>' AS ゲーム名, '<A href="http://erogamescape.dyndns.org/~ap2/ero/toukei_kaiseki/brand.php?brand='||b.id||'">'||b.brandname||'</A><A>'||CASE WHEN b.kind='CIRCLE' THEN '(同人)' ELSE '' END||'</A>' AS ブランド名, '<A>'||CASE WHEN point91='ボーイズラブ' THEN 'BL' WHEN point91='KineticNovel' THEN 'KN' WHEN point91='育成モノ' THEN '育成' ELSE substr(translate(point91,'(',' '),1,3) END||'</A>' AS ジャンル, '<FONT COLOR="#'||CASE WHEN current_date-g.sellday+1<=180 AND current_date-g.sellday+1>=31 THEN '0000FF' WHEN current_date-g.sellday+1<31 THEN 'FF0000' ELSE '' END||'">' ||to_char(g.sellday, 'YYYY/MM/DD')|| '</FONT>' AS 発売日, '<FONT COLOR="#'||CASE WHEN g.median >= 85 THEN 'FF0000' WHEN g.median < 85 AND g.median >= 80 THEN '0000FF' ELSE '' END||'">' ||g.median|| '</FONT>' AS 中央値, '<FONT COLOR="#'||CASE WHEN point1 >= 85 THEN 'FF0000' WHEN point1 < 85 AND point1 >= 80 THEN '0000FF' ELSE '' END||'">' ||to_char(point1,'990.99')|| '</FONT>' AS 平均点, '<FONT COLOR="#'||CASE WHEN point2 >=1000 THEN 'FF0000' WHEN point2 < 1000 AND point2 >=500 THEN '0000FF' ELSE '' END||'">' ||point2|| '</FONT>' AS データ数, '<FONT COLOR="#'||CASE WHEN point21 >=50 THEN 'FF0000' WHEN point21 < 50 AND point21 >=20 THEN '0000FF' ELSE '' END||'">' ||point21|| '</FONT>' AS 長い度, '<FONT COLOR="#'||CASE WHEN point22 >=15 THEN 'FF0000' WHEN point22 < 15 AND point22 >=8 THEN '0000FF' ELSE '' END||'">' ||point22|| '</FONT>' AS 作業度, '<FONT COLOR="#'||CASE WHEN point23 >=20 THEN 'FF0000' WHEN point23 < 20 AND point23 >=10 THEN '0000FF' ELSE '' END||'">' ||point23|| '</FONT>' AS 期待外れ度, '<FONT COLOR="#'||CASE WHEN point11 >= 85 THEN 'FF0000' WHEN point11 < 85 AND point11 >= 80 THEN '0000FF' ELSE '' END||'">' ||to_char(point11,'990.99')|| '</FONT>' AS リタイア平均点, '<FONT COLOR="#'||CASE WHEN point12 >=40 THEN 'FF0000' WHEN point12 < 40 AND point12 >=25 THEN '0000FF' ELSE '' END||'">' ||point12|| '</FONT>' AS リタイア人数, '<FONT COLOR="#'||CASE WHEN point12/(point2*1.0) >='0.25' THEN 'FF0000' WHEN point12/(point2*1.0) < '0.25' AND point12/(point2*1.0) >='0.10' THEN '0000FF' ELSE '' END||'">' ||to_char(point12/(point2*1.0),'0.999')|| '</FONT>' AS リタイア率, round(point12*10*sqrt(point12/(point2*1.0)),2) AS リタイア指数 FROM (SELECT ag.game AS id91, a.title AS point91 FROM attributegroupsboolean ag, attributelist a WHERE ag.attribute=a.id AND a.furigana like '01%' UNION ALL SELECT id AS id91, null FROM (SELECT id, count(id) FROM gamelist GROUP BY id)tb91 LEFT JOIN (SELECT ag.game AS id91, a.title AS point91 FROM attributegroupsboolean ag, attributelist a WHERE ag.attribute=a.id AND a.furigana like '01%')tb92 ON id=id91 WHERE point91 IS NULL ORDER BY id91 DESC)tb93, (SELECT game AS id1, AVG(tokuten) AS point1, COUNT(tokuten) AS point2 FROM (SELECT game, tokuten FROM userreview UNION ALL SELECT game, tokuten FROM reviewpagelist WHERE tokuten is not null)tb01 GROUP BY game)tb02 LEFT JOIN (SELECT game AS id1, count(game) AS point21 FROM povgroups WHERE pov=4 GROUP BY game)tb03 USING(id1) LEFT JOIN (SELECT game AS id1, count(game) AS point22 FROM povgroups WHERE pov=155 GROUP BY game)tb04 USING(id1) LEFT JOIN (SELECT game AS id1, count(game) AS point23 FROM povgroups WHERE pov=24 GROUP BY game)tb05 USING(id1), (SELECT id12, AVG(id13) AS point11, COUNT(id12) AS point12 FROM (SELECT uid AS id11, game AS id12, tokuten AS id13 FROM userreview WHERE giveup='t' UNION SELECT id11, id12, id13 FROM (SELECT uid AS id11, game AS id12, tokuten AS id13 FROM userreview)tb11, (SELECT uid AS id21, game AS id22 FROM povgroups WHERE pov=31)tb21 WHERE id11=id21 AND id12=id22)tb13 GROUP BY id12)tb14, gamelist g, brandlist b WHERE id1=g.id AND id12=g.id AND id91=g.id AND g.brandname=b.id AND point2>=5 AND point12>=3 ORDER BY リタイア指数 DESC, point12 DESC, point2 ASC LIMIT 100