エロゲーマーのためのSQL

道具や文書

1. SQL実行フォーム
2. テーブル一覧
3. コラム
4. 外部リンク、書籍

目次

はじめに

SQLはデータベースからデータを抽出したりするための言語です。

この文書は、ErogameScapeのデータベースからSELECTを使って自由自在にデータを取得できるようになることを目標にします。

エロゲーをやりはじめる大学生くらいのときに、大学の講義でデータベースを学んで、退屈だなーと思った時に、ErogameScapeでSQLを学ぶことで、少しでもSQLに興味を持って、自身でデータを加工することを学習して頂けると幸いです。

※私の大学のリレーショナルデータベースの授業では、自分の身の回りの何かをER図に落とし込んで、DBを設計し、PostgreSQLに実装し、実際にデータを入力してSELECTしてみるところまでをやりました。
ER図という概念を学んだとき「ああ、これは面白い」と思いました。
先生はこう言ったのです。
「ER図に落とし込むと、思いもよらなかったことが分かる。」と。
当時、どう言われたのか忘れたのですが、ErogameScapeのDBに置き換えて言うと、Aというクリエイターがたずさわったゲームのブランドが作った別のゲームでAというクリエイターと同じ職種のBというクリエイターが作ったゲームのうちAとBが共に関わったゲームなんていうことが分かる…のようなことを言われました。
これを聞いて「いやあ、面白いなあ…」と思いました。
しかし、世の中のSQLの入門書の例は、「面白いなあ…」という事象/事物を対象にしていなくて、従業員テーブル/給料テーブル/役職テーブルがあって…とか、商品マスタと取引先マスタと顧客マスタがあって…とか、実際仕事していると出てきそうだけど、興味がないからつまらない例ばかりです。
実益が得られて、SQLを学べる何かがあるといいのになあ…と思ってはや5年…いやもっとかな…この文書を作ってみました。
楽しいエロゲーライフのためにSQLを学んで自由自在にデータを加工し「エロスケは役に立たない」ではなく「エロスケが役に立つようにSQLを作った」となる方が1人でも多く生まれることを期待いたします。

brandlistテーブルから情報を抽出する

簡単な例からSQLを実行しつつ学んでいきましょう。
以下のSQLはbrandlistテーブルからidが1の行を取り出してすべての列を表示します。

SQLを実行した結果は以下の通りです。

SQLの意味を説明します。

SELECTは、テーブルからデータを取得する場合に書きます。他に、UPDATE(データを更新する)、DELETE(データを削除する)等があります。
この文書は、SELECTしか使いません。ひたすらSELECTすることを学びます。

SELECTの後ろの*は、すべての列、を意味します。
brandlistテーブルには、id、brandname等の列がありますが、すべての列を取り出したい場合は、*を指定すると取り出せます。

※すべての列を取り出す場合でも、*を使わずに、ちゃんと列名を指定してあげた方が、SQLを見て、どんな列を取り出しているかがわかるので、いいと思います。

列を指定して取り出したい場合のSQLの書き方は後述します。

FROMの後ろには、データを取り出したいテーブルを書きます。
brandlistテーブルからデータを取得したい場合は、FROMの後ろにbrandlistと書きます。

条件を指定してデータを抽出したい場合は、WHEREの後ろに条件を書きます。
idが1のデータを抽出したい場合は、WHEREの後ろにid=1と書きます。

※WHERE id = 1は、WHERE id = '1'とも書けます。
数値の場合は、シングルクォーテーションで括らなくても大丈夫です。
しかしWHERE id = '1'と書くことをお勧めします。
数値以外の場合は、必ずシングルクォーテーションで括る必要があります。例えば、WHERE id = 'a'です。
数値であっても括ってしまった方が特にプログラムの中にSQLを埋め込んだりする場合は間違いないです。
数値だからシングルクォーテーションはなしで…とかロジックを組むことがそもそも面倒です。

※徳丸浩さんの[SQLインジェクション][SQL]: SQLの暗黙の型変換はワナがいっぱい - 徳丸浩の日記(2009-09-24)を読んで、上記文章を削除いたしました。
暗黙の型変換、怖いです。

とりあえずこれはやめましょう>『数値の場合は、シングルクォーテーションで括らなくても大丈夫です。しかしWHERE id = '1'と書くことをお勧めします』 エロゲーマーのためのSQL -エロゲーマーのためのSQL- http://t.co/wbM8dPyoan

— 徳丸　浩 (@ockeghem) 2014, 1月 3

次は取り出す列を指定してbrandlistテーブルからidが1の行を取り出します。

SQLを実行した結果は先ほどのSQLと一緒で以下の通りです。

各列の意味はテーブル一覧を参照してください。
テーブル名をクリックすると、デーブルの内容が表示されます。

列の名前には別名をつけることができます。

別名は、取り出したい列名の後ろに半角スペースをつけて別につけたい名前を書きます。
brandname ブランド名
な感じです。

lostの列だけ、lost "解散?"と解散?をダブルクォーテーションで囲っているのは、囲わないとsyntax errorを返すからです。

列の名前に別名をつけるときは別名の前にASと書くことが多いと思います。

ASをつけた方が見やすい気がするのですが、PostgreSQL 8.4 の新機能によると、

SELECT 列 [AS] 列別名 FROM 表 [AS] 表別名」のように、列別名を指定する際の「AS」が省略できるようになりました（表別名のASは、以前のバージョンでも省略できます）。
他DBMSでは AS があるとエラーになるものがあるため、常に省略するようにしておけばSQLを移植する際に混乱がなくなります。

テープル名にも別名をつけることができます。

テーブル名に別名をつけたい場合は、テープル名の後ろに半角スペースをつけて別につけたい名前を書きます。
とても短い名前をつけるのが一般的だと思います。
FROM brandlist b
な感じです。

今、学んでいるSQLは、FROM句にあるテーブルが1つなので、列を取り出す際にテーブル名を指定する必要は無い(それは取り出すテーブルが一意だからです)のですが、FROM句に複数のテーブルが指定されている中から、列を取り出す場合が殆どです。
テーブルを指定して列を取り出す場合は、取り出す列の前にテーブル名.をつけます。

上記のようにSQL書くととても見づらいです。
そこで、brandlist bのようにテーブルに別名をつけて

のようにします。

brandlistテーブルとgamelistテーブルを結合して情報を抽出する

この章では結合を学びます。

※結合の条件を間違えるとサーバーにすごい負荷がかかりますので気をつけましょう。

前の章では1つのテーブル(brandlistテーブル)からデータを取り出してみます。。
データベースの神髄は複数のテーブルを結合していろんなデータを抜き出して分析できることです。
brandlistテーブルとgamelistテーブルを結合してデータを取り出してみます。

※gamelistテーブルの内容はテーブル一覧を参照してください。

SQLを実行すると、brandlistのidが1、ageが作ったゲームの一覧が表示されます。

ポイントは以下の部分です。

  FROM brandlist b
 INNER JOIN gamelist g
         ON b.id = g.brandname

ポイントの説明の前に、どのようにテーブルとテーブルを結合するのか?を説明します。

gamelistテーブルは、あるゲームがどのブランドと対応するかを示す列を持っています。
gamelistテーブルのbrandnameの列がどのブランドと対応するかを示す列です。
gamelistテーブルのbrandnameと、brandlistテーブルのidは対応しています。
この対応を使ってgamelistテーブルと、brandlistテーブルを結合します。

具体的な例を書きます。
大帝国のbrandnameは30、君が望む永遠のbrandnameは1です。

brandlistのidが30のブランドはALICE SOFT、brandlistのidが1のブランドはageです。

以上から大帝国のブランドはALICE SOFT、君が望む永遠のブランドはageと分かります。

図で書くと以下のような感じです。

以上の作業をSQLで書くと、最初に書いたポイント

  FROM brandlist b
 INNER JOIN gamelist g
         ON b.id = g.brandname

のようになります。

最初の2行は、brandlistテーブルと、gamelistテーブルを結合する、という意味です。

※INNER JOINの他にも結合の仕方がありますが、他の結合については後述します。

次の行は、brandlistテーブルのidと、gamelistテーブルのbrandnameが同じものを結合する、という意味です。

章のはじめのSQLに戻りましょう。

このSQLは、brandlistとgamelistをbrandlistのidとgamelistのbrandnameが等しいという条件で結合して、brandlistのidが1のものを表示します。
brandlistのidが1なのは、ageですので、このSQLはageが作ったゲームを表示します。

gamelistテーブルのbrandnameのように他のテーブルの列と同じ値が入る列(他のテーブルの列の値を参照する列)のことを外部キーといいます。
どの列が外部キーで、どのテーブルを参照しているかは、テーブル一覧を参照してください。

※外部キーには外部キーらしい列名をつけているつもりです…

条件を指定して情報を抽出する(1) WHERE句

この章では条件を指定して情報を抽出する方法を学びます。抽出する条件は、WHERE句に書くことが多いです。

SQLを実行した結果は以下の通りです。

WHERE b.id = 1と書くと、brandlistテーブルのid列の値が1のものを抽出して表示します。

=は比較演算子の1つです。
比較演算子には以下のようなものがあります(全部じゃないです。)。

=

