ライトノベル

ラノベオールタイムベストまとめ 2巻目

はじめに

データベースの醍醐味は、データをいろいろいじってみることで自分で想像だにしなかったことが見えてくること、だと思っています。
前回の文書は、投票数順、レーベル別順、年代別順、と投票順に並べてみたものでした。
この文書は、ちょっと視点をかえて、「どなたとどなたが同じラノベに投票したか」に焦点をあててみるものです。

投票された方、投票された方のフォロワーの方々、にしか面白くない文書になりそうな雰囲気がしますが…どうでしょうか…

前回同様、表を作るのに使ったSQLも記載していきます。
自己結合ってこうやって使うんだなあと思って頂ければいいかなと思います。

自己結合については、自己結合の使い方がよい文書ではないかと思います。
ミックさんの文書はお勧めです。

本題

ラノベオールタイムベストの基本ルールは、1人が10のライトノベルに投票する、でした。
ある方とある方が同じライトノベルに投票した…それも数が多いほど、そのお二方の趣向は似ていると思って良いと考えられるでしょう。
最初に、同じライトノベルに投票した数が多かった方々のご紹介です。

概要
  1. 参加者数 : 197人
  2. 有効投票数 : 1776
  3. 1人あたりの平均投票数 : 9
同じライトノベルに投票した数が多かった方々

twittertwitter同じラノベに
投票した数
kobu_wirdrehpic07
VVV0201yogu4956
kobu_wirdsusuki_AKI6
koo_koutekeru6
moukwairehpic06
rehpic0susuki_AKI6
Kotobuki_Prehpic05
MEG_apr02kobu_wird5
MEG_apr02aoi_kana5
MEG_apr02kentosan275
MEG_apr02moyashi45
Nagara_KDZKsusuki_AKI5
Nagara_KDZKrehpic05
Raining_summerVVV02015
Rebellion_vpirows5
Rebellion_vtekeru5
aoi_kanakobu_wird5
kentosan27susuki_AKI5
kobu_wirdrinda09075
moyashi4tsukushi_k5
moyashi4rehpic05
nai_omoitsukaokatachi5
rehpic0tekeru5
rehpic0yui06245
rehpic0tsukushi_k5

5つ以上同じラノベに投票した方々の表です。同じラノベに投票した数順に並び替えています。
同じラノベに投票した数が多いほど趣向が似ているかもしれません。
SELECT a1.twitter
     , a2.twitter
     , COUNT(*)
  FROM lightnovel.alltimebest a1
 INNER JOIN lightnovel.alltimebest a2
         ON a1.isbn = a2.isbn
 WHERE a1.twitter <> a2.twitter
   AND a1.twitter < a2.twitter
 GROUP BY a1.twitter
        , a2.twitter
HAVING COUNT(*) >= 5
 ORDER BY COUNT(*) DESC
        , a1.twitter

ちなみに、1つ以上同じラノベに投票した方々の組み合わせ数は以下の通りです。

同じラノベに投票した数 同じラノベに投票した方の組み合わせ数
7 1
6 5
5 19
4 72
3 258
2 907
1 3704

投票したラノベが5つ、6つ、7つ被るのは相当すごいと思います。
SELECT count
     , COUNT(*)
  FROM (
         SELECT a1.twitter
              , a2.twitter
              , COUNT(*)
           FROM lightnovel.alltimebest a1
          INNER JOIN lightnovel.alltimebest a2
                  ON a1.isbn = a2.isbn
          WHERE a1.twitter <> a2.twitter
            AND a1.twitter < a2.twitter
          GROUP BY a1.twitter
                 , a2.twitter
         HAVING COUNT(*) >= 1
       ) AS t
 GROUP BY count
 ORDER BY COUNT(*) 
;

ライトノベルオールスターに参加された人数は197人でした。
ルールにのっとって10のラノベに投票された方で、投票したラノベが他の方と完全にかぶらない方は一人もいらっしゃいませんでした。
SELECT twitter
     , COUNT(*)
  FROM lightnovel.alltimebest
 WHERE twitter IN ( SELECT DISTINCT twitter
                      FROM lightnovel.alltimebest
                     WHERE twitter NOT IN (
                                            SELECT twitter
                                              FROM (
                                                     SELECT a1.twitter
                                                          , COUNT(*)
                                                       FROM lightnovel.alltimebest a1
                                                      INNER JOIN lightnovel.alltimebest a2
                                                              ON a1.isbn = a2.isbn
                                                      WHERE a1.twitter <> a2.twitter
                                                      GROUP BY a1.twitter
                                                             , a2.twitter
                                                     HAVING COUNT(*) >= 1
                                                   ) AS t1
                                          )
                   )
 GROUP BY twitter
HAVING COUNT(*) >= 10 ;

