第1章 高效处理千万数据

小白进入公司已经一年有余，在自身的不断努力下，工作表现出色，获得领导及同事们的一致认可与赞扬，并获得“年度优秀员工”称号。

随着公司的业务规模变大，业务运营数据也迅速增长。为了充分利用好这些数据，公司领导决定成立运营分析部门。Mr.林参加了公司内部竞聘，通过层层筛选与选拔，众望所归地成为了运营分析部门的经理，负责部门运作及管理，为公司业务运营提供有效的数据分析支撑。

由于运营分析部门刚成立，急需招兵买马，因此小白也成为Mr.林“心仪”的对象。

Mr.林找到小白：小白，愿意加入运营分析团队吗？

小白兴奋地说：当然愿意啦！梦寐以求的事情，我就担心牛董不放人。

Mr.林：你放心！牛董那边我去说，只要你愿意就行。我先找牛董要人，现在领导层都很重视运营分析工作，等事情定下了就通知你过来上班。

小白美滋滋地说：好的。

半小时后，Mr.林打电话给小白：小白，牛董已经同意了，并且已经通知HR安排其他同事跟你交接工作。你今天交接完工作，明天就带上你的家当过来吧。

小白很快就进入了状态：遵命，领导。

第二天上班一大早小白带着全部家当来到Mr.林办公桌前报到。

Mr.林惊讶地问：小白，你就这点家当啊？除了一台笔记本电脑外，就一个保温杯、一个靠枕、一小瓶绿色植物、一本记事本，外加一支笔！跟我见到的其他女同事完全不一样，她们还有各种五花八门的小摆设、小公仔等。

小白淡定地说：嘿嘿！我就喜欢简单，就像做数据分析一样，简单而不空洞，能说明问题就好。

Mr.林面露喜悦之色：小白，我果然没选错你，对我胃口。

小白向Mr.林略微弯了下腰说道：Mr.林，请多多指教！

Mr.林：那我们就开始工作吧！小白，先给你介绍下我们部门的主要职责。考一下你，数据分析在我们日常经营分析工作中的作用体现在哪几方面呢？

小白底气十足：这个难不倒我，我刚进公司的第一天，您就给我介绍了数据分析，谈到它的作用。在我们日常经营分析工作中，数据分析主要有三大作用，如图1-1所示。

图1-1 数据分析三大作用

正是因为您告诉我数据分析这三大作用，我才对数据分析有了更深刻的理解与认识，加上您传授的实用数据分析方法与技巧，我对牛董交办的工作，才能轻松自如地处理完成。

Mr.林：说得一点也不错！我们运营分析部的工作正是基于数据分析这三大作用展开的。所以，运营分析部的主要职责如下：

① 负责完成公司运营日报、周报、月报等日常通报，告诉公司领导及运营部门现阶段公司整体运营情况，这是通过各个关键经营指标完成情况来衡量的。

② 根据公司运营需要，开展业务专题分析。比如基于日报、周报、月报的现状分析，我们对公司的运营情况有了基本了解，但这还不够，还需要知道运营情况具体好在哪里，差在哪里，是什么原因引起的。这时就需要开展原因分析，以进一步确定业务变动的具体原因。

③ 根据公司运营需要，开展市场研究工作。如果现有数据无法满足分析需求，就需要通过外部用户调研进行补充说明，我们才能进一步了解用户的真实想法与需求。

④ 开展预测分析，预测公司未来发展趋势，为公司制订运营目标及策略提供有效的决策依据，以保证公司的可持续健康发展。

⑤ 搭建公司经营分析体系，指导公司业务运营。

以上5条就是我们运营分析部门现阶段的主要工作职责，我会带领你和其他同事一起完成。

小白：好的，那我们接下来要做什么？

Mr.林：我要先对你进行一些培训，主要是数据处理与分析的应用工具的培训。

小白满脸疑惑地问道：这些工作不是用Excel就可以完成么？

Mr.林：嘿嘿！Excel当然是非常实用的数据分析工具，不过那是有前提条件的，因为Excel对数据有限制，Excel 97-2003版本，能容纳的行与列数都是有限制的，具体如图1-2所示，Excel 2007-2010版本最多也只有1048576行、16384列。

图1-2 Excel各版本对数据的限制

现在已经到了大数据时代，数据量动不动就超过百万条，Excel已经满足不了数据处理与分析的需求了。

没等Mr.林说完，小白又发问了：什么是大数据呢？

Mr.林耐心地解释道：大数据具有4大特点，可以用4个“V”来概括，如图1-3所示。

图1-3 大数据4大特点

举个例子，我们公司有1000多万的用户，单单一个用户信息表，Excel 2007—2010版本就无法容纳得下，更别说1000多万用户的购买行为数据。

这时候我们就要借助于数据库来实现数据的高效存储、处理、分析。

1.1 最容易上手的数据库

1.1.1 数据库那些事儿

Mr.林：我们先来认识下什么是数据库吧！

小白迫不及待地说：非常期待，快开始吧！

Mr.林：数据库（Database）是按照数据结构来组织、存储和管理数据的仓库。它利用数据库中的各种对象，记录、处理和分析各种数据。

随着现代社会进入信息时代，我们每天的工作和生活都离不开各种信息。对这样的海量数据，这时就需要采用数据库对其进行有效的存储与管理，并运用数据库进行合理的处理与分析，使其转化为有价值的数据信息。

图1-3 大数据4大特点

一个通用数据库具有以下几项基本功能。

★ 向数据库中添加新数据记录，例如增加用户注册信息。

★ 编辑数据库中的现有数据，例如修改某个用户信息。

★ 删除数据库中信息记录，例如删除失去时效性的数据，以释放存储空间。

★ 以不同方式组织和查看数据，例如对数据进行查询、处理与分析。

常用的数据库有Oracle、Microsoft SQL Server、MySQL、Microsoft Access等关系型数据库，随着大数据时代的到来，相关的数据库技术也快速发展，如基于NoSQL技术的分布式数据库Hbase、MongoDB、Redis等。

Access数据库

Mr.林：我们从关系型数据库Access入手，因为它够友好、够简单，会让我们的学习之旅更轻松。

小白：好的。

Mr.林：Access数据库是Microsoft Office办公软件中一个极为重要的组成部分，是一种关系数据库管理系统软件，它能够帮助用户处理各种海量信息，不仅能存储数据，更重要的是还能够对数据进行处理和分析，数据处理功能比Excel更胜一筹。由于目前Access 2010为较为常用版本，所以我们将基于Access 2010来学习数据处理和分析（如图1-5所示）。

图1-5 Access数据库示例

在Access 2010中，数据库窗口中包含“表”、“查询”、“窗体”、“报表”、“宏”、“模块”六大对象。在数据库中，“表”用来存储数据；“查询”用来查找数据；“窗体”、“报表”用于获取数据；而“宏”和“模块”则用来实现数据的自动化操作。

Access 2010数据库还提供了多种向导、生成器、模板，把数据存储、数据查询、界面设计、报表生成等操作规范化，为建立功能完善的数据库管理系统提供了方便，也使得普通用户不必编写代码，就可以完成大部分数据管理的任务，操作简便，容易使用和掌握。

Access数据库作为Microsoft Office办公软件包中的一员，它还可以与Excel、Word、Power-Point、Outlook等软件进行数据的交互与共享，例如分析报告的自动化，后面我会进一步介绍。

小白：好的。

Mr.林：下面我们就学习用Access 2010进行数据处理与分析，用到的主要对象是：表和查询。

（1）表

作为一个数据库，最基本的组成单位就是表。建立和规划数据库，首先要做的就是建立各种数据表。数据表是数据库中存储数据的唯一单位，数据库将各种信息分门别类地存放在各种数据表中，例如用户信息表、订单表、采购表等。

（2）查询

查询是数据库中应用最多的对象之一，可执行很多不同的功能，最常用的功能是根据指定条件从表中检索数据。

查询和表的区别在于，查询中的所有数据都不是真正单独存在的。查询实际上是一个固定的筛选，它根据指定条件将表中的数据筛选出来，并以表的形式返回筛选结果。

在Access数据库中，我们就是采用查询方式进行数据处理与分析的。

优势与不足

小白疑惑不解地问：为什么用Access数据库，而不用Oracle、Microsoft SQL Server等数据库呢？

Mr.林：因为Access数据库与Oracle等其他关系型数据库相比具有以下两大优势：

