Re: Very specialised query

Hi,

Le 26 mars 09 à 15:30, Matthew Wakeling a écrit :
> Now, it happens that there is an algorithm for calculating overlaps
> which is really quick. It involves iterating through the table in
> order of the start variable and keeping a list of ranges which
> "haven't ended yet". When you read the next range from the table,
> you firstly purge all the ranges from the list that end before the
> beginning of the new range. Then, you output a result row for each
> element in the list combined with the new range, then you add the
> new range to the list.
>
> This algorithm just doesn't seem to fit into SQL at all.

Maybe it's just that I didn't devote enough time to reading your
detailed explanation above, but this part sounds like it could be done
in an aggregate you'd use in a correlated subquery containing the
right ORDER BY, couldn't it?
   http://www.postgresql.org/docs/8.3/interactive/sql-createaggregate.html

HTH,
--
dim