sqlite超便利 / はてなブックマークの人気エントリについて
sqliteという、ファイルをDBとして扱えるライブラリ、ツールがあって超便利なので、はてなブックマークの人気エントリ、６月分をまとめて入力してみました。このファイルです。展開すると、hb.dbというファイルが出てきます。
準備
僕がWindows XPで作業したので、それで説明します。DB内のデータは全てシフトJISに変換してあります。
SQLite Download Pageから、Precompiled Binaries For Windowsのsqlite-3_2_2.zipを落としてくる。展開すると、sqlite3.exeが出来るので、hb.dbと同じディレクトリに入れて、コマンド プロンプトから
C:\work>sqlite3 hb.db
で起動。
sqlite>.schema
で定義されてるテーブル等が表示されるので、SQLで検索とかして遊ぶ。正規化は全然してありません。内容はカラム名から大体推測できると思います。
遊び方の例
遊び方の例を説明しますが、データがきちんと出来ているか、SQLが正しいかはいまいち確信が持てないので、間違っていたら指摘をお願いします。しかし勿論僕は大体正しいだろうとは思っています。
基礎的なデータ
daily_hotテーブルに6/1から6/30までの人気エントリデータが入っています。人気エントリは一日50件取ってきているので、1500になるはずですが、実際は1502行あります。なぜか6/8だけ52件あるからです。理由は分かりません。
人気エントリは同じエントリが複数日に掲載されるので、重複を省いて表示するVIEWがHOT_ENTRYで、こっちは1108件あります。最大で7日間にわたって人気エントリに表示された、ロングテイルな人気エントリもあります。
entryは実際に取得してきたコメント画面のデータ。commentはコメント一件につき一行のテーブルです。この辺から面倒になってきたので、あまり細かいデータは入れてありません。日付とID程度。コメントは34270件ある。
人気のあるドメイン
傾向が偏りがちであると話題のはてなブックマークなので、本当に偏っているか調べる。
select domain, count(*) from HOT_ENTRY group by domain order by count(*) desc;
| www.itmedia.co.jp | 108 |
| japan.cnet.com | 58 |
| itpro.nikkeibp.co.jp | 27 |
| headlines.yahoo.co.jp | 26 |
| blog.livedoor.jp | 22 |
| pc.watch.impress.co.jp | 22 |
| pcweb.mycom.co.jp | 19 |
| www.hatena.ne.jp | 18 |
| d:id:jkondo | 17 |
| internet.watch.impress.co.jp | 17 |
カリスマ被ブックマークダイアラー
?D限定で、多く人気エントリに入っている方。
select domain, count(*) from HOT_ENTRY where domain like 'd:id:%' group by domain order by count(*) desc;
| d:id:jkondo | 17 |
| d:id:umedamochio | 16 |
| d:id:naoya | 12 |
| d:id:kanose | 11 |
| d:id:kowagari | 10 |
| d:id:wetfootdog | 7 |
| d:id:keyword | 6 |
| d:id:mkusunok | 6 |
| d:id:higepon | 5 |
| d:id:kawasaki | 5 |
被アンテナに比べると、スタッフの方が多い感じがします。
アルファブックマーカー
一番最初にコメントを付けた回数の多い方。副問い合わせを使ったら返事が返ってこなかったので、作業用のテーブルが作ってあります。
select c.id, count(*) from comment c, get0 g where c.no = g.max and c.entry_no = g.entry_no group by id order by count(*);
| umedamochio | 30 |
| jazzanova | 27 |
| otsune | 22 |
| cx20 | 21 |
| citora | 18 |
| naoya | 18 |
| stella_nf | 18 |
| b4-tt | 16 |
| hiromark | 15 |
| uguisyu | 15 |
逆に、その方でコメントが終わっている場合が多い、オメガブックマーカーな方。
select id, count(*) from comment where no = ' 0' group by id order by count(*) desc;
| nazoking | 98 |
| kei1982 | 37 |
| Ash | 15 |
| buyaapl | 12 |
| ebibin | 11 |
| hirokiea | 11 |
| endor | 10 |
| maomaneki | 10 |
| muamqm | 9 |
| sivadselim | 9 |
すごい！ ぶっちぎりだ……。
あとは、自分と同じ傾向のブックマークを探すのとかに便利かも知れないので、どうぞご利用ください。
被アンテナが似てるかどうか調べるスクリプト(まだアンテナのMAP機能がなかったころ)とか、?Dを閲覧する時にTrackBack元の記事を全部もってきてページ内に埋め込むGreaseMonkeyとかを作った時は全く、一度もはてな内からは言及されなかったので、僕はかなり恨みに思っています。そのあとの、はてな村の話題では盛り上がったくせに……。とか、書いておけば、晒し上げ的なブックマークも期待できる、というメソッド。
