Oracle SQL : Calculating weighted probability-CodePudding

I'm struggling to retrieve a "weighted probability" from a database table in my SQL statement.

What do I need to do:

I have tabular information of probable financial values like:

Table my_table

ID	P [%]	Value [$]
1	50	200
2	50	200
3	60	100

I need to calculate the weighted probability of reasonable worst case financial value to occur.

The formula is:

P_weighted = 1 - (1 - P_1 * Value_1/Max(Value_1-n) * (1 - P_2 * Value_2/Max(Value_1-n) * ...

i.e.

P_weighted = 1 - Product(1 - P_i * Value_i / Max(Value_1-n)

P_weighted = 1 - (1 - 50% * 200 / 200) * (1 - 50% * 200 / 200) * (1 - 60% * 100 / 200) = 82.5%

I know the is not product function in (Oracle) SQL, and this can be substituted by EXP( SUM LN(x))) ensuring x is always positive.

Hence, if I were only to calculate the combined probability I could (regardless of the value I could do like:

SELECT EXP(SUM(LN(1 - t.P))) FROM FROM my_table t WHERE condition

When I need to include the Max(t.Value) I've got the following problem:

A SELECT list cannot include both a group function, such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, and an individual column expression, unless the individual column expression is included in a GROUP BY clause.

So I tried the following:

SELECT ROUND(1-EXP(SUM(LN(1 - t.P*t.Value/max(t.Value)))),1) FROM FROM my_table t WHERE condition GROUP BY t.P, t.Value

But this does obviously group the output by probability rather than multiplying it and just returns 0.5 or 50% instead of the product which should be 0.825 or 82.5%.

How do I get the weighted probability from by table above using (Oracle) SQL?

CodePudding user response：

Does this do it:

with da as (select .50 as p, 200 as v from dual union all select .50 , 200 from dual union all select .60,100 from dual),
mx as (select max(v) mx from da)
select exp(sum(ln(1-da.p*da.v/mx))) from da, mx;

EXP(SUM(LN(1-DA.P*DA.V/MX)))
----------------------------
            .175

CodePudding user response：

with 
test1 as(
        select max(value) v_max from my_table
        ),
test2 as(
        select  1-(my.p/100* value/t1.v_max) rez
        from my_table my, test1 t1
        )
        select to_char(round((1-(EXP (SUM (LN (rez)))))*100,2))||'%' "Weighted probability"
        from test2

RESULT:

Weighted probability
--------------------
82,5%

CodePudding user response：

If you want the calculation per-row then you can use an analytic SUM:

SELECT id,
       ROUND(1 - EXP(SUM(LN(1 - wp)) OVER (ORDER BY id)), 3) AS cwp
FROM   (
  SELECT id,
         p * value / MAX(value) OVER () AS wp
  FROM   table_name
)

Which, for the sample data:

CREATE TABLE table_name (ID, P, Value) AS
SELECT 1, .50, 200 FROM DUAL UNION ALL
SELECT 2, .50, 200 FROM DUAL UNION ALL
SELECT 3, .60, 100 FROM DUAL;

Outputs the cumulative weighted probabilities:

ID CWP

1 .5

2 .75

3 .825

ID	CWP
1	.5
2	.75
3	.825

If you just want the total weighted probability then:

SELECT ROUND(1 - EXP(SUM(LN(1 - wp))), 3) AS twp
FROM   (
  SELECT id,
         p * value / MAX(value) OVER () AS wp
  FROM   table_name
)

Which, for the sample data, outputs:

TWP

.825

TWP
.825

db<>fiddle here