CODEWITHSUNDEEP
sqlpostgresql
hanznv
hanznv

Reputation: 53

Table analysis in SQL (multiplication)

I have TABLE_A and i need to create TABLE_A_FINAL from it.

Rule:
In TABLE_A_FINAL we have rows with all possible combinations of ID_C and if in TABLE_A is same combination of ID_C we multiply the value of WEIGHT.
Is this possible in SQL? Thanks for help and advices.

TABLE_A
ID_N |WEIGHT |ID_C | 1 |1.15 | 1A | 2 |1.13 | 1A | 3 |1.65 | 1B | 4 |1.85 | 2A | 6 |1.57 | 2A |

TABLE_A_FINAL
ID_C |FINAL_WEIGHT | 1A |1.15×1.13 = 1.2995 | 1B |1.65 | 2A |1.85×1.57 = 2.9045 |

Upvotes: 3

Views: 84

Answers (2)

joop
joop

Reputation: 4523

A recursive CTE over a WINDOW to detect the chains of consecutive id_c ranges:

WITH RECURSIVE yy AS  (
        WITH xx AS  (
                SELECT  id_n,weight,id_c
                , lead(id_n) over (PARTITION BY id_c ORDER BY id_n) AS nxt
                , rank() over (PARTITION BY id_c ORDER BY id_n) rnk
                FROM multipliers
                )
        SELECT x0.rnk, x0.id_n,x0.weight,x0.id_c , x0.nxt
                , x0.weight AS prod
        FROM xx x0
        WHERE x0.rnk = 1 -- START of a chain
        UNION ALL
        SELECT x1.rnk, x1.id_n,x1.weight,x1.id_c , x1.nxt
                , yy.prod * x1.weight AS prod
        FROM xx x1
        JOIN yy ON x1.id_c = yy.id_c
           AND x1.rnk = 1+ yy.rnk -- NEXT member of chain
        )
SELECT * FROM yy
WHERE yy.nxt IS NULL -- no next: this must be the END of a chain
        ;

If there are zero- or negative weights, this will still behave as expected. NULL values for weight could be treated by yy.prod * AS COALESCE( x.weight,1) AS prod, etc.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270421

Unfortunately, Postgres doesn't have a product() aggregation function. You can emulate such a function with sum() and logs/exponentiation. Because your values all seem to be positive and non-zero:

select id_c, exp(sum(ln(weight)) as final_weight
from table_a
group by id_c;

If you have zeros or negative numbers, then the logic is rather more complicated, but still quite possible.

Upvotes: 3

Related Questions