Oracle 数据挖掘和分析：零售行业应用举例一
概述
Oracle Database10g R2 中有个新的Package: UTL_NLA,这个Package没什么名气,但是把线性代数功能带进了Oracle Database. 这使得Oracle数据库成为一个不错的科学计算和分析平台. 现在,我们可以很容易的在Oracle 数据库中写出很好的矩阵代码. 下面是从Oracle Database Data Warehousing Guide 10g R2中摘录的简要描述:
线性代数是有着很广泛的实际应用的数学分支.很多领域都存在可以用线性代数来描述的任务. 比如: 统计领域(多元线性回归,主成分分析），数据挖掘（聚类和分类），生物信息学（微阵列数据分析），业务研究（供应链和其他优化问题），计量经济学（分析消费者的需求数据）以及财务（资产分配问题）每个人都可以自由使用线性代数的各种函数库。Oracle的UTL_NLA包可以定义矩阵类型的数据并且把两个功能最强，使用最广的两个库封装到PLSQL包的子程序中。
线性代数依赖于矩阵运算，以前，在PLSQL中执行矩阵运算需要基于PLSQL 的本地数据类型，从零开始写矩阵运算函数。这需要写大量的程序并且性能有限。如果开发人员选择把数据发送到外部程序包中去处理而不是在Oracle 中创建矩阵计算函数来处理 ，就存在数据的导出、导入等IO时间消耗。使用UTL_NLA package可以不用做这些，可以快速交付实现。
BLAS和LAPACK可能是应用最广泛的线性代数库。这些库被广泛的应用于大量的科学计算程序和专门工具。
对开发者来说，这些函数库提供了积木实现大量的高级技术。比如 MATLAB工具库，其大多数功能都是建立在类似于BLAS和LAPACK之类的线性代数库的基础上的。在数据库中提供这些函数库，允许开发者编写紧凑的，易于阅读的代码用于矢量操作，当然因为这些库拥有高效而强劲的线性代数操作的实现，使用UTL_NLA Package的代码自然就具有这些特性。
除了科学计算，Oracle的线性代数支持可用于业务分析。一个例子就是多元线性回归。数据库中带有一个多元线性回归的应用例子，这个例子就是使用UTL_NLA 写的。这个应用在一个叫做OLS_Regression的对象中实现。注意，例子OLS Regression对象的文件文件可以在$ORACLE_HOME/plsql/demo 中找到。点击 这里 查看例子，学习如何使用这个功能。
Example 21-19 Linear Algebra
这是一个Oracle数据库附带的例子， 演示了如何使用Oracle 的线性代数功能来支持业务分析，它使用UTL_NLA Package来调用多元线性回归应用。这个多元线性回归应用在一个叫做 OLS_Regression 的对象中被实现。 对应的例子文件可以在$ORACLE_HOME/plsql/demo中找到。
考虑下列场景：零售商需要分析营销计划的效果。
背景：
每个门店都会把它每年的市场费用预算分配到如下四个方面：1） 媒体广告（media）；2）促销(promo)；3）折扣券(disct)；4）直邮(dmail)；回归分析需要在每个门店每年的销售额 与上述4项费用支出 之间建立线性关系。假设这些市场数据都存储于如下的表中：
sales_marketing_data (
/* Store information*/
store_no NUMBER,
year NUMBER,
/* Sales revenue (in dollars)*/
sales NUMBER, /* sales amount*/
/* Marketing expenses (in dollars)*/
media NUMBER, /*media advertisements*/
promo NUMBER, /*promotions*/
disct NUMBER, /*dicount coupons*/
dmail NUMBER /*direct mailers*/)
那么你可以建立如下线性模型：
Sales Revenue = a + b Media Advisements + c Promotions + d Discount Coupons + e Direct Mailer
实现：
这个模型可以使用下面的视图来实现，这个视图引用了OLS回归对象。
View start:
CREATE OR REPLACE VIEW sales_marketing_model (year, ols) AS SELECT year, OLS_Regression( /* mean_y => */ AVG(sales), /* variance_y => */ var_pop(sales), /* MV mean vector => */ UTL_NLA_ARRAY_DBL (AVG(media),AVG(promo), AVG(disct),AVG(dmail)), /* VCM variance covariance matrix => */ UTL_NLA_ARRAY_DBL (var_pop(media),covar_pop(media,promo), covar_pop(media,disct),covar_pop(media,dmail), var_pop(promo),covar_pop(promo,disct), covar_pop(promo,dmail),var_pop(disct), covar_pop(disct,dmail),var_pop(dmail)), /* CV covariance vector => */ UTL_NLA_ARRAY_DBL (covar_pop(sales,media),covar_pop(sales,promo), covar_pop(sales,disct),covar_pop(sales,dmail))) FROM sales_marketing_data GROUP BY year; |
view end:
通过这个视图，市场部经理可以进行一些分析，比如“这个销售营销模型对2004年的数据来说合理吗？”也就是说多重关联性是否大于可接受的值。查询sql如下：
SELECT model.ols.getCorrelation(1)
AS “Applicability of Linear Model”
FROM sales_marketing_model model
WHERE year = 2004;
也可做如下分析：“在2003年，如果没有任何市场活动，那么门店的基本收入是多少？” 或者“在2004年，最有效的是那种类型的市场活动？”
解释：
1、OLS_Regression 定义在 $ORACLE_HOME/plsql/demo/olstype.sql 中
OLS是普通最小二乘分析法(ordinary least—squares）的简写 ，想深入了解OLS可点击这里，而 OLS Regression 即指使用最小二乘法进行线性回归分析。
2、var_pop, 函数参考这里 , 或者这里 covar_pop 函数参考这里
3、OLS_Regression 使用说明
$Header: olstype.sql 09-jun-2004.17:32:14 lvbcheng Exp $ olstype.sql Copyright (c) 2004, Oracle. All rights reserved. 文件名 olstype.sql - 普通最小二乘回归 描述 这个文件包含普通最小二乘(OLS)的类型定义 This files contains the type definition for Ordinary Least Squares (OLS) 回归: y = b0 + b1 z1 + ... + bn zn. 备注 OLS_Regression 的构造函数需要用户传入如下参数：除其他值外， 预测变量的均值向量(vm), 自变量的协方差矩阵(vcm), 因变量和自变量的协方差(cv). 1. 有 n个 自变量 (zi) 就应该有n个均值, 这里 MV[i] = avg(zi). 2. 协方差矩阵(VCM) 是一个 NxN 对称矩阵， 并且作为一个阵列参数传入， 主要使用阵列的右上三角部分，(i <= j i是列，j是行) 当i!=j时， V[i,j] 是协方差 covar_pop(zi, zj)；当i=j的时候，V[i,j] 是方差 ，比如 ，如果有三个预测变量 (a, b, and c) 那么其协方差(VCM)就应该是 : [ var_pop(a) covar_pop(a,b) covar_pop(a,c) ] VCM = [ covar_pop(b,a) var_pop(b) covar_pop(b,c) ] [ covar_pop(c,a) covar_pop(c,b) var_pop(c) ] 但传给 OLS_Regression()的应该是右上三角部分: VCM = UTL_NLA_ARRAY_DBL(var_pop(a), covar_pop(a,b), covar_pop(a,c), var_pop(b), covar_pop(b,c), var_pop(c)) 3. 对于 N 个自变量(zi)和一个因变量(y)的情况, 协方差（CV) 应该有n中不同情况， CV[i] = covar_pop(y, zi). 4. 标准化的评分回归只需要一个相关矩阵(RM)和一个相关矢量(RV)， 使用 RM 替换上面的 VCM, 不过，RM[i,j] = corr(zi, zj) when i != j, and 1 when i = j. 使用 RV 替换上面的 CV 不过, RV[i] = corr(y,zi). 依赖 这个文件依赖于 UTL_NLA package. 参考 Johnson, Richard A., and Dean W. Wichern, "Applied Multivariate Statistical Analysis (5th ed.)". New Jersey: Prentice Hall, 2002. 《多元统计分析应用》 举例 1. 下面的SQL语句将从模型：y = b0 + b1 z1 + b2 z2 中返回回归方程。 而因变量y 和自变量 z1,z2 存储在表中：ols_data(y number, z1 number, z2 number). SELECT model.ols.getEquation() "OLS Regression Equation" FROM (SELECT OLS_Regression(AVG(y), VAR_POP(y), UTL_NLA_ARRAY_DBL(AVG(z1),AVG(z2)), UTL_NLA_ARRAY_DBL(VAR_POP(z1),COVAR_POP(z1,z2),VAR_POP(z2)), UTL_NLA_ARRAY_DBL(COVAR_POP(y,z1),COVAR_POP(y,z2))) ols FROM ols_data) model; 2. 下面的SQL语句将从模型：y = y = 0 + b1 z1 + b2 z2 中返回回归方程。 相应的数据存储在表table ols_data(y number, z1 number, z2 number) SELECT model.ols.getEquation() "OLS Regression Equation" FROM (SELECT OLS_Regression(AVG(y), VAR_POP(y), UTL_NLA_ARRAY_DBL(AVG(z1),AVG(z2)), UTL_NLA_ARRAY_DBL(VAR_POP(z1),COVAR_POP(z1,z2),VAR_POP(z2)), UTL_NLA_ARRAY_DBL(COVAR_POP(y,z1),COVAR_POP(y,z2)), 0) ols FROM ols_data) model; 3. 更多其它关于 OLS_Regressions 应用的例子可以参考 olsexmpl.sql 文件. |
专业需求，局限和可用性。
在使用UTL_NLA Package之前，必须了解一些事情，Oracle文档指出使用这个Package的开发者应该有线性代数基础，特别是BLAS和LAPACK的知识。我相信，如果要使用这个Package来实现一些总所周知的算法的话，只需要了解线性代数的基础即可。关于BLAS和LAPACK, 熟悉一些基本概念是重要的。(比如，矩阵存储表示：列或行),除了Oracle文档，其他一些有用的参考包括：
The Lapack Users’ Guide, the BLAS and the LAPACK chapters in the in CRC Handbook of Linear Algebra.
参考：http://oracledmt.blogspot.com/2007/04/way-cool-linear-algebra-in-oracle.html
关于作者:
| 昵称:商云方 档案信息:顾问, HAND张江技术中心 联系方式:你可以通过yunfang.shang@hand-china.com联系作者 点击查看商云方发表过的所有文章... 本文永久链接： http://blog.retailsolution.cn/archives/2157 |
对本文的评价:
