时间戳

首页 > 解决方案 > 如何按时间分组，如果mysql中没有数据计数为零？

mysql - 如何按时间分组，如果mysql中没有数据计数为零？

问题描述

sql查询是

select substring(InputTime, 1, 13) as Time, count(*) as count from DB group by hour(InputTime);

它出来了


+---------------+-------+
| Time          | count |
+---------------+-------+
| 2021-06-15 01 |    11 |
| 2021-06-15 02 |    22 |
| 2021-06-15 04 |    22 |
+---------------+-------+

但是我要


+---------------+-------+
| Time          | count |
+---------------+-------+
| 2021-06-15 01 |    11 |
| 2021-06-15 02 |    22 |
| 2021-06-15 03 |    0  |
| 2021-06-15 04 |    22 |
| 2021-06-15 05 |    0  |
...
...
...
...
...
+---------------+-------+

所以我想用零值填充空计数。如何编辑我的查询？

标签： mysql

解决方案

  1. 首先，您的查询可以用更简单的方式表示为：

SELECT COUNT(*) AS totalCalls, HOUR( end) AS Hour FROM callsDataTable c INNER JOIN products p ON c.company = p.number AND p.id IN (@_PRODUCTS) AND YEAR( end) = @_YEAR AND MONTH( end) = @_MONTH按ASC的 顺序按小时分组（end）HourHour

  1. 使用@NoDisplayName 在他们的回答中建议的想法：

CREATE TABLE hours_table（小时 INT）；

INSERT INTO hours_table VALUES(0), (1), (2), /* 把缺失值放在这里 */ (23);

  1. 您可以加入包含小时数的表以获得所需的结果：

SELECT COUNT(*) AS totalCalls, h.hours AS Hour FROM callsDataTable c INNER JOIN products p ON c.company = p.number RIGHT JOIN hours_table h ON h.hours = HOUR(c. end) AND p.id IN (@_PRODUCTS) AND YEAR( end) = @_YEAR AND MONTH( end) = @_MONTH GROUP BY h.hours ORDER BY h.hours ASC

  1. 如果它运行得太慢（而且我确信它非常慢），您应该研究一种使用 end BETWEEN '2015-01-01 00:00:00' AND '2015-01-31 23:59' 之类的方法： 59' 而不是比较 YEAR(end) 和 MONTH(end)。

  2. 可以这样完成：

SET @start = STR_TO_DATE(CONCAT(@_YEAR, '-', @_MONTH, '-01 00:00:00'), '%Y-%m-%d %H:%i:%s'); SET @end = DATE_SUB(DATE_ADD(@start, INTERVAL 1 MONTH), INTERVAL 1 SECOND);

选择 ... ...end在 @start 和 @end ... 之间

  1. 但这种变化本身并没有帮助。它需要一个字段端的索引来带来所需的速度提升：

ALTER TABLE 调用DataTable ADD INDEX(end);

  1. 在连接条件中使用 HOUR(c.end) 是运行缓慢的另一个原因。

可以通过将表 hours_table 与第一个查询（的简化版本）产生的结果集连接起来来改进它：

SELECT IFNULL(totalCalls, 0) AS totalCalls, h.hours AS Hour FROM hours_table h LEFT JOIN (SELECT COUNT(*) AS totalCalls, HOUR( end) as Hour FROM callsDataTable c INNER JOIN products p ON c.company = p.number AND p. id IN (@_PRODUCTS) AND YEAR( end) = @_YEAR AND MONTH( end) = @_MONTH GROUP BY HOUR( end) AS Hour ) d ON h.hours = d。Hour ORDER BY h.hours ASC

推荐阅读