实践SQLServer数据挖掘.ppt

《实践SQLServer数据挖掘.ppt》由会员分享，可在线阅读，更多相关《实践SQLServer数据挖掘.ppt（112页珍藏版）》请在三一办公上搜索。

1、第3章 实践SQL Server数据挖掘,创建数据挖掘项目,在SQL Server 2005的程序目录中打开进入BI Dev Studio，在文件菜单中选择“新建”“项目”注意项目文件是保存在“我的文档”文件夹中的,新建项目对话框,打开解决方案资源管理器查看,MovieClick数据集,2002年11月，对微软的雇员进行调查。调查询问了被调查者的电影观看行为、人口统计信爱所喜欢的电影、演员和导演。内容包括多久看一次电影多久租一次影片家里有多少台电视机谁通常会选择您观看的电影您最喜欢的电影,MovieClick数据集,2002年11月，对微软的雇员进行调查。内容包括您最喜欢的导演您最喜欢的男演员

2、和女演员年龄性别婚姻状况有几个小孩您家里有多少张床您拥有多少辆汽车,您的房子中有多少间浴室您最喜欢的爱好、兴趣领域是什么您的学历,在ssms中创建数据库,1 在ssms中创建数据库,2 从外部文件导入数据,2 从外部文件导入数据,2 从外部文件导入数据,2 从外部文件导入数据,2 从外部文件导入数据,2 从外部文件导入数据,2 从外部文件导入数据,2 从外部文件导入数据,在解决方案中新建数据源 BI,新建数据源视图,为了使下一步顺利进行，在数据挖掘项目节点上单击右键选择“属性”进行设置,将部署中的目标服务器更改,改为本机上的数据库服务器的名称,创建MovieClick数据源,在SSMS中创建M

3、ovieClick数据库，导入数据表在BI Dev Studio中创建项目MovieClick在MovieClick项目中建立数据源建立数据源视图：本例的要点是建立数据源中多张表之间的嵌套关系如果一个事例表和一个嵌套表之间的关系不存在于关系数据库中，则必须在DSV中指定该关系，否则将无法将嵌套表添加到模型中 大家可以尝试在SSMS的相应数据库中通过建立数据库关系图来建立表之间的关系,MovieClick数据源视图,使用DSV来浏览数据,使用命名计算,在DSV中可以创建命名计算、命名查询和视图，这些操作并不会影响到原数据库中的数据在DSV中，命名计算列是表中附加的虚列，所以可以在自己的数据中挖掘

4、派生信息，而不需要改变源数据命名计算列包含一个名称、一个包含计算的SQL表达式和一个可选的描述,使用命名计算,1 算术操作可以使用+,-,*,/和%(求模)。例如创建利润列Sales Price-Item Cost2 数学函数例：以对数的方法来规范化一个以指数级别增长的列Log(Sales Quantity)创建一个标志，判断实际值高于还是低于季度预算Sign(Actual Expenses-Budgeted Expenses),使用命名计算,3 组合表达式将婚姻状况和是否有孩子的属性进行组合Marital Status+Has Children4 CASE表达式CASE表达式可以创建有意义的

5、变量，可以达到如下的目的：改变值的标签手动离散化列减少有效状态的个数将一个属性从一个嵌套表中转换为一个事例级属性,使用命名计算,4 CASE表达式改变值的标签CASE Category When 1 then food When 2 then Beverage When 3 then goodsEND CASE,使用命名计算,4 CASE表达式手动离散化列：CASE When Age20 then Under 20 When Age=30 then Berween 20 and 30 When Age=40 then Between 30 and 40 ELSE Over 40END,使用命名

6、计算,4 CASE表达式减少有效状态的个数CASE Marital Status When Married then Marital Status When Never Married then Marital Status ELSE OtherEND,使用命名计算,4 CASE表达式将一个属性从一个嵌套表转换到事例表：CASE When Exists(Select Movie From Movies Where Movie=Star Wars And Movies.CustomerID=Customers.CustomerID)then True ELSE FalseEND,在Custome

7、rs表上创建一个命名计算,下面创建一个命名计算离散化并减少Num Bedrooms列中的状态。在DSV中右击Customers表，选择“新建命名计算”,在Customers表上创建一个命名计算,在对话框中输入计算列名Bedrooms、描述和表达式,在Customers表上创建一个命名计算,Case WHEN NUM BEDROOMS=1 THEN ONE WHEN NUM BEDROOMS=4 THEN FOUR OR MORE ELSE NONEEND,使用命名查询,可以使用数据源视图设计器中的“创建命名查询”对话框向数据源视图添加命名查询。命名查询是以表的形式表示的 SQL 表达式。在命名

