Apache Derby を使用した開発 -- 3連単を当てる: Apache Derby によるデータベース開発第 5 回

ページオプション JavaScript を要するドキュメントオプションは表示されません 原文はこちら

レベル: 中級

Robert Brunner (rb@ncsa.uiuc.edu), Research Scientist, National Center for Supercomputing Applications

2006年 7月 11日

SQL の SELECT 文を使うと、Apache Derby のデータベースに対してクエリーを行い、ある条件の下で複数のテーブルからデータを選択することができます。この記事は、このシリーズの前回の記事を元に、より強力な SELECT 文を開発します。クエリーで選択された複数のデータ行を変更するための ORDER BY 節や DISTINCT キーワードの使い方や、クエリーの中に基本的な算術演算子を含める方法を学びましょう。また、基本的な SQL の組み込み関数を使ってデータ型を別の型に変更する方法や、数量の集約計算や算術演算、日付や時刻のデータの扱い、文字ストリングの扱いなどについても学びます。

データベースに対するクエリー

前回の記事、「Developing with Apache Derby -- Hitting the Trifecta: Database development with Apache Derby, Part 4」では、Apache Derby データベースからデータを選択する方法について、基本的な概念を紹介しました。そこでは、SELECT 節や FROM 節、WHERE 節について、また 2 つのテーブルを結合して高度なデータ選択を行うといった概念についても説明しました。さらに、Derby ワークスペースの初期化方法についても説明し、Derby に含まれている SQL スクリプト・ファイルを使って、コード・リスト例の中で示した Apache Derby データベースを構築し、データを追加する方法を解説しました。まだ前回の記事を読んでいない人は、まずそれを読み、その記事からダウンロードできる SQL スクリプトを実行して、自分の Derby ワークスペースを初期化してください。

この記事では、こうした基本的な SQL の概念を基に、クエリー内での基本的な算術演算や SQL 関数を含めて、より高度なデータ選択操作について説明します。こうした演算や関数を WHERE 節の中で使用すると、クエリーの中でのデータ制約を強化したり、データ制約を SELCT 節に適用し、計算された値を抽出したりすることができます。しかしこの記事では、その前にまず、クエリーの結果を変更するために使用できるいくつかの SQL クエリー・キーワードについて説明します。Derby の ij ツールを起動して自分のテスト・データベースに接続し、そして Apache Derby データベースを使って SQL クエリーの書き方について学びましょう。

上に戻る

データの選択を変更する

前回の記事で取り上げたクエリーは、どれも単純なものでした。テーブル内のすべての行から、または 2 つのテーブルを単純に結合したものから、または単純な WHERE 節を含むクエリーから、一連の列を選択するだけでした。この機能は確かに便利ですが、言ってみればデータ・ダンプを行うようなものです。幸い SQL には、テーブルから選択された行も抽出されたデータもフィルターできる手法がたくさんあります。このセクションのこれから先では、クエリーによって抽出されたデータをコントロールするための 2 つの基本的な手法、DISTINCT キーワードと ORDER BY 節について説明します。

特定の行を抽出する

SQL クエリーを使ってデータを選択する場合、デフォルトでは、WHERE 節を満足するすべての行がデータベースから抽出されます。そのため、列の値が同じである複数の行が返ってくる可能性があります。ある固有の行の値のみが返されるようにクエリーを制限したい場合には、DISTINCT 修飾子を使います (リスト 1)。

                
rb$ java org.apache.derby.tools.ij    
ij version 10.1
ij> connect 'jdbc:derby:test' ;
ij> SELECT DISTINCT vendorNumber AS "Vendor #" FROM bigdog.vendors ;
Vendor #   
-----------
3          
2          
1          

3 rows selected

ij> SELECT DISTINCT vendorNumber AS "Vendor #", itemNumber as "Item #" 
FROM bigdog.vendors WHERE itemNumber > 5 ;
Vendor #   |Item #     
-----------------------
1          |6          
1          |7          
1          |8          
3          |9          
3          |10         

