パフォーマンス＆チューニング・ガイド Sybase® IQ 12.
ドキュメント ID：DC00283-01-1270-01 改訂：2006 年 6 月 Copyright © 1991-2006 by Sybase, Inc. All rights reserved. このマニュアルは Sybase ソフトウェアの付属マニュアルであり、新しいマニュアルまたはテクニカル・ノートで特に示さ れないかぎりは、後続のリリースにも付属します。このマニュアルの内容は、予告なく変更されることがありますが、 Sybase, Inc.
目次 はじめに .................................................................................................................................................. ix 第1章 データベース・テーブルからのデータの選択 ........................................ 1 前提条件 ................................................................................................. 2 テーブル情報の表示 ............................................................................... 3 クエリ結果の順序付け ...........................................................................
目次 第3章 クエリと削除の最適化 .......................................................................... 31 クエリ構築のヒント............................................................................. UNION ALL での GROUP BY がクエリ・パフォーマンスに 与える影響............................................................................. Adaptive Server Anywhere による処理を引き起こす条件............ クエリ・プラン .................................................................................... クエリ評価オプション .....................................................
目次 第5章 システム・リソースの管理 ................................................................. 103 パフォーマンス用語の概要................................................................. 104 パフォーマンス向上のための設計...................................................... 104 メモリ使用の概要 ............................................................................... 105 ページングによる使用可能メモリの増加.................................... 105 スワッピングをモニタするためのユーティリティ ..................... 106 サーバ・メモリ .......................................................
目次 データベース・サイズと構造の管理 ................................................. データベース・サイズの管理 ..................................................... インデックスの断片化の制御 ..................................................... カタログ・ファイル増大の最小化 .............................................. パフォーマンス向上のための非正規化....................................... 非正規化のリスク ....................................................................... 非正規化の短所........................................................................... 非正規化のパフォーマンスの利点 .....
目次 NTFS キャッシュの使用..................................................................... 180 挿入とクエリのチューニング ............................................................. 181 適切にチューニングされた挿入オペレーションの特性 .............. 181 クエリのチューニング................................................................. 182 バックアップ操作のチューニング...................................................... 182 索引.............................................................................................................................................
目次 viii Sybase IQ
はじめに このマニュアルの内容 このマニュアルでは、パフォーマンスとチューニングの推奨事項について 説明します。 対象読者 このマニュアルは、パフォーマンス上の問題を理解する必要があるシステ ム管理者とデータベース管理者を対象としています。リレーショナル・ データベース・システムの基礎知識と、Sybase IQ のユーザ・レベルの基 本的な経験があることを前提にしています。このマニュアルは、他のマ ニュアルとともに使用してください。 このマニュアルの使用方法 次のリストは、行う作業や必要性に応じてどの章を参照すべきかを示し ます。 関連マニュアル • SELECT 文の構築については、 「第 1 章 データベース・テーブルから のデータの選択」を参照してください。 • ジョイン条件については、 「第 2 章 テーブルのジョイン」を参照して ください。 • クエリの最適化については、 「第 3 章 クエリと削除の最適化」を参照 してください。 • メモリ、ディスク I/O、CPU の調整については、「第 5 章 システム・ リソースの管理」を参照してください。 • パフォーマン
• 『Sybase IQ トラブルシューティングおよびリカバリ・ガイド』－ 問題の解 決方法、システム・リカバリの実行方法、データベースの修復方法を紹介 しています。 • 『Sybase IQ エラー・メッセージ』－ Sybase IQ エラー・メッセージ (SQLCode、 SQLState、Sybase エラー・コードによって参照 )、および SQL プリプロセッ サのエラーと警告を示します。 • 『Sybase IQ ユーティリティ・ガイド』－ Sybase IQ ユーティリティ・プロ グラムのリファレンス項目 ( 使用可能な構文、パラメータ、オプションな ど ) について説明しています。 • 『Sybase IQ によるラージ・オブジェクト管理』－ Sybase IQ データ・リポ ジ ト リ 内 で の BLOB (Binary Large Object) および CLOB (Character Large Object) の格納と取得について説明しています。このオプションの製品を インストールするには、別のライセンスが必要です。 • 『Sybase IQ インストールおよび設定ガ
はじめに • 『Adaptive Server Anywhere SQL リファレンス・マニュアル』－ Adaptive Server Anywhere で使用する SQL 言語のリファレンスです。さらに、 Adaptive Server Anywhere のシステム・テーブルとプロシージャについて も説明します。 Sybase Product Manuals Web サイトでも、SQL Anywhere Studio 9.0.2 コレクショ ンの Adaptive Server Anywhere マニュアルを参照できます。Product Manuals (http://www.sybase.
Web 上の Sybase 製品 の動作確認情報 Sybase Web サイトの技術的な資料は頻繁に更新されます。 ❖ ❖ ❖ 製品動作確認の最新情報にアクセスする 1 Web ブラウザで Technical Documents を指定します。 (http://www.sybase.com/support/techdocs/) 2 [Certification Report] をクリックします。 3 [Certification Report] フィルタで製品、プラットフォーム、時間枠を指定し て [Go] をクリックします。 4 [Certification Report] のタイトルをクリックして、レポートを表示します。 コンポーネント動作確認の最新情報にアクセスする 1 Web ブラウザで Availability and Certification Reports を指定します。 (http://certification.sybase.
はじめに 4 時間枠を指定して [Go] をクリックします。EBF/Maintenance リリースのリ ストが表示されます。 鍵のアイコンは、自分が Technical Support Contact として登録されていな いため、一部の EBF/Maintenance リリースをダウンロードする権限がない ことを示しています。未登録ではあるが、Sybase 担当者またはサポート・ コンタクトから有効な情報を得ている場合は、[Edit Roles] をクリックし て、 「Technical Support Contact」 役割を MySybase プロファイルに追加します。 5 SQL 構文の表記規則 EBF/Maintenance レポートを表示するには [Info] アイコンをクリックします。 ソフトウェアをダウンロードするには製品の説明をクリックします。 このマニュアルで、構文の説明に使用する表記規則は次のとおりです。 • • SQL キーワードは大文字で示します。ただし、SQL キーワー ドは大文字と小文字を区別しないので、入力するときはどちらで入力して もかまいません。たとえば、S
書体の表記規則 表 1 に、このマニュアルで使用している書体の表記規則を示します。 表 1: 書体の表記規則 項目 Code 説明 User entry ユーザが入力するテキストには等幅 ( 固定幅 ) 文字フォントを使 用します。 「強調」 file names database objects サンプル・データベース SQL およびプログラム・コードは等幅 ( 固定幅 ) 文字フォントで 表記します。 強調する言葉は「 」で囲みます。 ファイル名は斜体で表記します。 テーブル、プロシージャなどのデータベース・オブジェクトの名 前は、印刷物ではゴシック体フォントで、オンラインでは斜体で 表記します。 Sybase IQ にはサンプル・データベースが用意されています。Sybase IQ マニュ アルで紹介している例の多くは、このサンプル・データベースによるものです。 サンプル・データベースは小規模企業の例を示しています。データベースに は、この企業の内部情報 (employee、department) とともに、製品情報 (product)、 販売情報 (sles_order、custome
はじめに アクセシビリティ・ツールの設定 アクセシビリティ・ツールを効率的に使用するには、設定が必要な場合もありま す。一部のスクリーン・リーダーは、テキストの大文字と小文字を区別して発音 します。たとえば、すべて大文字のテキスト (ALL UPPERCASE TEXT など ) はイ ニシャルで発音し、大文字と小文字の混在したテキスト (MixedCase Text など ) は 単語として発音します。構文規則を発音するようにツールを設定すると便利かも しれません。詳細については、ツールのマニュアルと『Sybase IQ の概要』の「ス クリーン・リーダの使用」を参照してください。 Sybase のアクセシビリティに対する取り組みについては、 Sybase Accessibility (http://www.sybase.
xvi Sybase IQ
第 1 章 この章について データベース・テーブルからのデータの 選択 この章では、基本的なクエリの構築と、製品設計を活用するための手法に ついて説明します。ここではテーブルの内容の表示、クエリ結果の順序付 け、カラムとローの選択、探索条件を使ったクエリの絞り込みを行う チュートリアル作業を実行します。 高度なクエリ・パフォーマンスの推奨事項については、 「第 3 章 クエリと 削除の最適化」を参照してください。 内容 トピック名 前提条件 ページ 2 テーブル情報の表示 3 クエリ結果の順序付け 5 カラムとローの選択 6 探索条件の使用 7 集約データの取得 11 分析データの取得 17 重複したローの削除 18 パフォーマンス＆チューニング・ガイド 1
前提条件 前提条件 DBISQL の代わりにグラフィカルなフロントエンド・ツールを使用してデータ ベースへのクエリを実行すると、ツールが生成する SQL 構文を表示できる場 合があります。たとえば、InfoMaker では [ テーブル ] ペインタ・バーの [SQL 構文 ] ボタンを選択して SQL 文を表示できます。 このチュートリアルでは、データベースから情報を取得するときに使用する SELECT 文について説明します。SELECT 文のことを一般にクエリと呼びます。 これは、SELECT 文がデータベース内の情報についてデータベース・サーバに 問い合わせるためです。 注意 SELECT 文は用途の広いコマンドです。大きなデータベースから非常に 具体的な情報を取得するアプリケーションでは、SELECT 文がきわめて複雑に なる場合があります。このチュートリアルでは、単純な SELECT 文だけを使 用します。以降のチュートリアルで、より高度なクエリについて説明します。 SELECT 文の完全な構文については、 『Sybase IQ リファレンス・マニュアル』 の「第 6 章 SQL 文」の「SEL
第1章 データベース・テーブルからのデータの選択 テーブル情報の表示 ここでは、employee テーブルのデータを表示します。 このチュートリアルで使用するサンプル・データベースは、架空の会社のもの です。データベースには、従業員、部署、注文などについての情報が格納され ています。すべての情報はテーブルに編成されています。 テーブルのリスト 『Sybase IQ の概要』では、Sybase Central で [ テーブル ] フォルダを開いてテー ブルのリストを表示する方法について説明しました。システム・ストアド・プ ロシージャの sp_iqtable を使用して、Interactive SQL からユーザ・テーブルを リストすることもできます。システム・ストアド・プロシージャは、Sybase IQ にストアド・プロシージャとして実装されているシステム関数です。 [SQL 文 ] ウィンドウで sp_iqtable と入力し、同じ名前のシステム・ストア ド・プロシージャを実行します。 システム・ストアド・プロシージャの詳細については、 『Sybase IQ リファレン ス・マニュアル』の「第 10
テーブル情報の表示 SELECT 文の使用 このレッスンでは、データベース内のテーブルの 1 つを表示します。使用する コマンドは、employee という名前のテーブル全体を表示します。 次のコマンドを実行します。 SELECT * FROM employee アスタリスクは、テーブル内のすべてのカラムを表す省略記号です。 SELECT 文は employee テーブルのすべてのローとカラムを取得し、DBISQL [ 結果 ] ウィンドウに次の該当するものが表示されます。 emp_id manager_id emp_fname emp_lname dept_id 102 501 Fran Whitney 100 105 501 Matthew Cobb 100 129 902 Philip Chin 200 148 1293 Julie Jordan 300 160 501 Robert Breault 100 employee テーブルには、カラムに編成された複数のローが格納されています。 各カラムには、emp_lname や emp_id
第1章 データベース・テーブルからのデータの選択 クエリ結果の順序付け ここでは、SELECT 文に ORDER BY 句を追加して、結果をアルファベット順 または数値順に表示します。 特に指定しないかぎり、 Sybase IQ ではテーブルのローが順不同で表示されます。 テーブルのローを意味のある順序で表示した方が便利なことがよくあります。 たとえば、従業員をアルファベット順で表示したいような場合です。 従業員をアルファベット 順にリストする 次の例は、SELECT 文に ORDER BY 句を追加して、結果をアルファベット順 に取得する方法を示します。 SELECT * FROM employee ORDER BY emp_lname 注意 emp_id manager_id emp_fname emp_lname 1751 1576 Alex Ahmed dept_id 400 1013 703 Joseph Barker 500 591 1576 Irene Barletta 400 191 703 Jeannette Bertrand 500
カラムとローの選択 カラムとローの選択 多くの場合、表示する必要がある情報は、テーブル内の一部のカラムだけです。 たとえば、従業員への誕生日カードを作成するには、emp_lname、dept_id、 birth_date の各カラムを表示すれば十分です。 各従業員の姓、部署、 誕生日をリストする ここでは、各従業員の誕生日、姓、部署 ID を選択します。次のコマンドを入 力します。 SELECT emp_lname, dept_id, birth_date FROM employee カラムの並べ替え emp_lname dept_id birth_date Whitney 100 1958-06-05 ... ... Cobb 100 1960-12-04 ... Chin 200 1966-10-30 ... Jordan 300 1951-12-13 ... Breault 100 1947-05-13 ...
第1章 データベース・テーブルからのデータの選択 探索条件の使用 ここでは、WHERE 句の複合探索条件、パターン・マッチング、探索条件ショー トカットを使用して、日付を比較する手順について説明します。 employee テーブルにある一部の従業員の情報だけを表示したいことがあり ます。SELECT 文に WHERE 句を追加すると、テーブルから一部のローだけ を選択できます。 たとえば、John という名前の従業員だけを表示するとします。 ❖ John という名前のすべての従業員をリストするには • 次のコマンドを入力します。 SELECT * FROM employee WHERE emp_fname = 'John' emp_id アポストロフィおよび 大文字と小文字の区別 manager_id emp_fname emp_lname dept_id 318 1576 John Crow 400 862 501 John Sheffield 100 1483 1293 John Letiecq 300 • 名前 'John' はアポストロフィ ( 一重
探索条件の使用 クエリでの日付の比較 検索対象の正確な値がわからない場合や、一連の値を表示したい場合があり ます。WHERE 句で比較を使用すると、探索条件を満たす一連のローを選択で きます。 1964 年 3 月 3 日より前に 生まれた従業員をリスト する 次の例は、日付の不等号探索条件の使い方を示します。次のコマンドを入力し ます。 SELECT emp_lname, birth_date FROM employee WHERE birth_date < 'March 3, 1964' emp_lname birth_date Whitney 1958-06-05 00:00:00.000 Cobb 1960-12-04 00:00:00.000 Jordan 1951-12-13 00:00:00.000 Breault 1947-05-13 00:00:00.000 Espinoza 1939-12-14 00:00:00.000 Dill 1963-07-19 00:00:00.
第1章 データベース・テーブルからのデータの選択 探索条件でのパターン・マッチング もう 1 つの便利な検索方法が、パターンによる検索です。SQL では、LIKE と いう語を使用してパターンを検索します。LIKE の使い方について、例を挙げ て説明します。 姓が BR で始まる従業員 をリストする 次のコマンドを入力します。 SELECT emp_lname, emp_fname FROM employee WHERE emp_lname LIKE 'br%' emp_lname emp_fname Breault Robert Braun Jane 探索条件内の % は、BR という文字の後に別の文字が何文字続いてもかまわな いことを示します。 姓検索の修飾 姓が BR で始まり、その直後または数文字後に T という文字を含み、T で終わ るかさらに別の文字が続くすべての従業員をリストするには、次のコマンドを 使用します。 SELECT emp_lname, emp_fname FROM employee WHERE emp_lname LIKE 'BR%T%' emp_lname
探索条件の使用 発音によるローのマッチング SOUNDEX 関数を使用すると、スペルだけでなく読みによってもローをマッチ ングできます。たとえば、電話メッセージが残されていて、その宛先が “Ms.
第1章 データベース・テーブルからのデータの選択 集約データの取得 ここでは、集約情報を返すクエリを構築する方法について説明します。集約情 報の例を次に示します。 • カラム内のすべての値の合計 • カラム内のエントリの数 • カラム内のエントリの平均値 集合関数の概要 従業員の人数を調べたいとします。次の文は、employee テーブルのローの数 を取得します。 SELECT count( * ) FROM employee count(*) 75 このクエリによって、1 つのカラム (count(*) というタイトル ) と 1 つのロー ( 従業員数が格納されている ) だけで構成されるテーブルが返されます。 次のコマンドは、やや複雑な集約クエリです。 SELECT count( * ), min( birth_date ), max( birth_date ) FROM employee count(*) min( birth_date ) max( birth_date ) 75 1936-01-02 1973-01-18 このクエリの結果セットは、3 つのカラムと 1
集約データの取得 集合関数によるグループ化されたデータの取得 テーブル全体についての情報を取得することに加えて、集合関数をローのグ ループに対して使うこともできます。 ローのグループに対する 集合関数の使用 各営業担当者が受け持つ注文数をリストするには、次のコマンドを入力します。 SELECT sales_rep, count( * ) FROM sales_order GROUP BY sales_rep sales_rep count(*) 129 57 195 50 299 114 467 56 667 54 このクエリの結果は、各営業担当者の ID 番号別に、営業担当者の ID が格納さ れたローと、sales_order テーブル内でその ID 番号を持つローの数で構成され ます。 GROUP BY 句を使用すると、結果のテーブルには、GROUP BY で指定したカ ラムで見つかった値別のローが表示されます。 グループの制限 WHERE 句を使用して、クエリでローを制限する方法についてはすでに説明し ました。GROUP BY 句の制限には、HAVING キーワードを使用
第1章 WHERE と GROUP BY の使用 データベース・テーブルからのデータの選択 注文数が 55 を超えており、ID が 1000 より大きいすべての営業担当者をリス トするには、次のコマンドを入力します。 SELECT sales_rep, count( * ) FROM sales_order WHERE sales_rep > 1000 GROUP BY sales_rep HAVING count( * ) > 55 Sybase IQ クエリ・オプティマイザは、それによってパフォーマンスが向上す る場合、述部を HAVING 句から WHERE 句に移動します。たとえば、上記の 例で WHERE 句の代わりに述部を次のように指定した場合、クエリ・オプティ マイザは述部を WHERE 句に移動します。 GROUP BY sales_rep HAVING count( *) > 55 AND sales_rep > 1000 Sybase IQ は、この最適化を (OR や IN を伴わない ) 単純な条件を使って実行し ます。このため、WHERE 句と HAVING 句の両方を含む
集約データの取得 year model color 1991 Chevrolet blue sales 54 1991 Chevrolet red 95 1991 Chevrolet white 49 1991 Chevrolet NULL 198 1991 Ford blue 52 1991 Ford red 55 1991 Ford white 9 1991 Ford NULL 116 1991 NULL NULL 314 NULL NULL NULL 657 このクエリを処理するときに、Sybase IQ は最初に、指定された 3 つすべての グループ化式 (year、model、color) によってデータをグループ化し、次に最後 の式 (color) を除くすべてのグループ化式によってデータをグループ化します。 5 番目のローの NULL は、color カラムの ROLLUP 値、つまり、そのモデルの すべての色の合計販売数を示します。343 は、1990 年のすべてのモデルと色の 合計販売数を表し、314 は
第1章 データベース・テーブルからのデータの選択 次のクエリは、GROUPING 関数を GROUP BY CUBE と組み合わせた使用例 です。 SELECT CASE GROUPING ( state ) WHEN 1 THEN 'ALL' ELSE state END AS c_state, CASE GROUPING ( gender ) WHEN 1 THEN 'ALL' ELSE gender END AS c_gender, CASE GROUPING ( education ) WHEN 1 THEN 'ALL' ELSE education END AS c_education, COUNT(*), CAST (ROUND ( AVG ( income ), 2 ) AS NUMERIC (18,2)) AS average FROM census GROUP BY CUBE (state, gender, education); このクエリの結果は次のとおりです。CUBE が生成した小計ローを示す NULL 値が、クエリ内の指定によって小計ローで ALL に置き換わっています。 c_
集約データの取得 c_state c_gender c_education count(*) average ALL m ALL 12 62000.00 ALL f BA 3 48333.33 ALL m HS 1 55000.00 ALL m MS 4 76000.00 ALL m BA 7 55000.00 ALL f MS 2 65000.00 ALL f HS 4 45000.00 NH ALL HS 2 50000.00 NH ALL MS 2 67000.00 MA ALL MS 4 75000.00 MA ALL HS 3 45000.00 MA ALL BA 7 52142.86 NH ALL BA 3 55000.
第1章 データベース・テーブルからのデータの選択 分析データの取得 ここでは、分析情報を返すクエリを構築する方法について説明します。統計関 数には、ランク付けと逆分散統計の 2 種類があります。ランク付け統計関数 は、グループ内の項目をランク付けしたり、分散統計を計算したり、結果セッ トを複数のグループに分割したりします。逆分散統計関数は、K- 理論パーセ ンタイル値を返します。これは、ひとまとまりのデータの値として許容し得る しきい値を決定する際に使用します。 ランク分析関数には、RANK、DENSE_RANK、PERCENT_RANK、NTILE が あります。逆分散統計関数には、PERCENTILE_CONT と PERCENTILE_DISC があります。 たとえば、自動車販売店の販売状況を調べたいとします。NTILE 関数で、各販 売店が販売した車の台数に基づいて、販売店を 4 つのグループに分類します。 ntile = 1 になっているのは、車の販売台数で上位 25% までのディーラです。 SELECT dealer_name, sales, NTILE(4) OVER ( ORDER BY
重複したローの削除 OVER 句は、関数がクエリの結果セットに対して処理を行うことを示します。 結果セットは、FROM、WHERE、GROUP BY、HAVING の各句がすべて評価 された後で返されるローです。OVER 句には、ランク付け統計関数の計算の対 象となるローのデータ・セットを定義します。 同様に、逆分布関数では WITHIN GROUP (ORDER BY) 句を指定する必要があ ります。ORDER BY 句は、百分位関数を実行する式と、各グループでローを ソートする順序を指定します。この ORDER BY 句は、WITHIN GROUP 句の 中でだけ使用されるもので、SELECT の ORDER BY とは異なります。WITHIN GROUP 句は、クエリの結果を並べ替えて、関数が結果を計算するためのデー タ・セットを形成します。 分析関数の詳細については、 『Sybase IQ リファレンス・マニュアル』の「第 5 章 SQL 関数」の「統計関数」を参照してください。個別の分析関数について は、「SQL 関数」の章の各関数の項を参照してください。 重複したローの削除 SELECT 文の
第 2 章 この章について 内容 テーブルのジョイン この章では、複数のテーブルにある情報を参照する方法と、さまざまな種 類のジョインについて説明します。ここでは、テーブルをジョインする チュートリアル作業を実行します。 トピック名 外積を使用したテーブルのジョイン ページ 19 ジョインの制限 20 テーブル間の関係 21 ジョイン演算子 22 アドホック・ジョインとジョイン・インデックスの使用 25 ジョインとデータ型 25 ストアまたはデータベース間ジョインのサポート 26 リモート・データベースと異種データベースのクエリ 27 サブクエリによるジョインの置き換え 28 外積を使用したテーブルのジョイン サンプル・データベースに、会社の財務データをリストする fin_data とい うテーブルがあります。各データ・レコードには、そのレコードの部署 と、それが支出レコードか収入レコードかを示す code カラムがあります。 fin_data テーブルには 84 のローがあります。 2 つのテーブルから同時に情報を取り出すには、SELECT クエリの FROM
ジョインの制限 外積ジョインは、ジョインを理解するための単純な出発点にすぎず、それ自体 はあまり役に立ちません。これ以降の項で、より選択性の高いジョインを構築 する方法について説明します。このジョインは、外積テーブルへの制限の適用 と考えることができます。 ジョインの制限 外積ジョインを有効に利用するには、何らかの条件を満たすローだけを結果に 含める必要があります。ジョイン条件と呼ばれるこの条件では、比較演算子 (=、=>、< など ) を使用して、あるテーブルの 1 つのカラムを別のテーブルの 1 つのカラムと比較します。これにより、外積の結果から一部のローを除外し ます。 たとえば、前の項のジョインを有効に利用するには、sales_order テーブルの sales_rep と employee テーブルの従業員番号が一致するローだけを結果に含 めるように指定します。これにより、各ローには注文と、その注文を担当する 営業担当者の情報が格納されます。 例1 これを実行するには、前のクエリに WHERE 句を追加し、従業員とその担当登 録のリストを表示します。 SELECT * FROM sales_o
第2章 相関名 テーブルのジョイン 相関名は、テーブルの特定のインスタンスのエイリアスです。このエイリアス は、1 つの文中でのみ有効です。相関名を作成するには、テーブル名のすぐ後 ろに、テーブル名の省略形をキーワード AS で区切って指定します。それ以降 は、修飾子としてテーブル名の代わりにこの省略形を使用する必要があります。 SELECT E.emp_lname, S.id, S.order_date FROM sales_order AS S, employee AS E WHERE S.sales_rep = E.emp_id ORDER BY E.
ジョイン演算子 外部キーによって関連付けられたテーブル asiqdemo データベースのいくつかのテーブルは、データベース内の他のテー ブルを参照しています。たとえば、sales_order テーブルには、注文を担当す る従業員を示す sales_rep カラムがあります。sales_order テーブルには、従業 員をユニークに識別するために必要な最小限の情報だけが格納されています。 sales_order テーブルの sales_rep カラムは、employee テーブルに対する外部 キーになっています。 外部キー 外部キーは、他のテーブルの候補キーの値を含む 1 つまたは複数のカラムです ( 候補キーの詳細については、 『Sybase IQ システム管理ガイド』の「第 5 章 デー タベース・オブジェクトの使用」を参照してください )。従業員データベース 内の各外部キーの関係は、2 つのテーブル間の矢印によって図示されます。 『Sybase IQ の概要』の図 1-1 (11 ページ ) のサンプル・データベースの図に、こ れらの矢印が示されています。矢印は関係の外部キー側を起点とし、候補キー 側
第2章 テーブルのジョイン ジョイン演算子 (KEY JOIN) は、単に WHERE 句の入力の手間を省くためのも ので、2 つのクエリはまったく同じものです。 『Sybase IQ の概要』の asiqdemo データベースの図では、外部キーがテーブル 間の線で表されています。図中で 2 つのテーブルが線で結合されていれば、 KEY JOIN 演算子を使用できます。キー・ジョインによるクエリで期待どおり の結果を得るには、アプリケーションで外部キーを強制的に適用する必要があ ります。 複数のテーブルの ジョイン ジョイン演算子を使用して、複数のテーブルをジョインできます。次のクエリ では、4 つのテーブルを使用して、注文の合計額を顧客別にリストしています。 customer、sales_order、sales_order_items、product の 4 つのテーブルを、テー ブルの各ペア間の 1 つの外部キー関係で接続しています。 SELECT company_name, CAST( SUM(sales_order_items.quantity * product.
ジョイン演算子 ナチュラル・ジョインを使用したテーブルのジョイン NATURAL JOIN 演算子は、共通のカラム名に基づいて 2 つのテーブルをジョ インします。言い換えると、Sybase IQ が各テーブルに共通するカラムを結び 付ける WHERE 句を生成します。 例 たとえば、次のようなクエリがあるとします。 SELECT emp_lname, dept_name FROM employee NATURAL JOIN department この例では、データベース・サーバが 2 つのテーブルを参照し、共通するカラ ム名は dept_id だけであると判断します。次の ON フレーズが内部的に生成さ れ、ジョインの実行に使用されます。 FROM employee JOIN department ... ON employee.dept_id = department.
第2章 テーブルのジョイン アドホック・ジョインとジョイン・インデックスの使用 クエリで参照されるジョイン・カラムにジョイン・インデックスを定義して いる場合、Sybase IQ は自動的にそれらを使用してクエリ処理を高速化しま す ( ジョイン・インデックスの定義については、『Sybase IQ システム管理ガ イド』の「第 6 章 Sybase IQ インデックスの使用」を参照してください )。 ジョイン・インデックスを使用しないジョインを「アドホック・ジョイン」呼 びます。クエリでいくつものテーブルを参照しており、その中にジョイン・イ ンデックスが定義されていないテーブルがある場合、Sybase IQ は定義されて いるテーブルではジョイン・インデックスを、それ以外のテーブルではアド ホック・ジョインを使用します。 可能なすべてのジョインに対してジョイン・インデックスを作成することはで きないため、ときにはアドホック・ジョインが必要になることがあります。 Sybase IQ の最適化によって、クエリはジョイン・インデックスなしでも同等 かそれ以上のパフォーマンスで実行されます。 ジョイン・インデック
ストアまたはデータベース間ジョインのサポート ストアまたはデータベース間ジョインのサポート この項では、ストア間またはデータベース間ジョインに対する現在のサポート を明確にします。 Sybase IQ データベー ス内でのテーブルの ジョイン Adaptive Server Enterprise テーブルと Sybase IQ テーブルの ジョイン 指定された Sybase IQ データベース内では、あらゆる種類のジョインがサポー トされます。つまり、カタログ・ストアの任意のシステムまたはユーザ・テー ブルを、IQ ストアの任意のテーブルに任意の順序でジョインできます。 Sybase IQ テーブルと Adaptive Server Enterprise データベースのテーブルのジョ インは、次の条件下でサポートされます。 • Sybase IQ データベースは、ローカル・データベースとリモート・データ ベースのどちらでもかまいません。 • ASE で Sybase IQ テーブルをプロキシ・テーブルとして使用する場合は、 テーブル名を 30 文字以内にしてください。 • ローカルの Adapt
第2章 Adaptive Server Anywhere テーブルと Sybase IQ テーブルの ジョイン テーブルのジョイン データベースが BLANK PADDING OFF を指定して構築された場合、CHAR データ型は Adaptive Server Anywhere と Sybase IQ の間で互換性がありません。 文字データをジョイン・キーとして使用して、Adaptive Server Anywhere テー ブルと Sybase IQ テーブルの間でデータベースのジョインを実行する場合は、 BLANK PADDING ON を指定して CHAR データ型を使用します。 注意 Sybase IQ CREATE DATABASE は、 新しいデータベースについては BLANK PADDING OFF をサポートしなくなりました。この変更は、既存のデータベース には影響しません。BlankPadding database プロパティを使用して、既存のデータ ベースの状態をテストすることができます。 select db_property ( ‘BlankPadding’ ) Sybase
サブクエリによるジョインの置き換え 3 プロキシ・データベース名を各リモート・テーブルの修飾名として使用 し、SELECT 文でプロキシ・テーブルを参照します。たとえば、次のよう な文を発行します。 SELECT a.c_custkey, b.o_orderkey FROM proxy_asiqdemo..cust2 a, asiqdemo..orders b WHERE a.c_custkey = b.
第2章 外部ジョインの使用 テーブルのジョイン 前項のチュートリアルのジョインは、より正確には「内部ジョイン」と呼ばれ ます。 外部ジョインを明示的に指定します。この場合は、GROUP BY 句も必要です。 SELECT company_name, MAX( sales_order.id ),state FROM customer KEY LEFT OUTER JOIN sales_order WHERE state = 'WA' GROUP BY company_name, state サブクエリの使用 company_name max(sales_order.
サブクエリによるジョインの置き換え クエリの別の表現方法 受注した 10 枚のタンクトップが出荷され、タンクトップの数量カラムの値が 18 になった場合にどうなるかを考えてみます。サブクエリを使ったクエリは、 ウールの帽子とタンクトップの両方のすべての注文をリストします。これに対 して、最初に使用した文は次のように変更する必要があります。 SELECT * FROM sales_order_items WHERE prod_id IN ( 401, 300 ) ORDER BY ship_date DESC サブクエリを使用するコマンドは、データベースのデータが変更されてもその まま機能するように、改善されています。 サブクエリについては、次の点に注意してください。 30 • NOT EXISTS 述部を使用するクエリなど、ジョインの構築に問題がある場 合もサブクエリが役立つことがあります。 • サブクエリが返せるのは 1 つのカラムだけです。 • サブクエリは、比較の引数、IN、または EXISTS 句としてのみ使用でき ます。 • 外部ジョインの ON 句の中に、サブクエリを使用す
第 3 章 この章について クエリと削除の最適化 この章では、次のようなクエリと削除のパフォーマンスに関する推奨事項 について説明します。 • 処理速度の速いクエリの構築 • クエリ・プランの使用 • クエリ処理オプションの設定 • 削除オペレーションの最適化 内容 トピック名 クエリ構築のヒント ページ 31 クエリ・プラン 35 クエリ処理の制御 37 削除オペレーションの最適化 40 クエリ構築のヒント ここでは、クエリ構造を改良するためのヒントを示します。 • サブクエリを含むコマンド文をジョインとして構成することによっ て、実行速度を高めることができる場合があります。 • GROUP BY 句で複数のカラムをグループ化する場合、カラムに対応 するユニークな値をもとに降順にカラムをリストします。これによっ て最適なクエリのパフォーマンスが実現されます。 • ジョイン・インデックスを使用すると、多くの場合、ジョイン・クエ リはアドホック・ジョインより高速に実行されますが、より多くの ディスク領域が必要となります。ただし、ジョイン・クエリがマルチ テー
クエリ構築のヒント UNION ALL での GROUP BY がクエリ・パフォーマンスに与える影響 パフォーマンスを向上させるために、非常に大きなテーブルを複数の小さな テーブルにセグメント化し、ビューで UNION ALL を使用してアクセスするこ とがよくあります。このようなビューを GROUP BY とともに使用する特定の 非常に個別的なクエリでは、Sybase IQ オプティマイザがいくつかの GROUP BY 処理を UNION ALL の各分岐に挿入して、処理を並列に実行し、結果を結 合することでパフォーマンスを向上させることができます。分割 GROUP BY と呼ばれるこの方法では、最上位レベルの GROUP BY で処理されるデータの 量が減少し、その結果、クエリ処理時間が減少します。 パフォーマンスが向上するのは、UNION ALL で GROUP BY を使用する特定 のクエリだけです。たとえば、次の簡単なクエリは分割 GROUP BY によって パフォーマンスが向上します。 CREATE VIEW vtable (v1 int, v2 char(4)) AS SELECT a1, a
第3章 • クエリと削除の最適化 クエリ内の集合関数で DISTINCT が指定されていない場合に、 分割 GROUP BY によってクエリのパフォーマンスが向上する可能性があります。次の クエリでは SUM DISTINCT を使用しているため、分割 GROUP BY による メリットはありません。 CREATE VIEW viewA (va1 int, va2 int, va3 int, va4 int) AS SELECT b1, b2, b3, b4 FROM tableB UNION ALL SELECT c1, c2, c3, c4 FROM tableC; SELECT SUM(DISTINCT va1) FROM viewA GROUP BY va3; • 分割 GROUP BY によってクエリのパフォーマンスを向上させるには、追 加の GROUP BY 演算子の処理に使われる集合情報とデータを格納するた めに、テンポラリ共有バッファ・キャッシュに十分なメモリが必要です。 CREATE VIEW viewA (va1 int, va2 int, va3 int, va4 int) A
クエリ構築のヒント • 分割 GROUP BY の例 分割 GROUP BY によってクエリのパフォーマンスを向上させるには、 AGGREGATION_PREFERENCE データベース・オプションをデフォルト値 の 0 に設定します。これにより、Sybase IQ オプティマイザは GROUP BY に 適用する最善のアルゴリズムを判断できるようになります。Sybase IQ オプ ティマイザが GROUP BY の処理にソート・アルゴリズムを選択するように AGGREGATION_PREFERENCE の値が設定されている場合は、分割 GROUP BY によるメリットはありません。AGGREGATION_PREFERENCE オプショ ンを使用すると、オプティマイザが GROUP BY の処理に選択するアルゴリ ズムを上書きできます。分割 GROUP BY では、この値を 1 または 2 に設定 しないでください。 次の例では、tableA という大きなテーブルを、tabA1、tabA2、tabA3、tabA4 という 4 つの小さなテーブルにセグメント化しています。この 4 つの小さな テーブル
第3章 クエリと削除の最適化 Sybase IQ と Adaptive Server Anywhere の違いの詳細については、 『Sybase IQ リ ファレンス・マニュアル』の「付録 A 他の Sybase データベースとの互換性」 を参照してください。 クエリ・プラン 最も効果的な構文を使用していなくても、正しいインデックスを作成していれ ば、通常は Sybase IQ クエリ・オプティマイザによって、最も効率的な方法で クエリを実行できます。もちろん、クエリを正しく設計することは重要です。 クエリを計画する場合に、クエリの実行速度と得られる結果の正確さが主要な 問題点となります。 クエリを実行する前に、Sybase IQ クエリ・オプティマイザはクエリ・プラン を作成します。Sybase IQ では、これ以降の項で説明するオプションを使用し て、クエリ・プランを調査および変更し、クエリを評価できます。このオプ ションを指定する方法の詳細については、 『Sybase IQ リファレンス・マニュア ル』を参照してください。 注意 整数値を指定できるデータベース・オプションでは、小数の option
クエリ・プラン • NOEXEC － このオプションを ON に設定すると、Sybase IQ はクエ リ・プランを生成しますが、クエリを実行しません。ただし、 EARLY_PREDICATE_EXECUTION オプションが ON の場合を除き ます。 • QUERY_DETAIL － このオプションと、QUERY_PLAN または QUERY_PLAN_AS_HTML の両方が ON の場合、Sybase IQ はクエ リ・プランを生成するときに、クエリについての追加情報を表示し ます。QUERY_PLAN と QUERY_PLAN_AS_HTML が OFF の場合、 このオプションは無視されます。 • QUERY_PLAN － このオプションが ON に設定されている場合 ( デフォル ト )、Sybase IQ はクエリについてのメッセージを生成します。ジョイン・ インデックスの使用方法、ジョイン順序、クエリのジョイン・アルゴリズ ムについてのメッセージなどが生成されます。 • QUERY_PLAN_AFTER_RUN － このオプションを ON に設定すると、ク エリの実行が終了し
第3章 クエリと削除の最適化 クエリ・ツリー オプティマイザは、クエリ内のデータの流れを表すクエリ「ツリー」を作成し ます。クエリ・プランでは、クエリ・ツリーが .iqmsg ファイル内にテキスト 形式で表示されます。オプションで、グラフィカル形式のクエリ・ツリーも作 成できます。 クエリ・ツリーはノードで構成されます。それぞれのノードは処理の段階を表 します。ツリーの一番下のノードはリーフ・ノードです。各リーフ・ノード は、クエリ内のテーブルまたはプリジョイン・インデックス・セットを表し ます。 プランの最上部にあるのは、演算子ツリーのルートです。情報はテーブルから 上方向に、ジョイン、ソート、フィルタ、格納、集合、サブクエリを表す演算 子を通じて流れます。 HTML クエリ・プランの使用 クエリ・プランを初めて使用するときは、QUERY_PLAN_AS_HTML オプショ ンを ON に設定することをおすすめします。このオプションを設定すると、 .
クエリ処理の制御 クエリの優先度の設定 処理をキューで待機しているクエリは、そのクエリを送信したユーザの優先 度、そしてクエリが送信された順序の順に実行されます。優先度の高いクエリ がすべて実行されるまで、優先度の低いキューのクエリは実行されません。 次のオプションは、クエリにユーザ別の処理の優先度を割り当てます。 • IQGOVERN_PRIORITY － 処理キューで待機しているクエリに数字の優 先度 (1、2、または 3 で、1 が最も高い ) を割り当てます。 • IQGOVERN_MAX_PRIORITY － DBA はユーザまたはグループの IQGOVERN_PRIORITY に上限値を設定できます。 • IQ_GOVERN_PRIORITY_TIME － 優先度の高い ( 優先度 1 の ) クエリが、 指定した時間より長く -iqgovern キューで待機している場合に、優先度の 高いユーザを開始できます。 クエリの優先度を調べるには、sp_iqcontext ストアド・プロシージャによって 返される IQGovernPriority 属性を確認します。 クエリ最適化オプション
第3章 クエリと削除の最適化 • ENABLED_ORDERED_PUSHDOWN_INSERTION － クエリ・オプティ マイザが、ジョイン・オプティマイザによって選択されたプッシュダウ ン・ジョイン用のセミジョイン述部に追加する方法を制御します。それら のセミジョインによって間接的に影響を受ける可能性のある中間のジョ インを再分析します。通常は、このオプションを変更しないでください。 • IN_SUBQUERY_PREFERENCE － IN サブクエリを処理するためのアル ゴリズムの選択を制御します。このオプションは、主に内部用として設計 されているため、経験のあるデータベース管理者のみが使用してください。 • INDEX_PREFERENCE － クエリ処理に使用するインデックスを設定し ます。Sybase IQ オプティマイザは、通常最適なインデックスを使用して、 ローカルな WHERE 句 の述部など、1 つの IQ インデックスの範囲内で処 理できる操作を実行します。このオプションは、テスト目的にオプティマ イザの選択を無効にするために使用します。通常の使用の際はこのオプ ション
削除オペレーションの最適化 通常の状況では、評価を遅らせることにメリットはなく、クエリの処理が遅く なるだけです。ただし、これによって次の 4 つの動作のいずれかをクエリ内の もっと後ろに移動できます。 • 最適化の前 • 初回の「最初のフェッチ」時 • 2 回目の「最初のフェッチ」時 ( 相関サブクエリの内部またはネストルー プ・プッシュダウン・ジョインの左側のみ ) • インデックスの不使用 ( 水平処理 ) 構文、パラメータ、使用例については、 『Sybase IQ リファレンス・マニュアル』 の「第 3 章 SQL 言語の要素」の「ユーザ指定の条件ヒント文字列」を参照し てください。 削除オペレーションの最適化 Sybase IQ は、削除オペレーションを処理するために次の 3 つのアルゴリズム から 1 つを選択します。 • スモール・デリート スモール・デリートでは、非常に少数のグループからローを削除するとき に最適なパフォーマンスが得られます。通常は、削除するローが 1 つだけ か、HG (High_Group) インデックスを持つカラムに等号述部がある場合に 選択されます
第3章 クエリと削除の最適化 削除コスト 12.
削除オペレーションの最適化 42 Sybase IQ
第 4 章 この章について OLAP の使用 オンライン分析処理 (OLAP: Online Analytical Processing) は、リレーショナル・ データベースに格納されている情報を効率的に分析するための手法です。 OLAP を使用すると、データをさまざまな次元で分析し、小計ローを含んだ 結果セットを取得し、データを多次元キューブに編成するという処理をす べて １ つの SQL クエリで行うことができます。また、フィルタを使用して データを絞り込み、結果セットを迅速に返すことができます。この章では、 Sybase IQ がサポートする SQL/OLAP 関数について説明します。 注意 以降で紹介する OLAP の例に出てくるテーブルは、asiqdemo データ ベースに含まれています。 内容 トピック名 OLAP について ページ 44 GROUP BY 句の拡張機能 47 統計関数 61 単純な集合関数 61 ウィンドウ 62 ランク付け関数 75 ウィンドウ集合関数 80 統計集合関数 81 分散統計関数 82 数値関数 85 OLAP
OLAP について OLAP について 1999 年の SQL 標準の改正によって、ANSI SQL 標準に複雑なデータ分析操作 を行うための拡張機能が導入されました。Sybase IQ では、以前のリリースで これらの SQL 拡張機能の一部が取り入れられていますが、Sybase IQ 12.
第4章 OLAP の使用 OLAP の利点 OLAP 関数を GROUPING、CUBE、ROLLUP という拡張機能と組み合わせて 使用すると、2 つの大きな利点があります。第一に、多次元のデータ分析、デー タ・マイニング、時系列分析、傾向分析、コストの割り当て、ゴール・シー ク、一時的な多次元構造変更、非手続き型モデリング、例外の警告を多くの場 合 1 つの SQL 文で実行できます。第二に、OLAP のウィンドウおよびレポー ト集合関数では、ウィンドウという関係演算子を使用することができ、これは セルフジョインや相関サブクエリを使用するセマンティック的に等価なクエ リよりも効率的に実行できます。OLAP を使用して取得した結果セットには小 計ローを含めることができ、この結果セットを多次元キューブに編成すること もできます。詳細については、 「ウィンドウ」(62 ページ ) を参照してください。 さまざまな期間での移動平均と移動和を計算したり、選択したカラムの値が変 化したときに集計とランクをリセットしたり、複雑な比率を単純な言葉で表現 したりできます。1 つのクエリ式のスコープ内で、それぞれ独自のパ
OLAP について 4 GROUP BY 句の式の重複しない値に基づいて、結果のローがグループ化 されます (NULL はそれぞれのドメインで特殊な値として扱われます )。 PARTITION BY 句がある場合は、GROUP BY 句の式はパーティション・ キーとして使用されます。 5 各パーティションについて、SELECT リストまたは HAVING 句の集合関 数が評価されます。いったん集合関数を適用すると、中間の結果セットに は個々のテーブル・ローが含まれなくなります。新しい結果セットには、 GROUP BY の式と、各パーティションについて計算した集合関数の値が 含まれます。 6 HAVING 句の条件が結果グループに適用されます。HAVING 句の条件を満 たさないグループが除外されます。 7 PARTITION BY 句で定義された境界に基づいて結果が分割されます。結果 ウィンドウについて、OLAP ウィンドウ関数 ( ランク付け関数および集合 関数 ) が計算されます。 図 4-1: 実行のセマンティック・フェーズ 詳細については、 「文法規則 2」(98 ページ ) を参照
第4章 OLAP の使用 GROUP BY 句の拡張機能 GROUP BY 句の拡張機能により、次のような処理を行う複雑な SQL 文を書く ことができます。 • 入力ローを複数の次元に分割し、結果グループの複数のサブセットを組み 合わせる。 • “ データ・キューブ ” を作成し、データ・マイニング分析のための疎密度 の多次元結果セットを用意する。 • 元のグループを含んだ結果セットを作成する ( 必要に応じて、小計ローと 合計ローを含める場合もある )。 ROLLUP や CUBE などの OLAP の Grouping() ( グループ化 ) 操作は、プレフィ クスや小計ローとして概念化できます。 プレフィクス GROUP BY 句を含むクエリでは、プレフィクスのリストが作成されます。プ レフィクスとは、GROUP BY 句の項目のサブセットであり、クエリの GROUP BY 句の項目のうち最も右にある 1 つまたは複数の項目を除外することで作成 されます。残りのカラムはプレフィクス・カラムと呼ばれます。 ROLLUP 例 1 次に示す ROLLUP のクエリの例では、GROUP BY
GROUP BY 句の拡張機能 GROUP BY での ROLLUP と CUBE プレフィクスに関する一般的なグループ化を簡単に指定するために、2 つの重 要な構文簡略化パターンが用意されています。1 つ目のパターンは ROLLUP、 2 つ目のパターンは CUBE と呼ばれます。 GROUP BY ROLLUP ROLLUP 演算子には、引数として適用するグループ化の式を、次の構文の中 で順序リストで指定します。 SELECT … [ GROUPING (column-name) … ] … GROUP BY [ expression [, … ] | ROLLUP ( expression [, … ] ) ] GROUPING は、カラム名をパラメータとして受け取り、表 4-1 に示すように ブール値を返します。 表 4-1: ROLLUP 演算子が指定された GROUPING によって返される値 結果値の種類 GROUPING の戻り値 ROLLUP 演算子によって作成された NULL 1(真) ローが小計であることを示す NULL 1(真) ROLLUP 演算子によって作成された
第4章 ROLLUP クエリの例 SELECT A, B, C, SUM( D ) FROM T1 GROUP BY ROLLUP (A, B, C); OLAP の使用 ROLLUP を使用せずに記述した同じ内容のクエリ SELECT * FROM ( ( SELECT A, B, C, SUM( D ) GROUP BY A, B, C ) UNION ALL ( SELECT A, B, NULL, SUM( D ) GROUP BY A, B ) UNION ALL ( SELECT A, NULL, NULL, SUM( D ) GROUP BY A ) UNION ALL ( SELECT NULL, NULL, NULL, SUM( D ) ) ) 小計ローはデータの分析に役立ちます。特に、データが大量にある場合、デー タにさまざまな次元がある場合、データがさまざまなテーブルに含まれている 場合、あるいはまったく異なるデータベースに含まれている場合に威力を発揮 します。たとえば販売マネージャが、売上高についてのレポートを営業担当者 別、地域別、四半期別に整理して、売上パターンの理解に役立
GROUP BY 句の拡張機能 次の例では、結果セットの中に Grouping() カラムが含まれています。強調表示 されているローは、小計ローであるために NULL を含んでいるのではなく、入 力データの結果として NULL を含んでいるローです。Grouping() カラムは強調 表示されています。このクエリは、employee テーブルと sales_order テーブ ルの間の外部ジョインです。このクエリでは、テキサス、ニューヨーク、また はカリフォルニアに住んでいる女性従業員を選択しています。営業担当者でな い ( したがって売上がない ) 女性従業員については、カラムに NULL が表示さ れます。 SELECT employee.emp_id AS Employee, year(order_date) AS Year, COUNT(*) AS Orders, GROUPING(Employee) AS GE, GROUPING(Year) AS GY FROM employee LEFT OUTER JOIN alt_sales_order ON employee.
第4章 OLAP の使用 小計ローの各カラムの値は、次のようになっています。 • プレフィクスに含まれているカラム － そのカラムの値です。たとえば前 述のクエリでは、Year=2000 のローに関する小計ローの Year カラムの値 は 2000 になります。 • プレフィクスから除外されたカラム － NULL です。たとえば、Year カラ ムから成るプレフィクスにより生成された小計ローでは、Quarter カラム の値は NULL になります。 • 集合関数 － 除外されているカラムの値を計算した結果です。 小計値は、集計されたローではなく基本データのローに対して計算され ます。多くの場合、たとえば SUM や COUNT などでは結果は等しくなり ますが、AVG、STDDEV、VARIANCE などの統計関数では結果が異なっ てくるため、この区別は重要です。 ROLLUP 演算子には次の制限があります。 • ROLLUP 演算子は、COUNT DISTINCT と SUM DISTINCT を除き、GROUP BY 句で使用可能なすべての集合関数をサポートしています。 • ROL
GROUP BY 句の拡張機能 Large Large Medium Medium Medium One size One size One size One size Small Small fits fits fits fits all all all all (NULL) Sweatshirt (NULL) Shorts Tee Shirt (NULL) Baseball Cap Tee Shirt Visor (NULL) Tee Shirt (NULL) (NULL) (NULL) (NULL) (NULL) (NULL) (NULL) (NULL) (NULL) (NULL) (NULL) 71 71 134 80 54 263 124 75 64 28 28 0 0 0 0 0 0 0 0 0 0 0 1 0 1 0 0 1 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 ROLLUP 例 3 次の例は、GROUPING を使用して、最初から格納されていた NULL 値と ROLLUP 操作によって生成された “NULL” 値とを区別する方法を 示しています。
第4章 2001 2001 2001 2001 501 600 601 700 936 936 792 1836 2001 2001 2001 2001 OLAP の使用 501 600 601 700 ROLLUP 例 4 次のクエリ例は、注文数を年別および四半期別に集計したデー タを返します。 SELECT year(order_date) AS Year, quarter(order_date) AS Quarter, COUNT(*) AS Orders FROM alt_sales_order GROUP BY ROLLUP(Year, Quarter) ORDER BY Year, Quarter 次の図は、このクエリの結果を示しています。結果セット内の小計ローは強調 表示されています。各小計ローでは、その小計の計算対象になったカラムに NULL 値が格納されています。 ロー [1] は、両方の年 (2000 年および 2001 年 ) のすべての四半期の注文数の合 計を示しています。このローは、Year カラムと Quarter カラムの両方が NULL であり、すべてのカラム
GROUP BY 句の拡張機能 この ROLLUP 操作の例では、年別、四半期別、地域別の注文数 を集計するというやや複雑な結果セットを返します。この例では、第 1 および 第 2 四半期と 2 つの地域 ( カナダと東部地区 ) だけを分析します。 ROLLUP 例 5 SELECT year(order_date) AS Year, quarter(order_date) AS Quarter, region, COUNT(*) AS Orders FROM alt_sales_order WHERE region IN ('Canada', 'Eastern') AND quarter IN (1, 2) GROUP BY ROLLUP (Year, Quarter, Region) ORDER BY Year, Quarter, Region 次の図は、このクエリの結果セットを示しています。各小計ローでは、その小 計の計算対象になったカラムに NULL が格納されています。 ロー [1] はすべてのローの集計結果であり、Year、Quarter、Region カラムに NULL が含まれてい
第4章 OLAP の使用 [3] としてマークされているローは、特定の年および四半期の全地域の注文数 の合計を示しています。 [4] としてマークされているローは、結果セット内のそれぞれの年の各四半期 の各地域の注文の合計数を示しています。 パフォーマンス＆チューニング・ガイド 55
GROUP BY 句の拡張機能 GROUP BY CUBE GROUP BY 句の CUBE 演算子は、データを複数の次元 ( グループ化の式 ) で グループ化することでデータを分析します。CUBE に次元の順序リストを引数 として指定すると、SELECT 文の中で、そのクエリに指定した次元の考えら れるすべての組み合わせの小計を計算し、選択した複数のカラムのすべての値 の組み合わせについての要約を示す結果セットを生成することができます。 CUBE の構文は次のとおりです。 SELECT … [ GROUPING (column-name) … ] … GROUP BY [ expression [,… ] | CUBE ( expression [,… ] ) ] GROUPING は、カラム名をパラメータとして受け取り、表 4-2 に示すように ブール値を返します。 表 4-2: CUBE 演算子が指定された GROUPING によって返される値 結果値の種類 GROUPING の戻り値 CUBE 演算子によって作成された NULL 1(真) ローが小計であることを示す NULL 1(真)
第4章 • OLAP の使用 GROUP BY のキーに定数式を指定することはできません。 注意 キューブのサイズがテンポラリ・キャッシュのサイズを超えると、CUBE のパフォーマンスが低下します。 GROUPING と CUBE 演算子を併用すると、格納されていた NULL 値と CUBE によって作成されたクエリ結果の “NULL” 値を区別することができます。 GROUPING 関数を使用して結果を分析する方法については、ROLLUP 演算子 の説明で紹介した例を参照してください。 CUBE 操作が返す結果セットには、集計カラムを除くすべてのカラムの値が NULL であるローが少なくとも 1 つは含まれています。このローは、集合関数 に対する全カラムの要約を表しています。 CUBE 例 1 次の例は、対象者の州 ( 地理的な位置 )、性別、教育レベル、およ び収入などで構成される調査データを使用したクエリです。最初に紹介するク エリには GROUP BY 句が指定されています。この句は、クエリの結果を census テーブルの state、gender、education カラムの値に応じてロ
GROUP BY 句の拡張機能 GROUP BY 句の CUBE 拡張機能を使用すると、調査データを 1 回参照するだ けで、調査データ全体における州別、性別、教育別の平均収入を計算し、state、 gender、education カラムの考えられるすべての組み合わせにおける平均収入 を計算することができます。CUBE 演算子を使用すると、たとえば、すべての 州における全女性の平均収入を計算したり、調査対象者全員の平均収入を、各 自の教育別および州別に計算したりすることができます。 CUBE でグループを計算するときには、計算されたグループのカラムに NULL 値が挿入されます。最初からデータベース内に格納されていた NULL なのか、 CUBE の結果として生成された NULL なのかを区別するためには、GROUPING 関数を使用する必要があります。GROUPING 関数は、指定されたカラムが上位 レベルのグループにマージされている場合は 1 を返します。 CUBE 例 2 次のクエリは、GROUP BY CUBE と GROUPING 関数を併用する 方法を示しています。 SELECT case g
第4章 CA CA CA CA CA MA MA MA MA MA MA MA MA MA MA MA MA MA MA MA MA MA MA ALL F F M M ALL ALL ALL ALL ALL ALL F F F F F F M M M M M M ALL 200 ALL 200 ALL 100 200 300 400 500 ALL 100 200 300 400 500 ALL 100 200 300 400 500 ALL 3 2 2 1 1 19 11 9 16 9 64 6 3 7 8 4 28 13 8 2 8 5 36 OLAP の使用 52200.00 58650.00 58650.00 39300.00 39300.00 58462.48 50530.73 59500.00 43640.67 33752.20 50064.78 58243.42 60451.00 59685.71 41959.88 29950.00 50146.16 58563.59 46810.63 58850.00 45321.47 36793.96 50001.
GROUP BY 句の拡張機能 次の図は、このクエリの結果セットを示しています。この結果セットでは、小 計ローが強調表示されています。各小計ローでは、その小計の計算対象になっ たカラムに NULL が格納されています。 先頭のロー [1] は、両方の年のすべての四半期の注文数の合計を示しています。 Orders カラムの値は、[3] としてマークされている各ローの値の合計です。こ れは、[2] としてマークされている 4 つのローの値の合計でもあります。 [2] としてマークされている一連のローは、両方の年の四半期別の注文数の合 計を示しています。[3] としてマークされている 2 つのローは、それぞれ 2000 年および 2001 年のすべての四半期の注文数の合計を示しています。 60 Sybase IQ
第4章 OLAP の使用 統計関数 Sybase IQ では、1 つの SQL 文内で複雑なデータ分析を実行できる機能を備え た単純な集合関数とウィンドウ集合関数の両方を提供しています。これらの関 数を使用して、たとえば “ ダウ工業株 30 種平均の四半期の移動平均はどうなっ ているか ” または “ 各部署のすべての従業員とその累積給与を一覧表示せよ ” というクエリに対する答えを計算することができます。さまざまな期間におけ る移動平均と累積和を計算したり、パーティション値が変化したときに集合計 算がリセットされるような方法で集計とランクを分割したりできます。1 つの クエリ式のスコープ内で、それぞれ独自のパーティショニング・ルールを持つ いくつかの異なる OLAP 関数を定義することができます。統計関数は 2 つのカ テゴリに分けられます。 • 単純な集合関数 (AVG、COUNT、MAX、MIN、SUM など ) は、データベー ス に 含 ま れるローのグループのデータを要約します。SELECT 文の GROUP BY 句を使ってグループを形成します。 • 1 つの引数を取る単項の統計集合
統計関数 ウィンドウ OLAP に関する ANSI SQL 拡張機能で導入された主な機能は、ウィンドウと呼 ぶ構造です。このウィンドウ拡張機能により、ユーザはクエリの結果セット ( または、クエリの論理パーティション ) をパーティションと呼ばれるローの グループに分割し、現在のローについて集計するローのサブセットを決定する ことができます。 1 つのウィンドウには 3 つのクラスのウィンドウ関数として、ランク付け関数、 ロー・ナンバリング関数、およびウィンドウ集合関数を使用できます。 ::= | ROW_NUMBER | 詳細については、 「文法規則 6」(98 ページ ) を参照してください。 ウィンドウ拡張機能は、ウィンドウ名または指定に対するウィンドウ関数の種 類を指定し、1 つのクエリ式のスコープ内のパーティション化された結果セッ
第4章 OLAP の使用 OLAP のウィンドウ指定に関して名前を指定することができます。冗長なウィ ンドウ定義を避けるために、この名前を使用して複数のウィンドウ関数を指定 できます。その場合は、キーワード WINDOW の後に少なくとも 1 つのウィン ドウ定義を指定します ( 複数指定する場合はカンマで区切ります )。ウィンド ウ定義には、クエリ内でウィンドウを識別するための名前と、ウィンドウの パーティション、順序、フレームを定義するためのウィンドウ指定の詳細を含 めます。 ::= ::= [ { } . . .
統計関数 ウィンドウ・パーティション ウィンドウ・パーティションとは、PARTITION BY 句を使用して、ユーザ指定 の結果セット ( 入力ロー ) を分割することです。パーティションは、カンマで 区切られた 1 つ以上の値の式によって定義されます。パーティションに分割さ れたデータは暗黙的にソートされ、デフォルトのソート順序は昇順 (ASC) にな ります。 ::= PARTITION BY ウィンドウ・パーティション句を指定しなかった場合は、入力が 1 つのパー ティションとして扱われます。 注意 統計関数に対してパーティションという用語を使用した場合は、結果 セットのローを PARTITION BY 句に基づいて分割することだけを意味します。 ウィンドウ・パーティションは任意の式に基づいて定義できます。また、ウィ ンドウ・パーティションの処理はグループ化の後に行われるので (GROUP BY 句が指定されている場合 )、SUM、AVG、VARIANCE などの集合関数の結果
第4章 OLAP の使用 OLAP のランク付け関数には順序の指定が必須であり、ランキング値の基準は、 ランク付け関数の引数ではなく ORDER BY 句で指定します。OLAP の集合関 数では、通常は ORDER BY 句の指定は必須ではありませんが、ウィンドウ・ フレームを定義するときには必須とされています (「ウィンドウ・フレーム」 (65 ページ ) を参照してください )。これは、各フレームの適切な集合値を計算 する前に、パーティション内のローをソートしなければならないためです。 この ORDER BY 句には、昇順および降順のソートを定義するためのセマン ティックと、NULL 値の取り扱いに関する規則を指定します。OLAP 関数は、 デフォルトでは昇順 (最も小さい値が 1 番目にランク付けされる) を使用します。 これは SELECT 文の最後に指定する ORDER BY 句のデフォルト動作と同じ ですが、連続的な計算を行う場合にはわかりにくいかもしれません。OLAP の 計算では、降順 ( 最も大きい値が 1 番目にランク付けされる ) でのソートが必 要になることがよくあります。この要件
統計関数 図 4-3: 分割された入力と、3 ロー分の移動ウィンドウ UNBOUNDED PRECEEDING と FOLLOWING ウィンドウ・フレームは、パーティションの先頭 (UNBOUNDED PRECEDING)、 最後 (UNBOUNDED FOLLOWING)、または両方まで到達する無制限の集合グ ループによって定義されます。 UNBOUNDED PRECEDING には、 パーティション内の現在のロー以前にあるすべ てのローが含まれており、ROWS または RANGE で指定できます。UNBOUNDED FOLLOWING には、パーティション内の現在のロー以後にあるすべてのローが含 まれており、ROWS または RANGE で指定できます。詳細については、 「ROWS」 (67 ページ ) と「RANGE」(70 ページ ) を参照してください。 FOLLOWING の値では、現在のロー以降にあるローの範囲または数を指定し ます。ROWS を指定する場合、その値には、ローの数を表す正の数を指定し ます。RANGE を指定する場合、そのウィンドウには、現在のローに指定の数 値を足した数
第4章 OLAP の使用 BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING の組 み合わせを使用すると、グループ化したクエリとのジョインを構築しなくて も、パーティション全体についての集合を計算できます。パーティション全体 についての集合は、レポート集合とも呼ばれます。 CURRENT ROW の概念 物理的な集合グループでは、現在のローに対する相対位置に基づき、隣接する ローの数に応じて、ローを含めるか除外するかが判断されます。現在のロー は、クエリの中間結果における次のローへの参照にすぎません。現在のローが 前に進むと、ウィンドウ内に含まれる新しいロー・セットに基づいてウィンド ウが再評価されます。現在のローをウィンドウ内に含めるという要件はありま せん。 ウィンドウ・フレーム句を指定しなかった場合のデフォルトのウィンドウ・フ レームは、ウィンドウ順序句を指定しているかどうかによって異なります。 • ウィンドウ指定にウィンドウ順序句が含まれている場合は、ウィンドウの 開始ポイントは UNBOUNDED PRECEDING、終了ポイントは CUR
統計関数 どのウィンドウ・フレームでも、現在のローが参照ポイントになります。 SQL/OLAP の構文には、ローベースのウィンドウ・フレームを、現在のローの 前または後にある任意の数のロー ( あるいは現在のローの前および後ろにある 任意の数のロー ) として定義するためのメカニズムが用意されています。 ウィンドウ・フレーム単位の代表的な例を次に示します。 • Rows Between Unbounded Preceding and Current Row － 各パーティションの 先頭を開始ポイントとし、現在のローを終了ポイントとするウィンドウを 指定します。累積和など、累積的な結果を計算するためのウィンドウを構 築するときによく使用されます。 • Rows Between Unbounded Preceding and Unbounded Following － 現在のローに 関係なく、パーティション全体についての固定ウィンドウを指定します。 そのため、ウィンドウ集合関数の値は、パーティションのすべてのローで 等しくなります。 • Rows Between 1 Preceding and 1 F
第4章 OLAP の使用 ローベースのウィンドウ・フレーム 図 4-4 の例では、 ロー [1] ～ [5] は 1 つのパー ティションを表しています。それぞれのローは、OLAP のウィンドウ・フレー ムが前にスライドするにつれて現在のローになります。このウィンドウ・フ レームは Between Current Row And 2 Following として定義されているため、各 フレームには、最大で 3 つ、最小で 1 つのローが含まれます。フレームがパー ティションの終わりに到達したときは、現在のローだけがフレームに含まれま す。網掛けの部分は、図 4-4 の各ステップでフレームから除外されているロー を表しています。 図 4-4: ローベースのウィンドウ・フレーム 図 4-4 のウィンドウ・フレームは、次のような規則で機能しています。 • ロー [1] が現在のローであるときは、ロー [4] および [5] が除外される。 • ロー [2] が現在のローであるときは、ロー [5] および [1] が除外される。 • ロー [3] が現在のローであるときは、ロー [1] および [2]
統計関数 平均値は次のようにして計算されています。 • ロー [1] = (10 + 50 + 100)/3 • ロー [2] = (50+ 100 + 120)/3 • ロー [3] = (100 + 120 + 500)/3 • ロー [4] = (120 + 500 + NULL)/3 • ロー [5] = (500 + NULL + NULL)/3 結果セット内の以降のすべてのパーティション ( たとえば B、C など ) につい ても、同様の計算が実行されます。 現在のウィンドウにローが含まれていない場合、COUNT 以外のケースでは、 結果は NULL になります。 RANGE 範囲ベースのウィンドウ・フレーム 前述のローベースのウィンドウ・フレームの 例では、さまざまなローベースのウィンドウ・フレーム定義の中から 1 つを紹 介しました。SQL/OLAP 構文では、また別の種類のウィンドウ・フレームとし て、物理的なローのシーケンスではなく、値ベース ( または範囲ベース ) の ロー・セットに基づいて境界を定義する方法が用意されています。 値ベースのウィンドウ・フレーム
第4章 OLAP の使用 このような範囲指定は内包的です。現在のローの year 値が 2000 である場合は、 ウィンドウ・パーティション内で、year 値が 2000 および 1999 であるすべての ローがこのフレームに含まれることになります。パーティション内での各ロー の物理的な位置は問われません。値ベースのフレームでは、ローを含めたり 除外したりする規則が、ローベースのフレームの規則とは大きく異なります ( ローベースのフレームの規則は、ローの物理的なシーケンスに完全に依存し ています )。 OLAP の AVG() 関数の例で考えてみます。次の部分的な結果セットは、値ベー スのウィンドウ・フレームの概念を具体的に表しています。前述のように、こ のフレームには次のローが含まれます。 • 現在のローと同じ year 値を持つロー • 現在のローから 1 を減算したのと同じ year 値を持つロー 次のクエリは、範囲ベースのウィンドウ・フレーム定義の例を示しています。 SELECT dimension, year, measure, AVG(measure) OVER(PARTITION B
統計関数 n FOLLOWING の指定には、次のような意味があります。 • パーティションがデフォルトの昇順 (ASC) でソートされている場合は、n は正の値として解釈されます。 • パーティションが降順 (DESC) でソートされている場合は、n は負の値と して解釈されます。 たとえば、year カラムに 1999 ～ 2002 の 4 種類の値が含まれているとします。 次のテーブルは、これらの値をデフォルトの昇順でソートした場合 ( 左側 ) と 降順でソートした場合 ( 右側 ) を示しています。 現在のローが 1999 で、フレームが次のように指定されている場合、このフレー ムには値 1999 のローと値 1998 のロー ( このテーブルには存在しません ) が含 まれます。 ORDER BY year ASC range BETWEEN CURRENT ROW and 1 FOLLOWING 注意 ORDER BY 値のソート順序は、値ベースのフレームに含まれるローの条 件をテストするときに重要な要素です。フレームに含まれるか除外されるか は、数値だけでは決まりません。 無制限ウ
第4章 OLAP の使用 このクエリの結果セットを次に示します。 emp_id -----102 105 160 243 247 249 266 278 316 445 453 479 501 529 582 604 839 1157 1250 emp_lname --------Whitney Cobb Breault Shishov Driscoll Guevara Gowda Melkisetian Pastor Lull Rabkin Siperstein Scott Sullivan Samuels Wang Marshall Soo Diaz curr --------45700.000 62000.000 57490.000 72995.000 48023.690 42998.000 59840.000 48500.000 74500.000 87900.000 64500.000 39875.500 96300.000 67890.000 37400.000 68400.000 42500.000 39075.000 54900.000 prev ---------64500.
統計関数 • インラインのウィンドウ指定。クエリ式の SELECT リスト内でウィンド ウを定義します。これにより、HAVING 句の後のウィンドウ句でウィンド ウを定義し、それをウィンドウ関数呼び出しから名前で参照するという方 法に加えて、関数呼び出しと一緒にウィンドウを定義するという方法が可 能になります。 注意 インラインのウィンドウ指定を使用する場合は、ウィンドウの名前 を指定できません。1 つの SELECT リスト内で複数のウィンドウ関数呼 び出しが同じウィンドウを使用する場合には、ウィンドウ句で定義した名 前付きウィンドウを参照するか、インラインのウィンドウ定義を繰り返す 必要があります。 ウィンドウ関数の例 ウィンドウ関数の例を次に示します。このクエリでは、デー タを部署別のパーティションに分け、在社年数が最も長い従業員を基点とした従 業員の累積給与を計算して、結果セットを返します。この結果セットには、マサ チューセッツ在住の従業員だけが含まれます。Sum_Salary カラムには、従業員 の給与の累積和が含まれます。 SELECT dept_id, emp_lname, start_d
第4章 200 200 200 200 200 200 200 200 200 200 200 Dill Powell Poitras Singer Kelly Martel Sterling Chao Preston Goggin Pickett 1985-12-06 1988-10-14 1988-11-28 1989-06-01 1989-10-01 1989-10-16 1990-04-29 1990-05-13 1990-07-11 1990-08-05 1993-08-12 54800.000 54600.000 46200.000 34892.000 87500.000 55700.000 64900.000 33890.000 37803.000 37900.000 47653.000 OLAP の使用 54800.000 109400.000 155600.000 190492.000 277992.000 333692.000 398592.000 432482.000 470285.000 508185.000 555838.
統計関数 これは次の指定に相当します。 ( COUNT (*) OVER ( ws RANGE UNBOUNDED PRECEDING ) - COUNT (*) OVER ( ws RANGE CURRENT ROW ) + 1 ) この RANK 関数の変換では、論理的な集合 (RANGE) を使用しています。この 結果、同位のロー ( 順序付けカラムに同じ値が含まれているロー ) が複数ある 場合は、それらに同じランクが割り当てられます。パーティション内で異なる 値を持つ次のグループには、同位のローのランクよりも 1 以上大きいランクが 割り当てられます。たとえば、順序付けカラムに 10、20、20、20、30 という値 を含むローがある場合、1 つ目のローのランクは 1 になり、2 つ目のローのラン クは 2 になります。3 つ目と 4 つ目のローのランクも 2 になりますが、5 つ目の ローのランクは 5 になります。ランクが 3 または 4 のローは存在しません。こ のアルゴリズムは非連続型ランキング (sparse ranking) とも呼ばれます。 『Sybase IQ リファレンス・マニ
第4章 OLAP の使用 PERCENT_RANK() 関数 PERCENT_RANK 関数は、個別の順位ではなく、パーセンテージでのランク を計算して、0 ～ 1 の小数値を返します。つまり、PERCENT_RANK が返すの はローの相対的なランクであり、この数値は、該当するウィンドウ・パーティ ション内での現在のローの相対位置を表します。たとえば、順序付けカラムの 値がそれぞれ異なる 10 個のローがパーティションに含まれている場合、この パーティションの 3 つ目のローに対する PERCENT_RANK の値は 0.222 ... と なります。パーティションの 1 つ目のローに続く 2/9 (22.222...
統計関数 Gowda Breault Diaz Melkisetian Driscoll Whitney Guevara Marshall Siperstein Soo Samuels 59840.000 57490.000 54900.000 48500.000 48023.690 45700.000 42998.000 42500.000 39875.500 39075.000 37400.
第4章 Samuels Singer Chao 37400.000 34892.000 33890.000 M M M OLAP の使用 19 20 21 ランク付けの例 3 この例では、カリフォルニアおよびテキサスの女性従業員を 取得し、給与を基準として降順にランク付けしています。累積和を降順で示す ために、PERCENT_RANK 関数を使用しています。 SELECT emp_lname, salary, sex, CAST(PERCENT_RANK() OVER (ORDER BY salary DESC) AS numeric (4, 2)) AS RANK FROM employee WHERE state IN ('CA', 'TX') AND sex ='F' ORDER BY salary DESC; このクエリの結果セットを次に示します。 emp_lname --------Savarino Smith Clark Garcia salary --------72300.000 51411.000 45000.000 39800.
統計関数 ウィンドウ集合関数 ウィンドウ集合関数を使用すると、複数のレベルの集合を 1 つのクエリで計算 できます。たとえば、支出が平均より少ない四半期をすべて列挙することがで きます。集合関数 (単純な集合関数 AVG、COUNT、MAX、MIN、SUM を含む ) を使用すると、1 つの文の中でさまざまなレベルで計算した結果を 1 つのローに 書き出すことができます。これにより、ジョインや相関サブクエリを使用しな くても、集合値をグループ内のディテール・ローと比較することができます。 これらの関数を使用して、非集合値と集合値を比較することも可能です。たと えば、営業部員が特定の年にある製品に対して平均以上の注文を出した顧客の 一覧を作成したり、販売マネージャが従業員の給与をその部署の平均給与と比 較したりすることが考えられます。 SELECT 文の中で DISTINCT が指定されている場合は、ウィンドウ演算子の 後に DISTINCT 操作が適用されます ( ウィンドウ演算子は、GROUP BY 句が 処理された後、SELECT リストの項目やクエリの ORDER BY 句が評価される 前に計算されま
第4章 OLAP の使用 ウィンドウ集合関数の例 2 この例のクエリは、給与の額がそれぞれの部署の平 均給与よりも 1 標準偏差以上高い従業員を表す結果セットを返します。標準偏 差とは、そのデータが平均からどのぐらい離れているかを示す尺度です。 SELECT * FROM (SELECT emp_lname AS E_name, dept_id AS Dept, CAST(salary AS numeric(10,2) ) AS Sal, CAST(AVG(Sal) OVER(PARTITION BY dept_id) AS numeric(10, 2)) AS Average, CAST(STDDEV_POP(Sal) OVER(PARTITION BY dept_id) AS numeric(10,2)) AS STD_DEV FROM employee GROUP BY Dept, E_name, Sal) AS derived_table WHERE Sal> (Average+STD_DEV ) ORDER BY Dept, Sal, E_name; このクエリの結果セットを次に示します。どの
統計関数 • STDDEV_POP － グループまたはパーティションの各ロー (DISTINCT が 指定されている場合は、重複が削除された後に残る各ロー ) に対して評価 される「値の式」についての母標準偏差を計算します。これは、母分散の 平方根として定義されます。 • STDDEV_SAMP － グループまたはパーティションの各ロー (DISTINCT が指定されている場合は、重複が削除された後に残る各ロー ) に対して評 価される「値の式」についての母標準偏差を計算します。これは、標本分 散の平方根として定義されます。 • VAR_POP － グループまたはパーティションの各ロー (DISTINCT が指定 されている場合は、重複が削除された後に残る各ロー ) に対して評価され る「値の式」についての母分散を計算します。これは、 「値の式」と「値 の式の平均」との差の 2 乗和をグループまたはパーティション内の残りの ローの数で割った値として定義されます。 • VAR_SAMP － グループまたはパーティションの各ロー (DISTINCT が指 定されている場合は、重複が削除された後に残
第4章 OLAP の使用 逆分散統計関数では、WITHIN GROUP (ORDER BY) 句を指定する必要があり ます。次に例を示します。 PERCENTILE_CONT ( expression1 ) WITHIN GROUP ( ORDER BY expression2 [ ASC | DESC ] ) expression1 の値には、numeric データ型の定数を、0 以上 1 以下の範囲で指定し ます。引数が NULL であれば、“wrong argument for percentile” エラーが返りま す。引数の値が 0 よりも小さいか、1 よりも大きい場合は、“data value out of range” エラーが返ります。 必須の ORDER BY には、パーセンタイル関数の実行の対象となる式と、各グ ループ内でのローのソート順を指定します。この ORDER BY 句は、WITHIN GROUP 句の内部でのみ使用するものであり、SELECT 文の ORDER BY とは 異なります。 WITHIN GROUP 句は、クエリの結果を並べ替えて、関数が結果を計算するた めのデ
統計関数 次のクエリ例では、SELECT 文に PERCENTILE_CONT 関数を含めています。 SELECT region, PERCENTILE_CONT(0.
第4章 OLAP の使用 分散統計関数の詳細については、 『Sybase IQ リファレンス・マニュアル』の「第 5 章 SQL 関数」の「PERCENTILE_CONT 関数 [ 統計 ]」と「PERCENTILE_DISC 関数 [ 統計 ]」を参照してください。 数値関数 Sybase IQ でサポートされる OLAP 数値関数には、 CEILING ( エイリアスは CEIL)、 EXP ( エイリアスは EXPONENTIAL)、FLOOR、LN ( エイリアスは LOG)、SQRT、 WIDTH_BUCKET があります。 :: = | | | | | | サポートされる数値関数の構文を表 4-3 に示します。 表 4-3: 数値関数の構文 数値関数 自然対数 構文 LENG
統計関数 WIDTH_BUCKET 関数 • FLOOR: 引数の値以下で、正の無限大に最も近い整数値を返します。 • CEILING: 引数の値以上で、負の無限大に最も近い整数値を返します。CEIL は CEILING の同意語です。 WIDTH_BUCKET 関数は、他の数値関数よりも少し複雑です。この関数は 4 つ の引数を取ります。具体的には、 「目的の値」、2 つの範囲境界、そしてこの範 囲を何個の等しいサイズ ( または可能な限り等しいサイズ ) の「バケット」に 分割するかを指定します。WIDTH_BUCKET 関数は、範囲の上限から下限ま での差のパーセンテージに基づき、目的の値が何番目のバケットに含まれるか を示す数値を返します。最初のバケットが、バケット番号 1 となります。 目的の値が範囲境界の外にある場合のエラーを避けるために、範囲の下限より も小さい目的の値は、先頭の補助バケット ( バケット 0) に配置されます。同 様に、範囲の上限よりも大きい目的の値は、末尾の補助バケット ( バケット N+1) に配置されます。 たとえば、WIDTH_BUCKET (14, 5,
第4章 OLAP の使用 注意 これは説明用の例であり、asiqdemo データベースから生成したものでは ありません。 SELECT customer_id, cust_last_name, credit_limit, WIDTH_BUCKET(credit_limit, 100, 5000, 10) "Credit Group" FROM customers WHERE territory = 'MA' ORDER BY "Credit Group"; CUSTOMER_ID ----------825 826 853 827 843 844 835 840 842 841 837 836 848 849 828 829 852 851 850 830 831 832 838 839 833 834 845 846 847 CUST_LAST_NAME -------------Dreyfuss Barkin Palin Siegel Oates Julius Eastwood Elliott Stern Boyer Stanton Berenger Olmos Kaurusmdki Minnel
OLAP の規則と制限 参照 『Sybase IQ リファレンス・マニュアル』の「第 5 章 SQL 関数」の「BIT_LENGTH 関数 [ 文字列 ]」、 「EXP 関数 [ 数値 ]」、 「FLOOR 関数 [ 数値 ]」、 「POWER 関数 [ 数値 ]」、 「SQRT 関数 [ 数値 ]」、 「WIDTH_BUCKET 関数 [ 数値 ]」を参照して ください。 OLAP の規則と制限 OLAP 関数を使用できる 場合 OLAP 関数を使用でき ない場合 SQL クエリ内では、次の条件下で OLAP 関数を使用できます。 • SELECT リストの中 • 式の中 • スカラ関数の引数として • 最後の ORDER BY 句の中 ( クエリ内のどこかで定義されている OLAP 関 数のエイリアスまたは位置参照を使用 ) OLAP 関数は、次の条件下では使用できません。 • サブクエリの中 • WHERE 句の検索条件の中 • SUM ( 集合 ) 関数の引数としてたとえば次の式は無効です。 SUM(RANK() OVER(ORDER BY dollars)) •
第4章 Sybase IQ の制限事項 OLAP の使用 Sybase IQ で SQL OLAP 関数を使用するときの制限事項を次に示します。 • ウィンドウ・フレーム定義の中でユーザ定義関数を使用することはできま せん。 • ウィンドウ・フレーム定義で使用する定数は符号なし数値でなければな らず、最大値 BIG INT 263-1 を超えてはなりません。 • ウィンドウ集合関数と RANK 関数は、DELETE および UPDATE 文では使 用できません。 • ウィンドウ集合関数と RANK 関数は、サブクエリ内では使用できません。 • CUME_DIST は、現時点ではサポートされていません。 • グループ化セットは、現時点ではサポートされていません。 • 相関関数と直線回帰関数は、現時点ではサポートされていません。 その他の OLAP の例 この項では、OLAP 関数を使用したその他の例を紹介します。 ウィンドウの開始ポイントと終了ポイントは、中間の結果ローが処理されると きに変化する可能性があります。たとえば、累積和を計算する場合には、ウィ ンドウの開始ポイントは
その他の OLAP の例 このクエリの結果セットを次に示します。 ID --302 400 400 401 401 401 500 501 501 501 501 601 700 700 description quantity -----------------Crew Neck 60 Cotton Cap 60 Cotton Cap 48 Wool cap 48 Wool cap 60 Wool cap 48 Cloth Visor 48 Plastic Visor 60 Plastic Visor 48 Plastic Visor 48 Plastic Visor 60 Zipped Sweatshirt 60 Cotton Shorts 72 Cotton Shorts 48 ship_date --------2001-07-02 2001-05-26 2001-07-05 2001-06-02 2001-06-30 2001-07-09 2001-06-21 2001-05-03 2001-05-18 2001-05-25 2001-07-07 2001-07-19 2001-05-18 2
第4章 OLAP の使用 このクエリ指定では、ウィンドウ句が ORDER BY 句の前にあることに注意し てください。ウィンドウ句を使用するときには、次の制限が適用されます。 • インラインのウィンドウ指定に PARTITION BY 句を含めることはできま せん。 • ウィンドウ句で指定されるウィンドウにウィンドウ・フレーム句を含める ことはできません。たとえば、 「文法規則 32」(100 ページ ) に次のように 記述されています。 ::= • インラインのウィンドウ指定にもウィンドウ句のウィンドウ指定にも ウィンドウ順序句を含めることができますが、両方に含めることはできま せん。たとえば、 「文法規則 31」(100 ページ ) に次のように記述されてい ます。 ::= 例： 複数の関数で使用されるウィンドウ 1 つの名前付きウィンドウを定義しておき、そのウィンドウに基づい
その他の OLAP の例 例：累積和の計算 このクエリでは、ORDER BY start_date の順序に従って、部署別の給与の累 積和を計算します。 SELECT dept_id, start_date, name, salary, SUM(salary) OVER (PARTITION BY dept_id ORDER BY start_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM emp1 ORDER BY dept_id, start_date; このクエリの結果セットを次に示します。 dept_id ------100 100 100 100 100 100 200 200 200 200 300 300 300 start_date ---------1996-01-01 1997-01-01 1998-01-01 1998-02-01 1998-03-12 1998-12-01 1998-01-01 1998-01-20 1998-02-01 1999-01-10 1998-03-12 1998-12-01 199
第4章 10 20 20 20 20 20 20 30 30 30 30 6 1 2 3 4 5 6 1 2 3 4 110 20 30 25 30 31 20 10 11 12 1 OLAP の使用 120.00 20.00 25.00 25.00 28.33 28.66 27.00 10.00 10.50 11.00 8.
その他の OLAP の例 例：1 つのクエリ内で複数の集合関数を使用 この例では、1 つのクエリ内で、異なるウィンドウに対して 2 種類の集合関数 を実行しています。 SELECT prod_id, month_num, sales, AVG(sales) OVER (WS1 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS CAvg, SUM(sales) OVER(WS1 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CSum FROM sale WHERE rep_id = 1 WINDOW WS1 AS (PARTITION BY prod_id ORDER BY month_num) ORDER BY prod_id, month_num; このクエリの結果セットを次に示します。 prod_id ------10 10 10 10 10 10 20 20 20 20 20 20 30 30 30 30 month_num --------1 2 3 4 5 6 1 2 3 4 5 6 1 2 3
第4章 10 10 10 10 10 10 10 20 20 20 20 20 20 30 30 30 30 30 1 2 3 4 5 5 6 1 2 3 4 5 6 1 2 3 4 4 150 120 100 130 120 31 110 20 30 25 30 31 20 10 11 12 1 1 250 370 470 350 381 381 391 20 50 75 85 86 81 10 21 33 25 25 OLAP の使用 250 370 370 350 350 281 261 20 50 75 85 86 81 10 21 33 24 14 例： 現在のローを除外するウィンドウ・フレーム この例では、現在のローを除外するウィンドウ・フレームを定義しています。 このクエリは、現在のローを除く 4 つのローの合計を計算します。 SELECT prod_id, month_num, sales, sum(sales) OVER (PARTITION BY prod_id ORDER BY month_num RANGE BETWEEN 6 PRECEDING AND 2 PRECED
その他の OLAP の例 例：ROW のデフォルトのウィンドウ・フレーム このクエリは、ROW のデフォルトのウィンドウ・フレームの例を示しています。 SELECT prod_id, month_num, sales, SUM(sales) OVER (PARTITION BY prod_id ORDER BY month_num RANGE BETWEEN 1 FOLLOWING AND 3 FOLLOWING) FROM sale ORDER BY prod_id, month_num; このクエリの結果セットを次に示します。 prod_id ------10 10 10 10 10 10 10 10 20 20 20 20 20 20 30 30 30 30 30 month_num --------1 1 2 3 4 5 5 6 1 2 3 4 5 6 1 2 3 4 4 sales ----100 150 120 100 130 120 31 110 20 30 25 30 31 20 10 11 12 1 1 sum(sales) ---------350 350 381 391 261
第4章 10 10 20 20 20 20 20 20 30 30 30 30 5 6 1 2 3 4 5 6 1 2 3 4 120 110 20 30 25 30 31 20 10 11 12 1 OLAP の使用 680 680 156 156 156 156 156 156 34 34 34 34 このクエリは、次のクエリと同じ意味になります。 SELECT prod_id, month_num, sales, SUM(sales) OVER (PARTITION BY prod_id ) FROM sale WHERE rep_id = 1 ORDER BY prod_id, month_num; 例： RANGE のデフォルトのウィンドウ・フレーム このクエリは、RANGE のデフォルトのウィンドウ・フレームの例を示してい ます。 SELECT prod_id, month_num, sales, SUM(sales) OVER (PARTITION BY prod_id ORDER BY month_num) FROM sale ORDER BY prod_id, month_nu
OLAP 関数の BNF 文法 30 30 4 4 1 1 35 35 このクエリは、次のクエリと同じ意味になります。 SELECT prod_id, month_num, sales, SUM(sales) OVER (PARTITION BY prod_id ORDER BY month_num RANGE BETWEEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM sale ORDER BY prod_id, month_num; OLAP 関数の BNF 文法 次の BNF (Backus-Naur Form ) 文法は、さまざまな ANSI SQL 統計関数に関す る具体的な構文サポートの概要を示しています。ここに記載されている関数の 多くは Sybase IQ で実装されています。 文法規則 1
