mysql - 将具有相同列的不同 MySQL 表上的数据组合并合并为唯一行并对其运行查询

Question

以下是我运行的用于分析 MySQL 数据库上的服务器日志的代码:

SELECT YEAR(datetime), MONTH( datetime ), MIN(DATE(datetime)), MAX(DATE(datetime)), COUNT(DISTINCT (ip)), COUNT(ip), (COUNT(ip) / COUNT(DISTINCT (ip))) AS Ratio
FROM  `server_log_1` 
WHERE  `state` LIKE  'action'
AND  `user_id` LIKE  '9'
GROUP BY MONTH( datetime )
UNION
SELECT YEAR(datetime), MONTH( datetime ), MIN(DATE(datetime)), MAX(DATE(datetime)), COUNT(DISTINCT (ip)), COUNT(ip), (COUNT(ip) / COUNT(DISTINCT (ip))) AS Ratio
FROM  `server_log_2` 
WHERE  `state` LIKE  'action'
AND  `user_id` LIKE  '9'
GROUP BY MONTH( datetime )
UNION
SELECT YEAR(datetime), MONTH( datetime ), MIN(DATE(datetime)), MAX(DATE(datetime)), COUNT(DISTINCT (ip)), COUNT(ip), (COUNT(ip) / COUNT(DISTINCT (ip))) AS Ratio
FROM  `server_log_3` 
WHERE  `state` LIKE  'action'
AND  `user_id` LIKE  '9'
GROUP BY MONTH( datetime )

这给了我结果:

YEAR(datetime)  MONTH( datetime )   MIN(DATE(datetime)) MAX(DATE(datetime)) COUNT(DISTINCT (ip))    COUNT(ip)   Ratio
2015                12              2015-12-14              2015-12-30              16              20              1.2500
2016                1               2016-01-05              2016-01-27              15              20              1.3333
2016                2               2016-02-02              2016-02-29              27              36              1.3333
2016                3               2016-03-04              2016-03-29              24              32              1.3333
2016                4               2016-04-01              2016-04-08              5               8               1.6000
2016                4               2016-04-09              2016-04-29              19              27              1.4211
2016                5               2016-05-02              2016-05-28              21              31              1.4762
2016                6               2016-06-01              2016-06-30              28              34              1.2143
2016                7               2016-07-01              2016-07-20              14              16              1.1429
2016                7               2016-07-21              2016-07-21              1               1               1.0000

这些是每个数据库的准确结果，但是当一个月分为 2 个不同的数据库(例如 2016-4 和 2016-7)时，您会看到这会导致该月生成 2 个不同的行。

我希望将这些行生成为单行，其中包含相应月份的值的总和。 (每月仅一行)

此外，如果可能的话，简化查询。

2016-12 年后我会遇到麻烦，按月分组将合并 2015-12 和 2016-12 的数据。我怎样才能避免这个问题？

请问你能写出正确的SQL语句吗？

Answer 1

在group by之前执行union all怎么样:

SELECT YEAR(datetime), MONTH(datetime), MIN(DATE(datetime)), MAX(DATE(datetime)), COUNT(DISTINCT (ip)), COUNT(ip), (COUNT(ip) / COUNT(DISTINCT (ip))) AS Ratio
FROM (
    (SELECT datetime, ip FROM server_log_1 WHERE state = 'action' AND user_id = 9) UNION ALL
    (SELECT datetime, ip FROM server_log_2 WHERE state = 'action' AND user_id = 9) UNION ALL
    (SELECT datetime, ip FROM server_log_3 WHERE state = 'action' AND user_id = 9) 
) AS table_all
GROUP BY YEAR(datetime), MONTH(datetime);

就性能而言，您希望为 state、user_id 上的每个表建立一个索引(也许还需要添加 datetime 和 ip)。