如何将社区人口数据均等拆分到二级街道分类以得到准确街道人口值

核心分配逻辑：先统计每个社区的街道总数量，计算基准分配人口=社区总人口÷街道数取整数部分，余数=社区总人口除以街道数的余数；将每个社区内的街道按任意规则排序（比如按街道ID、街道名称排序），前「余数」条街道分配人口为基准值+1，剩余街道分配人口为基准值，即可得到你需要的分配结果。

1. SQL实现（支持MySQL 8.0+/PostgreSQL等带窗口函数的数据库） #

WITH street_count AS (
    -- 统计每个社区的街道总数
    SELECT 
        NeighbourhoodID,
        COUNT(*) AS total_streets
    FROM Streets
    GROUP BY NeighbourhoodID
),
ranked_streets AS (
    -- 给每个社区内的街道按ID排序编号
    SELECT 
        s.Street,
        n.Neighbourhood,
        n.Population AS total_pop,
        sc.total_streets,
        ROW_NUMBER() OVER (PARTITION BY s.NeighbourhoodID ORDER BY s.StreetID) AS street_rank
    FROM Streets s
    JOIN Neighbourhoods n ON s.NeighbourhoodID = n.NeighbourhoodID
    JOIN street_count sc ON s.NeighbourhoodID = sc.NeighbourhoodID
)
-- 计算每条街道最终分配人口
SELECT 
    Neighbourhood,
    Street,
    CASE 
        WHEN street_rank <= (total_pop % total_streets) THEN (total_pop DIV total_streets) + 1
        ELSE total_pop DIV total_streets
    END AS Population
FROM ranked_streets
ORDER BY Neighbourhood, street_rank;

2. Excel/在线表格工具实现 #

操作步骤：

• 用VLOOKUP合并社区表和街道表，得到每行包含社区名、街道名、社区总人口、街道ID的基础表
• 新增列「社区街道总数」：假设A列为社区名，公式为=COUNTIF(A:A,A2)
• 新增列「社区内街道排序」：假设D列为街道ID，公式为=COUNTIFS(A:A,A2,D:D,"<="&D2)
• 新增列「最终人口」：假设C列为社区总人口，E列为社区街道总数，F列为社区内街道排序，公式为=IF(F2<=MOD(C2,E2),INT(C2/E2)+1,INT(C2/E2))

3. Python Pandas实现 #

import pandas as pd

# 读取社区表、街道表数据
neighbourhoods = pd.DataFrame({
    'NeighbourhoodID': [1,2,3],
    'Neighbourhood': ['Center','South','East'],
    'Population': [550,200,2]
})
streets = pd.DataFrame({
    'StreetID': [1,2,3,4,5,6,7],
    'Street': ['1st','2nd','3rd','4th','James','Frank','Kim'],
    'NeighbourhoodID': [1,1,1,1,3,3,3]
})

# 合并两个表
df = streets.merge(neighbourhoods, on='NeighbourhoodID', how='left')
# 统计每个社区的街道总数
df['total_streets'] = df.groupby('NeighbourhoodID')['Street'].transform('count')
# 给社区内的街道按ID排序编号
df['street_rank'] = df.groupby('NeighbourhoodID')['StreetID'].rank(method='min').astype(int)
# 计算每条街道的分配人口
df['Population'] = df.apply(
    lambda x: (x['Population']//x['total_streets'])+1 
    if x['street_rank'] <= (x['Population']%x['total_streets']) 
    else x['Population']//x['total_streets'], 
    axis=1
)
# 输出结果
result = df[['Neighbourhood','Street','Population']].sort_values(['Neighbourhood','street_rank'])
print(result)

内容的提问来源于stack exchange，提问作者vekeras