mysql - 在 MySQL 行中添加值

Question

我有一个表，用于存储解析的服务器日志:

mysql> SELECT id,date,message_type FROM log_entry LIMIT 10 ;                 
+----+---------------------+--------------+
| id | date                | message_type |
+----+---------------------+--------------+
|  1 | 2015-01-08 18:13:15 |            2 |
|  2 | 2015-01-08 18:13:14 |            1 |
|  3 | 2015-01-08 18:13:12 |            1 |
|  4 | 2015-01-08 18:13:11 |            1 |
|  5 | 2015-01-08 18:13:11 |            1 |
|  6 | 2015-01-08 18:13:11 |            1 |
|  7 | 2015-01-08 18:13:10 |            1 |
|  8 | 2015-01-08 18:13:08 |            1 |
|  9 | 2015-01-08 18:13:07 |            1 |
| 10 | 2015-01-08 18:13:06 |          512 |
+----+---------------------+--------------+

每个日志均按其message_type进行分类。我还可以按小时对它们进行分组:

mysql> select DAY(date) as day, HOUR(date) as hour, message_type, count(message_type) FROM log_entry WHERE date >= CURDATE() - INTERVAL 2 MONTH AND message_type BETWEEN 1 AND 4 GROUP BY day,hour,message_type ORDER BY date ASC LIMIT 3;
+------+------+--------------+---------------------+
| day  | hour | message_type | count(message_type) |
+------+------+--------------+---------------------+
|    8 |   18 |            1 |                 177 |
|    8 |   18 |            2 |                  61 |
|    8 |   18 |            4 |                  14 |
+------+------+--------------+---------------------+

在此示例中，在某个日期的第 8 天和第 18 小时，我有 177 个 message_type 为 1 的条目、61 类型为 2 且 < strong>14 类型为 4。

我想得到每小时的以下计算结果:

type(1) - type(2) - type(4)

在这种情况下会返回

+------+------+--------------+---------------------+
| day  | hour | result                             |
+------+------+--------------+---------------------+
|    8 |   18 | 102                                |
+------+------+--------------+---------------------+

我这样做是因为我想创建一个图表，显示我正在运行的服务器上的每小时事件。类型 1 条目表示有人已连接，类型 2 表示正常断开连接，类型 4 表示超时。

这是在数据库中可行的事情还是应该在应用程序层上执行此操作？

Answer 1

您可以使用 SUM() 函数和 CASE 语句对每个 message_type 进行计数并进行数学计算。

SELECT   DAY(date) as day, HOUR(date) as hour,  
         SUM(CASE WHEN message_type = 1 THEN 1 ELSE 0 END) -
         SUM(CASE WHEN message_type = 2 THEN 1 ELSE 0 END) -
         SUM(CASE WHEN message_type = 4 THEN 1 ELSE 0 END) as result
FROM     log_entry 
WHERE    date >= CURDATE() - INTERVAL 2 MONTH 
         AND message_type BETWEEN 1 AND 4 
GROUP BY day,hour;