如何添加行政区筛选并查询各行政区21岁以下人口最多的社区

1. 实现行政区（Borough）的添加与筛选功能 #

你的现有SQL仅计算了各社区的21岁以下人口总数，要关联行政区信息，需先绑定社区代码与行政区的对应关系。如果数据库中存在存储社区-行政区映射的表（比如public.neighborhoods，包含neighborhood_code和borough字段），先修改SQL完成关联：

SELECT 
  n.borough,
  u.neighborhood_code,
  SUM(u.under_18 + u.a18_to_19 + u.age_20) AS under_21_population
FROM public.uscb_acs_14to18_age u
JOIN public.neighborhoods n ON u.neighborhood_code = n.neighborhood_code
GROUP BY n.borough, u.neighborhood_code

若要筛选特定行政区，直接添加WHERE子句锁定目标：

SELECT 
  n.borough,
  u.neighborhood_code,
  SUM(u.under_18 + u.a18_to_19 + u.age_20) AS under_21_population
FROM public.uscb_acs_14to18_age u
JOIN public.neighborhoods n ON u.neighborhood_code = n.neighborhood_code
WHERE n.borough = '曼哈顿' -- 替换为你需要筛选的行政区名称
GROUP BY n.borough, u.neighborhood_code

2. 查询每个行政区中21岁以下人口最多的社区 #

使用窗口函数为每个行政区内的社区按人口排序，取排名第一的记录即可：

WITH neighborhood_under21 AS (
  SELECT 
    n.borough,
    u.neighborhood_code,
    SUM(u.under_18 + u.a18_to_19 + u.age_20) AS under_21_population
  FROM public.uscb_acs_14to18_age u
  JOIN public.neighborhoods n ON u.neighborhood_code = n.neighborhood_code
  GROUP BY n.borough, u.neighborhood_code
),
ranked_neighborhoods AS (
  SELECT 
    *,
    ROW_NUMBER() OVER (PARTITION BY borough ORDER BY under_21_population DESC) AS rn
  FROM neighborhood_under21
)
SELECT borough, neighborhood_code, under_21_population
FROM ranked_neighborhoods
WHERE rn = 1;

如果存在多个社区人口并列第一的情况，想要全部展示这些社区，只需把ROW_NUMBER()替换为RANK()。

补充说明 #

• 如果没有专门的社区-行政区映射表，先确认uscb_acs_14to18_age表是否自带行政区字段，或者补充对应的关联表。
• 确保SQL中的表名、字段名与你数据库内的实际名称一致，比如行政区字段可能叫borough_name，需对应修改。

内容的提问来源于stack exchange，提问作者Bjoern Erik F. Riechmann