常见数据预处理SQL方法

简介

本文主要介绍几个在风控或者推荐的机器学习项目中数据处理的几种SQL实现方法

保留原始列，并新增一列

很多情况下，对于变换后新增的列，希望它补充到最后一列，而非作为唯一输出。这种情况下，可以采用以下方法。

SQL实现方法如下：

SELECT *,  {其他脚本} FROM t1

注：{其他脚本}例如后文“类型转换”的 CAST (col_name as int) AS col_name_int 即SELECT *, CAST (col_name as int) AS col_name_int FROM t1

类型转换

当您引入数据后，发现字段类型需要转化，例如要从“string”转为“int”，以便特征抽取时作为连续特征使用，那么就可以做类型转换处理。

SQL实现方法如下：

SELECT CAST (col_name as int) AS col_name_int FROM t1

标准化缩放（归一化）

标准化缩放，即将数值变量的均值变为0，方差变为1，对于那些目标变量为输入特征的光滑函数模型，如线性回归，逻辑回归等，其对输入特征的大小很敏感，对特征进行标准化比较有效。 特别地，当我们使用逻辑回归进行分类预测时，若一些数值特别大的特征不提前做归一化处理，往往容易发生模型无法收敛，AUC停留在0.5的情况。

SQL实现方法如下：

SELECT ( col_name - (avg(col_name) over (partition by 1)) )*1.0/( stddev(col_name) over (partition by 1) ) AS col_name_std FROM t1

字符串截断

对于连续型数值特征，有时候太多的精度可能只是噪声，因此需要在保留重要信息的前提下对特征进行截断，需要进行类型转换后结束字符串。

SQL实现方法如下：

SELECT SUBSTR(string(col_name),1,10) AS col_name_trim FROM t1

col_name为double类型，以上操作表示从第1个往后截取10位数字

空值填充

实际问题中经常会遇到数据空值的情况，会影响模型的效果及处理效率，因此需要对空值进行填充处理，包括填充默认值或变量。

SQL实现方法如下：

SELECT if(col_name,avg(col_name)) AS col_name_filled FROM t1

col_name为数值类型，以上操作表示将空值填充为该列的均值

统计行内出现空值的个数

实际问题中会需要直接对行向量进行统计处理，比如统计行向量中空值的个数、正值或负值的个数等。

SQL实现方法如下：

SELECT isnull(col_name_1)+isnull(col_name_2)+..+isnull(col_name_n) AS cnt_null FROM t1