SQLAlchemy按col1分组查询各组内col2最高频值的实现方法

原有写法session.query(A.col1, func.count(A.col2)).group_by(A.col1).all()仅统计了每个col1分组下col2的总条目数，没有对col2的不同取值做频次统计，因此无法得到每个分组内的最高频col2值。

通用简便方案（支持窗口函数的数据库均可使用） #

适用范围：MySQL 8.0+、PostgreSQL、SQLite 3.25+、SQL Server等主流新版本数据库，仅需两层简单查询，逻辑清晰：

• 第一步：统计每个col1 + col2组合的出现频次，同时对同一col1分组内的频次做倒序排名
• 第二步：筛选出每个col1分组内排名第一的记录，即为该分组的最高频col2值

代码示例：

from sqlalchemy import func, desc

# 构造子查询：统计频次+排名
subq = session.query(
    A.col1,
    A.col2,
    func.count(A.col2).label("freq"),
    # 按col1分组，组内按频次倒序排名
    func.row_number().over(
        partition_by=A.col1,
        order_by=desc(func.count(A.col2))
    ).label("rank")
).group_by(A.col1, A.col2).subquery()

# 主查询：取每个分组排名第一的结果
res = session.query(
    subq.c.col1, 
    subq.c.col2.label("most_frequent_col2")
).filter(subq.c.rank == 1).all()

如果需要保留同分组内多个并列最高频的结果，将row_number()替换为rank()即可

PostgreSQL专属极简方案 #

如果使用PostgreSQL数据库，可以直接调用内置的mode()聚合函数，无需子查询，一行即可实现：

res = session.query(
    A.col1,
    func.mode().within_group(desc(A.col2)).label("most_frequent_col2")
).group_by(A.col1).all()

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