如何构建可处理两种场景的SQL？含成本对象求和与相对值计算

Let’s break this down step by step, starting with clear assumptions about your data structure (adjust as needed for your actual schema). I’ll assume your table is named cost_data with columns: account, entity, cost_object, prd, amount, and is_baseline (a boolean flag for the blue-marked baseline rows).

Scenario 1: Simple Window Function Calculation #

As you noted, this is straightforward with window functions. We first calculate the total amount per cost_object, then compute the relative amount (row value divided by the cost object’s total) grouped by prd, cost_object, account, and entity.

SELECT
    prd,
    cost_object,
    account,
    entity,
    amount,
    SUM(amount) OVER (PARTITION BY cost_object) AS total_cost_object_amount,
    -- Add NULLIF to avoid division by zero errors
    amount / NULLIF(SUM(amount) OVER (PARTITION BY cost_object), 0) AS relative_amount
FROM cost_data;

Scenario 2: Complex Association & Summation #

Since you mentioned more complex rules, let’s assume the common case where relative amounts are calculated against the baseline row(s) for the same account, entity, and cost_object (tweak the association keys if your actual rule differs).

First, we’ll pre-aggregate baseline values, then join them back to the main table to compute relative amounts:

WITH baseline_totals AS (
    SELECT
        account,
        entity,
        cost_object,
        SUM(amount) AS baseline_amount
    FROM cost_data
    WHERE is_baseline = TRUE
    GROUP BY account, entity, cost_object
)
SELECT
    cd.prd,
    cd.cost_object,
    cd.account,
    cd.entity,
    cd.amount,
    bt.baseline_amount,
    -- Handle division by zero
    cd.amount / NULLIF(bt.baseline_amount, 0) AS relative_amount
FROM cost_data cd
JOIN baseline_totals bt
    ON cd.account = bt.account
    AND cd.entity = bt.entity
    AND cd.cost_object = bt.cost_object;

Combining Both Scenarios into One Flexible Query #

To handle both cases in a single SQL, use a parameter (database-specific) or a conditional switch to toggle between logic. Here’s an example using a session parameter:

-- Set your target scenario (1 = window function, 2 = baseline comparison)
SET @scenario = 1;

WITH baseline_totals AS (
    SELECT
        account,
        entity,
        cost_object,
        SUM(amount) AS baseline_amount
    FROM cost_data
    WHERE is_baseline = TRUE
    GROUP BY account, entity, cost_object
)
SELECT
    prd,
    cost_object,
    account,
    entity,
    amount,
    -- Switch comparison total based on scenario
    CASE @scenario
        WHEN 1 THEN SUM(amount) OVER (PARTITION BY cost_object)
        WHEN 2 THEN bt.baseline_amount
    END AS comparison_total,
    -- Calculate relative amount with error handling
    CASE @scenario
        WHEN 1 THEN amount / NULLIF(SUM(amount) OVER (PARTITION BY cost_object), 0)
        WHEN 2 THEN amount / NULLIF(bt.baseline_amount, 0)
    END AS relative_amount
FROM cost_data cd
-- Left join to avoid breaking Scenario 1 if no baseline exists
LEFT JOIN baseline_totals bt
    ON cd.account = bt.account
    AND cd.entity = bt.entity
    AND cd.cost_object = bt.cost_object;

Quick Adjustments for Your Use Case: #

• If Scenario 2 uses different association rules (e.g., matching on prd instead of account), update the JOIN conditions in the CTE.
• For databases that don’t support session variables (like PostgreSQL), replace @scenario with a query parameter or hardcode the scenario value for testing.

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