组\u concat中的mysql计数

gstyhher 于 2021-06-17 发布在 Mysql

关注(0)|答案(2)|浏览(356)

我已经像这样创建了mysql表。

CREATE TABLE `log_info` (
 `log_id` int(11) NOT NULL AUTO_INCREMENT,
 `log_datetime` datetime NOT NULL,
 `log_attacker_ip` int(11) NOT NULL,
 `log_event` varchar(250) NOT NULL,
 `log_service_port` varchar(10) NOT NULL,
 `log_target_ip` int(11) NOT NULL,
 `log_severity` varchar(3) NOT NULL,
 PRIMARY KEY (`log_id`)
) ENGINE=InnoDB AUTO_INCREMENT=113 DEFAULT CHARSET=latin1

具有以下值：

INSERT INTO `log_info` (`log_id`, `log_datetime`, `log_attacker_ip`, `log_event`, `log_service_port`, `log_target_ip`, `log_severity`) VALUES
(1, '2018-11-13 00:16:45', 16843009, 'Traffic forward message', '80', 0, '5'),
(2, '2018-11-13 00:17:21', 16843009, 'Traffic forward message', '80', 0, '5'),
(3, '2018-11-13 00:17:24', 16843009, 'Traffic forward message', '80', 0, '5'),
(4, '2018-11-13 00:17:27', 16843009, 'Traffic forward message', '80', 0, '5'),
(5, '2018-11-13 00:17:30', 16843009, 'Traffic forward message', '80', 0, '5'),
(6, '2018-11-13 00:17:32', 16843009, 'Traffic forward message', '80', 0, '2'),
(7, '2018-11-13 00:17:34', 16843009, 'Traffic forward message', '80', 0, '5'),
(8, '2018-11-13 00:17:36', 16843009, 'Traffic forward message', '80', 0, '5'),
(9, '2018-11-13 00:17:39', 16843009, 'Traffic forward message', '80', 0, '1'),
(10, '2018-11-13 00:17:41', 16843009, 'Traffic forward message', '80', 0, '5'),
(11, '2018-11-13 00:17:44', 16843009, 'Traffic forward message', '80', 0, '1'),
(12, '2018-11-13 00:17:46', 16843009, 'Traffic forward message', '80', 0, '5'),
(13, '2018-11-13 00:17:48', 16843009, 'Traffic forward message', '80', 0, '4'),
(14, '2018-11-13 00:17:50', 16843009, 'Traffic forward message', '80', 0, '5'),
(15, '2018-11-13 00:17:53', 16843009, 'Traffic forward message', '80', 0, '5'),
(16, '2018-11-13 00:17:55', 16843009, 'Traffic forward message', '80', 0, '5'),
(17, '2018-11-13 00:17:57', 16843009, 'Traffic forward message', '80', 0, '5'),
(18, '2018-11-13 00:17:59', 16843009, 'ICMP', '80', 0, '3'),
(19, '2018-11-13 01:55:07', 16843009, 'ICMP', '80', 0, '5'),
(101, '2018-11-13 22:11:15', 134744072, 'bla', '443', 134744072, '4'),
(102, '2018-11-13 22:48:12', 134744072, 'bla', '443', 134744072, '4'),
(103, '2018-11-13 22:48:15', 134744072, 'bla', '443', 134744072, '4'),
(104, '2018-11-13 22:50:52', 2071690107, 'grrr', '21', 167837997, '2'),
(105, '2018-11-13 22:50:55', 2071690107, 'grrr', '21', 167837997, '2'),
(106, '2018-11-13 22:50:57', 2071690107, 'grrr', '21', 167837997, '2'),
(107, '2018-11-13 22:51:00', 2071690107, 'grrr', '21', 167837997, '2'),
(108, '2018-11-13 22:51:02', 2071690107, 'grrr', '21', 167837997, '2'),
(109, '2018-11-13 22:51:15', 2071690107, 'grrr', '21', 167903493, '2'),
(110, '2018-11-13 22:52:35', 2071690107, 'shhh', '0', 168433945, '1'),
(111, '2018-11-13 22:52:39', 2071690107, 'shhh', '0', 168433945, '1'),
(112, '2018-11-13 23:04:59', 134744072, 'bla', '443', 134744072, '4');

我在使用groupconcat拆分列[occurrences]的count（portno）时遇到了一些问题。
我的问题：

