Alternative to Self Join

I have asked a question here:
https://stackoverflow.com/questions/43807566/how-to-divide-two-values-from-the-same-column-but-at-different-rows

about dividing values from the same table, at the same column but on different rows. Now I have the problem where I have more numerators and denominators (with different uns). Is still the self join a good way to solve this problem with Postgres or there are better solutions?

Example:

| postcode | value | uns |
|----------|-------|-----|
|       AA |    40 |  53 |
|       BB |    20 |  53 |
|       AA |    10 |  54 |
|       AA |    20 |  55 |
|       AA |    10 |  56 |
|       AA |    30 |  57 |
|       AA |    50 |  58 |
|       BB |    10 |  54 |
|       BB |    10 |  55 |
|       BB |    70 |  56 |
|       BB |    80 |  57 |
|       BB |    10 |  58 |

Result should be:

| postcode | formula    |
|----------|------------|
|       AA | 18.888...  |
|       BB | 14.375     |

Where the value are grouped by postcode and the formula is (value with uns):

(V53 * V56 + V54 * V57 + V55 * V58) / (V56 + V57 + V58)

Paying attention to avoid eventual division by zero.
Formula can be even more complex but that is a good example.

You can aggregate all uns/value pairs into a JSON object, then use that to access the UNS values by name. This requires some casting as the values can only be extracted as text from the JSON object, but the formula looks very similar to your description then:

with vals(postcode, v) as (
  select postcode, json_object_agg(uns, value)
  from x
  group by postcode
), factors (postcode, denominator, divisor) as (
  select postcode, 
         (v->>'53')::decimal * (v->>'56')::decimal + (v->>'54')::decimal * (v->>'57')::decimal + (v->>'55')::decimal * (v->>'58')::decimal,
         (v->>'56')::decimal + (v->>'57')::decimal + (v->>'58')::decimal
  from vals
)
select postcode, 
       denominator / nullif(divisor, 0)
from factors;

I have divided the aggregation, the evaluation of the denominator and divisor and the final division into three steps to make it more readable.

Online example: http://rextester.com/IZYT54566

You can simplify the formula by creating a function:

create function val(p_vals json, p_uns text)
  returns decimal
as $
  select (p_vals ->> p_uns)::decimal;
$
language sql;

with vals (postcode, v) as (
  select postcode, json_object_agg(uns, value)
  from x
  group by postcode
), factors (postcode, denominator, divisor) as (
  select postcode, 
         val(v, '53') * val(v, '56') + val(v, '54') * val(v, '57') + val(v, '55') * val(v, '58'),
         val(v, '56') + val(v, '57') + val(v, '58')
  from vals
)
select postcode, 
       denominator / nullif(divisor, 0)
from factors;

StackExchange Database Administrators Q#172860, answer score: 6