5 rows selected

ij> 

DISTINCT について DISTINCT 修飾子を使う場合には、リスト 1 でも示しているように、SELECT 節の中にリストされている最初のアイテムが DISTINCT 修飾子である必要があり、また 1 つの SELECT 節には 1 つの DISTINCT 修飾子しか持つことができません。もし選択された何行かに NULL 値を持つ 1 つの列が含まれている場合には、固有の行を識別する際に複数の NULL 値は重複しているとみなされています。

このリストでの最初のクエリーは、クエリー出力を vendorNumber列 (この SELECT 節でリストされている唯一の列) の明確な、つまり固有の値のみに制限するために DISTINCT 修飾子を使っています。この記事のシリーズで使用しているサンプル・スキーマには、3 つのベンダー (vendorNumber の 1 と 2 と 3) しかありません。従って、クエリーの中で DISTINCT 修飾子が使われる場合には、3 つの行しか選択されません。

しかし DISTINCT 修飾子は、選択された列リスト全体に適用されます。従って、もし DISTINCT キーワードに続いて複数の列がリストされている場合には、すべての列の特別な組み合わせしか選択されません。これを 2 番目の例で示します。この例では、vendorNumber と itemNumber の両方が SELECT 節にリストされています。すべてのアイテムが固有の itemNumber を持っているため、これら 2 つの列の組み合わせはどれも固有であり、WHERE 節を満足するすべての行が選択されます。言い換えると、DISTINCT 修飾子は、結果に何の影響も与えません。

もう 1 点、リスト 1 から気がついたかも知れませんが、選択された行は同じ順序になっていません。もし選択された行同士の順序が重要な場合には、クエリーの中で ORDER BY 節を使うことによって簡単に順序を変更することができます。これを次のセクションで説明します。

行の順序を入れ替える

一般的に、Apache Derby であれ他のデータベースであれ、クエリーの結果としての行が特定の順序で返ってくる、と想定することはできません。もし順序が重要である場合には、クエリーで返されるデータを Apache Derby が特定の順序に並び替えられるように、ORDER BY 節を使います。そのために、通常はリスト 2 に示すように、比較用の順序値を提供する列を指定します。

                
ij> SELECT v.vendorNumber AS "Vendor #", vendorName as "Vendor",
p.price as "Price", p.itemNumber AS "Item #"
FROM bigdog.products AS p, bigdog.vendors AS v
WHERE p.itemNumber = v.itemNumber AND p.price > 20.0
ORDER by v.vendorNumber ;
Vendor #   |Vendor                        |Price   |Item #     
---------------------------------------------------------------
1          |Luna Vista Limited            |32.95   |8          
1          |Luna Vista Limited            |24.95   |7          
1          |Luna Vista Limited            |99.99   |2          
2          |Mikal Arroyo Incorporated     |49.95   |5          
2          |Mikal Arroyo Incorporated     |29.95   |4          
3          |Quiet Beach Industries        |34.95   |10         

6 rows selected


ij> SELECT v.vendorNumber AS "Vendor #", vendorName as "Vendor",
p.price as "Price", p.itemNumber AS "Item #"
FROM bigdog.products AS p, bigdog.vendors AS v
WHERE p.itemNumber = v.itemNumber AND p.price > 20.0
ORDER BY v.vendorNumber ASC, p.price DESC ;
Vendor #   |Vendor                        |Price   |Item #     
---------------------------------------------------------------
1          |Luna Vista Limited            |99.99   |2          
1          |Luna Vista Limited            |32.95   |8          
1          |Luna Vista Limited            |24.95   |7          
2          |Mikal Arroyo Incorporated     |49.95   |5          
2          |Mikal Arroyo Incorporated     |29.95   |4          
3          |Quiet Beach Industries        |34.95   |10         

6 rows selected

ij> 

