按非主键分组取最小值并关联同行列属性的SQLAlchemy实现问题

It looks like you're hitting two key issues here: incorrect join syntax in SQLAlchemy, and handling scenarios where a company might have multiple rows sharing the same minimum price. Let's work through the fixes step by step.

First: Fix the Join Condition #

Your original query uses Python's and operator, which doesn't translate properly to SQL. In SQLAlchemy, you need to use the & operator (with parentheses around each condition to avoid precedence issues) or pass multiple conditions as separate arguments to the join() method.

Here's the corrected version of your initial approach (works if each company has exactly one row with the minimum price):

subquery = db.session.query(
    PriceRecord.company_id,
    db.func.min(PriceRecord.price).label("minPrice")
).group_by(PriceRecord.company_id).subquery()

# Corrected join using & with parentheses
result = db.session.query(PriceRecord) \
    .join(
        subquery,
        (subquery.c.company_id == PriceRecord.company_id) & 
        (subquery.c.minPrice == PriceRecord.price)
    ).all()

Alternatively, you can pass conditions as separate arguments for better readability:

result = db.session.query(PriceRecord) \
    .join(
        subquery,
        subquery.c.company_id == PriceRecord.company_id,
        subquery.c.minPrice == PriceRecord.price
    ).all()

Handling Tie Cases (Multiple Rows with Same Minimum Price) #

If a company has multiple rows with the same minimum price, the above query will return all of them. To ensure you get exactly one row per company (e.g., the earliest record by PriceRecord_ID), use a window function like ROW_NUMBER()—this is the most reliable approach for this scenario.

from sqlalchemy import func, over

# Subquery to assign a row number to each record per company, ordered by price then record ID
rank_subquery = db.session.query(
    PriceRecord,
    func.row_number().over(
        partition_by=PriceRecord.company_id,
        order_by=[PriceRecord.price, PriceRecord.price_record_id]
    ).label("row_num")
).subquery()

# Filter to get only the first row per company (minimum price, smallest ID if tied)
result = db.session.query(rank_subquery) \
    .filter(rank_subquery.c.row_num == 1) \
    .all()

# If you want to return actual PriceRecord model instances instead of subquery rows:
result = db.session.query(PriceRecord) \
    .from_statement(db.text("""
        SELECT pr.*
        FROM (
            SELECT *,
                   ROW_NUMBER() OVER (PARTITION BY company_id ORDER BY price, price_record_id) AS row_num
            FROM price_record
        ) pr
        WHERE pr.row_num = 1
    """)).all()

The order_by clause in the window function lets you define priority for tie-breaking—here we first sort by price ascending, then by PriceRecord_ID ascending to pick the oldest record in case multiple rows have the same minimum price.

Key Takeaways #

• Replace Python's and with SQLAlchemy's & (or separate conditions) in join clauses to ensure correct SQL generation.
• Use window functions like ROW_NUMBER() to handle tie cases and guarantee exactly one row per group.

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