同様に、1つしかかぶらなかった方は、6人でした。
SELECT twitter
     , COUNT(*)
  FROM lightnovel.alltimebest
 WHERE twitter IN ( SELECT DISTINCT twitter
                      FROM lightnovel.alltimebest
                     WHERE twitter NOT IN (
                                            SELECT twitter
                                              FROM (
                                                     SELECT a1.twitter
                                                          , COUNT(*)
                                                       FROM lightnovel.alltimebest a1
                                                      INNER JOIN lightnovel.alltimebest a2
                                                              ON a1.isbn = a2.isbn
                                                      WHERE a1.twitter <> a2.twitter
                                                      GROUP BY a1.twitter
                                                             , a2.twitter
                                                     HAVING COUNT(*) >= 2
                                                   ) AS t1
                                          )
                   )
 GROUP BY twitter
HAVING COUNT(*) >= 10 ;
同様に、2つしかかぶらなかった方は、24人でした。
8割近くの方々には「3つ以上同じラノベを選んだ方」が1人はいらっしゃるかなと思います。

続きを読む

ラノベオールタイムベストまとめ

はじめに

2013年5月30日にi_j_さんつぶやきによって、5月30日から6月2日にかけて、ラノベクラスタの方々が、過去の全作品を通して、選出した良ラノベ10件をあげていく営みが行われました。
その結果、総参加者数197人、総有効投票数1776、というデータが集まりましたが、twitterは何もしないとデータは流れて消えて行くのみでもったいないと思いました。

そこで、「#ラノベオールタイムベスト」でつぶやかれた書籍を集計し報告するのがこの文書の目的です。
ついでに、集計のためにSQLのウィンドウ関数を使ってみたので、SQLのウィンドウ関数の使い方も実例を交えて書いていくことにします。

※フレームワークを使っていると、フレームワークに実装されたORMをなるべく使うことになる(直でSQL書いたら負けみたいな感じを受けます。自分は、SELECTについては、直でSQL書いちゃうのですが)…ので、ORMでデータを取り出し、スクリプト言語で取り出したデータを加工する…つまり集計することになるので、ちょっとしたサイトを構築する場合、ウィンドウ関数を使う機会はないのかなという気がしますが、こういう静的な文書を作る場合は、SQLでちょちょいと集計して加工できるのは便利かなと思います。

※書いてる途中で力尽きてきたので、ラノベオールタイムベストのデータの加工/集計については別記事で書くことにします。とりあえず、ErogameScapeで慣れている(そんな方は数少ないと思いますが…)のために、実行したSQLだけ記載しておきます。


私について

日曜プログラマーです。
仕事ではサーバー、NW機器を監視しています。

最近読んだラノベは、なれる!SE (9) ラクして儲かる?サービス開発 (電撃文庫)です。
SEな方々、またその周辺の方々に、大変お勧めのラノベかと思います。

最近読んだ小説はハゲタカ(上) (講談社文庫)です。
ハゲタカは…投資/投機に興味がないとちょっとつらいと思います。
同じ真山 仁さんの著書としてベイジン〈上〉 (幻冬舎文庫) の方が多くの方にお勧めだと思います。
書かれている内容が現実となってしまっているので、読んでいてなんとも言えない気分になります。

ブクスタ
というサービスを作ったので、ブクスタで積むネタがないかなあと思っていたら、「#ラノベオールタイムベスト」がはじまって、これはいいネタになると思って、集計作業をはじめたら、こんな文書ができあがりました。


元データについて

i_j_さんが、「みんなで好きなラノベを十作挙げよう」と宣言してはじまったのが「#ラノベオールタイムベスト」です。
10作あげていない方も、10作以上あげている方もいらっしゃいますが、すべてのデータを集計対象としました。
5件しかあげていない方の場合は5件、20件あげた方の場合は20件をデータとして扱っています。

つぶやいて頂いたタイトルは短縮されたものがあります。
極力調べて「これだろう」と思うものに正規化いたしました。(正規化という言葉でよいのか自信がないのですが、正規化ということにします。)
例えば「○マ」は「今日からマのつく自由業!」に変換しています。

正規化ができなかったものもあります。
たとえば「ずっと」というつぶやきもあったのですが、これがどの書籍にあたるか分かりませんでした。
正規化できはなかったものは集計対象外としました。

また「成田作品」というつぶやきもありました。
こちらも集計の対象外としました。

つぶやいて頂いたラノベは1巻で終わることが少なく大抵シリーズとなっています。
基本はシリーズを1レコードとして集計し、シリーズの中でも巻が特定されている場合は、特定の巻を1レコードとして集計しました。

例えば「涼宮ハルヒ」とつぶやいて頂いた場合は「涼宮ハルヒシリーズ」として集計しました。
「涼宮ハルヒの消失」とつぶやいて頂いた場合は「涼宮ハルヒの消失」として集計しました。

元となるデータをご覧になりたい方は以下のリンクをクリックし、表示されたフォームに「* FROM lightnovel.alltimebest ORDER BY id」と入力してください。
SQL入力フォーム

続きを読む
記事検索