Re: SOLVED Statistics query

On 04/10/2013 10:51 AM, Steve Crawford wrote:
> ...
>>
>> Given a point in time I would like to:
>>
>> 1. Identify all distinct unit_ids with an entry that exists in
>> the preceding hour then
>>
>> 2. Count both the total events and sum the status=1 events for
>> the most recent 50 events for each unit_id that fall within a
>> limited period (e.g. don't look at data earlier than midnight).
>> So unit_id 60 might have 50 events in the last 15 minutes while
>> unit_id 4 might have only 12 events after midnight.
>>
...

Guess I needed to wait for the coffee to absorb. I've come up with an
initial working solution (perhaps excess use of CTE but it's useful for
testing/debugging over different portions of collected data):

with

report_time as (
select
1365526800::int as list_end
),

report_ranges as (
select
extract(epoch from date_trunc('day', abstime(list_end)))::int as
day_start,
greatest(list_end-3600, extract(epoch from date_trunc('day',
abstime(list_end)))::int) as list_start,
list_end
from
report_time
),

today_events as (
select
unit_id,
event_time,
status
from
event_log d,
report_ranges r
where
d.event_time >= r.day_start and
d.event_time <= r.list_end
),

unit_id_list as (
select
distinct unit_id,
coalesce((select
i.event_time
from
today_events i
where
i.unit_id = o.unit_id and
i.event_time <= r.list_end
order by
event_time desc
limit 1
offset 49), r.day_start) as first_event
from
event_log o,
report_ranges r
where
event_time between r.list_start and r.list_end
)

select
unit_id,
(select
count(*)
from
today_events ii
where
ii.unit_id = oo.unit_id and
ii.event_time >= oo.first_event) as events,
(select
sum (case when status = -6 then 1 else 0 end)
from
today_events ii
where
ii.unit_id = oo.unit_id and
ii.event_time >= oo.first_event) as live_answer
from
unit_id_list oo
order by
unit_id
;

Cheers,
Steve