Re: Frames vs partitions: is SQL2008 completely insane?

2008/12/27 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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