（1）操作界面友好，易操作

Access与Excel、PowerPoint、Word都是微软Office产品，只要熟悉Excel、PowerPoint、Word中的任一款软件，即使没有数据库经验，对Access也能快速上手。Access风格与Windows完全一样，用户想要生成对象并应用，只要使用鼠标进行拖放即可，非常直观方便。并且作为Office办公软件的一部分，Access可以与Office其他软件集成，实现无缝连接。

（2）Access查询处理可直接生成相应的SQL语句

通过Access查询向导设置好需要的表关联及查询条件，单击“SQL视图”，即可获取相应的SQL语句，无须重新编写。在此基础上，还可以进行简单的调整、优化，即可转化为所需的SQL语句，方便快捷。

小白心中释然：那我就放心了，你没说之前，我还担心数据库比较难学呢。

Mr.林：不用担心，有Mr.林在嘛，包教包会，我们继续。

有优势，自然也有不足，Access是小型数据库，与Oracle等其他关系型数据库相比存在以下不足。

★ 数据库过大时（一般Access数据库文件百兆以上），其性能会变差。

★ 记录数过多时（一般记录数达到千万条以上），其性能会变差。

★ Access数据库中每个数据库文件上限为2GB。

虽然Access数据库存在以上三大不足，但并不妨碍我们使用它完成日常工作与学习任务，因为用它学习SQL处理数据真的很方便，不需要写SQL语句。只要数据记录不超亿条，其处理速度还是可以接受的，数据记录越少，其处理速度就越快。

1.1.2 万能的SQL

小白：Mr.林，你刚才提到了好几次SQL，什么是SQL呀？

Mr.林： SQL（Structured Query Language）是结构化查询语言，它是一种通用的关系型数据库操作语言。简单来说，它就是让数据库按我们的意思来实现查询操作的语言。

说到这里，小白灵机一动：我可不可以这样理解：SQL就好比动画片《葫芦娃兄弟》里那个女妖精手中的宝贝——如意，如意、如意，按我心意，快快显灵……

Mr.林忍不住捧腹大笑：哈哈！我看行，还是你机灵，你这个比喻既生动又形象。

由于SQL功能丰富强大，语言简洁易学，使用方法灵活，目前所有主要的关系数据库管理系统都支持SQL。

虽然Access数据库大部分查询都可通过菜单完成，不需要用到SQL语句，但是如果想真正利用Access数据库强大的数据处理、分析能力，那么掌握SQL是非常必要的。

作为一名优秀的数据分析师，只有亲自经历在数据库中处理与分析数据的过程，才能对分析结果有更深层次的认识，同时也会加深对业务的理解，否则看到的只是一个个数字，并不能体会其内涵。

另外，业内人士常说的数据挖掘，很多是通过对历史数据进行建模预测，生成一定的规则，然后数据库工程师将生成的规则编写成相应的SQL语句，并编写成数据库的存储过程，可定期执行它们得到数据模型结果。

最后，处理大数据的Hadoop，所使用的Hive语言（HQL），也是与SQL语言基本一致，只不过部分语句的编写或功能存在差异。掌握了SQL，再学习HQL就非常容易了。

小白：那么如何编写SQL语句呢？

基本语法

Mr.林：我们现在来了解一下SQL基本语法，常用的SQL语句如图1-6所示。

图1-6 Access数据库常用的SQL语句

我们做数据分析时，在数据库中主要的操作就是数据合并、数据分组、数据去重等，这些操作都是通过查询来完成的。因此，数据查询是数据库的核心操作。而在SQL查询语言中有一条查询命令，即SELECT语句。

SELECT语句是SQL的核心语言，它能完成强大的查询功能，根据指定的条件规则从数据库中查询出所要的数据。SELECT语句的基本语法是：

SELECT字段1,字段2,字段3,……
FROM表
WHERE条件

小白挠了挠头：不是太明白，能否举个例子？

Mr.林灵机一动：那好，我就给你举生活中例子：假设你爸妈催你结婚，并且他们已经上婚姻中介所帮你物色对象相亲。

小白红着小脸，不好意思地问道：你怎么知道我爸妈在催我？

Mr.林：人之常情呀！老人家都希望自己儿女早点结婚，等着抱孙子呢！我们继续刚才的例子。中介所工作人员从他们的会员数据库中按你爸妈的要求筛选出目标人选，供他们进一步选择，那么婚姻中介所的工作人员会在他的数据库命令窗口写下如下SQL语句：

SELECT姓名,性别,年龄,身高,婚姻状况,教育背景,月收入,是否有房,是否有车,备注
FROM会员表
WHERE性别=男
AND年龄BETWEEN(26,30)
AND身高BETWEEN(170,180)
AND婚姻状况=未婚
AND教育背景IN( 本科, 研究生)
AND月收入>=8000
AND是否有房=是
AND是否有车=是
AND备注IN ( 细心, 大方, 浪漫, 英俊, 绅士, 智慧 )
ORDER BY月收入DESC;

小白：您举的这个生动例子，确实很清晰直观。

我爸妈的要求，不对，差点被您绕晕了，应该是您假设我爸妈的要求：首先必须是男的，年龄在26至30岁之间，身高在1米7至1米8之间，婚姻状况为未婚，教育背景为本科或研究生，月收入不低于8000元，必须有房有车，还要求细心、大方、浪漫、英俊、绅士、智慧。最后筛出来的名单再按月收入降序排序。

Oh！My God！上哪找这样条件的未婚男士？如果有的话，我就考虑考虑。

Mr.林：哈哈！小白，你入戏还真快，这么快就进入角色了。

小白的脸瞬间又红了：Mr.林，您又在给我下套呀！不过这样的例子确实很生动，一看就懂，我大概知道SELECT语句怎么用了。

注意事项

Mr.林：好的，现在我们一起来了解下编写SQL语句时的一些注意事项。

① SQL语句中，英文字母大写或小写均可。

② 每个SQL语句的关键字用空格符号分隔，例如：

SELECT字段FROM表

③ 字段或参数之间用逗号分隔，例如：

SELECT姓名,性别,年龄,身高,教育背景
FROM会员表
WHERE教育背景IN( 本科 , 研究生 )

④ SQL语句中如参数为字符型，那么需要使用单引号，数值型不使用单引号，例如：

SELECT姓名,性别,月收入
FROM会员表
WHERE性别=男AND月收入>=8000

⑤ SQL语句结束时，在语句结尾处添加分号。在Access数据库中，用分号结束对于SQL语句不是必须的。

⑥ SQL语句中如表名、字段名中出现空格、“/”、“\”等特殊字符时，需用方括号“[]”将含有特殊字符的表名或字段名括起来，以免得到不正确的结果或SQL语句无法运行。

⑦ SQL语句中，“*”代表选择选定表格中的所有字段，并且按照其在数据库中的固定顺序来排序，例如：

SELECT * FROM表

⑧ 在函数参数或条件查询中，如果参数或查询条件为日期和时间类型数据，需要在数据值两端加上井字符号“#”，以表示数据类型为日期型。

⑨ SQL语句中使用的逗号、分号、单引号、括号等符号均为英文状态下输入的符号。

⑩ 应尽量避免在数据库中进行全表扫描。首先应考虑用WHERE子句筛选出需要的数据，其次在WHERE子句中，应尽量避免使用“!=”或“< >”、“OR”等，最后应尽量避免在WHERE子句中对字段进行函数操作，否则将进行全表扫描。

其他注意事项等介绍到具体示例时再进行讲解。

小白：好的，您刚才说的10条注意事项我都记下了，回去我再认真复习复习。

1.1.3 两招导入数据

Mr.林：小白，接下来我们学习如何将数据导入到数据库中。因为数据量大才采用Access数据库进行数据处理与分析，而大型数据文件一般以TXT文本形式存储，所以我们主要学习如何导入TXT文本数据。还记得我教你的如何将TXT文本数据导入Excel么？

小白：当然记得，工作中我常用呢。

Mr.林：很好，在Access数据库中导入TXT文本数据与Excel中的操作步骤类似，都是按照一定的数据分隔符号或者数据宽度，将文本中的数据自动分配到数据表中。

在Access数据库中主要有两种方式：一是直接导入法，二是建立链接法。下面以导入“订购明细.txt”数据为例，一起来学习这两种数据导入方法。

直接导入法