等しい

<>または!=

等しくない(標準は<>なのですが、!=でも動きます)

<=

右辺より左辺が小さいか等しい

<

右辺より小さい

>=

右辺より左辺が大きいか等しい

>

右辺より左辺が大きい

例えばbrandlistテーブルからidが1ではない行を取り出して(そんな取り出し方をすることはないですが…)すべての列を表示します。

SQLを実行した結果は以下の通りです。

使いどころは、得点が何点以上のものを抜き出すとか、データ数がいくつ以上のものを抜き出すとか、です。

複数の条件にあったものを抽出する場合は、ANDで条件をつなげます。

例えば企業でtwitterのIDを持っているブランドをbrandlistから抽出するSQLは以下のようになります。

SQLを実行した結果は以下の通りです。

WHERE b.kind = 'CORPORATION'
  AND b.twitter IS NOT NULL

b.kind = 'CORPORATION'で「kind(種別)」が「企業」であるという条件を指定しています。
続けてANDの後ろにb.twitter IS NOT NULLと書いて、twitterの列がNULLでないこと…つまりtwiterの列に何か登録されているという条件を指定しています。

NULLについて

NULLという用語がでてきました。
NULLはヌルまたはナルと読みます。
NULLとは、特殊な値で「データが存在しない」ことを意味します。
ある列の値がNULLである…、例えば、twitterの列がNULLである、という条件を指定したい場合は「twitter = NULL」ではなく「twitter IS NULL」と書く必要があります。
逆にtwiterの列がNULLでない、という条件を指定したい場合は「twitter <> NULL」ではなく「twitter IS NOT NULL」と書く必要があります。

※NULLという値と何かを比較した結果はNULLを返します。
WHERE句にtwitter = NULLと書いた場合、たとえ、twitterの値がNULLであったとしても、twitter = NULLの結果は必ずNULLとなります。
NULLとNULLは=にはならず、NULLになります。
SQLはWHERE句の条件にマッチした行(WHERE句の条件が真である行)を返します。
WHERE句にtwitter = NULLというような条件を書くと、この条件は必ずNULLとなり、真にはならないので、SQLは何も行を返しません。

「twitter IS NOT NULL」は、「twitter <> ''」ではいけないのか？と思うかもしれません。
結論から書くと、「twitter <> ''」でもOKです。

SQLを実行した結果は以下の通りです。

brandlistのtwitterのデフォルトの値はNULLです。
ブランドがtwitterのIDを持っている場合は、twitterの列にIDが格納されています。
twitterの値がNULLの場合は比較式が「NULL <> ''」となり、この式の値は「NULL」となるので、twitterの値がNULLの行は返しません。
ブランドがtwitterのIDを持っている場合はtwitterの値に何らかの英数字が入っているので、「何らかの英数字 <> ''」となり、この式の値は「真」となるので、行を返します。
結果、最初の目的である「twitterのIDを持っているブランドをbrandlistから抽出する」ことを達成できます。

しかし逆はどうでしょう。
企業でtwitterのIDを持っていないブランドをbrandlistから抽出するSQLは以下の通りでよいのでしょうか？

SQLを実行した結果は以下の通りです。

結果に何も表示されませんね。
b.twitter = ''は、「twitterの列が0文字の文字であること」という意味です。(0文字の文字…というのは想像しづらいですが…)
twitterの列の値がNULLだった場合、b.twitter = ''は「NULL = ''」という比較式になります。
NULLと''を比較すると…、NULLは何と比較してもNULLを返すのですから、NULLとなります。
SQLはWHERE句の値が真である行を返すので、結果、上のSQLは何も返しません。

長くなりましたが、ここで覚えなければいけないのは、twitterの列がNULLである、という条件を指定したい場合は「twitter IS NULL」と書く、twitterの列がNULLではない、という条件を指定したい場合は「twitter IS NOT NULL」と書く、ということです。

※NULLなんて面倒なものを使わずに、0文字の文字をデフォルトにすれば、「IS」なんて使わずに、「twitter = ''」「twitter <> ''」とできるし、その方が分かりやすいのでは？と思う方がいると思います。
twitterの列はtext型なので、デフォルトの値を0文字の文字にすることもできますが、得点のように数字しか入らないことがわかっている場合に設定するinteger型の場合は、デフォルトの値を0文字の文字にすることができません。
また後述しますが、外部結合という結合をした場合、NULL値が現れます。
したがってNULL値の扱い方を覚えておく必要があります。

※可能であればNULLはない方がよいです。RDBをある程度学習した方はNULLに関する理解を深めるために、ぜひNULL撲滅委員会を読んでください。
またWEB+DB PRESS Vol.69にも理論で学ぶSQL再入門「【第2回】テーブル設計におけるNULLの取り扱い方」という文書があります。

NULLを置換する

COALESCE(NULLを含む列名, 0)

並び替える ORDER BY句

ここまで触れてきませんでしたが、SQLの中にORDER BYという句があります。
前の章ででてきたSQLを再掲します。

SQLを実行した結果は以下の通りです。

ORDER BYは、並び替えをする場合に使います。
ORDER BYの後ろに並び替えたい列を指定することによって、デフォルトでは昇順で並び替えます。
上のSQLには、ORDER BYの後ろにb.idとありますので、ブランドのIDの昇順で並び替えて表示します。

※ORDER BYを指定しなかった場合、どのような順番で表示されるのでしょうか。
その答えは「よくわからない」です。
postgresqlの場合、最初はデータを挿入した順番で表示される気がしますが、データの更新をしたり、行を削除したりすると、順番がかわるような気がします。
データを取り出す場合は、ORDER BYを指定してデータを取り出しましょう。(一行だけ取り出したい場合や、取り出したデータをスクリプトで処理して、スクリプト側で並び替えたりする場合は、その限りではないです。)

条件を指定して情報を抽出する(2) パターンマッチ(LIKE、正規表現、SIMILAR TO)

次にブランド名フリガナが「ア」ではじまるブランドを抽出してみます。

SQLを実行した結果は以下の通りです。

LIKE述語を使うと「ア」ではじまる、とか「ア」で終わる、等の検索ができます。
LIKE述語の後ろの'ア%'の%は、「任意の文字列」という意味です。

b.brandfurigana LIKE 'ア%'

は、b.brandfuriganaの列において、最初に「ア」がきて次に「%」なので任意の文字列がくるもの…、つまつ「ア」ではじまるものを抽出します。

ゲーム名に「妹」という文字が入っているゲームを探したかったら、以下のようになります。

SQLを実行した結果は以下の通りです。

g.gamename LIKE '%妹%'

はg.gamenameにおいて、任意の文字列があって、妹があって、任意の文字列がくるものを抽出します。

%は任意の文字列ですが、0文字も含みます。
上のSQLの結果に「愛姉妹」があります。
「妹」で終わっていますが、「%妹%」で抽出されています。
2つ目の%にあてはまる文字はありませんが、%は0文字も含むので、「愛姉妹」も抽出されます。

サーバーに負荷をかけて欲しくないのでSQLの実行列を示しませんが、

SELECT id FROM gamelist WHERE g.gamename LIKE '%'

と書くと、すべてのゲームが抽出されてしまいます。
このSQLを書くのは、本当は「%」を含むゲーム名抽出したいというと思っている…場合だと思います。
「%」を含むゲームを抽出したい場合は以下のようにSQLを書きます。

SQLを実行した結果は以下の通りです。

ESCAPE '?'は、「?」に続く文字を特殊な文字としてでなはなく普通の文字として扱います、という意味です。
LIKE述語の後ろにESCAPEを書いておくことで、「%?%%」は、任意の文字があって、%があって、任意の文字列がくる、ものが抽出されます。

※SQLと関係ない話で恐縮ですが、あすか120%は面白かったなあ…

LIKE述語には「%」の他に任意の1文字を示す「_」が使えます。
例えば、ゲーム名が3文字のゲームを抽出するには以下のようなSQLになります。

SQLを実行した結果は以下の通りです。

※ああ、意外にあるんだなあという感想です。

逆に○○を含まないというゲームを抽出したい場合は、LIKEの前にNOTをつけます。
例えば、ゲーム名に「妹」を含むけど「姉」は含まないゲームを抽出するSQLは以下のようになります。

SQLを実行した結果は以下の通りです。

ちなみに、姉も妹も含むゲームを抽出するSQLは以下の通りです。くどいですか…、そうですか…

SQLを実行した結果は以下の通りです。

ところで正規表現という言葉をご存じでしょうか。
もし正規表現を知らないのであれば、これを機会に正規表現を学ぶことをオススメします。
webアプリケーションを作るのに正規表現は必須です。

※正規表現は、まずはWebにいろいろ情報があるので、それをあたりつつ、正規表現を試すサイトがあるので、そこでいろいろ試してみるのがいいと思います。
ある程度なれたら、詳説 正規表現 第3版を読みましょう。
この本は鉄板です。

話を戻しまして、SQLの中でも正規表現を使ってデータを抽出することができます。
が、DBによって文法が違います。
ErogameScapはPostgreSQLを使っているのでPostgreSQLの文法です。

