using custom distribution in Oracle / PL/SQL

Posted on 2007-07-24
Last Modified: 2013-12-18
Looking for some urgent help with implementing a custom statistical distribution in Oracle 10g: The scenario goes like this: I want a function that returns a single value based on a table that looks like this:
Value   Freq%
*******  *********
A         5.3%
B         4.8 %
C         5.1 %
D         10.5 %

When I call this function, I want it to return the value of A 5.3% of the time, the value of B 4.8% of the time and so on and so forth.

Is this possible using PL/SQL? if so could someone shed some light on how to approach this problem.

Much appreciated.
Question by:choochim
    LVL 34

    Expert Comment

    The way I have done this in the past is to use a random number generator (DBMS_RANDOM for example) and generate a number between 0 and 1, then take your percentages and convert then to numbers (5.3% = .053) and check them in order to see where the number falls and return that value.

    Author Comment

    thanks for the quick respone. One doubt though, what if I have multiple hits on a single random number value say - there are A,B,C as three possible hits for the same random number? Then what is the remedy?
    LVL 34

    Accepted Solution

    You have to make the numbers cumulative.

    Basically, if there are 3 values:

    A 40%
    B 30%
    C 30%

    Then the numbers you need to look for are:

    A 40%  0.4
    B 30% 0.7
    C 30% 1.0

    So if the number is <= .4 then you pick A, <= .7 then you pick B, <= 1 you pick C.  You have to do a small amount of research on the random number generator, but usually they generate numbers that are 0 < num <= 1, if this is the case, this should work.

    Author Comment

    Thanks. After posting my initial response, I figured that out and modified to a cume percentage and that did the trick.

    You guys are good !

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Join & Write a Comment

    I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
    Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now