|
We've seen this question--or ones very similar to it--before.
However, it's been quite a while, so let's look at it again.
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:
DATEADD(HH,DATEDIFF(HH,'2000-01-01',GETDATE()),'2000-01-01')
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
|
