丹尼尔瓦伦特, 博士, JMP 高级产品经理, SAS
乔恩·魏斯, JMP 销售和营销副总裁, SAS
抽象的
JMP 13 引入了两种新工具，使分析师可以轻松处理常见数据问题。 通常两个或多个数据表通过一个公共键相关联，但它们在事实抽样频率或维度（例如，长与宽）方面有很大不同。 对这两个（或更多）表进行实际连接可能会创建一个在内存中变得非常大的结果表。 虽然当事实表和维度表相对紧凑时这很麻烦，但它可以快速生成一个太大而无法放入内存的连接表。 解决方案是使用虚拟连接，它保留连接关系而不实际创建连接表——有效地丰富事实表而不增加它的负担。 分析师经常发现自己需要在数据库中进行复杂的表连接或汇总并编写 SQL，这给他们带来了一系列痛苦，尤其是当存在缓慢变化的维度等复杂关系时。 JMP Query Builder 是一种简单的 SQL 生成和编写工具，其代码随后可在数据库中执行。 在您拖放了正确且理想的查询后，查询生成器会自动为您编写 SQL 代码，从而避免了繁琐且容易出错的手动工作。
关系表结构概述
分析师需要一种形式的数据，即在列中包含变量，在行中包含观察值。 数据可能还需要明确地对观察结果进行分组的分类列（例如，收入水平、国家、机器等）。 数据还可能具有进一步说明观察结果的属性（例如，地址、序列号、客户 ID 等）。
此类分析的源数据通常是存储在数据库中的关系数据结构。 许多现代数据科学课程涵盖与关系数据结构、数据库和 SQL 相关的主题。 但大多数工程师、科学家和统计学家并没有在学术环境中接触过这些主题，因此他们必须在持续的非正式教育中了解这些主题。 JMP Query Builder 为分析师提供了关系数据结构和分析就绪数据之间的桥梁。 通过学习一些术语和概念，分析师可以使用查询生成器生成可供分析的数据。
本文中对关系数据结构的简要讨论并不能取代对该主题的透彻研究；相反，它介绍了在 JMP 中使用查询生成器及其虚拟连接功能时使用的术语和定义。
为什么要有多个表来存储重要数据？
假设您需要设计一种方法来存储有关客户购买贵公司产品的数据。 您可能首先列出所有可能需要收集的变量，例如：
- 购买日期
- 采购量
- 采购方式（PO 或 CC）
- 产品SKU
- 产品描述
- 顾客姓名
- 客户地址
- 客户电话号码
这当然不是一个全面的列表，但它是一个好的开始。 现在考虑维护这些数据。 对于每次购买，此表将有八列和一行。 某些字段可能有许多不同的值，例如购买金额或购买日期。 其他字段将具有更少的不同值和许多重复值，例如产品 SKU 和描述。
减小表大小的常用方法是将表分成三个逻辑组。 首先是包含事务的表或事实，例如购买日期和购买金额。 第二张表包含客户信息，第三张表包含产品信息。 为了维护数据列之间的链接，必须添加一个具有链接信息的列来关联三个表。 这些列称为钥匙. 显示此布局的一种常见方法是使用实体关系图 (ER) . 每个实体都有一个表名、一个或一组唯一标识每一行的键，以及一组提供有关每一行信息的属性或变量。 客户购买日期的 ER 图如图 1 所示。
图1。 客户购买数据实体关系图
Customers Dimension 表中 Customer ID 旁边的钥匙符号表示，如果您知道客户 ID，则可以在表中唯一地找到该客户以及所有相关信息。 客户 ID 也在 Purchases Fact 表中。 当 Customer ID 在 Purchases Fact 表中时，它被称为外键，因为它可能出现多次，并且将外部表中的行唯一地定义为它出现的行。
使用图 1 中的布局，很容易看出如何更新产品描述。 为此，您只需编辑 Product Dimension 表，一项更改将应用于所有购买过的商品，这一好处称为可维护性. 如果这是一张表，其中 Product Description 重复了很多次，您将不得不编辑很多行，这将不可避免地导致错误和数据质量问题。
案例一：当前人口调查
现在让我们看一个使用 Query Builder 的例子。 美国政府通过当前人口调查 (CPS) [USGCPS] 收集了大量有趣的数据。 CPS 是：
“当前人口调查 (CPS) 是美国历史最悠久、规模最大、最受认可的调查之一。 它非常重要，它提供了关于将我们定义为个人和社会的许多事物的信息——我们的工作、我们的收入和我们的教育。 除了作为月度劳动力统计数据的主要来源外，CPS 还用于为各种其他研究收集数据，让国家了解其人民的经济和社会福祉。 这是通过在每月的基本 CPS 问题中添加一组补充问题来完成的。 补充查询每个月都有所不同，涵盖范围广泛的主题，例如子女抚养费、志愿服务、健康保险范围和入学率。 补充通常每年或每半年进行一次，但补充的频率和重复频率完全取决于什么最能满足补充发起人的需求。”
这些数据可作为 36 个文本文件下载，这些文件是制表符分隔的文本文件，因此可以在 JMP 中轻松打开。 查看 JMP 表，创建了一个 ER 图（图 2）来通知查询生成器中的工作。
图 2。 CPS 劳动力统计数据表的 ER 图
要使用查询生成器，您需要定义主表（事实）和辅助表（维度).
CPS 数据的事实表称为所有数据.jmp . 该表有超过 500 万行，但只有五列。 这些列有一个“series_id”、日期和一个“值”列。 要使数据有意义，您需要找出series_id.jmp方法。 图 2 中的 ER 图显示“series_id”与系列.jmp桌子。 接下来，在 JMP 中打开这两个表并使用查询生成器定义连接。
在 JMP 中，选择所有数据.jmp作为主（事实）表和系列.jmp作为二级（维度）表（图 3）。 JMP 将自动查找适合的键并设置连接，但您应该通过单击系列表右侧的维恩图来验证它。 Query Builder 在两个表中都找到了“footnote_codes”，但您不想将它们作为键连接，而只是“series_id”，因此应该删除键关系。
图 3。 用于连接的查询生成器所有数据.jmp和系列.jmp
现在您需要定义连接表中的列（图 4）。 为此，请选择所有数据.jmp在左上角并从该表中选择所需的列。 现在删除“series_id”，因为结果表中不需要该键（系列和数据.jmp ).
图 4。 查询生成器加入系列和数据.jmp对所有“ _code.jmp ” 表
请注意系列和数据.jmp大约是 5 倍大所有数据.jmp桌子。 这种膨胀是由于所有“_code”字段的重复值造成的。 很明显，将数据存储在 36 个表中减少了贮存大幅要求。 接下来，选择系列和数据.jmp作为主表和所有“_code.jmp ”表作为辅助表（图 5）。
图 5。 查询生成器加入所有“ _code.jmp ”
仅使用“_code”表中的“_text”字段构建查询，因为您不再需要关键变量。 使用 JMP 中的 Cols > Columns Viewer，您可以轻松获得表格中现在 42 列的摘要完整的 join.jmp。 您仍然有 570 万行，这与所有数据.jmp ，但现在您有了描述数据的文本字段。 尽管表全连接.jmp仍然是桌子的六倍大所有数据.jmp,您现在有一个可用于分析的表格。
使用 Graph Builder 绘制制造业失业趋势全连接.jmp . 在图 6 中，红色趋势线显示了经济衰退期间该行业的中断。 2016 年的失业率回到了 2006-2007 年的水平。 绿色趋势线显示了汽车制造业的就业水平。 从 2010 年到 2015 年，汽车制造业增加了超过 300,000 个工作岗位。
图 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 作为沙箱，真正的数据始终存储在服务器上）；接下来使用重新编码实用程序将这些级别更改为缺失。
图 7。 完整数据集的检查日期
在重新编码窗口中，有 1,151 条记录是 01/01/1900。 通过删除字符串然后单击 Done -> In Place 将这个旧值更改为 •。 由于 RECORD DATE 是提取数据的日期并且对该分析不重要，因此删除该列。
本文是关于虚拟连接和查询生成器的，因此您可能想知道为什么我们要查看非规范化表。 似乎有机会使这些数据正常化。 值得注意的是，这些原始数据中缺少各个餐厅的地理编码位置，该任务稍后可以通过 JMP 插件执行。 由于此过程的计算成本很高，因此仅对唯一位置进行地理编码是有意义的，这些位置可能对这些数据进行多次检查。 此外，似乎有机会获得额外的违规表格，这远远少于独特的检查。
在拆分数据之前，对这些数据进行抽样，以便您的探索练习更易于管理这些数字。
可以使用查询生成器轻松执行采样。 Tables > JMP Query Builder 将打开启动对话框。 因为在这种情况下您没有执行连接，所以使用DOHMH_New_York_City_Restaurant_Inspection_Results.jmp作为此时没有辅助表的主表。 在 Query Builder 中，有一个名为 Sample 的选项卡。 打开“对这个结果集进行采样”的复选框，您可以选择第一个否行或随机否行。 从这些数据中随机抽取 10% 的样本，因此将 44,815 放入随机否行。 将所有列添加到 Include Columns 部分，然后检查 SQL。 查询生成器已自动生成 SQL 来执行此查询：
选择 t1.CAMIS、t1.DBA、t1.BORO、t1.BUILDING、
t1.STREET, t1.ZIPCODE, t1.PHONE, t1."美食描述",
t1."检查日期", t1."操作", t1."违规代码", t1."违规说明",
t1."CRITICAL FLAG", t1.SCORE, t1.GRADE, t1."GRADE DATE",
t1."检验类型", t1."年[检验日期]"
来自 DOHMH_New_York_City_Restaurant_Inspection_Results t1
随机排序（）限制 44815；
使用后随机排序 ()对于我们的采样函数，此查询将生成适当的数据子集。 将其保存为一个子集，然后拆分餐厅人口统计数据和违规代码。 查询生成器也可以在这里使用。 从违规代码开始。 同样，您只需要一个主表。 将 VIOLATION CODE 和 VIOLATION DESCRIPTION 拖放到变量列表中，然后再次拖入 VIOLATION CODE，这样您就可以按计数聚合并生成具有所需属性的表。 将聚合列的 JMP 名称更改为 VIOLATION COUNT。 这种为列或派生列生成别名的能力是查询生成器的一项强大功能。 您还可以通过将 VIOLATION COUNT 拖动到 Order By 角色来选择按新创建的聚合列自动排序，如图 8 所示。
图 8。 聚合列重命名为 Violation Count，具有 Order By 属性
由于只有 88 个唯一的违规代码，此生成的查询更加紧凑。 命名此表后D-ViolationCodes.jmp , 吨现在是使用 Graph Builder 探索哪些代码拥有最多记录的好时机。 将 VIOLATION DESCRIPTION 拖到 X 轴，将 VIOLATION COUNT 拖到 Y 轴。 然后右键单击 VIOLATION DESCRIPTION 和 Order By VIOLATION COUNT（降序）。 要只关注最上面的类别，您可以在 VIOLATION COUNT 上使用 Local Data 过滤器，其中包含超过 2,500 个唯一记录的违规类别（图 9）。
图 9。 最高违例计数（样本表中有超过 2500 条唯一记录）
现在暂时离开违规表，您可以专注于独特的餐厅人口统计数据。 此时，还可以删除违规说明，这样就省去了主事实表中的一些重复行。 使用 VIOLATION CODE，这是一种更紧凑的组织这些数据的方法（三个字母的字符串与句子长的字符串）。
同样，使用查询生成器来剥离独特的餐厅人口统计数据。 纽约市很可能有不止一家同名披萨店，但 Query Builder 可以处理这种情况。 CAMIS 列用作可用于标识唯一餐厅的 ID。 在 Query Builder 中，将 CAMIS 拖入两次，其中一次应该按计数聚合并重命名为 INSPECTION COUNT。 其余的人口统计数据是 DBA、BORO、BUILDING、STREET、ZIPCODE、PHONE 和 CUISINE DESCRIPTION。 最后，为地理编码器计算一个名为 ADDRESS 的列，这将作为查询后脚本完成。 运行查询，然后使用具有以下公式的公式列：
：建筑|| “” || ：街道|| “” || :博罗 || “纽约，” ||字符（ ：邮政编码 ）
这个公式将正确的地址放在一起。 打字: 地址 <<获取脚本;在脚本编辑器中将生成为查询后脚本生成列所需的 JSL。 将该代码复制并粘贴到查询生成器中的相应选项卡中，然后生成一个列，将数据分成 20 组，以便更好地与地理编码交互。 生成一个名为Group and Initialize Data: Sequence Data, From 1 to 20, Step 1, Repeat 1的新列后，就可以获取脚本并将其粘贴到查询后脚本中。 现在两列都是脚本，您可以删除查询表并重新运行查询。 将此数据表另存为D-餐厅.jmp . 作为最后一步，从现在的事实表中删除额外的列，包括：DBA、BORO、BUILDING、STREET、ZIPCODE、PHONE 和 CUISINE DESCRIPTION。
将此表另存为F-检查.jmp . 现在您已准备好使用 Xan Gregg 创建的加载项对餐厅组进行地理编码，该加载项可在 JMP 文件交换 [GREGG] 上获得，它使用 OpenStreetMaps.org 对位置进行地理编码。 该服务计算量大且容易失败，因此最好拆分D-餐厅使用表 > 子集按组。 该平台的 Subset By 功能（如图 10 所示）在这里很有用。
图 10。 按餐厅集团拆分的子表平台
新创建的 20 个表可以使用加载项单独进行地理编码。 地址字段有助于确定正确的纬度和经度。 最后，将结果保存到同一个数据表中。
要将数据表恢复为具有位置的单个维度表，请使用 Tables > Concatenate 功能，合并 20 个组；它被命名为D-RestaurantswLocation.jmp。
现在您已经使用查询生成器分解了该表，您可以使用虚拟连接重新构建它。
首先，从我们的二维表开始：餐厅-wLocation.jmp和违规代码.jmp . 右键单击 VIOLATION CODE 并选择 Link ID 将设置虚拟连接的开始。 请注意，列列表中出现了一个小钥匙图标。 移动到 Restaurants 表并对 CAMIS 执行相同操作。 要完成虚拟连接，移动到包含所有检查的事实表，然后为 CAMIS 和 VIOLATION CODE 选择 Link Reference。
所有列都显示在事实表中，而无需物理连接内存中的表。 虚拟连接也可以通过列属性编写脚本：
对于链接参考：
设置属性（“链接参考”，参考表（“D（f）-Restaurants-wLocation.jmp”））；
对于链接 ID：
Set Property("链接 ID", 1);
图 11。 虚拟加入
图 11 显示您可以访问两个维度表中的列，而不会增加事实表的负担。
最后，构建一个仪表板以利用虚拟连接的列来可视化和探索这些数据。
图 12 显示了 Dashboard Builder 如何显示五个图形的集合。 要将其中两个图表用作选择过滤器，请从 Dashboard Builder 中的 Hierarchical Filter 模板开始，使用 CRITICAL FLAG 和 BORO 作为过滤器和 Hierarchical Filter。 接下来，使用等级计数图表、地图上分位数的平均分数，最后是按检查类型划分的日期计数热图。
图 12。 带有 Final NYC Inspection Dashboard 的 Application Builder 的 Dashboard Builder 模式
这只是您可以使用这些数据创建的可视化类型的示例，利用虚拟连接提供的链接表。 例如，地理编码数据还允许您按菜系深入了解餐馆的位置（图 13）。
图 13。 在街道地图上显示按等级着色的地理编码餐厅
SQL 生成和部署
使用 Query Builder 的另一个主要优势是生成结构化查询语言 (SQL)。 SAS 可以在 Proc SQL 中使用 SQL，大多数数据库使用 SQL 作为编程语言来连接和管理表。 SQL 可以从您在查询生成器的点击式界面中定义的查询生成。
下面是 Query Builder 创建的用于将所有维度表连接到的连接表的 SQL系列.jmp和所有数据.jmp （来自案例 1）和来自 join of 的 SQL全连接.jmp和所有的_code.jmp表。 这库名声明、相关联的 libname 对表的引用以及过程数据库声明已添加。 其余代码由查询生成器生成。
请注意，大部分代码是 SELECT 语句中列的列表。 使用 Query Builder 生成这样的代码是一个很大的帮助，即使用户知道 SQL。 如果不需要，为什么要键入那么多表名和列名？
库名来源'你的文件位置' ;
过程数据库;
创造桌子source.dataN系列作为
选择t1.year, t1.period, t1.value, t1.footnote_codes,
t2.lfst_code, t2.periodicity_code, t2.series_title, t2.absn_code,
t2.activity_code, t2.ages_code, t2.class_code, t2.duration_code,
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.begin_period, t2.end_year, t2.end_period从来源.AllData t1
左边外部连接 source.series t2
在( t1.series_id = t2.series_id ) ;
选择t1.year, t1.period, t1.value, t1.series_title,
t1.seasonal, t1.begin_year, t1.begin_period, t1.end_year,
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从source.dataN系列t1
左边外部连接 source.footnote t13
在( t1.footnote_codes = t13.footnote_code )
左边外部连接 source.wkst t34
在( t34.wkst_code = t1.wkst_code )
左边外部连接 source.vets t33
在( t33.vets_code = t1.vets_code )
左边外部连接 source.tdat t32
在( t32.tdat_code = t1.tdat_code )
左边外部连接 source.sexs t31
在( t31.sexs_code = t1.sexs_code )
左边外部连接 source.seek t30
在( t30.seek_code = t1.seek_code )
左边外部连接 source.rwns t29
在( t29.rwns_code = t1.rwns_code )
左边外部连接 source.rnlf t28
在( t28.rnlf_code = t1.rnlf_code )
左边外部连接 source.rjnw t27
在( t27.RJNW_CODE = t1.rjnw_code )
左边外部连接 source.race t26
在( t26.race_code = t1.race_code )
左边外部连接 source.periodicity t25
在( t25.periodicity_code = t1.periodicity_code )
左边外部连接 source.pcts t24
在( t24.pcts_code = t1.pcts_code )
左边外部连接 source.orig t23
在( t23.orig_code = t1.orig_code )
左边外部连接 source.occupation t22
在( t22.occupation_code = t1.occupation_code )
左边外部连接 source.mjhs t21
在( t21.mjhs_code = t1.mjhs_code )
左边外部连接 source.mari t20
在( t20.mari_code = t1.mari_code )
左边外部连接 source.look t19
在( t19.look_code = t1.look_code )
左边外部连接 source.lfst t18
在( t18.lfst_code = t1.lfst_code )
左边外部连接 source.jdes t17
在( t17.jdes_code = t1.jdes_code )
左边外部连接 source.indy t16
在( t16.indy_code = t1.indy_code )
左边OUTER JOIN source.hour t15
在( t15.hour_code = t1.hour_code )
左边外部连接 source.hheader t14
在( t14.hheader_code = t1.hheader_code )
左边外部连接 source.expr t12
在( t12.expr_code = t1.expr_code )
左边外部连接 source.entr t11
在( t11.entr_code = t1.entr_code )
左边外部连接 source.education t10
在( t10.education_code = t1.education_code )
左边外部连接 source.duration t9
在( t9.duration_code = t1.duration_code )
左边外部连接 source.disa t8
在( t8.disa_code = t1.disa_code )
左边外部连接 source.class t7
在( t7.class_code = t1.class_code )
左边外部连接 source.chld t6
在( t6.chld_code = t1.chld_code )
左边外部连接 source.born t5
在( t5.born_code = t1.born_code )
左边外部连接 source.ages t4
在( t4.ages_code = t1.ages_code )
左边外部连接 source.activity t3
在( t3.activity_code = t1.activity_code )
左边外部连接 source.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 .