※ちなみに、標準SQLで正規表現を使った抽出をする場合は、SIMILAR TO演算子を使います。使うみたいですね…この文書を書くまで知りませんでした…
SIMILAR TO演算子を使っておけば、移植が楽ちんにできるんだと思います。
たいした正規表現使ってないから、SIMILAR TOで書いておけばよかったかなあ…

正規表現を使って、「萌」ではじまる4文字のクリエイターを抽出してみましょう。(そんな条件で抽出することはないでしょうけど…)

SQLを実行した結果は以下の通りです。

c.name ~ '^萌.{3}$'

PostgreSQLで正規表現を使う場合は、~を使います。
これ、読み方は「にょろ」で通じるので、「にょろ」と言っちゃうのですが、チルダですね。
上記SQLはnameにおいて、「萌」ではじまって任意の文字が3文字続くものを抽出します。
^萌.{3}$の部分が正規表現で、^は行頭を意味し、$は行末を意味し、.は任意の文字列を意味し、{3}は直前の文字が3つ…を意味します。
正規表現の詳しいところは、他のドキュメントを参照してください。
繰り返しますが、正規表現は強力です。

条件を指定して情報を抽出する(3) IN述語と副問い合わせ その1

ユーザーIDがap2またはhiroinの行をmyuserviewから抽出する…のように複数の値を指定して行を抽出する場合はIN述語を使います。

SQLを実行した結果は以下の通りです。

実際の使い道は上の例のようにIN述語に直接値をセットするのではなく、他のテーブルから抽出した結果をセットすることが多いと思います。
例えば、KISS×200 とある分校の話(idは2010)に
　・100点
　・POV「おかずに使える(idは1)」に「A」
をつけているユーザーさんを抽出するSQLは例えば以下のようになります。

SQLを実行した結果は以下の通りです。

まず以下のSQLの部分で、userreviewテーブルからKISS×200 とある分校の話(idは2010)に100点をつけたユーザーさんのIDを抽出します。

SELECT u.uid
 FROM userreview u
WHERE u.game = 2010
  AND u.tokuten = 100

抽出した結果は以下の通りです。

抽出した結果、上のSQLは下記のようになります。

SELECT p.uid
  FROM povgroups p
 WHERE p.game = 2010 --KISS×200 とある分校の話(idは2010)
   AND p.pov = 1     --POV「おかずに使える(idは1)」
   AND p.rank = 'A'    --rankはA
   AND p.uid IN ( 'robby','POCKY','POCKY',  ～中略～   ,'禁愚芸那' )  --userreviewテーブルから抽出されたユーザーさんのID
 ORDER BY p.uid

このSQLはpovgroupsテーブルから、IN述語に指定されたユーザーさんでKISS×200 とある分校の話(idは2010)にPOV「おかずに使える(idは1)」に「A」をつけたユーザーさんを抽出するSQLです。
このSQLを実行すると結果が

となります。

ちなみにSQLはいろんな書き方ができて、上のSQLは以下のようにも書けます。

SQLを実行した結果は以下の通りです。当然ながら一つ前のSQLと一緒の結果が返ってきます。

また以下のようにも書けます。

SQLを実行した結果は以下の通りです。

どんなSQLがいいか?というと、一番は実行時間が短いことだと思っています。

※その昔、postgresqlはINを使うと遅いので、EXISTSを使うという…多分、悪いノウハウがありました。
上の例のようにSQLはいろんな書き方ができて、INを使ったSQLは、EXISTSを使ったSQLに書き直すことができます。
ただ、何をしてるのか分かりにくいのがEXISTSかなと思っています。
PostgreSQLはINも速くなったので、INを使った方がいいと思います。

※一番上のSQLは、ORDER BYしているので、QUERY PLANにSortが出てこないといけないと思うのですが、出ていないのはバグかな…
ちゃんとソートされているので結果は問題ないのですが…

条件を指定して情報を抽出する(4) BETWEEN述語

ある列から、○○から××までのデータを抽出したいということはよくあることです。
ErogameScapeの得点は100点満点ですので、例えば80点から100点のデータを抽出したいとか、2000/01/01から2000/12/31に発売されたゲームを知りたいとか、よくあります。
そんな範囲を指定してデータを抽出するのがBETWEEN述語です。
BETWEEN述語はANDとセットで以下のように使います。

WHERE tokuten BETWEEN 80 AND 100

上記のSQLはtokutenが80から100のデータを抽出するという意味です。
2000/01/01から2000/12/31のデータを抽出する場合は以下のようになります。

WHERE sellday BETWEEN '2000/01/01' AND '2000/12/31'

それでは、実際にデータを抽出してみます。
userreviewテーブルからKISS×200 とある分校の話(idは2010)に80点から100点をつけたユーザーさんのIDを抽出します。

SQLを実行した結果は以下の通りです。

※ちなみにBETWEEN述語は内部で<=と>=に置き換えられます。
QUERY PLANを見てみてください。
BETWEEN '80' AND '100'は(tokuten >= 80) AND (tokuten <= 100)と置き換えられています。。
ですので、BETWEEN '100' AND '80'と書いてしまうと、(tokuten >= 100) AND (tokuten <= 80)となってしまいますので、返ってくるデータが0になってしまいます。

重複したデータをまとめて1行にする DISTINCT

重複した行がある場合、重複した行は1行だけ取り出したい場合は、DISTINCTを使用します。
ErogameScapeのgamlelistテーブルには、modelという列があります。
modelには、そのゲームの機種が格納されています。
どんな機種があるかを知りたい場合、以下のようなSQLを実行します。

SQLを実行した結果は以下の通りです。

上の例は列が1つでしたが、列が複数の場合もDISTINCTを使って重複する行を1つだけ表示することができます。
例えば、いとうのいぢさんが関わったゲームのゲーム名とゲームのIDを表示するSQLは以下のようになります。
いとうのいぢさんのIDは1345です。

SQLを実行した結果は以下の通りです。

DISTINCTなしで実行すると…

な結果になってしまって、望む結果が得られません。
なぜ、そうなってしまうのか?というと、以下のようなSQLを実行すれば納得できるかなと思います。

SQLを実行した結果は以下の通りです。

※SQLの中に見慣れないCASE式がありますが、これは後述します。
s.shubetuが1のときは原画、4のときはシナリオ、7のときはその他、それ以外のときにはそのまま表示するという意味です。

DISTINCT句を使うと、取り出した列で重複を削除してくれます。
もともとの主旨は、いとうのいぢさんが関わったゲームを取り出すことで、原画やシナリオ等の種別の情報は必要ないので、DISTINCT句を使って重複を削除する必要があります。

あるテーブルから同じ列を複数回抽出できる

例えば、ふとタイトル含まれる姉妹という言葉を母娘にしたらどうなるんだろうと思うことがあったりするかもしれません。
SQLはあるテーブルから同じ列を複数回抽出することができます。

SQLを実行した結果は以下の通りです。

regexp_replace( g.gamename , '姉妹' , '母娘' )

は、g.gamenameに含まれる姉妹を母娘に置換するという意味です。
上のSQLは、gamelistからgamenameを2回抽出しています。
こうすることで、置換前と置換後のゲーム名を見て比較することができます。
ここで説明したかったのは、1回だけしか列を抽出できないというルールはない…ということです。
通常、SQLで取り出して、PHPやRuby等のスクリプトで加工するのがメインだと思いますが、こういったテクニックを使ってユーザー作成SQL一覧には、職人技なSQLが多数あります。

条件を指定して情報を抽出する(5) AND、OR、NOT

上の方で少し説明しましたが、ここで複数の条件を指定する方法についてちゃんと説明します。
Aという条件とBという条件の両方を満たす行を抽出したい場合はANDを使います。
例えば、2012年に発売されたゲームで、中央値が80以上で、つけられた得点の数が10以上で、標準偏差が10以下のゲームを知りたい場合は以下のようなSQLとなります。

※toukei_temp_tableは1日に1回、各ゲームの統計情報を計算して格納するテーブルです。

SQLを実行した結果は以下の通りです。

※PostgreSQLは--をつけるとそれ以降の文字はコメントとして無視します。

Aという条件とBという条件のどちらかを満たす行を抽出したい場合はORを使います。
例えば、2012年に発売されたゲームで、中央値が80以上または平均値が80以上で、つけられた得点の数が10以上で、標準偏差が10以下のゲームを知りたい場合は以下のようなSQLとなります。

SQLを実行した結果は以下の通りです。

ANDとORが混在している場合、ANDの方が先に適用されます。
四則演算(+-*/)ではかけ算と割り算を先に計算して、そのあとに足し算と引き算をします。
例えば、1+2*3-4=1+6-4=3です。
もし、1+2を先に計算したい場合は、()をつけて、(1+2)*3-4のようになります。 同様にANDとORではANDが先に適用されるので、例えば、上のSQLを括弧なしで記述してしまうと

となりますが、SQLを実行した結果は

となり、当初の目標であった、例えば「つけられた得点の数が10以上で」を満たしていないデータが抽出されてしまいます。
SQLのWHERE句を見直してみましょう。

WHERE t.sellday BETWEEN '2012-01-01' AND '2012-12-31'
  AND t.median >= 80 OR t.average >= 80
  AND t.count  >= 10
  AND t.stddev <= 10

