SQL（Structured Query Language：構造化照会言語）： リファレンス

ここでは、SQLについて簡単に説明します。クエリーの利便性について紹介するのが目的であり、包括的なリファレンスではありません。

SELECT文の使用

JMPで使用する基本的なSQLステートメントはSELECT文です。SELECT文は、データソースから取得する行をデータベースに伝えます。データベースを照会するためのSQLステートメントを書くで説明した手順を完了すると、次のSQLステートメントを送信したことになります。

SELECT * FROM "Solubility"

*演算子は、「すべての列」の省略形で、指定したデータテーブルからのすべての列を返すようデータベースに要求します。

特定の列のみが必要な場合は、*をデータテーブル内の特定の列名で置き換えることができます。たとえば、例のSolubility（溶解度）データの場合、次の文を実行すると、エーテル（ETHER）、1- オクタノール（OCTANOL）、四塩化炭素（CHLOROFORM）の溶剤だけを選択できます。

SELECT ETHER, OCTANOL, CHLOROFORM FROM "Solubility"

注: JMPでは、SQLステートメントの末尾にセミコロンをつける必要はありません。

JMPでは、SQLをタイプせずにグラフィカルな方法で、簡単なSELECT文を作成できます。データソースから特定の列を選択するには、列のリストでそれらの列を強調表示します。

複数の列名を強調表示するには

• 一定範囲の列名を選択するには、Shiftキーを押しながら範囲の最初の列と最後の列をクリックします。

• Ctrlキー（Windows）またはcommandキー（macOS）を押しながら各列名を選択します。

SQLステートメントは選択に応じて変更されます。

データソースの一意のレコードだけを取得する必要がある場合、つまり、重複レコードを除外したい場合があります。これを行うには、DISTINCTキーワードを使用します。

SELECT DISTINCT ETHER, OCTANOL, CHLOROFORM FROM "Solubility"

並べ替えた結果

データベースの1つまたは複数のフィールドで並べ替えた結果を取得できます。これには、ORDER BYコマンドを使って並べ替える変数を指定します。

SELECT * FROM "Solbility" ORDER BY LABELS

ここでは、LABELS変数の値で並べ替え、すべてのフィールドを選択しています。別の変数も並べ替えのキーとして指定するには、次のようにカンマで区切って変数を指定します。

SELECT * FROM "Solubility" ORDER BY LABELS, ETHER, OCTANOL

WHERE文の使用

WHERE文は、条件に基づいてデータテーブルの特定の行を取得します。たとえば、「ETHER」列が1より大きい行をすべて選択する場合は、次のようになります。

SELECT * FROM "Solubility" WHERE ETHER > 1

WHERE文はFROM文の後に挿入し、次の論理演算子が使用できます。

条件を評価する際、まずNOT文がステートメント全体に対して処理され、次にAND文、最後にOR文が処理されます。そのため、

SELECT * FROM "Solubility" WHERE ETHER > -2 OR OCTANOL < 1 AND CHLOROFORM > 0

は、次の文と同じです。

SELECT * FROM "Solubility" WHERE ETHER > -2 OR (OCTANOL < 1 AND CHLOROFORM > 0)

IN文とBETWEEN文の使用

取得する値の範囲を指定するには、IN文とBETWEEN文をWHEREとともに使用します。IN文では値のリストを指定し、BETWEENでは値の範囲を指定します。たとえば、

SELECT * FROM "Solubility" WHERE LABELS IN (’Methanol’, ’Ethanol’, ’Propanol’)

は、「LABELS」列の値が「Methanol」、「Ethanol」、または「Propanol」の行をすべて取得します。

SELECT * FROM "Solubility" WHERE ETHER BETWEEN 0 AND 2

は、「ETHER」列の値が0から2までの行をすべて取得します。

LIKE文の使用

LIKE文では、指定した文字列に類似した値を選択できます。どの値でもとれる文字列を表すには%を使用します。たとえば、Solubility（溶解度）データの中からアルコールである化学薬品、つまり語尾が-olであるものを選択するには、次のようなSQLステートメントを使用します。

SELECT * FROM "Solubility" WHERE LABELS LIKE ‘%OL’

%演算子はLIKE文の中のどこにでも挿入できます。次の例はLABELの値がMで始まりOLで終わる行をすべて抽出します。

SELECT * FROM "Solubility" WHERE LABELS LIKE ‘M%OL’

集約関数の使用

集約関数はデータそのものよりもデータの要約を取得するのに使用します。SELECT文では、次の集約関数を使用できます。

以下に例をあげます。

• この文は、「ETHER」列の合計と「OCTANOL」列の合計を求めます。

SELECT SUM(ETHER), SUM(OCTANOL) FROM "Solubility"

• この文は、「ETHER」の値が1より大きい行の数を返します。

SELECT COUNT(*) FROM "Solubility" WHERE ETHER > 1

• この文は、アルコールであるデータの「OCTANOL」列の平均を返します。

SELECT AVG(OCTANOL) FROM "Solubility" WHERE LABELS LIKE ‘%OL’

