文章/答案/技术大牛

发布

MADlib——基于SQL的数据挖掘解决方案

共 28 篇文章

MADlib——基于SQL的数据挖掘解决方案（20）——时间序列分析之ARIMA

MADlib——基于SQL的数据挖掘解决方案（8）——数据探索之描述性统计

MADlib——基于SQL的数据挖掘解决方案（7）——数据转换之其它转换

MADlib——基于SQL的数据挖掘解决方案（6）——数据转换之矩阵分解

MADlib——基于SQL的数据挖掘解决方案（5）——数据转换之邻近度

MADlib——基于SQL的数据挖掘解决方案（3）——数据类型之向量

MADlib——基于SQL的数据挖掘解决方案（4）——数据类型之矩阵

MADlib——基于SQL的数据挖掘解决方案（26）——聚类之k-means方法

MADlib——基于SQL的数据挖掘解决方案（25）——分类之随机森林

MADlib——基于SQL的数据挖掘解决方案（24）——分类之决策树

MADlib——基于SQL的数据挖掘解决方案（23）——分类之SVM

MADlib——基于SQL的数据挖掘解决方案（22）——分类之朴素贝叶斯

MADlib——基于SQL的数据挖掘解决方案（21）——分类之KNN

MADlib——基于SQL的数据挖掘解决方案（19）——回归之聚类方差

MADlib——基于SQL的数据挖掘解决方案（30）——模型评估之预测度量

MADlib——基于SQL的数据挖掘解决方案（18）——回归之稳健方差

MADlib——基于SQL的数据挖掘解决方案（17）——回归之Cox比例风险回归

MADlib——基于SQL的数据挖掘解决方案（29）——模型评估之交叉验证

MADlib——基于SQL的数据挖掘解决方案（16）——回归之弹性网络回归

MADlib——基于SQL的数据挖掘解决方案（15）——回归之序数回归

MADlib——基于SQL的数据挖掘解决方案（14）——回归之多类回归

MADlib——基于SQL的数据挖掘解决方案（13）——回归之逻辑回归

MADlib——基于SQL的数据挖掘解决方案（12）——回归之广义线性模型

MADlib——基于SQL的数据挖掘解决方案（11）——回归之线性回归

MADlib——基于SQL的数据挖掘解决方案（10）——数据探索之主成分分析

MADlib——基于SQL的数据挖掘解决方案（9）——数据探索之概率统计

MADlib——基于SQL的数据挖掘解决方案（2）——MADlib基础

MADlib——基于SQL的数据挖掘解决方案（1）——数据挖掘入门

清单首页MADlib——基于SQL的数据挖掘解决方案文章详情

清单「MADlib——基于SQL的数据挖掘解决方案」 19/28

MADlib——基于SQL的数据挖掘解决方案（16）——回归之弹性网络回归

用户1148526

一、弹性网络回归简介

    要想理解弹性网络（Elastic Net）回归，正则化是必须要首先知道的，其次是岭回归和Lasso回归，知道了这些，弹性网络回归自然也就明白了。

1. 正则化

    假设利用最小二乘法来做线性回归，最小二乘法回归成功的条件是：

    即上面这个函数（损失函数）达到最小值，可得到最优的拟合参数θ。但是存在这样一种情况，如果我们用来拟合的自变量（特征变量）过多，而且特征变量之间存在很高的相关关系，比如下面这种情况：

    以上两个函数都可以很好的拟合数据，但右边的函数显然有过拟合的嫌疑。为了避免这种情况，有两种方法，第一种方法是舍掉

和

这两个变量，这就是所谓的特征选择，舍弃无用的特征变量。可以人工选择，也可以利用算法来做。但有些时候我们可能并不希望舍弃数据，一方面特征选择有一定的不确定性，另一方面这个过程是比较繁琐的，这种时候我们可以采用第二种方法来解决这一问题：减小

和

的值，即正则化，保留所有特征变量，但减少变量参数的值。例如，要减小

和

的值，我们可以在损失函数的后面加上

：

    如此一来在最小化目标函数时，因为在

和

前面乘了1000这样大的数字，导致

和

的值会非常的小，目标达成。这里我们有选择的让

和

的值变小，实际情况中，我们很难判断哪些特征变量需要正则化，所以一般情况下，我们是对所有的参数都正则化处理：

即目标函数设为

，其中：

是正则项，lambda（λ）为正则参数。需要注意的是，j是从1开始的，这意味着函数的常数项（

）并没有被正则化。所以lambda不能设的太大，否则会导致除了常数项外，所有的参数值都很小，因变量近似等于常数项，出现欠拟合现象。

2. 岭回归与Lasso回归

    岭回归（ridge regression）的目标函数就是上面介绍的

。如果矩阵化的话，也写成：

即最小化损失函数

惩罚函数

