基于文件大小总和的分组数据生成及总和与文件数计算方法

Alright, let's tackle this problem step by step. You need to group files into batches based on two clear rules: either the total size of the batch reaches or exceeds 10MB, or the number of files in the batch hits 10. Then, for each batch, you want to track the auto-incrementing batch number, start/end FileIds, and total batch size. Let's break this down with practical examples in both SQL and Python—two common tools for this kind of sequential data processing.

First, let's restate the core requirements and sample data to align:

Sample Input #

Expected Output #

Solution 1: Using SQL (Recursive CTE) #

If your data lives in a database, a recursive Common Table Expression (CTE) is a clean way to handle this sequential grouping logic. Here's a working implementation:

WITH ranked_files AS (
    -- Assign row numbers to ensure we process files in FileId order
    SELECT 
        FileId, 
        FileSize,
        ROW_NUMBER() OVER (ORDER BY FileId) AS rn
    FROM files
),
batch_recursion AS (
    -- Initialize with the first file
    SELECT 
        rn,
        FileId AS StartId,
        FileId AS EndId,
        FileSize AS BatchSize,
        1 AS BatchNo,
        FileSize AS running_total,
        1 AS file_count
    FROM ranked_files
    WHERE rn = 1
    
    UNION ALL
    
    -- Recursively process each subsequent file
    SELECT 
        rf.rn,
        -- Start new batch if conditions are met, else keep current start
        CASE 
            WHEN br.running_total + rf.FileSize >= 10 OR br.file_count >= 9 THEN rf.FileId
            ELSE br.StartId
        END AS StartId,
        rf.FileId AS EndId,
        -- Update batch size: reset if new batch, else add current file size
        CASE 
            WHEN br.running_total + rf.FileSize >= 10 OR br.file_count >= 9 THEN rf.FileSize
            ELSE br.BatchSize + rf.FileSize
        END AS BatchSize,
        -- Increment batch number if new batch
        CASE 
            WHEN br.running_total + rf.FileSize >= 10 OR br.file_count >= 9 THEN br.BatchNo + 1
            ELSE br.BatchNo
        END AS BatchNo,
        -- Update running total for size
        CASE 
            WHEN br.running_total + rf.FileSize >= 10 OR br.file_count >= 9 THEN rf.FileSize
            ELSE br.running_total + rf.FileSize
        END AS running_total,
        -- Update file count: reset if new batch, else increment
        CASE 
            WHEN br.running_total + rf.FileSize >= 10 OR br.file_count >= 9 THEN 1
            ELSE br.file_count + 1
        END AS file_count
    FROM batch_recursion br
    JOIN ranked_files rf ON rf.rn = br.rn + 1
)
-- Select only the final state of each batch
SELECT 
    BatchNo,
    StartId,
    EndId,
    BatchSize
FROM batch_recursion
WHERE rn IN (
    SELECT MAX(rn) FROM batch_recursion GROUP BY BatchNo
)
ORDER BY BatchNo;

How This Works #

1. ranked_files adds row numbers to ensure we process files strictly in FileId order—critical for correct batch sequencing.
2. The recursive CTE starts with the first file, then iterates through each subsequent file:
   • If adding the file would push the batch size to ≥10MB OR the batch would reach 10 files, we split into a new batch.
   • Otherwise, we add the file to the current batch and update the total size/count.
3. Finally, we filter to only keep the last entry of each batch (since the recursion generates a row for every file, we just need the final state per batch).

Solution 2: Using Python #

If you're working with data in a script, a loop-based approach is straightforward and easy to debug:

# Define input data as a list of (FileId, FileSize) tuples
files = [
    (1, 5), (2, 4), (3, 1), (4, 6),
    (5, 8), (6, 1), (7, 7), (8, 2)
]

batches = []
current_batch = {
    "start_id": None,
    "end_id": None,
    "total_size": 0,
    "file_count": 0
}
batch_number = 1

for file_id, size in files:
    if current_batch["start_id"] is None:
        # Initialize the first batch
        current_batch["start_id"] = file_id
        current_batch["end_id"] = file_id
        current_batch["total_size"] = size
        current_batch["file_count"] = 1
    else:
        # Calculate potential new batch state
        new_total = current_batch["total_size"] + size
        new_count = current_batch["file_count"] + 1
        
        # Check if we need to start a new batch
        if new_total >= 10 or new_count >= 10:
            # Save the current batch
            batches.append({
                "BatchNo": batch_number,
                "StartId": current_batch["start_id"],
                "EndId": current_batch["end_id"],
                "BatchSize": current_batch["total_size"]
            })
            # Increment batch number and start fresh
            batch_number += 1
            current_batch = {
                "start_id": file_id,
                "end_id": file_id,
                "total_size": size,
                "file_count": 1
            }
        else:
            # Add the file to the current batch
            current_batch["end_id"] = file_id
            current_batch["total_size"] = new_total
            current_batch["file_count"] = new_count

# Don't forget to add the final batch (it won't trigger the split condition)
batches.append({
    "BatchNo": batch_number,
    "StartId": current_batch["start_id"],
    "EndId": current_batch["end_id"],
    "BatchSize": current_batch["total_size"]
})

# Print results in a readable format
print(f"{'BatchNo':<8} {'StartId':<8} {'EndId':<6} {'BatchSize':<10}")
for batch in batches:
    print(f"{batch['BatchNo']:<8} {batch['StartId']:<8} {batch['EndId']:<6} {batch['BatchSize']:<10}")

How This Works #

• We use a current_batch dictionary to track the state of the batch we're building (start/end IDs, total size, file count).
• We loop through each file in order:
  • If no batch exists, we initialize one with the first file.
  • For each subsequent file, we check if adding it would violate either batch rule. If yes, we save the current batch and start a new one. If not, we update the current batch's state.
• After the loop ends, we add the final batch to our list (since it won't have triggered the split condition).

Key Takeaways #

• Order is non-negotiable: Always process files in FileId order—batches are sequential based on this ID.
• Track state: Both solutions rely on monitoring the current batch's total size and file count to decide when to split.
• Edge case handling: Don't forget the final batch that doesn't meet either split condition—it still needs to be included in the results.

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