Oracle SQL销售预测技术问询：基于州/城市的月度营收预测问题

问题分析 #

原代码的核心问题在于GROUP BY子句包含了year、id_month和revenue，导致每一条历史数据单独成组，无法跨年份计算回归趋势。REGR_SLOPE和REGR_INTERCEPT需要基于同一客户、州、城市、月份的所有年度数据来拟合线性趋势，错误的分组会直接导致回归计算失效。

修正后的客户级月度预测代码 #

该代码按客户、州、城市、月份分组，基于3年历史数据计算下一年度的月度预期营收：

SELECT 
    Cust_ID,
    State,
    City,
    id_month,
    MAX(year) + 1 AS forecast_year,
    -- 线性回归预测公式：y = 斜率*预测年份 + 截距
    REGR_SLOPE(revenue, year) * (MAX(year) + 1) + REGR_INTERCEPT(revenue, year) AS forecasted_revenue
FROM 
    New_customer_data
GROUP BY 
    Cust_ID, State, City, id_month;

州+城市维度的月度总预估营收 #

如果需要汇总州+城市维度的月度预测值，可以通过子查询先计算客户级预测，再求和：

WITH customer_monthly_forecast AS (
    SELECT 
        Cust_ID,
        State,
        City,
        id_month,
        MAX(year) + 1 AS forecast_year,
        REGR_SLOPE(revenue, year) * (MAX(year) + 1) + REGR_INTERCEPT(revenue, year) AS cust_forecast
    FROM 
        New_customer_data
    GROUP BY 
        Cust_ID, State, City, id_month
)
SELECT 
    State,
    City,
    id_month,
    forecast_year,
    SUM(cust_forecast) AS state_city_monthly_forecast
FROM 
    customer_monthly_forecast
GROUP BY 
    State, City, id_month, forecast_year;

关键说明 #

• 修正后的GROUP BY仅保留核心维度（客户ID、州、城市、月份），确保回归函数能基于该维度下的所有年度数据拟合趋势。
• REGR_SLOPE(revenue, year)计算营收随年份变化的增长斜率，REGR_INTERCEPT计算线性回归的截距，两者结合得到下一年度的预测值。
• 州+城市维度的汇总通过先计算客户级预测再求和，保证数据准确性。

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