。通过修改λ的值控制惩罚项，λ值越高，惩罚越强，因此系数量级降低。

    Lasso回归和岭回归的区别在于惩罚项的不同：

    Lasso回归的惩罚项用的是绝对值（也称为L1正则化），而不是岭回归中的平方（L2正则化）。L2正则化并不具有产生稀疏解的能力，得到的系数仍然需要数据中的所有特征才能计算预测结果，从计算量上来说并没有得到改观。L1正则化能产生稀疏性，稀疏的解除了计算量上的好处之外，更重要的是更具有“可解释性”。

    假设有一个大的数据集，有10000个特征，其中一些特征是相关的。如果我们使用岭回归，它会保留所有特征，系数会收缩。但问题是模型同样复杂，因为还是有10000个特征，这会导致很差的模型性能。若使用Lasso回归，当我们有相关联的变量，它只会保留一个变量，将其它相关联的变量系数设置为0。这可能会导致一些信息的丢失，结果是模型精确度降低。

    实际上，弹性网络回归本质上就是岭回归和Lasso回归的组合，其目标函数为：

    可以通过调整 控制L1和L2惩罚项。弹性网络是一种使用L1和L2先验作为正则化矩阵的线性回归模型。这种组合用于只有很少的权重非零的稀疏模型，比如Lasso回归, 但是又能保持岭回归的正则化属性。

    当多个特征和另一个特征相关的时候弹性网络非常有用。Lasso 倾向于随机选择其中一个，而弹性网络更倾向于选择两个。在实践中，Lasso 和 Ridge 之间权衡的一个优势是它允许在循环过程（Under rotate）中继承 Ridge 的稳定性。

二、MADlib的弹性网络回归相关函数

1. 训练函数

（1）语法

elastic_net_train( tbl_source,  
                   tbl_result,  
                   col_dep_var,  
                   col_ind_var,  
                   regress_family,  
                   alpha,  
                   lambda_value,  
                   standardize,  
                   grouping_col,  
                   optimizer,  
                   optimizer_params,  
                   excluded,  
                   max_iter,  
                   tolerance  
                 )

（2）参数

参数名称	数据类型	描述
tbl_source	TEXT	包含训练数据的源表名。
tbl_result	TEXT	包含模型的输出表名，输出表列如表2所示。
col_dep_var	TEXT	因变量表达式。col_dep_var和col_ind_var可以是有效的PostgreSQL表达式。例如，col_dep_var = 'log(y+1)'、 col_ind_var = 'array[exp(x1), x2, 1/(1+x3)]'。在二项回归情况下，可以使用布尔表达式，如col_dep_var = 'y < 0'。
col_ind_var	TEXT	自变量表达式。使用‘*’指定tbl_source中除以下描述中的列以外的所有列。如果col_dep_var是列名，它自动排除在自变量之外。如果col_dep_var是一个PostgreSQL表达式，表达式中用到的列名，只有显式出现在excluded参数中才被排除。因此，比较好的做法是将因变量表达式中所含的列名都添加到excluded参数的字符串中。
regress_family	TEXT	指定回归类型，有效值为‘gaussian’（‘linear’）线性回归或‘binomial’（‘logistic’）二项回归。
alpha	FLOAT8	弹性网络控制参数，取值范围是0, 1。1表示L1正则化，0表示L2正则化。该参数是超参，其值并不是自动从模型中学习得到而是手动设置。
lambda_value	FLOAT8	指定正则化参数，必须是正数。该参数也是超参数。
standardize（可选）	BOOLEAN	指定是否规范化数据，缺省值为TRUE。设置为TRUE通常能产生更好的结果和更快的收敛速度。
grouping_col（可选）	TEXT	缺省值为NULL。可以指定单列或逗号分隔的多列，将输入数据按列划分为离散组，每组执行一个回归。缺省不使用分组，全部数据生成单一模型。该参数当前不支持表达式。
optimizer（可选）	TEXT	优化器名称，可指定为‘fista’或‘igd’，缺省为‘fista’。
optimizer_params（可选）	TEXT	指定逗号分隔的优化参数，缺省为NULL。这些参数根据优化器参数的值而有所不同，后面将详细描述。
excluded（可选）	TEXT	逗号分隔的列名，缺省值为NULL。当col_ind_var入参为‘*’时，可从特征列中排除此参数中的列。
max_iter（可选）	INTEGER	缺省值为1000，指定允许的最大迭代次数。
tolerance	FLOAT8	缺省值为1e-6，指定停止迭代的条件。无论是‘fista’还是‘igd’优化器，如果两次连续迭代的对数似然值之差小于此参数值，或者迭代次数超过max_iter参数的限制，计算结束。

表1 elastic_net_train函数参数说明

列名	数据类型	描述
family	TEXT	回归类型，‘gaussian’或‘binomial’。
features	TEXT[]	传给算法的特征（自变量）数组。
features_selected	TEXT[]	算法选择的特征数组。
coef_nonzero	FLOAT8[]	选定特征的回归系数。
coef_all	FLOAT8[]	所有特征的回归系数。
intercept	FLOAT8	模型截距。所有自变量为0时，因变量的值。
log_likelihood	FLOAT8	算法产生的对数似然值。
standardize	BOOLEAN	如果数据已被规范化，将被设置为TRUE。
iteration_run	INTEGER	执行的迭代次数。

表2 elastic_net_train函数输出表列说明

2. 优化参数

    前面提到的optimizer\_params参数可分为预热、交叉验证和优化三类。该参数值为逗号分隔的名值对字符串，字符串用$$括起来，其中所有命名参数都是可选的，并且具有“<param\_name>= <value>”格式。

（1）预热参数

