I have an idea for an aggregate function (actually a pair) that would be very
useful. It's something I've wanted very frequently with Oracle and other
databases and while it's possible to implement in SQL it's hard to do
efficiently. Whereas it would be really easy for the database to do it
efficiently.
lookup_min(column1,column2)
lookup_max(column1,column2)
would return the value of column2 (or one of the values in the case of
duplicates) where column1 is the minimum/maximum value. Ie, it would have an
accumulator that stores two values, the minimum/maximum value found so far,
and the value of column2 for that record.
So it would be possible to say for example:
select min(column1),lookup_min(column1,column2) from tab
to do the equivalent of:
select column1,column2 where column1=(select min(column1) from tab) limit 1
except it would be way more efficient. (Especially if there's an index on
column1 and postgres were taught to use indexes for min/max, but that's a
different story.)
I'm not sure on the names, perhaps someone has a better idea?
I would be interested in doing this myself, it sounds like a fairly
straightforward thing to implement and would be a useful first project.
However I'm really a bit bewildered by the number of steps aggregate functions
seem to have to go through to store accumulator data. It seems like they're
going to a lot of effort to store the accumulator data in a database internal
data-type. Is there something I can read to catch up on what these data
structures are for and how to use them?
--
greg