良好的查询可以帮助您取得成功
级别：初级
Brian Corcoran，JMP 研发总监，SAS
JMP 提供了多种方法将各种数据格式输入到产品中。 原因是显而易见的。多年来开发了许多工具来收集和格式化数据。在大多数情况下，这些工具不允许您将数据转换为 JMP 数据表。您需要将此数据导入 JMP 进行分析。在其他情况下，数据可能位于数据库中的某个位置，您需要在开始之前提取它。获取数据可能会消耗宝贵的时间。JMP 多年来不断发展，增加了强大的数据采集功能来帮助您完成这项任务。
本文尝试提供有关如何使用 JMP 中的查询生成器功能的小样本，以及使用 Excel 向导的简单示例。希望它也表明这段旅程也可以是回报，因为可以通过准备查询本身来获得对数据的洞察。 虽然 Windows 版本用于桌面屏幕截图，但此处讨论的所有功能在 JMP 的 Mac 版本中也可用。 使用了 JMP 14，但 JMP 13 也可以完成讨论的所有操作。
本文提出了一个假设的使用案例。想象一下，您正在研究世界趋势，并且世界银行数据库经常提供有价值的信息。同事告诉你，2017年世界发展指标数据最近已经出来了，他们已经将数据存入公司的SQL Server数据库中。这是一个很大的表，包含超过 400,000 个观测值和 62 列。 [1]打开 JMP 并选择“文件”->“数据库”->“查询生成器”，打开数据库连接并指向该表。在查询生成器的第一个屏幕中，选择 world_bank 表作为主表并使用“表快照”查看：
Query Builder Table SnapshotQuery Builder Table Snapshot查询生成器表快照
至少对于典型的 JMP 用户来说，这是一种有点奇怪的格式。有四个变量或列用于国家/地区信息，然后有一列用于每年的数据。这些行看起来像是一个区域，然后是正在测量的数据。在某些方面，它看起来与典型 JMP 表格的预期相反。国家名称“阿拉伯世界”似乎代表一个地区，所以这也令人困惑。如果您按“立即导入”按钮，桌子就进来了，事情变得更加清晰了。通过滚动到中间，您可以看到行以相关国家/地区开头，然后是特定主题的数据位，例如“农村人口”。这都是按国家/地区排序的，这看起来并不直观。
Immediate Import Table AppearanceImmediate Import Table Appearance立即导入表外观
让我们重新开始。这次，我们可以继续选择“构建查询“ 而不是 ”立即导入”。我们知道我们需要前四个变量包含国家/地区信息，因此我们可以选择它们并按“添加“ 在里面包含的列查询生成器中列选择面板的选项卡。这里的数据很多，所以我们只做2010-2017年的变量。此时，对话框顶部如下所示：
Initial Column SelectionInitial Column Selection初始列选择我们之前的情况确实还没有改善。现在，我们可以移动“指标代码” 变量进入订购依据查询生成器的窗格。这将按正在研究的主题而不是相关地区或国家来组织数据。这样对于分析来说似乎更加直观。
Ordered by Indicator CodeOrdered by Indicator Code按指标代码排序
数据还存在问题。首先，它有目前不感兴趣的区域集团。如何排除这些聚合条目？另外，查询特定项目的最佳方式是什么，例如《化肥消耗量》 ？
如果我们不按顺序排序，当然可以将所有数据导入 JMP，然后删除区域分组“指标代码”。然而，这给分析数据的人带来了负担。如果有人没有设置查询，那可能会令人困惑。在查询中，可以仅选择感兴趣的国家/地区。如果我们想要欧盟的所有国家怎么办？（我本来打算使用北美，但世界银行的定义只包含美国， 百慕大和加拿大！）我们可以手动选择它们，但有更好的方法。为此，我们将快速调用 JMP 中的另一个工具，即 Excel 向导。
在这种情况下，假设我们经常使用世界银行的数据。国家/地区代码很重要，但并不直观。表格通常使用代码而不指定完整的国家/地区名称，正如您通过浏览网站可以看到的那样。将这些代码放在单独的表中可能会很方便。 这样的表格是存在的，并且可以很容易地以 Excel 文件的形式从网站导入。因此，在本例中，打开国家/地区和国家/地区代码表上的 Excel 向导。 世界银行网站将它们分组在一个名为 CLASS.xls 的不起眼的表中，但您可以对其进行重命名。文件中有两个表，“经济体列表”和“组”。我们来看看“组”：
Preview of Groups Table in Excel FilePreview of Groups Table in Excel FileExcel 文件中的组表预览
我们在查询生成器数据中看到了阿拉伯世界的分组。 这看起来很有希望，但向下滚动还没有显示欧洲，因为预览在 100 行处结束。选择 ”显示所有行”我们可以看到一切，包括“欧洲和中亚”, “欧洲地区“ 和 ”欧洲联盟”。这 ”欧洲联盟”组包含当前联盟中所有国家/地区的国家/地区代码。我们可以使用它在查询生成器中选择国家/地区吗？答案是肯定的，所以让我们继续提取我们需要的组。我们可以通过使用“选择预览中的第一行”来做到这一点欧洲联盟”，然后按“旁边的绿色加号数据从行开始”。
Select the First Row in “European Union”Select the First Row in “European Union”选择“欧盟”中的第一行
现在我们可以按对话框上的“下一步”按钮，选择最后一行“欧洲联盟”，然后按“”旁边的绿色加号数据以行结尾”。 这将数据减少到我们感兴趣的国家/地区。
现在我们可以选择“进口”并获得一排包含 28 个欧盟国家条目的条目。我们将继续将生成的 JMP 表保存为“EU.jmp”。
现在我们可以返回到查询生成器。我们可以选择我们感兴趣的国家，通过一个繁琐的过程在列表中选择它们： “国家的名字”或者“国家代码”。但是，查询生成器有一个强大的过滤器类型，称为“匹配列值”。要使用它，我们首先拖动“国家代码”进入过滤器窗格。这将产生一个长列表，其中包含每个代码。现在，我们可以选择红色小三角菜单“ t1.国家代码“ 为了过滤器类型并选择“匹配列值”。将出现一个显示打开的 JMP 表名称的框。由于我们仍然打开“EU.jmp”，因此它会显示在列表中。我们选择它，变量 (国家代码）我们想要匹配的。确保“所有行”是从下拉列表中选择的，而不是“选定的行”，因为我们希望这种情况下的所有国家都参与其中。
Selecting the Match Column Values filterSelecting the Match Column Values filter选择匹配列值过滤器
这将把我们的结果减少到仅涉及欧盟国家的观察结果。这里真的没有魔法。我们正在构建一个 WHERE/IN 子句来替换国家/地区代码。如果你看一下SQL在查询生成器中的窗格中，您可以看到 JMP 生成的 SQL：
Generated SQLGenerated SQL生成的 SQL
但希望我们已经让这个过程更容易完成。现在我们已经将数据范围缩小到我们真正感兴趣的范围，如果能够更轻松地选择我们想要分析的特定信息，那就太好了。为此，我们可以拖动“指标名称”到过滤器窗格。虽然我们可以单击此处感兴趣的项目，然后运行查询，但如果能够为任何用户提供更具交互性的界面，那就太好了。我们可以按“ t1.指标名称”并选择“运行时提示”。这将为我们提供一个对话框，用于指定最终用户提示的标题。
Prompt on RunPrompt on Run运行时提示
现在，当我们按下“运行查询”按钮，我们将获得项目列表，我们可以选择我们感兴趣的项目。例如我们可以选择“GDP 增长率（年百分比）”并得到一个28行的表。然后我们可以将其放入图形生成器中。拖动“国家的名字”进入地图形状拖放区域会产生一张欧洲地图，然后我们可以将感兴趣的年份拖进去。2014 年之后数据开始变得稀疏，因此这一年会产生：
JMP 正在寻找“斯洛伐克”而不是“斯洛伐克共和国”，因此这可能是一个使用的机会重新编码。从这些数据来看，我们似乎总是会做类似的操作。 我们将为地图形状选择国家/地区名称，然后选择我们感兴趣的年份来检查结果。如果我们要重复查看某一年，则保存脚本并将其作为查询生成器放入查询生成器中是有意义的查询后脚本。 预览窗格中有一个用于此目的的选项卡。如果我们转到上一张图，并使用红色小三角菜单执行“保存脚本”->“到脚本窗口”，我们将得到一个如下所示的脚本：
Graph Builder(
Size( 534, 454 ),
Show Control Panel( 0 ),
Variables( Color( :Name( "2014" ) ), Shape( :Country Name ) ),
Elements( Map Shapes( Legend( 7 ) ) )
);
如果我们将其粘贴到查询后脚本选项卡中，当我们选择要检查的观测值时，在提取数据并创建 JMP 表后，它将始终运行相同的图形生成器报告。这可以大大简化重复性任务。
Post-Query ScriptPost-Query Script查询后脚本
最好的部分是，当我们保存查询时，我们可以对其进行设置，以便最终用户甚至看不到查询生成器对话框。通过选择“打开时运行”在查询名称字段的下拉菜单中，然后保存查询，最终用户只会看到提示以及生成的表格和图表。
Run on OpenRun on Open打开时运行
Query with Run on Open started from Recent FilesQuery with Run on Open started from Recent Files从最近的文件开始查询打开时运行使用匹配列过滤器的一个注意事项是，具有要匹配的值的表必须存在于我们将运行查询的计算机上。如果这是一个问题，有一种方法可以解决这个问题。创建具有匹配列的过滤器后，转到过滤器的红色小三角菜单。在这个例子中，这将是“ t1.国家代码”。选择过滤器类型 -> 手动列表。 这会将匹配列过滤器创建的值导入到将用于 SQL 的文本列表中。下次保存时，这将被保存到查询中，并且您将不再需要数据表来匹配。这种方法的缺点是失去了动态更改所选匹配值和更改查询的能力。
至此，我们已经过滤了大量令人困惑的数据，并创建了可重复的查询和分析，可以将其发送给最终用户。使用该查询的人实际上并不需要了解该查询，或者根本不需要了解很多 JMP 知识。
使用的约定：
查询生成器窗口窗格是加粗的
按钮/对话框控件是“引用和加粗”
变量/列名称是“引用并斜体”
[1]世界银行集团。世界发展指标、世界银行集团和网络。 2018 年 1 月 25 日。 https://datacatalog.worldbank.org/dataset/world-development-indicators 。 根据 Create Commons CC BY 4.0 许可证获得。
