基于MySQL 8.0.21的FIFO订单分组重量求和查询需求

Hey there! Let's work through this FIFO grouping problem for your orders table. Since you're using Go with GORM and MySQL 8.0, here's a practical approach that gets the job done while keeping performance in mind for your 200k+ rows:

Core Approach #

We'll leverage MySQL 8.0's support for user-defined variables to track running weights and group IDs as we process orders in FIFO order (assuming order_id is auto-incrementing and represents creation order—if not, swap it with a created_at timestamp). Doing the grouping directly in the database avoids loading all 200k+ rows into Go memory at once, which is key for performance.

MySQL Query Implementation #

This query will group orders into batches where each batch's total weight stays under 500, and return each group's order IDs and total weight:

SELECT
    group_id,
    JSON_ARRAYAGG(order_id) AS order_ids,
    SUM(weight) AS total_weight
FROM (
    SELECT
        order_id,
        weight,
        -- Update group ID: stay in current group if adding this order doesn't exceed 500
        @current_group := IF(@current_weight + weight < 500, @current_group, @current_group + 1) AS group_id,
        -- Update running weight: reset to current order's weight if we start a new group
        @current_weight := IF(@current_weight + weight < 500, @current_weight + weight, weight) AS running_weight
    FROM orders
    ORDER BY order_id ASC -- Critical for FIFO: process orders in creation order
    CROSS JOIN (SELECT @current_group := 1, @current_weight := 0) AS init_vars
) AS grouped_orders
GROUP BY group_id
ORDER BY group_id ASC;

Go/GORM Integration #

Define a struct to map the query results, then execute the raw query with GORM:

// Define a struct to hold each group's data
type OrderGroup struct {
    GroupID     int     `gorm:"column:group_id"`
    OrderIDs    []int   `gorm:"column:order_ids"` // GORM will parse JSON array to []int
    TotalWeight float64 `gorm:"column:total_weight"`
}

// Fetch FIFO groups from the database
func GetFIFOOrderGroups(db *gorm.DB) ([]OrderGroup, error) {
    var groups []OrderGroup
    query := `
        SELECT
            group_id,
            JSON_ARRAYAGG(order_id) AS order_ids,
            SUM(weight) AS total_weight
        FROM (
            SELECT
                order_id,
                weight,
                @current_group := IF(@current_weight + weight < 500, @current_group, @current_group + 1) AS group_id,
                @current_weight := IF(@current_weight + weight < 500, @current_weight + weight, weight) AS running_weight
            FROM orders
            ORDER BY order_id ASC
            CROSS JOIN (SELECT @current_group := 1, @current_weight := 0) AS init_vars
        ) AS grouped_orders
        GROUP BY group_id
        ORDER BY group_id ASC;
    `

    // Execute the raw query and scan results into our struct slice
    err := db.Raw(query).Scan(&groups).Error
    return groups, err
}

Performance Tips #

• Add an index: Make sure order_id (or your FIFO sorting column, like created_at) has an index to avoid full-table sorting:

  CREATE INDEX idx_orders_order_id ON orders(order_id);
  

• Filter early: If you only need groups for a subset of orders (e.g., a date range), add a WHERE clause in the inner query to reduce the data processed.
• Batch if needed: If your groups end up being extremely large (thousands of groups), you can modify the query to paginate results using LIMIT and OFFSET, or use a cursor to process groups incrementally.

Note on FIFO Order #

If order_id doesn't represent the actual creation order of orders, replace ORDER BY order_id ASC with ORDER BY created_at ASC (assuming you have a created_at timestamp column), and add an index on created_at as well.

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