ベスト・プラクティスは、列名を使うこと 列番号を使った方が手軽なように思えるかもしませんが、一般的には賢明な方法ではありません。その理由として、SELECT 節にリストされている列を変更したら、あるいはもっと単純に、そうした列の順序を変更したらどうなるかを考えてみてください。また ORDER BY 節の中で使われている数字の変更を忘れてしまうと、クエリーが壊れてしまいます。もっと悪い場合には、誤ったデータが返ってきてしまうかも知れません。一般的に言って、(たとえタイプの手間は面倒でも) 常に明示的に、列名を直接指定することがベスト・プラクティスです。

リスト 2 の中で、最初のクエリーは、bigdog.vendorsテーブルを bigdog.products テーブルに結合してできたテーブル中にある全ての行のサブセットをリストするために、ORDERBY 節を使っています。これらの行は、vendorNumber の順に並べられています (このサブセットは WHERE 節を適用することで作られます)。ORDER BY 節は、この例のように列名を取ることもでき、あるいは SELECT キーワードの後にリストされている列の順序から取った列番号を取ることもできます。

また、ソート・プロセス中に使用する列を複数指定することもできます。さらには、昇順ソート用に ASC を (ASC がデフォルト値です)、降順ソート用に DESC を使用することもできます。例えば、最初のクエリーの中で ORDER BY 1 DESC, 4 DESC という節を使ってもクエリーが返す行は同じですが、それらの行は、vendorNumber 列を第 1 優先ソート用に使って降順に並べられ、次に itemNumber 列を第 2 優先ソート用に使って降順に並べられます。

上に戻る

クエリーの中で代数演算を行う

データベースから列を選択することにも幾つかの利点がありますが、テーブル中のデータに基づいて数量を計算できたり選択できたりすると、さらに大きな可能性が生まれます。Apache Derby には、幾つかの算術演算子が用意されており、その詳細を表 1 に示します。これらの演算子は、SELECT 節または WHERE 節の中で使用することができます。

これらの演算子は、通常皆さんの期待通りの振る舞いをするため、使い方は単純です。例えば、もし消費税が 8.25%の場合、SELECT price, price * 1.0825 FROM bigdog.products ;を使うと、あるアイテムの消費税適用前と適用後の価格を返すことができます。別の例として、購入されたアイテムの数を追跡する numberItems という列と、そうしたアイテムが購入された際の価格を含む price という別の列があった場合、こうしたアイテムに対して、ある価格での支払い額の合計を返すために、numberItems * price を使うことができます。後で出てくるコード・リストの中で使われているいくつかのクエリーを見ると、こうした演算子の使い方がもっとよく分かるでしょう。

こうした演算子を使う上で唯一気になるのは、異なるデータ型 (整数型と浮動小数点型など) を算術演算の中で使った場合にどうなるのか、という点です。どちらのオペランドも同じデータ型の場合には、結果の型も同じです。もし割り算を実行すると、切り捨てが行われ、予期せぬ問題が発生する可能性があります (例えば 2 つの整数値を使っている場合など)。この問題に対応するためには、後で説明する CAST 関数を使います。一方、2 つのオペランドのデータ型が異なる場合には、演算結果の型は、より複雑な型に格上げされます。

上に戻る

Derby の SQL 関数を使う

SQL は強力で表現力に富む言語であり、非常に様々なことを行うことができます。SQL 言語の強力さの 1 つとして、様々なデータ型を直接扱える点を挙げることができます。このセクションでは、Apache Derby に用意されている SQL-92 標準の組み込み関数について説明します。これらの関数を SQL ステートメントの中で使うことによって、数多くの共通なデータ操作を単純化することができます。この記事では、これらの関数を次の 5 つのカテゴリーに分けています。

• 型変換
• 集約
• 代数
• 日付と時刻
• 文字ストリング

型変換関数

Apache Derby で使用する最も単純なタイプの関数は、型変換関数です。この関数は、ある 1 つのデータ型 (例えば文字ストリングなど) を別のデータ型 (例えば、日付型や整数型など) に、あるいはその逆に変換します。型変換関数には、2 つの主なカテゴリーがあります。1 つは表 2 に示す明示的な型変換関数、もう 1 つは、より強力な型変換を実現する CAST 関数です。

