我希望按小时收集数量.但不是每个小时都在我的桌子上.

为了确保数据始终包含空闲时间,我构建了一个小时表,其日期时间为2000-2037.我想我可以将数据表LEFT JOIN连接到此表以跟踪缺少的小时数.但我需要帮助.

表：date_hour：

`hour`
2000-01-01 00:00:00
2000-01-01 01:00:00
...
2036-12-31 23:00:00

表my_data：

log_date               field1
2015-05-01 00:31:00    1000
2015-05-01 04:19:00    2000    
2015-05-01 05:19:00    1000
2015-05-01 07:19:00    100
2015-05-01 07:35:00    6000

期望的结果：

hour                   count
2015-05-01 00:00:00    1
2015-05-01 01:00:00    0
2015-05-01 02:00:00    0
2015-05-01 03:00:00    0
2015-05-01 04:00:00    1
2015-05-01 05:00:00    1
2015-05-01 06:00:00    0
2015-05-01 07:00:00    2

MySQL尝试：

SELECT
    dh.hour,
    COUNT(md.*) AS count
FROM
    date_hour dh
    LEFT JOIN my_data md ON dh.hour = ????md.log_date????
WHERE
        dh.hour >= '2015-05-01'
    AND dh.hour <  '2015-05-02'
GROUP BY
    dh.hour
ORDER BY
    dh.hour;

实现这些目标的最有效方法是什么？假设每天有100k-1MM的记录,目标是一次测量至少30天的数据.

解决方法:

可以使用DATE_FORMAT剥离分钟和秒钟,如：

询问

SELECT
    dh.hour,
    COUNT(md.*) AS count
FROM
    date_hour dh LEFT JOIN my_data md 
    ON dh.hour = DATE_FORMAT(md.log_date, "%Y-%m-%d %H:00:00")
WHERE
        dh.hour >= '2015-05-01'
    AND dh.hour <  '2015-05-02'
GROUP BY
    dh.hour
ORDER BY
    dh.hour
;

产量

+------------------------+-----------+
|          hour          |   count   |
+------------------------+-----------+
| 2015-05-01 00:00:00    | 1         |
| 2015-05-01 01:00:00    | 0         |
| 2015-05-01 02:00:00    | 0         |
| 2015-05-01 03:00:00    | 0         |
| 2015-05-01 04:00:00    | 1         |
| 2015-05-01 05:00:00    | 1         |
| 2015-05-01 06:00:00    | 0         |
| 2015-05-01 07:00:00    | 2         |
| ... trailing hours ... | allzeroes |
+------------------------+-----------+

2015-05-01 08:00:00之后的所有内容都是零(my_data中没有数据)

sqlfiddle