丹尼尔·瓦伦特，博士，JMP高级产品经理，SAS
乔恩·魏斯 ，JMP 销售与营销副总裁，SAS
抽象的
JMP 13 引入了两个新工具，使分析师能够轻松处理常见的数据问题。通常，两个或多个数据表通过一个公共键相关联，但它们在事实采样频率或维度（例如，长与宽）方面有很大差异。对这两个（或更多）表进行实际连接可能会创建一个在内存中变得非常大的表。虽然当事实表和维度表相对紧凑时这很麻烦，但它可以快速生成一个太大而无法放入内存的连接表。解决方案是使用虚拟连接，它可以保留连接关系而不实际创建连接表 - 有效地丰富事实表而不增加其负担。分析师经常发现自己需要在数据库中进行复杂的表连接或汇总并编写 SQL，这会带来一系列麻烦，尤其是当存在缓慢变化的维度等复杂关系时。JMP Query Builder 是一种简单的 SQL 生成和编写工具，其代码可以在数据库中执行。在您拖放正确且理想的查询后，查询生成器会自动为您编写 SQL 代码，从而无需进行繁琐的手动且容易出错的工作。
关系表结构概述
分析师需要数据的形式，即列中包含变量，行中包含观察值。数据可能还需要分类列，以便明确对观察值进行分组（例如，收入水平、国家、机器等）。数据还可能具有进一步阐明观察值的属性（例如，地址、序列号、客户 ID 等）。
此类分析的源数据通常是存储在数据库中的关系数据结构。许多现代数据科学课程涵盖与关系数据结构、数据库和 SQL 相关的主题。但大多数工程师、科学家和统计学家在学术环境中并未接触过这些主题，因此他们必须在持续的非正式教育中学习这些主题。JMP Query Builder 为分析师在关系数据结构和可分析数据之间架起了一座桥梁。通过学习一些术语和概念，分析师可以使用 Query Builder 生成可供分析的数据。
本文对关系数据结构的简要讨论并非旨在取代对该主题的深入研究；而是介绍使用查询生成器及其 JMP 中的虚拟连接功能时所使用的术语和定义。
为什么要有多个表来存储重要数据？
假设您需要设计一种方法来存储有关客户购买贵公司产品的数据。您可能首先列出可能需要收集的所有变量，例如：
- 购买日期
- 购买金额
- 购买方式（PO 或 CC）
- 产品 SKU
- 产品描述
- 客户名称
- 客户地址
- 客户电话号码
这当然不是一个完整的列表，但它是一个好的开始。现在考虑维护这些数据。此表将有八列和每笔购买一行。某些字段可能有许多不同的值，例如购买金额或购买日期。其他字段将具有更少的不同值和许多重复值，例如产品 SKU 和说明。
减小表格大小的常用方法是将表格分成三个逻辑组。第一个表包含交易或事实，如购买日期和购买金额。第二个表包含客户信息，第三个表包含产品信息。为了维护数据列之间的链接，必须添加一个包含链接信息的列来关联这三个表。这些列称为键。显示此布局的常用方法是使用实体关系图 (ER) 。每个实体都有一个表名、一个或一组唯一标识每一行的键，以及一组提供有关每一行信息的属性或变量。客户购买日期的 ER 图如图 1 所示。
图 1. 客户购买数据的实体关系图
客户维度表中客户 ID 旁边的钥匙符号表示，如果您知道客户 ID，则可以在表中唯一地找到该客户及其所有相关信息。客户 ID 也位于购买事实表中。当客户 ID 位于购买事实表中时，它被称为外键，因为它可能出现多次，并且唯一地将外部表中的行定义为它出现的行。
通过图 1 中的布局，很容易看出如何更新产品描述。为此，您只需编辑“产品维度”表，并且该更改将应用于所有购买，这一好处称为可维护性。如果这是一张包含多次重复的产品描述的表，则您必须编辑许多行，这将不可避免地导致错误和数据质量问题。
案例一：当前人口调查
现在让我们使用查询生成器查看一个示例。美国政府通过当前人口调查 (CPS)[USGCPS] 收集了大量有趣的数据。CPS 包括：
“当前人口调查 (CPS) 是美国历史最悠久、规模最大、最知名的调查之一。它非常重要，提供有关定义我们作为个人和社会的许多信息 - 我们的工作、我们的收入和我们的教育。除了作为每月劳动力统计数据的主要来源之外，CPS 还用于收集各种其他研究的数据，这些研究让国家了解其人民的经济和社会福祉。这是通过在每月的基本 CPS 问题中添加一组补充问题来实现的。补充查询每月都不同，涵盖各种主题，例如子女抚养费、志愿服务、健康保险覆盖范围和入学率。补充通常每年或每两年进行一次，但补充的频率和重复性完全取决于最能满足补充赞助商需求的内容。”
这些数据可以下载为 36 个文本文件，由于这些文件是制表符分隔的文本文件，因此可以轻松地在 JMP 中打开。查看 JMP 表，创建了一个 ER 图（图 2）以告知查询生成器中的工作。
图 2. CPS 劳动力统计数据表的 ER 图
要使用查询生成器，您需要定义主表（事实）和次表（维度）。
CPS 数据的事实表称为所有数据.jmp。该表有 500 多万行，但只有五列。这些列有“series_id”、日期和“value”列。要理解数据，您需要找出系列编号.jmp 意思是。图 2 中的 ER 图显示“series_id”与系列.jmp表。接下来，在 JMP 中打开这两个表并使用查询生成器定义连接。
在 JMP 中，选择所有数据.jmp作为主（事实）表和系列.jmp作为次要（维度）表（图 3）。JMP 将自动查找适合的键并设置连接，但您应该通过单击系列表右侧的维恩图来验证它。查询生成器在两个表中都找到了“footnote_codes”，但您不想将它们作为键连接，而只想使用“series_id”，因此应该删除该键关系。
图 3. 查询生成器用于连接所有数据.jmp和系列.jmp
现在您需要定义连接表中的列（图 4）。为此，选择所有数据.jmp并从该表中选择所需的列。现在删除“series_id”，因为在结果表中不需要该键（系列和数据.jmp）。
图 4. 查询生成器连接系列和数据.jmp对所有人来说“_代码.jmp” 表
请注意系列和数据.jmp大约是所有数据.jmp表。这种膨胀是由于所有“_code”字段的值重复造成的。因此，显然将数据存储在 36 个表中可以大大减少存储需求。接下来，选择系列和数据.jmp 作为主表，所有“_code.jmp”表作为辅助表（图 5）。
图 5. 查询生成器将所有“_代码.jmp”
仅使用“_code”表中的“_text”字段构建查询，因为您不再需要关键变量。使用 JMP 中的 Cols > Columns Viewer，您可以轻松获取表中现在 42 列的摘要完整 join.jmp。你仍然会有 570 万行，这与所有数据.jmp，但现在您有了描述数据的文本字段。即使表格完整加入.jmp仍然比桌子大六倍AllData.jmp，您现在有了一个可用于分析的表格。
使用图形生成器绘制制造业失业趋势完整加入.jmp。图 6 中，红色趋势线表示经济衰退期间该行业的混乱。2016 年的失业率回到了 2006-2007 年的水平。绿色趋势线表示汽车制造业的就业水平。2010 年至 2015 年，汽车制造业新增了 30 多万个就业岗位。
图 6. 图形生成器显示 CPS 劳动力统计数据
案例2：餐厅检查数据
本案例探讨了如何使用虚拟连接和 JMP 查询生成器来分析 DOHMH 纽约市餐厅检查结果[多哈姆]。想象一下，你是一名新上任的检查员，正在探索一些历史数据，看看这些数据能揭示什么。来自网站https://data.cityofnewyork.us/Health/DOHMH-New-York-City-Restaurant-Inspection-Results/xx67-kt59，您可以下载这些数据的 .csv 版本，该版本可在 JMP 中轻松打开。2016 年 7 月 17 日的完整下载生成一个 163.3 MB 的文件，其中包含 448,149 条记录。
这些数据有一个 INSPECTION DATE 列，即检查事件的 M/D/Y。要查看这些数据中包含多少年，请右键单击并使用新公式列 > 日期时间 > 年份。将其从连续转换为名义，然后分析 > 分布如图 7 所示。级别 1900 可能是缺失数据的代码，您需要解决这个问题。破坏性地编辑这些数据不是问题（因为 JMP 在这里用作沙箱，而真实数据始终存储在服务器上）；接下来使用 Recode 实用程序将这些级别更改为缺失。
图 7. 完整数据集的检验日期
在重新编码窗口中，有 1,151 条记录为 01/01/1900。删除字符串，然后单击“完成”->“就位”，将此旧值更改为 •。由于记录日期是提取数据的日期，与此分析无关，因此删除该列。
本文是关于虚拟连接和查询生成器的，因此您可能想知道我们为什么要查看非规范化表。似乎有机会对这些数据进行规范化。值得注意的是，这些原始数据中缺少各个餐厅的地理编码位置，这项任务可以稍后使用 JMP 插件执行。由于此过程的计算成本很高，因此只对唯一位置进行地理编码是有意义的，这些数据中可能存在多次检查。此外，似乎有机会创建一个额外的违规表，违规数量远少于唯一检查。
在拆分数据之前，先对这些数据进行抽样，以便您的探索练习能够更轻松地管理这些数字。
使用查询生成器可以轻松执行抽样。表格 > JMP 查询生成器将调出启动对话框。由于您在本例中没有执行连接，因此请使用DOHMH_New_York_City_Restaurant_Inspection_Results.jmp作为主表，此时没有次表。在查询生成器中，有一个名为“示例”的选项卡。选中“对此结果集进行抽样”复选框，您可以选择前N行或随机N行。对这些数据进行随机 10% 的抽样，因此在随机N行中输入 44,815。将所有列添加到“包含列”部分，然后检查 SQL。查询生成器已自动生成执行此查询的 SQL：
选择 t1.CAMIS、t1.DBA、t1.BORO、t1.BUILDING、
t1.街道，t1.邮政编码，t1.电话，t1。“菜肴描述”，
t1。“检查日期”, t1。“行动”, t1。“违规代码”, t1。“违规描述”,
t1。“严重标记”, t1.分数, t1.成绩, t1。“成绩日期”,
t1."检验类型", t1."年份[检验日期]"
来自 DOHMH_纽约市餐厅检查结果 t1
按随机排序（）限制为 44815；
使用后随机排序（） 函数进行抽样，此查询将生成适当的数据子集。将其保存为子集，然后分离餐厅人口统计和违规代码。这里也可以使用查询生成器。从违规代码开始。同样，您只需要一个主表。将 VIOLATION CODE 和 VIOLATION Description 拖放到变量列表中，然后再次拖入 VIOLATION CODE，这样您就可以按计数聚合并生成具有所需属性的表。将聚合列的 JMP 名称更改为 VIOLATION COUNT。这种为列或派生列生成别名的能力是查询生成器的一个强大功能。您还可以选择按新创建的聚合列自动排序，方法是将 VIOLATION COUNT 拖到 Order By 角色，如图 8 所示。
图 8. 聚合列重命名为 Violation Count，并带有 Order By 属性
由于只有 88 个唯一违规代码，因此生成的查询更加紧凑。将此表命名为D-违规代码.jmp后，这是使用 Graph Builder 探索哪些代码具有最多记录的好时机。将 VIOLATION STUDIO 拖到 X 轴，将 VIOLATION COUNT 拖到 Y 轴。然后右键单击 VIOLATION STUDIO 并按 VIOLATION COUNT 排序（降序）。要仅关注排名靠前的类别，您可以使用 VIOLATION COUNT 上的本地数据过滤器，其中包含有超过 2,500 条唯一记录的违规类别（图 9）。
图 9. 违规次数最多（采样表中有 >2500 条唯一记录）
现在，您可以暂时不看违规表，而是专注于独特的餐厅人口统计数据。此时，您还可以删除违规描述，这样可以节省主事实表中的大量重复行。使用违规代码，这是一种更紧凑的方式来组织这些数据（三个字母的字符串与句子长度的字符串）。
再次使用查询生成器提取独特的餐厅人口统计数据。纽约市很可能有不止一家同名的披萨店，但查询生成器可以处理这种情况。CAMIS 列用作 ID，可用于识别独特的餐厅。在查询生成器中，将 CAMIS 拖入两次，其中一次应按计数聚合并重命名为 INSPECTION COUNT。其余人口统计数据为 DBA、BORO、BUILDING、STREET、ZIPCODE、PHONE 和 CUISINE Description。最后，为地理编码器计算一个名为 ADDRESS 的列，这将作为后查询脚本完成。运行查询，然后使用具有以下公式的公式列：
：建築||“ “|| :街道 ||“ “|| :博罗 ||“纽约，”||炭（ ：邮政编码 ）
此公式将正确的地址组合在一起。输入:地址<<获取脚本; 在脚本编辑器中将生成生成后查询脚本列所需的 JSL。将该代码复制并粘贴到查询生成器中的相应选项卡中，然后生成一个列，该列将数据分成 20 个组，以便更好地与地理编码交互。生成名为“分组和初始化数据：序列数据，从 1 到 20，步骤 1，重复 1”的新列后，您可以获取脚本并将其粘贴到后查询脚本中。现在两个列都是脚本，您可以删除查询表并重新运行查询。将此数据表另存为D-餐厅.jmp. 最后一步，从当前事实表中删除多余的列，包括：DBA、BORO、BUILDING、STREET、ZIPCODE、PHONE 和 CUISINE Description。
将此表另存为 F-检查.jmp. 现在，您可以使用 Xan Gregg 创建的插件对餐厅组进行地理编码，该插件可在 JMP 文件交换 [GREGG] 上使用，该插件使用 OpenStreetMaps.org 对位置进行地理编码。此服务计算成本高昂，容易失败，因此最好将其拆分D-餐厅 使用表格 > 子集按组进行分组。此平台的子集功能（如图 10 所示）在这里很有用。
图 10. 按餐厅组划分的子集表平台
可以使用插件对新创建的 20 个表进行单独地理编码。地址字段有助于确定正确的纬度和经度。最后，将结果保存到同一张数据表中。
要将数据表恢复为包含位置的单一维度表，请使用表格 > 连接功能，将 20 个组组合在一起；它被命名为 D-RestaurantswLocation.jmp。
现在您已经使用查询生成器分解了该表，您可以使用虚拟连接重新构建它。
首先，从我们的两个维度表开始： 餐厅-wLocation.jmp 和违规代码.jmp. 右键单击 VIOLATION CODE 并选择 Link ID 将设置虚拟连接的开始。请注意，列表中会出现一个小钥匙图标。移至 Restaurants 表并对 CAMIS 执行相同操作。要完成虚拟连接，请移至包含所有检查的事实表，然后选择 CAMIS 和 VIOLATION CODE 的 Link Reference。
所有列都会显示在事实表中，而无需在内存中物理连接表。虚拟连接也可以通过列属性编写脚本：
链接参考：
设置属性（“链接引用”，参考表（“D(f)-Restaurants-wLocation.jmp”））；
对于链接 ID：
设置属性（“链接ID”，1）；
图 11. 虚拟连接
图 11 显示您可以访问两个维度表中的列，而不会增加事实表的负担。
最后，构建一个仪表板，利用虚拟连接的列来可视化和探索这些数据。
图 12 显示了 Dashboard Builder 如何显示五张图表的集合。要使用其中两张图表作为选择过滤器，请从 Dashboard Builder 中的分层过滤器模板开始，使用 CRITICAL FLAG 和 BORO 作为过滤器和分层过滤器。接下来，使用等级计数图表、地图上按分位数显示的平均分数，最后使用按检查类型显示日期计数的热图。
图 12. Application Builder 的仪表板构建器模式与最终纽约市检查仪表板
这只是利用这些数据进行可视化的一个例子，利用虚拟连接提供的链接表。地理编码数据还允许您按菜系挖掘餐馆的位置（图 13）。
图 13. 街道级地图上按等级显示地理编码的餐馆
SQL 生成和部署
使用查询生成器的另一个主要优势是生成结构化查询语言 (SQL)。SAS 可以在 Proc SQL 中使用 SQL，大多数数据库都使用 SQL 作为编程语言来连接和管理表。可以从查询生成器的点击界面中定义的查询生成 SQL。
下面是查询生成器创建的将所有维度表连接到连接表的 SQL系列.jmp和所有数据.jmp（来自案例 1）以及来自以下连接的 SQL完整加入.jmp以及所有的_代码.jmp表格。库名称语句、表上的相关 libname 引用以及进程 sql 语句被添加。其余代码由查询生成器生成。
请注意，大多数代码都是 SELECT 语句中列的列表。使用查询生成器生成此类代码非常有帮助，即使用户了解 SQL。如果不需要，为什么要输入那么多表和列名？
库名称 来源 '你的文件位置';
进程 sql;
创造 桌子源数据Nseries作为
选择t1.年份，t1.周期，t1.值，t1.脚注代码，
t2.lfst_code、t2.periodicity_code、t2.series_title、t2.absn_code、
t2.活动代码、t2.年龄代码、t2.类别代码、t2.持续时间代码，
t2.education_code、t2.entr_code、t2.expr_code、t2.hheader_code、
t2.hour_code、t2.indy_code、t2.jdes_code、t2.look_code、
t2.mari_code、t2.mjhs_code、t2.occupation_code、t2.orig_code、
t2.pcts_code、t2.race_code、t2.rjnw_code、t2.rnlf_code、
t2.rwns_code、t2.seek_code、t2.sexs_code、t2.tdat_code、
t2.vets_code、t2.wkst_code、t2.born_code、t2.chld_code、
t2.disa_code、t2.seasonal、t2.footnote_codes、t2.begin_year、
t2.开始周期，t2.结束年份，t2.结束周期从源.所有数据 t1
左边外连接源.系列 t2
在 （t1.系列编号 = t2.系列编号） ；
选择t1.年份，t1.周期，t1.值，t1.系列标题，
t1.季节性，t1.开始年份，t1.开始周期，t1.结束年份，
t1.end_period、t13.footnote_text、t34.wkst_text、t33.vets_text、
t32.tdat_text，t31.sexs_text，t30.seek_text，t29.rwns_text，
t28.rnlf_text，t27.RJNW_TEXT，t26.race_text，t25.periodicity_text，
t24.pcts_text，t23.orig_text，t22.occupation_text，t21.mjhs_text，
t20.mari_text，t19.look_text，t18.lfst_text，t17.jdes_text，
t16.indy_text，t15.hour_text，t14.hheader_text，t12.expr_text，
t11.entr_text，t10.education_text，t9.duration_text，t8.disa_text，
t7.class_text，t6.chld_text，t5.born_text，t4.ages_text，
t3.activity_text，t2.absn_text从源数据Nseries t1
左边OUTER JOIN 源.脚注 t13
在 （t1.脚注代码 = t13.脚注代码）
左边外连接源.wkst t34
在 （t34.wkst_code = t1.wkst_code）
左边外连接源.vets t33
在 （t33.vets_code = t1.vets_code）
左边外连接源.tdat t32
在 （t32.tdat_代码 = t1.tdat_代码）
左边外连接源.sexs t31
在 （t31.性别代码 = t1.性别代码）
左边外连接源.seek t30
在 （t30.seek_code = t1.seek_code）
左边外连接源.rwns t29
在 （t29.rwns_code = t1.rwns_code）
左边外连接源.rnlf t28
在 （t28.rnlf_code = t1.rnlf_code）
左边外连接源.rjnw t27
在 （t27.RJNW_CODE = t1.rjnw_code）
左边外连接源.race t26
在 （t26.比赛代码 = t1.比赛代码）
左边外连接源.周期性 t25
在 （t25.周期性代码 = t1.周期性代码）
左边OUTER JOIN 源.pcts t24
在 （t24.pcts_code = t1.pcts_code）
左边OUTER JOIN 源.orig t23
在 （t23.原始代码 = t1.原始代码）
左边外连接源.职业 t22
在 （t22.职业代码 = t1.职业代码）
左边外连接源.mjhs t21
在 （t21.mjhs_code = t1.mjhs_code）
左边外连接源.mari t20
在 （t20.mari_code = t1.mari_code）
左边外连接源.look t19
在 （t19.外观代码 = t1.外观代码）
左边外连接源.lfst t18
在 （t18.lfst_code = t1.lfst_code）
左边外连接源.jdes t17
在 （t17.jdes_code = t1.jdes_code）
左边外连接源.indy t16
在 （t16.indy_code = t1.indy_code）
左边OUTER JOIN 源.小时 t15
在 （t15.小时代码 = t1.小时代码）
左边外连接源.hheader t14
在 （t14.标题代码 = t1.标题代码）
左边外连接源.expr t12
在 （t12.表达式代码 = t1.表达式代码）
左边外连接源.entr t11
在 （t11.entr_code = t1.entr_code）
左边外连接源.education t10
在 （t10.教育代码 = t1.教育代码）
左边OUTER JOIN 源.持续时间 t9
在 （t9.持续时间代码 = t1.持续时间代码）
左边外连接源.disa t8
在 ( t8.disa_code = t1.disa_code )
左边OUTER JOIN 源.类 t7
在 （t7.类别代码 = t1.类别代码）
左边外连接源.chld t6
在 （t6.孩子代码=t1.孩子代码）
左边外连接源.born t5
在 （t5.出生代码 = t1.出生代码）
左边外连接源.ages t4
在 （t4.年龄代码 = t1.年龄代码）
左边OUTER JOIN 源.活动 t3
在 （t3.活动代码=t1.活动代码）
左边外连接源.absn t2
在 （t1.absn_code = t2.absn_code）；
辞职;
概括
分析师需要将数据放在一个表中，并以一种可以进行可视化和分析的方式进行组织。然而， 数据通常被组织成多个表（规范化）以便加载、维护和存储，因此分析师需要将多个表合并并汇总成一个可供分析的表。
JMP 查询生成器是一种在 JMP 中连接多个表的强大方法。预览功能允许用户在运行耗时的表连接操作之前查看连接是否正确定义。生成的 SQL 可以部署到 SAS 或数据库中，从而节省大量手动编写此类代码的时间。
参考
[UGSCPS] 劳动力统计 - 当前人口调查 - CPS。检索日期：2016 年 8 月 18 日，来自http://www.bls.gov/cps/data.htm.
[DOHMH] DOHMH 纽约市餐厅检查结果。检索日期：2016 年 8 月 17 日，来源：https://data.cityofnewyork.us/Health/DOHMH-New-York-City-Restaurant-Inspection-Results/xx67-kt59.
[格雷格]Gregg, X. (2014 年 5 月 28 日)。地理编码地名。检索日期：2016 年 8 月 18 日，来自https://community.jmp.com/docs/DOC-6175.