$$  
  warmup = <value>,  
  warmup_lambdas = <value>,  
  warmup_lambda_no = <value>,  
  warmup_tolerance = <value>  
$$

warmup：缺省值为FALSE。如果warmup设置为TRUE，使用一系列严格递减的lambda值，以用户希望计算的lambda值的结束。大的lambda值了给出一个稀疏解决方案，作为下一轮lambda解决方案的初始猜测。对于大数据集，这有时会加速整个计算过程，并且实际上可能比只计算单一lambda值还快。
warmup_lambdas：缺省为NULL。当warmup为TRUE时，设置lambda值。此参数为NULL时，lambda值将自动生成。
warmup_lambda_no：缺省值是15，指定预热使用的lambda值个数。如果warmup_lambdas为NULL，此参数值将被自动生成的lambda值的数目覆盖。
warmup_tolerance：预热时使用的容忍值，缺省与训练函数的tolerance参数相同。

（2）交叉验证参数

    出于性能考虑，如果使用交叉验证，则预热被禁用。而且交叉验证不支持分组。

$$  
  n_folds = <value>,  
  validation_result = <value>,  
  lambda_value = <value>,  
  n_lambdas = <value>,  
  alpha = <value>  
$$

    超参数（lambda和alpha）优化可以使用内置的交叉验证进行，这通过给n\_folds参数分配一个大于1的整数来激活。交叉验证参数应该提供一个列表。例如，为了用L1范数进行正则化，并且使用集合{0.3, 0.4, 0.5}中的lambda值，则参数中应包含'lambda\_value={0.3, 0.4,0.5}'，注意这里‘{}’与‘[]’符合都是有效的。

n_folds：缺省值为0，指定折数（k）。为激活交叉验证，该值应大于1。如果该值大于2，每折用作一次验证集，而其它K - 1折形成训练集。
validation_result：缺省值为NULL，指定存储交叉验证结果的表名。只有此参数值非NULL时才会创建结果表，结果包括参数值与相应的平均误差。
lambda_value：缺省值为NULL，设置用于交叉验证的lambda值。缺省为NULL时，lambda值将自动生成。
n_lambdas：缺省值是15。交叉验证使用的lambda值个数。如果lambda_value参数中没有给出lambda值，会自动生成此参数给出的lambda集合。如果lambda_value参数值非空，此参数被提供的lambda值个数所覆盖。注意，如果只想交叉验证alpha而不是lambda，那么设置lambda_value为NULL，并且设置n_lambdas等于0。此时在下一个参数中指定的alpha值的集合上做交叉验证，所使用的lambda值将是训练函数调用中指定的值。
alpha：弹性网络控制参数，是提供给交叉验证的一个值列表。注意alpha值不会自动生成，如果没有指定，所使用的alpha值将是训练函数调用中指定的值。

（3）优化参数

    FISTA优化器参数

$$  
  max_stepsize = <value>,  
  eta = <value>,  
  use_active_set = <value>,  
  activeset_tolerance = <value>,  
  random_stepsize = <value>  
$$

max_stepsize：缺省值是4.0，指定初始回溯步长。在每次迭代中，算法首先尝试步长 = max_stepsize，如果它不起作用，则尝试小一些的步长，步长 = 步长/eta，其中eta必须大于1。使用大步长会显著加快计算速度，并使总的迭代次数最小化。一个仔细选择的步长可能使计算时间减少10倍以上。
eta：缺省值为2.0，必须大于1。
use_active_set：缺省值为FALSE，如果设置为TRUE，使用有效集法（active-set method）加快计算速度。通过围绕特征有效集（非0回归系数）进行迭代，将获得相当大的加速提升。经过一个完整的循环处理所有变量后，只迭代有效集直到收敛。如果下一个循环不改变有效集迭代结束，否则重复此过程。
activeset_tolerance：计算有效集时使用的容忍值，缺省与训练函数的tolerance参数值相同。
random_stepsize：缺省为FALSE，是否给步长增加一些随机性，有时这可以加速计算。

    IGD优化器参数

$$  
    stepsize = <value>,  
    step_decay = <value>,  
    threshold = <value>,  
    parallel = <value>  
$$

stepsize：步长，缺省0.01。
step_decay：当前实际使用的步长是（上次步长）/exp（step_decay）。缺省值为0，意味着此时IGD中使用的步长为个常量。
threshold：缺省为1e-10。当系数非常小时，可以设置此参数为0。由于SGD的随机性，对于拟合系数，我们只能得到非常小的值。因此计算结束时需要阈值来筛选极小值，并把它们强制设为零。按下面的步骤实现：（1）将每个系数乘以相应特征的标准差；（2）计算重定系数绝对值的平均值；（3）用平均值除以每个重定系数，如果结果的绝对值小于threshold参数值，设置原始系数为0。
parallel：指定是否在多个段上执行并行计算，缺省为TRUE。

3. 预测函数

（1）元组预测

    此预测函数对线性回归返回FLOAT类型的值，对二项回归返回布尔值。预测函数（elastic\_net\_gaussian\_predict()和elastic\_net\_binomial\_predict()）语法如下：

elastic_net_<family>_predict(  
                     coefficients,  
                     intercept,  
                     ind_var  
                   )

    参数：