ANDとORでは、ANDの方が先に適用されて、その後にORが適用されるので、上記のWHERE句は「2012年に発売されて、中央値が80以上のゲーム」または「平均値が80以上で、つけられた得点の数が10以上で、標準偏差が10以下のゲーム」を抽出するという意味になります。
四則演算と同様、ORを先に適用したい場合は()をつけます。

WHERE t.sellday BETWEEN '2012-01-01' AND '2012-12-31'
  AND ( t.median  >= 80 OR t.average >= 80 )
  AND t.count  >= 10
  AND t.stddev <= 10

と、先に適用して欲しい部分に()をつけることで、2012年に発売されたゲームで、中央値が80以上または平均値が80以上で、つけられた得点の数が10以上で、標準偏差が10以下のゲームが抽出されるようになります。

○○ではないという条件でデータを抽出したい場合はNOTを使います。
たとえばリトルバスターズ!(gamelistのidが7062)に20点以下をつけたユーザー以外のユーザーがクドわふたー(13525)につけた得点の平均値を求めるSQLは例えば以下のようになります。

SQLを実行した結果は以下の通りです。

AVGは集約関数と呼ぶものです。
説明は後述しますが、AVGは与えられた値の平均値を返します。

 WHERE u.uid NOT IN ( SELECT uid
                        FROM userreview
                       WHERE game = 7062
                         AND tokuten <= 20 )

赤字の部分が「リトルバスターズ!(gamelistのidが7062)に20点以下をつけたユーザー」を抽出するSQLです。
赤字の部分のSQLを実行した結果を展開すると…

 WHERE u.uid NOT IN ('nigoriyu','batiyxa22','flat090', ・・・ , 'yotsuba0707')

となります。
IN述語は、複数の値を指定して、その値を含む行を抽出するのに使うのでした。
今回はINの前にNOTがついています。
NOTがつくと、否定の意味になりますので、NOT INは○○を含まないという意味になります。
上のWHERE句の意味は、u.uidに'nigoriyu','batiyxa22','flat090', ・・・ , 'yotsuba0707'を含まないとなります。

ちなみに、SQLってこんなこともできるんだよ的な感じで…、TRUEの否定はFALSEですが、これをSQLで書くと以下のようになります。

SQLを実行した結果は以下の通りです。

ちゃんとf、つまりFALSEが返ってきます。
TRUE、FALSE、NULLをAND、OR、NOTで演算した場合にどのような結果になるかは一覧は3値論理における論理演算の結果を参照してください。

算術演算と型キャスト(1)

SQLは算術演算子を使った基本的な演算や、算術関数を使った演算ができます。
どのような演算が出来るかは算術関数と演算子(PostgreSQL 9.1.0文書)を参照してください。
ここでは算術演算子、算術関数を使ったSQLの例をいくつかご紹介します。

面白くもなんともない例ですが、1+1をSQLで計算するには以下のようになります。

SQLを実行した結果は以下の通りです。

では幾分実用的な使い方を…

SQLを実行した結果は以下の通りです。

上記SQLはユーザーID「ap2」が各ゲームにつけた得点と中央値の差および偏差値を表示するものです。

     , u.tokuten - t.median AS "得点-中央値"

u.tokutenはユーザーID「ap2」の得点、t.medianはtoukei_temp_tableから抽出したゲームの中央値です。
u.tokuten - t.medianは、ユーザーID「ap2」の得点とtoukei_temp_tableから抽出したゲームの中央値の差を表示します。

     , round( ( 10 * (u.tokuten-t.average) / t.stddev + 50)::numeric , 1 ) AS 偏差値

偏差値は以下のように算出します。
偏差値 = 10 × ( 得点 - 平均値 ) / 標準偏差 + 50
SQLでは「10 * (u.tokuten-t.average) / t.stddev + 50」となっている部分です。
そのまま表示すると、小数部がとても長くなってしまうので、、roundという関数を使って、小数第二位で四捨五入して、小数第一位まで表示することにします。
postgresqlのマニュアルのround関数の部分を見ると「round(v numeric, s int)」と書いてあります。
第一引数は「numeric」型を、第二引数は「int」型を渡してあげる必要があります。

     , round( ( 10 * (u.tokuten-t.average) / t.stddev + 50) , 1 ) AS 偏差値

と書けばいいかな?と思うかもしれませんが、駄目です。
第一引数は「numeric」型でないといけないですが、( 10 * (u.tokuten-t.average) / t.stddev + 50)の計算結果の型は「double precision」となるからです(なぜそうなるのかが私には分かりません。)
計算結果の型を知るうまい方法がないような気がしていまして、実際は「やってみてエラーが出たらcastする(型変換する)」ことになるのかなと思います。

※数値計算以外の場合はどんな型になるのかは雰囲気で分かると思います…

実際に型をcastしないで実行してみましょう。

SQLを実行した結果は以下の通りです。

