Geek Answers Handbook

How can I select hourly bills from a table, including missed hours?

I am going to count by the hour. But not every hour is presented at my desk.

To ensure that the data always includes an empty clock, I built an hour table with the hours per day and time from 2000 to 2037. I believe table tables LEFT JOINcan be found in this table to track missing hours. But I need some help.

Table date_hour:

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

Table 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

Desired Result:

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 Error:

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;

What is the most effective way to achieve these goals? Suppose each day has 100k-1MM records to measure at least 30 days of data at a time.

+4
source share
2 answers

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

+3

LEFT JOIN DATE_FORMAT , , , . , @amdixon, .

, , . , 30M (30 , 1M ).

, date_hour (, ) hour. , :

WHERE
        date_hour.hour >= '2015-05-01 00:00:00'
    AND date_hour.hour <  '2015-05-02 00:00:00'

, - 1M , , 1M . . 1M , 30M , , , 1M , .

, ( - ). , log_date , , my_data.

1

my_data. log_date. WHERE. , , , my_data. log_date, . , MySQL , datetime DATE_FORMAT, date_hour.hour , ( , date_hour.hour). , . datetime .

TIMESTAMPADD(HOUR,
    TIMESTAMPDIFF(HOUR,'2015-01-01 00:00:00',DateTimeValue),
    '2015-01-01 00:00:00')

"2015-01-01", . datetime - , , , , .

SELECT
    date_hour.hour,
    COUNT(my_data.log_date) AS count
FROM
    date_hour
    LEFT JOIN my_data ON 
        date_hour.hour = TIMESTAMPADD(HOUR, TIMESTAMPDIFF(HOUR,'2015-01-01 00:00:00',my_data.log_date), '2015-01-01 00:00:00')
WHERE
    date_hour.hour   >= '2015-05-01 00:00:00' AND
    date_hour.hour   <  '2015-05-02 00:00:00' AND
    my_data.log_date >= '2015-05-01 00:00:00' AND
    my_data.log_date <  '2015-05-02 00:00:00'
GROUP BY
    date_hour.hour
ORDER BY
    date_hour.hour
;

date_hour my_data, , 1M , . , 1M , . , ( 1 , , ).

2

datetime " ", log_hour my_data, log_date log_date . . log_hour, . log_date, log_hour:

SELECT
    date_hour.hour,
    COUNT(my_data.log_hour) AS count
FROM
    date_hour
    LEFT JOIN my_data ON date_hour.hour = my_data.log_hour
WHERE
    date_hour.hour   >= '2015-05-01 00:00:00' AND
    date_hour.hour   <  '2015-05-02 00:00:00' AND
    my_data.log_hour >= '2015-05-01 00:00:00' AND
    my_data.log_hour <  '2015-05-02 00:00:00'
GROUP BY
    date_hour.hour
ORDER BY
    date_hour.hour
;
+1

More articles:


All Articles