coefficients：FLOAT8[]类型，拟合系数，通常指定为模型表的coef_all或coef_nonzero列。
intercept：FLOAT8[]类型，模型截距。
ind_var：FLOAT8[]类型，系数对应的自变量。对于coef_all，使用模型结果表中的features列。对于coef_nonzero使用模型结果表中的features_selected中的列。

    对于二项回归，elastic\_net\_binomial\_prob()函数输出实例为真的概率：

elastic_net_binomial_prob(  
                     coefficients,  
                     intercept,  
                     ind_var  
                   )

（2）表预测

    也可以使用另一个预测函数，将预测结果保存在一个表中，这在将弹性网络与普通交叉验证函数一同使用时很有用。语法如下：

elastic_net_predict( tbl_model,  
                     tbl_new_sourcedata,  
                     col_id,  
                     tbl_predict  
                   )

    参数：

tbl_model：TEXT类型，包含模型的训练函数输出表名。
tbl_new_sourcedata：TEXT类型，包含测试数据的表名。
col_id：TEXT类型，行ID列。
tbl_predict：TEXT类型，存储预测结果的表名。

    这里不需要指定“linear”或“logistic”回归类型，因为模型中已经包含此信息。

三、简单示例

1. 获取联机帮助

select madlib.elastic_net_train();

2. 创建测试表并生成数据

-- 房屋价格表  
drop table if exists houses;  
create table houses ( id int,           -- 逻辑主键  
                      tax int,          -- 税金  
                      bedroom int,      -- 卧室数  
                      bath float,       -- 卫生间数  
                      price int,        -- 价格  
                      size int,         -- 使用面积  
                      lot int,          -- 占地面积  
                      zipcode int       -- 邮编  
                      );  
insert into houses (id, tax, bedroom, bath, price, size, lot, zipcode) values  
(1  ,  590 ,       2 ,    1 ,  50000 ,  770 , 22100  , 94301),  
(2  , 1050 ,       3 ,    2 ,  85000 , 1410 , 12000  , 94301),  
(3  ,   20 ,       3 ,    1 ,  22500 , 1060 ,  3500  , 94301),  
(4  ,  870 ,       2 ,    2 ,  90000 , 1300 , 17500  , 94301),  
(5  , 1320 ,       3 ,    2 , 133000 , 1500 , 30000  , 94301),  
(6  , 1350 ,       2 ,    1 ,  90500 ,  820 , 25700  , 94301),  
(7  , 2790 ,       3 ,  2.5 , 260000 , 2130 , 25000  , 94301),  
(8  ,  680 ,       2 ,    1 , 142500 , 1170 , 22000  , 94301),  
(9  , 1840 ,       3 ,    2 , 160000 , 1500 , 19000  , 94301),  
(10 , 3680 ,       4 ,    2 , 240000 , 2790 , 20000  , 94301),  
(11 , 1660 ,       3 ,    1 ,  87000 , 1030 , 17500  , 94301),  
(12 , 1620 ,       3 ,    2 , 118600 , 1250 , 20000  , 94301),  
(13 , 3100 ,       3 ,    2 , 140000 , 1760 , 38000  , 94301),  
(14 , 2070 ,       2 ,    3 , 148000 , 1550 , 14000  , 94301),  
(15 ,  650 ,       3 ,  1.5 ,  65000 , 1450 , 12000  , 94301),  
(16 ,  770 ,       2 ,    2 ,  91000 , 1300 , 17500  , 76010),  
(17 , 1220 ,       3 ,    2 , 132300 , 1500 , 30000  , 76010),  
(18 , 1150 ,       2 ,    1 ,  91100 ,  820 , 25700  , 76010),  
(19 , 2690 ,       3 ,  2.5 , 260011 , 2130 , 25000  , 76010),  
(20 ,  780 ,       2 ,    1 , 141800 , 1170 , 22000  , 76010),  
(21 , 1910 ,       3 ,    2 , 160900 , 1500 , 19000  , 76010),  
(22 , 3600 ,       4 ,    2 , 239000 , 2790 , 20000  , 76010),  
(23 , 1600 ,       3 ,    1 ,  81010 , 1030 , 17500  , 76010),  
(24 , 1590 ,       3 ,    2 , 117910 , 1250 , 20000  , 76010),  
(25 , 3200 ,       3 ,    2 , 141100 , 1760 , 38000  , 76010),  
(26 , 2270 ,       2 ,    3 , 148011 , 1550 , 14000  , 76010),  
(27 ,  750 ,       3 ,  1.5 ,  66000 , 1450 , 12000  , 76010);

3. 训练模型

    本次培训lambda值指定为1，alpha为0.5，实际并没有抑制过渡拟合。

drop table if exists houses_en, houses_en_summary;  
select madlib.elastic_net_train( 'houses',                  -- 源表  
                                 'houses_en',               -- 结果表  
                                 'price',                   -- 因变量  
                                 'array[tax, bath, size]',  -- 自变量  
                                 'gaussian',                -- 回归类型  
                                 0.5,                       -- alpha值  
                                 1,                         -- lambda值  
                                 true,                      -- 标准化数据  
                                 null,                      -- 分组列  
                                 'fista',                   -- 优化器  
                                 '',                        -- 优化参数  
                                 null,                      -- 排除列  
                                 1000,                      -- 最大迭代次数  
                                 1e-6                       -- 容忍值  
                               );

