Eric Hill ( エリック・ヒル )、SAS、JMP 主席システム開発者
注: これは論文のテキストのみであり、スクリーンショットは含まれていません。全体を確認するには、添付の論文バージョンを参照してください。
クエリビルダー
SQL データを JMP に取り込むための新しい JMP 12 ツール
(JMP 12.1 用に更新)
Eric Hill、JMP ソフトウェア開発者
コンテンツ
- 導入. 2
セクション 1: 接続を確立する. 3
セクション 2: クエリのテーブルの選択. 6
セクション 3: クエリの構築. 11
列の追加. 13
クエリを保存する. 15
クエリの実行. 15
質問に答える. 16
- フィルタリング. 19
プロンプトフィルター. 23
- サンプリング. 25
独自のSQLを書く. 26
セクション 4: バックグラウンド クエリの管理. 27
クエリステータスパネル 28
クエリ実行ウィンドウ..28
セクション 5: クエリの共有. 28
セクション 6: JMP スクリプト言語 (JSL) からのクエリの実行. 29
JMP 12.1 の新機能は何ですか?. 31
- 検索. 31
関連テーブルを追加する. 32
テンプレートとして開く. 34
計算列の数式を編集する. 34
バグ修正. 35
- 結論. 35
導入
JMP には、すぐに使える分析ツールが豊富に用意されていますが、分析に必要なデータを必要な方法で JMP に取り込めなければ、それらのツールは役に立ちません。JMP では、かなり前から、 [ファイル] > [データベース] > [テーブルを開く]からリレーショナル データベースから JMP にデータをインポートする機能をサポートしています。[テーブルを開く] は、そこまでは問題ありませんが、いくつか注目すべき制限があります。
- リレーショナル データベースのほとんどのデータは正規化された方法で保存されており、必要なすべてのデータを取得するには結合が必要ですが、 「テーブルを開く」では結合はサポートされていないため、SQL を自分で記述する必要があります。
- テーブルを開くには、WHERE 句を作成するための数式エディターが用意されていますが、たとえば、フィルター処理に役立つカテゴリ変数の個別の値が表示されるわけではありません。
- テーブルを開く では、生成される JSL スクリプトの接続文字列にパスワードが保存されるため、クエリを他のユーザーと安全に共有することが困難になる場合があります。
- テーブルを開くは、データのクリーンアップ、モデリング タイプ、フォーマットなど、データのインポート後に必要な後処理手順についてはサポートしていません。
クエリ ビルダーは、テーブルを開くの制限に対処するためにゼロから設計されており、プロンプトによるインタラクティブなフィルタリングを使用して、同僚と安全に共有できる複数テーブルのクエリを構築するのに役立ちます。
このホワイト ペーパーでは、クエリ ビルダーを、無料で入手できるデルストア2というPostgreSQLサンプル データベースとともに使用します。PostgreSQLは、ここから無料でダウンロードできるオープン ソース データベースです。
http://www.postgresql.org/download/
Dellstore2サンプル データベースは次の場所にあります:
http://wiki.postgresql.org/wiki/Sample_Databases
Dellstore2サンプル データベースは、他のデータベース プラットフォーム (MySQL、Oracle、SQL Server) でも使用できます。
セクション 1: 接続を確立する
Initiate クエリビルダー using File > Database > Query Builder from the menu:
JMPスターターの最初のページから起動することもできます。
Lastly, on Windows, you can also find a クエリビルダー tool on the ホームウィンドウ toolbar:
After launching クエリビルダー, you will be greeted with データベース接続を選択 dialog box:
The データベース接続を選択 dialog box is a subset of the テーブルを開く window. クエリビルダー and テーブルを開く use the same ODBC connections and in fact share connections, so any connections you make in either place will show up in both.
既存の DSN を使用してデータベースに接続するか、新しい DSN を作成するには、[新しい接続...]をクリックします。必要な資格情報を入力するよう求められます。
接続後、JMP は接続からスキーマを取得し、これが必要な接続であることを確認できます。
選択したスキーマで使用可能なテーブルも表示できます。 [次へ]をクリックして次の手順に進みます。
JMP 12.2の新機能: スキーマ - テーブルパネルの 2 つの検索ボックスに注目してください。これらは JMP 12.1 アップデートの新機能で、スキーマとテーブルのリストに入力しながらフィルタリングします。多くのユーザーが何百ものスキーマと、特定のスキーマ内の何百ものテーブルを持っていることは承知していますが、新しい検索ボックスを使用すると、リストを絞り込んで探しているものを簡単に見つけることができます。
セクション 2: クエリのテーブルの選択
図 1: クエリのテーブルの選択
をクリックすると、 [テーブルの選択]ダイアログ ボックスが表示されます。 [テーブルの選択]ダイアログで、クエリに含めるテーブルを選択します (上の図 1 を参照)。クエリに含めるテーブルを [使用可能なテーブル]リストから選択し、右上の[プライマリテーブル] および[セカンダリテーブル] リストに追加します。
- クエリ内のプライマリテーブルは通常、他のテーブルに一致する値があるかどうかに関係なく、すべての行を取得するテーブルです。これは、データ ウェアハウスの用語ではファクト テーブルです。クエリにはプライマリ テーブルが 1 つだけ存在できます。
- セカンダリテーブルは通常、ルックアップ テーブルです。デフォルトでは、プライマリテーブルの列の値がセカンダリ テーブルの指定された列の値と一致する場合にのみ、セカンダリテーブルの行がクエリに含まれます。これらは、データ ウェアハウス用語ではディメンション テーブルです。クエリには、必要な数のセカンダリテーブルを追加できます。
右下のパネルには、選択したテーブルに関する情報が表示されます。 [列]タブには、テーブル内の各列に関する情報 (何らかのキーであるかどうかなど) が表示されます。
JMP 12.2の新機能:Note the 検索ボックス in the 利用可能なテーブル panel, which is new in JMP 12.1. Use it to find the table you are looking for when your schemas contain a lot of tables.
テーブル スナップショットパネルにはデータのスナップショットが表示されるので、テーブルに必要な情報が含まれているかどうかを確認できます。
通常、クエリを作成するときは、答えたい質問を念頭に置いて、それらの質問に基づいて、クエリに含める必要があるテーブルを決定します。ここで調査するデータは、架空の DVD レンタル会社のものです。答えたい質問は次のとおりです。
- どの映画のジャンルが最も収益をもたらしていますか?
- 最も多くの収益をもたらしている俳優は誰ですか?
レンタルテーブルには、この会社の存続期間中の各 DVD レンタル イベントの記録があるため、このテーブルがこのクエリのプライマリ テーブルになります。コラムパネルから、レンタルには在庫テーブルと顧客テーブルへの外部キー関係を持つ列があることがわかります。
図2: 外部キー
外部キー関係 (外部キー制約とも呼ばれる) とは、あるテーブルの列の値が別のテーブルの主キー列に存在しなければならないことを意味します。図 2から、 レンタルテーブルの在庫ID列の値は在庫テーブルの主キー列に存在する必要があり、 レンタルテーブルの顧客ID列の値は顧客テーブルの主キー列に存在する必要があり、rentals テーブルのスタッフID列の値はスタッフテーブルの主キー列に存在する必要があることがわかります。このクエリでは、レンタルに関与したスタッフの顧客ではなく、レンタルした映画に関する詳細情報を探しているため、在庫テーブルをセカンダリ テーブルとしてクエリに追加します。
在庫テーブルのコラムパネルを見ると、 膜テーブルとの外部キー関係がさらに確認できます。
図3: 在庫表の詳細
したがって、膜テーブルもクエリに含まれます。膜テーブルの詳細を見ると、 レンタル料金列があります。これは収益を計算するために必要なものですが、映画のカテゴリや映画の俳優に関する情報は何も見つかりません。
図4: フィルムテーブルの列
しかし、利用可能なテーブルリストを見ると、 映画俳優テーブルと映画カテゴリーテーブルがあり、映画の ID をその俳優とカテゴリにマッピングしているように見えます。また、俳優とカテゴリに関する詳細情報を持つ俳優テーブルとカテゴリテーブルもあります。そのため、クエリにこれらのテーブルも含める必要があります。ただし、これらの 4 つのテーブルを単純に追加すると、何かがおかしくなるようです。
図5: 自動参加が失敗しました
中央に「X」がある「停止標識」アイコンは、JMP が 2 つのテーブルを自動的に結合できなかったことを示しているため、結合を手動で設定する必要があります。JMP の自動結合機能が必要な動作を頻繁に実行しない場合は、 [クエリーのテーブルを選択] の横にある赤い三角形からオフにすることができます。
パブリック.映画俳優 (t14)を選択し、 [結合の編集]ボタン ( ) をクリックすると、 [条件の編集]ダイアログ ボックスが表示され、結合を構成できるようになります。最初、ダイアログ ボックスは次のようになります。
図6: 結合条件の編集
結合条件がまだ存在しないため、 映画俳優.映画IDを在庫.フィルムIDに結合します。これを実現するには、左列リストから在庫テーブルを選択し、両方のリストで映画IDを選択します。
図7: 結合条件の編集
同様に、 公開映画カテゴリの結合条件をinventory.film_id = フィルムカテゴリ.film_idに設定できます。そうすると、見た目が良くなります。
図8: テーブル間の結合は有効です
すべてのセカンダリ テーブルに、左外部結合のベン図アイコンが表示されています。映画俳優と在庫の間で行った結合を例にすると、左外部結合とは、映画俳優テーブルに一致するかどうかに関係なく、在庫テーブル (この場合は左のテーブル) のすべての行がクエリに含まれることを意味しますが、在庫テーブルの行と一致するfilm_actorテーブルの行のみが含まれます。
ちなみに、テーブル名の後の括弧内のt1 、 t2 、 t3などは、クエリーにテーブルを追加したときに JMP がテーブルに割り当てるテーブル別名です。テーブル別名を使用すると、正しい SQL を簡単に生成でき、生成された SQL が読みやすくなります。テーブルのテーブル別名を変更するには、主テーブルまたは二次テーブルのリストでテーブルを右クリックし、 [別名の変更]を選択します。テーブル別名には、スペースや特殊文字を含めることはできません。
左外部結合以外の結合が必要な場合は、 [参加]ダイアログのチェックボックスを使用して結合タイプを変更できます。結合タイプとベン図アイコンは、チェックボックスの状態を反映して変化します。
図9: 結合タイプの変更
結合が正しく設定されているかどうかを確認するには、 [結合のプレビュー]ボタンをクリックします。これにより、クエリが実行され、最初の数百行のデータのスナップショットが表示されるので、正しく設定されているかどうかを確認できます。
クエリに必要なテーブルがすべて揃ったので、 [クエリの作成] をクリックしてクエリ ビルダー自体を起動し、クエリに含める列、使用するフィルター条件、結果のデータの順序などを指定できます。
セクション 3: クエリの構築
When the クエリビルダー window comes up initially for this query, it looks like this:
- クエリ名ボックスにはクエリの名前が入ります。デフォルトではプライマリ テーブルの名前になります。クエリ名は、クエリの実行結果である JMP データ テーブルに割り当てられる名前で、クエリをファイルに保存するときのデフォルトのファイル名でもあります。データ ソースには、クエリが使用しているデータ ソースの名前が表示されます。
- テーブルボックスには、現在クエリに含まれているテーブルが表示されます。テーブルを追加または削除したり、結合を変更したりするには、[変更...]ボタンをクリックします。
- [使用可能な列] リストには、クエリ内のすべてのテーブルのすべての列が表示されます。このリストから列を選択し、 [含まれる列] 、 [フィルター] 、または[並べ替え] パネルにドラッグします。または、必要に応じて、右クリック オプションを使用して同じ操作を実行することもできます。
JMP 12.1 の新機能:長いリストで特定の列を検索する必要がある場合に備えて、[使用可能な列] パネルに検索ボックスが追加されました。他の検索ボックスと同様に、入力時にフィルター処理されます。 - 「含まれる列」パネルには、クエリに含まれるすべての列のグリッドが表示されます。 「含まれる列」グリッドを使用して、JMP にインポートされた後の列のデータの形式やモデリングの種類などを設定します。また、JMP にインポートする前にデータを要約したい場合は、列の「グループ化」とともに集計を指定することもできます。
このパネルには、サンプリングをサポートするデータベースの場合、サーバー上でデータのランダム サンプルを生成するための[サンプル]タブもあります。 - この領域の[クエリ プレビュー]タブは自動的に更新され、クエリを実行したときに返されるデータのスナップショットが表示されます。 [ SQL]タブには、現在の選択に基づいてクエリビルダーが生成している SQL が表示されます。 [クエリ後スクリプト]タブでは、クエリの完了後に実行される JSL スクリプトを指定できます。 [クエリ ステータス]タブには、バックグラウンドで実行されているクエリのステータスが表示され、クエリをキャンセルできます。
- フィルターパネルを使用すると、データをフィルター処理するための条件を指定し (WHERE 句を作成)、フィルターをクエリを実行するたびに実行されるプロンプトに変換できます。
- 並び替えパネルでは、返される行を並べ替えるために使用する列を指定できます (ORDER BY 句)。
列の追加
すべてのクエリにはいくつかの列が必要です。次のいずれかの方法を使用して、クエリに列を追加します。
- 「すべて追加」ボタンをクリックすると、クエリ内のすべてのテーブルから利用可能なすべての列が追加されます。
- Select specific columns from the 利用可能な列 list and click 追加 or drag them over.
- Double-click on a column in the 利用可能な列 list.
For this query, I will add 映画ID, タイトル, and レンタル料金 from the 膜 table, カテゴリID and 名前 from the カテゴリ table, and 俳優ID, ファーストネーム, and 苗字 from the 俳優 table.
図10: 最初に列を追加した後
含まれる列のJMP 名、モデリング タイプ、および形式列を使用すると、データが JMP にインポートされた後の各列の名前、モデリング タイプ、および形式を制御できます。
図11: 列の詳細を調整した後
For the purposes of this analysis, I would also prefer that the actor names were concatenated in a single column rather than split into separate ファーストネーム and 苗字 columns. To achieve that, I can add a 計算列 by selecting 計算列の追加 from the red triangle menu on the 利用可能な列 panel.
This will add a column named 計算1 to the 利用可能な列 list, but I can right-click it and use 列名の変更 to rename it to 俳優名, add it to the query and remove ファーストネーム and 苗字. Here is how the preview of the query looks after doing so:
図12: 計算列を使用したクエリプレビュー
(これらの俳優名の値は少々奇妙に見えるかもしれませんが、実際にはデータベースからの正しい名前です。俳優名は、名と姓をランダムに組み合わせて生成されたようです。)
クエリを保存する
この時点で、このクエリに十分な時間を費やしたので、作業内容を失わないように保存したいと思います。クエリを保存するには、クエリビルダーウィンドウの右下にある [保存]ボタンをクリックするだけです。ファイル名を入力するように求められます。クエリ名ボックスに入力されたテキストがデフォルトで使用されます。クエリに映画カテゴリー俳優という名前を付けます。クエリビルダーウィンドウの左下に、クエリが保存されたことを確認するメッセージが表示されます。
Saving a query creates a file with a .jmpクエリ extension. Back on the クエリビルダー window, you’ll notice that the クエリ名 has been changed to match the file name, so it is now 映画カテゴリー俳優.
開いているクエリに類似した別のクエリを作成する必要があり、元のクエリを保存したい場合は、[名前を付けて保存]ボタンを使用して、別の名前でコピーを保存できます。
クエリの実行
この時点でクエリを実行して、データベースのすべての行を含む JMP データ テーブルを作成することもできます。クエリビルダー ウィンドウの下部にある [クエリの実行] ボタンをクリックして、クエリを実行します。デフォルトでは、JMP はバックグラウンドでクエリを実行するため、クエリの実行中でも作業を続けることができます。このクエリ ビルダーウィンドウからまだ処理中のクエリのステータスを確認するには、ウィンドウの中央下部にある [クエリ ステータス]タブを使用するか、 [表示] > [実行中のクエリ]を使用して、現在バックグラウンドで実行中のすべてのクエリのステータスを確認します。この特定のクエリは、わずか 1 ～ 2 秒で 88,020 行を返します。
図13: JMPデータテーブルでのクエリ結果
結果の JMP データ テーブルには 3 つのスクリプトと構文テーブル変数が定義されていることに注意してください。
質問に答える
この時点で、私が求めていた最初の質問にほぼ答えられるようになりましたが、決定する必要があります。データベースに結果を要約させるか、生データを JMP に取り込んで JMP で処理させるかです。生データがメモリに収まらないほど大きくなく、インポートに長時間かからない場合は、生データを JMP にインポートしてそこで分析を行うと多くの利点があります。JMP はリレーショナル データベースよりもはるかに優れたデータ分析能力を持っています。
手元のタスクでは、 カテゴリ名と俳優名に分布プラットフォームを使用し、 Rental Rate を頻度変数として使用するだけです。結果のヒストグラムを昇順でカウントで並べ替えると、両方の質問に答える結果が得られます。
図14: クエリ結果での配布プラットフォームの使用
ただし、すべての生データを取り込むとサイズや経過時間が制限される場合は、代わりに包含列パネルの集計列を使用して、データベースでデータを集計することができます。ただし、そのためには、2 つの質問に 1 つずつ答える必要があります。
To answer the question of どの映画のジャンルが最も収益をもたらしていますか?, I can remove the columns having to do with actor information from the query and ask the database to sum レンタル料金 grouping by カテゴリ名, and then I can order the result by the summed レンタル料金. Here are the steps:
- Click the 集約 column for レンタル料金 and select 和.
Notice that グループ化 becomes checked for all variables that do not have a specified aggregation. Once any column in a query specifies an aggregation, all columns in the query that do ない specify an aggregation are required to be グループ化 columns. - Right-click on the 変数名 column for レンタル料金 and select 並び替え.
- 並び替えパネルでSUM(t4.レンタル料率)を選択し、下向き矢印ボタン（降順）をクリックします。
結果は次のようになります。
Similarly, taking the category columns back out and putting the actor columns back in, I can answer the question of 最も多くの収益をもたらしている俳優は誰ですか?:
フィルタリング
クエリがすべての生データを取得し、 profileplatformを使用して収益に関する質問に答えていた状態に戻りましょう。JMP で分析を行う際の 1 つのオプションは、JMP 分析用のスクリプトをリクエストできることです。
I can then paste that script into the クエリ後スクリプト panel of クエリビルダー:
図15: クエリ後のJSLスクリプトの追加
このスクリプトは、クエリを実行するたびに実行されるようになりました。
さて、質問をもう少し絞り込みたいと思います。データベース内のすべてのレンタルのカテゴリと俳優の収益を見るのではなく、映画がレンタルされた国、レンタルされた店舗、または特定の日付範囲に基づいて見たいと思うかもしれません。
The レンタル table has レンタル日, and 顧客ID fields, so I’m close to having what I need. We saw earlier that 顧客ID is a foreign key reference to the お客様 table. From the お客様 table, we can get the country that the customer is from and the store from which the DVD was rented.
クエリ ビルダーのテーブルパネルで[変化...]ボタンをクリックすると、戻ってクエリにテーブルを追加できます。国情報を取得するには、顧客、住所、都市、および国のテーブルをクエリに追加する必要があります。幸い、このデータベースの管理者が設定したキー リレーションシップにより、すべてのテーブルが正常に自動結合されます。
必要な追加テーブルを追加した後、クエリ ビルダーに戻り、クエリに必要な列(レンタル日、 ストアID 、国)を追加できます。また、わかりやすい JMP 名を設定し、 ストアIDのモデリング タイプを修正しました。
図16: レンタル日、店舗ID、国列を追加した後
First, let’s filter on the customer’s country. I can right-click on the t7.国 variable in the 含まれる列 panel and select フィルター条件. After doing so, a filter for country appears in the フィルター panel:
図17: 国フィルター
この時点で、国リストを下にスクロールして興味のある国をチェックし、クエリを実行すると、それらの国の行だけが含まれるようになります。
Similarly, I can add filters for ストアID and レンタル日, after which the Filters panel looks like this:
レンタル日フィルターを追加すると、連続変数なので、デフォルトで単純比較フィルターが表示されます。代わりに範囲フィルターを使用します。t1.rental_dateの横にある赤い三角のメニューを使用して、フィルター タイプを範囲に変更できます。
Now the filter for t1.レンタル日 looks like:
日付範囲を指定できるようになりました。したがって、2005 年後半のすべてのレンタルを検索する場合は、次のようにします。
図18: 日付範囲フィルター
クエリ ビルダーのすべてのフィルター タイプには、効果的に無効にする方法があり、特定のクエリですべてのフィルターがアクティブではない場合でも、複数のフィルターをクエリに追加できます。値のリストを表示するカテゴリ フィルターの場合、上部の(すべて)項目を選択すると、フィルターが無効になります。数値を要求するフィルターの場合、値を欠落（。）に設定すると、フィルター (またはフィルターの一部) が無効になります。したがって、2006 年 1 月 1 日以降のすべてのレンタルが必要な場合は、次のようにします。
Now the フィルター panel for my query looks like 図19 below.
| 図 19: Missing を使用してフィルターを非アクティブ化する | 図20: 2つのORグループを持つフィルター |
図19に示すフィルター選択を使用してクエリを実行すると、顧客の国がフランスまたはドイツで、DVD が 2006 年 1 月 1 日以降に店舗 2 でレンタルされた行が返されます。デフォルトでは、複数のフィルターは AND で結合されるため、行が結果に表示されるにはすべてのフィルターに一致する必要があります。OR フィルターが必要な場合は、 [OR]ボタンをクリックします。これにより、別のフィルター グループが作成され、2 つのフィルター グループが OR で結合されます。たとえば、上の図 20に示すフィルターは、カテゴリがドラマであるか、映画のタイトルに「Bucket」が含まれるレンタルを返します。
プロンプトフィルター
クエリ ビルダーでフィルター条件を指定できるのは便利ですが、クエリ ビルダーのユーザー インターフェイス全体を表示する必要のない他のユーザーが使用するクエリを作成し、そのユーザーがクエリを実行するたびに異なるフィルター条件を入力できるようにしたい場合もあります。これを実現する最も簡単な方法は、[フィルター] の横にある赤い三角形のメニューから[実行時にすべてプロンプト]を選択することです。
これにより、クエリ内のすべてのフィルターがプロンプトフィルターになります。一部のフィルターのみをプロンプトにし、他のフィルターはプロンプトにしない場合は、赤い三角形のメニューを使用して、フィルターごとにプロンプトのオン/オフを切り替えることができます。
実行時にすべてプロンプトを設定した後、このクエリを実行すると、フィルター値を希望どおりに設定するプロンプトが表示されます。
プロンプトのデフォルト設定は、クエリが実行される前にクエリビルダーで選択されたもの (または、クエリがファイルから直接実行される場合は保存されたクエリ ファイルで選択されたもの) になります。
プロンプトで使用するラベルをカスタマイズすることもできます。たとえば、プロンプトを「国: 」ではなく「顧客の国: 」にしたいとします。クエリ ビルダーに戻り、 t7.国フィルターの赤い三角形のメニューを使用して、 [プロンプトの構成]を選択します。これにより、プロンプトのラベルを変更したり、プロンプトのカテゴリ レベルを提供するテーブルを変更したりできるダイアログ ボックスが表示されます。
サンプリング
データベースがランダム サンプリングをサポートしている場合、クエリ ビルダーの上部中央のセクションにあるサンプルタブに、データベースがサポートするサンプリング オプションが表示されます。このホワイト ペーパーの例では、指定した行数の単純なランダム サンプリングをサポートする PostgreSQL を使用しています。
SQL Server と Oracle は異なるサンプリング オプションをサポートしているため、サンプルタブには異なるオプションが反映されます。
| 図 21: SQL Server のサンプリング オプション | 図22: Oracleサンプリングオプション |
データが JMP にダウンロードされる前に、データベースによってサンプリングが実行されるため、これは、大量の行が取得されることになるクエリから取得される行数を制限するのに適した方法です。
独自のSQLを書く
クエリ ビルダーには現在または将来的に多くの機能がありますが、クエリ ビルダーが直接サポートしていないクエリが必要になる場合があります。また、JMP で実行する必要があるクエリを正確に実行する SQL をすでに作成し、苦労してデバッグしている場合もあります。クエリ ビルダーには、クエリに独自の SQL を提供する 2 つのオプションがあります。
- カスタム フィルター式–クエリ ビルダーでテーブルの選択と含める列の指定まで必要な処理が実行されているが、フィルターの一部としてカスタム SQL 式を含めたい場合は、フィルターの赤い三角形のメニューから[カスタム式の追加]を選択できます。
| 図23: カスタムフィルタ式の追加 | 図24: 結果のフィルタ |
クエリに対して有効な SQL であれば、カスタム式ボックスに任意の SQL を入力します。
カスタム式フィルターがプロンプト フィルターとして設定されている場合、カスタム式はフィルター プロンプト ダイアログに表示され、編集可能になります。
- Convert the entire query to a カスタムSQLクエリ – If you just want to write your own SQL for an entire query, then, after launching クエリビルダー and specifying the data connection, you can use the red triangle menu at the top left of クエリビルダー next to the クエリ名 field and select カスタムSQLに変換する:
図25: カスタムSQLへの変換
クエリビルダーウィンドウがカスタム SQL モードに切り替わり、独自の SQL を入力するための広い領域が提供されます。クエリの構築を対話的に開始した場合、カスタム SQLボックスは、これまでに行った選択に基づいて生成された SQL で初期化されます (下の図 26 を参照)。
図26: カスタムSQLクエリ
You can edit the SQL as needed, or paste in SQL from another source, and then you can click the アップデート button to update the クエリのプレビュー. Some caveats:
- カスタム SQL クエリを使用してプロンプト フィルタリングを実行することはできません。
- クエリをカスタム SQL に変換して SQL を編集した後、クエリをインタラクティブに戻すと、SQL に対して行ったカスタマイズはすべて失われます。クエリ ビルダーは SQL を解釈しません。
セクション 4: バックグラウンド クエリの管理
クエリ ビルダーは、デフォルトでバックグラウンドでクエリを実行しますが、JMP スクリプト言語 (JSL) からバックグラウンドでクエリを実行することもできます (セクション 6 を参照)。JMP には、現在実行中のバックグラウンド クエリのリストを表示し、必要に応じて実行中のクエリを停止する 2 つの方法が用意されています。
クエリステータスパネル
クエリ ビルダーの下部中央のセクションには、クエリ ステータスタブがあります。クエリ ビルダー ウィンドウから起動されたバックグラウンド クエリはすべて、このタブに表示されます。
実行中のクエリを停止する場合は、リストでクエリを選択して[停止]ボタンをクリックするだけです。一部の行が取得されている場合、JMP は取得済みの行を含むデータ テーブルを開きます。データ テーブルの名前には、結果が部分的であることが反映されます。
クエリ実行ウィンドウ
ホーム ウィンドウまたはメニューを含む他の JMP ウィンドウの[表示]メニューから、 [実行中のクエリ]を選択します。これにより、クエリ ビルダーの [クエリステータス]タブと同じウィンドウが開きますが、 [実行中のクエリ]ウィンドウには、呼び出し方法 (クエリ ビルダー ウィンドウまたは JMPスクリプト言語) に関係なく、現在バックグラウンドで実行中のすべてのクエリが含まれます。
ここでも、クエリを選択して「停止」をクリックすると、クエリの実行が停止され、部分的な結果が開きます。
セクション 5: クエリの共有
In the introduction, it was mentioned that テーブルを開く created scripts that were difficult to share, because the database password is included in the connection string. クエリビルダー works differently. If you save a .jmpクエリ file for a query whose connection required a password, and you open that file in a text editor, you will see something like this:
ユーザー名 ( ユーザID ) が接続文字列に存在しているのがわかりますが、 PWD=%_PWD_%があります。 “%_PWD_%” はパスワードではなく、クエリ ビルダーによって配置されたプレースホルダーです。クエリ ビルダーがそのプレースホルダーを見ると、元々パスワードが必要であったことがわかります。そのため、このクエリが開かれたときに、クエリを開いているユーザーがまだPostgres dvdレンタル DSN に接続していない場合、クエリ ビルダーはユーザーにパスワードの入力を求めることを認識します。
DSN 自体はどうでしょうか。この .jmpquery ファイルを、マシンにPostgres dvdrentalという DSN が定義されていない JMP ユーザーに送るとどうなるでしょうか。このようなシナリオではテーブルを開くは続行できませんが、クエリ ビルダーはユーザーにこの状況を通知し、別の DSN (おそらく同じデータベースに接続する) に接続するか、新しい DSN を作成する機会を提供します。
異なるユーザーがマシン上で異なる DSN を定義する可能性があるため、一部の組織ではDSN なしの接続に移行しています。クエリビルダーは現在、DSN ベースの接続文字列を DSN なしの接続文字列に変換できませんが、クエリのスクリプトで接続文字列を変更して、 DSSNを指定する代わりにドライバとサーバを指定すると、クエリビルダーは生成するすべてのスクリプトまたは .jmpquery ファイルにこの DSN なしの接続を伝播します。
セクション 6: JMP スクリプト言語 (JSL) からのクエリの実行
クエリ ビルダーで作成されたクエリは、JMP スクリプト言語 (JSL)スクリプトから実行することもできます。最適なオプションは、クエリを.jmp クエリファイルとして保存し、JSL から開いて実行することです。たとえば、 c:\users\public\tempフォルダにpg_rentals.jmpクエリという名前のクエリが保存されているとします。JSL からそのクエリ ファイルを使用して実行できる操作をいくつか示します。
- クエリ ビルダーで .jmpquery ファイルを開きます。
開ける( 「c:\users\public\temp\pg_rentals.jmpquery」 );
クエリ= 開ける( 「c:\users\public\temp\pg_rentals.jmpquery」,プライベート）;
- Open the query in クエリビルダー for editing:
クエリ<< 修正する;
クエリ<< 走る（）;
- クエリをフォアグラウンドで実行します。クエリが終了すると、結果のデータ テーブルが JMP で開きます。dtResult は結果データ テーブルへの参照になります。
dt結果=クエリ<< フォアグラウンドで実行（）;
- クエリをフォアグラウンドで実行しますが、結果のデータ テーブルを JMP で開かず、非公開にしておきます。dtResult は結果データ テーブルへの参照になります。
dt結果=クエリ<< フォアグラウンドで実行(プライベート）;
- クエリをバックグラウンドで実行します。クエリが終了すると、結果のデータ テーブルが JMP で開きます。クエリは単にバックグラウンドで起動されるため、バックグラウンドで実行しても結果のデータ テーブルへの参照を返すことはできません。この後の例では、クエリをバックグラウンドで実行するときに結果のデータ テーブルへの参照を取得する方法を示します。
クエリ<< バックグラウンドを実行（）;
- クエリをバックグラウンドで実行します。クエリが成功した場合は、結果データ テーブルで Distribution プラットフォームを呼び出します ( queryResult は、結果データ テーブルを参照する実行完了時スクリプト内の特別な JSL 変数です)。クエリがユーザーによってキャンセルされた場合、またはエラーが発生した場合は、JMP ログにメッセージを書き込みます。
クエリ<< バックグラウンドを実行(
実行完了時（
クエリ結果<< 分布( 名目分布( カラム( :タイトル） ） ）;
),
実行がキャンセルされた場合（書く( "\!Nクエリはキャンセルされました!" ) ),
エラー時（書く( "\!Nクエリの実行中にエラーが発生しました!" ); )
);
- クエリをバックグラウンドで実行します。クエリが正常に完了したら、MyFunc 関数を呼び出します。クエリ結果データ テーブルは常に関数の最初のパラメーターとして渡されます。
::マイファンク= 関数({ dt },{デフォルト ローカル},
書く( "\!nレンタル数: ", NRows(dt ) );
dt<< 分布( 名目分布( カラム( :タイトル） ） ）;
);
クエリ<< バックグラウンドを実行(実行完了時（::マイファンク) );
JSL スクリプトで別の .jmpquery ファイルからクエリを開くのが不十分な場合は、クエリ ビルダーウィンドウの左上にある赤い三角形のメニューから、クエリ スクリプト可能オブジェクトを作成するための生のスクリプトを取得できます。
たとえば、単純なクエリに対して「実行スクリプトをスクリプト ウィンドウに保存」を呼び出すと、次の結果が表示されます。
新しい SQL クエリは、.jmpquery ファイルを開いたときに開けるが行うのと同じように、SQL クエリのスクリプト可能なオブジェクトを返し、すべて同じ方法で使用できます。
JMP 12.1 の新機能は何ですか?
検索
私たちが話を聞いた多くの人は、データベース内に何百ものスキーマ、テーブル、列があり、膨大なデータを持っています。クエリ ビルダーの JMP 12.1 バージョンでは、スキーマ、テーブル、列のリストに検索ボックスが追加され、探しているものをすばやく見つけられるようになりました。
これらの検索ボックスはすべて入力すると更新され、正規表現を使用して検索を絞り込むこともできます。
関連テーブルを追加する
In the クエリのテーブルを選択 dialog box, if a table that you have added to the query has foreign key references to other tables, you can add all of those related tables to the query by clicking the 関連テーブルを追加する button.
テンプレートとして開く
以前に作成して .jmpquery ファイルに保存した別のクエリと非常によく似たクエリを作成する必要がよくあります。以前保存したクエリをいつでも開いて変更し、[名前を付けて保存]を使用して別の .jmpquery ファイルに保存できますが、誤って[保存]をクリックすると、元のクエリが上書きされます。 [テンプレートとして開く]機能は、そのようなシナリオを防ぐために用意されています。 [テンプレートとして開く] は、 Mac と Windows の両方で、.jmpquery ファイルのホーム ウィンドウ コンテキスト メニューから使用できます。
.jmpquery をテンプレートとして開くと、実際には、ディスク上の .jmpquery ファイルとは関連付けられていない元のクエリのコピーが開かれます。クエリをテンプレートとして開き、変更を加えた後、誤って[保存]ボタンを押した場合は、クエリを保存するファイルを指定するように求められます。
JMP スクリプト言語 (JSL) から .jmpquery ファイルをテンプレートとして開くこともできます。
開ける( 「C:\Users\Public\Temp\rentals.jmpquery」,テンプレートとして);
計算列の数式を編集する
JMP 12.0 では、データベース サーバーで計算される計算列をクエリに追加し、結果の値をクエリで返すことができました。ただし、以前に作成した計算列の式を編集する必要がある場合、編集することはできませんでした。代わりに、計算列を削除して新しい列を追加する必要があり、複雑な式を再作成する必要がある場合がありました。JMP 12.1 では、計算列を編集するオプションが追加されました。[使用可能な列]で列を右クリックし、 [式の編集]を選択するだけです。
バグ修正
ご想像のとおり、JMP 12.1 のクエリ ビルダーでは、お客様から報告されたバグも含め、多数のバグが修正されています。クエリ ビルダーの使用中に問題が発生した場合は、SAS テクニカル サポートに問題を報告していただき、対処してください。
結論
クエリビルダーは、JMP 12（JMP 12.1で更新）の新機能で、リレーショナルデータベースに対して高度にカスタマイズされたマルチテーブルSQLクエリを作成するのに役立ちます。目標は、データを分析可能な形式でJMPにすばやく取り込むことと、クエリを安全に共有することを容易にすることです。ぜひお試しいただき、フィードバックをお寄せください（eric.hill@jmp.com) 何がうまく機能し、何がうまく機能しないかを検討します。
