Oracle分析函数技术问询：基于半年周期计算进口单价

Got it, let's walk through this solution clearly. First, let's confirm the core goal: you want to compute the import unit price grouped by your defined six-month periods (Jan-Jun = period 1, Jul-Dec = period 2) using Oracle's analytic functions. Since unit price is total CIF (import value) divided by total quantity for the period, here's how to build this step by step.

Step 1: Assign the Six-Month Period to Each Record #

First, we need to tag each row with its correct period. We'll use EXTRACT(MONTH) to pull the month from your import date column, then a CASE statement to map it to period 1 or 2:

CASE 
    WHEN EXTRACT(MONTH FROM import_date) BETWEEN 1 AND 6 THEN 1
    ELSE 2
END AS six_month_period

Step 2: Use Analytic Functions to Compute Period-Level Totals #

Next, we'll use window functions to calculate total CIF and total quantity for each period. Then we can divide those totals to get the period's unit price. Here's the full query, including safeguards against division by zero:

SELECT 
    import_id,
    import_date,
    cif_value,
    quantity,
    -- Assign the six-month period
    CASE 
        WHEN EXTRACT(MONTH FROM import_date) BETWEEN 1 AND 6 THEN 1
        ELSE 2
    END AS six_month_period,
    -- Total CIF value for the entire period
    SUM(cif_value) OVER (PARTITION BY six_month_period) AS total_period_cif,
    -- Total quantity imported in the period
    SUM(quantity) OVER (PARTITION BY six_month_period) AS total_period_quantity,
    -- Calculate unit price, handle division by zero to avoid errors
    CASE 
        WHEN SUM(quantity) OVER (PARTITION BY six_month_period) = 0 THEN NULL
        ELSE SUM(cif_value) OVER (PARTITION BY six_month_period) / SUM(quantity) OVER (PARTITION BY six_month_period)
    END AS period_unit_price
FROM 
    Import;

Key Notes for Customization #

• If you need to calculate unit price per additional dimensions (like product type or supplier), just add those columns to the PARTITION BY clause. For example:

  PARTITION BY product_id, six_month_period
  

• If you only want one row per period (instead of every row showing the period's data), you could wrap this in a GROUP BY query instead—but the analytic function approach lets you keep individual row details while still showing period-level metrics.

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