4. 查看结果模型

\x on  
select * from houses_en;

    结果：

-[ RECORD 1 ]-----+--------------------------------------------  
family            | gaussian  
features          | {tax,bath,size}  
features_selected | {tax,bath,size}  
coef_nonzero      | {19.1307716652,14331.3082101,40.4478161865}  
coef_all          | {19.1307716652,14331.3082101,40.4478161865}  
intercept         | 12944.5112825  
log_likelihood    | -741004029.774  
standardize       | t  
iteration_run     | 263

    迭代了263次，函数收敛。

5. 使用预测函数评估残差

\x off  
select id, price, predict, price - predict as residual, round(abs((price - predict) / price * 100)::numeric,4)  as residual_pct  
from (  
    select  
        houses.*,  
        madlib.elastic_net_gaussian_predict(  
            m.coef_all,             -- 全部拟合系数  
            m.intercept,            -- 模型截距  
            array[tax,bath,size]    -- 系数对应的特征列  
            ) as predict  
    from houses, houses_en m) s  
order by id;

    结果：

 id | price  |     predict      |     residual     | residual_pct 
----+--------+------------------+------------------+--------------
  1 |  50000 |  69707.793238673 | -19707.793238673 |      39.4156
  2 |  85000 | 118725.858774125 | -33725.858774125 |      39.6775
  3 |  22500 |  70533.120083594 | -48033.120083594 |     213.4805
  4 |  90000 | 110833.060093874 | -20833.060093874 |      23.1478
  5 | 133000 | 127531.470580514 |   5468.529419486 |       4.1117
  6 |  90500 |   86269.57051355 |    4230.42948645 |       4.6745
  7 | 260000 | 188301.483230903 |  71698.516769097 |      27.5764
  8 | 142500 |  87608.689163141 |  54891.310836859 |      38.5202
  9 | 160000 | 137479.471846418 |  22520.528153582 |      14.0753
 10 | 240000 | 224857.774590971 |  15142.225409029 |       6.3093
 11 |  87000 | 100694.151128927 | -13694.151128927 |      15.7404
 12 | 118600 | 123158.748033449 |  -4558.748033449 |       3.8438
 13 | 140000 |  172100.67635306 |  -32100.67635306 |      22.9291
 14 | 148000 | 158233.248348839 | -10233.248348839 |       6.9144
 15 |  65000 | 105525.808650455 | -40525.808650455 |      62.3474
 16 |  91000 | 108919.982927354 | -17919.982927354 |      19.6923
 17 | 132300 | 125618.393413994 |   6681.606586006 |       5.0503
 18 |  91100 |   82443.41618051 |    8656.58381949 |       9.5023
 19 | 260011 | 186388.406064383 |  73622.593935617 |      28.3152
 20 | 141800 |  89521.766329661 |  52278.233670339 |      36.8676
 21 | 160900 | 138818.625862982 |  22081.374137018 |      13.7237
 22 | 239000 | 223327.312857755 |  15672.687142245 |       6.5576
 23 |  81010 |  99546.304829015 | -18536.304829015 |      22.8815
 24 | 117910 | 122584.824883493 |  -4674.824883493 |       3.9647
 25 | 141100 |  174013.75351958 |  -32913.75351958 |      23.3265
 26 | 148011 | 162059.402681879 | -14048.402681879 |       9.4915
 27 |  66000 | 107438.885816975 | -41438.885816975 |      62.7862
(27 rows)

    可以看到，预测与实际值之差还是比较大的。主要原因是我们没有大的特征集。弹性回归一般在有大数据集的时候工作得很好。

四、分组示例

1. 按邮编分组训练模型

    本次训练除了增加邮编分组列，其它参数与前一次训练相同。

drop table if exists houses_en1, houses_en1_summary;  
select madlib.elastic_net_train( 'houses',                  -- 源表  
                                 'houses_en1',              -- 结果表  
                                 'price',                   -- 因变量  
                                 'array[tax, bath, size]',  -- 自变量  
                                 'gaussian',                -- 回归类型  
                                 0.5,                       -- alpha值  
                                 1,                         -- lambda值  
                                 true,                      -- 标准化数据  
                                 'zipcode',                 -- 分组列  
                                 'fista',                   -- 优化器  
                                 '',                        -- 优化参数  
                                 null,                      -- 排除列  
                                 1000,                      -- 最大迭代次数  
                                 1e-6                       -- 容忍值  
                               );

2. 查看每个分组的模型

\x on  
select * from houses_en1;

    结果：

-[ RECORD 1 ]-----+--------------------------------------------  
zipcode           | 94301  
family            | gaussian  
features          | {tax,bath,size}  
features_selected | {tax,bath,size}  
coef_nonzero      | {18.3256413727,15278.5402561,33.2539221347}  
coef_all          | {18.3256413727,15278.5402561,33.2539221347}  
intercept         | 23185.4057684  
log_likelihood    | -758812991.622  
standardize       | t  
iteration_run     | 1000  
-[ RECORD 2 ]-----+--------------------------------------------  
zipcode           | 76010  
family            | gaussian  
features          | {tax,bath,size}  
features_selected | {tax,bath,size}  
coef_nonzero      | {12.715063895,11840.1947579,31.8858681578}  
coef_all          | {12.715063895,11840.1947579,31.8858681578}  
intercept         | 40625.1513799  
log_likelihood    | -830719826.169  
standardize       | t  
iteration_run     | 1000

    每组生成一个模型，两组的迭代次数都达到1000。

