Обсуждение: GROUP BY hour

I have, what I imagine to be, a fairly simple question. I have a query
that produces output for a line graph. Each row represents an interval
on the graph.

SELECT COUNT(call_id) AS value, EXTRACT(hour from start_time) AS hour
FROM c_call WHERE  start_time >= '2008-08-01 00:00:00' AND end_time <=
'2008-08-01 23:59:59' GROUP BY hour


This works great when there is data in each interval but when a given
interval has no data the group is omitted. What is the best way to
ensure that the result contains a row for each interval with the value
field set to zero or null? The reporting tool is incapable of filling
in the gaps.

Thanks

Nathan Thatcher wrote:
> I have, what I imagine to be, a fairly simple question. I have a query
> that produces output for a line graph. Each row represents an interval
> on the graph.
>
> SELECT COUNT(call_id) AS value, EXTRACT(hour from start_time) AS hour
> FROM c_call WHERE  start_time >= '2008-08-01 00:00:00' AND end_time <=
> '2008-08-01 23:59:59' GROUP BY hour
>
>
> This works great when there is data in each interval but when a given
> interval has no data the group is omitted. What is the best way to
> ensure that the result contains a row for each interval with the value
> field set to zero or null? The reporting tool is incapable of filling
> in the gaps.
>
> Thanks
>
>
Use generate_series as part of your query. You can get a listing of all
the hours, which can be integrated with your other data in a variety of
ways, using:

select '2008-08-01 00:00:00'::timestamp+generate_series(0,23)*'1
hour'::interval as hour;

        hour
---------------------
 2008-08-01 00:00:00
 2008-08-01 01:00:00
...
 2008-08-01 23:00:00

Nathan Thatcher escreveu:
> I have, what I imagine to be, a fairly simple question. I have a query
> that produces output for a line graph. Each row represents an interval
> on the graph.
>
> SELECT COUNT(call_id) AS value, EXTRACT(hour from start_time) AS hour
> FROM c_call WHERE  start_time >= '2008-08-01 00:00:00' AND end_time <=
> '2008-08-01 23:59:59' GROUP BY hour
>
>
> This works great when there is data in each interval but when a given
> interval has no data the group is omitted. What is the best way to
> ensure that the result contains a row for each interval with the value
> field set to zero or null? The reporting tool is incapable of filling
> in the gaps.
>



Try:
SELECT s.hour::int, coalesce(t.value,0) FROM generate_series(0,23) AS
s(hour)
LEFT OUTER JOIN
(SELECT COUNT(call_id) AS value, EXTRACT(hour from start_time) AS hour
    FROM c_call WHERE  date_trunc('day',start_time) = '2008-08-01'
GROUP BY hour) AS t
ON s.hour = t.hour;

Osvaldo

Brilliant!

On Fri, Aug 1, 2008 at 12:18 PM, Steve Crawford
<scrawford@pinpointresearch.com> wrote:
> Nathan Thatcher wrote:
>>
>> I have, what I imagine to be, a fairly simple question. I have a query
>> that produces output for a line graph. Each row represents an interval
>> on the graph.
>>
>> SELECT COUNT(call_id) AS value, EXTRACT(hour from start_time) AS hour
>> FROM c_call WHERE  start_time >= '2008-08-01 00:00:00' AND end_time <=
>> '2008-08-01 23:59:59' GROUP BY hour
>>
>>
>> This works great when there is data in each interval but when a given
>> interval has no data the group is omitted. What is the best way to
>> ensure that the result contains a row for each interval with the value
>> field set to zero or null? The reporting tool is incapable of filling
>> in the gaps.
>>
>> Thanks
>>
>>
>
> Use generate_series as part of your query. You can get a listing of all the
> hours, which can be integrated with your other data in a variety of ways,
> using:
>
> select '2008-08-01 00:00:00'::timestamp+generate_series(0,23)*'1
> hour'::interval as hour;
>
>       hour
> ---------------------
> 2008-08-01 00:00:00
> 2008-08-01 01:00:00
> ...
> 2008-08-01 23:00:00
>
>