良いクエリは成功への道を切り開く
レベル: 初級
ブライアン・コーコラン、SAS JMP研究開発ディレクター
JMP は、さまざまなデータ形式を製品に取り込むためのさまざまな方法を提供します。その理由は明らかです。データを収集してフォーマットするためのツールが長年にわたって開発されてきました。ほとんどの場合、これらのツールではデータを JMP データ テーブルに変換できません。分析するには、このデータを JMP に取り込む必要があります。また、データがどこかのデータベースにある場合は、開始する前に抽出する必要があります。データの取り込みには貴重な時間がかかります。JMP は長年にわたって進化し、このタスクを支援する強力なデータ取得機能を追加してきました。
このホワイト ペーパーでは、JMP のクエリ ビルダー機能の使用方法を少し紹介し、Excel ウィザードの簡単な使用例も紹介します。クエリの作成自体からデータに関する洞察が得られるという点で、この過程が報いとなることも示せれば幸いです。デスクトップのスクリーンショットには Windows 版が使用されていますが、ここで説明する機能はすべて Mac 版の JMP でも利用できます。JMP 14 を使用しましたが、JMP 13 でも説明されているすべての機能を実行できます。
この論文では、仮想的な使用例を紹介します。世界の動向を研究していて、世界銀行のデータベースがしばしば貴重な情報を提供していると想像してください。同僚から、2017 年の世界開発指標データが最近利用可能になり、そのデータを会社の SQL Server データベースに取り込んだと聞きました。これは、400,000 を超える観測値と 62 の列を持つ大きなテーブルです。 [1] JMP を開いて、[ファイル] -> [データベース] -> [クエリ ビルダー] を選択し、データベース接続を開いてテーブルを指定します。クエリ ビルダーの最初の画面で、world_bank テーブルをプライマリ テーブルとして選択し、[テーブル スナップショット] を使用して確認します。
Query Builder Table Snapshotクエリビルダー テーブルスナップショットクエリビルダー テーブルスナップショット
これは、少なくとも一般的な JMP ユーザーにとっては、いくぶん奇妙な形式です。国情報用の 4 つの変数または列があり、その後に各年のデータ用の列があります。行には、地域と思われるものがあり、その後に測定されたデータがあります。いくつかの点で、これは一般的な JMP テーブルに期待されるものとは逆のように見えます。国名「アラブ世界」は地域を表しているように見えるため、これも混乱を招きます。[今すぐインポート] ボタンを押すと、テーブルが表示され、少しわかりやすくなります。真ん中までスクロールすると、行が問題の国で始まり、次に「農村人口」などの特定の主題のデータが続くことがわかります。これはすべて国別にソートされていますが、直感的ではないようです。
Immediate Import Table Appearance即時インポートテーブルの外観即時インポートテーブルの外観
最初からやり直しましょう。今回は、「今すぐインポート」ではなく、「クエリの作成」を選択します。国情報を含む最初の 4 つの変数が必要なことはわかっているので、それらを選択し、クエリ ビルダーの列選択パネルの [含まれる列]タブで [追加] を押します。ここには大量のデータがあるので、2010 ～ 2017 年の変数だけを実行しましょう。この時点で、ダイアログの上部は次のようになります。
Initial Column Selection初期列選択初期列選択 以前の状況はまだ改善されていません。これで、「インジケーター コード」変数をクエリ ビルダーの [並べ替え] ペインに移動できます。これにより、データが地域や国ではなく、調査対象の主題別に整理されます。分析がより直感的になるようです。
Ordered by Indicator Codeインジケーターコード順インジケーターコード順
データにはまだ問題があります。まず、現在関心のない地域グループがあります。これらの集計エントリを除外するにはどうすればよいでしょうか。また、 「肥料消費量」などの特定の項目を照会する最適な方法は何でしょうか。
「指標コード」で並べ替えを行わない場合は、すべてのデータを JMP にインポートしてから地域グループを削除することは確かに可能です。 しかし、それはデータを分析する人に負担をかけます。クエリを設定していない場合、混乱を招く可能性があります。クエリ内では、関心のある国だけを選択することもできます。欧州連合のすべての国が必要な場合はどうでしょうか? (北米を使用するつもりでしたが、世界銀行の定義には米国、バミューダ、カナダのみが含まれています!) 手動で選択することもできますが、もっと良い方法があります。そのためには、JMP の別のツールである Excel ウィザードをすばやく呼び出します。
このシナリオでは、世界銀行のデータを頻繁に操作するとします。国コードは重要ですが、直感的ではありません。Web サイトを見るとわかるように、表では国名全体を指定せずにコードを使用することがよくあります。これらのコードを別の表にすると便利かもしれません。そのような表は存在しており、Web サイトから Excel ファイルとして簡単にインポートできます。したがって、この場合は、国と国コードの表で Excel ウィザードを開きます。世界銀行の Web サイトでは、これらを CLASS.xls というわかりにくい表にグループ化していますが、名前を変更できます。ファイル内には、「経済の一覧」と「グループ」という 2 つの表があります。「グループ」を見てみましょう。
Preview of Groups Table in Excel FileExcel ファイルでのグループ テーブルのプレビューExcel ファイルでのグループ テーブルのプレビュー
クエリ ビルダー データで確認したアラブ世界のグループがあります。これは有望に見えますが、下にスクロールしてもプレビューが 100 行で終了するため、ヨーロッパはまだ表示されません。[すべての行を表示] を選択すると、[ヨーロッパと中央アジア]、[ヨーロッパ地域]、[欧州連合] などすべてが表示されます。[欧州連合] グループには、現在連合に加盟しているすべての国の国コードが含まれています。これを使用してクエリ ビルダー内で国を選択できますか? 答えは「はい」です。では、必要なグループだけを抽出してみましょう。これを行うには、プレビューで [欧州連合] の最初の行を選択し、[データは行から始まります] の横にある緑色のプラスを押します。
Select the First Row in “European Union”「欧州連合」の最初の行を選択します「欧州連合」の最初の行を選択します
ここで、ダイアログの「次へ」ボタンを押し、「欧州連合」の最後の行を選択して、「データは行で終わります」の横にある緑色のプラスを押します。これにより、関心のある国だけにデータが絞り込まれます。
ここで、「インポート」を選択すると、欧州連合加盟国の 28 のエントリを含む行が表示されます。次に、結果の JMP テーブルを「EU.jmp」として保存します。
ここで、クエリ ビルダーに戻ります。関心のある国を選択するには、リストで「国名」または「国コード」を選択するという面倒な手順を踏む必要があります。ただし、クエリ ビルダーには、列値の一致という強力なフィルター タイプがあります。これを使用するには、まず「国コード」をフィルターペインにドラッグします。これにより、すべてのコードを含む長いリストが生成されます。次に、フィルター タイプの「 t1.国コード」の赤い三角メニューを選択し、「列値の一致」を選択します。開いている JMP テーブルの名前を示すボックスが表示されます。「EU.jmp」はまだ開いているため、リストに表示されます。これを選択し、一致させる変数 ( 国コード ) を選択します。また、この場合はすべての国が必要なので、ドロップダウン リストから「選択した行」ではなく「すべての行」が選択されていることを確認することも重要です。
Selecting the Match Column Values filter列値の一致フィルターの選択列値の一致フィルターの選択
これにより、結果が EU 加盟国が関係する観測のみに絞り込まれます。ここでは、魔法は何も行われません。国コードを置換する WHERE/IN 句を作成しています。クエリ ビルダー内の構文パネルを見ると、JMP が生成している SQL を確認できます。
Generated SQL生成されたSQL生成されたSQL
しかし、このプロセスがずっと簡単に実行できるようになったと思います。本当に興味のあるデータに絞り込んだので、分析したい特定の情報を簡単に選択できるようにしておくと良いでしょう。これを行うには、「インジケーター名」をフィルターペインにドラッグします。ここで興味のある項目をクリックしてクエリを実行することもできますが、ユーザーにとってよりインタラクティブなインターフェイスがあると便利です。「 t1.インジケーター名」の赤い三角形のメニューを押して、「実行時にプロンプト」を選択します。これにより、エンドユーザーへのプロンプトのタイトルを指定するためのダイアログが表示されます。
Prompt on Run実行時にプロンプトを表示実行時にプロンプトを表示
ここで、「クエリを実行」ボタンを押すと、項目のリストが表示され、関心のある項目を選択できます。たとえば、 「GDP 成長率 (年率 %)」を選択すると、28 行のテーブルが表示されます。次に、それをグラフ ビルダーに配置できます。 「国名」をマップ シェイプドロップ領域にドラッグすると、ヨーロッパの地図が作成され、関心のある年をドラッグできます。2014 年以降はデータがまばらになり始めるため、その年を選択すると次のようになります。
JMP は「スロバキア共和国」ではなく「スロバキア」を探しているので、 Recode を使用する機会があるかもしれません。このデータを見ると、常に同様の操作を行うように見えます。マップ シェイプの国名を選択し、結果を調べるために関心のある年を選択します。特定の年を繰り返し調べる場合は、スクリプトを保存して、クエリ ビルダーにポスト クエリ スクリプトとして配置するのが理にかなっています。プレビュー ペインの横に、このためのタブがあります。前のグラフに戻り、赤い三角のメニューを使用して [スクリプトの保存] -> [スクリプトウィンドウに] を実行すると、次のようなスクリプトが表示されます。
Graph Builder(
Size( 534, 454 ),
Show Control Panel( 0 ),
Variables( Color( :Name( "2014" ) ), Shape( :Country Name ) ),
Elements( Map Shapes( Legend( 7 ) ) )
);
これを「クエリ後のスクリプト」タブに貼り付けると、調べたい観測を選択すると、データが抽出されて JMP テーブルが作成された後に、常に同じグラフ ビルダー レポートが実行されます。これにより、繰り返しのタスクが大幅に簡素化されます。
Post-Query Scriptクエリ後のスクリプトクエリ後のスクリプト
最も優れている点は、クエリを保存するときに、エンド ユーザーにクエリ ビルダー ダイアログが表示されないように設定できることです。クエリ名フィールドのドロップダウン メニューで [開いたときに実行] を選択し、クエリを保存すると、エンド ユーザーにはプロンプトと結果のテーブルとグラフのみが表示されます。
Run on Openオープン時に実行オープン時に実行
Query with Run on Open started from Recent Files最近使ったファイルから実行して開くクエリ最近使ったファイルから実行して開くクエリ 一致する列フィルターを使用する際の注意点の 1 つは、一致する値を持つテーブルが、クエリを実行するマシン上に存在する必要があることです。これが問題になる場合は、回避策があります。一致する列を持つフィルターを作成したら、フィルターの赤い三角形のメニューに移動します。この例の場合、それは「 t1.Country Code」になります。フィルター タイプ -> 手動リストを選択します。これにより、一致する列フィルターによって作成された値が、SQL に使用されるテキスト リストにインポートされます。これは、次に保存するときにクエリに保存され、データ テーブルを一致させる必要がなくなります。この方法の欠点は、選択した一致した値を動的に変更してクエリを変更する機能が失われることです。
この時点で、大量の混乱したデータをフィルタリングし、エンド ユーザーに提供できる繰り返し可能なクエリと分析を作成しました。クエリを使用するユーザーは、クエリに関する知識や、JMP に関する知識をほとんど必要としません。
使用される規則:
クエリビルダーのウィンドウペインは太字で表示されます
ボタン/ダイアログコントロールは「引用符付き太字」で表示されます
変数/列名は「引用符で囲み、斜体で表記」
[1]世界銀行グループ。世界開発指標、世界銀行グループ、nd Web。2018年1月25日。https 
/datacatalog.worldbank.org/dataset/world-development-indicators。Create Commons CC BY 4.0ライセンスに基づいて取得。
