UserAgent情報からSQLでOSとブラウザを簡易的に判別/集計する

PostgreSQL SQL ブラウザ

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