これも2つのタグ構造で共通の <a href="URL">TITLE</a> という形なので、一つの正規表現を追加して拾えます。↓
# select
ary[1],
r[1] as url,
r[2] as title
from html_raw_tmp,
regexp_matches(line,'(\d{4}/\d{2}/\d{2})(.+?)(li|div)>', 'g') as ary,
regexp_matches(ary[2], ']*>([^<]+)') as r;
+------------+------------------------------------------+-------------------------------------------------------------+
| ary | url | title |
+------------+------------------------------------------+-------------------------------------------------------------+
| 2014-09-16 | kenpg.seesaa.net/article/405510842.html | 続きます → http://kenpg2.seesaa.net/ |
| 2014-09-14 | kenpg.seesaa.net/article/405425703.html | 指数表記で 3.00e+08 とかする方法 |
| 2014-09-13 | kenpg.seesaa.net/article/405374546.html | 例のメッセージなしで Rgui を起動 |
| 2014-09-12 | kenpg.seesaa.net/article/405030672.html | 列なしテーブルの使いみち |
| 2014-09-11 | kenpg.seesaa.net/article/404550720.html | VBS + BAT でクエリの試行錯誤 |
...
(3)記事のカテゴリ/タグを抽出
これは位置からして違うので、別々の正規表現で対応せざるを得ません。一つ目はコロンに狭まれたカテゴリ(常に一つ)。↓
# select
ary[1],
substring(ary[2], ': ([^ ]+) :')
from html_raw_tmp,
regexp_matches(line,'(\d{4}/\d{2}/\d{2})(.+?)(li|div)>', 'g') as ary;
+------------+--------------------+
| ary | substring |
+------------+--------------------+
| 2014-09-16 | このブログについて |
| 2014-09-14 | R |
| 2014-09-13 | R |
| 2014-09-12 | PostgreSQL |
| 2014-09-11 | PostgreSQL |
| 2014-09-10 | 音楽、PC、他 |
| 2014-09-09 | このブログについて |
| 2014-09-08 | このブログについて |
| 2014-09-07 | R |
| 2014-09-06 | R |
| 2014-09-05 | PostGIS |
| 2014-09-04 | PostGIS |
| 2015-05-23 | |
| 2015-04-19 | |
...
カテゴリが見つからなかった行は、リニューアル後でタグが複数あり。それを全て拾ってカンマでつなぎます。↓ regexp_matchesが配列を返すので、string_agg関数で連結する形。
# select
ary[1],
(select string_agg(r[1], ', ')
from regexp_matches(ary[2], '>([^<]+)', 'g') as r)
from html_raw_tmp,
regexp_matches(line,'(\d{4}/\d{2}/\d{2})(.+?)(li|div)>', 'g') as ary;
+------------+----------------------------------+
| ary | string_agg |
+------------+----------------------------------+
| 2014-09-16 | |
| 2014-09-14 | |
| 2014-09-13 | |
...
| 2014-09-05 | |
| 2014-09-04 | |
| 2015-05-23 | info |
| 2015-04-19 | info |
| 2015-04-18 | WebSocket, Python, PostgreSQL |
| 2015-04-17 | WebSocket, Python |
| 2015-04-16 | Python, Firefox, WebSocket |
| 2015-04-15 | BAT, Windows, PC |
| 2015-04-14 | PC, Windows |
| 2015-04-13 | Python, PostgreSQL |
| 2015-04-12 | BAT, Python, WSH |
| 2015-04-11 | Python, 実行環境, BAT |
| 2015-04-10 | PostgreSQL, 実行環境, Python |
| 2015-04-09 | Python, 実行環境, PostgreSQL |
| 2015-04-08 | Python, EXCEL, xml |
(4)全体をまとめてクエリ
(1)~(3)を一つにまとめ、where句で不要な行(記事以外へのリンク等)を削り、日付降順にすれば出来上がり。実際クエリすると ↓ こうなります。
# select distinct
ary[1] as ymd,
r[1] as url,
r[2] as title,
coalesce(substring(str, ': ([^ ]+) :'),
(select string_agg(r[1], ', ')
from regexp_matches(str, '>([^<]+)', 'g') as r)
) as tag
from html_raw_tmp,
regexp_matches(line,'(\d{4}/\d{2}/\d{2})(.+?)(li|div)>', 'g') as ary,
cast(ary[2] as text) as str,
regexp_matches(str, ']*>([^<]+)') as r
where r is not null
order by 1 desc;
+------------+------------------------------------------+-------------------------------------------------------------+----------------------------------+
| ymd | url | title | tag |
+------------+------------------------------------------+-------------------------------------------------------------+----------------------------------+
| 2015-05-23 | kenpg2.seesaa.net/article/419462759.html | http://kenpg.bitbucket.org/ に移転しました | info |
| 2015-04-19 | kenpg2.seesaa.net/article/417500233.html | 移転の予告(来月から) | info |
| 2015-04-18 | kenpg2.seesaa.net/article/417234379.html | pywebsocket & PostgreSQL の Web クライアント(暫定) | WebSocket, Python, PostgreSQL |
| 2015-04-17 | kenpg2.seesaa.net/article/417146780.html | pywebsocket でブラウザ ⇒ サーバに処理中止を指示(暫定) | WebSocket, Python |
| 2015-04-16 | kenpg2.seesaa.net/article/417129799.html | ポータブルな Firefox & Python で WebSocket 通信テスト | Python, Firefox, WebSocket |
| 2015-04-15 | kenpg2.seesaa.net/article/417084348.html | Windows コマンドプロンプトをポータブルな Console に変更 | BAT, Windows, PC |
| 2015-04-14 | kenpg2.seesaa.net/article/417041397.html | ffmpeg でパソコン操作画面の録画 & VIDEO タグのテスト | PC, Windows |
| 2015-04-13 | kenpg2.seesaa.net/article/416929125.html | psycopg2 でリアルタイムに PostgreSQL のメッセージ取得 | Python, PostgreSQL |
| 2015-04-12 | kenpg2.seesaa.net/article/416918807.html | Python コマンドプロンプト非表示で Web サーバ起動 | BAT, Python, WSH |
| 2015-04-11 | kenpg2.seesaa.net/article/416873383.html | Python バッチファイル一つで Web サーバの起動も再起動もする | Python, 実行環境, BAT |
| 2015-04-10 | kenpg2.seesaa.net/article/416824303.html | Python & PostgreSQL のポータブルな Web クライアント(2) | PostgreSQL, 実行環境, Python |
| 2015-04-09 | kenpg2.seesaa.net/article/416763140.html | Python & PostgreSQL のポータブルな Web クライアント(1) | Python, 実行環境, PostgreSQL |
| 2015-04-08 | kenpg2.seesaa.net/article/416668183.html | Python 2.7 + win32com で Excel ファイルを XML に変換 | Python, EXCEL, xml |
...
(693 rows)
クエリ結果を確認したらTSV出力。psqlの表示設定をTSV用にし、\gコマンド+出力先ファイルパスを打ちます。別のクエリを実行してなければ、何回でも\gで同じクエリを呼び出せて便利。
# \f '\t' \\ \t on \\ \pset format unaligned
# \g r:/tmp/kenpg_seesaa_lists.tsv
-- check result
# \! less r:/tmp/kenpg_seesaa_lists.tsv
2015-05-23 kenpg2.seesaa.net/article/419462759.html http://kenpg.bitbucket.org/ に移転しました info
2015-04-19 kenpg2.seesaa.net/article/417500233.html 移転の予告(来月から) info
2015-04-18 kenpg2.seesaa.net/article/417234379.html pywebsocket & PostgreSQL の Web クライアント(暫定) WebSocket, Python, PostgreSQL
2015-04-17 kenpg2.seesaa.net/article/417146780.html pywebsocket でブラウザ ⇒ サーバに処理中止を指示(暫定) WebSocket, Python
2015-04-16 kenpg2.seesaa.net/article/417129799.html ポータブルな Firefox & Python で WebSocket 通信テスト Python, Firefox, WebSocket
2015-04-15 kenpg2.seesaa.net/article/417084348.html Windows コマンドプロンプトをポータブルな Console に変更 BAT, Windows, PC
2015-04-14 kenpg2.seesaa.net/article/417041397.html ffmpeg でパソコン操作画面の録画 & VIDEO タグのテスト PC, Windows
2015-04-13 kenpg2.seesaa.net/article/416929125.html psycopg2 でリアルタイムに PostgreSQL のメッセージ取得 Python, PostgreSQL
2015-04-12 kenpg2.seesaa.net/article/416918807.html Python コマンドプロンプト非表示で Web サーバ起動 BAT, Python, WSH
2015-04-11 kenpg2.seesaa.net/article/416873383.html Python バッチファイル一つで Web サーバの起動も再起動もする Python, 実行環境, BAT
2015-04-10 kenpg2.seesaa.net/article/416824303.html Python & PostgreSQL のポータブルな Web クライアント(2) PostgreSQL, 実行環境, Python
2015-04-09 kenpg2.seesaa.net/article/416763140.html Python & PostgreSQL のポータブルな Web クライアント(1) Python, 実行環境, PostgreSQL
2015-04-08 kenpg2.seesaa.net/article/416668183.html Python 2.7 + win32com で Excel ファイルを XML に変換 Python, EXCEL, xml
こうして出力したTSV