如何用BigQuery ML及dbt实现按分区构建多元线性回归模型

核心思路 #

由于BQML不支持在单个CREATE MODEL语句中按customer_key+category分组创建模型，且循环遍历1亿个分组创建模型的方式无法利用BigQuery的并行计算能力，效率极低。因此直接使用线性回归的代数公式计算斜率是最优方案，可完全借助BigQuery的分布式计算并行处理所有分组，同时适配dbt的SQL模型开发模式。

线性回归中，spend对week_key的斜率公式为：

斜率 = 协方差(week_key, spend) / 方差(week_key)

其中：

• 总体协方差用COVAR_POP，总体方差用VAR_POP
• 样本协方差用COVAR_SAMP，样本方差用VAR_SAMP（根据业务需求选择，通常样本统计量更适合非全量数据场景）

BigQuery原生SQL实现 #

WITH grouped_stats AS (
  SELECT
    customer_key,
    category,
    COVAR_SAMP(week_key, spend) AS covar_week_spend,
    VAR_SAMP(week_key) AS var_week
  FROM
    `your_project.your_dataset.your_table`
  GROUP BY
    customer_key,
    category
)
SELECT
  customer_key,
  category,
  -- 处理方差为0的边界情况（同一分组内week_key全部相同）
  CASE WHEN var_week = 0 THEN 0 ELSE covar_week_spend / var_week END AS slope
FROM
  grouped_stats

dbt模型适配实现 #

在dbt中创建模型文件（例如models/analysis/customer_category_spend_slope.sql），内容如下：

{{ config(
  materialized='table',
  partition_by={
    "field": "customer_key",
    "data_type": "int64"
  },
  cluster_by=["category"] -- 根据实际数据分布调整，优化大表查询性能
) }}

WITH grouped_stats AS (
  SELECT
    customer_key,
    category,
    COVAR_SAMP(week_key, spend) AS covar_week_spend,
    VAR_SAMP(week_key) AS var_week
  FROM
    {{ ref('your_source_table') }} -- 替换为你的源表dbt引用
  GROUP BY
    customer_key,
    category
)
SELECT
  customer_key,
  category,
  CASE WHEN var_week = 0 THEN 0 ELSE covar_week_spend / var_week END AS slope
FROM
  grouped_stats

dbt模型优化说明 #

• materialized='table'：将结果存储为物理表，便于后续查询复用
• partition_by和cluster_by：根据数据分布设置分区与聚簇键，提升大表的查询效率
• 边界处理：通过CASE语句避免var_week=0时的除以0报错，此时分组内week_key无变化，斜率设为0

为什么不使用BQML循环建模？ #

1. 性能瓶颈：循环创建1亿个模型无法利用BigQuery的并行计算能力，串行执行会导致耗时极长
2. BQML功能限制：目前BQML不支持按分组批量创建模型，循环方式无官方支持
3. 资源浪费：每个模型都会占用存储资源，1亿个模型的存储成本极高，完全无必要

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