8、查询中，可以指定一个 SQL 表达式来选择从一个或多个数据源的一个或多个表返回的行和列。命名查询基于一个表达式，除此之外，它在行和关系方面都与数据源视图中的其他表相似。该表达式作为表显示在数据源视图中，其行为也与表相同。,使用命名查询,创建命名查询时，需要为 SQL 查询返回的此表的列和数据指定名称，并根据需要对命名查询进行说明。SQL 表达式可以引用数据源视图中的其他表。命名查询允许不修改基础数据源即可扩展数据源视图中现有表的关系架构。例如，可以使用一系列命名查询将一个复杂的维度表分割为几个较小、较简单的维度表以便在数据库维度中使用。,使用命名查询,对于数据挖掘来说，有用的典型查询是过滤、连

9、接和采样查询基于列的值过滤行：Select*From Movies Where Movie!=Star Wars,使用命名查询,从嵌套表中过滤掉不常见的项select CustomerID,Movie from Movieswhere Movie in(select distinct Movie from Movies Group by Movie Having count(Movie)20),对记录按照Movie字段分组,对分组按照组的聚合结果作过滤,使用命名查询,从一个外表中连接信息：select customers.*,Education.Education Level from Cus

10、tomers Join Education on Customers.Education Id=Education.Education Id,使用命名查询,采样一个SQL Server数据库中的行select*from customers tablesample(30 precent)pepeatable(1),基于Customers表创建命名查询,在Customers表上创建一个命名查询，该Customers表之包含拥有房子的客户。由于这个表包含一个命名计算，所以必须将命名计算手动添加到查询中首先复制Bedrooms Named Calculation中的代码其次在DSV中右击然后选择“新建

11、命名查询”,3.3 创建和编辑模型,3.3.1 结构和模型挖掘结构包含所有列的数据类型和内容类型的定义，但没有包含列的用法定义和使用的算法挖掘模型包括挖掘算法及其相关的参数，还有来自挖掘结构的列有了挖掘结构的概念，可以很容易地对同一数据集测试不同的假定,3.3.2 使用数据挖掘向导,使用数据挖掘向导与使用DMX语言创建数据挖掘结构/模型所需要的信息是相同的：选择算法、选择数据源并指定源表的用法、选择表中的列并制定这些列的用法、对数据挖掘模型命名。,指定表的类型：事例表或嵌套表,指定列的用法,指定了事例表和嵌套表后，可以确定要包括在挖掘结构中的表的每一列的使用类型。如果没有指定列的使用类型，则挖

12、掘结构中将不会包含该列。数据挖掘列可以为下列四种类型之一：键列、输入列、可预测列或输入列和可预测列的组合。键列包含表中每个行的唯一标识符。某些挖掘模型（如顺序分析和聚类分析以及时序模型）可能包含多个键列。预测列包含要在挖掘模型中预测的信息。,指定列的用法,例如，一系列表可能包含客户 ID、人口统计信息以及每位客户在某个特定商店消费的金额。客户 ID 可以唯一地标识客户，并且使事例表与嵌套表相关，因此，一般会将客户 ID 作为键列。可以使用从人口统计信息中选择的列作为输入列，将说明每位客户消费金额的列作为预测列。可以生成一个挖掘模型，该模型可将人口统计信息与某位客户在商店中的消费金额关联起来。可

13、以使用此模型作为有针对性的营销的基础。,指定列的用法,数据挖掘向导提供了“建议”功能，在选择预测列时将启用该功能。数据集包含的列经常多于生成挖掘模型时要使用的列。“建议”功能可以计算出一个数值分数（介于 0 到 1 之间），用于说明数据集中的每一列与预测列之间的关系。根据此分数，该功能可以建议可用作挖掘模型的输入的列。如果使用了“建议”功能，您就可以使用建议的列，修改选择的列以满足需要，也可以忽略建议。,提供相关列建议,提供相关列建议,单击“建议”打开“提供相关列建议”对话框，该对话框列出与可预测列关系最密切的列。“提供相关列建议”对话框按照各属性与可预测属性的相关性对其进行排序。值大于 0.

