Re: getting the nearest number from a column

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.
>
> --http://www.mladen-gogala.com

I believe that there are instances where a local minimum would make sense - what is the minimum temperature in the last 24 hours, and how does that minimum temperature compare with the minimum temperature from a year ago for the same time period? There is a work around - take a look at the output of the following: SELECT
  OWNER,
  TABLE_NAME,
  BLOCKS,
  MIN(BLOCKS) OVER (ORDER BY OWNER,TABLE_NAME ROWS BETWEEN UNBOUNDED PRECEDING AND 0 FOLLOWING) MIN_FROM_START,   MIN(BLOCKS) OVER (ORDER BY OWNER,TABLE_NAME ROWS BETWEEN 10 PRECEDING AND 10 FOLLOWING) MIN_SUR_10,
  MIN(BLOCKS) OVER (ORDER BY OWNER,TABLE_NAME ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) MIN_SUR_1,
  MIN(BLOCKS) OVER (ORDER BY OWNER,TABLE_NAME ROWS BETWEEN 0 PRECEDING AND UNBOUNDED FOLLOWING) MIN_TO_END,
  MIN(BLOCKS) OVER (ORDER BY OWNER,TABLE_NAME ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) MIN_FROM_START_TO_END FROM
  DBA_TABLES
ORDER BY
  OWNER,
  TABLE_NAME; Imagine the number of blocks returned as the temperature in a specified time period (one measurement per minute). If the MIN_SUR_10 returns a value 20 degrees lower than the current value, might that indicate a problem with the weather that someone would find interesting?

I agree that the default behavior is a bit different from what should reasonably be expected per the SQL Reference manual for 10.2.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Tue Apr 10 2007 - 15:21:53 CDT