Блог

Главная - Вопросы по программированию - Как выбрать данные подсчета по группам с помощью регулярных выражений в MYSQL?

Как выбрать данные подсчета по группам с помощью регулярных выражений в MYSQL?

#mysql #sql

#mysql #sql

Вопрос:

У меня есть таблица, в которой сохраняется информация о геолокации пользователя. Как я могу подсчитать количество пользователей с одним и тем же устройством, создав регулярное выражение?

Запрос

 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

Комментарии:

1. И каково правило для извлечения пользовательского агента? Это не очевидно.

Ответ №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

Ответ №2:

Я придумал очень уродливое PHP-решение, используя array_count_values и помещая данные в массив. Это работает для того, что я пытаюсь сделать. Для чего это стоит, я извлекаю user agent из Navigator DOM. Обычно он возвращает строку, похожую на эту

Mozilla / 5.0 (Linux; Android 7.0; Сборка SM-G892A / NRD90M; wv) AppleWebKit / 537.36 (KHTML, как Gecko) Версия / 4.0 Chrome / 67.0.3396.87 Mobile 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 )