Microsoft Query:基本操作
Microsoft Query是用于将数据从外部数据源检索到其他Microsoft Office应用(特别是Microsoft Excel)中的一种程序。通过使用Microsoft Query可以从企业的数据库和文件中检索数据,而不必在Excel中重新输入需要分析的数据。也可以在每次更新数据库时,自动通过源数据库中的数据来更新Excel报表和汇总数据。
一、Microsoft Query的数据源类型
Microsoft Query可以从多种类型的数据源中检索数据,常见的数据源类型包括:
- Microsoft Office Access
- Microsoft SQL Server
- Microsoft FoxPro
- Oracle
- Microsoft Office Excel
- 文本文件
在Microsoft Query中,可以使用“查询向导”创建简单的查询。我们知道在Microsoft Query或Access数据库中,查询是一种查找记录的方法,而这些记录回到了用户对数据库存储的数据提出的特定问题。
二、Microsoft Query与Access的异同
Microsoft Query与Access数据库的区别:
- 两者的数据处理与分析功能类似,都可以采用SQL语句进行查询处理,查询操作界面都很类似。
- 不同之处在于Microsoft Query的性能要稍弱于Access,以及Microsoft Query数据处理的灵活性及速度都不急Access数据库。
以下几种情况可采用Microsoft Query进行处理分析:
- 计算机没有安装Access或其他数据库软件。
- 数据处理量相对较小,数据量在Excel可容纳的范围内。
- 无须进行数据处理,或者数据处理操作较为简单。
- 初学者学习SQL语句。
补充一点:如果数据量超过Excel容纳范围(如Excel2007-2010版本最大记录数为1048576),不过只需得到统计分析结果,无须获取处理后的明细数据,也是可以使用Microsoft Query进行数据处理与分析的。在计算机配置允许的情况下,Microsoft Query工具也可以处理千万级别的数据,只是性能会变差,耗时较长。
三、使用Microsoft Query的步骤
使用Microsoft Query进行数据处理与分析的步骤如下:
- 建立数据源以链接数据;
- 使用“查询向导”选择所需的数据;
- 在查询操作界面进行相应的SQL查询操作;
- 将数据结果返回到Excel中。
四、Microsoft Query使用
1. 数据导入
1)建立数据源连接数据
我们仍然以“订购明细”、“用户明细”文本数据为例,我们需要了解不同年龄段用户的产品购买偏好,要用到“订购明细”中的用户ID、产品,以及“用户明细”表的“年龄”字段,首先就是建立数据源以连接数据。
操作步骤如下:
- 在Excel【数据】选项卡上的【获取外部数据】组中,打击【自其它来源】,然后单击【来自Microsoft Query】;
- 在弹出的【选择数据源】对话框中,在【数据库】选项卡下,双击【<新数据源>】 ;
- 弹出【创建新数据源】对话框中:
- 第一项输入框中输入自定义的数据源名称,以标识好额区分数据源;
- 第二项下拉框中,单击选择作为数据源的数据库类型的驱动程序,本例选择“Microsoft Text Driver(*.txt;*.csv)";
- 在第三项设置数据源所在文件夹路径;
- 在第四项可选择其中一个文本文件作为数据源选定默认表。
2)使用“查询向导”选择数据
建立好数据源,接下来使用“查询向导”选择我们所需要的数据。
- 返回【选择数据源】对话框,确认对话框中下方的【使用查询向导创建/编辑查询】项被勾选上,双击数据库选项卡下的刚建好的名为“数据”的数据源。
- 在弹出的【查询向导-选择列】对话框中,将所需要的“订购明细”表的用户ID、产品、数量字段,以及用户明细表的年龄字段,分别从左边【可用的表和列】中选择并移至右边的【查询结果中的列】。
- 在弹出的警告提示框在红,单击【确定】按钮。
- Microsoft Query查询操作界面如下所示:
观察后发现,查询操作显示的界面有以下问题:
- 订购明细与用户明细两个表之间没有建立关系;
- 用户ID与年龄没有对应,同一个用户有不同的年龄信息;
- 在界面最下方有提示“将字段从某一表拖拽到临沂表的相关字段。
这一切都是源于没有对“订购明细”与“用户明细”两个表建立关系,接下来要建立两表之间的关系:将“用户明细”表中的“用户ID”字段拖拽到“订购明细”表的“用户ID”字段,得到如下图的结果:
Microsoft Query工具中,用拖动字段的方式建立各表之间的连接关系默认是内连接(INNER JOIN),即选择两个表中关键字段相匹配的记录。
在Microsoft Query中,单击菜单中的【表】,选择【连接】项,如下图所示,从上到下依次对应内连接(INNER JOIN)、左连接(LEFT JOIN)、右连接(RIGHT JOIN)三种关系,这与Access数据库中的链接关系是一致的。
2. 数据处理
在菜单栏中有个SQL按钮,单击打开后呈现出了相应的SQL语句,如下所示:
现在我们只需要修改SQL语句,采用PARTITION分组函数,增加年龄分组相应的语句,即可实现年龄分组。
SELECT 订购明细.用户ID, 订购明细.产品, 订购明细.数量, PARTITION(用户明细.年龄,1,100,5) AS 年龄分组
FROM 订购明细.txt 订购明细, 用户明细.txt 用户明细
WHERE 用户明细.用户ID = 订购明细.用户ID
运行之后的结果如下:
数据合并、数据计算、数据分组、数据去重等其他的SQL语句数据处理方式,均与Access数据库中的基本一致。
3. 数据分析
1)SQL语句分析法
上一篇: JITWatch查看反汇编的机器码
下一篇: MySql查询语句实践