I have a table with a datetime field. I would like to get an hourly summary from that table, which is easily achieved by using a 'group by'.
However, I would like the summary to include every hour within the last 48, even if no records exist for that hour. In this case it would simply return a zero for that hour. Any ideas?
The solution relies on two techniques:
Generate the datetimes
This is done by using a numbers table. The query will use a table with the numbers from 0 to 47 in it. Each number is used as an interval value in a date expression, subtracting from a base datetime value. This generates the 48 datetime values that mark the groups for the purposes of the join.
LEFT OUTER JOIN to your data
The LEFT OUTER JOIN uses the 48 hourly datetime values as the "left" table, with your data as the "right" table, so that if no rows exist for a given hour, the aggregate functions will return zero because they ignore NULLs, and NULLs are what a LEFT OUTER JOIN produces for unmatched rows.
We start by taking the current datetime value and applying a "floor" or "truncate" technique to round it down to the nearest previous hour. In SQL, it can be done with an expression like this:
SELECT CURRENT_DATE + EXTRACT(HOUR FROM CURRENT_TIMESTAMP) HOUR
The EXTRACT function returns the current hour as an integer, e.g. 16 for 4 p.m. This number is then added, as an interval of hours, to the value of the current date, e.g. '2008-06-22'. The result is a timestamp for the beginning of the current hour, '2008-06-22 16:00:00'. Unfortunately, implementation varies from one DBMS to the next. In Oracle, you can instead simply TRUNC the SYSDATE value to the hour. In SQL Server, which has the GETDATE function for current timestamp but no equivalent CURRENT_DATE function, you can round down to the nearest previous hour like this:
Here '2000-01-01' is some arbitrary early "base date." The difference between this date and the current timestamp given by GETDATE(), as a number of whole hours, is added back to the base date, effectively rounding down to the nearest previous hour.
One satisfactory way to generate the 48 required numbers is to have an actual table of numbers:
SELECT n FROM numbers WHERE n BETWEEN 0 AND 47
Another way is to generate the numbers "on the fly" from a cross join of the integers table (the values 0 through 9) with itself:
SELECT 10 * t.i + u.i AS n FROM integers AS t CROSS JOIN integers AS u WHERE 10 * t.i + u.i BETWEEN 0 AND 47
Now we can use these 48 numbers as intervals to subtract from the starting hourly value, producing 48 hourly values. This is done in a subquery, which produces a derived table. The derived table becomes the left table in our LEFT OUTER JOIN to the data being summarized:
SELECT h.start_hour , SUM(mytable.amount) AS sum_amount FROM ( SELECT CURRENT_DATE + EXTRACT(HOUR FROM CURRENT_TIMESTAMP) HOUR - ( 10 * t.i + u.i ) HOUR AS start_hour FROM integers AS t CROSS JOIN integers AS u WHERE 10 * t.i + u.i BETWEEN 0 AND 47 ) AS h LEFT OUTER JOIN mytable ON mytable.datetimefield >= h.start_hour AND mytable.datetimefield < h.start_hour + 1 HOUR GROUP BY h.start_hour ORDER BY h.start_hour DESC
Dig Deeper on Oracle development languages
Related Q&A from Rudy Limeback
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading