丹尼爾·瓦倫特，博士，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. 顧客購買資料的實體關係圖
客戶維度表中客戶 ID 旁邊的鑰匙符號表示如果您知道客戶 ID，您就可以在表中唯一地找到該客戶及其所有相關資訊。客戶 ID 也在購買事實表中。當客戶 ID 位於購買事實表中時，它被稱為外鍵，因為它可能出現多次，並且唯一地定義了外部表中它出現的行。
透過圖 1 中的佈局，可以輕鬆看到如何更新產品描述。要做到這一點，您只需編輯產品維度表，而此變更將應用於所有購買，此好處稱為可維護性。如果這是一張包含多次重複產品描述的表格，則您必須編輯多行，這不可避免地會導致錯誤和資料品質問題。
案例一：當前人口調查
現在讓我們來看一個使用查詢產生器的範例。美國政府透過當前人口調查（CPS）[USGCPS]收集了大量有趣的數據。 CPS 是：
“目前人口調查（CPS）是美國歷史最悠久、規模最大、最知名的調查之一。它極為重要，提供了許多定義我們作為個人和社會的資訊——我們的工作、我們的收入和我們的教育。除了作為月度勞動力統計數據的主要來源之外，CPS 還用於收集各種其他研究的數據，讓國家了解人民的經濟和社會福祉。這是透過在每月的基本 CPS 問題中添加一組補充問題來實現的。補充諮詢每個月都有所不同，涵蓋各種各樣的主題，例如子女撫養費、志願服務、健康保險範圍和學校入學等。補充通常每年或每兩年進行一次，但補充的頻率和重複完全取決於最能滿足補充贊助商的需求。 」
這些資料可以作為 36 個文字檔案下載，由於這些檔案是製表符分隔的文字文件，因此可以輕鬆地在 JMP 中開啟。查看 JMP 表，建立了 ER 圖（圖 2）來告知查詢產生器中的工作。
圖 2. CPS 勞動力統計表的 ER 圖
要使用查詢產生器，您需要定義主表（事實）和次表（維度）。
CPS 資料的事實表稱為所有數據.jmp。該表有 500 多萬行，但只有 5 列。列有一個「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 筆記錄。
這些資料有一個檢查日期列，它是檢查事件的 M/D/Y。若要查看這些資料包含多少年份，請以滑鼠右鍵並使用新公式列>日期時間>年份。圖 7 顯示將其從連續轉換為名義，然後分析 > 分佈。等級 1900 可能是缺少資料的程式碼，您需要解決這個問題。破壞性地編輯這些資料不是問題（因為這裡使用 JMP 作為沙箱，並且真實資料始終儲存在伺服器上）；接下來使用重新編碼實用程式將這些層級變更為缺失。
圖 7. 完整資料集的檢驗日期
在重新編碼視窗中，有 1,151 筆記錄為 01/01/1900。透過刪除字串然後按一下「完成」->「就地」將此舊值變更為•。由於記錄日期是提取資料的日期且與本次分析無關，因此請刪除該列。
本文討論的是虛擬連接和查詢產生器，所以您可能想知道為什麼我們要看非規範化表。似乎有機會使這些數據規範化。值得注意的是，這些原始資料中缺少各個餐廳的地理編碼位置，這項任務可以稍後使用 JMP 外掛程式來執行。由於該過程在計算上很昂貴，因此僅對唯一位置進行地理編碼才有意義，這些數據中可能會有多次檢查。此外，似乎還有機會獲得額外的違規表，其數量遠遠少於唯一檢查。
在拆分資料之前，先對這些資料進行抽樣，以便您的探索練習能夠更輕鬆地管理這些數字。
使用查詢產生器可以輕鬆執行採樣。表格 > JMP 查詢產生器將開啟啟動對話方塊。因為在這種情況下您沒有執行連接，所以請使用DOHMH_紐約城餐廳檢驗結果.jmp此時為主表，沒有次表。在查詢產生器中，有一個名為「Sample」的選項卡。選取「對此結果集進行取樣」複選框，您可以選擇前N行或隨機N行。從這些數據中隨機取 10% 的樣本，因此將 44,815 放在隨機N行中。將所有列新增至「包含列」部分，然後檢查 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後，現在是使用圖形產生器探索哪些程式碼擁有最多記錄的好時機。將 VIOLATION Description 拖曳到 X 軸，將 VIOLATION COUNT 拖曳到 Y 軸。然後右鍵單擊“VIOLATION Description”並按“VIOLATION COUNT”排序（降序）。為了只專注於最主要的類別，您可以使用包含違規類別的「違規計數」的本機資料篩選器，這些類別有超過 2,500 個唯一記錄（圖 9）。
圖 9. 違規次數最多（採樣表中有 >2500 筆唯一記錄）
現在，您可以暫時離開違規表，專注於獨特的餐廳人口統計。此時，您還可以刪除違規描述，這可以節省主事實表中的一些重複行。使用 VIOLATION CODE，這是組織這些資料的更緊湊的方式（三個字母的字串與句子長度的字串）。
再次，使用查詢產生器來提取獨特的餐廳人口統計資料。紐約很可能有多家同名披薩店，但 Query Builder 可以處理這種情況。 CAMIS 欄位用作 ID，可用於識別唯一的餐廳。在查詢產生器中，拖入 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-餐廳 使用“表格”>“子集”按群組。如圖 10 所示，該平台的 Subset By 功能在這裡很有用。
圖 10. 按餐廳組別劃分的子集表平台
可以使用該外掛程式對新建立的 20 個表進行單獨地理編碼。地址字段有助於確定正確的緯度和經度。最後，將結果儲存到同一張資料表中。
若要將資料表還原為具有位置的單一維度表，請使用表格>連接功能，合併 20 個群組；它被命名為 D-RestaurantswLocation.jmp。
現在您已經使用查詢產生器分解了該表，您可以使用虛擬連接重新建置它。
首先，從我們的兩個維度表開始： 餐廳-wLocation.jmp 和違規代碼.jmp. 右鍵單擊“違規代碼”並選擇“連結 ID”將設定虛擬連接的開始。請注意，清單中出現了一個小鑰匙圖示。移至餐廳表格並對 CAMIS 執行相同操作。要完成虛擬連接，請前往包含所有檢查的事實表，然後選擇 CAMIS 和 VIOLATION CODE 的連結參考。
所有欄位都會顯示在事實表中，而無需在記憶體中實體連接表。虛擬連線也可以透過列屬性來編寫腳本：
連結參考：
設定屬性（“連結引用”，參考表（“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.