3. 预测残差

\x off  
drop table if exists houses_en1_prediction;  
select madlib.elastic_net_predict(  
                'houses_en1',             -- 模型表  
                'houses',                 -- 数据表  
                'id',                     -- ID列  
                'houses_en1_prediction'   -- 预测结果表  
              );  
  
select  houses.id,  
        houses.price,  
        houses_en1_prediction.prediction,  
        houses.price - houses_en1_prediction.prediction as residual,  
        round(abs((houses.price - houses_en1_prediction.prediction)/houses.price*100)::numeric,4) as residual_pct  
from houses_en1_prediction, houses  
where houses.id = houses_en1_prediction.id order by id;

    结果：

 id | price  |    prediction    |     residual      | residual_pct 
----+--------+------------------+-------------------+--------------
  1 |  50000 |  74881.594478112 |  -24881.594478112 |      49.7632
  2 |  85000 | 119872.439931862 |  -34872.439931862 |      41.0264
  3 |  22500 |  74079.616314736 |  -51579.616314736 |     229.2427
  4 |  90000 | 112915.893049959 |  -22915.893049959 |      25.4621
  5 | 133000 | 127813.216094614 |  5186.78390538601 |       3.8998
  6 |  90500 |  90471.778028099 |  28.2219719010027 |       0.0312
  7 | 260000 | 183341.149985394 |   76658.850014606 |      29.4842
  8 | 142500 |  89832.471055535 |   52667.528944465 |      36.9597
  9 | 160000 | 137342.549608418 |   22657.450391582 |      14.1609
 10 | 240000 | 213959.289287949 |   26040.710712051 |      10.8503
 11 |  87000 | 103136.050501923 |  -16136.050501923 |      18.5472
 12 | 118600 | 124997.427972749 | -6397.42797274899 |       5.3941
 13 | 140000 | 169078.877493042 |  -29078.877493042 |      20.7706
 14 | 148000 | 158498.683486974 |  -10498.683486974 |       7.0937
 15 |  65000 |  106233.07014012 |   -41233.07014012 |      63.4355
 16 |  91000 |  115547.76869999 |   -24547.76869999 |      26.9756
 17 | 132300 |   127646.7210843 |  4653.27891569999 |       3.5172
 18 |  91100 |  93234.081506446 |   -2134.081506446 |       2.3426
 19 | 260011 | 172346.059328314 |   87664.940671686 |      33.7159
 20 | 141800 |  99689.561720526 |   42110.438279474 |      29.6971
 21 | 160900 |  136420.11517185 |    24479.88482815 |      15.2143
 22 | 239000 | 199041.343077962 |   39958.656922038 |      16.7191
 23 |  81010 | 105651.892572334 |  -24641.892572334 |      30.4183
 24 | 117910 |    124379.827686 |      -6469.827686 |       5.4871
 25 | 141100 | 161112.873317428 |  -20012.873317428 |      14.1835
 26 | 148011 |  154432.02633984 |    -6421.02633984 |       4.3382
 27 |  66000 |  114156.25026681 |   -48156.25026681 |      72.9640
(27 rows)

    本次的预测结果同样较大。

五、比较coef_nonzero与coef_all

1. 用L1正则化和大的lambda值（30000）预测模型

drop table if exists houses_en2, houses_en2_summary;  
select madlib.elastic_net_train( 'houses',                  -- 源表  
                                 'houses_en2',              -- 结果表  
                                 'price',                   -- 因变量  
                                 'array[tax, bath, size]',  -- 自变量  
                                 'gaussian',                -- 回归类型  
                                 1,                         -- alpha值  
                                 30000,                     -- lambda值  
                                 true,                      -- 标准化数据  
                                 null,                      -- 分组列  
                                 'fista',                   -- 优化器  
                                 '',                        -- 优化参数  
                                 null,                      -- 排除列  
                                 10000,                     -- 最大迭代次数  
                                 1e-6                       -- 容忍值  
                               );

2. 查看结果模型

\x on  
select * from houses_en2;

     结果：

-[ RECORD 1 ]-----+--------------------------------  
family            | gaussian  
features          | {tax,bath,size}  
features_selected | {tax,size}  
coef_nonzero      | {6.94744249834,29.7137297658}  
coef_all          | {6.94744249834,0,29.7137297658}  
intercept         | 74445.7039382  
log_likelihood    | -1635348585.07  
standardize       | t  
iteration_run     | 151

    可以看到，这次只迭代了151次。与前面的模型不同，由于lambda值足够大，features\_selected中少了bath列，因为其系数已为0。

3. 预测残差

\x off  
select id, price, predict, price - predict as residual, round(abs((price - predict) / price * 100)::numeric,4)  as residual_pct  
from (  
    select  
        houses.*,  
        madlib.elastic_net_gaussian_predict(  
            m.coef_all,                   -- 全部拟合系数  
            m.intercept,                  -- 模型截距  
            array[tax,bath,size]          -- 特征列  
            ) as predict  
    from houses, houses_en2 m) s  
