Re: Time intersect query - Mailing list pgsql-general

On Mar 23, 2009, at 5:44 AM, Brad Murray wrote:

> My current procedure...
> 1) Create temporary table with each possible data point.  This
> example uses
> recursive functions from pgsql 8.4 but was originally implemented by
> using
> large numbers of queries from php.  My knowledge of the recursive
> functions
> is pretty week, but I was able to put this together without too much
> trouble.
>
> create temp table timerange as with recursive f as (
>    select '2009-03-21 18:20:00'::timestamp as a
>    union all
>    select a+'30 seconds'::interval as a from f where a < '2009-03-21
> 20:20:00'::timestamp
> ) select a from f;

I think you can do that easier with the generate_series function, no
need to use recursion that way. It's probably also convenient to have
the end of the interval available. It would be something like:

SELECT TIMESTAMP WITH TIME ZONE 'epoch' + f.a * INTERVAL '1 second' AS
start,
TIMESTAMP WITH TIME ZONE 'epoch' + f.a * INTERVAL '1 second' +
INTERVAL '30 seconds' AS end
FROM generate_series(
    EXTRACT(EPOCH FROM '2009-03-21 18:20:00'::timestamp)::bigint,
    EXTRACT(EPOCH FROM '2009-03-21 20:20:00'::timestamp)::bigint,
    30
) AS f(a)

I get the impression you don't use this just once, so it may be better
to keep the results (maybe with some added columns with derived values
that can be used to join on easily) instead of creating a temp table.
You could also add your mycount column here with some initialisation
value (likely 0).

I used something similar to generate a table that contained start and
end dates of weeks based on week numbers and years. We had another
table that contained periodic information, left joining the two tables
it was easy to split the period table into a record per week with
either the periodic information or NULL values (which meant no data
for that week). I realise weeks per year aren't much data, but neither
are your periods I think (although more). Having a scheduled script
that would delete everything older than say a month would keep this
table quite manageable (~90k records).

> 2) Update table with record counts
> alter table timerange add column mycount integer;
> explain analyze update timerange set mycount = (select count(*) from
> streamlogfoo where client = 3041 and a between startts(ts,viewtime)
> and ts);

With the above you could join streamlogfoo and group by
timerange.start, like so:

SELECT timerange.start, COUNT(*)
FROM timerange
LEFT JOIN streamlog ON (streamlog.ts BETWEEN timerange.start AND
timerange.end)
GROUP BY timerange.start

> -----------------
>
> This seems to work reasonably well, with the following exceptions...
>
> 1) The number reported is the number at the set time period, not the
> highest
> value between each data point.  With a 30 second interval, this
> isn't a big
> problem, but with larger intervals gives results that do not match
> what I'm
> looking for (maximum users).
> 2) This does not scale well for large numbers of points, as
> internally each
> data point is a complete scan through the data, even though most of
> the data
> points will be common for the entire range.
>
> I'm thinking this would be a good use for the new window functions,
> but I'm
> not sure where to begin.  Any ideas?


Well, you'd need something to partition over, and since you don't know
where your intervals start and end I don't see how you could do that
without at least first generating your intervals. After that there
doesn't seem to be much use for the windowing functions, as a simple
group by seems to do what you want.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,49c75d4b129742009819935!