Oracle Database 12c 中的预测查询

简介

Oracle Database 12c 中新引入的一个 SQL 特性是能为数据创建动态预测模型。您所要做的只是编写一个 SQL 查询，该查询将构建一个预测模型，然后将此模型应用于您的数据。所有这些可以一步完成，您无需了解其内部工作原理。预测查询执行期间构建的预测模型仅在查询运行时才存在。查询完成，即删除所有模型及相关设置。这些被称为临时模型。

此新特性令人困惑的一点就是它有很多名字。在文档中，它有时被称作“动态模型”，有时又被称作“动态查询”。此特性也是组成 SQL 的 Oracle Data Miner 工具的一部分，本文中称之为“预测查询”。

动态模型 = 动态查询 = 预测查询。是有点混乱。跟 Oracle 的一些人谈了之后，了解到似乎他们以后可能会一直使用“预测查询”这个名字。

什么是预测查询？为什么需要预测查询？

预测查询可以让您使用数据库中的数据挖掘算法快速构建和评估数据，而无需了解所需设置和对模型进行微调。预测查询执行一结束，预测查询执行期间创建的所有模型立刻不复存在。您无法检查或调优使用的算法或生成的模型。因此，只能信任 Oracle 在后台的行为。如果您是数据科学家一类的人物，通常希望调优模型，那么此方法可能并不适合您。但如果您希望快速构建模型并对数据进行评估，那么您应考虑使用预测查询。

使用预测查询的一大好处是可以对数据进行分区，从而针对每个分区构建预测模型。这意味着，它将数据分到相关分区，然后创建一个该分区特定的预测模型，然后评估该分区内的数据。通常，对大多数数据挖掘工具而言，您必须具体定义如何为分区创建数据子集，定义如何构建模型然后运行模型评估数据。所有这些步骤都必须手动定义。而在预测查询中定义分区之后，所有这些工作都将自动替您完成。这样做主要有两个好处。第一个好处是创建新的数据分区时（即分区属性存在新值），预测查询会自动发现，然后接管后面的所有工作。第二个好处是预测查询允许您使用并行查询选项加快数据评估过程。这在处理大数据时特别有用。

使用预测查询与使用 DMBS_PREDICTIVE_ANALYTICS PL/SQL 程序包中的 PREDICT 过程非常类似，只不过预测查询使用的是 SQL，而不是 PL/SQL。

可以用两种办法创建预测查询。第一种方法是编写一条 SQL 语句提示数据库使用数据库中的数据挖掘算法。第二种（也更为轻松）方法是使用 Oracle Data Miner 工具中的预测查询节点。

本文的以下各部分将详细介绍如何分别使用 Oracle Data Miner 和 SQL 构建预测查询。

使用 Oracle Data Miner 构建预测查询

只有连接到 Oracle 12c 数据库后，才会在作为 SQL Developer 一部分的 Oracle Data Miner GUI 中显示 Predictive Queries 选项。您将在 Components 窗口下看到 Predictive Queries 部分，如图 1 所示。

图 1：Components 窗口的 Predictive Queries 部分

Oracle Data Miner 中提供了四种类型的预测查询节点。

为了演示如何在 Oracle Data Miner 工具中构建和使用预测查询节点，下面的示例将使用预测查询节点构建和应用一个分类模型。

要在工作流中创建一个数据源和若干预测查询节点，您需要执行以下步骤：

1. 为包含数据的表创建和定义一个数据源节点。该数据源节点位于 Components 窗口的 Data 部分中。

2. 在工作流中创建预测查询节点。

3. 将该数据源节点连接到预测查询节点。为此，右键单击数据源节点，从菜单中选择 Connect，然后将鼠标移至预测查询节点上再次点击鼠标。将在两个节点之间出现一个灰色箭头。这表示它们已经连接。

要定义预测查询节点的设置，您需要执行以下步骤：

1. 双击打开预测查询节点。

2. 这将打开 Edit Predictive Query node 窗口。该窗口有 4 个选项卡/部分，您可以在其中定义要进行的预测、如何对数据分区（并因此决定将创建的预测查询模型数量）、输入属性列表以及输出属性列表。

3. 定义预测。