CAST 関数を使うと、文字ストリングの長さを変更したり、あるデータ型を別のデータ型に変更したりすることができます。これには、表 2 に挙げた型変換関数がサポートしていない型 (DECIMAL など) が含まれます。このように CAST を使うことによって、クエリーによって選択された列を読みやすくしたり、もっとコンパクトにしたり、あるいは算術式の中に数値精度を保存したりすることができます。

ベスト・プラクティスは、明示的な型変換を行うこと 多くの場合は Derby が暗黙的かつ自動的に型変換を行ってくれるにもかかわらず､なぜ明示的な型変換を行う必要があるのでしょう？単純な答えとしては、明示的にすることでコードの論理を理解しやすくなるからです。コードが理解しやすければ維持管理が容易になり、一般的にはプログラミング・エラーも起こりにくくなります SQL クエリーを書く場合には、明示的であることが常にベスト・プラクティスなのです。

しかし、Java™プログラムの中でデータベースから結果を抽出する場合には、アプリケーションの中でデータ型を変換するという選択肢もあります。どちらの方法でも同じ結果が得られますが、データベースに値をキャストさせることでパフォーマンスを向上させることができます。これは、ネットワーク・トラフィックを最小限に抑えられるためです。つまりデータベースは実際に必要なデータしか送らないようになり、すべての処理は、接続されるシン・クライアントではなくデータベース・エンジンにプッシュされるからです。この手法は、極端な使い方をした場合にはデータベース・サーバーに過大な負荷を与えることになるため、そうならないことが確実な場合にのみ使用すべきです。この型変換関数の使い方は、リスト 3 に示すように、ごく単純です。

                
ij> SELECT p.price AS "Price", 
DOUBLE(p.price * 1.0825) AS "Float Total", 
CAST(p.price * 1.0825 AS DECIMAL(6,2)) AS "Total",
CAST(p.description AS CHAR(20)) AS "Description"
FROM bigdog.products AS p 
WHERE p.price > 21.0 AND p.stockDate < Date('2006-01-21') ;
Price   |Float Total           |Total    |Description         
--------------------------------------------------------------
24.95   |27.008375             |27.00    |White beach towel   
32.95   |35.668375             |35.66    |Blue-striped beach to

2 rows selected

リスト 3 には 1 つのクエリーしかありませんが、このクエリーを見ると型変換関数の使い方が分かります。このクエリーの 2 ライン目は、選択された price 列と、1.0875 という数値係数 (アイテムの価格に 8.75%の消費税を適用します) との積を、DOUBLE 精度の浮動小数点数に変換します。3 番目と 4 番目のラインは、この積を CAST 関数を使ってまず DECIMAL 型に変換し、次に、選択された description 列を 20文字しか表示しないように切り捨てます。CAST 関数が値を DECIMAL 型に変換する際に、計算された価格を切り捨てていることに注意してください。最後に、DATE 関数は、日付の文字ストリングを DATE 型に変換しています。このステップは必要なわけではありませんが、明示的にすることがベスト・プラクティスなのです (明示的な型に関する囲み記事を見てください)。

このクエリーを見ると、クエリー中の列名をリネームするための AS 節の使い方も分かります。このクエリーは列名の代わりに関数を使っているので、これは重要です。Apache Derby は、列に対して明示的な名前がない場合には自動的に数字を使用しますが、これではデータの素性がほとんど分かりません。すべての列に対して明示的に名前を付けることによって、そうした問題を回避することができます。また、これらの名前には空白が含まれているため、これらの名前が文字ストリング定数であることを ij ツールに示すために、二重引用符で囲む必要があります。

集約された値を計算する

