ユーザーエージェントの実行結果

SQLの説明

ユーザーレビューの参照ログから、ユーザーエージェントをカウント。
ビッグタイトル発売に伴うアクセス増大時にコストオーバーするのは目に見えているので、１日以内のデータに限定します。
素のIE8がトップシェア。正式版がリリースされたIE9も増えてきていますね。IE9はなかなか良い感じです。
火狐ユーザーは早くも5.0へ移行した模様。6週ペースでメジャーバージョンアップ予定というハイペースなリリースサイクルはやはり不安が残るところです。

検索結果

実行したSQL

      select
coalesce(
       coalesce(substring(agent from 'EmptyBrowser'),'')
||' '||coalesce(substring(agent from 'ICC-Crawler'),'')
||' '||coalesce(substring(agent from 'Yahoo! Slurp'),'')
||' '||coalesce(substring(agent from 'Nintendo Wii'),'')
||' '||coalesce(substring(agent from 'Nitro'),'')
||' '||coalesce(substring(agent from 'PSP'),'')
||' '||coalesce(substring(agent from 'PS2'),'')
||' '||coalesce(substring(agent from 'PLAYSTATION 3'),'')
||' '||coalesce(substring(agent from 'iPhone'),'')
||' '||coalesce(substring(agent from 'BlackBerry'),'')
||' '||coalesce(substring(agent from 'Android [0-9]{1,2}.[0-9]{1,2}'),'')
||' '||coalesce(substring(agent from 'DoCoMo [0-9]{1,2}.[0-9]{1,2}'),'')
||' '||coalesce(substring(agent from 'KDDI'),'')
||' '||coalesce(substring(agent from 'DoCoMo/[0-9]{1,2}.[0-9]{1,2}'),'')
||' '||coalesce(substring(agent from 'SoftBank/[0-9]{1,2}.[0-9]{1,2}'),'')
||' '||coalesce(substring(agent from 'Chrome/[0-9]{1,2}.[0-9]{1,2}'),'')
||' '||coalesce(substring(agent from 'Lunascape [0-9]{1,2}.[0-9]{1,2}'),'')
||' '||coalesce(substring(agent from 'Firefox/[0-9]{1,2}.[0-9]{1,2}'),'')
||' '||coalesce(substring(agent from 'Safari/[0-9]{1,3}.[0-9]{1,2}'),'')
||' '||coalesce(substring(agent from 'Opera [0-9]{1,2}.[0-9]{1,2}'),'')
||' '||coalesce(substring(agent from 'Sleipnir/[0-9]{1,2}.[0-9]{1,2}'),'')
||' '||coalesce(substring(agent from 'Konqueror/[0-9]{1,2}.[0-9]{1,2}'),'')
||' '||coalesce(substring(agent from 'DreamPassport/[0-9]{1,2}'),'')
||' '||coalesce(substring(agent from 'MSIE [0-9]{1,2}.[0-9]'),'')
, '-unknown-') as ua 
, count(distinct ip) as ip 
, count(*) from userreview_display_log 
where agent ~ '.+' and timestamp >= current_date - '1 days'::interval 
group by 
       coalesce(substring(agent from 'EmptyBrowser'),'')
||' '||coalesce(substring(agent from 'ICC-Crawler'),'')
||' '||coalesce(substring(agent from 'Yahoo! Slurp'),'')
||' '||coalesce(substring(agent from 'Nintendo Wii'),'')
||' '||coalesce(substring(agent from 'Nitro'),'')
||' '||coalesce(substring(agent from 'PSP'),'')
||' '||coalesce(substring(agent from 'PS2'),'')
||' '||coalesce(substring(agent from 'PLAYSTATION 3'),'')
||' '||coalesce(substring(agent from 'iPhone'),'')
||' '||coalesce(substring(agent from 'BlackBerry'),'')
||' '||coalesce(substring(agent from 'Android [0-9]{1,2}.[0-9]{1,2}'),'')
||' '||coalesce(substring(agent from 'DoCoMo [0-9]{1,2}.[0-9]{1,2}'),'')
||' '||coalesce(substring(agent from 'KDDI'),'')
||' '||coalesce(substring(agent from 'DoCoMo/[0-9]{1,2}.[0-9]{1,2}'),'')
||' '||coalesce(substring(agent from 'SoftBank/[0-9]{1,2}.[0-9]{1,2}'),'')
||' '||coalesce(substring(agent from 'Chrome/[0-9]{1,2}.[0-9]{1,2}'),'')
||' '||coalesce(substring(agent from 'Lunascape [0-9]{1,2}.[0-9]{1,2}'),'')
||' '||coalesce(substring(agent from 'Firefox/[0-9]{1,2}.[0-9]{1,2}'),'')
||' '||coalesce(substring(agent from 'Safari/[0-9]{1,3}.[0-9]{1,2}'),'')
||' '||coalesce(substring(agent from 'Opera [0-9]{1,2}.[0-9]{1,2}'),'')
||' '||coalesce(substring(agent from 'Sleipnir/[0-9]{1,2}.[0-9]{1,2}'),'')
||' '||coalesce(substring(agent from 'Konqueror/[0-9]{1,2}.[0-9]{1,2}'),'')
||' '||coalesce(substring(agent from 'DreamPassport/[0-9]{1,2}'),'')
||' '||coalesce(substring(agent from 'MSIE [0-9]{1,2}.[0-9]'),'')
order by ip desc