线性回归 Codelab

线性回归 Codelab

关于此 Codelab

subject上次更新时间:11月 21, 2023
account_circleGoogle 员工编写

1. 简介

在此 Codelab 中,您将学习如何使用线性回归来创建可预测每次点击费用的模型。

前提条件

要完成本 Codelab,您需要足够的高质量广告系列数据来建立模型。

2. 创建临时表

请运行以下查询

CREATE TABLE
 linear_regression_example_data
AS(
 
WITH all_data AS (
   
SELECT
     imp
.user_id as user_id,
     ROW_NUMBER
() OVER(PARTITION BY imp.user_id) AS rowIdx,
     imp
.browser AS browser_name,
     gender_name
AS gender_name,
     age_group_name
AS age_group_name,
     DATETIME
(TIMESTAMP_MICROS(
       imp
.query_id.time_usec), "America/Los_Angeles") as impression_time,
     clk
.advertiser_click_cost_usd AS label
   
FROM adh.google_ads_impressions imp
     
INNER JOIN adh.google_ads_clicks clk USING (impression_id)
     
LEFT JOIN adh.gender ON demographics.gender = gender_id
     
LEFT JOIN adh.age_group ON demographics.age_group = age_group_id
 
)
 
# Need just one user ID or regression won't work
 SELECT
   label
,
   browser_name
,
   gender_name
,
   age_group_name
,
   
# Although BQML could divide impression_time into several useful variables on
   
# its own, it may attempt to divide it into too many features. As a best
   
# practice extract the variables that you think will be most helpful.
   
# The output of impression_time is a number, but we care about it as a
   
# category, so we cast it to a string.
   CAST
(EXTRACT(DAYOFWEEK FROM impression_time) AS STRING) AS day_of_week,
   CAST
(EXTRACT(HOUR FROM impression_time) AS STRING) AS hour,
 FROM
   all_data
 WHERE
   rowIdx
= 1 # This ensures that there'
s only 1 row per user.
   
AND
   label
IS NOT NULL
   
AND
   gender_name
IS NOT NULL
   
AND
   age_group_name
IS NOT NULL
);

3. 创建和训练模型

最佳实践是将表创建步骤与模型创建步骤分开。

对您在上一步中创建的临时表运行以下查询。您不必费心提供开始日期和结束日期,因为系统会根据临时表中的数据推断出这些日期。

CREATE OR REPLACE
MODEL
`example_linear`
OPTIONS
(
   model_type
= 'adh_linear_regression'
)
AS (
   
SELECT *
   
FROM
       tmp
.linear_regression_example_data
);

SELECT * FROM ML.EVALUATE(MODEL `example_linear`)

mean_absolute_error

mean_squared_error

mean_squared_log_error

median_absolute_error

r2_score

explained_variance

1

0.11102380666874107

0.019938972461569476

0.019503393448234131

0.091792024503562136

-9.8205955364568478

-9.7975398794423025