注: 集約関数を使用したとき、結果のJMPデータテーブルの列名は「Expr1000」、「Expr1001」などとなります。取得後、これらの列名を必要に応じて変更します。

GROUP BYコマンドとHAVINGコマンド

GROUP BYコマンドとHAVINGコマンドは集約関数とともに使用すると特に便利です。これらのコマンドでは、データセットのフィールド値に基づいて、集約関数を複数回実行できます。

たとえば、データテーブルの「ETHER」の値について、ETHER=0、ETHER=1などの各値のレコード数を取得したい場合があります。

• SELECT COUNT(ETHER) FROM "Solubility" GROUP BY (ETHER)は、「ETHER」列の水準ごとの度数を含んだ1列のデータを返します。

• SELECT COUNT(ETHER) FROM "Solubility" WHERE OCTANOL > 0 GROUP BY (ETHER)は、上の文と同じ働きをしますが、「OCTANOL」列の値が0より大きい行だけを返します。

GROUP BYを列の集約関数とともに使用する場合、SELECT文に列そのものを含めます。たとえば、

SELECT ETHER, COUNT(ETHER) FROM "Solubility" GROUP BY (ETHER)

は、度数に加えて、「ETHER」列の水準を含む列を返します。

サブクエリーの使用

集約関数はWHERE文内での値の計算に使用すると便利です。たとえば、「ETHER」列の値が平均よりも大きいすべての値を取得したいとします。つまり、まず「ETHER」列の平均値を求めて、そして、その値よりも大きい値を持つレコードを選択します。興味のある変数の平均値を取得するにはSELECT AVG(ETHER) FROM "Solubility"を使用することはすでに説明しました。適切なSQLコマンドのWHERE条件の中にこの文を使用します。

SELECT * FROM "Solubility" WHERE ETHER > (SELECT AVG(ETHER) FROM "Solubility")

SQLクエリーの保存と読み込み

クエリーを作成した後で、そのクエリーを再び使用したい場合があります。クエリーは、使用するたびに手動で入力する必要はありません。外部ファイルに書き出しておくことができます。このためには、図3.67に示すウィンドウで［SQLの書き出し］ボタンをクリックします。すると、SQLクエリーをテキストファイルに保存するためのウィンドウが表示されます。

保存したクエリーを読み込むには、図3.67に示すウィンドウで［SQLの読み込み］ボタンをクリックします。すると、読み込むクエリーを選択するためのウィンドウが表示されます。クエリーを開くと、それがウィンドウに読み込まれます。

WHERE式エディタの使用

JMPでは、ODBC読み込み時にWHERE式エディタを使用することによって、ベンダー独自の関数に基づく基本的なSQL式を簡単に作成できます。たとえば、比較に「=」または「==」のどちらを使うか、平均の計算にavg()またはaverage()のどちらを使うかを知らなくてもSQLクエリーを作成できます。

さらに、文字列リテラルは二重引用符（"文字列"）ではなく一重引用符（'文字列'）で囲みます。

WHERE式エディタを開くには

1. データベースへの接続の手順に従って、データベースに接続します。

2. 「データベース: テーブルを開く」ウィンドウで［詳細］ボタンをクリックします。

3. ［WHERE式］ボタンをクリックします。

WHERE式エディタの計算式操作パネルから計算式や関数、項を選んで追加します。これらは、強調表示された青いボックスに適用されます。

1. テーブル名ブラウザをクリックしてテーブルを選択します。選択したテーブルの列がリストに表示されます。

2. SQLベンダー名ブラウザをクリックし、使用するSQLの種類（GenericSQL、Access、DB2、MySQL、Oracle、SQL Server、SAS SQLのいずれか、またはすべて）を選択します。関数または演算子をクリックし、表示されるリストから演算子を選びます。

注: SQL ServerのBinary、Geography、およびGeometryのデータ型はサポートされていません。

3. 計算式編集領域をクリックして、空の計算式エレメントを選びます（選択された構成要素は、赤い枠で囲まれます）。クリックした部分に近い一番小さいボックス内のすべての構成要素が選択されます。次に実行するアクションは、それらすべての構成要素に適用されます。

4. キーパッドのボタンをクリックして、計算式に演算子を追加します。

5. （オプション）WHERE節の表示をカスタマイズするには、計算式エディタの赤い三角ボタンをクリックし、メニューから次のいずれかのオプションを選びます。

ボックスの表示

WHERE式の項の周りのボックスの表示と非表示を切り替えます。

大きいフォント

計算式のフォントサイズを大きくします。

小さいフォント

計算式のフォントサイズを小さくします。

簡略化

WHERE式ステートメントをできるだけ簡略化します。

パネルレイアウトのリセット

図3.68のようなパネルを表示します。

WHERE式エディタは、計算式エディタで説明している計算式エディタと同じように動作します。

図3.68 WHERE式エディタ 

より詳細な情報が必要な場合や、質問があるときは、JMPユーザーコミュニティで答えを見つけましょう (community.jmp.com).