Apache Derby には、複数の行に対して操作を行う、集約関数 (aggregate function) として知られる関数も用意されており、これを使ってクエリーを強化することができます。集約関数は、SQL-92 では集合関数 (set function) として知られていますが、簡単に言えば列関数 (column function) です。つまり多くの行に渡って、ある 1 つの列の数量を計算して返すのです。Apache Derby は 5 つの集約関数をサポートしています。これを表 3 に示します。

こうした集約関数は通常、他の方法では見つけにくい有用な情報を素早く見つけるために使われます。一例をリスト 4 に示します。

                
ij> SELECT COUNT(p.itemNumber) AS Number, 
CAST((AVG(p.price) + 0.005) AS DECIMAL(5,2)) AS Average,
MIN(p.stockDate) AS "First Date", MAX(p.stockDate) AS "Last Date"
FROM bigdog.products AS p ;
NUMBER     |AVERAGE    |First Date|Last Date 
---------------------------------------------
10         |31.66      |2005-12-20|2006-03-31

1 row selected

リスト 4 は、5 つの集約関数のうちの 4 つを使って、bigdog.products テーブル中のデータの要約情報を取得しています。COUNT 関数によって、このテーブルには 10行あることがわかります (これがわかるのは、このクエリーではテーブルから選択される行を WHERE 節で制限していないためです)。 AVG 関数は、bigdog.products テーブルの中にある全アイテムの平均価格を計算します。この計算の結果は浮動小数点数になるため、このクエリーは、結果を適切なサイズの DECIMAL データ型に戻すように明示的にキャストしています。リスト 3 とは異なり、この例では0.005 を追加しており、CAST 関数を使って計算結果を強制的に丸めています。最後に、MIN 関数と MAX 関数は、bigdog.products テーブルから日付の最大値と最小値を抽出しています。

代数関数

Apache Derby には、表 4 に示すように、より複雑な算術演算を SQL クエリーの中で行うための 3 つの組み込み関数があり、先ほど説明した算術演算子と組み合わせて使うと、特に強力な演算を行うことができます。これらの関数は、クエリーの SELECT 節または WHARE 節の中で使用することができます。

日付関数と時刻関数

Apache Derby は、日付や時刻用に多様なデータ型をサポートしています。こうした様々なデータ型をデータベース・クエリーの中で簡単に使えるように、日付と時刻用に 6 つの関数が用意されています。これを表 5 に示します。多くの場合、先ほど説明した型変換関数の代わりに、これらの関数を使うことができます。例えば、リスト 3 に示したクエリーの WHERE 節の p.stockDate <Date('2006-01-21') という部分を、YEAR(p.stockDate) < 2006 で置き換えることができます。

文字ストリング関数

Apache Derby に組み込まれた関数カテゴリーの最後は、文字データの処理を単純化するためのものです。表 6 に示すように、このための関数は 8 つあり、文字値を数える、文字値をすべて大文字または小文字に変換する、あるいはストリングの最初または最後の空白を取り除く、特定のサブストリングを見つける、サブストリングを選択する、等を行うことができます。

これらの関数で使用される文字値は、データベース列 (Apache Derby データベースがサポートする文字データ型の 1 つ) の場合もあれば、文字データ型に直接変換できるデータベース列、あるいは固定文字ストリングなどの場合もあります。例えば、SELECT UCASE (p.description), UCASE ('hello world'), UCASE (p.stockDate) FROM bigdog.products AS p ; というクエリーは 3 つの列を返しますが、それらはすべて大文字の文字ストリングとして表示されます。

上に戻る

まとめ

この記事では、より強力な SQL クエリーを書くための手法をいくつか紹介しました。まず、DISTINCT キーワードを使うことによって、クエリー中の、ある固有の行のみを選択することができます。第 2 に、ORDER BY 節を使うと、クエリー中の選択された行を、1 つ以上の基準に従ってソートすることができます。第 3 に、クエリーに使用できる基本的な代数演算子を学びました。そして最後に、SQL の基本的な組み込み関数を詳細に見てきました。多くの場合、こうした関数を使うことによって複雑な SQL クエリーを単純化することができます。そのための手法については、今後の記事でさらに詳しく説明します。そこでは、既存データベースの中のデータを選択的に削除したり変更したりするための使い方に触れる予定です。