order by id;

4. 用coef_nonzero加速预测

    可以用coef\_nonzero加速预测函数评估残差。这需要检查模型结果表的feature\_selected列，为预测函数提供正确的自变量集合。

\x off  
select id, price, predict, price - predict as residual, round(abs((price - predict) / price * 100)::numeric,4)  as residual_pct  
from (  
    select  
        houses.*,  
        madlib.elastic_net_gaussian_predict(  
            m.coef_nonzero,               -- 非0系数  
            m.intercept,                  -- 模型方差  
            array[tax,size]               -- 对应特征列  
            ) as predict  
    from houses, houses_en2 m) s  
order by id;

    结果：

 id | price  |     predict      |     residual      | residual_pct 
----+--------+------------------+-------------------+--------------
  1 |  50000 | 101424.266931887 | -51424.2669318866 |     102.8485
  2 |  85000 | 123636.877531235 |  -38636.877531235 |      45.4552
  3 |  22500 | 106081.206339915 | -83581.2063399148 |     371.4720
  4 |  90000 | 119117.827607296 | -29117.8276072958 |      32.3531
  5 | 133000 | 128186.922684709 |   4813.0773152912 |       3.6189
  6 |  90500 | 108190.009718915 |  -17690.009718915 |      19.5470
  7 | 260000 | 157119.312909723 |  102880.687090277 |      39.5695
  8 | 142500 | 113935.028663057 |  28564.9713369428 |      20.0456
  9 | 160000 | 131799.592783846 |  28200.4072161544 |      17.6253
 10 | 240000 | 182913.598378673 |  57086.4016213268 |      23.7860
 11 |  87000 | 116583.600144218 | -29583.6001442184 |      34.0041
 12 | 118600 | 122842.722992761 |  -4242.7229927608 |       3.5773
 13 | 140000 | 148278.940070862 | -8278.94007086198 |       5.9135
 14 | 148000 | 134883.191046754 |  13116.8089532462 |       8.8627
 15 |  65000 | 122046.449722531 |  -57046.449722531 |      87.7638
 16 |  91000 | 118423.083357462 | -27423.0833574618 |      30.1353
 17 | 132300 | 127492.178434875 |  4807.82156512521 |       3.6340
 18 |  91100 | 106800.521219247 |  -15700.521219247 |      17.2344
 19 | 260011 | 156424.568659889 |  103586.431340111 |      39.8392
 20 | 141800 | 114629.772912891 |  27170.2270871088 |      19.1609
 21 | 160900 | 132285.913758729 |  28614.0862412706 |      17.7838
 22 | 239000 | 182357.802978806 |   56642.197021194 |      23.6997
 23 |  81010 | 116166.753594318 |  -35156.753594318 |      43.3980
 24 | 117910 | 122634.299717811 | -4724.29971781059 |       4.0067
 25 | 141100 | 148973.684320696 | -7873.68432069599 |       5.5802
 26 | 148011 | 136272.679546422 |  11738.3204535782 |       7.9307
 27 |  66000 | 122741.193972365 |  -56741.193972365 |      85.9715
(27 rows)

    虽然这次只用了两个自变量进行预测，但与前面一个查询的结果相同。同时可以看到，虽然结果模型少了一个特征，但预测误差比lambda=1时更大了，说明可能出现了拟合不足的情况。

六、交叉验证示例

1. 训练时使用交叉验证

    为了找出最佳的lambda值，这次使用3折交叉验证，自动生成lambda。这个训练函数将执行较长时间，因为弹性网络被调用15次。

\x on  
drop table if exists houses_en3, houses_en3_summary, houses_en3_cv;  
select madlib.elastic_net_train( 'houses',                  -- 源表  
                                 'houses_en3',              -- 结果表  
                                 'price',                   -- 因变量  
                                 'array[tax, bath, size]',  -- 自变量  
                                 'gaussian',                -- 回归类型  
                                 1,                         -- alpha值  
                                 1,                         -- lambda值  
                                 true,                      -- 标准化数据  
                                 null,                      -- 分组列  
                                 'fista',                   -- 优化器  
                                 $$ n_folds = 3,            -- 交叉验证参数  
                                    validation_result=houses_en3_cv,  
                                    n_lambdas = 5  
                                 $$,                         
                                 null,                      -- 排除列  
                                 10000,                     -- 最大迭代次数  
                                 1e-6                       -- 容忍值  
                               );  
  
select * from houses_en3;

    结果：

-[ RECORD 1 ]-----+-------------------------------------------  
family            | gaussian  
features          | {tax,bath,size}  
features_selected | {tax,bath,size}  
coef_nonzero      | {22.933010813,9449.64725727,58.1847775893}  
coef_all          | {22.933010813,9449.64725727,58.1847775893}  
intercept         | -10794.876829  
log_likelihood    | -479245790.957  
standardize       | t  
iteration_run     | 157

2. 查看交叉验证细节

\x off  
select round(lambda_value::numeric,4) lambda_value,  
       round(mean::numeric,4) mean,  
       round(std::numeric,4) std  
  from houses_en3_cv order by lambda_value desc;

    结果：

 lambda_value |       mean       |       std         
