2008/12/27 Tom Lane <t...@sss.pgh.pa.us>: > I notice that the current patch code seems to implement > first/last/nth_value using the frame, but lead/lag using the partition, > which doesn't conform to spec AFAICS ... but lead/lag on the frame > doesn't actually appear to be a useful definition so I'd rather go > with that than with what the letter of the spec seems to say.
In 4.15, it says: The lead and lag functions each take three arguments, a <value expression> VE, an <exact numeric literal> OFFSET, and a <value expression> DEFAULT. For each row R within the *window partition P of R* defined by a window structure descriptor, the lag function returns the value of VE evaluated on a row that is OFFSET number of rows before R within P, for lead/lag, and returns the value of VE evaluated on the n-th row from the first (if FROM FIRST is specified or implied) or the last (if FROM LAST is specified) row of the *window frame* of R defined by a window structure descriptor for nth_value, added * by me. I understand lead/lag can affect all rows in the partition whereas first/last/nth_value does only rows in the frame. I guess that's why 6.10 rule 6.b forbids frame caluse in lead/lag but actually we can ignore frame though the frame is specified in the window attached with lead/lag, and it is better if you call aggregate with frame and lead/lag on the same window spec, which allows us to optimize it by calling them on the same node. It violates the spec but we'd better to extend the standard like the offset argument of lead/lag. Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
