如何使用Postgres按比例将数据分配到组中?

Question

我有一个Postgres表，其中包含人名、年龄和他们拥有的弹珠数量。为简单起见，该示例只有两行，但可以更多。

<表类> 名称 年龄 大理石 tbody><<tr>爱丽丝410鲍勃720

对于表中存储的任意年龄组

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;

或者，您可以在查询

中提供年龄组数组

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;