STEP 01 启动Access，单击【文件】选项卡，单击【新建】按钮，在【可用模板】中选择【空数据库】，并为新建的数据库文件命名，设置好存放路径，最后单击【创建】按钮，如图1-7所示。

图1-7 新建数据库文件

STEP 02 在创建好的数据库中，单击【外部数据】选项卡，在【导入并链接】组中单击【文本文件】按钮，弹出如图1-8所示的对话框，浏览指定数据源，并在【指定数据在当前数据库中的存储方式和存储位置】项中，选中默认的【将源数据导入当前数据库的新表中】项，单击【确定】按钮。

图1-8 【获取外部数据—文本文件】对话框

STEP 03 在弹出的第一个【导入文本向导】对话框中，选中默认的【带分隔符】作为数据分隔方式，如图1-9所示，单击【下一步】按钮。

图1-9 【导入文本向导】对话框1

STEP 04 在弹出的第二个【导入文本向导】对话框中（如图1-10所示），选择【逗号】作为分隔符，并勾选【第一行包含字段名称】复选框，单击【下一步】按钮。

图1-10 【导入文本向导】对话框2

需要说明的是，分隔符及第一行是否包含字段名称需根据数据本身的实际情况进行选择，本例中为以逗号分隔，并且第一行包含字段名称。

STEP 05 在弹出的第三个【导入文本向导】对话框中（如图1-11所示），可对文本数据的各个字段名称、数据类型、索引以及是否导入字段进行设置，用户可根据数据本身的实际情况进行选择与设置。本例采用默认设置，单击【下一步】按钮。

图1-11 【导入文本向导】对话框3

STEP 06 在弹出的第四个【导入文本向导】对话框中（如图1-12所示），选择【让Access添加主键】，则Access数据库会将“ID”字段添加为目标表中的第一个字段，并且用从1开始的唯一ID自动填充它，单击【完成】按钮。

图1-12 【导入文本向导】对话框4

STEP 07 在弹出的【保存导入步骤】对话框中保存详细信息，有助于在以后重复执行该操作，而不必每次都逐步完成向导。可根据数据导入的需求，选择是否保存导入步骤，本例选择不保存导入步骤，单击【关闭】按钮。

文本数据导入Access数据库后的结果如图1-13所示，用鼠标双击左边Access对象框里的“订购明细”表，即可在右边窗口显示产品订购明细。

图1-13 文本数据导入结果

小白：Mr.林，刚才您说到两个新名词“索引”、“主键”，这两个是什么呀？

Mr.林：索引相当于对指定的列进行排序，它就好比是一本书的目录，通过它可以快速查询到结果，不需要进行全表扫描，可以大大加快数据库的查询速度。

主键是确定数据中每一条记录的唯一标识，其实也是一个索引，是一个特殊索引，因为主键所在列里的每一个记录都是唯一的，在同一个表里只能有一个主键。简单来说，主键就是所在列不能出现相同记录的特殊索引，且这个索引只能在表里出现一次。

综上所述，主键与索引的具体区别为以下四点。

① 主键用于标识数据库记录的唯一性，不允许记录重复，且键值不能为空。主键也是一个特殊索引，主键等于索引，索引不一定等于主键。

② 索引可以提高查询速度，通过它可以快速查询到结果，不需要进行全表扫描。

③ 使用主键，数据库会自动创建主索引，也可以在非主键上创建索引，提高查询速度。

④ 数据表中只允许有一个主键，但是可以有多个索引。

在Access数据库中，虽然主键不是必需的，但最好为每个表都设置一个主键，这样可提高查询速度。

小白点了点头：明白。

建立链接法

Mr.林：现在我们来学习第二种导入方法：建立链接法。

建立链接法与直接导入法步骤基本类似，不同的地方就在于步骤2与步骤7。在步骤2中，对于【指定数据在当前数据库中的存储方式和存储位置】项，更改为选中【通过创建链接表来链接到数据源】，如图1-14所示。

图1-14 【获取外部数据—文本文件】对话框

因为这种方法是以链接方式建立数据库与源数据的链接关系，所以只要不删除，这个链接关系一直存在，也就无须【保存导入步骤】，所以采用链接方式就没有步骤7。

其余数据导入操作步骤基本一致，小白，你可以事后自行练习这两种文本数据导入方法。

小白：好的，您介绍的“直接导入法”与“建立链接法”这两种方法之间有何区别呢？

Mr.林：你这个问题问得真好，不愧是做数据分析的好苗子。

★ 直接导入法：Access数据库中的表与数据源脱离了联系，对数据的更改不会影响源文本数据文件。

★ 建立链接法：链接表显示源文本文件中的数据，但是它实际上并不将数据存储在数据库中，对源文本文件进行的任何更改都将自动反映到链接表中，即数据会随数据源的变化而自动更新。

你可以根据实际需求，选择“直接导入法”或“建立链接法”导入文本数据。

Mr.林：如果数据是以Excel格式存储的，将Excel数据导入Access数据库的步骤基本与TXT文本数据导入步骤类似，同样你也可事后自行练习数据导入方法。

小白：好的。

1.1.4 数据合并的二三式

Mr.林：小白，接下来我们就要开始学习用Access数据库处理数据啦！再考一下你，什么是数据处理？数据处理主要包含哪些操作？

小白：这个难不倒我。数据处理就是根据数据分析的目的，将采集到的数据，用适当的处理方法整理和加工，形成适合数据分析要求的样式，也就是一维表。它是数据分析前必不可少的阶段。数据处理包括数据合并、数据计算、数据分组、数据去重等操作。

Mr.林：说的没错，我们先来学习数据合并。数据合并包括横向合并与纵向合并。

横向合并

Mr.林：横向合并就是从多个表中，根据各表共有的关键字段，把各表所需的记录一一对应起来。这个功能也相当于Excel中的VLOOKUP精确匹配功能。

例如刚才导入的“订购明细”表，它只记录了用户订购单的相应信息，但缺乏用户的相关背景信息，如果要统计不同性别的用户的产品购买偏好，就必须获得用户的性别信息。这时就需要将“订购明细”表与“用户明细”表根据关键字段“用户ID”进行关联匹配查询，如图1-15所示。

图1-15 “订购明细”表与“用户明细”表关系图

现在就看看在Access数据库中如何实现数据横向合并。

小白：接下来要先做什么呢？怎么做？

Mr.林：主要有两种方式，一种是菜单操作法，另一种就是SQL查询法。我们先学习菜单操作法。

（1）菜单操作法

首先，建立“订购明细”与“用户明细”两表的数据库关系。

STEP 01 单击【数据库工具】选项卡，在【关系】组中单击【关系】按钮。

STEP 02 在弹出的【显示表】对话框中，同时选中“订购明细”与“用户明细”两表（可结合Shift键同时选中，也可结合Ctrl键依次选中），如图1-16所示。单击【添加】按钮，再单击【关闭】按钮，以关闭【显示表】对话框。

图1-16 【显示表】对话框

STEP 03 在【关系管理器】中（如图1-17所示），将“订购明细”表中的“用户ID”字段用鼠标拖到“用户明细”表中的“用户ID”字段处，松开鼠标。

图1-17 关系管理器

STEP 04 在弹出【编辑关系】对话框中，单击【联接类型】按钮，默认选择第一种关系【只包含两个表中联接字段相等的行】，单击【确定】按钮，返回【编辑关系】对话框，如图1-18所示。

图1-18 【编辑关系】与【联接属性】对话框

小白，这里需要注意：在本例中，我们确定“订购明细”表中的“用户ID”记录信息都在“用户明细”表中，所以可以用第一种关系；反之，则需要选择第三种关系，以确保“订购明细”表信息的完整性。

STEP 05 单击【创建】按钮，返回【关系管理器】，可以看到，在【关系】窗口中两个表的“用户ID”字段之间出现了一条关系连接线，如图1-19所示。

图1-19 两表关系连接示例

STEP 06 单击【保存】按钮，并关闭【关系管理器】。

Mr.林：小白，这样这两张表的关系就建立好了。接下来就要执行查询步骤，从“订购明细”表中选取“产品”字段，从“用户明细”表中选取“性别”字段，就能取出我们需要的不同用户性别与所购买产品一一对应的明细数据，以方便统计不同性别的用户的产品购买偏好。

可利用Access数据库中“简单查询”功能来实现，具体查询操作步骤如下。

STEP 01 单击【创建】选项卡，在【查询】组中单击【查询向导】按钮。

