Search Postgresql Archives

Re: Time intersect query

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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!



--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux