Charles Hooper wrote:
> On Apr 10, 1:44 pm, Mladen Gogala <mgogala.SPAM-ME...._at_verizon.net>
> wrote:
>> On Tue, 10 Apr 2007 10:25:40 -0700, hasta_l3 wrote:
>>> On 10 avr, 18:39, Mladen Gogala <mgogala.SPAM-ME...._at_verizon.net> wrote:
>>>> On Tue, 10 Apr 2007 08:56:06 -0700, hasta_l3 wrote:
>>>>> Would you mean a bug in Oracle (documentation), Mladen ?
>>>>> Granted, this area is somewhat tricky, but I don't see a bug here,
>>>>> for my understanding is :
>>>>> - RANK is a ranking function, that is computed for the whole
>>>>> group/partition
>>>> Find me a mention that this is a windowing
>>>> function:http://download-east.oracle.com/docs/cd/B19306_01/server.102/
>> b14200/
>>>> functions087.htm#i1280029
>>> Well, MIN is so classified in the database warehousing guide :
>>> http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14223/
>> analysis.htm#i1014372
>>
>>
>>
>>> The windowing behavior is demonstrated in the analytic example of the
>>> page you refer to above
>> Demonstration is not enough. Second, min as a windowing function makes
>> no sense. This is such an important feature that it was a must to mention
>> it. Who would be interested in "local minimum". Minimum should be returned
>> for the whole partition, which is, in this case, the entire table. This
>> is, at the very least, a documentation bug. In my opinion, it is also a
>> software bug as "local minimum" makes no sense, at least not to me.
>> Windowing functions make sense for lag and lead, but not for "min" or
>> "max". As for the data warehouse guide, SQL functions are documented in
>> SQL reference. That is where one should look.
FWIW, The behavior is as specified in the SQL Standard.
The trick here lies in in the ORDER BY clause.
The usage of ORDER BY in OLAP implies that order matters to the user and
hence windowing semantics are used.
If ORDER BY is ommitted then MIN (and others) return the same value for
the whole partition.
Cheers
Serge
PS: Heck this could be used in a DBMS independent application. Pretty
sophisticated dumping... (sorry catching up across multiple threads here ;-)
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Received on Tue Apr 10 2007 - 16:45:28 CDT
