如何使用SQL计算特定行的复合指标：(Actual+Forecast)/Target

Absolutely! This is a common scenario in SQL, and it’s totally feasible to compute (Actual + Forecast) / Target given your sample data structure. Here’s how you can pull it off:

Method 1: Conditional Aggregation (Works in All SQL Databases) #

Since your metrics are stored as individual rows (one row per metric type), we can use conditional logic to "pivot" these rows into columns, then run the calculation. This approach works across every major SQL dialect:

SELECT
  -- Sum Actual and Forecast values, then divide by Target
  (MAX(CASE WHEN Metric = 'Actual' THEN Value END) + MAX(CASE WHEN Metric = 'Forecast' THEN Value END)) 
  / MAX(CASE WHEN Metric = 'Target' THEN Value END) AS Calculated_Metric
FROM your_table_name;

Breakdown of the Logic:

• Each CASE statement grabs the value for a specific metric (e.g., only returns the Value when Metric is 'Actual').
• MAX() (or SUM()—either works here since each metric has one value) converts that isolated value into a column-like result.
• We then combine these values with basic arithmetic to get your desired metric.

If your table has grouping dimensions (like dates, product IDs, etc.), just add a GROUP BY clause to compute the metric for each group:

SELECT
  group_column, -- Replace with your actual grouping column(s)
  (MAX(CASE WHEN Metric = 'Actual' THEN Value END) + MAX(CASE WHEN Metric = 'Forecast' THEN Value END)) 
  / MAX(CASE WHEN Metric = 'Target' THEN Value END) AS Calculated_Metric
FROM your_table_name
GROUP BY group_column;

Method 2: Using PIVOT (For Databases That Support It) #

If you’re using a SQL dialect with the PIVOT operator (like SQL Server, Oracle, or PostgreSQL 11+), you can simplify the query by first pivoting rows into columns:

SELECT
  (Actual + Forecast) / Target AS Calculated_Metric
FROM your_table_name
PIVOT (
  MAX(Value) FOR Metric IN (Actual, Forecast, Target)
) AS Pivoted_Data;

This is more readable, but note that PIVOT isn’t universal—conditional aggregation is the safer choice if you need cross-database compatibility.

For your sample data, either method will return (10 + 15)/12 = 25/12 ≈ 2.083.

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