PostgreSQL中zipnames表按优先级聚合同一zipcode单一行的SQL实现

我有一张名为zipnames的表，包含id、zipcode、name_fi和name_sv四个字段，其中name_fi和name_sv均可为空。每个zipcode最多对应四种行：两个名称都存在、仅name_fi存在、仅name_sv存在、两者都为空。

现需编写SELECT语句，使每个zipcode仅返回一行，优先级规则为：优先返回双名称都存在的行，其次是仅单个名称存在的行，仅当无任何名称存在的行时才返回双空行。

使用环境为PostgreSQL v14，可提供纯SQL或Python的SQLAlchemy代码实现。

示例数据 #

| id | zipcode | name_fi  | name_sv     |
|----|---------|----------|-------------|
|  1 | 00000   |          |             |
|  2 | 00100   | HELSINKI | HELSINGFORS |
|  3 | 00100   | HELSINKI |             |
|  4 | 00100   |          | HELSINGFORS |
|  5 | 00100   |          |             |
|  6 | 50100   | MIKKELI  |             |
|  7 | 50100   |          |             |
|  8 | 64250   |          | PJELAX      |

期望输出 #

| id | zipcode | name_fi  | name_sv     |
|----|---------|----------|-------------|
|  1 | 00000   |          |             |
|  2 | 00100   | HELSINKI | HELSINGFORS |
|  6 | 50100   | MIKKELI  |             |
|  8 | 64250   |          | PJELAX      |

纯SQL实现 #

核心逻辑是给每行标记优先级，再按zipcode分组取优先级最高的行：

• 双名称都存在：优先级1（最高）
• 仅单个名称存在：优先级2
• 双空：优先级3（最低）

用ROW_NUMBER()窗口函数实现排序筛选：

SELECT id, zipcode, name_fi, name_sv
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY zipcode 
            ORDER BY 
                CASE 
                    WHEN name_fi IS NOT NULL AND name_sv IS NOT NULL THEN 1
                    WHEN name_fi IS NOT NULL OR name_sv IS NOT NULL THEN 2
                    ELSE 3
                END
        ) AS rn
    FROM zipnames
) t
WHERE rn = 1;

SQLAlchemy实现（Python） #

对应SQL逻辑，用case构造优先级排序条件，结合窗口函数筛选：

from sqlalchemy import create_engine, select, case, func
from sqlalchemy.orm import sessionmaker
from your_model_module import Zipnames  # 替换为你的模型类

# 初始化数据库连接
engine = create_engine('postgresql://user:password@host:port/dbname')
Session = sessionmaker(bind=engine)
session = Session()

# 构造子查询，添加优先级排序后的行号
subquery = (
    select(
        Zipnames.id,
        Zipnames.zipcode,
        Zipnames.name_fi,
        Zipnames.name_sv,
        func.row_number().over(
            partition_by=Zipnames.zipcode,
            order_by=case(
                [(Zipnames.name_fi.isnot(None) & Zipnames.name_sv.isnot(None), 1)],
                [(Zipnames.name_fi.isnot(None) | Zipnames.name_sv.isnot(None), 2)],
                else_=3
            )
        ).label('rn')
    )
).subquery()

# 主查询筛选每个zipcode的首行
query = select(subquery).where(subquery.c.rn == 1)

# 执行查询并获取结果
results = session.execute(query).fetchall()

# 输出结果示例
for row in results:
    print(row)

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