14、05 的列将被自动选中，以包括在模型中。,在“指定列的内容和数据类型”页上，单击“检测”。某个算法将运行此示例数字数据，并确定这些数字列是包含连续值还是包含离散值。在 SSAS中处理挖掘模型之前，可以为作为建模依据的挖掘结构列定义数据类型。Analysis Services 支持以下挖掘结构列数据类型：Text、Long、Boolean、Double、Date,向导中的最后一步是对挖掘结构和关联的挖掘模型进行命名。如果选择“允许钻取”，则会在模型中启用钻取功能。这样，您就可以浏览用于生成模型的源数据。,3.3.4 使用数据挖掘设计器,有些操作在向导中无法实现，需要在数据挖掘设计器中进行设置离散

15、化属性：若所选择的数据挖掘算法不支持连续的属性，在数据挖掘向导中系统会自动将连续的列内容类型改为Discretized。若要指定离散的方法和参数，可以在挖掘结构的列列表中单击，然后在属性窗口中设置DiscretizationMethod和DiscretiztionBuckets属性,设置属性的类型和离散化参数,多次将同一列添加到挖掘结构中,一个挖掘结构中可以包含多个模型，可能要用多种不同的方法来针对某一特殊的列创建多个模型，目的是为了查看这一列如何影响预测的结果。例如可能想采用下面的方式来比较Age列是如何影响预测结果的把Age列作为连续的值将Age列离散化为3桶、5桶或7桶从DSV中将列拖到

16、结构树种即可实现多次将同一个列添加到模型中,创建属性层次,若两个列间存在相关关系，在DMX中可以使用结构Related to来说明。在设计器中可以通过设置列的Classified Column属性实现例如要创建一个Product Category-Product Name(产品目录-产品名称)层次，可将Product Category列的Classified Column属性设置为Product Name,创建属性层次,添加一个在另外一个表中查找其值的列,如果数据已经是规范化的(数据库由很多小表构成，相互用主键和外键连接)，则想要挖掘的表中包含了查找表的外键而不是希望在模型中出现的真实数据列。

17、在DSV中右击有外键的表，选择“显示相关表”就可以添加相应的列,添加一个在另外一个表中查找其值的列,例如：假设正在挖掘一个Purchases表和另外一个Products表。Purchases表有一个Product ID列，Products表的Product Name列于Purchases表的Product ID列相关联要创建一个使用Product Name列的挖掘结构，只要右击Pruchases表，选择“显示相关表”就会调入Products表，然后即可将Product Name列单击并拖入到结构树中,使用挖掘模型编辑器,使用挖掘模型编辑器，可以在挖掘结构上创建多个挖掘模型：为每个模型设置要使用

18、的算法以及算法的参数为每个模型选择要用到的列以及指定这些列的用法对每一列设置特定于算法的建模标志,挖掘模型编辑器,设置列的属性,列的属性：Input：输入列Predict：可预测列，也可作为其他可预测列的输入列PredictOnly：仅作为可预测列，不能作为其他可预测列的输入列忽略：该模型不包含这个指定的列,设置模型的属性,选择列头，则有关模型的属性就会出现在属性窗口可以设置名称及模型使用的算法，或添加注释,创建附加模型,在挖掘模型菜单中选择新建,创建附加模型,3.4 处理,在服务器上训练模型的过程称为处理。处理的过程包含读取源数据并作高速缓存，然后训练模型。有关挖掘模型和挖掘结构的处理有3种

19、状态：经过处理的、部分经过处理的和没有经过处理的部分经过处理：可能有一个包含几个挖掘模型的挖掘结构，而只想处理其中的一个挖掘模型，这时的挖掘结构就是部分经过处理的。在挖掘模型菜单下选择处理，在弹出的处理挖掘结构对话框中可以设置处理选项,3.4 处理,处理选项：处理全部处理默认值不处理：删除与该对象有关的所有数据处理结构：读和高速缓存数据，而不处理挖掘结构包含的模型处理清除结构：挖掘结构将删除缓冲的源数据而不处理包含的模型,3.5 使用模型,3.5.1 了解模型查看器SSAS的每一个数据挖掘算法都有属于它的查看器。查看器提供了模型多个方面的信息提供的视图包括图和表图带有相应的工具栏可以实现一些操

