SQL_Tips003 - ErogameScape -エロゲー批評空間- の備忘録

SQL実行コスト

サブクエリをformやjavascriptに代入しておく

以下の記事は、HTML、JavaScriptについての知識が必要です。
2011/11/21 元は『5票以上』でしたが、最初のスクリプトの方が軽量になってしまいました。これは、ユーザーさんの増加に伴い、『5票以上』のユーザーさんが増加した為です。例として不適切なので、『20票以上』に改訂しました。

例えば、下記は、投票を20票以上受けているユーザーに限定した、全ての得点の平均値を算出するものです。
「SELECT uid～COUNT(voter_uid) >= 20」が、投票を20票以上受けているユーザーのリストを作成するサブクエリです。

SQL

SELECT AVG(tokuten)
  FROM userreview
 WHERE uid IN (SELECT uid 
                 FROM user_user
                GROUP BY uid
               HAVING COUNT(voter_uid) >= 20
              )
 

実行コスト

Aggregate (cost=37087.69..37087.71 rows=1 width=4)

では、ここで使われているサブクエリの結果が、予め判っていたらどうでしょうか。

SQL

SELECT AVG(tokuten)
  FROM userreview
 WHERE uid IN ('user1','user2','user3','user4')
 

実行コスト

Aggregate (cost=1129.44..1129.45 rows=1 width=4)

'user1'～'user4'というユーザーが実在するかはさておき、実行コストが大幅に低減できることが予想できます。
というわけで、最初のSQLを実行し、inputの値として保持、２回目のSQLで保持した値を使用することが出来ます。
ですので、最初のSQLは、“HTML文を作成するSQL”を記述することになります。

HTML文を作成するSQL 2010/12/11訂正

SELECT
   CHR(60)||'form'||CHR(10)
|| ' name='||CHR(34)||'SQLForm'||CHR(34)||CHR(10)
|| ' method='||CHR(34)||'post'||CHR(34)||CHR(10)
|| ' action='||CHR(34)||'http://erogamescape.dyndns.org/~ap2/ero/toukei_kaiseki/select.php'||CHR(34)||CHR(10)
|| CHR(62)||CHR(10)
|| CHR(60)||'input type='||CHR(34)||'hidden'||CHR(34)||' name='||CHR(34)||'SQL'||CHR(34)||CHR(10)
|| ' value='||CHR(34)||CHR(34)
|| ' /'||CHR(62)||CHR(10)
|| CHR(60)||'input size=100 name='||CHR(34)||'userlist'||CHR(34)||CHR(10)
|| ' value='||CHR(34)||CHR(10)
|| array_to_string(array(
       SELECT CHR(39)||uid||CHR(39) FROM user_user GROUP BY uid HAVING COUNT(voter_uid) >= 20
   ) , ',')
|| CHR(34)||CHR(10)
|| ' /'||CHR(62)||CHR(10)
|| CHR(60)||'input'||CHR(10)
|| ' type='||CHR(34)||'button'||CHR(34)||CHR(10)
|| ' value='||CHR(34)||'実行する'||CHR(34)||CHR(10)
|| ' onClick='||CHR(34)||'postSQL()'||CHR(34)||' /'||CHR(62)||CHR(10)
|| CHR(60)||'/form'||CHR(62)||CHR(10)
|| CHR(60)||'br /'||CHR(62)||CHR(10)
|| CHR(60)||'script type='||CHR(34)||'text/javascript'||CHR(34)||' language='||CHR(34)||'JavaScript'||CHR(34)||CHR(62)||CHR(10)
|| '  function postSQL()'||CHR(123)||CHR(10)
|| '  var f = this.document.forms.SQLForm;'||CHR(10)
|| '  f.SQL.value = '||CHR(34)||'AVG(tokuten) FROM userreview WHERE uid IN ('||CHR(34)||' + f.userlist.value + '||CHR(34)||')'||CHR(34)||';'||CHR(10)
|| '  f.submit();'||CHR(10)
|| '  '||CHR(125)||CHR(10)
|| CHR(60)||'/script'||CHR(62)
 

SQLをPOSTするHTML（HTML文を作成するSQLの結果） 2010/12/11訂正

<form
 name="SQLForm"
 method="post"
 action="http://erogamescape.dyndns.org/~ap2/ero/toukei_kaiseki/select.php"
>
<input type="hidden" name="SQL"
 value=""
 />
<input size=100 name="userlist"
 value="'user1','user2','user3','user4'"
/>
<input
 type="button"
 value="実行する"
 onClick="postSQL()" />
</form>
<br />
<script type="text/javascript" language="JavaScript">
  function postSQL(){
  var f = this.document.forms.SQLForm;
  f.SQL.value = "AVG(tokuten) FROM userreview WHERE uid IN (" + f.userlist.value + ")";
  f.submit();
  }
</script>
 

もしくは、javascriptの一部にSQLの結果を挿入することも。

HTML文を作成するSQL - 直接javascript内にSQLの結果を挿入 2010/12/11訂正

SELECT
   CHR(60)||'form '||CHR(10)
|| ' name='||CHR(34)||'SQLForm'||CHR(34)||CHR(10)
|| ' method='||CHR(34)||'post'||CHR(34)||CHR(10)
|| ' action='||CHR(34)||'http://erogamescape.dyndns.org/~ap2/ero/toukei_kaiseki/select.php'||CHR(34)||CHR(10)
|| CHR(62)||CHR(10)
|| CHR(60)||'input '
|| ' name='||CHR(34)||'SQL'||CHR(34)
|| ' type='||CHR(34)||'hidden'||CHR(34)
|| ' value='||CHR(34)||CHR(34)
|| ' /'||CHR(62)||CHR(10)
|| CHR(60)||'/form'||CHR(62)||CHR(10)
|| CHR(60)||'input'||CHR(10)
|| ' type='||CHR(34)||'button'||CHR(34)||CHR(10)
|| ' value='||CHR(34)||'実行する'||CHR(34)||CHR(10)
|| ' onClick='||CHR(34)||'postSQL()'||CHR(34)||' /'||CHR(62)||CHR(10)
|| CHR(60)||'script type='||CHR(34)||'text/javascript'||CHR(34)||' language='||CHR(34)||'JavaScript'||CHR(34)||CHR(62)||CHR(10)
|| '  function postSQL()'||CHR(123)||CHR(10)
|| '  var f = this.document.forms.SQLForm;'||CHR(10)
|| '  f.SQL.value = '||CHR(34)||'AVG(tokuten) FROM userreview WHERE uid IN ('
||         array_to_string(array(
               SELECT CHR(39)||uid||CHR(39) FROM user_user GROUP BY uid HAVING COUNT(voter_uid) >= 20
           ) , ',')
|| ')'||CHR(34)||';'||CHR(10)
|| '  f.submit();'||CHR(10)
|| '  '||CHR(125)||';'||CHR(10)
|| CHR(60)||'/script'||CHR(62)
 

【管理】POSTボタンを生成しました。