One of the popular queries in financial analytic systems is to calculate some moving aggregate within some time interval, i.e. moving average of trade price within 5 minutes window. Unfortunately this case is not supported by PostgreSQL:

select symbol,date,avg(price) over (order by date range between '5 minutes' preceding and current row) from Trades;
ERROR:  RANGE PRECEDING is only supported with UNBOUNDED

Is there some principle problem in implementing such kind of window?
May be I missed something, but it seems to me that it should not be very difficult. There is update_frameheadpos function which adjusts head position of windows in "rows" mode and
reports error in rows mode:

        if (frameOptions & FRAMEOPTION_ROWS)
            /* In ROWS mode, bound is physically n before/after current */
int64 offset = DatumGetInt64(winstate->startOffsetValue);

            if (frameOptions & FRAMEOPTION_START_VALUE_PRECEDING)
                offset = -offset;

            winstate->frameheadpos = winstate->currentpos + offset;
            /* frame head can't go before first row */
            if (winstate->frameheadpos < 0)
                winstate->frameheadpos = 0;
            else if (winstate->frameheadpos > winstate->currentpos)
                /* make sure frameheadpos is not past end of partition */
                spool_tuples(winstate, winstate->frameheadpos - 1);
                if (winstate->frameheadpos > winstate->spooled_rows)
                    winstate->frameheadpos = winstate->spooled_rows;
            winstate->framehead_valid = true;
        else if (frameOptions & FRAMEOPTION_RANGE)
            /* parser should have rejected this */
elog(ERROR, "window frame with value offset is not implemented");

The straightforward approach to support range mode is to advance head position until "distance" between head and current row is less or equal than specified range value. Looks like not something too complex to implement, doesn't it? Are there some caveats? Certainly it assumes that window is ordered by key and the key type supports subtraction, so "text" can not be used here.
Something else?

Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to