乐趣区

关于数据库:深入浅出特征工程-基于-OpenMLDB-的实践指南下

jiezi

1. 筹备常识

在上期系列文章中（深入浅出特色工程 — 基于 OpenMLDB 的实际指南（上）），咱们介绍了特色工程的根底概念、实际工具，以及根本的基于单表的特色脚本开发。在本篇文章中，咱们将基于主表和副表，去开展具体介绍更加简单和弱小的基于多表的特色脚本开发。同时，咱们仍然依靠 OpenMLDB 所提供的 SQL 语法进行特色工程脚本示例，对于 OpenMLDB 的更多信息能够拜访咱们的 GitHub repo，以及文档网站。
OpenMLDB GitHub repo
OpenMLDB 文档 · 看云

如果你想运行本篇教程所举例的 SQL，请依照以下两个步骤做筹备：

  • 举荐应用 OpenMLDB docker 镜像在单机版下运行本教程，运行形式参考 OpenMLDB 疾速上手。如果应用集群版，请应用离线模式（SET @@execute_mode=’offline’）。集群版一般线上模式仅反对简略的数据预览性能，因而无奈运行教程中大部分的 SQL。
  • 本教程相干的所有数据以及导入操作脚本能够在这里下载。

在本篇文章中，咱们将会应用到主表和副表，进行举例说明。咱们仍然应用上篇的反欺诈交易的样例数据，蕴含一张主表用户交易表（表一 t1）和一张副表商户流水表（表二 t2）。须要多表特色工程的背景，是在关系数据库设计中，为了防止数据冗余和一致性，个别都会依照肯定的设计准则（数据库设计范式），把数据存入多个数据表中。在特色工程中，为了取得足够的无效信息，须要在多个表中取出数据，因而须要基于多表进行特色工程。

表一：主表，用户交易表 t1

副表：表二，商户流水表 t2


在传统关系数据库中，为了获得多表的信息，最罕用的形式是应用 join 进行拼接。然而对于特色工程的需要来说，数据库的 join 并不能十分高效的满足需要。最次要的起因是咱们的主表样本表有一个用于模型训练的 label 列，其每一个值只能对应一行数据记录。所以理论中咱们心愿在 join 当前，后果表格的行数须要和主表的行数保持一致。

2. 副表单行特色

2.1 LAST JOIN

OpenMLDB 目前反对 LAST JOIN 来进行相似数据库的 join 操作。LAST JOIN 能够看作一种非凡的 LEFT JOIN。在满足 JOIN 条件的前提下，左表的每一行拼取一条符合条件的最初一行。LAST JOIN 分为无序拼接，和有序拼接。咱们用更简略的表为例，假如表 s1，s2 的 schema 均为

(id int, col1 string, std_ts timestamp)

那么，咱们能够做这样的 join 操作：

-- des c: 基于 ORDER BY 的有序 LAST JOIN 拼接
SELECT * FROM s1 LAST JOIN s2 ORDER BY s2.std_ts ON s1.col1 = s2.col1;

如下图，LAST JOIN 时配置 ORDER BY ，则右表按 std_ts 排序，拼接最初一条命中的数据行。以左表第二行为例，符合条件的右表有 2 条，按 std_ts 排序后，抉择最初一条 3, b, 2020-05-20 10:11:13 。
⬆ LAST JOIN 过程示例


⬆ LAST JOIN 拼接后果

3. 副表多行聚合特色

OpenMLDB 针对副表拼接场景，扩大了规范的 WINDOW 语法，新增了 WINDOW UNION 的个性，反对从副表拼接多条数据造成副表窗口。在副表拼接窗口的根底上，能够不便构建副表多行聚合特色。同样地，结构副表多行聚合特色也须要实现两个步骤：

  • 步骤一：定义副表拼接窗口。
  • 步骤二：在副表拼接窗口上结构副表多行聚合特色。

3.1 步骤一：定义副表拼接窗口

主表的每一个样本行都能够从副表中按某列拼接多行数据，并容许定义拼接数据的工夫区间或者条数区间。咱们通过非凡的窗口语法 WINDOW UNION 来定义副表拼接条件和区间范畴。为了不便了解，咱们将这种的窗口咱们称之为副表拼接窗口。

副表拼接窗口的语法定义为：

window window_name as (UNION other_table PARTITION BY key_col ORDER BY order_col ROWS_RANGE|ROWS BETWEEN StartFrameBound AND EndFrameBound)

