I have a table with a list of markets and corresponding amounts related to those markets

I would like this $10 in the blank market to be allocated to the rest of the markets proportionately based on amounts excluding the blank market (ex. amount(A)/sum(A B C D))

The desired output is:

I think I can query it using multiple CTEs, but wanted to see if it's possible to allocate using as few CTEs as possible or not using CTE at all.

CodePudding user response：

So with this CTE just for data:

with data(market, amount) as (
    select * from values
    ('A',   10),
    ('B',   30),
    ('C',   50),
    ('D',   10),
    (null, 10)
)

we can:

select d.*
    ,sum(iff(d.market is null, d.amount,null)) over() as to_spread
    ,sum(iff(d.market is not null, d.amount,null)) over() as total
    ,div0(d.amount, total) as part
    ,part * to_spread as bump
    ,d.amount   bump as result
from data as d
qualify market is not null

to get:

We can then fold a few of those steps up:

select d.*
    ,d.amount    div0(d.amount, sum(iff(d.market is not null, d.amount,null)) over()) * sum(iff(d.market is null, d.amount,null)) over() as result
from data as d
qualify market is not null

seems these results are on fixed point numbers, the truncation of division, will loss "amounts", which could be spread fairly, but that might require a second pass.