--------------+------------------+-----------------  
  100000.0000 | -4128231363.7200 | 1221228181.7600  
    5623.4133 | -1222931198.7000 |  103345863.4470  
     316.2278 | -1144722373.6400 |   98220325.5374  
      17.7828 | -1142632269.8300 |  101332958.7120  
       1.0000 | -1142517820.0700 |  101527783.2400  
(5 rows)

    可以看到，因为n\_lambdas=5，所以交叉验证自动产生了5个lambda值，最后的1.0是我们在训练函数中指定的lambda值。当lambda=316时，标准差最小。下面用lambda=316进行训练生成模型，然后用这个模型进行预测，将结果与lambda=1时的模型预测比较。

drop table if exists houses_en4, houses_en4_summary;  
select madlib.elastic_net_train( 'houses',                  -- 源表  
                                 'houses_en4',              -- 结果表  
                                 'price',                   -- 因变量  
                                 'array[tax, bath, size]',  -- 自变量  
                                 'gaussian',                -- 回归类型  
                                 1,                         -- alpha值  
                                 316,                       -- lambda值  
                                 true,                      -- 标准化数据  
                                 null,                      -- 分组列  
                                 'fista',                   -- 优化器  
                                 '',                        -- 优化参数  
                                 null,                      -- 排除列  
                                 10000,                     -- 最大迭代次数  
                                 1e-6                       -- 容忍值  
                               );  
  
\x on  
select * from houses_en4;

    结果：

-[ RECORD 1 ]-----+--------------------------------------------  
family            | gaussian  
features          | {tax,bath,size}  
features_selected | {tax,bath,size}  
coef_nonzero      | {22.7898939713,9142.85771283,57.9936622207}  
coef_all          | {22.7898939713,9142.85771283,57.9936622207}  
intercept         | -9730.60572809  
log_likelihood    | -497109765.778  
standardize       | t  
iteration_run     | 320

    执行预测

\x off  
select id, price, predict, price - predict as residual, round(abs((price - predict) / price * 100)::numeric,4)  as residual_pct  
from (  
    select  
        houses.*,  
        madlib.elastic_net_gaussian_predict(  
            m.coef_all,             -- 全部拟合系数  
            m.intercept,            -- 模型截距  
            array[tax,bath,size]    -- 系数对应的特征列  
            ) as predict  
    from houses, houses_en4 m) s  
order by id;

    结果：

 id | price  |     predict      |     residual      | residual_pct   
----+--------+------------------+-------------------+--------------  
  1 |  50000 |  57513.409337746 |   -7513.409337746 |      15.0268  
  2 |  85000 | 114255.562098622 |  -29255.562098622 |      34.4183  
  3 |  22500 |  61341.331818108 |  -38841.331818108 |     172.6281  
  4 |  90000 | 103774.078339511 |  -13774.078339511 |      15.3045  
  5 | 133000 | 125628.263070736 |  7371.73692926399 |       5.5427  
  6 |  90500 |  77733.411866969 |   12766.588133031 |      14.1067  
  7 | 260000 | 200236.843264003 |   59763.156735997 |      22.9858  
  8 | 142500 |  82761.964683443 |   59738.035316557 |      41.9214  
  9 | 160000 | 137479.007935812 |   22520.992064188 |      14.0756  
 10 | 240000 | 254224.237107707 |  -14224.237107707 |       5.9268  
 11 |  87000 |  96976.948064419 |   -9976.948064419 |      11.4678  
 12 | 118600 | 117966.815706951 |  633.184293048995 |       0.5339  
 13 | 140000 | 181272.626517032 |  -41272.626517032 |      29.4804  
 14 | 148000 | 154763.224373076 | -6763.22437307599 |       4.5697  
 15 |  65000 | 102887.922142515 |  -37887.922142515 |      58.2891  
 16 |  91000 | 101495.088942381 |  -10495.088942381 |      11.5331  
 17 | 132300 | 123349.273673606 |    8950.726326394 |       6.7655  
 18 |  91100 |  73175.433072709 |   17924.566927291 |      19.6757  
 19 | 260011 | 197957.853866873 |   62053.146133127 |      23.8656  
 20 | 141800 |  85040.954080573 |   56759.045919427 |      40.0275  
 21 | 160900 | 139074.300513803 |   21825.699486197 |      13.5648  
 22 | 239000 | 252401.045590003 |  -13401.045590003 |       5.6071  
 23 |  81010 |  95609.554426141 |  -14599.554426141 |      18.0219  
 24 | 117910 | 117283.118887812 |  626.881112187999 |       0.5317  
 25 | 141100 | 183551.615914162 |  -42451.615914162 |      30.0862  
 26 | 148011 | 159321.203167336 |  -11310.203167336 |       7.6415  
 27 |  66000 | 105166.911539645 |  -39166.911539645 |      59.3438  
(27 rows)

    可以看到，本次预测的误差比lambda=1时小。

    MADlib强烈建议在使用大的max\_iter参数在全数据集合上进行训练前，先使用小的max\_iter参数在一个数据子集上运行elastic\_net\_train()函数。在跑全集前调整参数以获得最佳性能，然后再将最佳参数应用到全集训练上。