Reputation: 930
Return count for each hour in last 24 hours filling in blanks with zeros
I'm trying to get a recordset with a count of sales for each hour in the past 24 hours, filling in empty hours with zeros.
My example dataset looks like this and contains two test rows:
id | saleID | amount | created
---|-------------------|-------------|-------------
1 | 6032b317-1533... | 20000 | 2019-01-10 23:56:40
2 | 43556fg6-5344... | 60000 | 2019-01-11 18:06:32
The current time, for example, is 18:10, so the result I'm looking for is:
| hourOrderID | saleHour | saleHourTotal |
| ----------- | -------- | ------------- |
| 1 | 18:00 | 1 |
| 2 | 17:00 | 0 |
| 3 | 16:00 | 0 |
| 4 | 15:00 | 0 |
| 5 | 14:00 | 0 |
| 6 | 13:00 | 0 |
| 7 | 12:00 | 0 |
| 8 | 11:00 | 0 |
| 9 | 10:00 | 0 |
| 10 | 9:00 | 0 |
| 11 | 8:00 | 0 |
| 12 | 7:00 | 0 |
| 13 | 6:00 | 0 |
| 14 | 5:00 | 0 |
| 15 | 4:00 | 0 |
| 16 | 3:00 | 0 |
| 17 | 2:00 | 0 |
| 18 | 1:00 | 0 |
| 19 | 0:00 | 0 |
| 20 | 23:00 | 1 |
| 21 | 22:00 | 0 |
| 22 | 21:00 | 0 |
| 23 | 20:00 | 0 |
| 24 | 19:00 | 0 |
| 25 | 18:00 | 0 |
Please note; between 18:00-19:00 today there was 1 sale and between 18:00-19:00 yesterday there was 0. This is my issue.
The result I'm actually getting is:
| hourOrderID | saleHour | saleHourTotal |
| ----------- | -------- | ------------- |
| 1 | 18:00 | 1 |
| 2 | 17:00 | 0 |
| 3 | 16:00 | 0 |
| 4 | 15:00 | 0 |
| 5 | 14:00 | 0 |
| 6 | 13:00 | 0 |
| 7 | 12:00 | 0 |
| 8 | 11:00 | 0 |
| 9 | 10:00 | 0 |
| 10 | 9:00 | 0 |
| 11 | 8:00 | 0 |
| 12 | 7:00 | 0 |
| 13 | 6:00 | 0 |
| 14 | 5:00 | 0 |
| 15 | 4:00 | 0 |
| 16 | 3:00 | 0 |
| 17 | 2:00 | 0 |
| 18 | 1:00 | 0 |
| 19 | 0:00 | 0 |
| 20 | 23:00 | 1 |
| 21 | 22:00 | 0 |
| 22 | 21:00 | 0 |
| 23 | 20:00 | 0 |
| 24 | 19:00 | 0 |
| 25 | 18:00 | 1 | <----- this isn't yesterday, it's today
The query I've been working on is:
SELECT
aux.id AS hourOrderID,
CONCAT(aux.dh, ':00') AS saleHour,
COALESCE(COUNT(ets.saleID), 0) AS saleHourTotal
FROM eventTicketSales AS ets
RIGHT JOIN (
SELECT 1 AS id, HOUR(UTC_TIMESTAMP()) AS dh UNION
SELECT 2 AS id, HOUR(DATE_SUB(UTC_TIMESTAMP(), INTERVAL 1 HOUR)) AS dh UNION
SELECT 3 AS id, HOUR(DATE_SUB(UTC_TIMESTAMP(), INTERVAL 2 HOUR)) AS dh UNION
SELECT 4 AS id, HOUR(DATE_SUB(UTC_TIMESTAMP(), INTERVAL 3 HOUR)) AS dh UNION
SELECT 5 AS id, HOUR(DATE_SUB(UTC_TIMESTAMP(), INTERVAL 4 HOUR)) AS dh UNION
SELECT 6 AS id, HOUR(DATE_SUB(UTC_TIMESTAMP(), INTERVAL 5 HOUR)) AS dh UNION
SELECT 7 AS id, HOUR(DATE_SUB(UTC_TIMESTAMP(), INTERVAL 6 HOUR)) AS dh UNION
SELECT 8 AS id, HOUR(DATE_SUB(UTC_TIMESTAMP(), INTERVAL 7 HOUR)) AS dh UNION
SELECT 9 AS id, HOUR(DATE_SUB(UTC_TIMESTAMP(), INTERVAL 8 HOUR)) AS dh UNION
SELECT 10 AS id, HOUR(DATE_SUB(UTC_TIMESTAMP(), INTERVAL 9 HOUR)) AS dh UNION
SELECT 11 AS id, HOUR(DATE_SUB(UTC_TIMESTAMP(), INTERVAL 10 HOUR)) AS dh UNION
SELECT 12 AS id, HOUR(DATE_SUB(UTC_TIMESTAMP(), INTERVAL 11 HOUR)) AS dh UNION
SELECT 13 AS id, HOUR(DATE_SUB(UTC_TIMESTAMP(), INTERVAL 12 HOUR)) AS dh UNION
SELECT 14 AS id, HOUR(DATE_SUB(UTC_TIMESTAMP(), INTERVAL 13 HOUR)) AS dh UNION
SELECT 15 AS id, HOUR(DATE_SUB(UTC_TIMESTAMP(), INTERVAL 14 HOUR)) AS dh UNION
SELECT 16 AS id, HOUR(DATE_SUB(UTC_TIMESTAMP(), INTERVAL 15 HOUR)) AS dh UNION
SELECT 17 AS id, HOUR(DATE_SUB(UTC_TIMESTAMP(), INTERVAL 16 HOUR)) AS dh UNION
SELECT 18 AS id, HOUR(DATE_SUB(UTC_TIMESTAMP(), INTERVAL 17 HOUR)) AS dh UNION
SELECT 19 AS id, HOUR(DATE_SUB(UTC_TIMESTAMP(), INTERVAL 18 HOUR)) AS dh UNION
SELECT 20 AS id, HOUR(DATE_SUB(UTC_TIMESTAMP(), INTERVAL 19 HOUR)) AS dh UNION
SELECT 21 AS id, HOUR(DATE_SUB(UTC_TIMESTAMP(), INTERVAL 20 HOUR)) AS dh UNION
SELECT 22 AS id, HOUR(DATE_SUB(UTC_TIMESTAMP(), INTERVAL 21 HOUR)) AS dh UNION
SELECT 23 AS id, HOUR(DATE_SUB(UTC_TIMESTAMP(), INTERVAL 22 HOUR)) AS dh UNION
SELECT 24 AS id, HOUR(DATE_SUB(UTC_TIMESTAMP(), INTERVAL 23 HOUR)) AS dh UNION
SELECT 25 AS id, HOUR(DATE_SUB(UTC_TIMESTAMP(), INTERVAL 24 HOUR)) AS dh
) AS aux ON HOUR(ets.created) = aux.dh
AND ets.created > DATE_SUB(UTC_TIMESTAMP, INTERVAL 24 HOUR)
GROUP BY aux.id, aux.dh
ORDER BY hourOrderID
And you can fiddle with my bits here :o
I would like it so the last hour in the set is from the previous day but my brain has decided it can't go any further and requires assistance.
I tried adding another UNION for a 25th hour and also tried extending the timeframe to 25 hours but the results were far off what I wanted:
AND ets.created > DATE_SUB(UTC_TIMESTAMP, INTERVAL 25 HOUR
Please suggest what can be changed to make this work as expected.
Upvotes: 2
Views: 902
Answers (2)
Reputation: 1661
(For purposes of syntax, I'm assuming you're using MySQL.)
In order to produce 25 rows of data from a single SELECT statement, you need a table (real or fake) that has 25 rows, which is exactly what your inner SELECT did. Before showing a simpler way to actually construct the inner table, I'd suggest adding a few more columns to it (assuming the current time is 6 PM on December 31):
| hourOrderID | saleHour | startTime | endTime |
| ----------- | -------- | ---------------- | ---------------- |
| 1 | 18:00 | 2019-01-10 18:00 | 2019-01-10 19:00 |
| 2 | 17:00 | 2019-01-10 17:00 | 2019-01-10 18:00 |
| 3 | 16:00 | 2019-01-10 16:00 | 2019-01-10 17:00 |
| 4 | 15:00 | 2019-01-10 15:00 | 2019-01-10 16:00 |
| 5 | 14:00 | 2019-01-10 14:00 | 2019-01-10 15:00 |
| 6 | 13:00 | 2019-01-10 13:00 | 2019-01-10 14:00 |
| 7 | 12:00 | 2019-01-10 12:00 | 2019-01-10 13:00 |
| 8 | 11:00 | 2019-01-10 11:00 | 2019-01-10 12:00 |
| 9 | 10:00 | 2019-01-10 10:00 | 2019-01-10 11:00 |
| 10 | 9:00 | 2019-01-10 09:00 | 2019-01-10 10:00 |
| 11 | 8:00 | 2019-01-10 08:00 | 2019-01-10 09:00 |
| 12 | 7:00 | 2019-01-10 07:00 | 2019-01-10 08:00 |
| 13 | 6:00 | 2019-01-10 06:00 | 2019-01-10 07:00 |
| 14 | 5:00 | 2019-01-10 05:00 | 2019-01-10 06:00 |
| 15 | 4:00 | 2019-01-10 04:00 | 2019-01-10 05:00 |
| 16 | 3:00 | 2019-01-10 03:00 | 2019-01-10 04:00 |
| 17 | 2:00 | 2019-01-10 02:00 | 2019-01-10 03:00 |
| 18 | 1:00 | 2019-01-10 01:00 | 2019-01-10 02:00 |
| 19 | 0:00 | 2019-01-10 00:00 | 2019-01-10 01:00 |
| 20 | 23:00 | 2019-01-09 23:00 | 2019-01-10 00:00 |
| 21 | 22:00 | 2019-01-09 22:00 | 2019-01-09 23:00 |
| 22 | 21:00 | 2019-01-09 21:00 | 2019-01-09 22:00 |
| 23 | 20:00 | 2019-01-09 20:00 | 2019-01-09 21:00 |
| 24 | 19:00 | 2019-01-09 19:00 | 2019-01-09 20:00 |
| 25 | 18:00 | 2019-01-09 18:00 | 2019-01-09 19:00 |
Then your query would be quite straightforward:
SELECT
aux.hourOrderID,
aux.saleHour,
COALESCE(COUNT(ets.saleID), 0) AS saleHourTotal
FROM madeUpTable AS aux LEFT JOIN eventTicketSales AS ets
ON aux.startTime <= ets.created AND ets.created < aux.endTime
GROUP BY aux.hourOrderID
ORDER BY hourOrderID
Turns out you can use Common Table Expressions (https://dev.mysql.com/doc/refman/8.0/en/with.html) to do exactly this:
WITH RECURSIVE aux AS
(
SELECT 1 AS hourOrderId,
HOUR(UTC_TIMESTAMP()) AS saleHour,
DATE_ADD(
DATE_FORMAT(UTC_TIMESTAMP(), "%Y-%m-%d %H:00:00"),
INTERVAL 0 HOUR
) AS startDate,
DATE_ADD(
DATE_FORMAT(UTC_TIMESTAMP(), "%Y-%m-%d %H:00:00"),
INTERVAL 1 HOUR
) AS endDate
UNION ALL
SELECT n + 1,
HOUR(DATE_SUB(startDate, INTERVAL 1 HOUR)),
DATE_SUB(startDate, INTERVAL 1 HOUR),
DATE_SUB(startDate, INTERVAL 1 HOUR)
FROM aux
WHERE n < 25
)
SELECT
aux.hourOrderID,
aux.saleHour,
COALESCE(COUNT(ets.saleID), 0) AS saleHourTotal
FROM madeUpTable AS aux LEFT JOIN eventTicketSales AS ets
ON aux.startTime <= ets.created AND ets.created < aux.endTime
GROUP BY aux.hourOrderID
ORDER BY hourOrderID
Personally I try to limit use of this technique on code that involves other people because it's not something that most people have seen in the wild, but for SQL that is just for me, I find this more straightforward to read because the query itself is simple. IMHO the most complicated part of this is actually just rounding the current time to the nearest hour while preserving the date part.
Upvotes: 1
Reputation: 930
I took the advice of @Strawberry in the comment section and decided to do this application level, which now makes a lot of sense, as it's much easier to read. I don't regret trying the SQL version, as I learned a bit.
I'm posting the solution I went with below, which is in PHP for anybody that needs something similar. If anybody has a more efficient solution than this please share in the comments.
The Query:
SELECT
CONCAT(DATE_FORMAT(created, '%Y-%m-%d %H'), ':00:00') AS saleHour,
SUM(amount) AS totalSales
FROM eventTicketSales
WHERE created BETWEEN DATE_SUB(UTC_TIMESTAMP(), INTERVAL 24 HOUR) AND UTC_TIMESTAMP
GROUP BY HOUR(created);
The result:
saleHour totalSales
2019-01-11 17:00:00 10000
2019-01-10 23:00:00 20000
Stored in $salesIn24Hours
The Logic:
$hours = 24 + 1;
for ($i=0; $i<$hours; $i++) {
$date = date('Y-m-d H', strtotime('-'.$i.' HOUR')).':00:00';
$key = array_search($date, array_column($salesIn24Hours, 'saleHour'));
if ($key !== FALSE) {
echo $date . ' ' . $salesIn24Hours[$key]['totalSales'] . '<br>';
} else {
echo $date . ' 0' . '<br>';
}
}
The Result:
2019-01-11 21:00:00 0
2019-01-11 20:00:00 0
2019-01-11 19:00:00 0
2019-01-11 18:00:00 0
2019-01-11 17:00:00 10000
2019-01-11 16:00:00 0
2019-01-11 15:00:00 0
2019-01-11 14:00:00 0
2019-01-11 13:00:00 0
2019-01-11 12:00:00 0
2019-01-11 11:00:00 0
2019-01-11 10:00:00 0
2019-01-11 09:00:00 0
2019-01-11 08:00:00 0
2019-01-11 07:00:00 0
2019-01-11 06:00:00 0
2019-01-11 05:00:00 0
2019-01-11 04:00:00 0
2019-01-11 03:00:00 0
2019-01-11 02:00:00 0
2019-01-11 01:00:00 0
2019-01-11 00:00:00 0
2019-01-10 23:00:00 20000
2019-01-10 22:00:00 0
2019-01-10 21:00:00 0
Upvotes: 1
Related Questions
- How can I select hourly counts from a table, including missing hours?
- MySQL Query for obtaining count per hour
- How to MYSQL get data group by every hour in 24 hours even data if not present , IF 0 will select NULL
- Query to retrieve count per hour and zero if none
- how to count each hour to one day with mysql
- Get hourly result for count in the last 48 hours - MySql
- MySQL - Group by hour including zero values
- MYSQL return 0 on hourly count
- Get Counts per Hour for all Dates
- MySQL get record count by hour, even the hours with no records