主页 / dba / 问题 / 89301
Accepted
Rob D
Asked: 2015-01-15 10:02:18 +0800 CST

如何正确聚合？

  • 772

我有一个包含超过 600 万条记录的日志表。我想要一个查询，向我显示src去往同一目的地/端口的所有事件。

我试过这个：

SELECT src, dst, dstport, COUNT(src) AS Hits 
FROM logs  
GROUP BY src 
ORDER BY Hits DESC;

不确定这个查询是否给了我我想要的。此外，排除特定端口的最佳方法是什么？说dstport = 53？

我希望每次每个 src 到达相同的 dst 和 dstport 时都显示，同时保持对事件的计数。说 src 10.110.0.10 到达 dst 10.2.9.124:53 2,345,568 次 & 也到达 192.168.9.18:80 174 次；那么结果将如下所示：示例：

+-----------------+-----------------+---------+---------+ | src | dst | dstport | Hist | +-----------------+-----------------+---------+---------+ | 10.110.0.10 | 10.2.9.124 | 53 | 2345568 | +-----------------+-----------------+---------+---------+ | 10.110.0.10 | 192.168.9.18 | 80 | 174 | +-----------------+-----------------+---------+---------+

但是通过上面的查询我得到了这个结果：

+-----------------+-----------------+---------+---------+ | src | dst | dstport | Hist | +-----------------+-----------------+---------+---------+ | 10.110.0.10 | 10.2.9.124 | 53 | 1443780 | | 10.110.0.10 | 192.168.9.124 | 53 | 1402210 | | 10.192.31.23 | 10.192.1.120 | 8082 | 319507 | | 10.192.31.19 | 10.192.1.186 | 8081 | 319203 | | 192.168.31.131 | 192.168.31.130 | 80 | 290818 | +-----------------+-----------------+---------+---------+

mysql mysql-5

1 个回答

  1. Best Answer

    要通过聚合查看所有内容，您可以这样做

    SELECT src,CONCAT(dst,':',dstport) dsp_port,COUNT(1) hits
FROM logs
GROUP BY src,dsp_port;

    查看小计

    SELECT
    IF
    (
        ISNULL(src)=1,
        'Total',
        IF
        (
            ISNULL(dst_port)=1,
            CONCAT(src,' Total'),
            CONCAT(src,' ',dst_port)
        )
    ) Statistic,hits
FROM
(
    SELECT src,CONCAT(dst,':',dstport) dst_port,COUNT(1) hits
    FROM logs
    GROUP BY src,dst_port
    WITH ROLLUP
) A;

    使用您的示例数据

    mysql> DROP DATABASE IF EXISTS rob_d;
Query OK, 1 row affected (0.02 sec)

mysql> CREATE DATABASE rob_d;
Query OK, 1 row affected (0.00 sec)

mysql> USE rob_d
Database changed
mysql> CREATE TABLE logs
    -> (
    ->     src CHAR(15) NOT NULL,
    ->     dst CHAR(15) NOT NULL,
    ->     dstport  INT NOT NULL
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO logs (src,dst,dstport) VALUES
    -> ('10.110.0.10',   '10.2.9.124'    ,53),
    -> ('10.110.0.10',   '192.168.9.124' ,53),
    -> ('10.192.31.23',  '10.192.1.120'  ,8082),
    -> ('10.192.31.19',  '10.192.1.186'  ,8081),
    -> ('192.168.31.131','192.168.31.130',80);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM logs;
+----------------+----------------+---------+
| src            | dst            | dstport |
+----------------+----------------+---------+
| 10.110.0.10    | 10.2.9.124     |      53 |
| 10.110.0.10    | 192.168.9.124  |      53 |
| 10.192.31.23   | 10.192.1.120   |    8082 |
| 10.192.31.19   | 10.192.1.186   |    8081 |
| 192.168.31.131 | 192.168.31.130 |      80 |
+----------------+----------------+---------+
5 rows in set (0.00 sec)

    这是查询的输出

    mysql> SELECT
    ->     IF
    ->     (
    ->         ISNULL(src)=1,
    ->         'Total',
    ->         IF
    ->         (
    ->             ISNULL(dst_port)=1,
    ->             CONCAT(src,' Total'),
    ->             CONCAT(src,' ',dst_port)
    ->         )
    ->     ) Statistic,hits
    -> FROM
    -> (
    ->     SELECT src,CONCAT(dst,':',dstport) dst_port,COUNT(1) hits
    ->     FROM logs
    ->     GROUP BY src,dst_port
    ->     WITH ROLLUP
    -> ) A;
+----------------------------------+------+
| Statistic                        | hits |
+----------------------------------+------+
| 10.110.0.10 10.2.9.124:53        |    1 |
| 10.110.0.10 192.168.9.124:53     |    1 |
| 10.110.0.10 Total                |    2 |
| 10.192.31.19 10.192.1.186:8081   |    1 |
| 10.192.31.19 Total               |    1 |
| 10.192.31.23 10.192.1.120:8082   |    1 |
| 10.192.31.23 Total               |    1 |
| 192.168.31.131 192.168.31.130:80 |    1 |
| 192.168.31.131 Total             |    1 |
| Total                            |    5 |
+----------------------------------+------+
10 rows in set (0.00 sec)

mysql>
    • 0

相关问题