如何在MYSQL中使用正则表达式按组选择计数数据

Question

我有一个保存用户地理位置信息的表。如何通过创建正则表达式来计算使用相同设备的用户数量？

查询

SELECT userAgent, COUNT(DISTINCT userAgent) as countVisitor 
FROM geolocation 
WHERE last_update BETWEEN '2020-11-01' AND '2020-12-01' 
GROUP BY userAgent
ORDER BY last_update ASC 

结果

userAgent                                            | countVisitor
Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:82.0) G...| 1
Mozilla/5.0 (iPad; CPU OS 11_0 like Mac OS X) Appl...| 1
Mozilla/5.0 (iPad; CPU OS 11_4 like Mac OS X) Appl...| 1
Mozilla/5.0 (iPhone; CPU iPhone OS 11_0 like Mac O...| 1
Mozilla/5.0 (Linux; Android 7.0; SM-G892A Build/NR...| 1

期望结果

userAgent   | countVisitor
Ubuntu      | 1
iPad        | 2
iPhone      | 1
Android     | 1

Answer 1

嗯。我看不出真正可以使用的模式。您可以使用CASE表达式：

SELECT (CASE WHEN userAgent LIKE '%Ubuntu%' THEN 'Ubuntu'
WHEN userAgent LIKE '%iPad%' THEN 'iPad'
WHEN userAgent LIKE '%iPhone%' THEN 'iPhone'
WHEN userAgent LIKE '%Android%' THEN 'Android'
ELSE 'Other'
end) as platform,COUNT(DISTINCT ipAddress) as countVisitor 
FROM geolocation 
WHERE last_update BETWEEN '2020-11-01' AND '2020-12-01' 
GROUP BY platform
ORDER BY MIN(last_update) ASC 

Answer 2

我提出了一个非常丑陋的PHP解决方案，使用array_count_values并将数据推入数组。它适用于我想要做的事情。为了它的价值，我从Navigator DOM中检索用户代理。它通常返回类似于此的字符串

Mozilla/5.0(Linux；Android 7.0；SM-G892A内部版本/NRD950M；wv(AppleWebKit/537.36(KHTML，类似Gecko(版本/4.0 Chrome/67.0.3396.87移动Safari/537.36

仅凭此字符串，您就可以检索所需的任何信息(设备、浏览器等(。

查询

$uniquevisitorsOS= $con->prepare("SELECT userAgent,
COUNT(DISTINCT userAgent) as countVisitor 
FROM geolocation 
WHERE last_update BETWEEN ? AND ? GROUP BY userAgent 
ORDER BY last_update ASC ");
$uniquevisitorsOS->bind_param("ss",$c_datefrom,$c_dateto);
$uniquevisitorsOS->execute();
$visitorOSResult = $uniquevisitorsOS->get_result();

而

//
//
//create arrays of operating systems
$os_arr = Array();
//
//
while ($visitorOS = $visitorOSResult->fetch_assoc()) {
//
//
//
//
//operating systems
/*
Windows
Mac OS X
Android
Linux
Other
*/
//
//
//
//userAgent col
//
$userAgent = $visitorOS['userAgent'];
//
//
//
//find operating system
if (strpos($userAgent, 'Windows') !== false) { 
//
//windows os
//
$os = 'Windows';
//
//
$os_arr[] = $os;
//
//
}else 
if (strpos($userAgent, 'Mac OS X') !== false) {
//mac os
//
$os = 'Mac OS X';
//
//
$os_arr[] = $os;
//
//
}else 
if (strpos($userAgent, 'Android') !== false) {
//android
//
$os = 'Android';
//
//
$os_arr[] = $os;
//
//
}else 
if (strpos($userAgent, 'Linux') !== false) {
//linux
//
$os = 'Linux';
//
//
$os_arr[] = $os;
//
}else{
//Other
//
$os = 'Other';
//
//
$os_arr[] = $os;
//
//
}
//
//
}
//count values
$osArr = (array_count_values($os_arr));
//
//
//test print
print_r($osArr);

结果

Array ( [Linux] => 2 [Mac OS X] => 4 [Android] => 4 [Windows] => 1 )