ERROR: function round(double precision, integer) does not exist LINE 8: , round( ( 10 * (u.tokuten-t.average) / t.stddev + 50) ... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.

function round(double precision, integer) does not existと怒られます。
この結果を見て、roundの第一引数にあたる「10 * (u.tokuten-t.average) / t.stddev + 50」の計算結果の型は「double precision」だと分かります。

※型を表示する関数があってもよさそうな気もしたのですがありませんでした。多分…

postgresqlの場合、型をcast(変換)する方法は2種類あります。
マニュアルに載っている通りで

• CAST ( expression AS type )
• expression::type

の2つがあります。
前者はSQLに準拠、後者はPostgreSQL独自の文法です。
私の書いたSQLは後者を使って、「10 * (u.tokuten-t.average) / t.stddev + 50」をnumeric型にキャストしています。

型キャスト(2)

ここでは型キャストをもう少し詳しく説明します。
DBのデータは「型」を持っています。

※どんな型があるかはマニュアルを参照してください。

例えばuserreviewテーブルの各列は下記テーブルのように型を設定しています。

SQLはDBからデータを取り出す他に取り出したデータを加工する関数がいろいろあります。

※どんな関数があるかはマニュアルを参照してください。

関数を使ってデータを加工する際に、関数に渡す値の型を気にする必要があります。
関数じゃないですが…、例えば、足し算をするときには「+」を使いますが、

と、INTEGER型のidと、TEXT型のgamenameを足し算しようとすると怒られます。

ERROR: operator does not exist: integer + text LINE 1: EXPLAIN SELECT id + gamename ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

上記の例は間違っていることに気がつきやすいのでいいと思うのですが、「あれ?そうなの?」というパターンもありまして(私がそう思うだけかもしれないのですが…)、それが「算術演算と型キャスト(1回目)」で書いたような例です。
型が違う場合は「You might need to add explicit type casts.」に言われるので、その場合は型をキャストしてあげましょう。

PostgreSQLにおける日時の扱い方

ここではPostgreSQLにおける日時の扱い方について説明します。
公式ドキュメントはこちらです。
ここに記載する内容は多分選択するDB(PostgreSQL,MySQL,Oracle等)ごとに違います。
多くの方はMySQLをお使いだと思いますので…MySQL 5.1 リファレンスマニュアルの11.5. 日付時刻関数を参照してください。

※古い話ですが、その昔、日時については、DBからデータを取り出してからスクリプトで加工するよりは、データを取り出す前にDBの関数で加工してから取り出した方が楽ちんでした。
PHPにDateTimeクラスが出来てからは、日付計算が楽になりましたが、DateTimeクラスがないVerではPHPで日付計算をするのは面倒だったので、DBに計算させて返すということをしていました。

それでは、まず基本的な使い方からです。
現在の日時を得るには以下のSQLを実行します。

SQLを実行した結果は以下の通りです。※当たり前ですが、実行した時刻によって結果は異なります。

今から1週間前の日時を得るには以下のSQLを実行します。

SQLを実行した結果は以下の通りです。※当たり前ですが、実行した時刻によって結果は異なります。

上記SQLはこんな書き方もできます。

一週間後は「-」演算子を「+」に書き換えます。

SQLを実行した結果は以下の通りです。※当たり前ですが、実行した時刻によって結果は異なります。

日時の差分をとることもできます。
以下のSQLは2000年1月1日から今までの日数時間を表示します。

SQLを実行した結果は以下の通りです。※当たり前ですが、実行した時刻によって結果は異なります。

さて、実例に入ります。
今日から一ヶ月後までに発売されるゲームの一覧を表示したい場合は、以下のようなSQLになります。

SQLを実行した結果は以下の通りです。

ゲームによっては、発売された当初は得点が高く、だんだんと低くなっていくことがあります。
発売から2週間以内につけられた得点と、2週間以降につけられた得点の差を知りたいなあと思ったときは以下のようなSQLになるかと思います。

SQLを実行した結果は以下の通りです。

groupaは発売から2週間以内につけられた得点の平均、groupbは発売から2週間目以降につけられた得点の平均です。
16641は古色迷宮輪舞曲 ～HISTOIRE DE DESTIN～です。
まだ説明していないSQLがでてきているので説明を補足します。
「SELECT sellday + interval '2weeks' FROM gamelist WHERE id = 16641」は、古色迷宮輪舞の発売日(2012-07-27)に2週間を足した値が返します。
実行結果は「2012-08-10」です。
上記SQLは以下のようになります。

「CASE WHEN ( play_tourokubi <= '2012-08-10' ) THEN tokuten ELSE NULL END」は、userreviewテーブルから1行取り出してplay_tourokubi(ユーザーさんが得点を入力した日付)が2012-08-10以前だったらtokutenを、そうでなかったらNULLを返します。
例えば、2012-08-01に80点をつけたユーザーさんのデータの場合は「80」、2012-08-11に70点をつけたユーザーさんのデータの場合は「NULL」となります。
CASE式を使うことで取得したデータをふるいにかけることができます。

※CASE式については、後ほど詳細に説明します。

AVGは集約関数の1つで()の中の値の平均値を返します。
「AVG( CASE WHEN ( play_tourokubi <= '2012-08-10' ) THEN tokuten ELSE NULL END )」の中の「CASE WHEN ( play_tourokubi <= '2012-08-10' ) THEN tokuten ELSE NULL END」はuserreviewテーブルから1行取り出してplay_tourokubi(ユーザーさんが得点を入力した日付)が2012-08-10以前だったらtokutenを、そうでなかったらNULLを返すのでした。
1行目が80、2行目が70、3行目がNULLだった場合、AVG( 80, 70, NULL)になるので、( 80 + 70 ) / 2 で75が返ります。

※AVGはNULLを除いて平均値を出します。
集約関数についても、後ほど詳細に説明します。

集約関数 AVG、COUNT、MAX、MIN、SUM、STDDEV

あるゲームにつけられた得点の平均値はいくつだろうか、またつけられた得点の数はいくつだろうか、といったことを調べる際に使うのが集約関数です(集合関数ともいいます)。
ErogameScapeで使いそうな集約関数には以下の関数があります。

AVG

全ての入力値の平均値

COUNT

入力行の数

MAX

最大値

MIN

最小値

SUM

和

STDDEV

標本標準偏差

以下に実際の使用例を示します。
ユーザー「ap2」がuserreviewテーブルに入力したデータの総数は以下のようなSQLで数えることができます。

SQLを実行した結果は以下の通りです。

ユーザー「ap2」がuserreviewテーブルに入力したデータで得点が入力されているものの総数を数えたい場合は、COUNT(tokuten)とします。
COUNTの括弧の中に列名をいれると、その列のNULL値を除いた数を表示します。

SQLを実行した結果は以下の通りです。

ユーザー「ap2」がつけた得点の平均値を知りたい場合は、AVGを使います。

SQLを実行した結果は以下の通りです。

AVGの返り値の型はnumericなので何もしないと小数点以下の桁数が多くなってしまいます。
通常は適切に丸めて表示することになると思います。
例えば小数点第二位を四捨五入して第一位までにしたい場合は、round関数を使います。

SQLを実行した結果は以下の通りです。

round( AVG(tokuten) , 1 )は、AVG(tokuten)の結果を小数第二位で四捨五入して、小数点第一位にして表示するという意味です。

各ブランドがいくつゲームを作った(作っている)のか?を調べるのにも集約関数を使います。
以下のSQLは各ブランドが作ったゲーム数を降順に表示するSQLです。

SQLを実行した結果は以下の通りです。

GROUP BY句は、ある列でグループ化して集約関数を適用したい場合に必須の句です。
brandnameごとにgamelistのゲームを数えたいので、brandnameをGROUP BY句の中に書きます。
言葉で説明するのがきついので、以下に集約関数とGROUP BYがどのように動くのかを図で示します。

上記のSQLは、まず

  FROM brandlist b
 INNER JOIN gamelist g
         ON g.brandname = b.id

の部分が動いて以下のような表が生成されます。

※全部の列を書くと冗長なので一部の列だけを書きます。

次に

SELECT b.brandname
     , COUNT(*)
 GROUP BY b.brandname

の部分が動きます。
brandnameごとに、行数を数えていきます。
#defineは7、(無)パワフルテクニック。は3、(猫)milkcatは4…以下略と、すべてのbrandnameについて数えていきます。
その結果、以下のような表になります。

最後に

 ORDER BY COUNT(*) DESC
 LIMIT 10

が適用されて、行数の降順に10個だけ結果を表示します。

brandnameの他にkind(企業が同人か)を表示したい場合は、SELECT句にkindを加えるとともに、GROUP BY句にもkindを加える必要があります。

SQLを実行した結果は以下の通りです。

ちなみにGROUP BY句にkindを入れ忘れた場合、実行エラーとなります。
PostgreSQLの場合、エラーの中で優しく「kindをGROUP BYに入れないといけません」と教えてくれるので、なぜエラーになったのか迷うことはないでしょう。
上のSQLからGROUP BY句のb.kindを消して実行してみてください。
以下のエラーがでます。

ERROR: column "b.kind" must appear in the GROUP BY clause or be used in an aggregate function LINE 2: , b.kind ^

GROUP BY句には集約関数以外の列を全部書く、と覚えておけば間違いないです。

※9.0以前のPostgreSQLは上で説明したとおり、集約関数以外の列をGROUP BY句に書く必要がありましたが、9.1以降のPostgreSQLではGROUP BY句に主キーを指定すれば良いように変更されています。
brandlistテーブルの主キーはidです。
上のSQLは以下のように書けます。

集約関数を使って平均値や標準偏差を出した後に、その平均値や標準偏差をある値で絞り込んだ結果を表示したい場合に使うのがHAVING句です。
例えば2013年に発売されたゲームについて、5つ以上得点がつけられて、その得点の標準偏差が25以上のゲームを、標準偏差の降順で表示したい場合は以下のSQLになります。

SQLを実行した結果は以下の通りです。

「5つ以上得点がつけられて、その得点の標準偏差が25以上」をSQLで書いたのが以下の部分です。

HAVING COUNT(tokuten) >= 5
   AND STDDEV(tokuten) >= 25

substring ( gamename from 1 for 30 )は、gamenameの1文字目から30文字分だけを表示するというものです。
他にも文字列を加工するための関数があります。
公式ドキュメントを参照して下さい。

※WHERE句にu.play_tourokubi >= '2013-01-01'を書いているのはSQLの実行時間を短くするためです。
この条件はあってもなくても結果はかわらないのですが、u.play_tourokubi >= '2013-01-01'がない場合、userreviewテーブルのデータすべてとgamelistテーブルを突き合わせるので相当時間がかかります。
2013年に発売されたゲームであれば得点がつけられたのも2013年以降のはずですので、u.play_tourokubi >= '2013-01-01'でuserreviewテーブルのデータを絞り込んでから突き合わせた方が速いです。
u.play_tourokubi >= '2013-01-01'がある場合、90ms程度で結果が返ってきますが、ない場合は、350ms程度かかります。

条件を指定して情報を抽出する(6) IN述語と副問い合わせ その2

すでに条件を指定して情報を抽出する(3) IN述語と副問い合わせ その1で説明していますが、改めてここでもう一度IN句と副問い合わせについて説明します。
gamelistのIDが19268,18037,15937のデータを抜き出したい、POV「おかずに使える」のAに10件以上登録されているゲームの一覧を取得したい、そんな場合に使用するのがIN句です。
まずは前者の使い方から説明します。
gamelistのIDが19268,18037,15937のデータを抜き出したい場合は以下のようなSQLを実行します。

SQLを実行した結果は以下の通りです。

ちなみに、上記SQLは以下のSQLと一緒です。

この使い方をするのはPHP等のスクリプトからSQLを実行する場合で、人間がSQLをガリガリ書く場合には使われないと思います。
SQLをガリガリ書く場合は、後者の「POV「おかずに使える」のAに10件以上登録されているゲームの一覧を取得したい」のような場合かと思います。
POV「おかずに使える」のAに10件以上登録されているゲームの一覧は以下のように表現できます。

SQLを実行した結果は以下の通りです。

IN句の後ろに括弧でくくった中にSELECT文がでてきました。
この括弧でくくられた中にあるSELECT文のことを副問い合わせと言います。
副問い合わせがあるSQL文の場合、まず副問い合わせの中身が実行されます。

SELECT game
  FROM povgroups
 WHERE pov = 1
   AND rank = 'A'
 GROUP BY game
HAVING COUNT(*) >= 10 )

上記SQLの実行結果は以下の通りです。
13733,6122,12049,2769,10459,以下略
元のSQLは以下のようになります。

SELECT gamename
  FROM gamelist
 WHERE id IN ( 13733,6122,12049,2769,10459,以下略 )
 ORDER BY furigana

※上記SQLは以下のようにも書けます。

SQLは他のプログラミング言語と同様いろいろな書き方ができます。

IN句はNOTをつけることによって「含まない」という表現になります。
例えば「ネガティブ」なPOVが1つもつけられていなく、得点が10個以上つけられていて、得点の中央値が80以上のゲームを抽出するSQLは以下の通りです。

SQLを実行した結果は以下の通りです。

ちょっと難しいかもしれませんので解説をいれます。
まず以下の部分で「ネガティブ」なPOVのidを抽出します。

SELECT id
  FROM povlist
 WHERE system_group = 'ネガティブ'

抽出した結果、元のSQLは以下のようになります。

