基于MySQL的Peewee查询：获取各州出口额最高产品及问题解决

Alright, let's fix this problem. Your current query correctly calculates the maximum total export value per state, but it fails to pair that value with the right product—this happens because grouping only by state without linking the max value back to its corresponding product leads to arbitrary product selection (or errors if ONLY_FULL_GROUP_BY is enabled).

Let's start with optimal SQL approaches, then translate them to Peewee.

Optimal SQL Queries #

1. Using Window Functions (MySQL 8.0+, Recommended) #

Window functions make this task clean and efficient by ranking products within each state:

SELECT state, product, total_export_value
FROM (
    SELECT 
        state, 
        product,
        SUM(export_value) AS total_export_value,
        ROW_NUMBER() OVER (
            PARTITION BY state 
            ORDER BY SUM(export_value) DESC
        ) AS rn
    FROM trade
    WHERE origin_country = ? AND year = ?
    GROUP BY state, product
) AS ranked_trades
WHERE rn = 1;

2. Subquery Join (Compatible with Older MySQL Versions) #

If you're stuck on a pre-8.0 MySQL version, use nested subqueries to first calculate totals, then find max values, and finally map back to products:

SELECT s.state, s.product, s.total_export_value
FROM (
    SELECT state, product, SUM(export_value) AS total_export_value
    FROM trade
    WHERE origin_country = ? AND year = ?
    GROUP BY state, product
) AS s
INNER JOIN (
    SELECT state, MAX(total_export_value) AS max_total
    FROM (
        SELECT state, product, SUM(export_value) AS total_export_value
        FROM trade
        WHERE origin_country = ? AND year = ?
        GROUP BY state, product
    ) AS s2
    GROUP BY state
) AS m
ON s.state = m.state AND s.total_export_value = m.max_total;

Corrected Peewee Implementations #

1. Window Function Approach (Cleanest) #

Peewee fully supports window functions, so this is the best way to go if your MySQL version allows it:

from peewee import fn, Window

# Step 1: Calculate totals and rank products per state
ranked_subquery = (
    models.Trade
    .select(
        models.Trade.state,
        models.Trade.product,
        fn.SUM(models.Trade.export_value).alias("total_export_value"),
        # Rank products in each state by total export value (descending)
        fn.ROW_NUMBER().over(
            partition_by=[models.Trade.state],
            order_by=[fn.SUM(models.Trade.export_value).desc()]
        ).alias("rn")
    )
    .where(
        models.Trade.origin_country == origin_country,
        models.Trade.year == args["year"]
    )
    .group_by(models.Trade.state, models.Trade.product)
    .alias("ranked_trades")
)

# Step 2: Select only the top-ranked product per state
final_query = (
    ranked_subquery
    .select(
        ranked_subquery.c.state,
        ranked_subquery.c.product,
        ranked_subquery.c.total_export_value
    )
    .from_(ranked_subquery)
    .where(ranked_subquery.c.rn == 1)
)

# Execute and iterate over results
for result in final_query.execute():
    print(f"State: {result.state}, Product: {result.product}, Total: {result.total_export_value}")

2. Subquery Join Approach #

For compatibility with older MySQL versions, use this nested subquery method:

# Step 1: Calculate total export value for each state + product combination
sum_subquery = (
    models.Trade
    .select(
        models.Trade.state,
        models.Trade.product,
        fn.SUM(models.Trade.export_value).alias("total_export_value")
    )
    .where(
        models.Trade.origin_country == origin_country,
        models.Trade.year == args["year"]
    )
    .group_by(models.Trade.state, models.Trade.product)
    .alias("sum_sub")
)

# Step 2: Find the maximum total export value per state
max_subquery = (
    sum_subquery
    .select(
        sum_subquery.c.state,
        fn.MAX(sum_subquery.c.total_export_value).alias("max_total")
    )
    .group_by(sum_subquery.c.state)
    .alias("max_sub")
)

# Step 3: Join to get the product that matches the max total per state
final_query = (
    sum_subquery
    .select(
        sum_subquery.c.state,
        sum_subquery.c.product,
        sum_subquery.c.total_export_value
    )
    .join(
        max_subquery,
        on=(
            (sum_subquery.c.state == max_subquery.c.state) &
            (sum_subquery.c.total_export_value == max_subquery.c.max_total)
        )
    )
)

# Execute the query
results = final_query.execute()

Why Your Original Query Failed #

Your original code grouped the main query by state only, then tried to pull product from the base Trade table. This doesn't work because:

• Without linking the max value back to its specific product, MySQL will either return a random product (if ONLY_FULL_GROUP_BY is disabled) or throw an error (if enabled).
• The join condition only matched state + product, but grouping by state broke the link between the max value and the correct product.

The solutions above ensure we first calculate all state-product totals, then explicitly map the max total back to its corresponding product.

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