参考文献

• このシリーズの他の記事を調べてください。
  • 「Apache Derby を使用した開発 -- 3 連単を当てる：Apache Derby の紹介」 (developerWorks, 2006 年 2 月) は、Apache Derby データベースを紹介し、このシリーズで取り上げる数多くの話題の基礎を提供しています。
  • 「Apache Derby を使用した開発 - 3 連単を当てる: 第 1 回 Apache Derby によるデータベース開発 Derby を使用して今すぐ作業を始めよう」 (developerWorks, 2006年3月) は、ij ツールを紹介し、このツールを使って Apache Derby データベースに接続する方法について説明しています。
  • 「Apache Derby を使用した開発 -- 3 連単を当てる : 第 2 回 Apache Derby によるデータベース開発 スキーマ」 (developerWorks, 2006年4月) は、データベースに関するいくつかの概念 (スキーマやテーブル、列のデータ型など) を説明し、また SQL を簡単に紹介しています。
  • 「Apache Derby を使用した開発 -- 3 連単を当てる : 第 3 回 Apache Derby によるデータベース開発 スクリプトを実行しデータを挿入する」 (developerWorks, 2006年5月) は、Apache Derby での SQL スクリプトの実行について紹介し、また Apache Derby データベースのテーブルにデータを挿入する方法について説明しています。
  • 「Developing with Apache Derby -- Hitting the Trifecta: Database development with Apache Derby, Part 4: Select data with a query」 (developerWorks, 2006年5月) は、SQL クエリーの概念を、基本的な SELECT 節や FROM 節、WHERE 節など含めて紹介しています。
  
  
• Apache Derby のデータベースの使い方についてさらに詳しく知るためには、Apache Derby Project のオンライン・マニュアルをよく読んでください。
  
  
• Apache Derby のダウンロードとインストールの詳細を説明した Apache Derby Project のチュートリアルを受講してください。
  
  
• Derby ダウンロードを適切に検証する方法を学んでください。
  
  
• developerWorks の Apache Derby project area を調べてみてください。今日からでも Derby を使い始めるための様々な記事やチュートリアル、その他の資料が用意されています。
  
  
• Apache Derby コードベースを使って構築された、IBM® Cloudscape™ データベースについて学んでください。
  
  
• developerWorks の Open source ゾーンに用意された Apache に関する記事や、無料の Apache チュートリアルを調べてみてください。
  
  
• developerWorks の Open source ゾーンをご覧ください。オープンソース技術を使った開発や、IBM 製品でオープンソース技術を使用するためのハウ・ツー情報やツール、プロジェクトの更新情報など、豊富な情報が用意されています。
  
  
• Safari bookstore では、この記事で取り上げた話題や、その他の技術的な話題に関する技術書が豊富に取り揃えられています。
  
  

• Apache Derby Project のホームページから Apache Derby をダウンロードしてください。
  
  
• 皆さんの次期オープンソース開発プロジェクトを IBM trial software を使って革新してください。ダウンロード、あるいは DVD で入手することができます。
  
  

• developerWorks blogs から developerWorks のコミュニティーに加わってください。

著者について

		Robert J. Brunnerは、米国立スーパー・コンピュータ応用研究所に科学者として勤務するかたわら、イリノイ大学アーバナ・シャンペーン校で天文学の助教授を務めています。何冊かの著作と、さまざまな分野にわたる数多くの記事や解説書を発表しています。

記事の評価

サイト改善のため、ご意見をお寄せください。こちらのフォームからお願いいたします。 この情報はお客様が目的を達成するうえで役に立ちましたか？ はい いいえ わからない   お気軽にご意見・ご感想をお寄せください。   この記事は役に立ちましたか？ 5 4 3 2 1 大変素晴らしい 不充分・不完全である

上に戻る