STEP 02 在弹出的【新建查询】对话框中，默认选择【简单查询向导】，如图1-20所示，单击【确定】按钮。

图1-20 【新建查询】对话框

STEP 03 在弹出的【简单查询向导】第一个对话框【表/查询】项中，选择“订购明细”表，并把“用户ID”、“产品”两个字段移至【选定字段】框中，如图1-21所示。

图1-21 【简单查询向导】对话框1

STEP 04 在【简单查询向导】第二个对话框【表/查询】项中，选择“用户明细”表，并把“性别”字段移至【选定字段】框中，如图1-22所示，单击【完成】按钮，在弹出的对话框中，单击【保存】按钮。

图1-22 【简单查询向导】对话框2

Mr.林：“订购明细”和“用户明细”两表联合查询的结果，如图1-23所示。

图1-23 简单查询结果示例

还没等Mr.林说完，小白就抢着说：哇！结果出来啦，确实比Excel方便！如果用VLOOKUP匹配这么大量的数据，至少也要好几分钟，要是匹配的字段较多，速度还会更慢。

Mr.林：没错，这就是数据库具备的Excel所不具备的优势。在Access数据库左侧对象栏中可以发现比查询前新增了一个查询对象“订购明细 查询”，这就是我们刚才新建的查询。只要双击它，Access数据库就会按指定的条件重新执行查询，如果数据量非常大，双击需谨慎！

小白笑嘻嘻地说：好的。

Mr.林：另外还有一种菜单操作法，如果你对Access查询功能及原理比较熟悉，还可以用“查询设计”新建查询，相比“查询向导”会快捷一点，当然这要看个人习惯与偏好，仁者见仁，智者见智。后面我会通过其他例子介绍“查询设计”功能的使用方法。

小白：好的。

（2）SQL查询法

Mr.林：现在我们就来学习SQL查询法。小白，记得我之前提到Access数据库中进行查询处理可直接生成相应的SQL语句，不需要我们亲自编写SQL语句吗？

小白：我记得呀！我也好奇到底Access数据库是怎么生成相应SQL语句的。

Mr.林故弄玄虚：呵呵！见证奇迹的时刻到了。在刚才的Access数据库查询结果窗口中（如图1-23所示），单击Access数据库窗口最右下方的按钮，效果如图1-24所示。

图1-24 SQL查询视图数据横向合并示例

Mr.林：小白，你看SQL视图窗口里不就是写好的SQL语句嘛！

小白瞪大眼睛：咦！果然，很神奇。

Mr.林：Access数据库可直接生成相应的SQL语句，我说的没错吧？让我们一起来看看生成的SQL语句与刚才的查询操作是否能对应上。

这一条SQL语句的意思是：选择查询“订购明细”表的“用户ID”、“产品”字段，以及相对应的“用户明细”表中的“性别”字段，从“用户明细”表内连接“订购明细”表选择，条件是“用户明细”表的“用户ID”字段与“订购明细”表的“用户ID”字段相等。

没错，对上了，Access数据库生成的SQL语句就是我们刚才联合查询的操作。

只需单击【设计】选项卡中【结果】组的【运行】按钮，如图1-24所示，Access数据库就直接执行查询操作。

小白：那么，内连接是什么意思呢？

Mr.林：这需要先了解下数据库连接关系，主要包含内连接（INNER JOIN）、左连接（LEFT JOIN）、右连接（RIGHT JOIN）三种数据库关系，使用时请注意区分（如图1-25所示）。

图1-25 三种数据库连接关系图

★ 内连接（INNER JOIN）：选择两个表中关键字段相匹配的记录，对应图1-18“联接属性”对话框中的第一个关系。

★ 左连接（LEFT JOIN）：选择第一个表中的所有记录以及第二个表中与关键字段相匹配的记录，对应图1-18“联接属性”对话框中的第二个关系。

★ 右连接（RIGHT JOIN）：选择第二个表中的所有记录以及第一个表中与关键字段相匹配的记录，对应图1-18“联接属性”对话框中的第三个关系。

小白：明白了。

Mr.林：这条SQL语句我们还可以进一步简化为：

SELECT B.用户ID, B.产品, A.性别
FROM用户明细AS A, 订购明细AS B
WHERE A.用户ID = B.用户ID;

① 这条SQL语句的条件采用WHERE子句进行编写，相对内连接（INNER JOIN）语法更容易理解。WHERE子句由一个运算符 (WHERE) 及后面的条件表达式（A.用户ID = B.用户ID) 组成。Access数据库会选择出符合WHERE子句所列条件的记录，如果没有指定WHERE子句，查询会返回表中的所有行。条件表达式的书写规则如图1-26所示。

图1-26 条件表达式的书写规则

② 这条SQL语句对“用户明细”表和“订购明细”表的表名分别重新命名为A、B，用关键字AS来命名；在编写SQL语句时，关键字AS可省略，直接在原表名后加上别名，中间以空格分隔。

③ 如果需要从不同表引用字段，先写上表名（或别名），再加上“点”（.），再加上相应字段，以区分不同表的相同的字段名，防止出错，提高查询效率，特别是在各表有相同字段的情况下，例如：B.用户ID。

④ 对字段名同样可以重新命名，其方法与为表命名别名的方式一致。采用别名的好处在于可简化表名，使SQL语句清晰易懂、易编写。

小白：明白，这些注意事项我都记下了。

纵向合并

Mr.林：现在我们来学习如何进行数据的纵向合并，也就是数据记录的合并。合并的表必须具有相同的字段结构，它们字段的数目必须相同，并且字段的数据类型也必须相同。

假设刚才的“订购明细”表是以每天一个表的方式存储的，即每天的数据保存为一个表，如“订购明细20110901”、“订购明细20110902”、“订购明细20110903”、“订购明细20110904”等，现在需要把它们合并到一张表中。

（1）菜单操作法

我们可采用Access数据库中“追加查询”功能来实现。先看看如何把“订购明细20110902”表追加到“订购明细20110901”中，具体查询操作步骤如下。

STEP 01 单击【创建】选项卡，在【查询】组中单击【查询设计】按钮。

STEP 02 在弹出的【设计视图】和【显示表】对话框中，选择“订购明细20110902”表，单击【添加】按钮将表添加进查询的【设计视图】，如图1-27所示。

图1-27 查询设计视图——【显示表】对话框

STEP 03 单击【设计】选项卡【查询类型】组的【追加】按钮，弹出【追加】对话框，在【表名称】下拉列表框中选择“订购明细20110901”作为目标表，如图1-28所示。

图1-28 追加查询设计视图—【追加】对话框

STEP 04 单击【确定】按钮，返回查询的【设计视图】，依次双击选择“订购明细20110901”表中所有字段，被选择的字段将在下面的查询设计网格中显示，如图1-29所示。

图1-29 追加查询设计视图—字段选择

STEP 05 单击【设计】选项卡中【结果】组的【运行】按钮（如图1-30所示），Access数据库将弹出如图1-31所示的提示框，单击【是】按钮，Access数据库直接执行追加查询操作。

图1-30 【设计】选项卡

图1-31 追加查询操作提示框

双击“订购明细20110901”表可以查看追加查询的结果。重复上述步骤，将“订购明细20110903”、“订购明细20110904”表中记录，通过“追加查询”功能追加至“订购明细20110901”表中，以完成数据纵向合并的目的。

如果不希望把各表数据记录都追加至“订购明细20110901”表中，而希望追加至表名为“订购明细201109”的空白数据表，可新建一张数据结构与“订购明细20110901”表一致，并且表名为“订购明细201109”的空白数据表，通过上述的“追加查询”功能，将“订购明细20110901”等表追加至“订购明细201109”表中。

小白：原来如此。可是我有个问题，如果需要合并的表较少，通过菜单操作还可接受，如果需要合并的表较多时，这样操作就比较麻烦了，该怎么办？

（2）SQL查询法

Mr.林：小白，你这个问题很不错，当需要合并的表较多时，这样操作确实效率低下，我们可以转变下思路，想一想能否采用SQL语句呢？

小白：对啊！我们可以从追加查询的SQL视图中取出相应的SQL语句，只要更改要追加数据的表名，分别运行，就比刚才重复菜单操作来得便捷。Mr.林，是这样的吗？

