EnoelさんのSQLを真似して新たな手法に挑戦！・・・と意気込んで作り始めたものの、SQLより説明文作成に大苦戦。
早くも懲りたかも・・・（＾＾；
| 仮記号 | 置き換え内容 | 置き換え例 | 詳細 |
|---|---|---|---|
| ★１ | ユーザー名 | gouyoku | 調べたいユーザーIDを指定します。 |
| ★２ | 最低一致本数 | 10 | 基準ID（★１で指定したユーザー）と何本以上同じゲームをプレイしているかを指定します。 1以上の整数値を入れて下さい。 |
| 仮記号 | 置き換え内容 | 置き換え例 | 詳細 |
|---|---|---|---|
| ★１ | ユーザー名 | gouyoku | 調べたいユーザーIDを指定します。 |
| ★２ | 最低一致本数 | 10 | 基準ID（★１で指定したユーザー）と何本以上同じゲームをプレイしたユーザーさんの得点を集めるかを指定します。 1以上の整数値を入れて下さい。 この値を大きくするほどプレイ傾向の似ているユーザーさんの得点を集める事になります。 どの位の値を指定するかは、「自分と似た評価のユーザーさんベスト100」の実行結果（一致本数）を参考にして下さい。 |
| ★３ | 最高誤差 | 7.40 | 基準IDと最高何点までの得点誤差があるユーザーさんの得点を集めるかを指定します。 0以上の値（小数点以下の指定も可）を入れて下さい。 この値を小さくするほど評価傾向の似ているユーザーさんの得点を集める事になります。 どの位の値を指定するかは、「自分と似た評価のユーザーさんベスト100」の実行結果（誤差）を参考にして下さい。 |
| ★４ | 最低表示データ数 | 10 | 実行結果表示時に、データ数（★２、★３の条件に該当したユーザーさん）何件以上のゲームを表示するかを指定します。 1以上の整数値を入れて下さい。 |
| 仮記号 | 置き換え内容 | 置き換え例 | 詳細 |
|---|---|---|---|
| ★１ | ユーザー名 | gouyoku | 調べたいユーザーIDを指定します。 |
| ★２ | 最低一致本数 | 10 | 基準ID（★１で指定したユーザー）と何本以上同じゲームをプレイしたユーザーさんの得点からお勧めゲームを求めるかを指定します。 1以上の整数値を入れて下さい。 この値を大きくするほどプレイ傾向の似ているユーザーさんの得点を集める事になります。 どの位の値を指定するかは、「自分と似た評価のユーザーさんベスト100」の実行結果（一致本数）を参考にして下さい。 |
| ★３ | 最高誤差 | 7.40 | 基準IDと最高何点までの得点誤差があるユーザーさんの得点からお勧めゲームを求めるかを指定します。 0以上の値（小数点以下の指定も可）を入れて下さい。 この値を小さくするほど評価傾向の似ているユーザーさんの得点（お勧め）を集める事になります。 どの位の値を指定するかは、「自分と似た評価のユーザーさんベスト100」の実行結果（誤差）を参考にして下さい。 |
| ★４ | 最低お勧め人数 | 10 | 実行結果表示時に、何人以上のお勧め者（★２、★３の条件に該当したユーザーさん）がいたゲームを対象とするかを指定します。 1以上の整数値を入れて下さい。 どの位の値を指定するかは、「自分と似た評価のユーザーさん限定の統計表」の実行結果（データ数）を参考にして下さい。 |
SELECT main AS "</TABLE><TABLE>" FROM ((SELECT '</TABLE><H2>はじめに</H2><DL>これは各自がSELECT文を直接編集する事で求める結果を得るSQLです。<BR>SELECT文中にある★１～★４を「置き換えリスト」の説明を参考に編集して下さい。<BR><BR>SQL文は<BR><OL><LI> <A HREF="#nita1">自分と似た評価のユーザーさんベスト100</A><BR><LI> <A HREF="#nita2">自分と似た評価のユーザーさん限定の統計表</A><BR><LI> <A HREF="#nita3">自分と似た評価のユーザーさんのお勧めゲーム</A><BR></OL>の3種類あります。<BR><BR><BR></DL><H2><A NAME="nita1">SQL1： 自分と似た評価のユーザーさんベスト100</A></H2><DL>自分（指定したユーザー）と似た評価をしているユーザーさん上位100人を調べるSQLです。</DL><H3>実行結果サンプル</H3><DL><FORM METHOD="POST" ACTION="http://erogamescape.dyndns.org/~ap2/ero/toukei_kaiseki/select.php" TARGET="result"><INPUT TYPE=HIDDEN NAME="SQL" VALUE="'||chr(39)||'<A href='||chr(39)||'||chr(34)||'||chr(39)||'http://erogamescape.dyndns.org/~ap2/ero/toukei_kaiseki/user_infomation.php?user=urlencode('||chr(39)||'||chr(34)||'||chr(39)||''||chr(39)||'||u.uid||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||')'||chr(39)||'||chr(34)||'||chr(39)||'>'||chr(39)||'||u.uid||'||chr(39)||'</A>'||chr(39)||' AS ユーザー名, '||chr(39)||'<A href='||chr(39)||'||chr(34)||'||chr(39)||''||chr(39)||'||url||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||' title='||chr(39)||'||chr(34)||'||chr(39)||''||chr(39)||'||title||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||' target='||chr(39)||'||chr(34)||'||chr(39)||'_brank'||chr(39)||'||chr(34)||'||chr(39)||'><CENTER>'||chr(39)||'||CASE WHEN url like '||chr(39)||'http%'||chr(39)||' THEN '||chr(39)||'○'||chr(39)||' ELSE '||chr(39)||''||chr(39)||' END||'||chr(39)||'</CENTER></A>'||chr(39)||' AS HP, substr(translate(hitokoto,'||chr(39)||' '||chr(39)||','||chr(39)||''||chr(39)||'),1,350)||CASE WHEN length(hitokoto)>350 THEN '||chr(39)||' ...'||chr(39)||' ELSE '||chr(39)||''||chr(39)||' END AS コメント, round(point81,1) AS 平均点, round(point83,1) AS 標準偏差, '||chr(39)||'<FONT COLOR='||chr(39)||'||chr(34)||'||chr(39)||'#'||chr(39)||'||CASE WHEN point82>=200 THEN '||chr(39)||'FF0000'||chr(39)||' WHEN point82<200 AND point82>=100 THEN '||chr(39)||'0000FF'||chr(39)||' ELSE '||chr(39)||''||chr(39)||' END||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||'>'||chr(39)||' ||point82|| '||chr(39)||'</FONT>'||chr(39)||' AS 得点数, '||chr(39)||'<FONT COLOR='||chr(39)||'||chr(34)||'||chr(39)||'#'||chr(39)||'||CASE WHEN point2>=200 THEN '||chr(39)||'FF0000'||chr(39)||' WHEN point2<200 AND point2>=100 THEN '||chr(39)||'0000FF'||chr(39)||' ELSE '||chr(39)||''||chr(39)||' END||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||'>'||chr(39)||' ||point2|| '||chr(39)||'</FONT>'||chr(39)||' AS 感想数, '||chr(39)||'<FONT COLOR='||chr(39)||'||chr(34)||'||chr(39)||'#'||chr(39)||'||CASE WHEN point5>=500 THEN '||chr(39)||'FF0000'||chr(39)||' WHEN point5<500 AND point5>=200 THEN '||chr(39)||'0000FF'||chr(39)||' ELSE '||chr(39)||''||chr(39)||' END||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||'>'||chr(39)||' ||CASE WHEN point5 is null THEN '||chr(39)||'0'||chr(39)||' ELSE to_char(point5,'||chr(39)||'999999'||chr(39)||') END|| '||chr(39)||'</FONT>'||chr(39)||' AS POV数, '||chr(39)||'<FONT COLOR='||chr(39)||'||chr(34)||'||chr(39)||'#'||chr(39)||'||CASE WHEN point6>=500 THEN '||chr(39)||'FF0000'||chr(39)||' WHEN point6<500 AND point6>=200 THEN '||chr(39)||'0000FF'||chr(39)||' ELSE '||chr(39)||''||chr(39)||' END||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||'>'||chr(39)||' ||CASE WHEN point6 is null THEN '||chr(39)||'0'||chr(39)||' ELSE to_char(point6,'||chr(39)||'999999'||chr(39)||') END|| '||chr(39)||'</FONT>'||chr(39)||' AS POV感想数, point61 AS 個性派度, '||chr(39)||'<FONT COLOR='||chr(39)||'||chr(34)||'||chr(39)||'#'||chr(39)||'||CASE WHEN point30=point82 THEN '||chr(39)||'999999'||chr(39)||' WHEN point30>=50 THEN '||chr(39)||'FF0000'||chr(39)||' WHEN point30<50 AND point30>=30 THEN '||chr(39)||'0000FF'||chr(39)||' ELSE '||chr(39)||''||chr(39)||' END||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||'>'||chr(39)||' ||point30|| '||chr(39)||'</FONT>'||chr(39)||' AS 一致本数, '||chr(39)||'<FONT COLOR='||chr(39)||'||chr(34)||'||chr(39)||'#'||chr(39)||'||CASE WHEN point31=0 THEN '||chr(39)||'999999'||chr(39)||' ELSE '||chr(39)||''||chr(39)||' END||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||'>'||chr(39)||' ||point31|| '||chr(39)||'</FONT>'||chr(39)||' AS 誤差, '||chr(39)||'<FONT COLOR='||chr(39)||'||chr(34)||'||chr(39)||'#'||chr(39)||'||CASE WHEN date_part('||chr(39)||'day'||chr(39)||','||chr(39)||'now'||chr(39)||'-point89)+1<31 THEN '||chr(39)||'FF0000'||chr(39)||' WHEN date_part('||chr(39)||'day'||chr(39)||','||chr(39)||'now'||chr(39)||'-point89)+1<=180 AND date_part('||chr(39)||'day'||chr(39)||','||chr(39)||'now'||chr(39)||'-point89)+1>=31 THEN '||chr(39)||'0000FF'||chr(39)||' ELSE '||chr(39)||''||chr(39)||' END||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||'>'||chr(39)||' ||to_char(point89, '||chr(39)||'YYYY/MM/DD'||chr(39)||')|| '||chr(39)||'</FONT><BR><DIV align=right><A>(</A><FONT COLOR='||chr(39)||'||chr(34)||'||chr(39)||'#'||chr(39)||'||CASE WHEN date_part('||chr(39)||'day'||chr(39)||',point89-point88)+1>=1460 THEN '||chr(39)||'FF0000'||chr(39)||' WHEN date_part('||chr(39)||'day'||chr(39)||',point89-point88)+1<1460 AND date_part('||chr(39)||'day'||chr(39)||',point89-point88)+1>=730 THEN '||chr(39)||'0000FF'||chr(39)||' ELSE '||chr(39)||''||chr(39)||' END||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||'>'||chr(39)||'||date_part('||chr(39)||'day'||chr(39)||',point89-point88)+1||'||chr(39)||'</FONT><A>日)</A></DIV>'||chr(39)||' AS 最終得点登録日（利用期間） FROM (SELECT uid, AVG(tokuten) AS point81, COUNT(tokuten) AS point82, STDDEV(tokuten) AS point83, MIN(play_tourokubi) AS point88, MAX(play_tourokubi) AS point89 FROM userreview GROUP BY uid) AS u, (SELECT uid, hitokoto, title, url FROM myuserview) AS m, (SELECT uid, count(hitokoto) AS point2 FROM userreview GROUP BY uid) AS h LEFT JOIN (SELECT uid, count(pov) AS point5, count(memo) AS point6 FROM povgroups GROUP BY uid)tb3 USING(uid), (SELECT id52, round(SUM(point61)/COUNT(id52),1) AS point61 FROM (SELECT id52, id41, abs(point41-point51) AS point61 FROM (SELECT game AS id41, AVG(tokuten) AS point41 FROM (SELECT game, tokuten FROM userreview UNION ALL SELECT game, tokuten FROM reviewpagelist WHERE tokuten IS NOT NULL)tb41 GROUP BY game)tb41, (SELECT game AS id51, uid AS id52, tokuten AS point51 FROM userreview WHERE tokuten is not null)tb51 WHERE id41=id51 GROUP BY id41, id52, point61)tb61 GROUP BY id52)tb61, (SELECT id22, COUNT(id22) AS point30, round(SUM(point31)/COUNT(id22),2) AS point31 FROM (SELECT id22, id11, abs(point11-point21) AS point31 FROM (SELECT game AS id11, round(tokuten,2) AS point11 FROM userreview WHERE uid='||chr(39)||'gouyoku'||chr(39)||' AND tokuten is not null)tb11, (SELECT game AS id21, uid AS id22, tokuten AS point21 FROM userreview WHERE tokuten is not null)tb21 WHERE id11=id21 GROUP BY id11, id22, point31)tb31 GROUP BY id22 HAVING COUNT(id22)>='||chr(39)||'10'||chr(39)||' ORDER BY point31 ASC LIMIT 100)tb31 WHERE u.uid=m.uid AND u.uid=h.uid AND u.uid=id52 AND u.uid=id22 ORDER BY point31 ASC, point30 DESC"><INPUT TYPE="submit" VALUE="サンプル"></FORM>次項目、「置き換えリスト」例での実行結果です。<BR>最上位には検索基準となったID（この場合はgouyoku）が来ます。<BR>「個性派度」は各ユーザーさんがつけた得点がErogameScape全体の平均点とどれくらい差があるか、「一致本数」は基準IDが評価したゲーム何本プレイしているか、「誤差」は基準IDが評価したゲームを各ユーザーさんがどの位の得点誤差で評価しているかです。<BR>誤差が小さいほど基準IDと評価が似ている事になります。</DL><H3>置き換えリスト</H3><DL>置き換えは★１、★２の2種類です。</DL><TABLE BORDER><TR><TH>仮記号</TH><TH>置き換え内容</TH><TH>置き換え例</TH><TH>詳細</TH></TR><TR><TD>★１</TD><TD>ユーザー名</TD><TD>gouyoku</TD><TD>調べたいユーザーIDを指定します。</TD></TR><TR><TD>★２</TD><TD>最低一致本数</TD><TD>10</TD><TD>基準ID（★１で指定したユーザー）と何本以上同じゲームをプレイしているかを指定します。<BR>1以上の整数値を入れて下さい。</TD></TR></TABLE><H3>SELECT文</H3><DL>リストの例を参考に★１、★２を求める検索条件に置き換えて【実行】ボタンを押して下さい。<FORM METHOD="POST" ACTION="http://erogamescape.dyndns.org/~ap2/ero/toukei_kaiseki/select.php" TARGET="result"><TEXTAREA NAME="SQL" ROWS="35" COLS="120%">'||chr(39)||'<A href='||chr(39)||'||chr(34)||'||chr(39)||'http://erogamescape.dyndns.org/~ap2/ero/toukei_kaiseki/user_infomation.php?user=urlencode('||chr(39)||'||chr(34)||'||chr(39)||''||chr(39)||'||u.uid||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||')'||chr(39)||'||chr(34)||'||chr(39)||'>'||chr(39)||'||u.uid||'||chr(39)||'</A>'||chr(39)||' AS ユーザー名, '||chr(39)||'<A href='||chr(39)||'||chr(34)||'||chr(39)||''||chr(39)||'||url||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||' title='||chr(39)||'||chr(34)||'||chr(39)||''||chr(39)||'||title||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||' target='||chr(39)||'||chr(34)||'||chr(39)||'_brank'||chr(39)||'||chr(34)||'||chr(39)||'><CENTER>'||chr(39)||'||CASE WHEN url like '||chr(39)||'http%'||chr(39)||' THEN '||chr(39)||'○'||chr(39)||' ELSE '||chr(39)||''||chr(39)||' END||'||chr(39)||'</CENTER></A>'||chr(39)||' AS HP, substr(translate(hitokoto,'||chr(39)||' '||chr(39)||','||chr(39)||''||chr(39)||'),1,350)||CASE WHEN length(hitokoto)>350 THEN '||chr(39)||' ...'||chr(39)||' ELSE '||chr(39)||''||chr(39)||' END AS コメント, round(point81,1) AS 平均点, round(point83,1) AS 標準偏差, '||chr(39)||'<FONT COLOR='||chr(39)||'||chr(34)||'||chr(39)||'#'||chr(39)||'||CASE WHEN point82>=200 THEN '||chr(39)||'FF0000'||chr(39)||' WHEN point82<200 AND point82>=100 THEN '||chr(39)||'0000FF'||chr(39)||' ELSE '||chr(39)||''||chr(39)||' END||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||'>'||chr(39)||' ||point82|| '||chr(39)||'</FONT>'||chr(39)||' AS 得点数, '||chr(39)||'<FONT COLOR='||chr(39)||'||chr(34)||'||chr(39)||'#'||chr(39)||'||CASE WHEN point2>=200 THEN '||chr(39)||'FF0000'||chr(39)||' WHEN point2<200 AND point2>=100 THEN '||chr(39)||'0000FF'||chr(39)||' ELSE '||chr(39)||''||chr(39)||' END||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||'>'||chr(39)||' ||point2|| '||chr(39)||'</FONT>'||chr(39)||' AS 感想数, '||chr(39)||'<FONT COLOR='||chr(39)||'||chr(34)||'||chr(39)||'#'||chr(39)||'||CASE WHEN point5>=500 THEN '||chr(39)||'FF0000'||chr(39)||' WHEN point5<500 AND point5>=200 THEN '||chr(39)||'0000FF'||chr(39)||' ELSE '||chr(39)||''||chr(39)||' END||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||'>'||chr(39)||' ||CASE WHEN point5 is null THEN '||chr(39)||'0'||chr(39)||' ELSE to_char(point5,'||chr(39)||'999999'||chr(39)||') END|| '||chr(39)||'</FONT>'||chr(39)||' AS POV数, '||chr(39)||'<FONT COLOR='||chr(39)||'||chr(34)||'||chr(39)||'#'||chr(39)||'||CASE WHEN point6>=500 THEN '||chr(39)||'FF0000'||chr(39)||' WHEN point6<500 AND point6>=200 THEN '||chr(39)||'0000FF'||chr(39)||' ELSE '||chr(39)||''||chr(39)||' END||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||'>'||chr(39)||' ||CASE WHEN point6 is null THEN '||chr(39)||'0'||chr(39)||' ELSE to_char(point6,'||chr(39)||'999999'||chr(39)||') END|| '||chr(39)||'</FONT>'||chr(39)||' AS POV感想数, point61 AS 個性派度, '||chr(39)||'<FONT COLOR='||chr(39)||'||chr(34)||'||chr(39)||'#'||chr(39)||'||CASE WHEN point30=point82 THEN '||chr(39)||'999999'||chr(39)||' WHEN point30>=50 THEN '||chr(39)||'FF0000'||chr(39)||' WHEN point30<50 AND point30>=30 THEN '||chr(39)||'0000FF'||chr(39)||' ELSE '||chr(39)||''||chr(39)||' END||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||'>'||chr(39)||' ||point30|| '||chr(39)||'</FONT>'||chr(39)||' AS 一致本数, '||chr(39)||'<FONT COLOR='||chr(39)||'||chr(34)||'||chr(39)||'#'||chr(39)||'||CASE WHEN point31=0 THEN '||chr(39)||'999999'||chr(39)||' ELSE '||chr(39)||''||chr(39)||' END||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||'>'||chr(39)||' ||point31|| '||chr(39)||'</FONT>'||chr(39)||' AS 誤差, '||chr(39)||'<FONT COLOR='||chr(39)||'||chr(34)||'||chr(39)||'#'||chr(39)||'||CASE WHEN date_part('||chr(39)||'day'||chr(39)||','||chr(39)||'now'||chr(39)||'-point89)+1<31 THEN '||chr(39)||'FF0000'||chr(39)||' WHEN date_part('||chr(39)||'day'||chr(39)||','||chr(39)||'now'||chr(39)||'-point89)+1<=180 AND date_part('||chr(39)||'day'||chr(39)||','||chr(39)||'now'||chr(39)||'-point89)+1>= 31 THEN '||chr(39)||'0000FF'||chr(39)||' ELSE '||chr(39)||''||chr(39)||' END||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||'>'||chr(39)||' ||to_char(point89, '||chr(39)||'YYYY/MM/DD'||chr(39)||')|| '||chr(39)||'</FONT><BR><DIV align=right><A>(</A><FONT COLOR='||chr(39)||'||chr(34)||'||chr(39)||'#'||chr(39)||'||CASE WHEN date_part('||chr(39)||'day'||chr(39)||',point89-point88)+1>=1460 THEN '||chr(39)||'FF0000'||chr(39)||' WHEN date_part('||chr(39)||'day'||chr(39)||',point89-point88)+1<1460 AND date_part('||chr(39)||'day'||chr(39)||',point89-point88)+1>=730 THEN '||chr(39)||'0000FF'||chr(39)||' ELSE '||chr(39)||''||chr(39)||' END||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||'>'||chr(39)||'||date_part('||chr(39)||'day'||chr(39)||',point89-point88)+1||'||chr(39)||'</FONT><A>日)</A></DIV>'||chr(39)||' AS 最終得点登録日（利用期間） FROM (SELECT uid, AVG(tokuten) AS point81, COUNT(tokuten) AS point82, STDDEV(tokuten) AS point83, MIN(play_tourokubi) AS point88, MAX(play_tourokubi) AS point89 FROM userreview GROUP BY uid) AS u, (SELECT uid, hitokoto, title, url FROM myuserview) AS m, (SELECT uid, count(hitokoto) AS point2 FROM userreview GROUP BY uid) AS h LEFT JOIN (SELECT uid, count(pov) AS point5, count(memo) AS point6 FROM povgroups GROUP BY uid)tb3 USING(uid), (SELECT id52, round(SUM(point61)/COUNT(id52),1) AS point61 FROM (SELECT id52, id41, abs(point41-point51) AS point61 FROM (SELECT game AS id41, AVG(tokuten) AS point41 FROM (SELECT game, tokuten FROM userreview UNION ALL SELECT game, tokuten FROM reviewpagelist WHERE tokuten IS NOT NULL)tb41 GROUP BY game)tb41, (SELECT game AS id51, uid AS id52, tokuten AS point51 FROM userreview WHERE tokuten is not null)tb51 WHERE id41=id51 GROUP BY id41, id52, point61)tb61 GROUP BY id52)tb61, (SELECT id22, COUNT(id22) AS point30, round(SUM(point31)/COUNT(id22),2) AS point31 FROM (SELECT id22, id11, abs(point11-point21) AS point31 FROM (SELECT game AS id11, round(tokuten,2) AS point11 FROM userreview WHERE uid='||chr(39)||'★１'||chr(39)||' AND tokuten is not null)tb11, (SELECT game AS id21, uid AS id22, tokuten AS point21 FROM userreview WHERE tokuten is not null)tb21 WHERE id11=id21 GROUP BY id11, id22, point31)tb31 GROUP BY id22 HAVING COUNT(id22)>='||chr(39)||'★２'||chr(39)||' ORDER BY point31 ASC LIMIT 100)tb31 WHERE u.uid=m.uid AND u.uid=h.uid AND u.uid=id52 AND u.uid=id22 ORDER BY point31 ASC, point30 DESC</TEXTAREA><BR><INPUT TYPE="submit" VALUE="実行"> <INPUT TYPE="reset" value="初期状態に戻す"></FORM></DL><BR><H2><A NAME="nita2">SQL2： 自分と似た評価のユーザーさん限定の統計表</A></H2><DL>自分（指定したユーザー）と似た評価をしているユーザーさん限定の得点で統計表を求めるSQLです。</DL><H3>実行結果サンプル</H3><DL><FORM METHOD="POST" ACTION="http://erogamescape.dyndns.org/~ap2/ero/toukei_kaiseki/select.php" TARGET="result"><INPUT TYPE=HIDDEN NAME="SQL" VALUE="'||chr(39)||'<A href='||chr(39)||'||chr(34)||'||chr(39)||'http://erogamescape.dyndns.org/~ap2/ero/toukei_kaiseki/game.php?game='||chr(39)||'||g.id||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||'>'||chr(39)||'||gamename||'||chr(39)||'</A><A>'||chr(39)||'||CASE WHEN g.model='||chr(39)||'PC'||chr(39)||' AND g.erogame='||chr(39)||'f'||chr(39)||' THEN '||chr(39)||'(非18禁)'||chr(39)||' WHEN g.model='||chr(39)||'PC'||chr(39)||' THEN '||chr(39)||''||chr(39)||' ELSE '||chr(39)||'('||chr(39)||'||g.model||'||chr(39)||')'||chr(39)||' END||'||chr(39)||'</A>'||chr(39)||' AS ゲーム名, '||chr(39)||'<A href='||chr(39)||'||chr(34)||'||chr(39)||'http://erogamescape.dyndns.org/~ap2/ero/toukei_kaiseki/brand.php?brand='||chr(39)||'||b.id||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||'>'||chr(39)||'||b.brandname||'||chr(39)||'</A><A>'||chr(39)||'||CASE WHEN b.kind='||chr(39)||'CIRCLE'||chr(39)||' THEN '||chr(39)||'(同人)'||chr(39)||' ELSE '||chr(39)||''||chr(39)||' END||'||chr(39)||'</A>'||chr(39)||' AS ブランド名, '||chr(39)||'<A>'||chr(39)||'||CASE WHEN point91='||chr(39)||'ボーイズラブ'||chr(39)||' THEN '||chr(39)||'BL'||chr(39)||' WHEN point91='||chr(39)||'KineticNovel'||chr(39)||' THEN '||chr(39)||'KN'||chr(39)||'WHEN point91='||chr(39)||'育成モノ'||chr(39)||' THEN '||chr(39)||'育成'||chr(39)||' ELSE substr(translate(point91,'||chr(39)||'('||chr(39)||','||chr(39)||' '||chr(39)||'),1,3) END||'||chr(39)||'</A>'||chr(39)||' AS ジャンル, '||chr(39)||'<FONT COLOR='||chr(39)||'||chr(34)||'||chr(39)||'#'||chr(39)||'||CASE WHEN current_date-g.sellday+1<=180 AND current_date-g.sellday+1>=31 THEN '||chr(39)||'0000FF'||chr(39)||' WHEN current_date-g.sellday+1<31 THEN '||chr(39)||'FF0000'||chr(39)||' ELSE '||chr(39)||''||chr(39)||' END||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||'>'||chr(39)||' ||to_char(g.sellday, '||chr(39)||'YYYY/MM/DD'||chr(39)||')|| '||chr(39)||'</FONT>'||chr(39)||' AS 発売日, '||chr(39)||'<FONT COLOR='||chr(39)||'||chr(34)||'||chr(39)||'#'||chr(39)||'||CASE WHEN g.median is not null THEN '||chr(39)||'999999'||chr(39)||' ELSE '||chr(39)||''||chr(39)||' END||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||'>'||chr(39)||' ||g.median|| '||chr(39)||'</FONT>'||chr(39)||' AS 中央値, '||chr(39)||'<FONT COLOR='||chr(39)||'||chr(34)||'||chr(39)||'#'||chr(39)||'||CASE WHEN point52>=90 THEN '||chr(39)||'FF0000'||chr(39)||' WHEN point52<90 AND point52>=80 THEN '||chr(39)||'0000FF'||chr(39)||' ELSE '||chr(39)||''||chr(39)||' END||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||'>'||chr(39)||' ||CASE WHEN point52=0 THEN null ELSE point52 END|| '||chr(39)||'</FONT>'||chr(39)||' AS 自己評価, '||chr(39)||'<FONT COLOR='||chr(39)||'||chr(34)||'||chr(39)||'#'||chr(39)||'||CASE WHEN point11-g.median>=3 THEN '||chr(39)||'FF0000'||chr(39)||' WHEN point11-g.median<=-5 THEN '||chr(39)||'0000FF'||chr(39)||' ELSE '||chr(39)||''||chr(39)||' END||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||'>'||chr(39)||' ||to_char(point11,'||chr(39)||'990.99'||chr(39)||')|| '||chr(39)||'</FONT>'||chr(39)||' AS 平均点, to_char(point13,'||chr(39)||'90.9'||chr(39)||') AS 標準偏差, point14 AS 最高点, point15 AS 最低点, '||chr(39)||'<FONT COLOR='||chr(39)||'||chr(34)||'||chr(39)||'#'||chr(39)||'||CASE WHEN point12>=100 THEN '||chr(39)||'FF0000'||chr(39)||' WHEN point12<100 AND point12>=50 THEN '||chr(39)||'0000FF'||chr(39)||' ELSE '||chr(39)||''||chr(39)||' END||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||'>'||chr(39)||' ||point12|| '||chr(39)||'</FONT>'||chr(39)||' AS データ数, '||chr(39)||'<A href='||chr(39)||'||chr(34)||'||chr(39)||''||chr(39)||'||g.shoukai||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||' target='||chr(39)||'||chr(34)||'||chr(39)||'_brank'||chr(39)||'||chr(34)||'||chr(39)||'><center>'||chr(39)||'||CASE WHEN g.shoukai like '||chr(39)||'http%'||chr(39)||' THEN '||chr(39)||'○'||chr(39)||' ELSE '||chr(39)||''||chr(39)||' END||'||chr(39)||'</center></A>'||chr(39)||' AS 紹介1, '||chr(39)||'<A href='||chr(39)||'||chr(34)||'||chr(39)||'http://www.getchu.com/soft.phtml?id='||chr(39)||'||g.comike||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||' target='||chr(39)||'||chr(34)||'||chr(39)||'_brank'||chr(39)||'||chr(34)||'||chr(39)||'><center>'||chr(39)||'||CASE WHEN g.comike is not null THEN '||chr(39)||'○'||chr(39)||' ELSE '||chr(39)||''||chr(39)||' END||'||chr(39)||'</center></A>'||chr(39)||' AS 紹介2, '||chr(39)||'<A href='||chr(39)||'||chr(34)||'||chr(39)||'http://em.nog.jp/detail.php?game='||chr(39)||'||g.id||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||' target='||chr(39)||'||chr(34)||'||chr(39)||'_brank'||chr(39)||'||chr(34)||'||chr(39)||'><center>'||chr(39)||'||CASE WHEN g.id is not null THEN '||chr(39)||'○'||chr(39)||' ELSE '||chr(39)||''||chr(39)||' END||'||chr(39)||'</center></A>'||chr(39)||' AS 紹介3 FROM (SELECT ag.game AS id91, a.title AS point91 FROM attributegroupsboolean ag, attributelist a WHERE ag.attribute=a.id AND a.furigana like '||chr(39)||'01%'||chr(39)||')tb91 RIGHT JOIN (SELECT game AS id11, AVG(tokuten) AS point11, COUNT(tokuten) AS point12, STDDEV(tokuten) AS point13, MAX(tokuten) AS point14, MIN(tokuten) AS point15 FROM (SELECT game, tokuten FROM userreview WHERE tokuten is not null AND uid IN (SELECT id22 FROM (SELECT id22, id11, abs(point11-point21) AS point31 FROM (SELECT game AS id11, round(tokuten,2) AS point11 FROM userreview WHERE uid='||chr(39)||'gouyoku'||chr(39)||' AND tokuten is not null)tb11, (SELECT game AS id21, uid AS id22, tokuten AS point21 FROM userreview WHERE tokuten is not null)tb21 WHERE id11=id21 GROUP BY id11, id22, point31)tb31 GROUP BY id22 HAVING COUNT(id22)>='||chr(39)||'10'||chr(39)||' AND SUM(point31)/COUNT(id22)<='||chr(39)||'7.40'||chr(39)||'))tb31 GROUP BY game HAVING COUNT(tokuten)>='||chr(39)||'10'||chr(39)||')tb41 ON id91=id11 LEFT JOIN (SELECT game AS id51, tokuten AS point52 FROM userreview WHERE uid='||chr(39)||'gouyoku'||chr(39)||' AND tokuten is not null)tb51 ON id11=id51, gamelist g, brandlist b WHERE id11=g.id AND g.brandname=b.id ORDER BY point11 DESC, b.brandname ASC"><INPUT TYPE="submit" VALUE="サンプル"></FORM>次項目、「置き換えリスト」例での実行結果です。<BR>「自己評価」は検索基準となったID（この場合はgouyoku）が登録した得点で、「紹介1」はメーカー、「紹介2」はGetchu.com、「紹介3」はerogmapのゲーム紹介ページへのリンクとなっています。<BR>※ 「中央値」のみ全ユーザーとレビューサイトの得点を対象としています</DL><H3>置き換えリスト</H3><DL>置き換えは★１～★４の4種類です。</DL><TABLE BORDER><TR><TH>仮記号</TH><TH>置き換え内容</TH><TH>置き換え例</TH><TH>詳細</TH></TR><TR><TD>★１</TD><TD>ユーザー名</TD><TD>gouyoku</TD><TD>調べたいユーザーIDを指定します。</TD></TR><TR><TD>★２</TD><TD>最低一致本数</TD><TD>10</TD><TD>基準ID（★１で指定したユーザー）と何本以上同じゲームをプレイしたユーザーさんの得点を集めるかを指定します。<BR>1以上の整数値を入れて下さい。<BR>この値を大きくするほどプレイ傾向の似ているユーザーさんの得点を集める事になります。<BR>どの位の値を指定するかは、「<A HREF="#nita1">自分と似た評価のユーザーさんベスト100</A>」の実行結果（一致本数）を参考にして下さい。</TD></TR><TR><TD>★３</TD><TD>最高誤差</TD><TD>7.40</TD><TD>基準IDと最高何点までの得点誤差があるユーザーさんの得点を集めるかを指定します。<BR>0以上の値（小数点以下の指定も可）を入れて下さい。<BR>この値を小さくするほど評価傾向の似ているユーザーさんの得点を集める事になります。<BR>どの位の値を指定するかは、「<A HREF="#nita1">自分と似た評価のユーザーさんベスト100</A>」の実行結果（誤差）を参考にして下さい。</TD></TR><TR><TD>★４</TD><TD>最低表示データ数</TD><TD>10</TD><TD>実行結果表示時に、データ数（★２、★３の条件に該当したユーザーさん）何件以上のゲームを表示するかを指定します。<BR>1以上の整数値を入れて下さい。</TD></TR></TABLE><H3>SELECT文</H3><DL>リストの例を参考に★１～★４を求める検索条件に置き換えて【実行】ボタンを押して下さい。<BR>（★１は2箇所あります）<FORM METHOD="POST" ACTION="http://erogamescape.dyndns.org/~ap2/ero/toukei_kaiseki/select.php" TARGET="result"><TEXTAREA NAME="SQL" ROWS="33" COLS="120%">'||chr(39)||'<A href='||chr(39)||'||chr(34)||'||chr(39)||'http://erogamescape.dyndns.org/~ap2/ero/toukei_kaiseki/game.php?game='||chr(39)||'||g.id||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||'>'||chr(39)||'||gamename||'||chr(39)||'</A><A>'||chr(39)||'||CASE WHEN g.model='||chr(39)||'PC'||chr(39)||' AND g.erogame='||chr(39)||'f'||chr(39)||' THEN '||chr(39)||'(非18禁)'||chr(39)||' WHEN g.model='||chr(39)||'PC'||chr(39)||' THEN '||chr(39)||''||chr(39)||' ELSE '||chr(39)||'('||chr(39)||'||g.model||'||chr(39)||')'||chr(39)||' END||'||chr(39)||'</A>'||chr(39)||' AS ゲーム名, '||chr(39)||'<A href='||chr(39)||'||chr(34)||'||chr(39)||'http://erogamescape.dyndns.org/~ap2/ero/toukei_kaiseki/brand.php?brand='||chr(39)||'||b.id||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||'>'||chr(39)||'||b.brandname||'||chr(39)||'</A><A>'||chr(39)||'||CASE WHEN b.kind='||chr(39)||'CIRCLE'||chr(39)||' THEN '||chr(39)||'(同人)'||chr(39)||' ELSE '||chr(39)||''||chr(39)||' END||'||chr(39)||'</A>'||chr(39)||' AS ブランド名, '||chr(39)||'<A>'||chr(39)||'||CASE WHEN point91='||chr(39)||'ボーイズラブ'||chr(39)||' THEN '||chr(39)||'BL'||chr(39)||' WHEN point91='||chr(39)||'KineticNovel'||chr(39)||' THEN '||chr(39)||'KN'||chr(39)||'WHEN point91='||chr(39)||'育成モノ'||chr(39)||' THEN '||chr(39)||'育成'||chr(39)||' ELSE substr(translate(point91,'||chr(39)||'('||chr(39)||','||chr(39)||' '||chr(39)||'),1,3) END||'||chr(39)||'</A>'||chr(39)||' AS ジャンル, '||chr(39)||'<FONT COLOR='||chr(39)||'||chr(34)||'||chr(39)||'#'||chr(39)||'||CASE WHEN current_date-g.sellday+1<=180 AND current_date-g.sellday+1>=31 THEN '||chr(39)||'0000FF'||chr(39)||' WHEN current_date-g.sellday+1<31 THEN '||chr(39)||'FF0000'||chr(39)||' ELSE '||chr(39)||''||chr(39)||' END||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||'>'||chr(39)||' ||to_char(g.sellday, '||chr(39)||'YYYY/MM/DD'||chr(39)||')|| '||chr(39)||'</FONT>'||chr(39)||' AS 発売日, '||chr(39)||'<FONT COLOR='||chr(39)||'||chr(34)||'||chr(39)||'#'||chr(39)||'||CASE WHEN g.median is not null THEN '||chr(39)||'999999'||chr(39)||' ELSE '||chr(39)||''||chr(39)||' END||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||'>'||chr(39)||' ||g.median|| '||chr(39)||'</FONT>'||chr(39)||' AS 中央値, '||chr(39)||'<FONT COLOR='||chr(39)||'||chr(34)||'||chr(39)||'#'||chr(39)||'||CASE WHEN point52>=90 THEN '||chr(39)||'FF0000'||chr(39)||' WHEN point52<90 AND point52>=80 THEN '||chr(39)||'0000FF'||chr(39)||' ELSE '||chr(39)||''||chr(39)||' END||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||'>'||chr(39)||' ||CASE WHEN point52=0 THEN null ELSE point52 END|| '||chr(39)||'</FONT>'||chr(39)||' AS 自己評価, '||chr(39)||'<FONT COLOR='||chr(39)||'||chr(34)||'||chr(39)||'#'||chr(39)||'||CASE WHEN point11-g.median>=3 THEN '||chr(39)||'FF0000'||chr(39)||' WHEN point11-g.median<=-5 THEN '||chr(39)||'0000FF'||chr(39)||' ELSE '||chr(39)||''||chr(39)||' END||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||'>'||chr(39)||' ||to_char(point11,'||chr(39)||'990.99'||chr(39)||')|| '||chr(39)||'</FONT>'||chr(39)||' AS 平均点, to_char(point13,'||chr(39)||'90.9'||chr(39)||') AS 標準偏差, point14 AS 最高点, point15 AS 最低点, '||chr(39)||'<FONT COLOR='||chr(39)||'||chr(34)||'||chr(39)||'#'||chr(39)||'||CASE WHEN point12>=100 THEN '||chr(39)||'FF0000'||chr(39)||' WHEN point12<100 AND point12>=50 THEN '||chr(39)||'0000FF'||chr(39)||' ELSE '||chr(39)||''||chr(39)||' END||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||'>'||chr(39)||' ||point12|| '||chr(39)||'</FONT>'||chr(39)||' AS データ数, '||chr(39)||'<A href='||chr(39)||'||chr(34)||'||chr(39)||''||chr(39)||'||g.shoukai||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||' target='||chr(39)||'||chr(34)||'||chr(39)||'_brank'||chr(39)||'||chr(34)||'||chr(39)||'><center>'||chr(39)||'||CASE WHEN g.shoukai like '||chr(39)||'http%'||chr(39)||' THEN '||chr(39)||'○'||chr(39)||' ELSE '||chr(39)||''||chr(39)||' END||'||chr(39)||'</center></A>'||chr(39)||' AS 紹介1, '||chr(39)||'<A href='||chr(39)||'||chr(34)||'||chr(39)||'http://www.getchu.com/soft.phtml?id='||chr(39)||'||g.comike||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||' target='||chr(39)||'||chr(34)||'||chr(39)||'_brank'||chr(39)||'||chr(34)||'||chr(39)||'><center>'||chr(39)||'||CASE WHEN g.comike is not null THEN '||chr(39)||'○'||chr(39)||' ELSE '||chr(39)||''||chr(39)||' END||'||chr(39)||'</center></A>'||chr(39)||' AS 紹介2, '||chr(39)||'<A href='||chr(39)||'||chr(34)||'||chr(39)||'http://em.nog.jp/detail.php?game='||chr(39)||'||g.id||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||' target='||chr(39)||'||chr(34)||'||chr(39)||'_brank'||chr(39)||'||chr(34)||'||chr(39)||'><center>'||chr(39)||'||CASE WHEN g.id is not null THEN '||chr(39)||'○'||chr(39)||' ELSE '||chr(39)||''||chr(39)||' END||'||chr(39)||'</center></A>'||chr(39)||' AS 紹介3 FROM (SELECT ag.game AS id91, a.title AS point91 FROM attributegroupsboolean ag, attributelist a WHERE ag.attribute=a.id AND a.furigana like '||chr(39)||'01%'||chr(39)||')tb91 RIGHT JOIN (SELECT game AS id11, AVG(tokuten) AS point11, COUNT(tokuten) AS point12, STDDEV(tokuten) AS point13, MAX(tokuten) AS point14, MIN(tokuten) AS point15 FROM (SELECT game, tokuten FROM userreview WHERE tokuten is not null AND uid IN (SELECT id22 FROM (SELECT id22, id11, abs(point11-point21) AS point31 FROM (SELECT game AS id11, round(tokuten,2) AS point11 FROM userreview WHERE uid='||chr(39)||'★１'||chr(39)||' AND tokuten is not null)tb11, (SELECT game AS id21, uid AS id22, tokuten AS point21 FROM userreview WHERE tokuten is not null)tb21 WHERE id11=id21 GROUP BY id11, id22, point31)tb31 GROUP BY id22 HAVING COUNT(id22)>='||chr(39)||'★２'||chr(39)||' AND SUM(point31)/COUNT(id22)<='||chr(39)||'★３'||chr(39)||'))tb31 GROUP BY game HAVING COUNT(tokuten)>='||chr(39)||'★４'||chr(39)||')tb41 ON id91=id11 LEFT JOIN (SELECT game AS id51, tokuten AS point52 FROM userreview WHERE uid='||chr(39)||'★１'||chr(39)||' AND tokuten is not null)tb51 ON id11=id51, gamelist g, brandlist b WHERE id11=g.id AND g.brandname=b.id ORDER BY point11 DESC, b.brandname ASC</TEXTAREA><BR><INPUT TYPE="submit" VALUE="実行"> <INPUT TYPE="reset" value="初期状態に戻す"></FORM></DL><BR><H2><A NAME="nita3">SQL3： 自分と似た評価のユーザーさんのお勧めゲーム</A></H2><DL>自分（指定したユーザー）と似た評価をしているユーザーさんの得点からお勧めゲームを調べるSQLです。</DL><H3>実行結果サンプル</H3><DL><FORM METHOD="POST" ACTION="http://erogamescape.dyndns.org/~ap2/ero/toukei_kaiseki/select.php" TARGET="result"><INPUT TYPE=HIDDEN NAME="SQL" VALUE="'||chr(39)||'<A href='||chr(39)||'||chr(34)||'||chr(39)||'http://erogamescape.dyndns.org/~ap2/ero/toukei_kaiseki/game.php?game='||chr(39)||'||g.id||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||'>'||chr(39)||'||gamename||'||chr(39)||'</A><A>'||chr(39)||'||CASE WHEN g.model='||chr(39)||'PC'||chr(39)||' AND g.erogame='||chr(39)||'f'||chr(39)||' THEN '||chr(39)||'(非18禁)'||chr(39)||' WHEN g.model='||chr(39)||'PC'||chr(39)||' THEN '||chr(39)||''||chr(39)||' ELSE '||chr(39)||'('||chr(39)||'||g.model||'||chr(39)||')'||chr(39)||' END||'||chr(39)||'</A>'||chr(39)||' AS ゲーム名, '||chr(39)||'<A href='||chr(39)||'||chr(34)||'||chr(39)||'http://erogamescape.dyndns.org/~ap2/ero/toukei_kaiseki/brand.php?brand='||chr(39)||'||b.id||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||'>'||chr(39)||'||b.brandname||'||chr(39)||'</A><A>'||chr(39)||'||CASE WHEN b.kind='||chr(39)||'CIRCLE'||chr(39)||' THEN '||chr(39)||'(同人)'||chr(39)||' ELSE '||chr(39)||''||chr(39)||' END||'||chr(39)||'</A>'||chr(39)||' AS ブランド名, '||chr(39)||'<A>'||chr(39)||'||CASE WHEN point91='||chr(39)||'ボーイズラブ'||chr(39)||' THEN '||chr(39)||'BL'||chr(39)||' WHEN point91='||chr(39)||'KineticNovel'||chr(39)||' THEN '||chr(39)||'KN'||chr(39)||'WHEN point91='||chr(39)||'育成モノ'||chr(39)||' THEN '||chr(39)||'育成'||chr(39)||' ELSE substr(translate(point91,'||chr(39)||'('||chr(39)||','||chr(39)||' '||chr(39)||'),1,3) END||'||chr(39)||'</A>'||chr(39)||' AS ジャンル, '||chr(39)||'<FONT COLOR='||chr(39)||'||chr(34)||'||chr(39)||'#'||chr(39)||'||CASE WHEN current_date-g.sellday+1<=180 AND current_date-g.sellday+1>=31 THEN '||chr(39)||'0000FF'||chr(39)||' WHEN current_date-g.sellday+1<31 THEN '||chr(39)||'FF0000'||chr(39)||' WHEN current_date-g.sellday+1>1825 THEN '||chr(39)||'999999'||chr(39)||' ELSE '||chr(39)||''||chr(39)||' END||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||'>'||chr(39)||' ||to_char(g.sellday, '||chr(39)||'YYYY/MM/DD'||chr(39)||')|| '||chr(39)||'</FONT>'||chr(39)||' AS 発売日, '||chr(39)||'<FONT COLOR='||chr(39)||'||chr(34)||'||chr(39)||'#'||chr(39)||'||CASE WHEN g.median>=85 THEN '||chr(39)||'FF0000'||chr(39)||' WHEN g.median<85 AND g.median>=80 THEN '||chr(39)||'0000FF'||chr(39)||' ELSE '||chr(39)||''||chr(39)||' END||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||'>'||chr(39)||' ||g.median|| '||chr(39)||'</FONT>'||chr(39)||' AS 中央値, '||chr(39)||'<FONT COLOR='||chr(39)||'||chr(34)||'||chr(39)||'#'||chr(39)||'||CASE WHEN point1>=85 THEN '||chr(39)||'FF0000'||chr(39)||' WHEN point1<85 AND point1>=80 THEN '||chr(39)||'0000FF'||chr(39)||' ELSE '||chr(39)||''||chr(39)||' END||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||'>'||chr(39)||' ||to_char(point1,'||chr(39)||'990.99'||chr(39)||')|| '||chr(39)||'</FONT>'||chr(39)||' AS 平均点, '||chr(39)||'<FONT COLOR='||chr(39)||'||chr(34)||'||chr(39)||'#'||chr(39)||'||CASE WHEN point2>=1000 THEN '||chr(39)||'FF0000'||chr(39)||' WHEN point2<1000 AND point2>=500 THEN '||chr(39)||'0000FF'||chr(39)||' ELSE '||chr(39)||''||chr(39)||' END||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||'>'||chr(39)||' ||point2|| '||chr(39)||'</FONT>'||chr(39)||' AS データ数, round((point11*2-point1+point12*0.01)*30,0) AS お勧め度, '||chr(39)||'<A href='||chr(39)||'||chr(34)||'||chr(39)||''||chr(39)||'||g.shoukai||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||' target='||chr(39)||'||chr(34)||'||chr(39)||'_brank'||chr(39)||'||chr(34)||'||chr(39)||'><center>'||chr(39)||'||CASE WHEN g.shoukai like '||chr(39)||'http%'||chr(39)||' THEN '||chr(39)||'○'||chr(39)||' ELSE '||chr(39)||''||chr(39)||' END||'||chr(39)||'</center></A>'||chr(39)||' AS 紹介1, '||chr(39)||'<A href='||chr(39)||'||chr(34)||'||chr(39)||'http://www.getchu.com/soft.phtml?id='||chr(39)||'||g.comike||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||' target='||chr(39)||'||chr(34)||'||chr(39)||'_brank'||chr(39)||'||chr(34)||'||chr(39)||'><center>'||chr(39)||'||CASE WHEN g.comike is not null THEN '||chr(39)||'○'||chr(39)||' ELSE '||chr(39)||''||chr(39)||' END||'||chr(39)||'</center></A>'||chr(39)||' AS 紹介2, '||chr(39)||'<A href='||chr(39)||'||chr(34)||'||chr(39)||'http://em.nog.jp/detail.php?game='||chr(39)||'||g.id||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||' target='||chr(39)||'||chr(34)||'||chr(39)||'_brank'||chr(39)||'||chr(34)||'||chr(39)||'><center>'||chr(39)||'||CASE WHEN g.id is not null THEN '||chr(39)||'○'||chr(39)||' ELSE '||chr(39)||''||chr(39)||' END||'||chr(39)||'</center></A>'||chr(39)||' AS 紹介3 FROM (SELECT ag.game AS id91, a.title AS point91 FROM attributegroupsboolean ag, attributelist a WHERE ag.attribute=a.id AND a.furigana like '||chr(39)||'01%'||chr(39)||')tb91 RIGHT JOIN (SELECT game AS id11, AVG(tokuten) AS point11, COUNT(tokuten) AS point12 FROM (SELECT game, tokuten FROM userreview WHERE tokuten is not null AND uid IN (SELECT id22 FROM (SELECT id22, id11, abs(point11-point21) AS point31 FROM (SELECT game AS id11, round(tokuten,2) AS point11 FROM userreview WHERE uid='||chr(39)||'gouyoku'||chr(39)||' AND tokuten is not null)tb11, (SELECT game AS id21, uid AS id22, tokuten AS point21 FROM userreview WHERE tokuten is not null)tb21 WHERE id11=id21 GROUP BY id11, id22, point31)tb31 GROUP BY id22 HAVING COUNT(id22)>='||chr(39)||'10'||chr(39)||' AND SUM(point31)/COUNT(id22)<='||chr(39)||'7.40'||chr(39)||'))tb31 GROUP BY game HAVING COUNT(tokuten)>='||chr(39)||'10'||chr(39)||')tb41 ON id91=id11 LEFT JOIN (SELECT game AS id51, tokuten AS id52 FROM userreview WHERE uid='||chr(39)||'gouyoku'||chr(39)||' AND tokuten is not null)tb51 ON id11=id51, (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, gamelist g, brandlist b WHERE id1=g.id AND id11=g.id AND g.brandname=b.id AND id52 is null AND point11>=75 AND point11-point1>=-2 ORDER BY お勧め度 DESC, point1 DESC LIMIT 100"><INPUT TYPE="submit" VALUE="サンプル"></FORM>次項目、「置き換えリスト」例での実行結果です。<BR>（この場合はgouyokuに似た評価のユーザーさんが、gouyokuにお勧めなゲーム）<BR>「中央値」「平均点」「データ数」は全データから求めた値で、「紹介1」はメーカー、「紹介2」はGetchu.com、「紹介3」はerogmapのゲーム紹介ページへのリンクとなっています。</DL><H3>置き換えリスト</H3><DL>置き換えは★１～★４の4種類です。</DL><TABLE BORDER><TR><TH>仮記号</TH><TH>置き換え内容</TH><TH>置き換え例</TH><TH>詳細</TH></TR><TR><TD>★１</TD><TD>ユーザー名</TD><TD>gouyoku</TD><TD>調べたいユーザーIDを指定します。</TD></TR><TR><TD>★２</TD><TD>最低一致本数</TD><TD>10</TD><TD>基準ID（★１で指定したユーザー）と何本以上同じゲームをプレイしたユーザーさんの得点からお勧めゲームを求めるかを指定します。<BR>1以上の整数値を入れて下さい。<BR>この値を大きくするほどプレイ傾向の似ているユーザーさんの得点を集める事になります。<BR>どの位の値を指定するかは、「<A HREF="#nita1">自分と似た評価のユーザーさんベスト100</A>」の実行結果（一致本数）を参考にして下さい。</TD></TR><TR><TD>★３</TD><TD>最高誤差</TD><TD>7.40</TD><TD>基準IDと最高何点までの得点誤差があるユーザーさんの得点からお勧めゲームを求めるかを指定します。<BR>0以上の値（小数点以下の指定も可）を入れて下さい。<BR>この値を小さくするほど評価傾向の似ているユーザーさんの得点（お勧め）を集める事になります。<BR>どの位の値を指定するかは、「<A HREF="#nita1">自分と似た評価のユーザーさんベスト100</A>」の実行結果（誤差）を参考にして下さい。</TD></TR><TR><TD>★４</TD><TD>最低お勧め人数</TD><TD>10</TD><TD>実行結果表示時に、何人以上のお勧め者（★２、★３の条件に該当したユーザーさん）がいたゲームを対象とするかを指定します。<BR>1以上の整数値を入れて下さい。<BR>どの位の値を指定するかは、「<A HREF="#nita2">自分と似た評価のユーザーさん限定の統計表</A>」の実行結果（データ数）を参考にして下さい。</TD></TR></TABLE><H3>SELECT文</H3><DL>リストの例を参考に★１～★４を求める検索条件に置き換えて【実行】ボタンを押して下さい。<BR>（★１は2箇所あります）<FORM METHOD="POST" ACTION="http://erogamescape.dyndns.org/~ap2/ero/toukei_kaiseki/select.php" TARGET="result"><TEXTAREA NAME="SQL" ROWS="34" COLS="120%">'||chr(39)||'<A href='||chr(39)||'||chr(34)||'||chr(39)||'http://erogamescape.dyndns.org/~ap2/ero/toukei_kaiseki/game.php?game='||chr(39)||'||g.id||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||'>'||chr(39)||'||gamename||'||chr(39)||'</A><A>'||chr(39)||'||CASE WHEN g.model='||chr(39)||'PC'||chr(39)||' AND g.erogame='||chr(39)||'f'||chr(39)||' THEN '||chr(39)||'(非18禁)'||chr(39)||' WHEN g.model='||chr(39)||'PC'||chr(39)||' THEN '||chr(39)||''||chr(39)||' ELSE '||chr(39)||'('||chr(39)||'||g.model||'||chr(39)||')'||chr(39)||' END||'||chr(39)||'</A>'||chr(39)||' AS ゲーム名, '||chr(39)||'<A href='||chr(39)||'||chr(34)||'||chr(39)||'http://erogamescape.dyndns.org/~ap2/ero/toukei_kaiseki/brand.php?brand='||chr(39)||'||b.id||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||'>'||chr(39)||'||b.brandname||'||chr(39)||'</A><A>'||chr(39)||'||CASE WHEN b.kind='||chr(39)||'CIRCLE'||chr(39)||' THEN '||chr(39)||'(同人)'||chr(39)||' ELSE '||chr(39)||''||chr(39)||' END||'||chr(39)||'</A>'||chr(39)||' AS ブランド名, '||chr(39)||'<A>'||chr(39)||'||CASE WHEN point91='||chr(39)||'ボーイズラブ'||chr(39)||' THEN '||chr(39)||'BL'||chr(39)||' WHEN point91='||chr(39)||'KineticNovel'||chr(39)||' THEN '||chr(39)||'KN'||chr(39)||'WHEN point91='||chr(39)||'育成モノ'||chr(39)||' THEN '||chr(39)||'育成'||chr(39)||' ELSE substr(translate(point91,'||chr(39)||'('||chr(39)||','||chr(39)||' '||chr(39)||'),1,3) END||'||chr(39)||'</A>'||chr(39)||' AS ジャンル, '||chr(39)||'<FONT COLOR='||chr(39)||'||chr(34)||'||chr(39)||'#'||chr(39)||'||CASE WHEN current_date-g.sellday+1<=180 AND current_date-g.sellday+1>=31 THEN '||chr(39)||'0000FF'||chr(39)||' WHEN current_date-g.sellday+1<31 THEN '||chr(39)||'FF0000'||chr(39)||' WHEN current_date-g.sellday+1>1825 THEN '||chr(39)||'999999'||chr(39)||' ELSE '||chr(39)||''||chr(39)||' END||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||'>'||chr(39)||' ||to_char(g.sellday, '||chr(39)||'YYYY/MM/DD'||chr(39)||')|| '||chr(39)||'</FONT>'||chr(39)||' AS 発売日, '||chr(39)||'<FONT COLOR='||chr(39)||'||chr(34)||'||chr(39)||'#'||chr(39)||'||CASE WHEN g.median>=85 THEN '||chr(39)||'FF0000'||chr(39)||' WHEN g.median<85 AND g.median>=80 THEN '||chr(39)||'0000FF'||chr(39)||' ELSE '||chr(39)||''||chr(39)||' END||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||'>'||chr(39)||' ||g.median|| '||chr(39)||'</FONT>'||chr(39)||' AS 中央値, '||chr(39)||'<FONT COLOR='||chr(39)||'||chr(34)||'||chr(39)||'#'||chr(39)||'||CASE WHEN point1>=85 THEN '||chr(39)||'FF0000'||chr(39)||' WHEN point1<85 AND point1>=80 THEN '||chr(39)||'0000FF'||chr(39)||' ELSE '||chr(39)||''||chr(39)||' END||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||'>'||chr(39)||' ||to_char(point1,'||chr(39)||'990.99'||chr(39)||')|| '||chr(39)||'</FONT>'||chr(39)||' AS 平均点, '||chr(39)||'<FONT COLOR='||chr(39)||'||chr(34)||'||chr(39)||'#'||chr(39)||'||CASE WHEN point2>=1000 THEN '||chr(39)||'FF0000'||chr(39)||' WHEN point2<1000 AND point2>=500 THEN '||chr(39)||'0000FF'||chr(39)||' ELSE '||chr(39)||''||chr(39)||' END||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||'>'||chr(39)||' ||point2|| '||chr(39)||'</FONT>'||chr(39)||' AS データ数, round((point11*2-point1+point12*0.01)*30,0) AS お勧め度, '||chr(39)||'<A href='||chr(39)||'||chr(34)||'||chr(39)||''||chr(39)||'||g.shoukai||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||' target='||chr(39)||'||chr(34)||'||chr(39)||'_brank'||chr(39)||'||chr(34)||'||chr(39)||'><center>'||chr(39)||'||CASE WHEN g.shoukai like '||chr(39)||'http%'||chr(39)||' THEN '||chr(39)||'○'||chr(39)||' ELSE '||chr(39)||''||chr(39)||' END||'||chr(39)||'</center></A>'||chr(39)||' AS 紹介1, '||chr(39)||'<A href='||chr(39)||'||chr(34)||'||chr(39)||'http://www.getchu.com/soft.phtml?id='||chr(39)||'||g.comike||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||' target='||chr(39)||'||chr(34)||'||chr(39)||'_brank'||chr(39)||'||chr(34)||'||chr(39)||'><center>'||chr(39)||'||CASE WHEN g.comike is not null THEN '||chr(39)||'○'||chr(39)||' ELSE '||chr(39)||''||chr(39)||' END||'||chr(39)||'</center></A>'||chr(39)||' AS 紹介2, '||chr(39)||'<A href='||chr(39)||'||chr(34)||'||chr(39)||'http://em.nog.jp/detail.php?game='||chr(39)||'||g.id||'||chr(39)||''||chr(39)||'||chr(34)||'||chr(39)||' target='||chr(39)||'||chr(34)||'||chr(39)||'_brank'||chr(39)||'||chr(34)||'||chr(39)||'><center>'||chr(39)||'||CASE WHEN g.id is not null THEN '||chr(39)||'○'||chr(39)||' ELSE '||chr(39)||''||chr(39)||' END||'||chr(39)||'</center></A>'||chr(39)||' AS 紹介3 FROM (SELECT ag.game AS id91, a.title AS point91 FROM attributegroupsboolean ag, attributelist a WHERE ag.attribute=a.id AND a.furigana like '||chr(39)||'01%'||chr(39)||')tb91 RIGHT JOIN (SELECT game AS id11, AVG(tokuten) AS point11, COUNT(tokuten) AS point12 FROM (SELECT game, tokuten FROM userreview WHERE tokuten is not null AND uid IN (SELECT id22 FROM (SELECT id22, id11, abs(point11-point21) AS point31 FROM (SELECT game AS id11, round(tokuten,2) AS point11 FROM userreview WHERE uid='||chr(39)||'★１'||chr(39)||' AND tokuten is not null)tb11, (SELECT game AS id21, uid AS id22, tokuten AS point21 FROM userreview WHERE tokuten is not null)tb21 WHERE id11=id21 GROUP BY id11, id22, point31)tb31 GROUP BY id22 HAVING COUNT(id22)>='||chr(39)||'★２'||chr(39)||' AND SUM(point31)/COUNT(id22)<='||chr(39)||'★３'||chr(39)||'))tb31 GROUP BY game HAVING COUNT(tokuten)>='||chr(39)||'★４'||chr(39)||')tb41 ON id91=id11 LEFT JOIN (SELECT game AS id51, tokuten AS id52 FROM userreview WHERE uid='||chr(39)||'★１'||chr(39)||' AND tokuten is not null)tb51 ON id11=id51, (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, gamelist g, brandlist b WHERE id1=g.id AND id11=g.id AND g.brandname=b.id AND id52 is null AND point11>=75 AND point11-point1>=-2 ORDER BY お勧め度 DESC, point1 DESC LIMIT 100</TEXTAREA><BR><INPUT TYPE="submit" VALUE="実行"> <INPUT TYPE="reset"a value="初期状態に戻す"></FORM></DL><BR><TABLE>' AS main)) j