SELECT gamename
     , model
  FROM gamelist
 WHERE id NOT IN ( SELECT DISTINCT game
                     FROM povgroups
                    WHERE pov IN ( 24,97,103,176,152,98,39,106,502,113,501,528,137,155 )
                 )
   AND count2 >= 10
   AND median >= 80
 ORDER BY furigana

次に以下の部分が実行されます。

SELECT DISTINCT game
  FROM povgroups
 WHERE pov IN ( 24,97,103,176,152,98,39,106,502,113,501,528,137,155 )

その結果、元のSQLは以下のようになります。

SELECT gamename
     , model
  FROM gamelist
 WHERE id NOT IN ( 10295,2026,7494,16812, 省略 )
   AND count2 >= 10
   AND median >= 80
 ORDER BY furigana

上記SQLが実行され、gamelistテーブルからidが10295ではなく、2062でもなく、7494でもなく、16812でもなく…省略、さらにデータ数(count2)が10以上で、中央値が80以上の行のgamenameとmodelが表示されます。

条件を指定して情報を抽出する(7) 副問い合わせ(サブクエリ)

前の章に出てきた副問い合わせについて、ちゃんと説明します。
副問い合わせとは、()で囲まれたSELECT文のことです。
あるSELECT文で抽出された結果を元に、さらにSELECTしたい…という場合に使います。
簡単な例からいきます。
elf(id=70)が最後に発売したタイトルを知りたい場合は以下のようなSQLを実行します。

SQLを実行した結果は以下の通りです。

まず、elfが作ったゲームのうち一番大きい発売日を抽出します。

SELECT MAX(sellday)
  FROM gamelist
 WHERE brandname = 70

次に、gamelistの中から抽出した発売日でかつelfが作ったゲームを抽出します。

SELECT gamename , sellday
  FROM gamelist
 WHERE brandname = 70
   AND sellday = '2013-08-08'

※上記SQL文ではIN述語を使わずに以下のようにSQLを書きました。

   AND sellday = ( SELECT MAX(sellday)
                     FROM gamelist
                    WHERE brandname = 70 )

　SELECTが返す行が1行な場合は、このようにsellday IN ()ではなく、sellday = ()とすることができます。
もし、SELECTが返す結果が複数行の場合はエラーとなります。
だいぶ昔、PostgreSQLはINを使うと性能が悪いということがありましたが、今ではそんなことはありませんので、常にINを使って問題ないでしょう。

もう一つ例を書きます。
例えば声優の葉村夏緒さんが最後に出演したゲームに出演した声優さんの情報が知りたいといった場合は、
1. 声優の葉村夏緒さん(id=9170)が最後に出演したゲームのidを調べる
2. ゲームのidからそのゲームに出演した声優さん(shubetu=5)の情報を抽出する
という手順を踏みます。
SQLで書くと以下のようになります。

SQLを実行した結果は以下の通りです。

「最後に出演したゲーム」を抽出するために、MAX(sellday)で最後に発売される(発売された)ゲームを抽出するのではなく、葉村夏緒さんが出演したゲームを発売日降順に並び替えて最初の1行目の結果を「最後に出演したゲーム」として抽出しています。
これは、発売日が同じゲームがあるとMAX(sellday)で抽出されるゲームが2つになってしまうからです。

今までの例では、WHERE句の中で副問い合わせを使っていました。
副問い合わせは、FROM句の中でも使えます。
声優の葉村夏緒さんが最後に出演したゲームに出演した声優さんの情報を抽出するSQLは以下のようにも書けます。

SQLを実行した結果は以下の通りです。

 INNER JOIN ( SELECT id
                FROM gamelist
               WHERE id IN ( SELECT game
                               FROM shokushu
                              WHERE creater = 9170 )
               ORDER BY sellday DESC
               LIMIT 1 ) AS t
         ON t.id = s.game

副問い合わせをFROM句の中で使う場合は、( SELECT ～ )で抽出した結果に別名をつける必要があります。
上の例ではtという別名をつけています。

※別名をつける際にASを書くのは必須ではないのですが、あった方が私は分かりやすいので書くことにしています。

LEFT OUTER JOIN(左外部結合)

テーブルの結合にはINNER JOINの他にLEFT OUTER JOIN(左外部結合)、RIGHT OUTER JOIN（右外部結合）、FULL OUTER JOIN（完全外部結合）があります。
おそらくLEFT OUTER JOIN(左外部結合)だけ覚えておけばいいんじゃないかな…と思いますので(実際私はLEFT OUTER JOINしか使いません)、LEFT OUTER JOINとは何かとその使いどころについて説明します。

ErogameScapeはクリエイターさんをお気に入りに登録すると、クリエイターさんの名前の横にハートマークがつくようになっています。
クリエイターさんの情報を表示しつつ、もしそのクリエイターさんにお気に入りのユーザーさんがいた場合はフラグをたてたい…そんな場合のSQLは以下のようになります。
※idが5930から5950のクリエイターさんの情報を表示します。

SQLを実行した結果は以下の通りです。

お気に入りに登録しているユーザーさんが1人でもいる場合は、flagの列に1が入ります。
もし、INNER JOINで書いてしまうと、お気に入り登録がないクリエイターさんは表示されなくなってしまいます。
試してみましょう。

左外部結合は、LEFT OUTER JOINの左側にあるテーブルの情報は全部表示しつつ、右側のテーブルの内容をくっつける動作をします。

CASE式

CASE式の有用性について書かれている最もよい文書はCASE式のススメではなかろうかと思います。
povはランクをA,B,Cとつけることができます。
あるゲームにおいて「おかずに使える」のPOVがつけられた数をランク別に知りたいという場合、CASE式が威力を発揮します。
まずはCASE式の文法を書きます。

CASE WHEN condition THEN result
     [WHEN ...]
     [ELSE result]
END

まずは簡単だけど、面白くもなんともない例から書きます。
ErogameScapeのクリエイターさんの原画/シナリオ/声優等の職種を格納するshokushuテーブルのshubetuは1: 原画 2:シナリオ 3:音楽 4:キャラデザ 5:声優 6:歌手 7:その他となっています。
イノセントガール(id=19478)のクリエイターさんの情報をSQLで抽出すると以下のようになってしまい誰がどんな職種なのかさっぱり分かりません。

SQLを実行した結果は以下の通りです。

このままですとshubetuとshubetu_detailがいったいなんなのか分かりませんのでCASE式を使って書き換えます。

SQLを実行した結果は以下の通りです。

※ここで「おやっ?」と思った方、多分その疑問は正解です。
ErogameScapeのDBにはshubetuが1だと原画で、2だとシナリオで…というテーブルがありません。
PHPのスクリプト側にshokushu_id_to_shokushu_nameという関数があって、1だと原画、2だとシナリオ…という情報を持っています。
なんかいまいちな気もするのですが、1:原画、2:シナリオ…という情報だけのテーブルを持つのも何か冗長な気がして、かといってshokushuテーブルのshubetuに直接、原画、シナリオと格納すると、将来なにがしらかの拡張…例えば1:原画、2:シナリオ…という情報だけじゃなくなってテーブルを新たに追加することになった場合に面倒かも…とかあって、何か中途半端になっています。
セオリーはどうなんでしょうか…

ここまでは教科書に載っているCASE式の使い方で、いやあ、こんなのSQLじゃなくてスクリプト側でやるからCASE式って使い道ないなと思っていた私でしたが、CASE式の真の使い道はこれではありません。
それでは本題に入ります。
美少女万華鏡 －呪われし伝説の少女－(id=16065)において「おかずに使える(id=1)」のPOVがつけられた数をランク別に知りたいという場合のSQLを下に示します。

SQLを実行した結果は以下の通りです。

もし上記SQLをCASE式を使わないで実現しようとすると、povgroupsテーブルを3回、上から下まで検索しないと駄目です。
CASE式を使わないで実現しようととすると以下のようになるかと思います。

それでは、SQLの解説をします。
まずSUMを除いた結果がどうなるかを見てみます。
わかりやすさのためにユーザーID(uid)も表示します。

SQLを実行した結果は以下の通りです。

CASE式を使って、rankがAだったらA列に1をB列とC列には0を、rankがBだったらB列に1をA列とC列には0を、rankがCだったらC列に1をA列とB列には0を格納しています。
この後、A列の数字を全部足せば、Aをつけられた数の総和が、同様にB列の数字を全部足せば、Bをつけられた数の総和が求まります。
総和を求めるには集約関数のSUMを使います。
Excelだとピボットテーブルでちょいちょいと作れますが、SQLの場合はCASE式とSUMを駆使するんだなあと思って頂ければ…と思います。

分析関数(ウィンドウ関数)

私が大学生だった頃は授業で分析関数に触れることはありませんでした。
分析関数の使い方について、いまいち「これがいい!」という文書がなくて、今でもしっくりきていなくて、使いこなせていません。
ここでは実際にErogameScapeで使っている例を紹介していきます。

ErogameScapeは元々「エロゲー評価統計情報」というサイトが前身で、そのサイトの目的は「BEEP!メガドライブ」にあったランキングのエロゲー版を作ることでした。
ランキングを作りたいといった場合、分析関数を使うと簡単に実現できます。
以下のSQLはデータ数が10以上のエロゲーを中央値降順に並び替えて先頭列に順位をつけたものです。

