基于历史日销数据拆分月度预算至日度：能否用Google BigQuery ML实现？

可以，利用往年日度销售数据的时序规律、日度波动特征，结合Google BigQuery ML的时序或回归模型，能精准将月度预算拆分到日度维度。以下是基于BigQuery ML的具体操作步骤：

一、数据准备与预处理 #

• 整理历史数据：将往年日度销售数据（含日期、销售额、星期标识、节假日/促销标记等字段）导入BigQuery表，例如命名为project.dataset.historical_daily_sales
• 计算基准占比：先统计历史每个月内，单日销售额占当月总销售额的比例，作为模型的初始参考基线
• 导入预算数据：将已拆分至月度的年度预算表导入BigQuery，例如project.dataset.monthly_budget，包含月份、月度预算金额字段

二、构建训练特征集 #

在BigQuery中创建训练视图，生成模型所需的结构化特征：

CREATE OR REPLACE VIEW project.dataset.sales_training_features AS
SELECT
  DATE_TRUNC(sale_date, MONTH) AS month,
  sale_date,
  EXTRACT(DAYOFWEEK FROM sale_date) AS day_of_week,
  EXTRACT(DAY FROM sale_date) AS day_of_month,
  -- 标记周末/工作日
  IF(EXTRACT(DAYOFWEEK FROM sale_date) IN (1,7), 1, 0) AS is_weekend,
  -- 历史单日销售额占当月的比例
  sales / SUM(sales) OVER(PARTITION BY DATE_TRUNC(sale_date, MONTH)) AS daily_sales_ratio
FROM project.dataset.historical_daily_sales;

三、训练BigQuery ML模型 #

推荐使用ARIMA_PLUS时序模型，它能自动捕捉日度、月度的季节性波动；若需结合促销、节假日等外部特征，可改用线性回归模型。

1. ARIMA_PLUS时序模型（优先选择） #

CREATE OR REPLACE MODEL project.dataset.daily_budget_forecast_model
OPTIONS(
  MODEL_TYPE='ARIMA_PLUS',
  TIME_SERIES_TIMESTAMP_COL='sale_date',
  TIME_SERIES_DATA_COL='daily_sales_ratio',
  TIME_SERIES_ID_COL='month', -- 按月份分组建模
  HOLIDAY_REGION='CN' -- 根据业务所在地区调整，如'US'、'EU'
) AS
SELECT sale_date, daily_sales_ratio, month
FROM project.dataset.sales_training_features;

2. 线性回归模型（适配多特征场景） #

CREATE OR REPLACE MODEL project.dataset.daily_budget_regression_model
OPTIONS(MODEL_TYPE='LINEAR_REG') AS
SELECT
  day_of_week,
  day_of_month,
  is_weekend,
  daily_sales_ratio AS label,
  month
FROM project.dataset.sales_training_features;

四、预测日度占比并计算最终日度预算 #

利用训练好的模型预测目标年度的日度占比，再结合月度预算计算单日预算金额：

WITH target_year_dates AS (
  -- 生成目标年度的所有日期
  SELECT DATE_ADD('2024-01-01', INTERVAL i DAY) AS target_date
  FROM UNNEST(GENERATE_ARRAY(0, 365)) i
  WHERE DATE_ADD('2024-01-01', INTERVAL i DAY) <= '2024-12-31'
),
predicted_daily_ratios AS (
  SELECT
    target_date,
    DATE_TRUNC(target_date, MONTH) AS month,
    forecast_value AS predicted_ratio
  FROM ML.FORECAST(
    MODEL project.dataset.daily_budget_forecast_model,
    STRUCT(365 AS horizon) -- 预测全年日度占比
  )
)
SELECT
  pr.target_date,
  ROUND(mb.monthly_budget * pr.predicted_ratio, 2) AS daily_budget
FROM predicted_daily_ratios pr
JOIN project.dataset.monthly_budget mb
ON pr.month = mb.month
ORDER BY pr.target_date;

五、验证与优化 #

• 历史回测：选取某一年的月度预算，用模型拆分日度后与实际日度销售额对比，计算MAE、RMSE等误差指标评估模型效果
• 特征迭代：若误差较大，可加入促销活动标记、行业淡旺季系数等特征重新训练模型
• 人工修正：对已知的大型促销日、特殊节假日，可手动调整模型预测的占比，确保预算符合业务实际

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