Re: SOLVED Statistics query - Mailing list pgsql-general

...

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