mysql - 按浏览器对用户代理进行分组

Question

我有一个表格，每一行都包含有关用户页面加载的数据。其中一个字段是该命中的用户代理。如何按浏览器对结果进行分组？我可以按普通用户代理分组:

SELECT useragent, COUNT(useragent) AS c
FROM hitstable
GROUP BY useragent
ORDER BY c DESC

但我想按浏览器分组，而不考虑版本或其他更改的数据。我希望它输出一个列表，列出浏览器的使用频率。

我该怎么做？

Answer 1

我发现使用 MySQL CASE 来解析浏览器、操作系统和位架构很有帮助。数据($_SERVER['HTTP_USER_AGENT'])通过 PHP 插入到 MySQL 表中。也许您会发现以下代码很有帮助。它还根据 UserAgent 呈现一些 Font Awesome 图标。

下面的 SELECT 按浏览器别名分组。当然，操作系统和 BitArchitecture 也可以这样做。

SELECT 
    CASE
        WHEN `UserAgent` LIKE '%Mac%OS%' THEN '<i class="fa fa-apple"></i> Mac OS X'
        WHEN `UserAgent` LIKE '%iPad%' THEN '<i class="fa fa-apple"></i> iPad'
        WHEN `UserAgent` LIKE '%iPod%' THEN '<i class="fa fa-apple"></i> iPod'
        WHEN `UserAgent` LIKE '%iPhone%' THEN '<i class="fa fa-apple"></i> iPhone'
        WHEN `UserAgent` LIKE '%imac%' THEN '<i class="fa fa-apple"></i> mac'
        WHEN `UserAgent` LIKE '%android%' THEN '<i class="fa fa-android"></i> android'
        WHEN `UserAgent` LIKE '%linux%' THEN '<i class="fa fa-linux"></i> linux'
        WHEN `UserAgent` LIKE '%Nokia%' THEN 'Nokia'
        WHEN `UserAgent` LIKE '%BlackBerry%' THEN 'BlackBerry'
        WHEN `UserAgent` LIKE '%win%' THEN
            CASE
                WHEN `UserAgent` LIKE '%NT 6.2%' THEN '<i class="fa fa-windows"></i> Windows 8'
                WHEN `UserAgent` LIKE '%NT 6.3%' THEN '<i class="fa fa-windows"></i> Windows 8.1'
                WHEN `UserAgent` LIKE '%NT 6.1%' THEN '<i class="fa fa-windows"></i> Windows 7'
                WHEN `UserAgent` LIKE '%NT 6.0%' THEN '<i class="fa fa-windows"></i> Windows Vista'
                WHEN `UserAgent` LIKE '%NT 5.1%' THEN '<i class="fa fa-windows"></i> Windows XP'
                WHEN `UserAgent` LIKE '%NT 5.0%' THEN '<i class="fa fa-windows"></i> Windows 2000'
                ELSE '<i class="fa fa-windows"></i> Windows'
            END      
        WHEN `UserAgent` LIKE '%FreeBSD%' THEN 'FreeBSD'
        WHEN `UserAgent` LIKE '%OpenBSD%' THEN 'OpenBSD'
        WHEN `UserAgent` LIKE '%NetBSD%' THEN 'NetBSD'
        WHEN `UserAgent` LIKE '%OpenSolaris%' THEN 'OpenSolaris'
        WHEN `UserAgent` LIKE '%SunOS%' THEN 'SunOS'
        WHEN `UserAgent` LIKE '%OS/2%' THEN 'OS/2'
        WHEN `UserAgent` LIKE '%BeOS%' THEN 'BeOS'
        ELSE 'Unknown'
    END AS `OS`,
    CASE
        WHEN `UserAgent` LIKE '%edge%'THEN '<i class="fa fa-edge"></i> Edge'
        WHEN `UserAgent` LIKE '%MSIE%' THEN '<i class="fa fa-internet-explorer"></i> Internet Explorer'
        WHEN `UserAgent` LIKE '%Firefox%' THEN '<i class="fa fa-firefox"></i> Mozilla Firefox'
        WHEN `UserAgent` LIKE '%Chrome%' THEN '<i class="fa fa-chrome"></i> Google Chrome'
        WHEN `UserAgent` LIKE '%Safari%' THEN '<i class="fa fa-safari"></i> Apple Safari'
        WHEN `UserAgent` LIKE '%Opera%' THEN '<i class="fa fa-opera"></i> Opera' 
        WHEN `UserAgent` LIKE '%Outlook%' THEN 'Outlook' 
        ELSE 'Unknown'
    END AS `Browser`,
    CASE
        WHEN `UserAgent` LIKE '%WOW64%' THEN '64 bit'
        WHEN `UserAgent` LIKE '%x64%' THEN '64 bit'
        ELSE '32 bit'
    END AS `BitArchitecture`
FROM `Logs`
GROUP BY `Browser`;