丹尼爾瓦倫特, 博士, 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 .