其中，最根本的不可或缺的语法元素包含：

  • UNION other_table：other_table 是指进行 WINDOW UNION 的副表。
    主表和副表须要放弃 schema 统一。大部分状况下，主表和副表的 schema 都是不同的。因而，咱们能够通过对主表和副表进行列筛选和默认列配置来保障参加窗口计算的主表和副表 schema 统一。列筛选还能够去掉无用列，只在要害列上做
    WINDOW UNION 和聚合。
  • PARTITION BY key_col: 示意按列 key_col 从副表拼接匹配数据。
  • ORDER BY order_col: 示意副表拼接数据集依照 order_col 列进行排序
  • `ROWS_RANGE BETWEEN StartFrameBound AND
    EndFrameBound`: 示意副表拼接窗口的工夫区间

  • StartFrameBound示意该窗口的上界

    • UNBOUNDED PRECEDING: 无上界。
    • time_expression PRECEDING: 如果是工夫区间，能够定义工夫偏移，如 30d preceding 示意窗口上界为以后行的工夫 -30 天。

    • EndFrameBound示意该工夫窗口的下界。

      • CURRENT ROW：以后行 time_expression
      • PRECEDING: 如果是工夫区间，能够定义工夫偏移，如 1d PRECEDING。这示意窗口下界为以后行的工夫 - 1 天。
  • ROWS BETWEEN StartFrameBound AND EndFrameBound: 示意副表拼接窗口的工夫区间

  • StartFrameBound示意该窗口的上界。

    • UNBOUNDED PRECEDING: 无上界。
    • number PRECEDING: 如果是条数区间，能够定义工夫条数。如，100 PRECEDING示意窗口上界为的以后行的前 100 行。

  • EndFrameBound示意该工夫窗口的下界。

    • CURRENT ROW：以后行
    • number PRECEDING: 如果是条数窗口，能够定义工夫条数。如，`1

    PRECEDING` 示意窗口上界为的以后行的前 1 行。

  • 配置窗口区间界时，请留神:

    • OpenMLDB 目前无奈反对以后行当前的工夫作为上界和下界。如 `1d
      FOLLOWING`。换言之，咱们只能解决历史工夫窗口。这也根本满足大部分的特色工程的利用场景。
    • OpenMLDB 的下界工夫必须 >= 上界工夫
    • OpenMLDB 的下界的条数必须 <= 上界条数
  • INSTANCE_NOT_IN_WINDOW: 标记为副表拼接窗口。主表除了以后行以外，其余数据不进入窗口。

更多语法和个性能够参考 OpenMLDB 窗口 UNION 参考手册。

以下通过具体例子来形容 WINDOW UNION 的拼接窗口定义操作。对于后面所述为用户交易表 t1，咱们须要定义商户流水表 t2 的副表上拼接窗口，该拼接是基于 mid 进行。因为 t1 和 t2 的 schema 不同，所以咱们首先别离从 t1 和 t2 抽取雷同的列，对于不存在的列，能够配置缺省值。其中，mid 列用于两个表的拼接，所以是必须的；其次，工夫戳的列（t1 中的 trans_time ，t2 中的 purchase_time ）蕴含时序信息，在定义工夫窗口时候也是必须的；其余列依照聚合函数须要，进行必要的筛选保留。
以下 SQL 和示意图为从 t1 抽取必要列，生成 t11。

(select id, mid, trans_time as purchase_time, 0.0 as purchase_amt, "" as purchase_type from t1) as t11


⬆ 从 t1 抽取必要列，生成 t11

以下 SQL 和示意图为从 t2 抽取必要列，生成 t22。

(select 0L as id, mid, purchase_time, purchase_amt, purchase_type from t2) as t22


⬆ 从 t2 抽取必要列，生成 t22

能够看到，别离实现抽取当前生成的表格 t11 和 t22，曾经具备了雷同的 schema，两者能够进行逻辑上的 UNION 操作。然而在 OpenMLDB 中，WINDOW UNION 并不是真的为了进行传统数据库中的 UNION 操作，而是为了对于 t11 中的每一个样本行，去构建副表 t22 上的工夫窗口。咱们依照商户 ID mid，对 t11 中的每一行数据，从 t22 中获取对应的拼接数据，而后按生产工夫(purchase_time) 排序，结构副表拼接窗口。比方咱们定义一个 w_t2_10d 的窗口：不蕴含主表除了以后行以外的数据行，加上副表通过 mid 拼接上的十天以内的数据，示意图如下所示。能够看到，黄色和蓝色暗影局部，别离定义了样本 6 和样本 9 的副表拼接窗口。
​
⬆ 副表拼接窗口示例

该窗口定义过程的 SQL 脚本如下所示（留神，这还不是一个残缺的 SQL）：