Mr.林：小白，不错啊！刚学的就马上用上了，你说的是一种思路，不过这样也要分开运行多次，有几个表就要运行几次，还是有点慢。我教你一个只要运行一次的妙招。

小白张大嘴巴：还有这样的妙招？Mr.林，快快教我。

Mr.林：就是用UNION ALL或UNION指令进行两表或多表合并，但是所有查询中的列数和列的顺序必须相同，数据类型必须兼容。

小白疑惑不解地问：这两个指令有何不同呢？什么情况下该用UNION ALL？什么情况下该用UNION？

Mr.林：UNION ALL就是将各表的记录合并到一起，并且对这些记录不做任何更改。而UNION会删除各表存在的重复记录，并对记录进行自动排序，这样UNION比UNION ALL需要更多的计算资源，所以尽可能使用UNION ALL指令进行各表合并。

一般需要使用UNION ALL指令的情况如下：

★ 知道有重复记录且想保留这些记录。

★ 知道不可能存在任何的重复记录。

★ 不在乎是否存在任何的重复记录。

现在我们就来看看如何使用UNION ALL来合并9月1日—9月4日的4张当日订购明细表，合并至“订购明细201109”的空白数据表，因为我们知道这4张当日订购明细表之间不可能存在重复记录，所以这里使用UNION ALL指令，具体步骤如下。

STEP 01 新建表名为“订购明细201109”的空白数据表，要求其表结构、各字段数据类型与“订购明细20110901”表一致，相应的SQL语句如下：

SELECT*INTO订购明细201109
FROM订购明细20110901
WHERE 1=2;

小白疑惑不解地问：为什么条件是“1=2”呢？

Mr.林：我们的目的是得到“订购明细20110901”表的结构，而不需要表里面的记录，因而需要设置一个不可能存在的条件，因为1是不可能等于2的，所以在建立的“订购明细201109”表里将插入0条记录，这样就巧妙地新建一张表结构、各字段数据类型与“订购明细20110901”表一致的空白数据表“订购明细201109”。

STEP 02 在刚才的Access数据库追加查询结果窗口中，单击Access数据库窗口最右下方的按钮，得到如下SQL语句：

INSERT INTO订购明细20110901(订单编号,订购日期,用户ID,产品,[单价(元)],数量,订购金额)
SELECT订单编号,订购日期,用户ID,产品,[单价(元)],数量,订购金额
FROM订购明细20110902;

STEP 03 修改、添加相应的SQL语句，结果如下（红色加粗部分为修改、添加之处）：

INSERT INTO订购明细201109(订单编号,订购日期,用户ID,产品,[单价(元)],数量,订购金额)
SELECT订单编号,订购日期,用户ID,产品,[单价(元)],数量,订购金额
FROM
(SELECT A.订单编号,A.订购日期,A.用户ID,A.产品,A.[单价(元)],A.数量,A.订购金额
FROM订购明细20110901 AS A
UNION ALL
SELECT B.订单编号,B.订购日期,B.用户ID,B.产品,B.[单价(元)],B.数量,B.订购金额
FROM订购明细20110902 AS B
UNION ALL
SELECT C.订单编号,C.订购日期,C.用户ID,C.产品,C.[单价(元)],C.数量,C.订购金额
FROM订购明细20110903 AS C
UNION ALL
SELECT D.订单编号,D.订购日期,D.用户ID,D.产品,D.[单价(元)],D.数量,D.订购金额
FROM订购明细20110904 AS D);

Mr.林看到小白疑惑不解的样子，就继续解释：下面圆括号里的语句是子查询语句，子查询的结果将生成一张数据表，它将作为插入语句（INSERT INTO）的源表，这也叫嵌套查询，原理就与IF函数的嵌套原理基本类似。

小白豁然开朗：原来是这样。

Mr.林：小白，你有没有发现，因为各个表的表结构、数据类型都是一样的，所以对于这段SQL语句我们还可以简化：

INSERT INTO订购明细201109
SELECT * FROM
(SELECT * FROM订购明细20110901
UNION ALL
SELECT * FROM订购明细20110902
UNION ALL
SELECT * FROM订购明细20110903
UNION ALL
SELECT * FROM订购明细20110904);

小白：果然简化了很多。这么说，当多张表的表结构、数据类型一样时，可以用“SELECT *”的方式查询所有字段与记录；当多张表的表结构、数据类型不一样时，就要单独提出需要的字段，有时候甚至还要进行字段类型的转换，将各表对应的各个字段类型统一，是这样的吗？

Mr.林：没错，孺子可教也！

接下来我们就要将修改好的SQL语句，复制至SQL视图窗口运行。

STEP 04 单击【创建】选项卡，在【查询】组中单击【查询设计】按钮，关闭弹出查询的【显示表】对话框，单击Access数据库窗口右下方的按钮，进入SQL视图窗口，将修改好的SQL语句复制至SQL视图窗口，如图1-32所示。

图1-32 SQL查询视图数据纵向合并示例

STEP 05 单击【设计】选项卡中【结果】组的【运行】按钮，如图1-32所示，Access数据库将弹出类似如图1-31所示的提示框，单击【是】按钮，Access数据库直接执行追加查询操作。

小白：Mr.林，我有个问题，刚才建空白数据表“订购明细201109”的时候，我们只用了“订购明细20110901”的表结构，那为什么不把9月1日—9月4日4张当日订购明细表的记录也一起加进去呢？

Mr.林：小白，不错，有想法。刚才我介绍的是追加查询的方法，你的提议是直接查询并把数据添加至新表中，这是可行的。这样好了，你来写相应的SQL语句。

小白不客气地说：那我就班门弄斧啦！我这就写来：

SELECT*INTO订购明细201109
FROM
(SELECT*FROM订购明细20110901
UNION ALL
SELECT*FROM订购明细20110902
UNION ALL
SELECT*FROM订购明细20110903
UNION ALL
SELECT*FROM订购明细20110904);

小白写完问道：Mr.林，您看，是这样的么？

Mr.林：咱们运行一下，不就知道结果了嘛！

小白：好的，那我单击“运行”啦！哇，果真！这样比较方便快捷，一步到位！

1.1.5 快速实现数据计算

Mr.林：小白，接下来学习在Access数据库中进行数据计算，这里的数据计算是指对原有的字段进行相应的计算得到新的字段，以满足我们的分析需求。你还记得数据计算有哪几种方式么？

小白：当然记得，工作中也常用到嘛！数据计算包括简单计算与函数计算。

★ 简单计算就是加、减、乘、除等简单算术运算。

★ 函数计算就是通过软件内置的函数进行计算，比如求和，求平均值、最大值、最小值等。

Mr.林：还不错，都没忘，那我们就先来学习Access数据库中的简单计算。

简单计算

Mr.林：以“订购明细”表为例，这个表里面有每张订单的详细信息，如订购的“产品”、“单价”、“数量”、“订购金额”，这里的“订购金额”就是通过“单价”x“数量”计算而来。现在假设没有这个“订购金额”字段，而需要通过简单计算方式来新增“订购金额”字段。

（1）菜单操作法

STEP 01 单击【创建】选项卡，在【查询】组中单击【查询设计】按钮。

STEP 02 在弹出查询的【设计视图】和【显示表】对话框中，选择“订购明细”表，单击【添加】按钮将表添加进查询的【设计视图】。

STEP 03 依次双击选择“订购明细”表中所有字段，被选择的字段会在下面的查询设计网格中显示，如图1-33所示。

图1-33 查询设计视图—字段选择

STEP 04 在第8个字段表中输入“订单金额： [单价(元)]*[数量]”，表示“订单金额=单价(元)×数量”，如图1-33所示。

STEP 05 单击【设计】选项卡中【结果】组的【运行】按钮，运行结果如图1-34所示。

图1-34 简单计算的运行结果

Mr.林：小白，你看，我们计算出来的“订单金额”与原表的“订购金额”数据一致。

小白：没错，完全相等。

（2）SQL查询法

Mr.林：我们再来看SQL查询法。同理，在刚才的Access数据库查询结果窗口中，单击Access数据库窗口右下方的按钮，得到如下简化的SQL语句：

SELECT订单编号,订购日期,用户ID,产品,[单价(元)],数量,订购金额,[单价(元)]*[数量]AS订单金额
FROM订购明细;

可以看出，在SQL语句中进行简单计算的方式，就是直接写出运算表达式，然后对新增的字段采用AS命令命名即可。

