SQLクエリーの作成
データベーステーブルを選択したら、データを読み込むか、クエリーを作成します。クエリービルダーでは、SQL式を書かずに、対話式にデータベースクエリーを作成することができます。
データベーステーブルを選択（そして必要に応じて結合）したら、［クエリーの作成］をクリックしてクエリービルダーウィンドウを開きます。含める列を選択し、標本抽出の基準やフィルタを指定して、クエリーを作成します。また、クエリーを保存し、後で編集、実行することもできます。
「選択可能な列」リストにデータベーステーブルの列がすべて一覧されます。t1やt2といった接頭部（エイリアスともいう）が、各列を対応するデータベーステーブルに関連付けます。
クエリービルダーの手順を省き、すべてのデータを読み込む場合は、［すぐに読み込む］をクリックします。
注: ［テーブル］メニューの［JMPクエリービルダー］にも同じオプションが多くありますが、JMPデータテーブルを照会および結合できるという点で異なります。［JMPクエリービルダー］を使用したデータテーブルの照会と結合を参照してください。
データベーステーブルから列を選択する
映画のレンタル回数を、ジャンル別、評価別、そして未婚／既婚や年齢といった人口統計データ別に調べるとしましょう。
注: クエリービルダーの例で使用しているデータベースは、JMPと一緒にインストールされるサンプルには含まれていません。
1. ［ファイル］>［新規作成］>［データベースクエリー］を選択し、データベースに接続し、「SQBTest」というスキーマを選択します。データベースへの接続を参照してください。
2. 「クエリーのテーブルを選択」ウィンドウで、「g6_Customers」を選択し、［主］をクリックします。
3. 「g6_Movies」と「g6_Rentals」を選択し、［副］をクリックします。
4. ［クエリーの作成］をクリックし、クエリービルダーウィンドウを開きます。
5. 「選択可能な列」ボックスで、「t1.Gender」、「t1.Age」、「t1.Married」、「t1.KidsUnder12」、「t2.Rating」、「t2.Genre」を選択します。
6. ［含める列］タブで［追加］をクリックします。
図3.38 選択されている列
注:
– クエリーに8個以上のテーブルが含まれている場合、テーブル名の上の左上角のあたりに検索ボックスが表示されます。長いリストの中から列を見つけるには、「選択可能な列」の下にある検索ボックスにその名前を入力すると便利です。赤い三角ボタンをクリックすると、大文字／小文字を区別するオプションや、正規表現を使って検索するオプションが表示されます。検索ボックスを非表示にするには、「選択可能な列」の赤い三角ボタンをクリックし、表示されるメニューで［検索ボックスの表示］の選択を解除します。
– 参照列は、クエリーに使用できないので「選択可能な列」リストには表示されません。
– 列の幅を変更するには、［含める列］タブの「表示形式」リストからオプションを選択します。
7. 列の下にある［SQL］タブを選択すると、クエリーのSQLステートメントが確認できます。このコードは、クエリーの実行後、データテーブルプロパティとして保存されます。
8. 右下角の［保存］をクリックします。
作業内容は、「g6_Customers.jmpquery」として保存されます。後でこのファイルを開けば、現在の時点に戻ったり、クエリーを実行したりできます。
9. ［クエリーの実行］をクリックし、データを読み込みます。
データテーブルには、次のようなスクリプトが含まれます。
– 「ソース」スクリプトは、データベースに再接続し、データを読み込みます。
– 「クエリーの変更」スクリプトは、クエリーをクエリービルダーで開きます。
– 「データベースから更新」スクリプトは、データを再び読み込み、更新します。
ヒント:
• 列の名前を変更するには、［含める列］タブでJMP名をダブルクリックして新しい名前を入力します。
• エイリアスを変更するには、「クエリーのテーブルを選択」ウィンドウでテーブルを右クリックし、［エイリアスの変更］を選択します。エイリアスには大文字と小文字の区別がありません。
• ［クエリービルダー］の環境設定で、「ODBC」の［可能な限りクエリーをバックグラウンドで実行］をオフにしない限り、クエリーはバックグラウンドで実行されます。ODBCクエリーの進行状況は、［表示］>［実行中のクエリー］で確認することもできます。
注: SASクエリービルダーの場合、クエリーはすべてフォアグラウンドで実行されます。
• プレビューのロードに時間がかかり過ぎる場合は、［プレビューを自動的に更新］をオフにします。データのビューを更新するには、［クエリーのプレビュー］タブの下にある［更新］をクリックします。大規模なデータベースを使用することが多い場合は、「クエリービルダー」の環境設定にあるプレビューオプションの変更を検討してください。また、プレビュー可能な行の最大数を制限することを検討してください。JMPの［クエリービルダー］の環境設定で、［プレビュー表示する最大行数］の値を変更します。
• データベースの重複する行を省略するには、［含める列］タブで［重複していない行のみ］を選択します。
JMP 12との互換性の維持
JMP 13で追加された機能をクエリーに追加した場合、そのクエリーはJMP 12では読み込めなくなります。JMP 13を使用しているが、JMP 12でも実行できるクエリーを作成したい場合は、［クエリービルダー］の環境設定で［デフォルトでJMP 12とのクエリーの互換性を維持する］を選択します。このオプションを選択すると、互換性の問題を生じる機能がクエリービルダーで非表示になります。
クエリーをJMP 12からJMP 13または14に移行する準備ができたら、この環境設定の選択を解除してください。
計算列の作成
既存の列から新しい列を作成できます。たとえば、2つの列の平均を計算し、その平均を新しい列に保存できます。この場合、日時の値には間違った形式が用いられることがあります。「選択可能な列」の赤い三角ボタンをクリックし、［計算列の追加］を選択して、計算式エディタで新しい列を作成します。
レンタル期間内に1本の映画を見ることのできる最大回数を計算したいとします。この場合、各映画の再生時間と貸出日数を含むデータベースを照会します。以下の例は、これらのデータから新しい計算列を作成する方法を示しています。
注: クエリービルダーの例で使用しているデータベースは、JMPと一緒にインストールされるサンプルには含まれていません。
1. ［ファイル］>［新規作成］>［データベースクエリー］を選択し、データベースに接続し、「SQBTest」というスキーマを選択します。データベースへの接続を参照してください。
2. 「クエリーのテーブルを選択」ウィンドウで、「g6_Rentals」を主テーブル、「g6_Movies」を副テーブルとして選択します。
3. ［クエリーの作成］をクリックし、クエリービルダーウィンドウを開きます。
4. 「選択可能な列」の赤い三角ボタンをクリックし、［計算列の追加］を選択します。
すると、計算列ウィンドウが表示されます。このウィンドウにはJMPの計算式エディタが含まれています。
図3.39 計算式エディタを伴う計算列ウィンドウ
注:
– 計算式エディタの左側のリストには演算子と関数があります（図3.39）。データベースに基づいてサーバーのタイプを変更しなければならない場合があります。
– 「演算子」のリストにConcatenate（||）演算子は含まれていません。手動で計算エディタボックスに入力する必要があります。
5. 左側の「g6_Rentals」リストで、「DaysOut」を選択し、乗算
のボタンをクリックします。
図3.40 計算列
6. 空白のボックスを選択し、(24 * 60と入力してEnterキーを押します。
この計算式では、1日の時間数と1時間の分数を掛けています。最初の括弧を入力すると、閉じる括弧は自動的に入力されます。
図3.41 計算式の最初の部分
7. 外側のボックスをクリックして式全体を選択し、除算
のボタンをクリックします。
8. 左側のリストで「g6_Movies」を選択した後、「LengthMins」を選択します。
図3.42 次に表示される計算式
9. ［OK］をクリックします。
「Calc1」という名前の新しい列が作成されます。
10. この列を右クリックし、［列名の変更］を選択します。
11. 「見ることのできる最大回数」と入力し、［OK］をクリックします。
12. 「選択可能な列」リストで「見ることのできる最大回数」を選択し、［追加］をクリックします。
13. 「t2.Name」を選択し、［追加］をクリックします。
［クエリーのプレビュー］タブを見ると、Nanny McPheeはレンタル期間中に最大160回見られることがわかります。
共通する値のグループ化
データをJMPに読み込む前に、列の中で共通している値をまとめる（グループ化する）ことができます。共通する値をグループ化するには、集計関数を選択し、共通する値の計算方法を指定します。
注: 集計がサポートされているかどうかは、データベースによって異なります。詳細については、データベースに関するマニュアルを参照してください。
ある映画が何回レンタルされたかを調べたいとしましょう。この例では、各項目番号の度数が計算され、共通する映画の値が1つの行にグループ化されます。
注: クエリービルダーの例で使用しているデータベースは、JMPと一緒にインストールされるサンプルには含まれていません。
1. ［ファイル］>［新規作成］>［データベースクエリー］を選択し、データベースに接続し、「SQBTest」というスキーマを選択します。データベースへの接続を参照してください。
2. 「クエリーのテーブルを選択」ウィンドウで、「g6_Movies」を主テーブル、「g6_Rentals」を副テーブルとして選択します。
3. ［クエリーの作成］をクリックし、クエリービルダーウィンドウを開きます。
4. 「選択可能な列」ボックスで、「t1.Name」と「t2.ItemNo」を選択し、［追加］をクリックします。
5. 「t2.ItemNo」を選択し、「集計」リストから［度数］を選択します。
「t1.Name」の［グループ別］チェックボックスがオンになっています。個々の映画名のすべてのインスタンスが1つの行にグループ化されます。
図3.43 グループ化した列
6. ［クエリーの実行］をクリックし、データを読み込みます。
7. データテーブルで「度数-ItemNo」列を右クリックし、［並べ替え］>［降順］を選択します。
「Scarface（スカーフェイス）」が最も頻繁にレンタルされたことがわかります。
図3.44 「度数-ItemNo」列を並べ替えたところ（一部分）
ヒント:
• 行のグループ化を解除するには、列の「集計」リストで［なし］を選択します。
• DISTINCT集計関数は、異なる値を含む行だけを表示します。重複した値を含む行は除かれます。これらの関数は、データベースに重複した値が多く含まれている場合に便利です。
データの標本を読み込む
データベースが大きい場合は、データの標本を抽出することができます。標本抽出を行うと、行のサブセットが戻され、クエリーにかかる時間が短くなります。データベースのクエリーを実行すると、［標本］タブで選択したオプションに従ってデータの一部のみが読み込まれます。
標本抽出の方法は、データベースのベンダーによって異なります。
• SQL Serverは、デフォルトでブロック標本抽出をサポートしています。ブロック標本とは、1つのページ全体の行を抽出したものです（たとえば、1ページ目と5ページ目のすべての行）。1000行を指定した場合、ぴったり1000行にはならないことがありますが、ほぼ1000行が読み込まれます。
• Oracleなどのデータベースは、行ごとの標本抽出をサポートしています。5000行を選択した場合、データ内でのサイクル方法によって4800～5200の行が読み込まれます。
JMPは、主要なデータベースベンダーのものについては、その標本抽出機能を検出し、可能な限りそれに合ったオプションを提供します。［標本］タブの機能のうち、ベンダーがサポートしていないものは、無効になります。
データの標本を読み込みたいとしましょう。この例では、ランダムに5000行を選択します。
注: クエリービルダーの例で使用しているデータベースは、JMPと一緒にインストールされるサンプルには含まれていません。
1. ［ファイル］>［新規作成］>［データベースクエリー］を選択し、データベースに接続し、「SQBTest」というスキーマを選択します。データベースへの接続を参照してください。
2. 「クエリーのテーブルを選択」ウィンドウで、「g6_Rentals」を主テーブル、「g6_Movies」を副テーブルとして選択します。
3. ［クエリーの作成］をクリックし、クエリービルダーウィンドウを開きます。
4. ［含める列］タブで［すべて追加］ボタンをクリックします。
5. ［標本］タブをクリックし、［この結果セットから標本抽出］を選択します。
6. ［行数(ランダムに抽出)］を選択し、「5000」と入力します。
［標本の抽出］セクションには、このデータベースがサポートする唯一の抽出方法である［ブロックまたはページ］がオプションとして表示されています。
7. ［クエリーの実行］をクリックし、データを読み込みます。
新しいデータテーブルは、およそ5000の行から成ります。ブロックまたはページの抽出を行うと、標本に含まれる行数が4900だったり、5600だったりします。
ヒント:
• クエリーを実行するたびに同じ標本セットを作成したい場合は、［シード値］として64,000までの正の整数を指定します。性別ごとの映画のレンタル回数をクエリーしたいとしましょう。［シード値］として「1」を入力し、クエリーを実行します。結果の中で、男性客の分布が低くなっています。［シード値］として「2」を入力し、もう一度クエリーを実行します。この手順を繰り返すと、女性と男性が似たような分布になるシード値を見つけられます。
• ［含める列］タブに個々の列を追加するには、列を右クリックし、［クエリーに含める］を選択するか、［追加］ボタンをクリックします。
フィルタを選択してデータのサブセットを読み込む
フィルタを使い、値のサブセットをデータテーブルに読み込むこともできます。クエリーにJMP 12との互換性がある場合は、一部のフィルタが使用できなくなります。JMP 12との互換性の維持を参照してください。
連続尺度の列とカテゴリカルな列の両方に使えるフィルタ
単純な比較
指定した演算子を使用して値を照合します。
Age > 14は、14を超える年齢にマッチします。
範囲
指定した演算子を使用して値の範囲を照合します。
12 ≤ Age ≤ 17は、12～17の年齢にマッチします。
Is NULLまたはIs Not NULL
欠測値にマッチします。
［NULLまたはnot NULLのいずれか］は欠測値と非欠測値にマッチします。
カスタム式
独自のSQL式を書くことができます。
( ( ( t2.Gender IN ( 'F' ) ) AND ( (t2.Age >= 20) AND (t2.Age <= 50) ) ) )
上記の式は性別（Gender）がFで年齢（Age）が20～50の場合にマッチします。
カテゴリカルな列にのみ使えるフィルタ
リストボックス
1つまたは複数の値を選択できるリストボックスを表示します。リストボックスは、［クエリービルダー］の環境設定に基づくカテゴリカルな列のデフォルトのフィルタです。
手動のリスト
列の値を入力できます。
チェックボックスリスト
チェックボックスリストを表示します。
注: リストボックス、手動のリスト、およびチェックボックスリストには［リストにないもの］オプションが含まれており、選択した値にマッチしない行を読み込むことができます。
含む
指定した値を含む、または含まない文字列にマッチします。Contains Comedy OR RomanceはComedyとRomanceにマッチします。
ほとんどの場合、カテゴリを表す列のデフォルトのフィルタはリストボックスです。水準数が1000を超える列の場合は、［含む］フィルタが自動的に選択されます。水準数は［クエリービルダー］の環境設定で変更できます。
LikeまたはNot Like
ワイルドカードを使用したあいまい検索を行います。%ワイルドカード（0個またはそれ以上の文字）および_ワイルドカード（1文字のみ）にマッチします。
Genre Like %comは、“RomCom”など、“com”前に任意の数の文字がある値にマッチします。“Comedy”にもマッチするようにするには、%com%またはContains comを使用します。
列の値でマッチ
指定した列の値にマッチします。テーブルを選択した後、列を選択します。［一致しないものを選択］オプションを使用すると、選択した行を除くすべての行が得られます。例については、既存のデータテーブルから一致するデータを読み込むを参照してください。
次の例では、30歳以上の顧客による、RomComおよびComedyジャンルの映画のレンタルのデータを読み込む方法を紹介しています。
注: クエリービルダーの例で使用しているデータベースは、JMPと一緒にインストールされるサンプルには含まれていません。
1. ［ファイル］>［新規作成］>［データベースクエリー］を選択し、データベースに接続し、「SQBTest」というスキーマを選択します。データベースへの接続を参照してください。
2. 「クエリーのテーブルを選択」ウィンドウの「使用可能なテーブル」リストから「g6_Rentals」を選択し、［主］をクリックします。
3. 「g6_Customers」と「g6_Movies」を選択し、［副］をクリックします。
4. ［クエリーの作成］をクリックし、クエリービルダーウィンドウを開きます。
5. 「選択可能な列」ボックスで、「t2.Gender」、「t2.Age」、「t3.Genre」を選択し、［含める列］タブで［追加］をクリックします。
6. ［含める列］タブですべての列を選択し、［選択した列をフィルタに追加］
をクリックします。
「フィルタ」アウトラインに列のフィルタが表示されます。
7. 「t2.Age」のフィルタを「≥ 30」に設定します。
8. 「t3.Genre」の赤い三角ボタンをクリックし、フィルタの種類として［LikeまたはNot Like］を選択した後、「%com%」を入力し、Enterキーを押します。
「com」の前後のワイルドカード（%）は、任意の数の文字に一致します。［クエリーのプレビュー］タブに、「RomCom」（ロマンティックコメディ）と「Comedy」（コメディ）の2つのジャンルの映画が表示されています。
図3.45 フィルタの選択
9. 「フィルタ」の赤い三角ボタンをクリックし、［実行時にすべての確認メッセージを表示］を選択します。
クエリーを実行するユーザは、フィルタをカスタマイズできるようになります。
10. ［クエリーの実行］をクリックします。
11. 「クエリーの確認」ウィンドウで［OK］をクリックすると、選択済みのフィルタを適用してデータが読み込まれます。
注:
• ほとんどの場合、カテゴリを表す列のデフォルトのフィルタはリストボックスです。水準数が1000を超える列の場合は、［含む］フィルタが自動的に選択されます。水準数は［クエリービルダー］の環境設定で変更できます。
• フィルタの赤い三角ボタンのメニューにある［条件付き］オプションを使用すると、階層的なカテゴリ内でデータをフィルタ処理できます。たとえば、State（州）フィルタとCity（都市）フィルタがあるとします。州を選択した後、その州内の都市だけを表示するには、「City」（都市）の赤い三角ボタンをクリックし、［条件付き］を選択します。
• 「フィルタ」リストの上部にある［逆にする］オプションを使用すると、すべてのフィルタに対して指定した行以外のすべての行を選択できます。このオプションは、すべての行を選択するフィルタに対しては使用できません。
• フィルタリスト内の「< 空白>」は、データベースにその列の欠測値が含まれていることを示します。
• 行数の多いカテゴリカルデータの列に対してフィルタを作成する場合、JMPはテーブル内の行数を判断しようとします。
– ［クエリービルダー］の環境設定にある［サイズが判断できないテーブルのカテゴリの水準を取得する］はデフォルトで選択されているため、JMPは自動的に水準を取得しようとします。このオプションの選択を解除すると、［クエリービルダー］の環境設定の「カテゴリカル列の代替のフィルタタイプ」で指定されている［含む］のフィルタが使用されます。
– カテゴリカルな列に百万を超える行がある場合、JMPはそのフィルタ列から一意の水準値を取得しません。［クエリービルダー］の環境設定の「カテゴリの水準を自動的に取得するテーブルの最大行数」に指定できる値は、最小値である-1（制限なし）から最大値である10億までです。
• ［クエリービルダー］の環境設定で［デフォルトでJMP 12とのクエリーの互換性を維持する］が選択されていない限り、カテゴリカルな列のデフォルトのフィルタはリストボックスです。
既存のデータテーブルから一致するデータを読み込む
開いているデータテーブルから、クエリーに一致する行を選択することもできます。航空会社のデータを集めたデータベースがあるとしましょう。このデータベースには、飛行時間や機体番号などのデータが含まれています。また、機体番号を含んだデータテーブルもあります。［列の値でマッチ］フィルタを使い、機体番号が一致するデータだけを読み込んでみましょう。
注: クエリービルダーの例で使用しているデータベースは、JMPと一緒にインストールされるサンプルには含まれていません。
1. ［ヘルプ］>［サンプルデータライブラリ］を選択し、「Air Traffic.jmp」を開きます。
2. ［ファイル］>［新規作成］>［データベースクエリー］を選択し、データベースに接続し、「SQBTest」というスキーマを選択します。データベースへの接続を参照してください。
3. 「クエリーのテーブルを選択」ウィンドウの「使用可能なテーブル」リストから「g5_AIRLINE_ONTIMEPERF」を選択し、［主］をクリックします。
4. ［クエリーの作成］をクリックし、クエリービルダーウィンドウを開きます。
5. ［含める列］タブで［すべて追加］をクリックします。
6. ［含める列］タブで「t1.TailNum」を選択し、［選択した列をフィルタに追加］をクリックします。
7. 「フィルタ」列にある「t1.TailNum」の赤い三角ボタンをクリックし、［フィルタの種類］を選択して、［列の値でマッチ］を選択します。
8. 「値を比較するテーブル」の下で「Air Traffic」を選択します。
9. 「機体番号」列を選択し、リストから「すべての行 (38,118)」を選択します。
［クエリーのプレビュー］タブでデータのプレビューが更新され、フィルタリング後の値が表示されるようになります。
10. ［クエリーの実行］をクリックし、データを読み込みます。
読み込まれたデータテーブルには、「機体番号」列にある機体番号のデータのみが含まれています。
カスタム式を書いてデータのサブセットを読み込む
データを部分的に抽出するためのフィルタリングには、JMPにもともと用意されているフィルタを使う以外にも、独自のSQL式を書くという方法があります。
注: クエリービルダーの例で使用しているデータベースは、JMPと一緒にインストールされるサンプルには含まれていません。
1. フィルタリングしたい列を選択します（フィルタを選択してデータのサブセットを読み込むを参照）。
2. 「フィルタ」の赤い三角ボタンのメニューから［カスタム式の追加］を選択します。
3. 「カスタム式」ボックスに次のようなテキストを入力します。
( ( ( t2.Gender IN ( 'F' ) ) AND ( (t2.Age >= 20) AND (t2.Age <= 50) ) ) )
4. 「カスタム式」ボックスの外をクリックすると、［クエリーのプレビュー］タブが更新されます。
この式は性別（Gender）がFで年齢（Age）が20～50の場合にマッチします。
図3.46 カスタムのフィルタ式を書く
選択したデータの並べ替え
特定の列の値を基準に行を並べ替え、データテーブルでの表示順序を制御することができます。この例では、「Married」列を降順に並べ替え、データ全体を「age」（年齢）と「height」（身長）で並べ替えます。
注: クエリービルダーの例で使用しているデータベースは、JMPと一緒にインストールされるサンプルには含まれていません。
1. ［ファイル］>［新規作成］>［データベースクエリー］を選択し、データベースに接続し、「SQBTest」というスキーマを選択します。データベースへの接続を参照してください。
2. 「クエリーのテーブルを選択」ウィンドウで、「g4_bigclass」を選択し、［主］をクリックします。
3. ［クエリーの作成］をクリックし、クエリービルダーウィンドウを開きます。
4. ［含める列］タブで［すべて追加］をクリックします。
5. 「t1.age」と「t1.height」を選択し、［選択した項目で並べ替え］
をクリックします。
列が、右側の「Order By」アウトラインに表示されます。
列は、まず「age」（年齢）の若い順、次に「height」（身長）の低い順に並びます。
図3.47 「Order By」列の選択
6. 「Order By」アウトラインで、「t2 .height」を選択し、列の下にある［値の降順に並べる］
をクリックします。
「height」列が、高い順に並べ替えられます。
7. 「t2.height」を選択し、［リスト内で選択した項目を上へ移動］
をクリックします。
まず「height」列により並べ替えられます。「age」列の値が「height」の各水準内で並べ替えられます。「height」の68に対し、「age」が14から17まで順に並びます。
図3.48 列を並べ替えた結果
クエリーのステータスの表示
［クエリーのステータス］タブで、バックグラウンドで実行されているクエリーのステータスを確認できます。クエリー名、SQLステートメント、処理されたレコードの数が表示されます。いつでもクエリーを停止し、処理されたレコードのみを確認することができます。他のJMPウィンドウからバックグラウンドクエリーを確認するには、［表示］>［実行中のクエリー］を選択します。「クエリービルダー」の環境設定で［可能な限りクエリーをバックグラウンドで実行］をオフにした場合、ステータスの詳細は表示されません。
注: クエリービルダーでのSAS接続については、クエリーとクエリーのプレビューがすべてフォアグラウンドで実行されます。
クエリー後のスクリプトを書く
［クエリー後のスクリプト］タブには、クエリーに続けて実行するJSLスクリプトを書けます。たとえば、データを読み込み、分布を作成したいとしましょう。
Distribution( Column( :age, :gender ) );
このスクリプトは、最終的なデータテーブルに含まれる「ソース」スクリプトの一部となります。