GCP BigQuery回归模型预测失败问题排查求助

看起来你在BigQuery里构建股票tick价格的线性回归模型时，遇到了预测结果不符合预期的问题，我来帮你拆解下核心原因和对应的解决办法：

核心问题分析 #

1. Time >=130000时无结果的原因 #

你的model_data表是从原始股票tick表生成的，而原始表的Time最大值只有124256——根本不存在Time >=130000的记录。当你用这个条件做预测时，没有对应的特征数据（day_prev_Price、trend_3_day）输入给模型，自然返回空结果。

2. Time >=124256时仅返回一行且Price与原表一致的原因 #

这一行是原始表的最后一条历史记录，它已经包含在模型的训练/评估数据集里了。如果你的预测查询是直接对这行历史数据执行ML.PREDICT，线性回归模型对已知数据的拟合结果会非常接近原标签值（也就是原表的Price），但本质上你只是在验证模型对历史数据的拟合能力，而不是预测未来价格。

解决方案：构造未来时间的特征数据 #

要预测未来tick的价格，你需要手动构造未来Time对应的特征值（因为这些特征在原始表中不存在），具体步骤如下：

步骤1：获取最新的历史特征值 #

先从原始数据中提取最新的几条记录，用来计算未来时间点的特征：

WITH latest_4_ticks AS (
  SELECT Time, Price
  FROM `dataset.table`
  ORDER BY Time DESC
  LIMIT 4 -- 计算趋势需要最近3个tick的价格差，所以取4条
)
SELECT
  -- 提取计算趋势所需的价格差
  IF(LAG(Price,1) OVER(ORDER BY Time) - LAG(Price,2) OVER(ORDER BY Time) >0, 1, -1) AS min_1_trend,
  IF(LAG(Price,2) OVER(ORDER BY Time) - LAG(Price,3) OVER(ORDER BY Time) >0, 1, -1) AS min_2_trend,
  IF(LAG(Price,3) OVER(ORDER BY Time) - LAG(Price,4) OVER(ORDER BY Time) >0, 1, -1) AS min_3_trend,
  -- 提取最新的价格作为下一个tick的day_prev_Price
  (SELECT Price FROM latest_4_ticks LIMIT 1) AS latest_price
FROM latest_4_ticks
LIMIT 1

步骤2：构造未来时间的输入数据并执行预测 #

把构造好的未来Time和对应特征传入ML.PREDICT函数，比如预测接下来5个tick的价格：

WITH future_times AS (
  SELECT 124256 + offset AS Time
  FROM UNNEST(GENERATE_ARRAY(1, 5)) -- 生成接下来5个tick的Time
),
latest_features AS (
  SELECT
    IF(min_1_trend + min_2_trend + min_3_trend >0, 1, -1) AS trend_3_day,
    latest_price AS day_prev_Price
  FROM (
    WITH latest_4_ticks AS (
      SELECT Time, Price
      FROM `dataset.table`
      ORDER BY Time DESC
      LIMIT 4
    )
    SELECT
      IF(LAG(Price,1) OVER(ORDER BY Time) - LAG(Price,2) OVER(ORDER BY Time) >0, 1, -1) AS min_1_trend,
      IF(LAG(Price,2) OVER(ORDER BY Time) - LAG(Price,3) OVER(ORDER BY Time) >0, 1, -1) AS min_2_trend,
      IF(LAG(Price,3) OVER(ORDER BY Time) - LAG(Price,4) OVER(ORDER BY Time) >0, 1, -1) AS min_3_trend,
      (SELECT Price FROM latest_4_ticks LIMIT 1) AS latest_price
    FROM latest_4_ticks
    LIMIT 1
  )
)
SELECT
  ft.Time,
  predicted_Price
FROM ML.PREDICT(
  MODEL `dataset.model`,
  (
    SELECT
      ft.Time,
      lf.day_prev_Price,
      lf.trend_3_day
    FROM future_times ft
    CROSS JOIN latest_features lf
  )
)

额外建议 #

• 你用data_split_method='seq'做时间序列拆分是正确的，避免了数据泄露，这点可以保持。
• 线性回归对股票价格这类非线性波动数据的预测效果有限，你可以尝试boosted_tree_reg等非线性模型，或者加入更多特征（比如成交量移动平均线、价格波动率等）提升模型性能。

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