SQLを実行した結果は以下の通りです。

以下の部分が分析関数の部分です。

SELECT rank() OVER ( ORDER BY median DESC ) AS rank

分析関数の文法は以下の通りです。

function_name ([expression [, expression ... ]]) OVER ( window_definition )
function_name ([expression [, expression ... ]]) OVER window_name
function_name ( * ) OVER ( window_definition )
function_name ( * ) OVER window_name
ここで、window_definitionは以下の構文になります。

[ existing_window_name ]
[ PARTITION BY expression [, ...] ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ frame_clause ]
オプションのframe_clauseは次の中の１つです。

[ RANGE | ROWS ] frame_start
[ RANGE | ROWS ] BETWEEN frame_start AND frame_end
ここでframe_startおよびframe_endは以下のいずれかです。

UNBOUNDED PRECEDING
value PRECEDING
CURRENT ROW
value FOLLOWING
UNBOUNDED FOLLOWING

ではrank() OVER ( ORDER BY median DESC )を少しずつ見ていきます。
rank()は文法のfunction_nameにあたる部分で関数です。
関数の説明はウィンドウ関数に書いてあるのですが、説明がいまいちです…
rank()は、順位をつける関数です。
SQLの実行結果を見てみましょう。

WHITE ALBUM2は95点で1位なので、rankは1です。
YU-NOは94点で2位なので、rankは2です。
マブラヴ オルタネイティヴは93点で3位なので、rankは3です。
BALDR SKY Diveは90点で4位なので、rankは4です。
鬼畜王ランスは90点でやっぱり4位なので、rankは4です。
ちなみに89点のゲームは家族計画なのですが、こちらは4位のゲームが12個あるので16位ですから、rankは16になります。
というように、同点の場合は同じ順位をつけてくれるのがrank()関数です。

※ちなみに行番号を振りたい場合は、row_number()を使います。
以下に実行結果を示します。

SQLを実行した結果は以下の通りです。

次にOVER ( ORDER BY median DESC )の部分です。
OVERの()の中には、並び替えたい方法を書きます。
中央値降順に並び替えて順位をつけたいので、ORDER BY median DESCと書いています。

ちなみに、OVER()の中にORDER BY median DESCと書かずに、外側?にORDER BY median DESCと書くのはNGです。
試しに実行してみましょう。

行数も順位もおかしなことになっています。
これは分析関数が適用されて行数や順位が決定された後に、ORDER BY median DESCが実行されることが原因です。
※なぜ順位がすべて1になるのかは分からないです…

では次に各年ごとのランキングを表示するSQLを示します。

SQLを実行した結果は以下の通りです。

上記SQLはOVER句の中に新たにPARTITION BY ( EXTRACT(YEAR FROM sellday) )が増えています。
EXTRACT(YEAR FROM sellday)はselldayから年だけを抜き出しています。
PARTITION BYを日本語にすると「○○ごとに」という表現がいいのかな…と思います。
EXTRACT(YEAR FROM sellday)で年だけが抜き出されるので、2013年ごとに、2012年ごとに、2011年ごとに、ORDER BY median DESCして…つまり中央値順に並び替えて、rank()…順位をふっていく…となります。

WITH句

SQLを書いていると「この部分は同じこと書くんだけどなあ…」と思うことがあります。
多くのプログラム言語は関数という機能があって、共通処理は関数にします。
SQLにも共通部分を切り出す機能があります。
それがWITH句です。

では実際に使ってみましょう。
ErogameScapeでは得点順コメントを見る画面において、得点だけをつけているユーザーさんの名前の横に、そのユーザーさんが書いたコメント数を表示するようになっています。
例えばap2(5)となっていたら、ユーザーap2が5つコメントを書いていることを表します。
SEXティーチャー剛史に得点のみをつけたユーザーについて、SEXティーチャー剛史につけた点数とそのユーザーさんが書いたコメント数を表示するようなSQLを作りたいと思ったとします。
まず、userreviewテーブルからSEXティーチャー剛史に得点のみをつけたユーザーを抜き出す必要があります。
さらにそのユーザーのコメント数を集計した上で、ユーザーを抜き出した表にくっつける必要があります。
SQLは以下のようになります。

SQLを実行した結果は以下の通りです。

上記SQLはuserreviewテーブルからgame=19672、hitokoto IS NULL、tokuten IS NOT NULLという条件でデータを抽出するという作業を2回やっています。
赤字の部分です。

SELECT u.uid
     , u.tokuten
     , u.play_tourokubi
     , t1.count
  FROM userreview u
 INNER JOIN ( SELECT uid
                   , COUNT(*)
                FROM userreview
               WHERE uid IN ( SELECT uid
                                FROM userreview u
                               WHERE hitokoto IS NULL
                                 AND tokuten IS NOT NULL
                                 AND u.game = 19672 )
                 AND hitokoto IS NULL
               GROUP BY uid ) AS t1
    ON t1.uid = u.uid
 WHERE hitokoto IS NULL
   AND tokuten IS NOT NULL
   AND u.game = 19672

この繰り替えしの部分を、WITH句で切り出してみます。
WITH句の文法は…といいドキュメントが見つかりませんでした…、PostgreSQLの公式ドキュメントにリンクをはっておきます。

SQLの見通しがよくなりました…多分。
上記の例は見通しをよくするためのWITH句の使い方でした。
WITH句には再帰的問い合わせという機能があります。
こちらはErogameScapeでは使っていないのですが、通常のSQLではできないことが出来るので、ドキュメントにリンクをはっておきます。

ユーザーさんが作ったSQL

評価の多いソフト

ゲームにつけられた得点の数を100倍して、今日の日付から発売日を引いた日数で割った順番にならべてみたというSQLです。
発売されてから日数が経てば経つほど分母が大きくなっていき話題指数が下がっていくというものです。

長文感想参照数

長文感想参照数をゲームごと、ユーザーごと、コメントごとに集計したものです。
サブクエリはSELECTの中では使えないと思っていたのですが、このSQLを見て使えることを知りました…、衝撃です…

指定条件に該当するユーザーさん限定の統計表（中央値・３ヶ月版）

ここまで学んだことを総動員しつつPostgreSQLの独自機能を知らないとできないSQLです。
これが理解できればErogameScapeからデータを引っ張ってきて加工するには問題ないレベルに到達したと言えます。
読んで、ああ…なるほどなあ…と思いました。
まずSQLの意味を書きます。
最初にErogameScapeにデータを登録した日から最後にErogameScapeにデータを登録した日の差が1年で、付けた得点の数が10以上かつ平均が40から90かつ標準偏差が7から27のユーザーによるゲームの中央値と平均値と標準偏差と得点数を、すべてのユーザーによるゲームの中央値と平均値と標準偏差と得点数を比較した表を生成する、です。
単発で得点をいれたり100点や0点に偏って得点をつけるユーザーを排除した値と、そのままの値を比較しようという主旨のSQLです。

それでは順を追って説明します。
以下の赤字部分が「ErogameScapeにデータを登録した日から最後にErogameScapeにデータを登録した日の差が1年で、付けた得点の数が10以上かつ平均が40から90かつ標準偏差が7から27のユーザー」を抽出する部分です。

select my.gamename
     , my.model
     , my.brandname
     , my.sellday as 発売日
     , my.median as 中央値
     , replace((my.trust_median - my.median)::text, '-', '▲') as →
     , my.trust_median as 補正
     , my.toukei[1] as 平均値
     , replace((my.trust_toukei[1] - my.toukei[1])::text, '-', '▲') as →
     , my.trust_toukei[1] as 補正
     , my.toukei[2] as 標準偏差
     , replace((my.trust_toukei[2] - my.toukei[2])::text, '-', '▲') as →
     , my.trust_toukei[2] as 補正
     , my.toukei[3] as 得点数
     , replace((my.trust_toukei[3] - my.toukei[3])::text, '-', '▲') as →
     , my.trust_toukei[3] as 補正
  from ( select ttt.game_id
              , ttt.gamename
              , ttt.brand_id
              , ttt.brandname
              , ttt.sellday
              , ttt.model
              , ttt.median
              , ( select round(avg(foo.tokuten),1)
                    from ( select ur2.tokuten
                                , row_number() over(order by ur2.tokuten) as rank
                                , count(ur2.tokuten) over() as cnt
                             from userreview as ur2
                            where ur2.game = ttt.game_id
                              and ur2.uid = any(ur_wrap.userlist) ) as foo
                   where (    mod(foo.cnt,2) = 0 and foo.rank in (foo.cnt/2.0, foo.cnt/2.0+1))
                           or (mod(foo.cnt,2) = 1 and foo.rank = ceil(foo.cnt/2.0)) ) as trust_median
              , array[ round(ttt.average::int, 1)
                     , round(ttt.stddev, 1)
                     , ttt.count ] as toukei
              , ( select array[ round(avg(ur3.tokuten),1)
                              , round(stddev(ur3.tokuten),1)
                              , count(ur3.tokuten) ]
                    from userreview as ur3
                   where ur3.game = ttt.game_id
                     and ur3.uid = any(ur_wrap.userlist) ) as trust_toukei
           from toukei_temp_table as ttt
              , ( select array_agg(ur1.uid) as userlist
                    from ( select ur0.uid
                             from userreview as ur0
                            group by ur0.uid
                           having avg(tokuten) between 40 and 90
                              and stddev(tokuten) between 7 and 27
                              and count(ur0.tokuten) >= 30
                              and max(ur0.play_tourokubi) - min(ur0.play_tourokubi) >= '12 months'::interval ) as ur1
                ) as ur_wrap
          where ttt.sellday between current_date - '3 months'::interval and current_date 
       ) as my
 where 0 < ALL(trust_toukei)
 order by my.trust_median desc

