この日記の記事名とURLの組み合わせをmysqlに入れてみた
この日記の記事名とURLの組み合わせをmysqlに入れてみました。アクセスログのURLを記事名に解決するために使います。
今回、置換処理に使ったエディタはサクラエディタです。また、すべての置換処理で正規表現を使っています。
記事名とURLの組み合わせを格納するテーブル「articles」を作成する。
記事名とURLの組み合わせを格納するテーブル「articles」を作成します。テーブルを作成するためのDDLは以下です。
create table articles (
url varchar(300),
title varchar(300)
);
はてなダイアリーの記事一欄のHTMLソースを取得する。
記事一覧のHTMLソースを加工して、insert文を作ります。
で始まる行に印を付ける。
記事一覧のHTMLソースでは、
で始まる以下のようなに記事名とURLが含まれます。
<td class="titlecell"><a href="http://d.hatena.ne.jp/nattou_curry_2/20081207/1228654046"> The Java Tutorials -- How to Use Tablesの日本語訳(途中)</a></td>
そこで、
で始まる行に印を付けます。印を付けるために、エディタで以下の置換を行います。付ける印は「#」です。
置換前「^<td class="titlecell">」 → 置換後「#<td class="titlecell">」
印のない行を削除する。
印のない行は無関係なので、すべて削除します。削除するために、エディタで以下の置換を行います。
置換前「^[^#].*\r\n」 → 置換後「」
この結果、以下のような行だけが残ります。
#<td class="titlecell"><a href="http://d.hatena.ne.jp/nattou_curry_2/20081214/1229247877"> org.apache.commons.beanutils.PropertyUtils</a></td>
#<td class="titlecell"><a href="http://d.hatena.ne.jp/nattou_curry_2/20081213/1229179629"> org.apache.commons.beanutils</a></td>
#<td class="titlecell"><a href="http://d.hatena.ne.jp/nattou_curry_2/20081213/1229176493"> Commons BeanUtils 1.8.0 APIのパッケージ一覧</a></td>
記事名とURLの組み合わせのinsert文を作る。
ここで、記事の名前とURLの組み合わせのテーブルに入れるためのinsert文を作ります。エディタで以下の置換を行います。
置換前「^#<td class="titlecell"><a href="([^"]*)"> *([^<]*)</a></td> \r\n」 → 置換後「insert into articles values('\1','\2');\r\n」これにより、以下のようなinsert文ができます。
insert into articles values('http://d.hatena.ne.jp/nattou_curry_2/20081214/1229247877','org.apache.commons.beanutils.PropertyUtils');
insert into articles values('http://d.hatena.ne.jp/nattou_curry_2/20081213/1229179629','org.apache.commons.beanutils');
insert into articles values('http://d.hatena.ne.jp/nattou_curry_2/20081213/1229176493','Commons BeanUtils 1.8.0 APIのパッケージ一覧');このinsert文を実行すれば、今回の目的は作業です。
実際に、アクセスログのURLを記事名に解決してみた。
以下のsqlを実行して、この日記の通算アクセス数上位20位までの記事を調べました。アクセスログのURLを記事名に解決しています。
select
@rownum := @rownum + 1 as no,
title,
count
from
(select
b.title,
count(*) as count
from
access_log a,
articles b
where
a.url = b.url
group by
title
order by
2 desc) c,
(select @rownum := 0) d
limit 20;以下がこの日記の通算アクセス数上位20位の記事でした。
+------+------------------------------------------------------------------------------------------------------------------+-------+
| no | title | count |
+------+------------------------------------------------------------------------------------------------------------------+-------+
| 1 | viからeclipseに戻れない10の理由 | 500 |
| 2 | eclipseの本当のデメリットとそれを克服するための代替案 | 322 |
| 3 | PuTTYユーザマニュアルの日本語訳 | 244 |
| 4 | ActiveObjectsでたったの2時間で作れる3次元グラフ | 234 |
| 5 | (PuTTYユーザマニュアルの日本語訳) 5.2 PSCPの使い方 - 5.2 PSCP Usage | 234 |
| 6 | (PuTTYユーザマニュアル) 7.2 Plinkを使う - 7.2 Using Plink | 225 |
| 7 | The Java Tutorials -- How to Use Tablesの日本語訳(途中) | 202 |
| 8 | (Tomcat Wiki) ロギングのチュートリアル - Logging Tutorial | 191 |
| 9 | PHPとJavascriptでリアルタイム(?)3Dグラフ | 162 |
| 10 | BigDecimalの仕組みを図解してみた。 | 140 |
| 11 | org.apache.commons.beanutils | 139 |
| 12 | ActiveObjectsを使ってみた(4) - データベースの情報を取得する。 | 130 |
| 13 | ActiveObjectsを使ってみた(1) | 123 |
| 14 | BigDecimalの仕組みを利用してみた。 | 117 |
| 15 | ActiveObjectsを使ってみた(3) - 一覧に検索機能を追加する | 111 |
| 16 | (PuTTYユーザマニュアル) 7.3 バッチファイルやスクリプトでPlinkを使う - 7.3 Using Plink in batch files and scripts | 111 |
| 17 | Javascriptで3次元グラフ | 108 |
| 18 | ハイパーテキスト転送プロトコル -- HTTP/1.1の目次 (概要付き) | 107 |
| 19 | ActiveObjects関連の日記の目次 | 99 |
| 20 | org.apache.commons.beanutils.PropertyUtils | 94 |
+------+------------------------------------------------------------------------------------------------------------------+-------+
記事一覧のHTMLソースでは、
<td class="titlecell"><a href="http://d.hatena.ne.jp/nattou_curry_2/20081207/1228654046"> The Java Tutorials -- How to Use Tablesの日本語訳(途中)</a></td>
そこで、
置換前「^<td class="titlecell">」 → 置換後「#<td class="titlecell">」
印のない行を削除する。
印のない行は無関係なので、すべて削除します。削除するために、エディタで以下の置換を行います。
置換前「^[^#].*\r\n」 → 置換後「」
この結果、以下のような行だけが残ります。
#<td class="titlecell"><a href="http://d.hatena.ne.jp/nattou_curry_2/20081214/1229247877"> org.apache.commons.beanutils.PropertyUtils</a></td> #<td class="titlecell"><a href="http://d.hatena.ne.jp/nattou_curry_2/20081213/1229179629"> org.apache.commons.beanutils</a></td> #<td class="titlecell"><a href="http://d.hatena.ne.jp/nattou_curry_2/20081213/1229176493"> Commons BeanUtils 1.8.0 APIのパッケージ一覧</a></td>
記事名とURLの組み合わせのinsert文を作る。
ここで、記事の名前とURLの組み合わせのテーブルに入れるためのinsert文を作ります。エディタで以下の置換を行います。
置換前「^#<td class="titlecell"><a href="([^"]*)"> *([^<]*)</a></td> \r\n」 → 置換後「insert into articles values('\1','\2');\r\n」これにより、以下のようなinsert文ができます。
insert into articles values('http://d.hatena.ne.jp/nattou_curry_2/20081214/1229247877','org.apache.commons.beanutils.PropertyUtils');
insert into articles values('http://d.hatena.ne.jp/nattou_curry_2/20081213/1229179629','org.apache.commons.beanutils');
insert into articles values('http://d.hatena.ne.jp/nattou_curry_2/20081213/1229176493','Commons BeanUtils 1.8.0 APIのパッケージ一覧');このinsert文を実行すれば、今回の目的は作業です。
実際に、アクセスログのURLを記事名に解決してみた。
以下のsqlを実行して、この日記の通算アクセス数上位20位までの記事を調べました。アクセスログのURLを記事名に解決しています。
select
@rownum := @rownum + 1 as no,
title,
count
from
(select
b.title,
count(*) as count
from
access_log a,
articles b
where
a.url = b.url
group by
title
order by
2 desc) c,
(select @rownum := 0) d
limit 20;以下がこの日記の通算アクセス数上位20位の記事でした。
+------+------------------------------------------------------------------------------------------------------------------+-------+ | no | title | count | +------+------------------------------------------------------------------------------------------------------------------+-------+ | 1 | viからeclipseに戻れない10の理由 | 500 | | 2 | eclipseの本当のデメリットとそれを克服するための代替案 | 322 | | 3 | PuTTYユーザマニュアルの日本語訳 | 244 | | 4 | ActiveObjectsでたったの2時間で作れる3次元グラフ | 234 | | 5 | (PuTTYユーザマニュアルの日本語訳) 5.2 PSCPの使い方 - 5.2 PSCP Usage | 234 | | 6 | (PuTTYユーザマニュアル) 7.2 Plinkを使う - 7.2 Using Plink | 225 | | 7 | The Java Tutorials -- How to Use Tablesの日本語訳(途中) | 202 | | 8 | (Tomcat Wiki) ロギングのチュートリアル - Logging Tutorial | 191 | | 9 | PHPとJavascriptでリアルタイム(?)3Dグラフ | 162 | | 10 | BigDecimalの仕組みを図解してみた。 | 140 | | 11 | org.apache.commons.beanutils | 139 | | 12 | ActiveObjectsを使ってみた(4) - データベースの情報を取得する。 | 130 | | 13 | ActiveObjectsを使ってみた(1) | 123 | | 14 | BigDecimalの仕組みを利用してみた。 | 117 | | 15 | ActiveObjectsを使ってみた(3) - 一覧に検索機能を追加する | 111 | | 16 | (PuTTYユーザマニュアル) 7.3 バッチファイルやスクリプトでPlinkを使う - 7.3 Using Plink in batch files and scripts | 111 | | 17 | Javascriptで3次元グラフ | 108 | | 18 | ハイパーテキスト転送プロトコル -- HTTP/1.1の目次 (概要付き) | 107 | | 19 | ActiveObjects関連の日記の目次 | 99 | | 20 | org.apache.commons.beanutils.PropertyUtils | 94 | +------+------------------------------------------------------------------------------------------------------------------+-------+