基于特定行条件的列值透视与求和实现方案问询

Absolutely! This transformation is totally feasible using conditional aggregation and pivot operations—exactly the approach you mentioned. The core idea is to first calculate the sum of DailyAvg for Zone IDs 1-10 per day and run, then reshape the data so each run becomes a separate column.

Below are concrete examples using common tools to achieve this:

Using SQL (Conditional Pivot) #

Most SQL databases support pivot functionality, or you can use conditional aggregation for broader compatibility. Here's how to do it:

Step 1: Aggregate the Sum per Day & Run #

First, filter for Zone IDs 1-10 and calculate the total weight for each day-run pair:

SELECT 
    DaysInOp AS Day,
    RunNumber,
    SUM(DailyAvg) AS Weight
FROM your_table_name
WHERE Zone_id BETWEEN 1 AND 10
GROUP BY DaysInOp, RunNumber;

Step 2: Pivot to Columns #

Use the PIVOT clause (syntax varies slightly by database) to turn Run numbers into columns:

SELECT 
    Day,
    [1] AS "Run 1 Weight (lbs)",
    [2] AS "Run 2 Weight (lbs)",
    [3] AS "Run 3 Weight (lbs)",
    [4] AS "Run 4 Weight (lbs)",
    [5] AS "Run 5 Weight (lbs)"
FROM (
    SELECT 
        DaysInOp AS Day,
        RunNumber,
        SUM(DailyAvg) AS Weight
    FROM your_table_name
    WHERE Zone_id BETWEEN 1 AND 10
    GROUP BY DaysInOp, RunNumber
) AS aggregated_data
PIVOT (
    SUM(Weight)
    FOR RunNumber IN ([1], [2], [3], [4], [5])
) AS pivoted_results;

Note: For databases like PostgreSQL that don't have a native PIVOT clause, you can use conditional aggregation with CASE statements instead.

Using Excel Pivot Table #

If you're working with Excel, this is straightforward with a pivot table:

• Insert a pivot table using your dataset.
• Drag DaysInOp to the Rows area (rename to "Day").
• Drag RunNumber to the Columns area (rename each column to "Run X Weight (lbs)").
• Drag DailyAvg to the Values area, then open Value Field Settings to set it to Sum.
• Add a filter on Zone_id (in the Filters area) and select only IDs 1-10.

Using Python Pandas #

For a code-based approach in Python:

import pandas as pd

# Load your data into a DataFrame (adjust the source as needed)
df = pd.read_csv("your_data_file.csv")

# Filter for Zone IDs 1-10
filtered_df = df[df["Zone_id"].between(1, 10)]

# Calculate sum of DailyAvg per Day and Run
grouped = filtered_df.groupby(["DaysInOp", "RunNumber"])["DailyAvg"].sum().reset_index()

# Pivot to get runs as columns
pivoted_df = grouped.pivot(index="DaysInOp", columns="RunNumber", values="DailyAvg").reset_index()

# Rename columns to match your desired format
pivoted_df.columns = ["Day"] + [f"Run {col} Weight (lbs)" for col in pivoted_df.columns[1:]]

# Fill missing values (if any day-run pairs don't exist) with 0
pivoted_df = pivoted_df.fillna(0)

# View the result
print(pivoted_df)

All these methods will produce the exact output format you're looking for, with each row representing a day and columns for each run's total weight.

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