a) 定义输入数据集的 Case ID。Case ID 可以是主键，也可以是某个赋予每个属性唯一性的属性。Case ID 可以留空，但是定义 Case ID 可以允许过程和输出的可重复性。在本数据集示例中，Case ID 为 CUST_ID。

b) 为数据集定义目标属性。单击绿色加号图标打开 Add Target 窗口。在此窗口中，选择所需的目标属性。在本数据集中，这是 AFFINITY_CARD 属性。

c) 定义数据挖掘类型。这将是 Classification 或 Regression。要定义数据挖掘类型，您需要点击 Data Mining Type 列下的图标。将出现一个下拉列表，列出 Categorical 或 Numerical。在本例中，我们希望执行分类，因此需要选择 Categorical。

d) 定义预测输出。在此部分，您可以定义想要的预测输出类型。默认情况下，Classification 类型的输出由 Prediction、Prediction Details 和 Prediction Probability 几部分组成。对于 Regression 类型，则为 Prediction 和 Prediction Details。要删除其中一个或多个预测属性，您可以选择相应的属性，然后点击红色 X 图标进行删除。

4. 定义分区。在此部分，可以定义您希望将数据挖掘模型定义在哪些分区上。这可能是预测查询最大的好处。将为分区定义的每个值构建一个单独的数据挖掘模型。可以根据属性值创建分区，也可以通过定义特定的表达式来创建分区。要创建分区，请选择绿色加号图标。这将打开一个窗口，其中列出数据集中的各个属性。在本例中，需要按国家/地区为每个性别（男/女）创建一个单独的数据挖掘模型。因此，如果有 10 个国家/地区，预测查询将构建 20 个不同的数据挖掘模型。

5. 定义输入。默认情况下，将列出数据源节点中的所有属性。如果有任何您不希望在构建数据挖掘模型时用作输入的属性，可以从输入列表中删除。

6. 定义其他输出。在此部分，可以定义希望作为预测查询输出显示的属性。默认情况下，输出属性将包括定义的 Case ID、定义的 Target Attribute（本例中为 AFFINITY_CARD）以及 Partition Attributes。如果您想包含来自数据源的其他属性，可以使用绿色加号图标添加。

完成以上步骤之后，就定义好了预测查询。现在可以关闭 Predictive Query 窗口。

马上可以运行预测查询。要运行此节点，请右键点击该节点，从菜单中选择 Run。查询将在数据库中运行，使用数据源作为输入，并使用您输入的详细信息构建数据挖掘模型，然后使用这些模型对同一数据集进行评估或标记。预测查询节点完成后，右键点击节点并从菜单中选择 View Data，即可查看查询结果。图 2 显示了一个生成的输出示例。

图 2：预测查询节点生成的输出。

设置和定义其他预测查询节点，如异常检测、集群和特性提取节点，也可采用类似的办法。

预测查询让用户可以非常轻松地构建数据挖掘模型并将其应用于数据，效率非常高，可以根据分区的定义方式动态创建这些模型。

使用 SQL 编写预测查询

在上一部分，您已经看到了如何使用作为 SQL Developer 一部分的 Oracle Data Miner 工具构建预测查询。Oracle Data Miner 工具还允许为每种主要类型的数据挖掘方法创建预测查询。

在这一节，我们将了解如何着手使用 SQL 编写预测查询。示例将基于我们前面使用 Oracle Data Miner 工具构建预测查询时所使用的示例样本。这样便于比较这两种方法。

如果您已经构建了一个 Oracle Data Mining 模型，您应该能够使用 PREDICTION 和 PREDICTION_PROBABILITY 函数将该模型应用于数据。对于预测查询，我们同样也可以使用这些函数，但为使预测查询工作，还需要一些其他规范。

下面的示例显示了一个预测查询，它使用 MINING_DATA_BUIL_V 视图中的数据构建一些数据挖掘模型。

select cust_id, affinity_card,   
       PREDICTION( FOR to_char(affinity_card) USING *) OVER ()       
                                                 pred_affinity_card           
from mining_data_build_v);

结果如上所示，我们可以查看和比较实际值与预测值。您可能注意到有一个预测不正确。这在数据挖掘中很常见。它并不每次都对。但这样就够了。部分原因是因为数据挖掘算法试图按最适合所有数据所有记录的方式工作。一种提高准确性的方法是根据数据的不同分组来构建数据挖掘模型。例如，本例中有一个 COUNTRY_NAME 属性。也许为每个国家/地区构建一个单独的数据挖掘模型会更好。

