如何用SQLAlchemy/SQL按分组获取最小groupid对应的value值

原始数据表：

unit    groupid value
enhed1  24270   2
enhed2  24272   2
enhed3  24120   1
enhed4  24191   4
enhed4  24192   2

需求是为每个unit获取其分组内groupid最小的那一行对应的value，预期结果：

unit    value
enhed1  2
enhed2  2
enhed3  1
enhed4  4

你的代码问题分析 #

你尝试的SQLAlchemy代码错误在于：子查询select(func.min(table.c.groupid)).group_by(table.c.unit).scalar_subquery()仅返回单个标量值（所有unit的最小groupid中的某一个），而非每个unit对应的最小groupid。这导致WHERE条件匹配到的是全局最小groupid的行，最终分组后所有unit都返回同一个value。

select(
    [
        table.c.unit,
        table.c.value
    ]
)
.where(
    table.c.groupid == select(func.min(table.c.groupid  )).group_by(table.c.unit).scalar_subquery()
)
.group_by(table.c.unit)

正确解决方案 #

方法1：关联子查询（兼容所有SQLite版本） #

通过关联子查询，为每个unit匹配其对应的最小groupid：

SQL语句：

SELECT t.unit, t.value
FROM your_table t
WHERE t.groupid = (
    SELECT MIN(groupid)
    FROM your_table
    WHERE unit = t.unit
)

对应的SQLAlchemy代码：

from sqlalchemy import select, func

# 为表创建别名，避免关联时的字段歧义
t_alias = table.alias('t')
# 子查询：获取当前unit对应的最小groupid
subquery = select(func.min(table.c.groupid)).where(table.c.unit == t_alias.c.unit).scalar_subquery()

# 主查询：筛选出groupid等于对应最小groupid的行
stmt = select(t_alias.c.unit, t_alias.c.value).where(t_alias.c.groupid == subquery)

方法2：窗口函数（SQLite 3.25+支持） #

使用ROW_NUMBER()窗口函数按unit分组，按groupid升序排序后取第一行：

SQL语句：

SELECT unit, value
FROM (
    SELECT 
        unit, 
        value,
        ROW_NUMBER() OVER (PARTITION BY unit ORDER BY groupid ASC) AS rn
    FROM your_table
)
WHERE rn = 1

对应的SQLAlchemy代码：

from sqlalchemy import select, func, over

# 子查询：添加行号，按unit分组、groupid升序排序
rank_col = over(func.row_number(), partition_by=table.c.unit, order_by=table.c.groupid.asc()).label('rn')
subquery = select(table.c.unit, table.c.value, rank_col).subquery()

# 主查询：筛选出行号为1的行
stmt = select(subquery.c.unit, subquery.c.value).where(subquery.c.rn == 1)

以上两种方法都能正确返回每个unit对应最小groupid的value值，符合预期结果。

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