这个MYSQL查询在按组计数表条目时是否更有效

Question

我目前正在使用这个笨拙的查询按时间组统计slow_log中的条目：

SELECT count(query_time) `Result`  FROM `slow_log` WHERE `db` LIKE 'taco_query' and query_time > '00:59:00'
UNION
SELECT count(query_time) `Result`  FROM `slow_log` WHERE `db` LIKE 'taco_query' and query_time < '01:00:00' and query_time > '00:50:00'
UNION
SELECT count(query_time) `Result`  FROM `slow_log` WHERE `db` LIKE 'taco_query' and query_time < '00:50:00' and query_time > '00:40:00'
UNION
SELECT count(query_time) `Result`  FROM `slow_log` WHERE `db` LIKE 'taco_query' and query_time < '00:40:00' and query_time > '00:30:00'
UNION
SELECT count(query_time) `Result`  FROM `slow_log` WHERE `db` LIKE 'taco_query' and query_time < '00:30:00' and query_time > '00:20:00'
UNION
SELECT count(query_time) `Result`  FROM `slow_log` WHERE `db` LIKE 'taco_query' and query_time < '00:20:00' and query_time > '00:10:00'
UNION
SELECT count(query_time) `Result`  FROM `slow_log` WHERE `db` LIKE 'taco_query' and query_time < '00:10:00' and query_time > '00:5:00'
UNION
SELECT count(query_time) `Result`  FROM `slow_log` WHERE `db` LIKE 'taco_query' and query_time < '00:5:00' and query_time > '00:02:00'
UNION
SELECT count(query_time) `Result`  FROM `slow_log` WHERE `db` LIKE 'taco_query' and query_time < '00:02:00' and query_time > '00:01:00'
UNION
SELECT count(query_time) `Result`  FROM `slow_log` WHERE `db` LIKE 'taco_query' and query_time < '00:01:00' and query_time > '00:00:00'

有没有更好更有效的方法来做到这一点？

Answer 1

您可以使用事例表达式为计数分配标签并按其分组：

SELECT CASE
-- cases must be sorted descending
WHEN query_time > '01:00:00' THEN '> 01:00:00'
WHEN query_time > '00:50:00' THEN '> 00:50:00'
-- other ranges in between
WHEN query_time > '00:01:00' THEN '> 00:01:00'
ELSE                              '<= one minute'
END AS `label`, COUNT(*) AS `count`
FROM `slow_log`
WHERE `db` LIKE 'taco_query'
GROUP BY 1

请注意，原始查询会跳过精确的值(例如00:50:00.000与任何where子句都不匹配(。这将把它放在> 00:40:00括号中。我宁愿在括号中使用>=。

Answer 2

SELECT distinct(query_time) `Result`  FROM `slow_log` WHERE `db` LIKE 'taco_query' and query_time > '00:00:00'

我假设您需要获得distinct查询时间，您可以直接使用distinct关键字而不是UNION。如果要排除时间"00:00:00"、"01:00:00"、"05:00:00"。。然后您可以在NOT in运算符中添加它们。

SELECT distinct(query_time) `Result`  FROM `slow_log` WHERE `db` LIKE 'taco_query' and query_time > '00:00:00' and query_time NOT IN ('00:00:00', '01:00:00', '05:00:00',...)