同样，我们只需单击【设计】选项卡中【结果】组的【运行】按钮，Access数据库直接按照编写好的SQL语句执行相应的查询操作。

小白：确实简单、方便。

函数计算

Mr.林：我们继续来学习Access数据库中的函数计算。

假如，现在我们需要了解截至2011年年底用户注册天数的分布，以了解现有存量用户的构成情况，为后续用户细分做准备。数据库中现有的“用户明细”表只有用户注册日期，我们需要通过相应的日期函数计算得到用户注册的天数。

我们可用DATEDIFF函数，它的作用与Excel中的DATEDIF函数一致，但用法略有不同，不同之处在于日期间隔参数移至表达式前部，其参数设置及说明详见图1-35，其语法如下：

图1-35 DAtEDlff函数参数设置及说明表

DATEDIFF（"参数",起始日期,结束日期）

再次提醒：在函数参数或条件查询中，若参数或查询条件为日期和时间类型，需要在数据值两端加上井字符号（#），以表示数据类型为日期型。

我们可以在如图1-33所示的查询设计网格字段中输入函数进行计算，也可以直接在SQL语句中进行计算。在此以SQL语句应用为例，编写的SQL语句如下：

SELECT用户ID,注册日期,DATEDIFF("D",注册日期,#2011-12-31#) AS注册天数
FROM用户明细;

字段可根据实际需求选择，尽量减少不必要的字段，字段越少越好，可大大提升系统运行效率。

将编写好的SQL语句直接复制至一个新建的查询SQL视图窗口中（操作步骤可参见前文），单击【运行】按钮，得到如图1-36所示的结果。

图1-36 DAtEDlff函数计算结果示例

其他Access数据库常用函数如图1-37所示，可根据实际计算需求采用。

图1-37 Access数据库常用函数

小白：好的，我先记下，留着备用。

1.1.6 数据分组小妙招

Mr.林：小白，还记得数据分析三大基本方法么？

小白：当然记得：对比、细分与预测。

Mr.林：是的，现在我们就要学习其中的细分方法，也就是数据分组。

进行数据分析时不仅要对总体的数量特征和数量关系进行分析，还要深入总体的内部进行分组分析。数据分组是一种重要的数据分析方法，这种方法根据数据分析对象的特征，按照一定的标志（指标），如业务、用户属性、时间等维度，把数据分析对象划分为不同的部分和类型来进行研究，以揭示其内在的联系和规律性。

常用的数据分组方式主要包括数值分组、日期/时间分组两种。

数值分组

（1）IIF函数法

Mr.林：小白，还记得Excel中的IF函数吧？

小白：当然，这个函数可好用了，IF与VLOOKUP函数相当于万金油，是工作中用到最多的两个函数。

Mr.林：现在我们要学的第一个用于数值分组的函数是IIF，它与Excel中的IF函数用法及功能一样。在Access数据库中，IIF函数最多可进行13层嵌套，如果嵌套超过13层，运行时Access数据库将提示“表达式过于复杂”。

IIF函数语法如下：

IIF (表达式，表达式成立时返回的值，表达式不成立时返回的值)

仍以“用户明细”表为例。表中有个“年龄”字段，我们需要了解用户年龄结构，这时就需要对用户年龄进行分组。下面就采用IIF函数进行分组，编写的SQL语句如下：

SELECT用户ID,年龄,
IIF(年龄<=20,"20岁及其以下",
IIF(年龄<=30,"21-30岁",
IIF(年龄<=40,"31-40岁",
"40岁以上"))) AS年龄分组
FROM用户明细;

将编写好的SQL语句直接复制至一个新建的查询SQL视图窗口中，单击【运行】按钮，得到如图1-38所示的结果。

小白：果然，IIF函数的用法及效果与Excel中的IF函数一样。

图1-38 llf函数分组结果示例

（2）CHOOSE函数法

Mr.林：第二个用于数值分组的函数CHOOSE，它与Excel中的CHOOSE函数用法及功能一样。CHOOSE函数语法如下：

CHOOSE (参数，结果1，结果2，……，结果N)

说明：

① 参数可为数值或表达式，如果参数或表达式返回的值为1，则函数CHOOSE返回结果1；如果参数或表达式返回的值为2，函数CHOOSE返回结果2，以此类推。

② 参数或表达式返回的值必须为1～254的数字，如果小于1或者大于254，则Access数据库将返回错误值“#VALUE!”。

③ 如果参数为小数，则在使用前将被截尾取整，即相当于Excel中的lNt函数效果。

仍以“用户明细”表为例，对用户年龄进行分组，下面就采用CHOOSE函数进行分组，编写的SQL语句如下：

SELECT用户ID,年龄,
CHOOSE((年龄-1)/10+1,"10岁及其以下","11-20岁","21-30岁","31-40岁","40岁以上") AS年龄分组
FROM用户明细;

将编写好的SQL语句直接复制至一个新建的查询SQL视图窗口中，单击【运行】按钮，得到如图1-39所示的结果。

图1-39 CHOOSE函数分组结果示例

小白：Mr.林，请教一个问题，为何表达式为“(年龄-1)/10+1”？

Mr.林：我们可以分步来看。首先假设年龄范围是1～10岁，那么“(年龄-1)/10”返回的值就落入[0,1)区间，而“(年龄-1)/10+1”返回的值就落入[1,2)区间，根据刚才介绍的函数说明的第3点“如果参数为小数，则在使用前将被截尾取整”原则，那么参数最终返回值为1，也就对应第1个结果“10岁及其以下”，以此类推，就可把用户年龄划分为不同的范围，从而保证各个用户年龄都能落入正确的区间。

小白：原来如此，我明白了。

（3）SWITCH函数法

Mr.林：第三个用于数值分组的函数是SWITCH。SWITCH函数语法如下：

SWITCH (条件1，结果1，条件2，结果2，……，条件N，结果N)

说明：

① 如果条件1为true，SWltCH将返回结果1，如果条件2为true，SWltCH将返回结果2，以此类推。

② 参数由成对的条件表达式和结果值组成，条件表达式按照从左到右的顺序求值，将返回与第一个求值结果为true的表达式相对应的结果值。

③ SWltCH函数在SQL语句中的条件表达式最多可以达到14个，如果多于14个表达式，将提示错误。

④ 如果所有表达式的结果值都不为true，SWltCH将返回Null。

我们仍以“用户明细”表为例，对用户年龄进行分组。下面就采用SWITCH函数进行分组，编写的SQL语句如下：

SELECT用户ID,年龄,
SWITCH(年龄<=20,"20岁及其以下",
   年龄<=30,"21-30岁",
   年龄<=40,"31-40岁",
   年龄>40,"40岁以上") AS年龄分组
FROM用户明细;

将编写好的SQL语句直接复制至一个新建的查询SQL视图窗口中，单击【运行】按钮，得到的结果与IIF函数分组结果一样，如图1-38所示。

小白：果然，SWITCH函数分组与IIF函数分组效果一样，但SWITCH函数会让人感觉分组更清晰些。

（4）PARTITION函数法

Mr.林：第四个用于数值分组的函数是PARTITION。PARTITION函数语法如下：

PARTITION (数值参数，开始值，结束值，组距)

说明：

① 数值参数为要根据范围进行计算的整数。

② 开始值必须为整数，并且不能小于0。

③ 结束值也必须为整数，该数值不能等于或小于开始值。

④ 组距也必须为整数，指定在整个数值范围内（在开始值与结束值之间）的分区大小。

⑤ PARtltlON返回的内容为每组的“下限：上限”。

我们仍以“用户明细”表为例，对用户年龄进行分组。下面就采用PARTITION函数进行分组，开始值设置为1，结束值设置为100，组距设置为20，编写的SQL语句如下：

SELECT用户ID,年龄,
PARTITION(年龄,1,100,20) AS年龄分组
FROM用户明细;

将编写好的SQL语句直接复制至一个新建的查询SQL视图窗口中，单击【运行】按钮，得到的结果如图1-40所示。

图1-40 PARtltlON函数分组结果示例

小白：哇，用PARTITION函数分组更加简单、清晰、明了。

（5）四个分组函数的比较

小白：Mr.林，还有个问题：这四个数值分组函数，分别在什么情况下使用呢？

Mr.林：好的。我就来总结下这四个数值分组函数的优缺点，如图1-41所示，这样你可以根据实际情况选择相应的函数进行数据分组。

图1-41 四个分组函数的优缺点比较

如果要进行数值等距分组，可考虑使用PARTITION或CHOOSE函数；如果要进行数值不等距分组，可考虑使用SWITCH或IIF函数。

小白：经过您这么归纳、总结和对比，四个数值分组函数的优缺点很清晰直观，我知道该如何选择使用了。

日期/时间分组

Mr.林：介绍完数值分组，接下来学习对日期/时间的分组，这一类分组也是我们数据处理与分析工作中常用到的。

在Access数据库中，除了可以采用YEAR、MONTH、DAY等常用日期函数（如图1-37所示）进行日期分组外，我们还可以采用FORMAT函数进行日期/时间分组。FORMAT函数可对文本、数值、日期/时间等类型数据按指定要求进行格式化，这里我们主要介绍FORMAT函数的日期/时间格式化功能。FORMAT函数的语法如下：

FORMAT（日期/时间，日期/时间格式参数）

FORMAT函数中日期/时间相关的格式参数说明如图1-42所示。

图1-42 fORMAt函数日期/时间参数说明表

我们以“订购明细”表为例，对用户订购日期依次按年、季、月、日、星期、小时、分、秒等8个日期/时间单位进行格式化分组。下面就采用FORMAT函数进行分组，编写的SQL语句如下：

SELECT订单编号,订购日期,
FORMAT(订购日期,"yyyy") AS年,
FORMAT(订购日期,"q") AS季,
FORMAT(订购日期,"m") AS月,
FORMAT(订购日期,"d") AS日,
FORMAT(订购日期,"dddd") AS星期,
FORMAT(订购日期,"h") AS小时,
FORMAT(订购日期,"n") AS分,
FORMAT(订购日期,"s") AS秒
FROM订购明细;

将编写好的SQL语句直接复制至一个新建的查询SQL视图窗口中，单击【运行】按钮，得到的结果如图1-43所示。

图1-43 fORMAt函数日期/时间分组结果示例

小白：FORMAT函数真方便，我想要什么样的日期/时间分组，就有什么样的日期/时间分组。

1.1.7 重复数据巧处理

Mr.林：小白，还记得在Excel中处理重复数据的几种方式吗？

小白：当然记得，有函数、条件格式标识、高级筛选、数据透视表等方法。

Mr.林：没错，接下来学习Access数据库中处理重复数据的方法，主要包含重复项查询、不重复项查询以及数据去重查询，同样也可以通过菜单操作、SQL语句两种方式完成。

重复项查询

（1）菜单操作法

Mr.林：小白，之前学习新建简单查询时，对话框里面就有一项“查找重复项查询向导”功能（如图1-20所示），下面就要用它来查找数据重复项。我们以查找“订购明细”表中重复的“用户ID”为例，具体查询操作如下。

STEP 01 单击【创建】选项卡，在【查询】组中单击【查询向导】按钮。

STEP 02 在弹出的【新建查询】对话框中，选择【查找重复项查询向导】，如图1-20所示，单击【确定】按钮。

STEP 03 在弹出的【查找重复项查询向导】第一个对话框中，选择【表】视图，并在列表框中选择“订购明细”表作为查询对象，单击【下一步】按钮，如图1-44所示。

图1-44 【查找重复项查询向导】对话框1

STEP 04 在【查找重复项查询向导】第二个对话框【可用字段】列表框中，选择“用户ID”作为要进行查找重复项查询的字段，单击【完成】按钮，如图1-45所示。

图1-45 【查找重复项查询向导】对话框2

Mr.林：查找“订购明细”表中“用户ID”重复的结果，如图1-46所示。从重复项查询结果中可获知两个信息：重复订购的用户ID，每个重复订购用户所重复订购的次数。

图1-46 重复项查询结果示例

小白惊讶地说道：哇！比Excel方便很多！如果用Excel数据透视表进行重复用户统计，还需要把统计结果复制出来，再筛选出订购次数大于或等于2次的结果。

Mr.林：没错，Access数据库还能处理比Excel大得多的数据，而且速度一点也不慢。

（2）SQL查询法

小白：Mr.林，快单击Access数据库窗口右下方的按钮，我想看看这个查找重复项算法是怎样的，SQL语句是怎样写的。

Mr.林：好的。单击Access数据库窗口右下方的按钮，其简化的SQL语句如下：

SELECT First(用户ID),Count(用户ID) AS NumberOfDups
FROM订购明细
GROUP BY用户ID
HAVING Count(用户ID)>1;

将编写好的SQL语句直接复制至一个新建的查询SQL视图窗口中，单击【运行】按钮，同样可以得到如图1-46所示的结果。

小白：咦！多了几条陌生的语句，First、Count、GROUP BY、HAVING分别代表什么意思？Count，我知道，在Excel中是计数的意思，它在Access数据库中也是计数的意思吧！

Mr.林：没错，Count就是计数的意思。

First，很简单，就是第一，在这里的意思就是第一条记录。有第一就有最后，其函数为Last。如果有用户重复订购，那么我们也可不使用First函数，直接用字段表示即可，“用户ID”取哪个值都是一样的。

GROUP BY子句就是实现对数据按指定的分组字段进行分组，本例中按用户进行分组，这与数据透视表分组统计的道理是一致的。

HAVING子句在SELECT语句中与GROUP BY子句联合使用，用于表示GROUP BY子句输出结果的条件，其作用相当于WHERE子句之于SELECT语句。即WHERE子句设定被选择字段的条件，而HAVING子句设置GROUP BY子句形成的分组条件。

另外它们都需要使用关系比较运算符：“=”、“<”、“>”、“<=”、“>=”或“<>”。

在本例中，“HAVING Count(用户ID)>1”的意思就是对用户ID出现2次及以上的数据进行分组。

小白：明白了。

不重复项查询

Mr.林：找出了重复项，那么不重复项如何找呢，小白？

小白：我想想……有了，既然重复项是用户订购次数大于或等于2次的结果，那么不重复项不就是用户订购次数等于1的结果么？也就是说，我们只需在刚才查找重复项的SQL语句中，把“HAVING Count(用户ID)>1”更改为“HAVING Count(用户ID)=1”即可，是这样的么，Mr.林？

Mr.林：真聪明！非常正确，加10分，查找不重复项的SQL语句如下：

SELECT用户ID,Count(用户ID) AS NumberOfDups
FROM订购明细
GROUP BY用户ID
HAVING Count(用户ID)=1;

将编写好的SQL语句直接复制至一个新建的查询SQL视图窗口中，单击【运行】按钮，同样可以得到如图1-47所示的结果。

图1-47 不重复项查询结果示例

小白：学会SQL语句确实很方便，数据处理起来杠杠的，一条SQL语句就搞定了。

数据去重查询

Mr.林：小白，如果我要进行数据去重——就是找出所有购买行为的“用户ID”，但只保留其中一条，在Excel 2007-2010版本有“删除重复项”功能，那么在Access数据库中如何处理呢？

（1）GROUP BY子句

小白：非常简单啊！刚才对重复项和不重复项的查找都是在HAVING子句设置GROUP BY子句形成的分组条件下进行的，如果要去重，就不需要设置条件，显示出所有唯一的“用户ID”，直接用GROUP BY“用户ID”字段即可，这个同样与数据透视表分组统计的道理是一致的。

Mr.林：非常正确，再加10分，数据去重的SQL语句如下：

SELECT用户ID
FROM订购明细
GROUP BY用户ID;

将编写好的SQL语句直接复制至一个新建的查询SQL视图窗口中，单击【运行】按钮，可以得到如图1-48所示的结果。

图1-48 数据去重查询结果示例

（2）DISTINCT

Mr.林：在Access数据库中，我们还可以使用DISTINCT关键字，它的作用就是忽略所选字段中包含重复数据的记录，简单来说，就是数据去重。对于刚才的例子：找出所有有购买行为的“用户ID”，但只保留其中一条，其SQL语句如下：

SELECT DISTINCT用户ID
FROM订购明细;

将编写好的SQL语句直接复制至一个新建的查询SQL视图窗口中，单击【运行】按钮，同样可以得到如图1-48所示的结果。

需要注意：如果SELECT子句中包含多个字段，则对于结果中包含的特定记录，其所有字段的值组合必须是唯一的。

小白：好的。

1.1.8 数据分析一步到位

Mr.林：前面我们学习了Access数据库中的数据合并、数据计算、数据分组、重复数据处理四大常用的数据处理方式。

而数据处理的目的就是将采集到的数据，用适当的处理方法整理加工，形成适合数据分析要求的样式，也就是一维表，为数据分析做好准备工作。

小白：我知道您的意思了，接下来是要进行数据分析了吧？

Mr.林：没错。我们日常工作中所做的数据分析，主要指通过对比与细分进行现状分析及原因分析，通过数据分组了解其数据构成，甚至通过不同时间维度的对比，查找数据变化的原因，最后通过制作相关图表等对现状进行呈现及描述。

接下来学习在Access数据库中进行数据分析的方法，主要包含简单统计、分组统计、交叉表统计三种方法，我主要介绍SQL语句实现方式。

小白：Mr.林，打断一下，我发现Access数据库中有数据透视表功能，为什么不用数据透视表进行数据分析呢？

Mr.林：这个问题问得好。不用的原因是因为在Access数据库中使用数据透视表时，拖动一个字段，Access数据库就会计算一次，数据越多，其计算速度就越慢，也不知何时结束计算。如果每拖动一次字段就这样计算一次，你会疯掉的！你可以事后自行测试一下。

而用SQL语句，你只需写一次，直接单击【运行】按钮即可。并且有运行进度条显示运行进度，大大提高数据分析效率，所以我推荐用SQL语句进行数据分析。

小白：明白，您继续。

简单统计

Mr.林：在重复数据处理时我们已经用到一个计数函数Count，这是最常用的统计函数之一。Access数据库中常见的统计函数如图1-49所示。

图1-49 Access数据库常用的统计函数

我们以“订购明细”表为例，统计“订单总数”、“订购金额总额”、“平均订单金额”三个数据，SQL语句如下：

SELECT
Count(订单编号) AS订单总数,
Sum(订购金额) AS订购金额总额,
Avg(订购金额) AS平均订单金额
FROM订购明细;

将编写好的SQL语句直接复制至一个新建的查询SQL视图窗口中，单击【运行】按钮，可以得到如图1-50所示的结果。

图1-50 简单统计结果示例

分组统计

Mr.林：小白，在学习重复数据处理时我们还用过一个GROUP BY子句，实现对数据按指定的分组字段进行分组功能。

小白：对，您说这个与数据透视表分组统计的道理是一致的。

Mr.林：没错。我们同样以“订购明细”表为例，统计各个产品的“订单总数”、“订购金额总额”、“平均订单金额”的数据，SQL语句如下：

SELECT产品,
Count(订单编号) AS订单总数,
Sum(订购金额) AS订购金额总额,
Avg(订购金额) AS平均订单金额
FROM订购明细
GROUP BY产品;

将编写好的SQL语句直接复制至一个新建的查询SQL视图窗口中，单击【运行】按钮，可以得到如图1-51所示的结果。

图1-51 产品分组统计结果示例

Mr.林：小白，你发现这个分组统计与刚才的简单统计SQL语句有什么区别吗？

小白：发现了，就是在最后加上“GROUP BY产品”，并且在SELECT中增加“产品”字段。

Mr.林：是的，分组统计就是这么简单。再来看个复杂一点的案例。还是以“订购明细”表为例，我们需要了解用户订购时段分布信息。原有“订购明细”表只有“订购日期”字段，需要取出时段信息，这时可采用FORMAT函数，SQL语句如下：

SELECT FORMAT(订购日期,"h") AS时段,
Count(订单编号) AS订单数
FROM订购明细
GROUP BY FORMAT(订购日期,"h");

将编写好的SQL语句直接复制至一个新建的查询SQL视图窗口中，单击【运行】按钮，可以得到如图1-52所示的结果。我们只需复制出统计结果，保存至Excel中，调整一下时段顺序，即可绘制用户订购时段分布图。

图1-52 时段分组统计结果示例

小白：哦，只要在原来的基础上，把分组表达式当作一个字段，放在GROUP BY后面，同时在SELECT中增加分组表达式，并重新命名。

Mr.林：是的，我们再来看个更复杂一点的案例。还是以“订购明细”表为例，我们需要了解不同年龄段的用户订购分布情况，原有“订购明细”表中没有用户年龄信息，并且订购用户存在重复情况，需要去重。

小白：确实又复杂了一些，怎么做呢？

Mr.林：先将“订购明细”表与“用户明细”表按关键字段“用户ID”进行关联查询，并且可同时进行去重处理，采用PARTITION函数法对用户年龄分组，然后把查询结果作为子查询嵌套在分组统计查询中。SQL语句如下：

SELECT年龄分组,Count(用户ID) AS用户数
FROM
(SELECT DISTINCT A.用户ID, PARTITION(B.年龄,1,100,5) AS年龄分组
FROM订购明细A, 用户明细B
WHERE A.用户ID = B.用户ID)
GROUP BY年龄分组;

将编写好的SQL语句直接复制至一个新建的查询SQL视图窗口中，单击【运行】按钮，可以得到如图1-53所示的结果。

图1-53 年龄分组统计结果示例

Mr.林：这里需要说明的一点是：在Access数据库中进行数据去重处理时，需要使用嵌套查询，把数据去重结果作为子查询。如果本例需要了解的是各省的订单数分布，无须先进行数据去重处理，那么就无须使用嵌套查询，SQL语句如下：

SELECT B.省份, Count(A.订单编号) AS订单数
FROM订购明细A, 用户明细B
WHERE A.用户ID = B.用户ID
GROUP BY B.省份;

小白：明白！

交叉表统计

（1）菜单操作法

Mr.林：最后介绍交叉表统计，这个需要借助“简单查询”功能来实现。我们以“用户明细”表为例，统计不同省份、性别的用户分布情况，具体查询操作步骤如下。

STEP 01 单击【创建】选项卡，在【查询】组中单击【查询向导】按钮。

STEP 02 在弹出的【新建查询】对话框中，选择【交叉表查询向导】，如图1-20所示，单击【确定】按钮。

STEP 03 在弹出的【交叉表查询向导】第一个对话框中，选择【表】视图，并在列表框中选择“用户明细”表作为查询对象，单击【下一步】按钮，如图1-54所示。

图1-54 【交叉表查询向导】对话框1

STEP 04 在弹出的【交叉表查询向导】第二个对话框【可用字段】列表框中，选择“省份”作为行标题，单击【下一步】按钮，如图1-55所示。

图1-55 【交叉表查询向导】对话框2

STEP 05 在弹出的【交叉表查询向导】第三个对话框字段列表框中，选择“性别”作为列标题，单击【下一步】按钮，如图1-56所示。

图1-56 【交叉表查询向导】对话框3

STEP 06 在弹出的【交叉表查询向导】第四个对话框【字段】列表框中，选择“用户ID”作为每个行和列的交叉点统计项，并在【函数】列表框中，选择“Count”函数，并保持默认勾选的【是，包括各行小计】项，单击【下一步】按钮，如图1-57所示。

图1-57 【交叉表查询向导】对话框4

STEP 07 在弹出的【交叉表查询向导】第五个对话框中，输入该查询的名称，单击【完成】按钮，结果如图1-58所示。

图1-58 交叉表查询结果

（2）SQL语句法

小白：Mr.林，单击Access数据库窗口右下方的按钮，让我瞧瞧SQL语句是怎样写的。

Mr.林：好的。我们单击Access数据库窗口右下方的按钮，其简化的SQL语句如下：

TRANSFORM Count(用户ID) AS用户ID计数
SELECT省份,Count(用户ID) AS总计用户ID
FROM用户明细
GROUP BY省份
PIVOT性别;

Mr.林：小白，你看出交叉表的SQL语句与我们刚才学的分组统计SQL语句的区别没有？

小白：交叉表的SQL语句在分组统计SQL语句的基础上，前后增加了TRANSFORM与PIVOT语句。

Mr.林：没错，只要在分组统计SQL语句的基础上，前后增加TRANSFORM与PIVOT语句，并且在TRANSFORM后面增加每个行和列的交叉点统计函数及字段，在PIVOT后面增加要作为列标题的分组字段即可。

其运行结果与刚才的菜单操作法结果一样，我就不再重复运行了。小白，用Access数据库进行数据处理与分析的内容就先介绍到这儿，现在你能真正体会用SQL语句处理与分析数据的强大与实用了吧！除了掌握基本原理，还要做到结合实际情况，融会贯通。

小白：确实不是一般的强大与实用。