I have a Postgres table containing peoples' names, their age, and the number of marbles they own. The example has only two rows for simplicity, but that could be more.

I would like to retrieve the number of marbles per age group, where the number of marbles is distributed to each age group, proportionally¹ to the person's age. The age groups can vary, but are known at query time².

The output would look like this:

Is there an elegant way of querying my table to achieve this?

¹To be specific, the proportion of marbles per age group for one person would be:

marbles_higher_age_group = (age - lower_age_group) / (higher_age_group - lower_age_group) * marbles
marbles_lower_age_group = marbles - marbles_higher_age_group

²When querying the table, the age groups are known. They can differ over multiple queries and may be arbitrarily chosen. For instance, one query might use age groups [0,5,10], while the next query uses [0,1,4,8,12].

CodePudding user response：

For an arbitrary set of age groups stored in a table

select ag, sum(agm) marbles
from marbles m
join ( 
    select age_group low, lead(age_group) over(order by age_group) high
    from age_groups ag
) a on a.high >= m.age and a.low <= m.age
, lateral (
    select a.low ag , 1.0 * m.marbles * (a.high - m.age)/(a.high -  a.low) agm
    union all
    select a.high, 1.0 * m.marbles * (m.age -  a.low)/(a.high -  a.low)

) mm
group by ag
order by ag;

Alternatively you can provide an array of age groups in the query

select ag, sum(agm) marbles
from marbles m
join ( 
    select age_group low, lead(age_group) over(order by age_group) high
    from unnest(array[1,2,4,8,12]) age_groups(age_group)
) a on a.high >= m.age and a.low <= m.age
, lateral (
    select a.low ag, 1.0 * m.marbles * (a.high - m.age)/(a.high - a.low) agm
    union all
    select a.high, 1.0 * m.marbles * (m.age - a.low)/(a.high - a.low)
) mm
group by ag
order by ag;