SELECT MAX(log_id) AS 'log_id', MAX(log_datetime) AS 'recent_datetime', INET_NTOA(log_attacker_ip) AS 'attacker_IP', GROUP_CONCAT(DISTINCT log_service_port SEPARATOR ', ') AS 'portno', COUNT(*) AS 'occurences'
FROM log_info
WHERE log_datetime > NOW() - INTERVAL 30 DAY
AND log_datetime <= NOW()
GROUP BY attacker_IP
ORDER BY recent_datetime DESC

以下是我的结果：

+--------+---------------------+-----------------+--------+------------+
| log_id | recent_datetime     | attacker_IP     | portno | occurences |
+--------+---------------------+-----------------+--------+------------+
|    112 | 2018-11-13 23:04:59 | 8.8.8.8         | 443    |          4 |
|    111 | 2018-11-13 22:52:39 | 123.123.123.123 | 0, 21  |          8 |
|     19 | 2018-11-13 01:55:07 | 1.1.1.1         | 80     |         19 |
+--------+---------------------+-----------------+--------+------------+

我需要在列[occurrences]上使用group\u concat，以便它像列[portno]一样分开。

mysql aggregate-functions

来源：https://stackoverflow.com/questions/53463034/mysql-count-within-group-concat

2条答案

按热度按时间

juud5qan1#

我假设您想要一个Map到端口列表的事件列表，例如如果端口列表是 0, 21 你想要什么 2, 6 这是每个端口的出现次数。在这种情况下，可以使用此查询。您需要使用两个级别的分组，首先是 attacker_IP 以及 portno 然后 attacker_IP 要获取此数据：

SELECT MAX(log_id) AS log_id
     , MAX(recent_datetime) AS recent_datetime
     , attacker_IP
     , GROUP_CONCAT(portno) AS ports
     , GROUP_CONCAT(occurrences) AS occurrences
FROM (
    SELECT MAX(log_id) AS log_id
         , MAX(log_datetime) AS recent_datetime
         , INET_NTOA(log_attacker_ip) AS attacker_IP
         , log_service_port AS portno
         , COUNT(*) AS occurrences
    FROM log_info
    WHERE log_datetime > NOW() - INTERVAL 30 DAY
    AND log_datetime <= NOW()
    GROUP BY attacker_IP, portno) AS d
GROUP BY attacker_IP
ORDER BY recent_datetime DESC

输出：

log_id  recent_datetime         attacker_IP         ports   occurrences
112     2018-11-13 23:04:59     8.8.8.8             443     4
111     2018-11-13 22:52:39     123.123.123.123     21,0    6,2
19      2018-11-13 01:55:07     1.1.1.1             80      19

在dbfiddle上演示

赞(0）回复(0）举报 2021-06-18

b5buobof2#

我建议首先使用下一个查询：

SELECT
    MAX(log_id) AS 'log_id',
    MAX(log_datetime) AS 'recent_datetime',
    INET_NTOA(log_attacker_ip) AS 'attacker_IP',
    log_service_port AS 'portno',
    COUNT(*) AS 'occurences'
FROM
    log_info
WHERE
    log_datetime > NOW() - INTERVAL 30 DAY
AND
    log_datetime <= NOW()
GROUP BY
    attacker_IP, portno
ORDER BY
    recent_datetime DESC

前面的查询将显示 (attacker_IP, portno) . 现在，如果仍要连接端口号和引用，可以查询前一个，如下所示：

SELECT
    MAX(ip_port_logs.log_id) AS 'log_id',
    MAX(ip_port_logs.recent_datetime) AS 'recent_datetime',
    ip_port_logs.attacker_IP,
    GROUP_CONCAT(ip_port_logs.portno SEPARATOR ', ') AS 'ports',
    GROUP_CONCAT(ip_port_logs.occurences SEPARATOR ', ') AS 'ports_occurences'
FROM
    ( SELECT
          MAX(log_id) AS 'log_id',
          MAX(log_datetime) AS 'recent_datetime',
          INET_NTOA(log_attacker_ip) AS 'attacker_IP',
          log_service_port AS 'portno',
          COUNT(*) AS 'occurences'
      FROM
          log_info
      WHERE
          log_datetime > NOW() - INTERVAL 30 DAY
      AND
          log_datetime <= NOW()
      GROUP BY
          attacker_IP, portno
      ORDER BY
          recent_datetime DESC ) AS ip_port_logs
GROUP BY
    ip_port_logs.attacker_IP

赞(0）回复(0）举报 2021-06-18

我来回答

组\u concat中的mysql计数

2条答案

相关问题

热门标签

最新问答