PostgreSQLにて、DBに保存されたUserAgent情報からSQLでOSとブラウザを簡易的に判別/集計する。
- ロボットの事は考慮していない。
- ChromeのUserAgentには、「Safari」という文字列が含まれるので、Safariより先に判定する。
- 「MSIE」は「IE 10」まで、「IE 11」は「MSIE」が削除されて「Trident」と「rv:11.0」と表示される。
- 互換表示の場合、IE10:MSIE 7.0 と Trident/6.0 のように出てくるので、Tridentだけ判定して、「rv」があれば「rv」までを抽出。
- Tridentとrevの間に、「Touch」「MALNJS」「MAFSJS」などが入っているものがあるが、そのまま抽出。
- Mac OS Xのバージョンの表示は、FireFoxの場合「10.11」、その他の場合「10_11」と表記が異なる。
- 期限切れの「Windows NT 5.1」=「Windows XP」が含まれているが、テストなので気にしない。
例えば、logテーブルのuser_agentというカラムにUserAgent情報が保存されている場合に、OSとブラウザの種類・バージョンをグループ化して件数を調べる。
db_name=> SELECT COUNT(*), CASE WHEN user_agent LIKE '%Macintosh%' THEN SUBSTRING(user_agent from E'\(Mac OS[^)|;]*\)') WHEN user_agent LIKE '%iPad%' THEN SUBSTRING(user_agent from E'\(iPad[^)]*\)') WHEN user_agent LIKE '%iPhone%' THEN SUBSTRING(user_agent from E'\(iPhone[^)]*\)') WHEN user_agent LIKE '%Linux%' THEN SUBSTRING(user_agent from E'\(X11[^)]*\)') WHEN user_agent LIKE '%Windows NT%' THEN SUBSTRING(user_agent from E'\(Windows NT [0-9.]+\)[)|;].*') ELSE user_agent END AS os, CASE WHEN user_agent LIKE '%Edge%' THEN SUBSTRING(user_agent from E'\(Edge/[0-9.]+\)') WHEN user_agent LIKE '%Sleipnir%' THEN SUBSTRING(user_agent from E'\(Sleipnir/[0-9.]+\)') WHEN user_agent LIKE '%OPR%' THEN SUBSTRING(user_agent from E'\(OPR/[0-9.]+\)') WHEN user_agent LIKE '%Vivaldi%' THEN SUBSTRING(user_agent from E'\(Vivaldi/[0-9.]+\)') WHEN user_agent LIKE '%Firefox%' THEN SUBSTRING(user_agent from E'\(Firefox/[0-9.]+\)') WHEN user_agent LIKE '%Chrome%' THEN SUBSTRING(user_agent from E'\(Chrome/[0-9.]+\)') WHEN user_agent LIKE '%Trident%' THEN SUBSTRING(user_agent from E'\(Trident/[0-9.]+;[^)]*\)') WHEN user_agent LIKE '%MSIE%' THEN SUBSTRING(user_agent from E'\(MSIE [0-9.]+\)') WHEN user_agent LIKE '%Safari%' THEN SUBSTRING(user_agent from E'\(Version/[0-9.]+ .*Safari/[0-9.]+\)') ELSE user_agent END AS browser FROM log GROUP BY os, browser ORDER BY os, browser;
次のような結果になる。
count | os | browser -------+-------------------------------------+------------------------------------- 2 | Mac OS X 10.11 | Firefox/46.0 1 | Mac OS X 10_11_5 | Chrome/50.0.2661.102 10 | Mac OS X 10_11_5 | Version/9.1.1 Safari/601.6.17 9 | Windows NT 10.0 | Chrome/50.0.2661.102 19 | Windows NT 10.0 | Edge/13.10586 7 | Windows NT 10.0 | Firefox/46.0 1 | Windows NT 10.0 | Sleipnir/6.1.11 9 | Windows NT 10.0 | Trident/7.0; rv:11.0 1 | Windows NT 5.1 | Chrome/49.0.2623.112 1 | Windows NT 5.1 | Firefox/46.0 2 | Windows NT 6.0 | Chrome/49.0.2623.112 4 | Windows NT 6.1 | Chrome/52.0.2743.10 17 | Windows NT 6.1 | Firefox/46.0 1 | Windows NT 6.1 | OPR/37.0.2178.43 1 | Windows NT 6.1 | Trident/7.0; MAFSJS; rv:11.0 22 | Windows NT 6.1 | Trident/7.0; rv:11.0 1 | Windows NT 6.1 | Vivaldi/1.1.453.59 2 | Windows NT 6.3 | Chrome/51.0.2704.63 5 | Windows NT 6.3 | Firefox/46.0 1 | Windows NT 6.3 | OPR/37.0.2178.43 1 | Windows NT 6.3 | Trident/7.0; MALNJS; rv:11.0 3 | Windows NT 6.3 | Trident/7.0; Touch; rv:11.0 4 | Windows NT 6.3 | Trident/7.0; rv:11.0 1 | X11; Linux x86_64; rv:31.0 | Firefox/31.0 1 | X11; Ubuntu; Linux x86_64; rv:46.0 | Firefox/46.0