Re: Frames vs partitions: is SQL2008 completely insane?

2008/12/28 Tom Lane <tgl@sss.pgh.pa.us>:
> "Hitoshi Harada" <umi.tanuki@gmail.com> writes:
>> 2008/12/27 Tom Lane <tgl@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 ...
>
>> 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