Re: Frames vs partitions: is SQL2008 completely insane?

2008/12/28 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> "Hitoshi Harada" <umi(dot)tanuki(at)gmail(dot)com> writes:
>> 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 ...
>
>> 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,
>
> Well, that's interesting, but I think the controlling definition is in
> 6.10 general rule 1b, which very clearly states that the frame is to be
> used for lead/lag (and the adjacent rules say the same for all the other
> standard window functions).
>
> The wording in 4.15 does seem like evidence that the spec authors may
> have misspoke in 6.10, but we're never going to settle it from the text
> of the spec. Can anyone check what DB2 and Oracle do here?

I tested on Oracle 10.2.0, and the results are:

select depname, empno, salary,
lead(salary, 1) over (order by salary),
lag(salary, 1) over (order by salary),
first_value(salary) over (order by salary),
last_value(salary) over (order by salary)
from empsalary;

DEPNAME EMPNO SALARY LEAD(SALARY,1)OVER(ORDERBYSALARY)
LAG(SALARY,1)OVER(ORDERBYSALARY)
FIRST_VALUE(SALARY)OVER(ORDERBYSALARY)
LAST_VALUE(SALARY)OVER(ORDERBYSALARY)
personnel 5 3500 3900 　　　 3500 3500
personnel 2 3900 4200 3500 3500 3900
develop 7 4200 4500 3900 3500 4200
develop 9 4500 4800 4200 3500 4500
sales 4 4800 4800 4500 3500 4800
sales 3 4800 5000 4800 3500 4800
sales 1 5000 5200 4800 3500 5000
develop 10 5200 5200 5000 3500 5200
develop 11 5200 6000 5200 3500 5200
develop 8 6000 　　　 5200 3500 6000

which means the section 4.15 is true. Could anyone try DB2?

> In any case, both sections agree that last_value works on the frame,
> which makes it effectively useless with the default frame definition.
> So I'm still thinking that we need at least a subset of frame support.
> I'm tempted to propose that we just handle the CURRENT ROW and UNBOUNDED
> options for each end of the frame.

If we can afford it, I and many users are so glad with it. In my
opinion it is not so painful if we had done concrete fundamentals of
window functions, but isn't it up to our time for the release?

Regards,

--
Hitoshi Harada