上記SQLで得られたユーザーだけでuserreviewテーブルからデータを抽出して中央値等を計算します。
抽出の条件式を、uid = any( 上記SQLで得られたユーザー )と書くのですが、anyの引数はarrayとなるので、array_agg関数で、得られたユーザーを配列にしておきます。
以下のSQLを実行して確かに配列が返ってくることを確認して下さい。

※もしPostgresqlでなく他のDBだった場合は、array_agg関数が使えないので、ur_wrap.userlistの部分に直接サブクエリを書くことになるかと思います。
もしかしたらarray_agg相当の関数があるかもしれませんが…

次に抽出したユーザーだけの得点の平均値、標準偏差、データ数を求めます。
※中央値を求める部分については後述します。
抽出したユーザーだけの得点の平均値、標準偏差、データ数を求めているのは以下の赤字の部分です。
求めた結果はtrust_toukeiという配列に格納しています。
trust_toukei[1]には平均値、trust_toukei[2]には標準偏差、trust_toukei[3]にはデータ数が格納されます。

select my.gamename
     , my.model
     , my.brandname
     , my.sellday as 発売日
     , my.median as 中央値
     , replace((my.trust_median - my.median)::text, '-', '▲') as →
     , my.trust_median as 補正
     , my.toukei[1] as 平均値
     , replace((my.trust_toukei[1] - my.toukei[1])::text, '-', '▲') as →
     , my.trust_toukei[1] as 補正
     , my.toukei[2] as 標準偏差
     , replace((my.trust_toukei[2] - my.toukei[2])::text, '-', '▲') as →
     , my.trust_toukei[2] as 補正
     , my.toukei[3] as 得点数
     , replace((my.trust_toukei[3] - my.toukei[3])::text, '-', '▲') as →
     , my.trust_toukei[3] as 補正
  from ( select ttt.game_id
              , ttt.gamename
              , ttt.brand_id
              , ttt.brandname
              , ttt.sellday
              , ttt.model
              , ttt.median
              , ( select round(avg(foo.tokuten),1)
                    from ( select ur2.tokuten
                                , row_number() over(order by ur2.tokuten) as rank
                                , count(ur2.tokuten) over() as cnt
                             from userreview as ur2
                            where ur2.game = ttt.game_id
                              and ur2.uid = any(ur_wrap.userlist) ) as foo
                   where (    mod(foo.cnt,2) = 0 and foo.rank in (foo.cnt/2.0, foo.cnt/2.0+1))
                           or (mod(foo.cnt,2) = 1 and foo.rank = ceil(foo.cnt/2.0)) ) as trust_median
              , array[ round(ttt.average::int, 1)
                     , round(ttt.stddev, 1)
                     , ttt.count ] as toukei
              , ( select array[ round(avg(ur3.tokuten),1)
                              , round(stddev(ur3.tokuten),1)
                              , count(ur3.tokuten) ]
                    from userreview as ur3
                   where ur3.game = ttt.game_id
                     and ur3.uid = any(ur_wrap.userlist) ) as trust_toukei
           from toukei_temp_table as ttt
              , ( select array_agg(ur1.uid) as userlist
                    from ( select ur0.uid
                             from userreview as ur0
                            group by ur0.uid
                           having avg(tokuten) between 40 and 90
                              and stddev(tokuten) between 7 and 27
                              and count(ur0.tokuten) >= 30
                              and max(ur0.play_tourokubi) - min(ur0.play_tourokubi) >= '12 months'::interval ) as ur1
                ) as ur_wrap
          where ttt.sellday between current_date - '3 months'::interval and current_date 
       ) as my
 where 0 < ALL(trust_toukei)
 order by my.trust_median desc

tttはtoukei_temp_tableの別名です。
ur3.game = ttt.game_idがないと、すべてのゲームについて平均値等を計算することになるので実行コストがかさみます。
自信が無いのですが、where句のttt.sellday between current_date - '3 months'::interval and current_dateが適用されてゲームが絞りこまれたtoukei_temp_tableのgame_idと、userreviewのgameを突き合わせていると思います。
次に、抽出したユーザーだけの得点の中央値を求める部分の解説です。
中央値を求める集約関数はないので、分析関数を駆使して求めます。
以下の赤字の部分が中央値を求めている部分です。

select my.gamename
     , my.model
     , my.brandname
     , my.sellday as 発売日
     , my.median as 中央値
     , replace((my.trust_median - my.median)::text, '-', '▲') as →
     , my.trust_median as 補正
     , my.toukei[1] as 平均値
     , replace((my.trust_toukei[1] - my.toukei[1])::text, '-', '▲') as →
     , my.trust_toukei[1] as 補正
     , my.toukei[2] as 標準偏差
     , replace((my.trust_toukei[2] - my.toukei[2])::text, '-', '▲') as →
     , my.trust_toukei[2] as 補正
     , my.toukei[3] as 得点数
     , replace((my.trust_toukei[3] - my.toukei[3])::text, '-', '▲') as →
     , my.trust_toukei[3] as 補正
  from ( select ttt.game_id
              , ttt.gamename
              , ttt.brand_id
              , ttt.brandname
              , ttt.sellday
              , ttt.model
              , ttt.median
              , ( select round(avg(foo.tokuten),1)
                    from ( select ur2.tokuten
                                , row_number() over(order by ur2.tokuten) as rank
                                , count(ur2.tokuten) over() as cnt
                             from userreview as ur2
                            where ur2.game = ttt.game_id
                              and ur2.uid = any(ur_wrap.userlist) ) as foo
                   where (    mod(foo.cnt,2) = 0 and foo.rank in (foo.cnt/2.0, foo.cnt/2.0+1))
                           or (mod(foo.cnt,2) = 1 and foo.rank = ceil(foo.cnt/2.0)) ) as trust_median
              , array[ round(ttt.average::int, 1)
                     , round(ttt.stddev, 1)
                     , ttt.count ] as toukei
              , ( select array[ round(avg(ur3.tokuten),1)
                              , round(stddev(ur3.tokuten),1)
                              , count(ur3.tokuten) ]
                    from userreview as ur3
                   where ur3.game = ttt.game_id
                     and ur3.uid = any(ur_wrap.userlist) ) as trust_toukei
           from toukei_temp_table as ttt
              , ( select array_agg(ur1.uid) as userlist
                    from ( select ur0.uid
                             from userreview as ur0
                            group by ur0.uid
                           having avg(tokuten) between 40 and 90
                              and stddev(tokuten) between 7 and 27
                              and count(ur0.tokuten) >= 30
                              and max(ur0.play_tourokubi) - min(ur0.play_tourokubi) >= '12 months'::interval ) as ur1
                ) as ur_wrap
          where ttt.sellday between current_date - '3 months'::interval and current_date 
       ) as my
 where 0 < ALL(trust_toukei)
 order by my.trust_median desc

いったいなぜ上記の赤字のSQLで中央値求まるか?については、PostgreSQLの分析関数の衝撃1（モードとメジアン）を参照してください。
以前は何もしなくても見られたの気がするのですが、今は会員登録しないと見られないですね…

toukeiにはtoukei_temp_tableから得られた中央値等が、trust_toukeiおよびtrust_medianには抽出したユーザーだけの中央値等が格納されたので、my.trust_toukei[1] - my.toukei[1]で差分をとって、もし-の値だったら-を▲に置換して見やすくしています。
ゲームによっては、trust_toukeiに値が入ってこない場合があります。
trust_toukeiにデータがないゲームは表示しないようにするため、下から2行目のwhere句に0 < ALL(trust_toukei)という条件があります。

いかがだったでしょうか。
とてもよく出来ているSQLだと思います。

ユーザーさんが作ったすごいSQL

ただSQLを実行するだけでなくSQLの中にjavascriptを埋め込むことによって、私の想像をはるかに超える出力になっているSQLの一覧です。
私にも作り方は分かりません…

最近話題のゲームベスト50

やってやれないことはない…けど、実際やりました的なSQL。画像があると映えますね。

人気ユーザーさんランキング（軽量版）

どうやってSQLで書いたのかもはや分からないレベルの芸術作品です。

お好みおかず検索

ソースを見るとSQLの中に美しくjavascriptが埋め込まれている芸術作品。なんかもうErogameScapeじゃなくて別サイトみたい。

◯◯ゲーマーによる、◯◯ゲームの新着レビュー

なんかErogameScapeの新着コメントに似ている画面なんですけど、SQLだけで新着コメントよりも全然高機能な画面が実現されています。

この文書を書いた人

名前

ひろいん

twitter

@hiro_in