为此，我们以 PARTITION BY 子句中输入的内容为例。在此可添加 COUNTRY_NAME，查询运行时，将为每个国家/地区创建一个单独的数据挖掘模型。然后用这些模型评估数据。

select cust_id, affinity_card,   
       PREDICTION( FOR to_char(affinity_card) USING *) OVER  
                     (PARTITION BY "COUNTRY_NAME") pred_affinity_card           
from mining_data_build_v);

由上面显示的示例输出可以看到被预测属性 (AFFINITY_CARD) 的实际值和预测值。在上述示例中，我们看到两个值是相同的，如果查看所有记录，就会看到准确性非常高。这样很好，这表明预测查询生成了一个非常高效的数据库中数据挖掘模型。

但预测查询的问题在于这些非常高效的数据挖掘模型不再存在于数据库中。它们只存在于查询执行期间。

针对您的典型分类类型问题，目标属性应该是字符数据类型。如果不是，例如可能定义为数字，就需要对目标属性应用 TO_CHAR 函数，将其转换成分类值。如果不使用 TO_CHAR 转换数字属性，那么预测查询会将数据当成递归数据挖掘问题处理。

现在来看一个递归示例。

在此示例中，我们将使用另一个在安装 Oracle Data Miner 信息库时就提供的示例数据集。其中一个表名为 INSUR_CUST_LTV_SAMPLE。我们想要做的是通过这些数据预测每个客户的潜在寿命值 (LTV)。

以下示例显示一个预测查询，它计算预测 LTV，并将其与当前值一起显示。对于没有 LTV 值的记录，您可以通过预测值对 LTV 有一个大概的印象。

select customer_id,  
       ltv,
       PREDICTION( FOR ltv USING *) OVER ( ) pred_ltv 
from   insur_cust_ltv_sample;

正如我们在上面的例子中使用预测查询所做的一样，我们可以让数据库根据一个或一组属性的值创建多个数据挖掘模型。在下面的示例中，对上述 LTV 预测查询进行了扩展，将为每个 STATE 和 SEX 组合创建一个单独的数据挖掘模型。这将创建 44 个不同的数据挖掘模型，因为我们的数据集中有 22 个不同的州，当然还有两个不同的 SEX 值。

select customer_id,    
       ltv,  
       PREDICTION( FOR ltv USING *) 
                    OVER ( PARTITION BY STATE, SEX ) pred_ltv 
from   insur_cust_ltv_sample;

预测查询不能做什么

预测查询的使用有其限制。当您运行预测查询时，Oracle 将创建一个临时模型。该模型只在查询执行期间可用。预测查询生成的模型在此之后并不存在，也不可用于检查或调优模型。如果您需要检查模型、将评估结果与模型关联、指定特殊算法设置或使用同一模型执行多项评估查询，则必须使用 Oracle Data Miner Model Nodes 或使用一个名为 DBMS_DATA_MINING 的 Oracle Data Mining PL/SQL 程序包创建一个预定义模型。

另一个办法就是使用 DBMS_PREDICTIVE_ANALYTICS PL/SQL 程序包。这样就可以创建一些临时模型，但通过预测查询可以获得更好、更高效的解决方案。

关于作者

Brendan Tierney 是一位 Oracle ACE 总监兼独立顾问，在爱尔兰都柏林理工学院讲授数据挖掘和高级数据库。他在数据挖掘、数据仓储、数据架构和数据库设计领域有着 20 多年的丰富经验。Brendan 从事过的项目遍及爱尔兰、英国、比利时和美国。Brendan 还担任 UKOUG Oracle Scene 杂志编辑和 OUG Ireland BI SIG 副主席。Brendan 经常在欧美各种会议上发表演讲，并为 OTN、Oracle Scene、IOUG SELECT Journal 和 ODTUG Technical Journal 撰写技术文章。Brendan 还在 Oracle Press 出版了一本书，名为《Predictive Analytics using Oracle Data Miner》。您可以通过 twitter @brendantierney 或 www.oralytics.com 上的博客关注 Brendan。