如何在 SQLAlchemy/Postgres 中限制每个 `group_by` 的 N 个结果？

debugcn 发表于 Dev

努贝拉

这是我的 SQLAlchemy 查询代码

medium_contact_id_subq = (g.session.query(distinct(func.unnest(FUContact.medium_contact_id_lis))).filter(FUContact._id.in_(contact_id_lis))).subquery()
q = (g.session.query(FUMessage).
         filter(FUMessage.fu_medium_contact_id.in_(medium_contact_id_subq))
         .order_by(desc(FUMessage.timestamp_utc))
         )

我想限制FUMessage分组为medium_contact_idN 个结果。

作为一种解决方法，这是我当前丑陋且未优化的代码：

    medium_contact_id_lis = (g.session.query(distinct(func.unnest(FUContact.medium_contact_id_lis))).filter(FUContact._id.in_(contact_id_lis))).all()
    q = None
    for medium_contact_id_tup in medium_contact_id_lis:
        medium_contact_id = medium_contact_id_tup[0]
        if q is None:
            q = (g.session.query(FUMessage)
                 .filter(FUMessage.fu_medium_contact_id == medium_contact_id)
                 .limit(MESSAGE_LIMIT)
                 )
        else:
            subq = (g.session.query(FUMessage)
                 .filter(FUMessage.fu_medium_contact_id == medium_contact_id)
                 .limit(MESSAGE_LIMIT)
                 )
            q = q.union(subq)
    q = q.order_by(desc(FUMessage.timestamp_utc))

伊利亚·埃维莱拉

获取每组前N行的一种方法是使用窗口函数，例如rank()或row_number()在具有所需分组和顺序的子选择中，然后在封闭的选择中过滤。对于N = 1，您可以在 Postgresql 中使用DISTINCT ON ... ORDER BY组合。

使用函数元素的over()方法来生成窗口表达式，将其应用于 SQLAlchemy 很简单：

medium_contact_id_subq = g.session.query(
        func.unnest(FUContact.medium_contact_id_lis).distinct()).\
    filter(FUContact._id.in_(contact_id_lis)).\
    subquery()

# Perform required filtering in the subquery. Choose a suitable ordering,
# or you'll get indeterminate results.
subq = g.session.query(
        FUMessage,
        func.row_number().over(
            partition_by=FUMessage.fu_medium_contact_id,
            order_by=FUMessage.timestamp_utc).label('n')).\
    filter(FUMessage.fu_medium_contact_id.in_(medium_contact_id_subq)).\
    subquery()

fumessage_alias = aliased(FUMessage, subq)

# row_number() counts up from 1, so include rows with a row num
# less than or equal to limit
q = g.session.query(fumessage_alias).\
    filter(subq.c.n <= MESSAGE_LIMIT)

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。