20、作表支持以Html格式复制,3.5.2 使用挖掘准确性图表,验证数据挖掘模型验证是评估挖掘模型对实际数据执行情况的过程。在将挖掘模型部署到生产环境之前，务必通过了解其质量和特征来对其进行验证。可以使用多种方法评估数据挖掘模型的质量和特征使用统计信息有效性的各种度量值来确定数据或模型中是否存在问题可将数据划分定型集和测试集，以测试预测的准确性也可以请商业专家查看数据挖掘模型的结果，以确定发现的模式在目标商业方案中是否有意义。,3.5.2 使用挖掘准确性图表,度量数据挖掘模型的条件 数据挖掘的度量通常分为以下三类：准确性、可靠性和有用性“准确性”是模型与所提供数据中的属性的结果相关联程度的度量值。

21、准确性有各种度量值，但准确性的所有度量值都依赖于所使用的数据。,3.5.2 使用挖掘准确性图表,准确性、可靠性和有用性“准确性”事实上，值可能缺少或近似，数据可能已被多个进程更改。在探索和开发阶段，通常允许数据中存在一定数量的错误。例如，基于过去的销售额来预测特定商店的销售额的模型可能非常相关，并且非常准确，即使该商店一直使用错误的会计方法。所以，准确性的度量值必须通过评估可靠性来平衡。,3.5.2 使用挖掘准确性图表,准确性、可靠性和有用性。“可靠性”评估数据挖掘模型处理不同数据集的方法如果无论提供哪些测试数据，数据挖掘模型都生成相同类型的预测，或者发现相同常规类型的模式，则该数据挖掘模型是

22、可靠的。例如，为使用错误会计方法的商店生成预测的模型将不适用于其他商店，因此该模型是不可靠的,3.5.2 使用挖掘准确性图表,准确性、可靠性和有用性。“有用性”包括说明模型是否提供了有用信息的各种指标。如将商店位置与销售额相关联的数据挖掘模型可能既是准确的，也是可靠的，但可能是无用的，因为不能通过在同一位置增加更多商店来推广该结果。并且它没有回答为什么某些位置销售额较高这一基本商业问题。,3.5.2 使用挖掘准确性图表,SQL Server 支持多种验证方法。将数据分区为定型集和测试集将数据分区为定型集和测试集是用于准备要评估的数据的一种既定技术。定型数据集中的某些数据保留用于进行测试，其余数

23、据用于进行定型。模型完成后将用于针对测试集进行预测。由于定型集中的数据是从用于进行定型的相同数据中随机选择的，因此从测试得出的准确性指标可能受数据差异的影响较小，因此可以更好地反映模型的特征。,3.5.2 使用挖掘准确性图表,挖掘模型的交叉验证通过交叉验证，可以将一个数据集分区为许多更小的交叉部分，并可以对交叉部分创建多个模型以测试整个数据集的有效性。然后，Analysis Services 将为每个分区生成详细的准确性指标。使用这些信息，可以提高各个模型的质量，也可以确定特定数据集的最佳模型。,十折交叉验证,英文名叫做10-fold cross-validation，用来测试算法准确性，是常

24、用的测试方法。将数据集分成十分，轮流将其中9份作为训练数据，1份作为测试数据，进行试验。每次试验都会得出相应的正确率（或差错率）。10次的结果的正确率（或差错率）的平均值作为对算法精度的估计一般还需要进行多次10折交叉验证（例如10次10折交叉验证），再求其均值，作为对算法准确性的估计。,十折交叉验证,之所以选择将数据集分为10份，是因为通过利用大量数据集、使用不同学习技术进行的大量试验，表明10折是获得最好误差估计的恰当选择，而且也有一些理论根据可以证明这一点。但这并非最终结论，争议仍然存在。而且似乎5折或者20折与10折所得出的结果也相差无几,3.5.2 使用挖掘准确性图表,SSAS提供了

25、绘制预测准确性图表，针对新数据或现有数据测试模型，或者在图表和报表中比较多个模型的工具。“提升图”是将使用数据挖掘模型获得的改进与随机推测进行对比时，可视化所获得改进的方法。也可以创建将财务收益或成本与使用挖掘模型相关联的“利润图”，以及为回归模型创建“散点图”。“分类矩阵”是在表中对准确和不准确的推测进行排序的方法，以便可以快速方便地测量模型预测目标值的准确程度。,提升图,提升图是通过绘制针对测试数据集进行的预测查询的结果（根据测试数据集中存在的可预测列的已知值）而创建的。在数据挖掘设计器的“挖掘准确性图表”选项卡的“提升图”选项卡中，可以查看两种类型的图表：提升图：比较每个模型预测的准确性