(SELECT id, mid, trans_time as purchase_time, 0.0 as purchase_amt, "" as purchage_type FROM t1) as t11
window w_t2_10d as (UNION (SELECT 0L as id, mid, purchase_time, purchase_amt, purchase_type FROM t2) as t22 
PARTITION BY mid ORDER BY purchase_time
ROWS_RANGE BETWEEN 10d PRECEDING AND 1 PRECEDING INSTANCE_NOT_IN_WINDOW)

3.2 步骤二：构建副表多行聚合特色

对于副表拼接窗口进行多行聚合函数加工，结构多行副表聚合特色，使得最初生成的行数和主表雷同。以简略聚合函数为例，咱们能够结构样本的副表拼接特色：商户的最近 10 天的零售总额 w10d_merchant_purchase_amt_sum ，商户的最近 10 天生产总次数 w10d_merchant_purchase_count 。以下 SQL 基于下面 3.1 中所定义的副表拼接窗口，构建多行聚合特色。

SELECT 
id, 
-- 样本商户最近 10 天零售总额
sum(purchase_amt) over w_t2_10d as w10d_merchant_purchase_amt_sum,
-- 样本商户最近 10 天交易次数
count(purchase_amt) over w_t2_10d as w10d_merchant_purchase_count 
FROM   
(SELECT id, mid, trans_time as purchase_time, 0.0 as purchase_amt, "" as purchase_type FROM t1) as t11
window w_t2_10d as (UNION (SELECT 0L as id, mid, purchase_time, purchase_amt, purchase_type FROM t2) as t22 
PARTITION BY mid ORDER BY purchase_time
ROWS_RANGE BETWEEN 10d PRECEDING AND 1 PRECEDING INSTANCE_NOT_IN_WINDOW)

4. 特色组构建

一般而言，一个残缺特色抽取脚本将抽取几十、上百，甚至几百个特色。咱们能够依据特色类型、特色关联的表和窗口将这些特色分成若干组，而后将每一组特色搁置到不同的 SQL 子查问里；最初将这些子查问按主表 ID 拼接在一起。本节，咱们将承接后面的例子，演示如果将各种特色拼接在一起造成一个特色大宽表。
首先，咱们将特色分成 3 组：

而后，咱们应用 OpenMLDB SQL 将同一组特色构建在同一个子查问中：

  • 特色组 1
  -- 主表单行特色
  SELECT 
  -- 列直取
  id, uid, trans_type, 
  -- 单行工夫特色：day of week
  dayofweek(trans_time) as f_trans_day_of_week, 
  -- 单行工夫特色：交易日
  day(trans_time) as f_trans_day, 
  -- 单行工夫特色：交易小时
  hour(trans_time) as f_trans_hour, 
  -- 单行工夫特色：交易分钟
  minute(trans_time),
  -- 单行数学特色：交易额向上取整后取对数
  log(ceiling(trans_amt)) as f_trans_amt_log,
  -- 单行字符串特色: 卡号前四位
  substr(cardno, 1, 4),
  -- 副表特色
  t2.purchase_time as f_purchase_time, 
  t2.purchase_amt as f_purchase_amt, 
  t2.purchase_type as f_purchase_type
  
  FROM t1 LAST JOIN t2 ORDER BY t2.purchase_time ON t1.mid = t2.mid
  • 特色组 2
  -- 主表窗口特色
  SELECT 
  id as out2id,
  -- 最近 30 天 POS 交易总金额
  sum_where(trans_amt, trans_type = "POS") over w30d as w30d_sum_pos_trans_amt,
  -- 最近 30 天的最大 POS 交易金额
  max_where(trans_amt, trans_type = "POS") over w30d as w30d_max_pos_trans_amt,
  -- 最近 30 天的单次均匀 POS 交易金额
  avg_where(trans_amt, trans_type = "POS") over w30d as w30d_avg_pos_trans_amt,
  -- 最近 30 天的 POS 交易总次数
  count_where(trans_amt, trans_type = "POS") over w30d as w30d_count_pos_trans_amt,
  
  -- 最近一周总交易金额总额
  sum(trans_amt) over w7d as w7d_sum_trans_amt,
  -- 最近一周的总交易次数
  count(trans_amt) over w7d as w7d_count_trans_amt,
  from t1
  -- 最近 30 天的窗口
  window w30d as (PARTITION BY uid ORDER BY trans_time ROWS_RANGE BETWEEN 30d PRECEDING AND CURRENT ROW),
  -- 最近一周的窗口
  w7d as (PARTITION BY uid ORDER BY trans_time ROWS_RANGE BETWEEN 7d PRECEDING AND CURRENT ROW)
  • 特色组 3
-- 副表聚合特色
  SELECT 
  id as out3id,
  -- 样本商户最近一周零售总额
  sum(purchase_amt) over w7d_merchant as w7d_merchant_purchase_amt_sum,
  -- 样本商户最近一周交易次数
  count(purchase_amt) over w7d_merchant as w7d_merchant_purchase_count,
  -- 主表商户最近一周的流水
  FROM (select id, mid, cardno as card, trans_time as purchase_time, 0.0 as purchase_amt, "" as purchase_type from t1) as t11
   window w7d_merchant as (UNION (select 0L as id, mid, card, purchase_time, purchase_amt, purchase_type from t2) as t22 PARTITION BY mid ORDER BY purchase_time ROWS_RANGE BETWEEN 30d PRECEDING AND 1 PRECEDING INSTANCE_NOT_IN_WINDOW)

最初，将这三组特色，按主表 ID 拼接在一起：

SELECT * FROM 
-- 特色组 1
(
  -- 主表单行特色
  SELECT 
  -- 列直取
  id, uid, trans_type, 
  -- 单行工夫特色：day of week
  dayofweek(trans_time) as f_trans_day_of_week, 
  -- 单行工夫特色：交易日
  day(trans_time) as f_trans_day, 
  -- 单行工夫特色：交易小时
  hour(trans_time) as f_trans_hour, 
  -- 单行工夫特色：交易分钟
  minute(trans_time),
  -- 单行数学特色：交易额向上取整后取对数
  log(ceiling(trans_amt)) as f_trans_amt_log,
  -- 单行字符串特色: 卡号前四位
  substr(cardno, 1, 4),
  
  -- 副表特色
  t2.purchase_time as f_purchase_time, 
  t2.purchase_amt as f_purchase_amt, 
  t2.purchase_type as f_purchase_type
  
  FROM t1 LAST JOIN t2 ORDER BY t2.purchase_time ON t1.mid = t2.mid
) as out1 LAST JOIN 
-- 特色组 2
(
  -- 主表窗口特色
  SELECT 
  id as out2id,
  -- 最近 30 天 POS 交易总金额
  sum_where(trans_amt, trans_type = "POS") over w30d as w30d_sum_pos_trans_amt,
  -- 最近 30 天的最大 POS 交易金额
  max_where(trans_amt, trans_type = "POS") over w30d as w30d_max_pos_trans_amt,
  -- 最近 30 天的单次均匀 POS 交易金额
  avg_where(trans_amt, trans_type = "POS") over w30d as w30d_avg_pos_trans_amt,
  -- 最近 30 天的 POS 交易总次数
  count_where(trans_amt, trans_type = "POS") over w30d as w30d_count_pos_trans_amt,
  
  -- 最近一周总交易金额总额
  sum(trans_amt) over w7d as w7d_sum_trans_amt,
  -- 最近一周的总交易次数
  count(trans_amt) over w7d as w7d_count_trans_amt 
  from t1
  -- 最近 30 天的窗口
  window w30d as (PARTITION BY uid ORDER BY trans_time ROWS_RANGE BETWEEN 30d PRECEDING AND CURRENT ROW),
  -- 最近一周的窗口
  w7d as (PARTITION BY uid ORDER BY trans_time ROWS_RANGE BETWEEN 7d PRECEDING AND CURRENT ROW)
) as out2 ON out1.id = out2.out2id LAST JOIN
-- 特色组 3
(
   -- 副表聚合特色
   SELECT
   id as out3id,
   -- 样本商户最近一周零售总额
   sum(purchase_amt) over w7d_merchant as w7d_merchant_purchase_amt_sum,
   -- 样本商户最近一周交易次数
   count(purchase_amt) over w7d_merchant as w7d_merchant_purchase_count
   -- 主表商户最近一周的流水
   FROM (select id, mid, cardno as card, trans_time as purchase_time, 0.0 as purchase_amt, "" as purchase_type from t1) as t11
   window w7d_merchant as (UNION (select 0L as id, mid, card, purchase_time, purchase_amt, purchase_type from t2) as t22 PARTITION BY mid ORDER BY purchase_time ROWS_RANGE BETWEEN 30d PRECEDING AND 1 PRECEDING INSTANCE_NOT_IN_WINDOW)
) as out3 ON out1.id = out3.out3id;

5. 扩大浏览

开源机器学习数据库 OpenMLDB v0.4.0 产品介绍
想疾速试用 OpenMLDB 来开始写特色计算脚本？快来看一下 OpenMLDB 疾速上手
残缺的 SQL 语法参考文档：中国镜像站点，国内站点
本系列教程上篇：4PD 开发者社区：深入浅出特色工程 – 基于 OpenMLDB 的实际指南（上）

退出移动版