技术问询：如何获取各饮品对应的最大连续迭代次数？

Got it, let's work through this problem together. First, let's align on what we're solving: when you say "最大连续迭代次数", I assume we're targeting the longest streak of consecutive iterations (like sequential version updates, batch runs, etc.) for each drink in your table.

Let's start with a sample table to make this concrete—this helps illustrate the logic clearly:

For this data, Latte's longest consecutive iteration streak is 3 (iterations 4-6), and Cappuccino's is 2 (iterations 2-3).

Method 1: SQL (for database-stored tables) #

If your data lives in a SQL database, window functions are the way to go. Here's a reusable query:

WITH drink_groups AS (
    SELECT 
        "Drink Name",
        "Iteration Number",
        -- Create a group ID that stays consistent for consecutive iterations
        "Iteration Number" - ROW_NUMBER() OVER (PARTITION BY "Drink Name" ORDER BY "Iteration Number") AS group_id
    FROM your_drink_table
)
SELECT 
    "Drink Name",
    MAX(group_size) AS max_consecutive_iterations
FROM (
    SELECT 
        "Drink Name",
        group_id,
        COUNT(*) AS group_size
    FROM drink_groups
    GROUP BY "Drink Name", group_id
) AS group_counts
GROUP BY "Drink Name";

Quick breakdown:

• The ROW_NUMBER() function assigns a sequential number to each iteration per drink. Subtracting this from the iteration number creates a group_id that doesn't change for consecutive values.
• We then count the size of each group, and take the largest count per drink.

Method 2: Python (for pandas DataFrames) #

If you're working with a pandas DataFrame (common for data analysis workflows), here's a clean approach:

import pandas as pd

# Sample DataFrame (replace with your actual data)
drink_data = {
    "Drink Name": ["Latte", "Latte", "Latte", "Latte", "Latte", "Cappuccino", "Cappuccino", "Cappuccino"],
    "Iteration Number": [1, 2, 4, 5, 6, 2, 3, 5]
}
df = pd.DataFrame(drink_data)

# Create groups for consecutive iterations
df["group_id"] = df.groupby("Drink Name")["Iteration Number"].diff().ne(1).cumsum()

# Calculate max streak per drink
max_streaks = df.groupby(["Drink Name", "group_id"]).size() \
                .groupby("Drink Name").max() \
                .reset_index(name="max_consecutive_iterations")

print(max_streaks)

This will output:

Drink Name  max_consecutive_iterations
0  Cappuccino                           2
1        Latte                           3

The key trick here is diff().ne(1).cumsum(): it flags when an iteration isn't consecutive to the previous one, then creates a unique group ID for each streak. We then count streak lengths and grab the maximum per drink.

If your table has edge cases (like non-integer iteration numbers, gaps that should be ignored, or different column names), just tweak the code/query to match your data—feel free to share specifics if you need adjustments!

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