Re: Need help for function..

The example I gave will work for any amount of rows in the database. I just made up the sample data, (it doesn't represent any sort of distribution), plus you can also add a where clause for the minimum and maximum range values.

In reality, all you have to do is put this in a procedure and decide what to do with the results.
Look at global temporary tables, and inline views, both of which can make the manipulation of data easier. If you were on 9i, I would suggest writing your own user_defined function.

"LT Cheah" <LT_Cheah_at_usa.net> wrote in message news:lgs9tvojlkhn6b3uo1kluqjkcqq5e8r6oa_at_4ax.com...
> On Mon, 8 Dec 2003 15:35:39 -0000, Keith Jamieson wrote:
>
> In actual case there would be about 23000 data points per wafer.
> Yes the SQL Query like below would round out the data as below
> In the example you've done would result in a UNIFORM distribution (which
is
> not the case in for production data -- It ought to be a Normal
distribution
> (where you have a mode for the most frequent data bin).
> And if the max freq is say any 5 bins, the function could just choose the
> middle bin (if two then just the first value)
>
> Would such a function be possible to write ?
>
>
> => Oracle can do this for you, via a select statement
> =>
> => SQL> select * from mode_test
> => 2 /
> =>
> => WAFER_ID RESISTANCE
> => ---------- ----------
> => 1 1.741
> => 1 1.641
> => 1 1.743
> => 1 1.749
> => 1 1.659
> => 1 1.637
> =>
> => 1 select count(*),round(resistance,2) fr
> => 2 mode_test
> => 3 group by round(resistance,2)
> => 4* order by count(*) desc
> => 5 /
> =>
> => COUNT(*) ROUND(RESISTANCE,2)
> => ---------- -------------------
> => 2 1.64
> => 2 1.74
> => 1 1.66
> => 1 1.75
> =>
> =>
> => Now if you do write a function around this sql then which resistance is
the
> => one which occurs most often?
> =>
> => Keith Jamieson.
> =>
> =>
> => "LT Cheah" <LT_Cheah_at_usa.net> wrote in message
> => news:ahr8tv4u6oq9tli9fp7qe1kkh52bke018h_at_4ax.com...
> => > Is it possible to write the mode function using the available
statistical
> => or
> => > analytical function that is provided by Oracle? If so, would
appreciate
> => > some help here.
> => >
> => > I want to find the mode of resistance values for individual wafers
from
> => our
> => > production data that would be rounded to one decimal point in ohms.
> => > where one wafer would have > 20000 resistance data points.
> => >
> => > I've gone to Tahiti and could not find the function for mode of a
> => population
> => > which in EXCEL would be =mode(range), ie the function that returns
the
> => most
> => > frequently occurring, or repetitive value of the resistance in ohms
(after
> => > rounding to one decimal place).
> => >
> => > I'm running Oracle 8.1.7
> => >
> => > Appreciate any help I can get.
> =>
>
Received on Tue Dec 09 2003 - 05:54:09 CST