Обсуждение: Restriction of windows functions

Hi,

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");        }        else

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

Konstantin Knizhnik <k.knizhnik@postgrespro.ru> writes:
> ERROR:  RANGE PRECEDING is only supported with UNBOUNDED
> Is there some principle problem in implementing such kind of window?

There was such code in the original windowagg patch and it got rejected
as being too broken.  Please consult the archives.

> Certainly it assumes that window is ordered by key and the key type 
> supports subtraction, so "text" can not be used here.

IIRC, the sticking point was defining a reasonably datatype-independent
(i.e. extensible) notion of distance.
        regards, tom lane

On 17.06.2016 17:01, Tom Lane wrote:
>
> Certainly it assumes that window is ordered by key and the key type
> supports subtraction, so "text" can not be used here.
> IIRC, the sticking point was defining a reasonably datatype-independent
> (i.e. extensible) notion of distance.

Why it is not possible just to locate "-" or "+ operator for this type?
I do not see any difference here with locating comparison operator 
needed for sorting.

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

Konstantin Knizhnik <k.knizhnik@postgrespro.ru> writes:
> On 17.06.2016 17:01, Tom Lane wrote:
>> IIRC, the sticking point was defining a reasonably datatype-independent
>> (i.e. extensible) notion of distance.

> Why it is not possible just to locate "-" or "+ operator for this type?

Because there's no guarantee that that operator has anything to do with
the sort ordering imposed by the type's btree opclass.  We must have a
distance operator that is consistent with the sort order, or the windowing
logic will get hopelessly confused.  For that matter, we support multiple
opclasses (sort orderings) per data type, and there's certainly no way
that a single "-" operator will be consistent with all of them.

At the time we discussed extending the definition of a btree opclass to
allow specification of related "+" and "-" operators, but the
infrastructure additions required seemed rather daunting.  Now that
we have pg_amop.amoppurpose, it might be easier to add such a concept;
they could be put in with a new "purpose" that shows that they are
not intended as index search operators.

Again, please see the archives.  I'm just speaking off the cuff here,
and probably don't remember all the details.
        regards, tom lane