基于双变量的PostgreSQL计算面积比例

Question

我是PostgreSQL的新手，并不能真正解决一个简单的问题。以一个简化的表为例：

create table example(
    objectid integer,
    soil_type integer,
    area double precision);
    values (2,4,23.5),
    (2,3,30.45),
    (2,6,20.1),
    (1,3,10.5),
    (3,6,40.5),
    (3,4,20);

如何计算每种土壤类型在每个物体id中的面积份额？

Answer 1

这通过soil_type做了一个简单的组，以获得每个soil_type的和(面积)，这是放在子查询中的，其和被计算出来以得到总区域：

select soil_type, area, area / sum(area) over () as proportion 
from (select soil_type, sum(area) area from example group by soil_type)x;

 soil_type | area  |     proportion
-----------+-------+---------------------
         3 | 40.95 |  0.2823164426059979
         4 |  43.5 | 0.29989658738366076
         6 |  60.6 | 0.41778697001034126

你也可以：

with x as (select soil_type, sum(area) area from example group by soil_type)
select soil_type, area, area / (SELECT sum(area) FROM x) as proportion from x;