CODEWITHSUNDEEP
sqldatabasepostgresqlcalculated-columns
osbon123
osbon123

Reputation: 499

postgres alter table and insert row probabilities

I want to sum the weight of rows with the same id1 and then computes the ratio for each of that row (sort of like a probability) in a column prob.

Table data:

id1 weight  id2 
1   0.1    3   
1   0.2    4   
1   0.3    5   
1   0.8    6   
2   0.5     7    
2   0.6     8    
2   0.7     9

Output should be:

id1 weight  id2 prob 
1   0.1    3    0.07
1   0.2    4   0.1429
1   0.3    5   0.214
1   0.8    6   0.5714
2   0.5     7    0.2778 
2   0.6     8    0.3333
2   0.7     9    0.3388

Upvotes: 1

Views: 66

Answers (4)

user330315
user330315

Reputation:

This can easily be solved with a window function which is typically faster than any solution with a sub-query or derived table:

select id1, 
       weight, 
       id2, 
       weight / sum(weight) over (partition by id1) as prob
from items
order by id1, id2;

SQLFiddle example: http://sqlfiddle.com/#!15/64453/1

Upvotes: 1

Praveen
Praveen

Reputation: 9345

Try;

select 
    t.id1, t.weight, t.id2, (t.weight/t1.tot) prob
from tbl t
join (
    select id1, sum(weight) tot
    from tbl
    group by id1
) t1
on t.id1 = t1.id1

Upvotes: 0

jarlh
jarlh

Reputation: 44786

Use a correlated sub-select to sum all weights for an id:

select id1, weight, id2,
       weight / (select sum(weight) from table t2 where t2.id = t1.id) as prob
from table t1

Upvotes: 0

Raj Kamuni
Raj Kamuni

Reputation: 388

Try this

SELECT t.*,t.wieght/t1.Weight AS Prob FROM TABLE_NAME t INNER JOIN 
(
SELECT id1,SUM(weight) AS Weight FROM TABLE_NAME GROUP BY id1
)t1 ON t.id = t1.id

Upvotes: 0

Related Questions