26、利润图：显示与使用每个模型相关联的理论上的利润增长。,提升图,使用“图表类型”列表选择所需的图表类型。在从该列表中选择“利润图”时，会自动打开“利润图设置”对话框。（单击“设置”时也会打开此对话框。）使用此对话框，可以设置定义利润图的参数。在提升图中只能对包含可预测离散属性的挖掘模型进行比较。“挖掘准确性图表”选项卡不能用于时序模型或具有可预测连续属性的模型。,提升图类型,“提升图”选项卡显示挖掘模型所引起的“提升”变化的图形表示形式。例如，请考虑下面的事例，即 Adventure Works Cycles 的市场部要搞一个发送邮件的活动。从以往的活动中，他们推算应有 10%的答复率。在数据库

27、的一个表中，存储了一个包含 10,000 名潜在客户的列表。按照正常答复率计算预计将有 1,000 名客户答复,提升图类型,此外，还考虑到该计划的预算低于向数据库中所有 10,000 名客户发送邮件所需的费用。根据预算，他们只能承担向 5,000 名客户发送邮件广告的费用。市场部有下列两种选择：随机选择 5,000 名目标客户使用挖掘模型确定最有可能答复的 5,000 名目标客户,提升图类型,如果该公司随机选择 5,000 名客户，则在估计给予积极答复的 1,000 名客户中，只能有 500 名客户做出答复，因为正常情况下只有 10%的客户答复。这正是提升图中的随机线所表示的情况。但如果市场部

28、使用挖掘模型来确定发送邮件的目标，则预计可以获得更高的答复率，因为他们锁定了最有可能答复的客户。,提升图类型,如果该模型精确无误，则意味着它可以形成完全准确的预测，即该公司通过向该模型推荐的 1,000 名潜在客户发送电子邮件广告，预计可以得到全部 1,000 个答复(这正是提升图中的理想线所表示的情况)事实是挖掘模型最可能位于这两种极限情况之间，即介于随机推测模型和理想或精确无误的预测模型之间。与随机推测相比，答复率的任何提高均被 视为提升。,可以创建两种类型的图表，在一种图表中可以指定可预测列的状态，另一种则不指定该状态。该图对挖掘模型的结果显示一条线，同时还显示其他两条线：一条线表示理想

29、的模型会产生的结果，并带有永远不会错误的完美预测，一条线表示随机猜测的结果。模型的结果将位于理想模型和随机推测之间的某个位置。对随机行的任何改进称为“提升”，且模型展示的提升越多，模型越有效。从连续的可预测属性生成的提升图显示一个散点图而非线条。,分类矩阵,“分类矩阵”选项卡提供了另一种用于检查结构中的挖掘模型创建预测的准确性的方法。分类矩阵是以比较（即测试数据集中存在的实际值与挖掘模型预测的值之间的比较）的形式生成的。该矩阵是一个重要的工具，因为它不仅显示模型正确预测值的频率，而且还显示模型最经常预测错的其他值。,例如，假定已经构建了一个模型来预测食品杂货店的客户使用的会员卡类型。卡有三种：

30、铜卡、银卡和金卡。下表是根据测试数据库（其中的会员卡值是已知的）预测会员卡值的模型的分类矩阵表示形式。,散点图,如果选择除时序模型以外的任何包含连续可预测属性的模型，则“提升图”选项卡中会自动显示“散点图”。散点图以图形方式对照显示数据中的实际值与模型预测的值。其沿 X 轴显示实际值，沿 Y 轴显示预测值。该图还显示一条显示完美预测的线，在这条线上预测值和实际值完全匹配。某个点与该条理想 45 度角线的距离指示进行的预测的准确程度。,应用场景,考虑下面这个模型：Adventure Works Cycles 公司的市场部根据其在促销电子邮件中发送的链接的点击数来预测日销售额。由于点击数和销售额均为连续数值，因此，可以以图形方式将点击数显示为独立变量，将销售额显示为依赖变量。这样，图中的直线显示预期线性关系，而散布在该直线周围的点显示实际数据偏离预期值的程度。一目了然，该分析指出一组结果与某个特定输入相关联的紧密程度，以及所生成的模型与理想模型之间有多大差异,了解散点图,下面的关系图显示散点图的一个示例，该图是为刚刚说明的应用场景而创建的。,3.5.3 为MovieClick模型创建提升图,创建一个提升图，目标是预测哪些客户会每周去一次电影院,3.5.4 使用挖掘模型预测,3.5.5 针对MovieClick模型执行查询,