数据分析技术(第2版) 使用SQL和Excel工具

本书的第1版使用我们熟悉的工具SQL和Excel，从实用的角度解释数据分析。这本书的指导原则是从问题出发，同时从业务角度和技术角度提供解决方案，以指导读者。这个方法被证明是非常成功的。从第1版到现在已经过去了10年，这期间已经发生了很多变化，工具本身也发生了很多变化。例如，当年的Excel还没有功能区，而且在当时的数据库中，窗口函数也非常罕见。一些工具，如Python和R，以及NoSQL数据库变得越来越常见，它们改变了分析师赖以生存的工具世界。然而，随着技术延伸到大大小小的各项业务中，关系型数据库在今天仍然被广泛使用，而且SQL也变得更加至关重要。对于很多商务人士，Excel工具仍然是做报表和展示的理想之选。大数据不再是未知的领域，它是我们每天都会面临的问题、挑战和机遇。根据底层软件的变化，在第2版中对本书的内容做了调整和更新，同时包含了更多的示例和技术，以及增加了关于数据库性能的一整章新内容。同时，我一直在努力保持本书第1版的优势。本书仍然围绕着数据、分析和展示的原则—— 少见地将三个功能放在一起处理。示例围绕着所提出的问题，同时讨论了这些问题的业务相关性和技术实现。示例使用的是真实的代码。数据、代码以及Excel示例都可以在配套网站上找到。撰写这本书的初动机来源于我的一个同事—— Nick Drake，他是受过培训的统计学家。曾经，他一直在寻找一本书，关于介绍如何使用SQL编写可用于数据分析的复杂查询。当时，基于SQL的书籍，要么介绍SQL的基础查询结构，要么介绍数据库的工作原理。严格地讲，没有从分析数据的角度介绍SQL的书籍，也没有基于回答数据问题的书籍。在统计学的众多书籍中，没有一本书能够面对这样一个事实提出解决方案：统计学所用的数据，多数都存储于关系型数据库中，而本书则填补了这一空白。笔者与Michael Berry一起撰写的其他关于数据挖掘的书籍，侧重于高级算法和案例学习。相比之下，本书侧重于“操作方式”。首先描述了存储在数据库中的数据，然后继续完成准备数据和生成结果集的过程。书中穿插的内容，是我在这个领域多年经验的结晶，解释了结果集被应用的可能方式，以及为什么有些事情有效果，而有些事情无效。书中示例非常具有实践性，它们所使用的数据都在本书的配套网站上(www.wiley.com/go/dataanaly- sisusingsqlandexcel2e)。关于数据仓库和分析数据库的一个老生常谈的话题是它们实际上没有做任何事。是的，它们存储数据，能够将不同来源的数据汇集在一起，并整理数据使数据变得清晰。是的，它们定义业务维度，存储关于客户的事务，还可能总结重要的数据(是的，所有这些都非常重要！)然而，数据库中的数据存储在旋转的硬盘上，而且数据在计算机内存中的数据结构非常复杂。对于如此多的数据，信息却很少。我们如何探索这些数据(特别是描述客户的数据)？很多关于统计学建模和数据挖掘的华丽算法都有一条简单的规则：“无用输入，无用输出”。即使是复杂的技术，也只有当数据是好数据时，结果才是好的。数据是理解客户、产品以及市场的中心。本书中的章节覆盖了数据的不同方面，同时包含了SQL和Excel支持的重要的数据分析技术。这些数据分析技术的范围涵盖了很多内容，从初的探索性数据分析到生存分析，从超市购物车分析到朴素贝叶斯模型，从简单的动画到线性回归。当然，本书不可能涵盖所有的数据分析技术。本书所介绍的方法历经时间的考验，被认为是有用的且适用于很多不同的领域。后，只有数据和分析还不够，还必须将结果展示给正确的观众。为完整地探索数据值，需要将数据转化为故事和情景、图表、数据指标和透视图。本书内容和技术综述本书侧重于三个关键的技术领域，这些技术用于将数据转化为可操作的信息：● 关系型数据库存储数据。获取数据的基本的语言是SQL(注意，变种的SQL也用于NoSQL数据库)。● Excel工作表是展示数据的常见工具。或许，Excel强大的功能是绘图，它能够将包含数字的列转换为图片。● 统计学是数据分析的基础。这三种技术一并介绍，是因为它们是彼此相关的。SQL回答“我们如何访问数据？”统计学回答：“数据是如何相关的？”而使用Excel可以方便地向人们展示和证明我们所发现的结论。关于数据处理的描述围绕着SQL语言。在实际业务中，Oracle、PostgresSQL、MySQL、IBM DB2，以及微软的SQL Server等都是常见的数据库，它们存储海量的业务数据事务信息。好消息是所有的关系型数据库都支持SQL作为查询语言。然而，正如英国和美国被称为是“拥有共同语言的两个国家”一样，每种数据库支持一些与众不同的SQL方言。附录列出了如何使用不同的SQL方言实现一些常见的功能。相似地，也有其他华丽的展示工具和专业的制图包。然而，对于一台用于工作的电脑，安装Excel或类似的电子表格工具是再常见不过的事情了。统计学和数据挖掘技术通常并不需要高级工具。其中一些非常重要的技术，可以使用SQL和Excel轻易地实现，包括生存分析、相似模型、朴素贝叶斯模型和关联规则。事实上，本书中介绍的方法通常比这些工具中的方法更强大，因为书中的方法更接近数据，因此它们更精准，而且容易定制。对这些技术的介绍涵盖了基础思想和深度扩展，这是在其他工具中所没有的内容。本书章节描述了不同的技术，在熟悉工具和数据的前提下，为数据建模和数据探索提供扎实的知识介绍。本书同时强调，当简单工具遇到瓶颈时，高级工具是非常有用的。内容结构本书的14章可以分为4部分。前3章介绍SQL、Excel和统计学的核心概念。中间7章讨论特别适合使用SQL和Excel的数据探索和数据分析技术。在后续的3章中，从统计学和数据挖掘的角度，介绍了关于建模的更正式的思想。后，新增的第14章讨论编写SQL查询时的性能问题。每一章都通过不同的视角，介绍使用SQL和Excel做数据分析的方方面面，包括：● 使用数据分析的基础示例● 分析师需要回答的问题● 详解数据分析技术的工作原理● 实现技术的SQL语法● 以表格或图表展示结果，以及如何在Excel中创建它们SQL是一门精准的语言，以至于有时难以读懂。数据流程图通常有助于理解SQL的工作原理。这些数据流程图是SQL引擎实际处理数据的合理预测，当然，实际上的数据处理细节由数据库引擎决定。结果以表格或图表的形式展现，分布在本书的所有章节中。此外，本书强调了Excel的一些重要特征，介绍了Excel图表的一些有趣用法。每一章都有技术专栏，通常讲述某项技术的重要方面或与正文内容相关的一些有趣历史背景。章节引导第1章“数据挖掘者眼中的SQL”从数据分析的角度介绍SQL，这是SQL语言的查询部分，使用SELECT查询从数据库中获取数据。第1章介绍了描述数据结构的实体-关系图——表、列，以及它们彼此间的关系。该章同时介绍了用于描述查询处理过程的数据流程图；通过数据流程图，能够可视化地理解数据的处理过程。本章介绍了全书中使用到的一些重要功能——例如联接、聚合和窗口函数。此外，第1章还描述了全书示例所使用的数据集(该数据集也可以从网站自行下载)。数据包括存储零售数据的表，存储手机客户数据的表，以及其他描述邮政编码和日历的引用表。第2章“表中有什么？开始数据探索”介绍使用Excel做数据探索和结果展现。在Excel的众多功能中，或许有用的功能就是绘图了。正如一句古老的中国谚语所说，“百闻不如一见”。Excel的绘图依据是数据。这样的图表不仅美观有用，同时在Word文档、PPT展示、电子邮件、网站中也非常实用。图表并非终点，它们只是探索数据分析的一个方面。此外，本章还介绍了在表格中汇总列，以及使用Excel生成SQL查询的有趣想法。第3章“不同之处是如何不同”介绍了一些描述性统计学的核心概念，例如平均值、P值和卡方检测。本章的目的是展示如何将这些技术应用于数据表中的数据上。至于这些统计学内容和统计学测试方法的选择，是由它们的实用性决定的。同时，本章侧重介绍这些知识的使用方法，而不是它们的理论内容。多数的统计学测试方法都可以使用Excel(甚至SQL)来实现。SQL技术一些技术非常适合使用SQL和Excel。第4章“发生的地点在何处？”介绍了地理数据以及如何将地理信息纳入数据分析中。地理信息首先是位置，以经度和纬度描述。位置也可以用不同等级的地理信息描述，例如人口普查区、邮政编码区域，以及其他我们熟悉的国家和省份，这些数据都可从人口统计局(或是其他相似的政府机构)获取。这一章也讨论了如何使用不同地理等级比较结果集。后，不包含地图的地理信息是不完整的。使用基础的Excel功能，可以创建非常初级的地图。第5章“关于时间”讨论了客户行为的另一个关键特征：什么时候发生。该章描述了如何访问数据库中的日期和时间，以及如何使用这些信息来帮助理解客户。该章包含的示例，可以用于准确地比较不同年份的数据，并从历史上计算每天的活跃客户数量。该章后介绍Excel中的一个简单的动画——也是本书中一处使用Visual Basic的地方。第6章和第7章介绍了用于理解客户随时间变化的重要的数据分析技术。在传统的统计学中，生存分析根深蒂固，而且它也很适合处理与客户相关的问题。第6章“客户的持续时间有多久？使用生存分析理解客户和他们的价值”介绍了风险率和生存率的基本思想，解释了如何使用SQL和Excel简单地计算它们。或许令人感到惊讶的是，在使用生存分析时，并不需要复杂的统计学工具。第6章后续介绍了生存分析应用在实际业务中的重要性，例如平均客户生命周期。然后讲解如何将这些片段拼接在一起，形成对客户值计算的预测。第7章“影响生存率的因素：客户任期”扩展讨论三个不同的领域。，它解决了在以客户为中心的数据库中的重要问题：左截断(left-truncation)。第二，它介绍了生存分析领域中的一个非常有趣的思想：竞争风险。这个思想考虑了一个事实，即客户是因不同原因而离开的。第三，将生存分析应用在分析前和分析后。即当客户在其生命周期内发生一些事情时，我们如何量化所发生的事情，例如量化客户加入忠诚计划之后的影响，或量化一次失败的主要计费方法。第8章至第10章使用SQL和Excel介绍如何理解客户正在购买的内容。第8章“多次购买以及其他重复事件”介绍了关于购买事件的所有事——什么时候发生，在哪里发生，发生频率——除了购买的东西。该章介绍了RFM，一种理解客户购买行为的传统技术。同时介绍了随时间推移，在识别客户时的种种问题。即使是在我们查看详细的购买信息之前，我们也能发现很多关于购买的信息。在第9章“购物车里有什么？购物车分析”中，产品成了焦点。该章介绍了随时间推移，针对购买行为的探索性分析。该章包括了如何识别驱动客户行为的产品，同时介绍了Excel中一些有趣的可视化方法。第10章“关联规则”转移到对关联规则的正式讨论。关联规则是指被同时购买或按序购买的产品组合。在SQL中建立关联规则是相当复杂的。本章讨论的方法扩展了传统的关联规则分析，介绍更有效的替换指标，并展示如何生成不同事物的组合。例如，单击会导致一次购买行为(使用网站的一个实例)。在本章中解释的关联规则技术，比数据挖掘工具中的技术更强大，因为这里的技术是可以扩展的，并使用支持度、置信度和提升度之外的指标。建模技术接下来的3章讨论统计学和数据挖掘的建模技术和方法。第11章“SQL数据挖掘模型”介绍了数据挖掘的建模思想，以及建模相关的名词。同时讨论了一些重要的模型类型，这些模型适用于处理业务问题和SQL环境。相似性模型找到与给定示例相似的事物。查找模型使用查找表返回模型评分。该章同时介绍了一种更复杂的建模技术，即朴素贝叶斯模型。这门技术可以总结不同业务维度的信息来估算未知的数值。第12章“拟合线：线性回归模型”介绍了一种更传统的统计学技术：线性回归。该章介绍了不同种类的线性回归，包括多项式回归、加权回归、多维回归和指数回归。这些内容以Excel图表的形式介绍，同时包含R2值，用于衡量模型与数据的拟合度。对回归的介绍同时用到了Excel和SQL。虽然Excel中有几种内置的功能可以处理回归问题，但Solver比这些内置功能更强大。本章从线性回归的角度介绍了Solver(Solver是可与Excel绑定的免费加载项)。第13章“为进一步分析数据创建客户签名”介绍了客户签名。客户签名是一个数据结构，它总结了客户在某个特定的时间点的数据。客户签名在建模时非常强大。在介绍该章时认识到虽然SQL和Excel都非常强大，但有时还需要一些更复杂的工具。很多情况下，客户签名是总结客户信息的正确方法，而且SQL是完成这类总结的强大工具。性能编写SQL查询的一个原因是性能——通过至少完成一些分析工作，可以将已有的硬件资源分配给关系型数据库。编写一本关于通用SQL而非指定数据库的书籍，其缺点就是缺少关于特定数据库的一些技巧和提示。令人欣慰的是，很多关于编写SQL的实践能够普遍提升查询在不同数据库中的执行速度。第14章“性能问题：高效使用SQL”致力于这个话题。其中特别讨论了索引和如何利用索引，同时还介绍了编写查询的不同方法？——以及为什么有些方法的性能更好。本书读者对象本书面向不同技术等级的各类读者。技术方面不足的管理者，特别是那些负责理解客户或业务单元的管理者。通常情况下，这样的人精通Excel，然而，他们所需要的数据存储于关系型数据库中。为了帮助他们，本书中的示例提供了有用的结果集。这些示例十分详尽，不仅展示了业务问题，同时展示了技术方法和结果。另一部分读者，他们的工作是理解数据和客户，通常他们的职位描述中包含“分析师”字样。这些人通常使用Excel和其他工具，有时直接访问数据仓库或一些以客户为中心的数据库。本书能帮助他们提高SQL查询技巧，展示好的图表示例，以及介绍生存分析和关联规则，以便他们理解客户和业务。一部分重要的读者是数据科学家，他们精通诸如R或Python这样的工具，但是他们发现需要学习其他的工具。在业务世界中，以编程为中心的工具可能并不足以解决问题，分析师可能会发现他们不得不直接处理关系型数据库中的数据，并以Excel形式展现给用户。技术等级更高的是统计学家，他们通常使用有特殊功能的工具，例如SAS、SPSS、R和S-plus。然而，数据存储于数据库中。本书可以在SQL技术方面为他们提供帮助，并提供数据分析示例以帮助他们解决业务问题。此外，数据库管理员、数据库设计者和架构师应该会发现本书是非常有趣的。在不同章节中展示的查询，说明了人们对数据的使用方式和方法。这些查询应该可以促进数据库管理员和设计者创建更适合使用的高效数据库。建议所有的读者，即使是技术专家，阅读或至少浏览前3章内容。这些章节全部从分析海量数据的视角，介绍SQL、Excel和统计学知识。这个视角与平常所读书籍的视角不同。在这些章节中，有相当一部分的内容和想法贯穿全书，例如样本数据、数据流、SQL语法和格式转换、出色的图标绘制。需要的工具本书是独立的——读者应该可以直接通过书中的内容阅读并学习。本书中的所有SQL语句都经过测试(在微软SQL Server数据库上，少量查询在其他数据库(PostgresSQL)上测试)。可以从网上下载数据集和结果，网址为www.wiley.com/go/data-analysisusingsqlandexcel2e。对于想要尝试的读者，我们建议下载数据并执行书中的示例代码。本书中，多数示例是与数据库供应商无关的，因此，它们(或稍作修改后)应该可以在所有的关系型数据库中执行。这里不建议使用Microsoft Access或MySQL，因为它们缺少窗口函数——窗口函数是分析性查询的关键功能。如果没有数据库，可以下载一些程序包；数据库供应商通常会提供一些免费的单机版本。例如，SQL Server Express是微软提供的免费SQL Server版本，Oracle也提供免费版本的Oracle数据库，可以从www.postgres.org下载PostgresSQL数据库，其他数据库也有它们的免费版本。网站内容介绍配套网站(www.wiley.com/go/dataanalysisusingsqlandexcel2e)上包含本书使用的数据集。这些数据集包含如下信息：● 引用表。共有3个引用表，其中两张表包含人口统计信息(来自于人口统计局2000年的统计数据)，另一张表包含关于日期的日历信息。● Subscribers数据集，用于描述移动电话公司的客户子集。● Purchases数据集，用于描述客户购买模式的数据集。下载这些数据的同时，还可以下载将数据导入SQL Server和其他数据库的使用说明。此外，配套网站的其他页面包含更多的信息。例如，将数据导入常见数据库中的脚本，包含SQL查询的工作表，以及本书中使用Excel生成的所有表格和图表。总  结本书起源于一个同事的问题，他询问是否有一本关于使用SQL做数据分析的参考书。然而，所需要的并不是简单的关于SQL的参考书，即使它侧重介绍使用SQL做数据查询的实际使用。对于数据分析，不能凭空学习SQL。一个SQL查询，不管它编写的多么精妙，通常不是一个业务问题的完整解决方案。业务问题，需要被转换为可以使用查询回答的问题。然后需要将结果展示出来，通常以表格或Excel图表的形式。笔者想要扩展这个观点。在现实世界中，也不能凭空学习统计学知识。曾经，收集数据不仅花费时间且难以操作。现在，数据量非常足够。例如，本书的配套网站，只需要轻点几下，就能上传几GB的数据。数据分析的问题不再局限于几个统计学方法，同时包括管理和抽取数据。本书将三个核心概念融入到解决问题这一条线中。在笔者的数据挖掘生涯中，笔者发现SQL、Excel和统计学是分析数据的关键性工具，比某些特殊的技术更加重要。希望本书可以帮助读者改进他们的技术，并为他们理解客户和理解业务提供新思路。