基于分区列条件的中位数（Median）计算需求（含特殊分组规则）

Got it, let's break down how to solve this median calculation problem exactly as you described. The core requirements are:

• Only calculate medians for records where Label = 'Tag'
• For records where Item_Name includes C, E, or F: group them by their respective letter (e.g., all "Tag Cx" entries form one group)
• For all other Tag records: group by the combination of Label and Item_Name
• Compute the median Price for each group

I'll cover solutions in both SQL (works for most databases) and Python/Pandas, so you can pick the one that fits your workflow.

SQL Implementation #

Most modern databases support median calculation via percentile functions (since median is the 50th percentile). Here's how to structure the query with custom grouping:

PostgreSQL / SQL Server #

WITH tagged_groups AS (
    SELECT
        Price,
        -- Define custom group keys based on your rules
        CASE
            WHEN Item_Name LIKE '%C%' THEN 'Group C'
            WHEN Item_Name LIKE '%E%' THEN 'Group E'
            WHEN Item_Name LIKE '%F%' THEN 'Group F'
            ELSE CONCAT(Label, '_', Item_Name) -- Group by Label+Item_Name for others
        END AS group_key
    FROM your_table_name
    WHERE Label = 'Tag' -- Filter only Tag records
)
SELECT
    group_key,
    -- Calculate median using 50th percentile continuous value
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Price) AS median_price
FROM tagged_groups
GROUP BY group_key
ORDER BY group_key;

MySQL 8.0+ #

MySQL has a built-in MEDIAN() function for simpler syntax:

WITH tagged_groups AS (
    SELECT
        Price,
        CASE
            WHEN Item_Name LIKE '%C%' THEN 'Group C'
            WHEN Item_Name LIKE '%E%' THEN 'Group E'
            WHEN Item_Name LIKE '%F%' THEN 'Group F'
            ELSE CONCAT(Label, '_', Item_Name)
        END AS group_key
    FROM your_table_name
    WHERE Label = 'Tag'
)
SELECT
    group_key,
    MEDIAN(Price) AS median_price
FROM tagged_groups
GROUP BY group_key
ORDER BY group_key;

Python (Pandas) Implementation #

If you're working with data in a Python environment, Pandas makes this straightforward with custom grouping logic:

import pandas as pd

# Load your data into a DataFrame (example uses your sample data)
df = pd.DataFrame({
    'Label': ['Tag'] * 10,
    'Item_Name': ['Tag C1', 'Tag C2', 'Tag C3', 'Tag E1', 'Tag E2', 'Tag E3', 'Tag E4', 'Tag E5', 'Tag F1', 'Tag F2'],
    'Price': [231, 312, 416, 523, 152, 629, 29, 727, 671, 1002]
})

# Step 1: Filter only Label='Tag' records
tagged_df = df[df['Label'] == 'Tag'].copy()

# Step 2: Create custom group keys
# Use regex to extract C/E/F, else use Label+Item_Name
tagged_df['group_key'] = tagged_df['Item_Name'].str.extract(r'(C|E|F)')
tagged_df['group_key'] = tagged_df['group_key'].fillna(tagged_df['Label'] + '_' + tagged_df['Item_Name'])
# Format group names for C/E/F groups
tagged_df['group_key'] = tagged_df['group_key'].apply(lambda x: f'Group {x}' if len(x) == 1 else x)

# Step 3: Calculate median per group
median_results = tagged_df.groupby('group_key')['Price'].median().reset_index()

# Print the result
print(median_results)

Sample Output for Your Data #

Running the above code on your sample data will give you:

Which matches the expected median values:

• Group C: Sorted prices [231, 312, 416] → median = 312
• Group E: Sorted prices [29, 152, 523, 629, 727] → median = 523
• Group F: Sorted prices [671, 1002] → median = (671 + 1002)/2 = 836.5

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