在PostgreSql中计算每个地区的城市人口占总人口的百分比

Question

我有一个表格，即结算由 3 列和 5437 行组成。第一列有8组(A，B，C，D，E，F，G，H(，第二列有"6"组(城市，城镇，村庄，岛屿，郊区，哈姆雷特(

我的表格如下...

ID     district_name              Cluster         Population    
1003        A                    village            1543          
1002        B                    town               63437   
1003        C                    village            1698    
1002        D                    town               11785   
1040        E                    hamlet             100 
2034        F                    hamlet             190 
4003        G                    city               207561  
4243        H                    Suburb             682 
674         D                    Island             718 
1012        A                    village            1566
1040        F                    hamlet             200
1020        D                    town               11881
4055        D                    city               208763  

我想根据以下条件计算每个地区的人口百分比;

total population in each district/sum(population)*100
where cluster='city' or cluster='town'

A practical example of my desire outpout can be as follows ，

district_name  Population_total(City+Town+island) Urban_Percentage(Town_City)

     D                233147                      99.69(232429/233147 * 100)

Answer 1

SQL 演示

WITH  district as (         
     SELECT district_name, 
            sum("Population") as district_total
     FROM residentioal 
     where "Cluster"='city' or "Cluster"='town'
     GROUP BY district_name
), whole_total as (
     SELECT district_name, sum("Population") as total
     FROM residentioal 
     GROUP BY district_name
 )
SELECT d.district_name, 
       total as whole_total,
       district_total as city_town_total,        
       district_total * 100.0 / total as PP
FROM district d
JOIN whole_total  w
  ON d.district_name = w.district_name

输出

| district_name | whole_total | city_town_total |                pp |
|---------------|-------------|-----------------|-------------------|
|             D |      233147 |          232429 | 99.69203978605772 |
|             B |       63437 |           63437 |               100 |
|             G |      207561 |          207561 |               100 |

编辑：

SQL 演示

您实际上可以通过单个查询来做到这一点：

SELECT   district_name,
         sum("Population") as total,
         sum(CASE WHEN "Cluster" IN ('city', 'town') 
                  THEN "Population"
                  ELSE 0
             END ) as district_total,
         sum(CASE WHEN "Cluster" IN ('city', 'town') 
                  THEN "Population"
                  ELSE 0
             END )  * 100.0 / sum("Population") as PP
FROM residentioal
GROUP BY district_name;