Sybase IQ 12.7 User Manual

パフォーマンス＆チューニング・ガイド

Sybase® IQ

12.7

ドキュメント ID： DC00283-01-1270-01 改訂： 2006 年 6 月

このマニュアルは Sybase ソフトウェアの付属マニュアルであり、新しいマニュアルまたはテクニカル・ノートで特に示されないかぎりは、後続のリリースにも付属します。このマニュアルの内容は、予告なく変更されることがありますが、 Sybase, Inc. およびその関連会社では内容の変更に関して一切の責任を負いません。このマニュアルに記載されているソフトウェアはライセンス契約に基づいて提供されるものであり、無断で使用することはできません。

マニュアルの注文

マニュアルの注文を承ります。ご希望の方は、サイベース株式会社営業部または代理店までご連絡ください。マニュアルの変更は、弊社の定期的なソフトウェア・リリース時にのみ提供されます。このマニュアルの内容を弊社の書面による事前許可を得ずに電子的、機械的、手作業、光学的、またはその他のいかなる手段によっても複製、転載、翻訳することを禁じます。

Sybase、SYBASE ( ロゴ )、ADA Workbench、Adaptable Windowing Environment、Adaptive Component Architecture、Adaptive Server、Adaptive Server Anywhere、Adaptive Server Enterprise、Adaptive Server Enterprise Monitor、Adaptive Server Enterprise Replication、Adaptive Server Everywhere、Advantage Database Server、Afaria、Answers Anywhere、Applied Meta、Applied Metacomputing、AppModeler、APT Workbench、 APT-Build、APT-Edit、APT-Execute、APT-Translator、APT-Library、ASEP、Avak i、Avaki (Arrow Design)、Avaki Data Grid、Avan tG o、Backup Server、BayCam、Beyond Connected、Bit-Wise、BizTracker、Certified PowerBuilder Developer、Certified SYBASE Professional、Certified SYBASE Professional Logo、ClearConnect、Client-Library、Client Services、CodeBank、Column Design、ComponentPack、Connection Manager、Convoy/ DM、Copernicus、CSP、Data Pipeline、Data Workbench、DataArchitect、Database Analyzer、DataExpress、DataServer、DataWindow、 DataWindow .NET、DB-Library、dbQueue、Dejima、Dejima Direct、Developers Workbench、DirectConnect Anywhere、DirectConnect、Distribution Director、Dynamic Mobility Model、e-ADK、E-Anywhere、e-Biz Integrator、E-Whatever、EC Gateway、ECMAP、ECRTP、eFulfillment Accelerator、EII Plus、Electronic Case Management、Embedded SQL、EMS、Enterprise Application Studio、Enterprise Client/Server、Enterprise Connect、Enterprise Data Studio、Enterprise Manager、Enterprise Portal ( ロゴ )、Enterprise SQL Server Manager、Enterprise Work Architecture、 Enterprise Work Designer、Enterprise Work Modeler、eProcurement Accelerator、eremote、Everything Works Better When Everything Works Together、 EWA、ExtendedAssist、Extended Systems、ExtendedView、Financial Fusion、Financial Fusion ( および設計 )、Financial Fusion Server、Formula One、Fusion Powered e-Finance、Fusion Powered Financial Destinations、Fusion Powered STP、Gateway Manager、GeoPoint、GlobalFIX、 iAnywhere、iAnywhere Solutions、ImpactNow、Industry Warehouse Studio、InfoMaker、Information Anywhere、Information Everywhere、 InformationConnect、InstaHelp、Intelligent Self-Care、InternetBuilder、iremote、irLite、iScript、Jaguar CTS、jConnect for JDBC、KnowledgeBase、 Legion、Logical Memory Manager、M2M Anywhere、Mach Desktop、Mail Anywhere Studio、Mainframe Connect、Maintenance Express、Manage Anywhere Studio、MAP、M-Business Anywhere、M-Business Channel、M-Business Network、M-Business Suite、MDI Access Server、MDI Database Gateway、media.splash、Message Anywhere Server、MetaWorks、MethodSet、mFolio、Mirror Activator、ML Query、MobiCATS、MobileQ、 MySupport、Net-Gateway、Net-Library、New Era of Networks、Next Generation Learning、Next Generation Learning Studio、O DEVICE、OASiS、 OASiS logo、ObjectConnect、ObjectCycle、OmniConnect、OmniQ、OmniSQL Access Module、OmniSQL Toolkit、OneBridge、Open Biz、Open Business Interchange、Open Client、Open ClientConnect、Open Client/Server、Open Client/Server Interfaces、Open Gateway、Open Server、Open ServerConnect、Open Solutions、Optima++、Partnerships that Work、PB-Gen、PC APT Execute、PC DB-Net、PC Net Library、Pharma Anywhere、 PhysicalArchitect、Pocket PowerBuilder、PocketBuilder、Power++、Power Through Knowledge、power.stop、PowerAMC、PowerBuilder、 PowerBuilder Foundation Class Library、PowerDesigner、PowerDimensions、PowerDynamo、Powering the New Economy、PowerScript、PowerSite、 PowerSocket、Powersoft、PowerStage、PowerStudio、PowerTips、Powersoft Portfolio、Powersoft Professional、PowerWare Desktop、PowerWare Enterprise、ProcessAnalyst、Pylon、Pylon Anywhere、Pylon Application Server、Pylon Conduit、Pylon PIM Server、Pylon Pro、QAnywhere、 Rapport、Relational Beans、RemoteWare、RepConnector、Report Workbench、Report-Execute、Replication Agent、Replication Driver、Replication Server、Replication Server Manager、Replication Toolkit、Resource Manager、RFID Anywhere、RW-DisplayLib、RW-Library、SAFE、SAFE/PRO、 Sales Anywhere、Search Anywhere、SDF、Search Anywhere、Secure SQL Server、Secure SQL Toolset、Security Guardian、ShareSpool、ShareLink、 SKILS、smart.partners、smart.parts、smart.script、SOA Anywhere Trademark,SQL Advantage、SQL Anywhere、SQL Anywhere Studio、SQL Code Checker、SQL Debug、SQL Edit、SQL Edit/TPU、SQL Everywhere、SQL Modeler、SQL Remote、SQL Server、SQL Server Manager、SQL SMART、SQL Toolset、SQL Server/CFT、SQL Server/DBM、SQL Server SNMP SubAgent、SQL Station、SQLJ、Stage III Engineering、 Startup.Com、STEP、SupportNow、S.W.I.F.T. Message Format Libraries、Sybase Central、Sybase Client/Server Interfaces、Sybase Development Framework、Sybase Financial Server、Sybase Gateways、Sybase IQ、Sybase Learning Connection、Sybase MPP、Sybase SQL Desktop、Sybase SQL Lifecycle、Sybase SQL Workgroup、Sybase Synergy Program、Sybase Virtual Server Architecture、Sybase User Workbench、SybaseWare、Syber Financial、SyberAssist、SybFlex、SybMD、SyBooks、System 10、System 11、System XI ( ロゴ )、SystemTools、Tabular Data Stream、The Enterprise Client/Server Company、The Extensible Software Platform、The Future Is Wide Open、The Learning Connection、The Model For Client/Server Solutions、The Online Information Center、The Power of One、TotalFix、Trade Force 、Transa ct-SQ L、Translation Toolkit、Turning Imagination Into Reality、UltraLite、UltraLite.NET、UNIBOM、Unilib、Uninull、Unisep、Unistring、URK Runtime Kit for UniCode、Viafone、Viewer、 VisualWriter、VQL、WarehouseArchitect、Warehouse Control Center、Warehouse Studio、Warehouse WORKS、Wat com 、Watcom SQL、Watcom SQL Server、Web Deployment Kit、Web.PB、Web.SQL、WebSights、WebViewer、WorkGroup SQL Server、XA-Library、XA-Server、XcelleNet、 XP Server、XTNDAccess、および XTNDConnect は、米国法人 Sybase, Inc. およびその子会社の商標です。

Unicode と Unicode のロゴは、Unicode, Inc. の登録商標です。

このマニュアルに記載されている上記以外の社名および製品名は、各社の商標または登録商標の場合があります。

Use, duplication, or disclosure by the government is subject to the restrictions set forth in subparagraph (c)(1)(ii) of DFARS 52.227-7013 for the DOD and as set forth in FAR 52.227-19(a)-(d) for civilian agencies.

Sybase, Inc., One Sybase Drive, Dublin, CA 94568.

はじめに .................................................................................................................................................. ix

第 1 章データベース・テーブルからのデータの選択 ........................................ 1

前提条件 ................................................................................................. 2

テーブル情報の表示 ............................................................................... 3

クエリ結果の順序付け ........................................................................... 5

カラムとローの選択 ............................................................................... 6

探索条件の使用 ...................................................................................... 7

クエリでの日付の比較 .................................................................... 8

WHERE 句での複合探索条件 ......................................................... 8

探索条件でのパターン・マッチング............................................... 9

発音によるローのマッチング ....................................................... 10

探索条件を入力するためのショートカット.................................. 10

集約データの取得................................................................................. 11

集合関数の概要 ............................................................................. 11

集合関数によるグループ化されたデータの取得........................... 12

グループの制限 ............................................................................. 12

小計計算の活用 ............................................................................. 13

分析データの取得................................................................................. 17

重複したローの削除 ............................................................................. 18

第 2 章テーブルのジョイン.............................................................................. 19

外積を使用したテーブルのジョイン.................................................... 19

ジョインの制限 .................................................................................... 20

テーブル間の関係................................................................................. 21

プライマリ・キーによるローの識別............................................. 21

外部キーによって関連付けられたテーブル.................................. 22

ジョイン演算子 .................................................................................... 22

キー・ジョインを使用したテーブルのジョイン........................... 22

ナチュラル・ジョインを使用したテーブルのジョイン................ 24

アドホック・ジョインとジョイン・インデックスの使用 ................... 25

ジョインとデータ型 ............................................................................. 25

ストアまたはデータベース間ジョインのサポート .............................. 26

リモート・データベースと異種データベースのクエリ ....................... 27

サブクエリによるジョインの置き換え ................................................ 28

パフォーマンス＆チューニング・ガイド iii

第 3 章クエリと削除の最適化 .......................................................................... 31

クエリ構築のヒント............................................................................. 31

UNION ALL での GROUP BY がクエリ・パフォーマンスに

与える影響............................................................................. 32

Adaptive Server Anywhere による処理を引き起こす条件............ 34

クエリ・プラン.................................................................................... 35

クエリ評価オプション.................................................................. 35

クエリ・ツリー............................................................................. 37

HTML クエリ・プランの使用 ....................................................... 37

クエリ処理の制御 ................................................................................ 37

クエリの時間制限の設定 .............................................................. 37

クエリの優先度の設定.................................................................. 38

クエリ最適化オプションの設定 ................................................... 38

述部ヒントの設定 ......................................................................... 39

削除オペレーションの最適化 .............................................................. 40

削除コスト.................................................................................... 41

削除パフォーマンス・オプションの使用 ..................................... 41

第 4 章 OLAP の使用......................................................................................... 43

OLAP について .................................................................................... 44

OLAP の利点................................................................................. 45

OLAP の評価について .................................................................. 45

GROUP BY 句の拡張機能.................................................................... 47

GROUP BY での ROLLUP と CUBE............................................ 48

統計関数............................................................................................... 61

単純な集合関数............................................................................. 61

ウィンドウ.................................................................................... 62

数値関数........................................................................................ 85

OLAP の規則と制限............................................................................. 88

その他の OLAP の例 ............................................................................ 89

例：クエリ内でのウィンドウ関数................................................ 89

例：複数の関数で使用されるウィンドウ..................................... 91

例：累積和の計算......................................................................... 92

例：移動平均の計算 ..................................................................... 92

例： ORDER BY の結果................................................................ 93

例： 1 つのクエリ内で複数の集合関数を使用 .............................. 94

例：ウィンドウ・フレーム指定の ROWS と RANGE の比較 ..... 94

例：現在のローを除外するウィンドウ・フレーム....................... 95

例： ROW のデフォルトのウィンドウ・フレーム........................ 96

例： UNBOUNDED PRECEDING と

UNBOUNDED FOLLOWING ................................................. 96

例： RANGE のデフォルトのウィンドウ・フレーム.................... 97

OLAP 関数の BNF 文法 ....................................................................... 98

iv Sybase IQ

第 5 章システム・リソースの管理 ................................................................. 103

パフォーマンス用語の概要................................................................. 104

パフォーマンス向上のための設計...................................................... 104

メモリ使用の概要 ............................................................................... 105

ページングによる使用可能メモリの増加.................................... 105

スワッピングをモニタするためのユーティリティ ..................... 106

サーバ・メモリ ........................................................................... 106

バッファ・キャッシュの管理...................................................... 107

バッファ・キャッシュ・サイズの決定 ....................................... 107

バッファ・キャッシュ・サイズの設定 ....................................... 113

ページ・サイズの指定................................................................. 115

メモリの節約 ............................................................................... 116

ユーザが多数存在する場合の最適化........................................... 117

プラットフォーム固有のメモリ・オプション ............................ 119

メモリを増やすその他の方法...................................................... 122

プロセス・スレッド・モデル ............................................................. 123

スレッド不足エラー .................................................................... 124

スレッド使用を管理するための Sybase IQ オプション ............. 124

I/O の分散 ........................................................................................... 125

ロー I/O (UNIX オペレーティング・システム ) ........................... 125

ディスク・ストライピングの使用............................................... 125

内部ストライピング .................................................................... 127

複数の dbspace の使用 ............................................................... 128

戦略的なファイルの格納 ............................................................. 129

挿入、削除、同期のための作業領域........................................... 133

予約領域のオプションの設定...................................................... 133

リソース使用を調整するオプション .................................................. 133

同時クエリの制限........................................................................ 134

使用可能な CPU 数の設定........................................................... 134

クエリによるテンポラリ dbspace の使用の制限........................ 134

返されるローによるクエリの制限............................................... 135

カーソルのスクロールの禁止...................................................... 135

カーソル数の制限........................................................................ 135

文の数の制限 ............................................................................... 135

キャッシュ・ページのプリフェッチ........................................... 136

一般的な使用のための最適化...................................................... 136

プリフェッチされるローの数の制御........................................... 136

リソースを効率的に利用するための他の方法.................................... 137

マルチプレックス・データベースのディスク領域の管理 .......... 137

クエリ・サーバ間のロード・バランス ....................................... 137

データベース・アクセスの制限 .................................................. 137

ディスクのキャッシュ................................................................. 138

インデックスのヒント........................................................................ 138

正しいインデックス・タイプの選択........................................... 138

ジョイン・インデックスの使用 .................................................. 139

削除のための十分なディスク領域の確保.................................... 139

パフォーマンス＆チューニング・ガイド v

データベース・サイズと構造の管理 ................................................. 140

データベース・サイズの管理 ..................................................... 140

インデックスの断片化の制御 ..................................................... 140

カタログ・ファイル増大の最小化.............................................. 141

パフォーマンス向上のための非正規化....................................... 141

非正規化のリスク ....................................................................... 141

非正規化の短所........................................................................... 142

非正規化のパフォーマンスの利点.............................................. 142

非正規化の決定........................................................................... 142

ロードを高速化するための UNION ALL ビューの使用 ..................... 143

UNION ALL ビューを参照するクエリの最適化.......................... 143

ネットワーク・パフォーマンス......................................................... 145

大量のデータ転送の向上 ............................................................ 145

ヘビー・ネットワーク・ユーザの分離....................................... 145

少量のデータを小さなパケットに入れる ................................... 146

大量のデータを大きなパケットに入れる ................................... 146

サーバ・レベルのプロセス......................................................... 146

第 6 章パフォーマンスのモニタリングとチューニング................................. 147

Sybase IQ 環境の表示 ....................................................................... 147

ストアド・プロシージャを使用して情報を取得する ................. 148

Sybase Central パフォーマンス・モニタの使用........................ 148

データベース・プロシージャのプロファイリング..................... 149

バッファ・キャッシュのモニタリング.............................................. 157

バッファ・キャッシュ・モニタの起動....................................... 157

モニタ実行中の結果の確認......................................................... 163

バッファ・キャッシュ・モニタの停止....................................... 164

モニタリング結果の検査と保存 ................................................. 164

モニタリング結果の例................................................................ 165

バッファ・キャッシュの構造 ............................................................ 169

バッファ・マネージャのスラッシングの回避 ................................... 169

Windows システムでのページングのモニタリング ................... 171

UNIX システムでのページングのモニタリング ......................... 171

バッファ・キャッシュ・モニタリング・チェックリスト ................. 173

CPU 使用率をモニタリングするシステム・ユーティリティ ............ 176

第 7 章 Windows システムでのサーバのチューニング .................................. 177

パフォーマンスについての一般的なガイドライン............................ 177

スループットの最大化................................................................ 177

メモリの割り付け超過の防止 ..................................................... 178

物理メモリのモニタリング......................................................... 178

ファイル・システム ................................................................... 178

パフォーマンスのモニタリング......................................................... 179

仮想アドレス空間とワーキング・セットのモニタリング.......... 179

ページ・フォールトのモニタリング .......................................... 180

vi Sybase IQ

NTFS キャッシュの使用..................................................................... 180

挿入とクエリのチューニング ............................................................. 181

適切にチューニングされた挿入オペレーションの特性 .............. 181

クエリのチューニング................................................................. 182

バックアップ操作のチューニング...................................................... 182

索引....................................................................................................................................................... 185

パフォーマンス＆チューニング・ガイド vii

viii Sybase IQ

はじめに

このマニュアルの内容

対象読者

このマニュアルの使用方法

前提条件

DBISQL の代わりにグラフィカルなフロントエンド・ツールを使用してデータベースへのクエリを実行すると、ツールが生成する SQL 構文を表示できる場合があります。たとえば、InfoMaker では [ テーブル ] ペインタ・バーの [SQL 構文 ] ボタンを選択して SQL 文を表示できます。

このチュートリアルでは、データベースから情報を取得するときに使用する SELECT 文について説明します。SELECT 文のことを一般にクエリと呼びます。これは、SELECT 文がデータベース内の情報についてデータベース・サーバに問い合わせるためです。

注意 SELECT 文は用途の広いコマンドです。大きなデータベースから非常に

具体的な情報を取得するアプリケーションでは、SELECT 文がきわめて複雑になる場合があります。このチュートリアルでは、単純な SELECT 文だけを使用します。以降のチュートリアルで、より高度なクエリについて説明します。 SELECT 文の完全な構文については、『Sybase IQ リファレンス・マニュアル』の「第 6 章 SQL 文」の「SELECT 文」を参照してください。

チュートリアルのレッスンを読んで実行している間は、コンピュータで Sybase IQ ソフトウェアを実行しておくことが理想的です。

このチュートリアルでは、すでに DBISQL を起動し、サンプル・データベースに接続していることを前提にしています。まだこれらを行っていない場合は、

『Sybase IQ ユーティリティ・ガイド』の「第 2 章 Interactive SQL (dbisql) の使

用」を参照してください。

2 Sybase IQ

テーブル情報の表示

ここでは、employee テーブルのデータを表示します。

このチュートリアルで使用するサンプル・データベースは、架空の会社のものです。データベースには、従業員、部署、注文などについての情報が格納されています。すべての情報はテーブルに編成されています。

テーブルのリスト

『Sybase IQ の概要』では、Sybase Central で [ テーブル ] フォルダを開いてテー

ブルのリストを表示する方法について説明しました。システム・ストアド・プロシージャの sp_iqtable を使用して、Interactive SQL からユーザ・テーブルをリストすることもできます。システム・ストアド・プロシージャは、Sybase IQ にストアド・プロシージャとして実装されているシステム関数です。

[SQL 文 ] ウィンドウで sp_iqtable と入力し、同じ名前のシステム・ストアド・プロシージャを実行します。

第 1 章データベース・テーブルからのデータの選択

システム・ストアド・プロシージャの詳細については、『Sybase IQ リファレンス・マニュアル』の「第 10 章システム・プロシージャ」を参照してください。

パフォーマンス＆チューニング・ガイド 3

テーブル情報の表示

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 人ずつに 1 つのローがあり、それぞれのローは各カラムに値を持ちます。たとえば、従業員 ID が 102 の従業員は Fran Whitney であり、そのマネージャは従業員 ID 501 です。

DBISQL [ メッセージ ] ウィンドウにも一部の情報が表示されます。この情報については後で説明します。

テーブル名 employee は、実際のテーブル名がすべて小文字の場合にも、先頭は大文字の E で表示されます。Sybase IQ データベースは、文字列の比較で大文字と小文字を区別するもの ( デフォルト ) と区別しないものを作成できますが、その識別子では常に大文字と小文字は区別されません。

注意このマニュアルの例は、CREATE DATABASE 修飾子の CASE IGNORE

を使用して、大文字と小文字を区別しないように作成されています。デフォルトは CASE RESPECT であり、こちらの方がパフォーマンスが向上します。

データベースの作成方法については、『Sybase IQ システム管理ガイド』の「第

5 章データベース・オブジェクトの使用」を参照してください。

SELECT の代わりに select または Select と入力することもできます。Sybase IQ では、キーワードを大文字、小文字、またはその両方の組み合わせで入力できます。このマニュアルでは、通常、SQL キーワードに大文字を使用しています。

DBISQL 環境の操作方法と DBISQL の使用法は、オペレーティング・システムによって異なります。

データをスクロールして DBISQL 環境を操作する方法については、『Sybase IQ ユーティリティ・ガイド』の「第 2 章 Interactive SQL (dbisql) の使用」を参照してください。

4 Sybase IQ

クエリ結果の順序付け

ここでは、SELECT 文に ORDER BY 句を追加して、結果をアルファベット順または数値順に表示します。

特に指定しないかぎり、Sybase IQ ではテーブルのローが順不同で表示されます。テーブルのローを意味のある順序で表示した方が便利なことがよくあります。たとえば、従業員をアルファベット順で表示したいような場合です。

従業員をアルファベット順にリストする

次の例は、SELECT 文に ORDER BY 句を追加して、結果をアルファベット順に取得する方法を示します。

SELECT * FROM employee ORDER BY emp_lname

emp_id manager_id emp_fname emp_lname dept_id

1751 1576 Alex Ahmed 400

1013 703 Joseph Barker 500

591 1576 Irene Barletta 400

191 703 Jeannette Bertrand 500

1336 1293 Janet Bigelow 300

第 1 章データベース・テーブルからのデータの選択

注意

句の順序は重要です。ORDER BY 句は FROM 句と SELECT 句の後に指定します。

注意 FROM 句を省略した場合、またはクエリ内のすべてのテーブルが SYSTEM

dbspace にある場合、クエリは Sybase IQ ではなく Adaptive Server Anywhere に

よって処理されます。これにより、特に構文上およびセマンティック上の制限とオプション設定の効果に関して、クエリが異なる動作をする場合があります。処理に適用されるルールについては Adaptive Server Anywhere のマニュアルを参照してください。

FROM 句を必要としないクエリを実行する場合は、“FROM iq_dummy” 句を追加して、クエリを強制的に Sybase IQ で処理できます。iq_dummy は、データベースに作成される、ローが 1 つ、カラムが 1 つのテーブルです。

パフォーマンス＆チューニング・ガイド 5

カラムとローの選択

多くの場合、表示する必要がある情報は、テーブル内の一部のカラムだけです。たとえば、従業員への誕生日カードを作成するには、emp_lname、dept_id、 birth_date の各カラムを表示すれば十分です。

各従業員の姓、部署、誕生日をリストする

ここでは、各従業員の誕生日、姓、部署 ID を選択します。次のコマンドを入力します。

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 ...

SELECT emp_lname, dept_id, birth_date FROM employee

カラムの並べ替え

ローの順序付け

この 3 つのカラムは、SELECT コマンドに入力した順序で表示されています。カラムを並べ替えるには、コマンドで指定するカラム名の順序を変更します。たとえば、birth_date カラムを左側に配置するには、次のコマンドを使用します。

SELECT birth_date, emp_lname, dept_id FROM employee

次のように、特定のカラムだけを表示すると同時に、ローの順序を指定できます。

SELECT birth_date, emp_lname, dept_id FROM employee ORDER BY emp_lname

次のコマンドのアスタリスクは、テーブル内のすべてのカラムを表す省略記号です。

SELECT * FROM employee

6 Sybase IQ

探索条件の使用

❖ John という名前のすべての従業員をリストするには

第 1 章データベース・テーブルからのデータの選択

ここでは、WHERE 句の複合探索条件、パターン・マッチング、探索条件ショートカットを使用して、日付を比較する手順について説明します。

employee テーブルにある一部の従業員の情報だけを表示したいことがあります。SELECT 文に WHERE 句を追加すると、テーブルから一部のローだけを選択できます。

たとえば、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' はアポストロフィ ( 一重引用符 ) で囲む必要があります。アポストロフィは、John が文字列であることを示します。引用符 ( 二重引用符 ) には別の意味があります。引用符を使用すると、無効な文字列を有効なカラム名やその他の識別子として使用できるようになります。

• サンプル・データベースでは大文字と小文字が区別されないため、'JOHN'、 'john'、'John' のいずれで検索しても同じ結果が返ります。

次のように、これまで学習した句を組み合わせて実行できます。

SELECT emp_fname, emp_lname, birth_date FROM employee WHERE emp_fname = 'John' ORDER BY birth_date

• 句を指定する順序は重要です。FROM 句を最初に指定し、その後に WHERE 句、ORDER BY 句の順に指定します。これ以外の順序で句を入力すると、構文エラーが返されます。

• 文を複数の行に分ける必要はありません。[SQL 文 ] ウィンドウに自由なフォーマットで文を入力できます。入力した文が画面の行数を超えると、 [SQL 文 ] ウィンドウのテキストがスクロールします。

パフォーマンス＆チューニング・ガイド 7

探索条件の使用

クエリでの日付の比較

1964 年 3 月 3 日より前に生まれた従業員をリストする

検索対象の正確な値がわからない場合や、一連の値を表示したい場合があります。WHERE 句で比較を使用すると、探索条件を満たす一連のローを選択できます。

次の例は、日付の不等号探索条件の使い方を示します。次のコマンドを入力します。

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.000

Sybase IQ は、birth_date カラムに日付が格納されていることを認識し、自動的に 'March 3, 1964' を日付に変換します。

WHERE 句での複合探索条件

これまでに、比較演算子の等号 (=) と未満 (<) を見てきました。Sybase IQ では、より大きい (>)、以上 (>=)、以下 (<=)、等しくない (<>) などのその他の比較演算子もサポートされています。

これらの条件を AND や OR を使って組み合わせると、より複雑な探索条件を作成できます。

リストの修飾

8 Sybase IQ

1964 年 3 月 3 日より前に生まれた従業員のうち、Whitney という名前の従業員を除くすべての従業員をリストするには、次のコマンドを入力します。

SELECT emp_lname, birth_date FROM employee WHERE birth_date < '1964-3-3' AND emp_lname <> 'Whitney'

emp_lname birth_date

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.000

Francis 1954-09-12 00:00:00.000

探索条件でのパターン・マッチング

もう 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 emp_fname

Breault Robert

第 1 章データベース・テーブルからのデータの選択

最初の % 記号は文字列 “eaul” と一致し、2 番目の % 記号は空の文字列 ( 文字なし ) と一致します。

LIKE で使用できるもう 1 つの特殊文字に _ ( アンダースコア) 文字があります。これは 1 文字と一致します。

BR_U% というパターンは、BR で始まり、4 番目の文字が U であるすべての名前と一致します。Braun では、_ が A という文字と一致し、% が N と一致します。

パフォーマンス＆チューニング・ガイド 9

探索条件の使用

発音によるローのマッチング

SOUNDEX 関数を使用すると、スペルだけでなく読みによってもローをマッチングできます。たとえば、電話メッセージが残されていて、その宛先が “Ms. Brown” のように発音されていたとします。社内で Brown のように発音される名前を持つ従業員を見つける必要があります。

発音による姓の検索

Brown のように発音される姓を持つ従業員をリストするには、次のコマンドを入力します。

SELECT emp_lname, emp_fname FROM employee WHERE SOUNDEX( emp_lname ) = SOUNDEX( 'Brown' )

emp_lname emp_fname

Braun Jane

この探索条件に一致する従業員は Jane Braun だけです。

探索条件を入力するためのショートカット

省略形 BETWEEN の使用

省略形 IN の使用

SQL には、探索条件を入力するための省略形が 2 つあります。1 つは BETWEEN であり、値を範囲で検索するときに使用します。この例を次に示します。

SELECT emp_lname, birth_date FROM employee WHERE birth_date BETWEEN '1964-1-1' AND '1965-3-31'

これは次のコマンドに相当します。

SELECT emp_lname, birth_date FROM employee WHERE birth_date >= '1964-1-1' AND birth_date <= '1965-3-31'

もう 1 つの省略形 IN は、複数のいずれかの値を検索するときに使用します。次にコマンド例を示します。

SELECT emp_lname, emp_id FROM employee WHERE emp_lname IN ('Yeung','Bucceri','Charlton')

上記のコマンドは、次のコマンドと同じです。

SELECT emp_lname, emp_id FROM employee WHERE emp_lname = 'Yeung' OR emp_lname = 'Bucceri' OR emp_lname = 'Charlton'

10 Sybase IQ

集約データの取得

集合関数の概要

第 1 章データベース・テーブルからのデータの選択

ここでは、集約情報を返すクエリを構築する方法について説明します。集約情報の例を次に示します。

• カラム内のすべての値の合計

• カラム内のエントリの数

• カラム内のエントリの平均値

従業員の人数を調べたいとします。次の文は、employee テーブルのローの数を取得します。

SELECT count( * ) FROM employee

count(*)

このクエリによって、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 つのローで構成されます。3 つのカラムには、従業員数、年齢が最も高い従業員の誕生日、年齢が最も低い従業員の誕生日が格納されています。

COUNT、MIN、MAX を「集合関数」と呼びます。これらの各関数は、テーブル全体の情報を要約します。集合関数は、MIN、MAX、COUNT、AV G 、SUM、 STDDEV、VA R I A N C E と全部で 7 個あります。すべての関数が、パラメータとしてカラム名または式を使用します。前述のように、COUNT はアスタリスクもパラメータとして使用します。

パフォーマンス＆チューニング・ガイド 11

集約データの取得

集合関数によるグループ化されたデータの取得

テーブル全体についての情報を取得することに加えて、集合関数をローのグループに対して使うこともできます。

ローのグループに対する集合関数の使用

各営業担当者が受け持つ注文数をリストするには、次のコマンドを入力します。

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 キーワードを使用します。

GROUP BY 句の制限

12 Sybase IQ

注文数が 55 を超えるすべての営業担当者をリストするには、次のコマンドを入力します。

SELECT sales_rep, count( * ) FROM sales_order GROUP BY sales_rep HAVING count( * ) > 55

sales_rep count(*)

129 57

299 114

467 56

1142 57

注意 GROUP BY は常に HAVING の前に指定します。同様に、WHERE は GROUP

BY の前に指定します。

第 1 章データベース・テーブルからのデータの選択

WHERE と GROUP BY

の使用

小計計算の活用

ROLLUP の使用

注文数が 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 句の両方を含むクエリを構築するときは、できるだけ多くの条件を WHERE 句で指定するようにします。

日付や場所などの次元によって異なるデータがある場合に、各次元でデータがどのように異なるかを調べることが必要になる場合があります。ROLLUP 演算子と CUBE 演算子を使用すると、グループ化カラムへの参照のリストから複数レベルの小計と総計を作成できます。小計は、最も詳細なレベルから総計まで「ロールアップ」します。たとえば、販売データを分析している場合は、同じクエリを使用して全体の平均と年別の平均販売数を計算できます。

年別、モデル別、色別の合計自動車販売数を選択するには、次のコマンドを使用します。

SELECT year, model, color, sum(sales) FROM sales_tab GROUP BY ROLLUP (year, model, color);

year model color sales

1990 Chevrolet red 5

1990 Chevrolet white 87

1990 Chevrolet blue 62

1990 Chevrolet NULL 154

1990 Ford blue 64

1990 Ford red 62

1990 Ford white 63

1990 Ford NULL 189

1990 NULL NULL 343

パフォーマンス＆チューニング・ガイド 13

集約データの取得

CUBE の使用

year model color sales

1991 Chevrolet blue 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 は 1991 年の合計販売数を表します。最後のローは、すべての年のすべてのモデルとすべての色の合計販売数を表します。

ROLLUP 演算子には、引数としてグループ化式の順番リストを指定する必要があります。他のグループを含むグループをリストするときは、先に大きい方のグループをリストします ( たとえば、state をリストしてから city をリストします )。

ROLLUP 演算子は、集合関数の SUM、COUNT、AV G 、MIN、MAX、STDDEV、 VA R I A N C E とともに使用できます。ただし、ROLLUP は COUNT DISTINCT と SUM DISTINCT をサポートしていません。

次のクエリでは、人々の州 ( 地理的位置 )、性別、教育水準、所得を含む国勢調査のデータを使用します。GROUP BY 句の CUBE 拡張を使用すると、census テーブル内の国勢調査データを 1 回参照するだけで、州、性別、教育水準の国勢調査全体の平均所得を計算し、state、gender、education の各カラムの可能なすべての組み合わせの平均所得を計算できます。たとえば、すべての州のすべての女性の平均所得を計算する場合や、教育水準と地理的位置を基準に国勢調査のすべての人々の平均所得を計算する場合に、CUBE 演算子を使用します。

CUBE でグループを計算するときに、CUBE は計算されたグループのカラムに NULL 値を挿入します。各ローが表すグループの種類と、その NULL がデータ

ベースに格納されている NULL なのか、CUBE が挿入した NULL なのかを区別することは困難です。この問題を解決するのが GROUPING 関数です。指定されたカラムが上位レベルのグループにマージされている場合、この関数は 1 を返します。

14 Sybase IQ

第 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_state c_gender c_education count(*) average

MA f BA 3 48333.33

MA f HS 2 40000.00

MA f MS 1 45000.00

MA f ALL 6 45000.00

MA m BA 4 55000.00

MA m HS 1 55000.00

MA m MS 3 85000.00

MA m ALL 8 66250.00

MA ALL ALL 14 57142.86

NH f HS 2 50000.00

NH f MS 1 85000.00

NH f ALL 3 61666.67

NH m BA 3 55000.00

NH m MS 1 49000.00

NH m ALL 4 53500.00

NH ALL ALL 7 57000.00

ALL ALL ALL 21 57095.24

ALL ALL BA 10 53000.00

ALL ALL MS 6 72333.33

ALL ALL HS 5 47000.00

ALL f ALL 9 50555.56

パフォーマンス＆チューニング・ガイド 15

集約データの取得

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.00

ROLLUP と CUBE は、データ・ウェアハウス管理者が次のような処理を行う

ときに特に役立ちます。

• 地理や時間などの階層的な次元での小計 ( たとえば、年／月／日や国／州／市 )

• 要約テーブルへのデータの格納

ROLLUP と CUBE を使用すると、レベルごとに別々のクエリを使用する代わ

りに、1 つのクエリを使用して、複数レベルのグループ化を使ってデータを計算できます。

ROLLUP 演算子と CUBE 演算子の詳細については、『Sybase IQ リファレンス・マニュアル』の「第 6 章 SQL 文」の「SELECT 文」を参照してください。

16 Sybase IQ

分析データの取得

第 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 sales DESC ) FROM carSales;

dealer_name sales ntile Boston 1000 1 Worcester 950 1 Providence 950 1 SF 940 1 Lowell 900 2 Seattle 900 2 Natick 870 2 New Haven 850 2 Portland 800 3 Houston 780 3 Hartford 780 3 Dublin 750 3 Austin 650 4 Dallas 640 4 Dover 600 4

販売台数で上位 10% の販売店を調べるには、この例の SELECT 文で NTILE(10) を指定します。同様に、販売台数で 50% の販売店を調べるには、NTILE(2) を指定します。

NTILE はクエリ結果を指定された数のバケットに分割し、バケット内の各ローにバケット番号を割り当てるランク分析関数です。結果セットは 10 個 ( 十分位数 )、4 個 ( 四分位数 )、その他の数のグループに分割できます。

ランク分析関数では、OVER (ORDER BY) 句を指定する必要があります。 ORDER BY 句は、ランク付けを実行するパラメータと、各グループ内でローをソートする順序を指定します。この ORDER BY 句は、OVER 句の中だけで使用されるもので、SELECT の ORDER BY とは異なります。

パフォーマンス＆チューニング・ガイド 17

重複したローの削除

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 文の結果テーブルに、重複したローが含まれることがあります。 DISTINCT キーワードを使用すると、重複したローを削除できます。たとえば、

次のコマンドを実行すると、多くの重複したローが返ります。

市と州のユニークな組み合わせだけをリストするには、次のコマンドを使用します。

SELECT city, state FROM employee

SELECT DISTINCT city, state FROM employee

注意 ROLLUP 演算子と CUBE 演算子は、DISTINCT キーワードをサポートし

ていません。

この章では、単一テーブルの SELECT 文の概要について説明しました。単一テーブルの SELECT 文の詳細については、『Sybase IQ システム管理ガイド』の

「第 5 章データベース・オブジェクトの使用」、『Sybase IQ リファレンス・マ

ニュアル』の「第 3 章 SQL 言語の要素」、『Sybase IQ リファレンス・マニュアル』の「第 6 章 SQL 文」の「SELECT 文」を参照してください。

次の章では、SELECT 文の高度な使い方について説明します。

18 Sybase IQ

第 2 章テーブルのジョイン

この章について

この章では、複数のテーブルにある情報を参照する方法と、さまざまな種類のジョインについて説明します。ここでは、テーブルをジョインするチュートリアル作業を実行します。

内容

トピック名ページ

外積を使用したテーブルのジョインジョインの制限テーブル間の関係ジョイン演算子アドホック・ジョインとジョイン・インデックスの使用ジョインとデータ型ストアまたはデータベース間ジョインのサポートリモート・データベースと異種データベースのクエリサブクエリによるジョインの置き換え

外積を使用したテーブルのジョイン

サンプル・データベースに、会社の財務データをリストする fin_data というテーブルがあります。各データ・レコードには、そのレコードの部署と、それが支出レコードか収入レコードかを示す code カラムがあります。

fin_data テーブルには 84 のローがあります。

2 つのテーブルから同時に情報を取り出すには、SELECT クエリの FROM

句で、両方のテーブルをカンマで区切って指定します。

例

次の dbisql SELECT コマンドは、fin_code テーブルと fin_data テーブルのすべてのデータをリストします。

SELECT * FROM fin_code, fin_data

dbisql [ データ ] ウィンドウに表示されるこのクエリの結果は、fin_code テーブルのすべてのローと fin_data テーブルのすべてのローに一致します。このジョインを完全外積または直積と呼びます。各ローは、fin_code テーブルのすべてのカラム、fin_data テーブルのすべてのカラムの順で構成されます。

パフォーマンス＆チューニング・ガイド 19

ジョインの制限

例 1

例 2

外積ジョインは、ジョインを理解するための単純な出発点にすぎず、それ自体はあまり役に立ちません。これ以降の項で、より選択性の高いジョインを構築する方法について説明します。このジョインは、外積テーブルへの制限の適用と考えることができます。

外積ジョインを有効に利用するには、何らかの条件を満たすローだけを結果に含める必要があります。ジョイン条件と呼ばれるこの条件では、比較演算子

(=、=>、< など ) を使用して、あるテーブルの 1 つのカラムを別のテーブルの 1 つのカラムと比較します。これにより、外積の結果から一部のローを除外し

ます。

たとえば、前の項のジョインを有効に利用するには、sales_order テーブルの sales_rep と employee テーブルの従業員番号が一致するローだけを結果に含めるように指定します。これにより、各ローには注文と、その注文を担当する営業担当者の情報が格納されます。

これを実行するには、前のクエリに WHERE 句を追加し、従業員とその担当登録のリストを表示します。

SELECT * FROM sales_order, employee WHERE sales_order.sales_rep = employee.emp_id

カラムを識別するために、テーブル名をプレフィクスとして指定します。この例では必ずしも必要ありませんが、テーブル名のプレフィクスを使用すると文が明確になります。2 つのテーブルに同じ名前のカラムがあるときは、このプレフィクスを指定する必要があります。このようなコンテキストで使用するテーブル名を「修飾子」と呼びます。

このクエリの結果には 648 のローしかありません (sales_order テーブルの各ローに 1 つずつ )。ジョインした元の 48,600 のローのうち、648 のローにだけ 2 つのテーブルで共通する従業員番号が含まれています。

次のクエリでは、一部のカラムだけをフェッチし、結果を順序付けするように変更を加えています。

SELECT employee.emp_lname, sales_order.id, sales_order.order_date FROM sales_order, employee WHERE sales_order.sales_rep = employee.emp_id ORDER BY employee.emp_lname

SELECT コマンドに多くのテーブルがある場合は、修飾子名をいくつも入力しなければならないことがあります。このようなときは、相関名を使用して入力の手間を省くことができます。

20 Sybase IQ

第 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.emp_lname

この例では、sales_order テーブルと employee テーブルに対応する S と E という 2 つの相関名を作成しています。

注意テーブル名や相関名が必要になるのは、異なるテーブルに同じ名前のカ

ラムがあり、不明確になることを避ける場合だけです。相関名を作成した場合は、テーブル名の代わりに必ず相関名を使用します。相関名を作成していない場合は、テーブル名を使用します。

他の種類のジョインを構築するには、あるテーブルの情報が別のテーブルの情報とどのように関係するかを先に理解する必要があります。

テーブルのプライマリ・キーは、そのテーブル内の各ローを識別します。各テーブルは、外部キーを使って互いに関連付けられます。

ここでは、プライマリ・キーと外部キーを組み合わせて、複数のテーブルからクエリを構築する方法について説明します。

プライマリ・キーによるローの識別

asiqdemo データベースのすべてのテーブルには、プライマリ・キーが設定されています ( 各テーブルにプライマリ・キーを定義することをおすすめします )。プライマリ・キーは、テーブル内のローをユニークに識別する 1 つまたは複数のカラムです。たとえば、従業員番号は従業員をユニークに識別するため、

emp_id は employee テーブルのプライマリ・キーになります。

sales_order_items テーブルは、2 つのカラムでプライマリ・キーを構成して

いるテーブルの例です。注文 ID だけでは、sales_order_items テーブルのローがユニークに識別されません。注文には複数の項目が含まれる場合があるからです。また、line_id 番号も sales_order_items テーブルのローをユニークに識別しません。sales_order_items テーブルのローをユニークに識別するには、注文 ID 名と line_id の両方が必要です。両方のカラムが一緒になってテーブルのプライマリ・キーになります。

パフォーマンス＆チューニング・ガイド 21

ジョイン演算子

外部キーによって関連付けられたテーブル

asiqdemo データベースのいくつかのテーブルは、データベース内の他のテーブルを参照しています。たとえば、sales_order テーブルには、注文を担当する従業員を示す sales_rep カラムがあります。sales_order テーブルには、従業員をユニークに識別するために必要な最小限の情報だけが格納されています。 sales_order テーブルの sales_rep カラムは、employee テーブルに対する外部キーになっています。

外部キー

外部キーは、他のテーブルの候補キーの値を含む 1 つまたは複数のカラムです ( 候補キーの詳細については、『Sybase IQ システム管理ガイド』の「第 5 章デー

タベース・オブジェクトの使用」を参照してください )。従業員データベース

内の各外部キーの関係は、2 つのテーブル間の矢印によって図示されます。

『Sybase IQ の概要』の図 1-1 (11 ページ ) のサンプル・データベースの図に、こ

れらの矢印が示されています。矢印は関係の外部キー側を起点とし、候補キー側を指し示しています。

ジョイン演算子

多くの一般的なジョインは、外部キーで関連付けられた 2 つのテーブル間で行われます。最も一般的なジョインでは、外部キーの値がプライマリ・キーの値と等しいものに制限されます。すでに見てきた例では、sales_order テーブルの外部キーの値が、employee テーブルの候補キーの値と等しいものに制限されています。

SELECT emp_lname, id, order_date FROM sales_order, employee WHERE sales_order.sales_rep = employee.emp_id

KEY JOIN を使用すると、クエリをより簡単に表現できます。

キー・ジョインを使用したテーブルのジョイン

キー・ジョインは、外部キーで関連付けられたテーブルを簡単にジョインする方法です。例：

SELECT emp_lname, id, order_date FROM sales_order KEY JOIN employee

このコマンドは、次のように 2 つの従業員 ID 番号カラムを結び付ける WHERE 句を使ったクエリと同じ結果をもたらします。

SELECT emp_lname, id, order_date FROM sales_order, employee WHERE sales_order.sales_rep = employee.emp_id

22 Sybase IQ

複数のテーブルのジョイン

第 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.unit_price) AS INTEGER) AS value FROM customer KEY JOIN sales_order KEY JOIN sales_order_items KEY JOIN product GROUP BY company_name

company_name value

McManus Inc. 3,156

Salt & Peppers. 4,980

The Real Deal 1,884

Totos Active Wear 2,496

The Ristuccia Center 4,596

...

このクエリで使用している CAST 関数は、式のデータ型を変換します。この例では、整数として返される合計が値に変換されます。

パフォーマンス＆チューニング・ガイド 23

ジョイン演算子

ナチュラル・ジョインを使用したテーブルのジョイン

NATURAL JOIN 演算子は、共通のカラム名に基づいて 2 つのテーブルをジョインします。言い換えると、Sybase IQ が各テーブルに共通するカラムを結び付ける WHERE 句を生成します。

例

NATURAL JOIN を使用したときのエラー

たとえば、次のようなクエリがあるとします。

SELECT emp_lname, dept_name FROM employee NATURAL JOIN department

この例では、データベース・サーバが 2 つのテーブルを参照し、共通するカラム名は dept_id だけであると判断します。次の ON フレーズが内部的に生成され、ジョインの実行に使用されます。

FROM employee JOIN department ... ON employee.dept_id = department.dept_id

このジョイン演算子では、意図しないカラムを結び付けてしまう問題が起きる可能性があります。たとえば、次のクエリは意図しなかった結果をもたらします。

SELECT * FROM sales_order NATURAL JOIN customer

このクエリの結果には、ローが 1 つもありません。

データベース・サーバは、内部的に次の ON フレーズを生成します。

FROM sales_order JOIN customer

ON sales_order.id = customer.id

sales_order テーブルの id カラムは、注文の ID 番号です。一方、customer テーブルの id カラムは、顧客の ID 番号です。これらの番号は 1 つも一致しません。たとえ一致する番号があったとしても、当然それは意味を持ちません。

ジョイン演算子をむやみに使用しないように注意してください。ジョイン演算子は、単に強制力のない外部キーや共通のカラム名で WHERE 句を入力する手間を省くためのものであることを忘れないでください。WHERE 句を注意して使用しないと、意図しない結果をもたらすクエリを作成してしまう可能性があります。

24 Sybase IQ

第 2 章テーブルのジョイン

アドホック・ジョインとジョイン・インデックスの使用

クエリで参照されるジョイン・カラムにジョイン・インデックスを定義している場合、Sybase IQ は自動的にそれらを使用してクエリ処理を高速化します ( ジョイン・インデックスの定義については、『Sybase IQ システム管理ガイド』の「第 6 章 Sybase IQ インデックスの使用」を参照してください )。

ジョイン・インデックスを使用しないジョインを「アドホック・ジョイン」呼びます。クエリでいくつものテーブルを参照しており、その中にジョイン・インデックスが定義されていないテーブルがある場合、Sybase IQ は定義されているテーブルではジョイン・インデックスを、それ以外のテーブルではアドホック・ジョインを使用します。

可能なすべてのジョインに対してジョイン・インデックスを作成することはできないため、ときにはアドホック・ジョインが必要になることがあります。 Sybase IQ の最適化によって、クエリはジョイン・インデックスなしでも同等かそれ以上のパフォーマンスで実行されます。

ジョイン・インデックスの作成には、次の制約があります。

• インデックス内では、完全な外部ジョインだけがサポートされます。クエリは、インデックス付けされている場合、内部、左外部、右外部のジョインになります。

完全な外部ジョインでは、指定された左右両方のテーブルのすべてのローが結果に含まれ、対応するカラムに一致する値がないカラムについては

NULL が返されます。

• ジョイン述部の ON 句で使用できる比較演算子は EQUALS だけです。

• ON 句の代わりに NATURAL キーワードを使用できますが、1 対のテーブ

ルしか指定できません。

• ジョイン・インデックス・カラムはいずれも同じデータ型、精度、位取りでなければなりません。

ジョインとデータ型

最適なパフォーマンスを得るには、ジョイン・カラムを類似のデータ型にする必要があります。Sybase IQ では、暗黙の変換が存在する任意のデータ型でアドホック・ジョインを行うことができます。ただし、ジョイン・カラムのデータ型が同じでない場合は、データ型とテーブルのサイズによって、パフォーマンスがさまざまな範囲で低下する可能性があります。たとえば、INT を BIGINT のカラムにジョインすることはできますが、このジョインによって特定の種類の最適化ができなくなります。Sybase IQ インデックス・アドバイザは、データ型が異なるジョイン・カラムにパフォーマンス上の問題があると見なします。

暗黙のデータ型変換のテーブルについては、『Sybase IQ システム管理ガイド』の「第 7 章データベースへのデータの入出力」を参照してください。

パフォーマンス＆チューニング・ガイド 25

ストアまたはデータベース間ジョインのサポート

この項では、ストア間またはデータベース間ジョインに対する現在のサポートを明確にします。

Sybase IQ

ス内でのテーブルのジョイン

Adaptive Server Enterprise テーブルと

Sybase IQ テーブルの

ジョイン

データベー

指定された Sybase IQ データベース内では、あらゆる種類のジョインがサポートされます。つまり、カタログ・ストアの任意のシステムまたはユーザ・テーブルを、IQ ストアの任意のテーブルに任意の順序でジョインできます。

Sybase IQ テーブルと Adaptive Server Enterprise データベースのテーブルのジョインは、次の条件下でサポートされます。

• Sybase IQ データベースは、ローカル・データベースとリモート・データベースのどちらでもかまいません。

•ASE で Sybase IQ テーブルをプロキシ・テーブルとして使用する場合は、テーブル名を 30 文字以内にしてください。

• ローカルの Adaptive Server Enterprise テーブルをリモートの Sybase IQ 12 テーブルにジョインするには、ASE のバージョンが 11.9.2 以降である必要があります。また、次の適切なサーバ・クラスを使用してください。

• Adaptive Server Enterprise 12.5 以降のフロントエンドからリモートの

Sybase IQ 12.5 以降に接続するには、ASE 12.5 で追加された ASIQ サー

バ・クラスを使用します。

• Adaptive Server Enterprise 11.9.2 から 12.0 までのフロントエンドから

リモートの Sybase IQ 12.x ( または Adaptive Server Anywhere 6.x 以降 ) に接続するには、サーバ・クラス ASAnywhere を使用します。

• ローカルの Sybase IQ テーブルを任意のリモート・テーブルとジョインする場合は、ローカルのテーブルを FROM 句の最初に指定する必要があります。つまり、ローカルのテーブルは、ジョインの最も外側のテーブルになります。

Sybase IQ と Adaptive Server Enterprise の間のジョインは、コンポーネント統合サービス (CIS) に依存します。

Adaptive Server Enterprise データベースから Sybase IQ へのクエリの詳細については、Adaptive Server Enterprise 主要マニュアル・セットの『コンポーネント統合サービス・ユーザーズ・ガイド』を参照してください。

Sybase IQ から他のデータベースへのクエリの詳細については、「リモート・

データベースと異種データベースのクエリ」を参照してください。

26 Sybase IQ

第 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 では、ジョイン結果が正しくなるように、BLANK PADDING OFF によって影響を受ける既存のカラムをすべて変更することを推奨しています。ジョイン・カラムを VARCHAR ではなく CHAR データ型として再作成します。CHAR カラムでは、常にブランクが埋め込まれます。

リモート・データベースと異種データベースのクエリ

ここでは、Sybase IQ をコンポーネント統合サービス (CIS) と組み合わせて使用する方法について説明します。CIS を使用すると、Sybase IQ を通して Adaptive Server Enterprise データベースとリモート・データベースまたは非リレーショナル・データ・ソースにクエリを実行できます。CIS は Sybase IQ の一部としてインストールされます。

CIS を使用すると、リモート・サーバ上のテーブルに、ローカルのテーブルのようにアクセスできます。CIS は、複数のリモート異種サーバのテーブル間でジョインを実行し、1 つのテーブルの内容を、サポートされているリモート・サーバへ転送します。

リモートのデータベースやデータ・ソースにクエリを実行するには、そのテーブルをローカル・プロキシ・テーブルにマッピングする必要があります。CIS は、データがローカルに格納されているかのように、プロキシ・テーブルをクライアント・アプリケーションに示します。テーブルにクエリを実行すると、 CIS は実際のサーバ記憶位置を判別します。

❖ リモート・データベースをジョインするには

1 『Sybase IQ システム管理ガイド』の手順に従って、プロキシ・テーブルを

作成します。

2 リモート・テーブルをプロキシ・テーブルにマッピングします。

パフォーマンス＆チューニング・ガイド 27

サブクエリによるジョインの置き換え

3 プロキシ・データベース名を各リモート・テーブルの修飾名として使用

し、SELECT 文でプロキシ・テーブルを参照します。たとえば、次のような文を発行します。

SELECT a.c_custkey, b.o_orderkey FROM proxy_asiqdemo..cust2 a, asiqdemo..orders b WHERE a.c_custkey = b.o_custkey

詳細については、『Sybase IQ システム管理ガイド』の「第 16 章リモート・デー

タへのアクセス」と「第 17 章リモート・データ・アクセス用のサーバ・クラス」を参照してください。

サブクエリによるジョインの置き換え

ジョインは、複数のテーブルのデータから構築される結果テーブルを返します。サブクエリを使用して、同じ結果テーブルを取得することもできます。サブクエリは、単に別の SELECT 文の中にある SELECT 文です。より複雑で多くの情報を与えるクエリを構築するときに、このツールが役立ちます。

たとえば、注文とその発注先の会社を時系列にリストする必要があり、顧客 ID の代わりに会社名を使いたいとします。この結果を得るには、次のようなジョインを使用します。

ジョインの使用

1994 年の年初以降の各注文の order_id、order_date、company_name をリストするには、次のコマンドを入力します。

SELECT sales_order.id, sales_order.order_date, customer.company_name FROM sales_order KEY JOIN customer WHERE order_date > '1994/01/01' ORDER BY order_date

id order_date company_name

2473 1994-01-04 Peachtree Active Wear

2474 1994-01-04 Sampson & Sons

2036 1994-01-05 Hermanns

2475 1994-01-05 Salt & Peppers

2106 1994-01-05 Cinnamon Rainbows

28 Sybase IQ

第 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.id) state

Custom Designs 2547 WA

Its a Hit! (NULL) WA

在庫が少ない製品の注文項目をリストするには、次のコマンドを入力します。

SELECT * FROM sales_order_items WHERE prod_id IN ( SELECT id FROM product WHERE quantity < 20 ) ORDER BY ship_date DESC

id line_id prod_id quantity ship_date

2082 1 401 48 1994-07-09

2053 1 401 60 1994-06-30

2125 2 401 36 1994-06-28

2027 1 401 12 1994-06-17

2062 1 401 36 1994-06-17

カッコで囲まれたフレーズが、この文のサブクエリです。

( SELECT id FROM product WHERE quantity < 20 )

サブクエリを使用すると、検索を 1 回のクエリだけで実行できるようになります。このため、在庫が少ない製品のリストをクエリで検索し、さらにその製品の注文を別のクエリで検索する必要がなくなります。

このサブクエリは、製品テーブルの id カラムで WHERE 句の探索条件を満たすすべての値をリストします。

パフォーマンス＆チューニング・ガイド 29

サブクエリによるジョインの置き換え

クエリの別の表現方法

受注した 10 枚のタンクトップが出荷され、タンクトップの数量カラムの値が 18 になった場合にどうなるかを考えてみます。サブクエリを使ったクエリは、

ウールの帽子とタンクトップの両方のすべての注文をリストします。これに対して、最初に使用した文は次のように変更する必要があります。

SELECT * FROM sales_order_items WHERE prod_id IN ( 401, 300 ) ORDER BY ship_date DESC

サブクエリを使用するコマンドは、データベースのデータが変更されてもそのまま機能するように、改善されています。

サブクエリについては、次の点に注意してください。

• NOT EXISTS 述部を使用するクエリなど、ジョインの構築に問題がある場合もサブクエリが役立つことがあります。

• サブクエリが返せるのは 1 つのカラムだけです。

• サブクエリは、比較の引数、IN、または EXISTS 句としてのみ使用でき

ます。

• 外部ジョインの ON 句の中に、サブクエリを使用することはできません。

30 Sybase IQ

第 3 章クエリと削除の最適化

この章について

内容

クエリ構築のヒント

この章では、次のようなクエリと削除のパフォーマンスに関する推奨事項について説明します。

• 処理速度の速いクエリの構築

• クエリ・プランの使用

• クエリ処理オプションの設定

• 削除オペレーションの最適化

トピック名ページ

クエリ構築のヒントクエリ・プランクエリ処理の制御削除オペレーションの最適化

ここでは、クエリ構造を改良するためのヒントを示します。

• サブクエリを含むコマンド文をジョインとして構成することによって、実行速度を高めることができる場合があります。

• GROUP BY 句で複数のカラムをグループ化する場合、カラムに対応するユニークな値をもとに降順にカラムをリストします。これによって最適なクエリのパフォーマンスが実現されます。

• ジョイン・インデックスを使用すると、多くの場合、ジョイン・クエリはアドホック・ジョインより高速に実行されますが、より多くのディスク領域が必要となります。ただし、ジョイン・クエリがマルチテーブル・ジョイン・インデックスの最大のテーブルを参照しない場合は、アドホック・ジョインの方がジョイン・インデックスよりパフォーマンスが高くなります。

• 追加のカラムを使用して、頻繁に行う計算の結果を格納すると、パフォーマンスを向上させることができます。

パフォーマンス＆チューニング・ガイド 31

クエリ構築のヒント

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, a2 FROM tableA UNION ALL SELECT b1, b2 FROM tableB;

SELECT COUNT(*), SUM(v1) FROM vtable GROUP BY v2;

このクエリを分析するときに、オプティマイザは先に tableA で COUNT(*) GROUP BY を実行し、tableB で COUNT(*) GROUP BY を実行した後、結果を

最上位レベルの GROUP BY に渡します。最上位レベルの GROUP BY は、2 つの COUNT(*) の結果の SUM を実行し、最終的なクエリ結果を生成します。最上位レベルの GROUP BY の役割が変化していることに注意してください。最上位レベルの GROUP BY が使用している集合関数は COUNT ではなく SUM です。

分割 GROUP BY の制限

分割 GROUP BY によってパフォーマンスが向上する状況とクエリには、いくつかの制限があります。

• クエリで UNION ではなく UNION ALL を使用している場合に、分割 GROUP BY によってクエリのパフォーマンスが向上する可能性があります。次のク

エリでは UNION で GROUP BY を使用しているため、分割 GROUP BY によるメリットはありません。

CREATE VIEW viewA (va1 int, va2 int, va3 int, va4 int) AS SELECT b1, b2, b3, b4 FROM tableB UNION SELECT c1, c2, c3, c4 FROM tableC;

SELECT SUM(va1) FROM viewA GROUP BY va3;

32 Sybase IQ

第 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) AS SELECT b1, b2, b3, b4 FROM tableB UNION ALL SELECT c1, c2, c3, c4 FROM tableC UNION ALL SELECT d1, d2, d3, d4 FROM tableD UNION ALL SELECT e1, e2, e3, e4 FROM tableE UNION ALL SELECT f1, f2, f3, f4 FROM tableF UNION ALL SELECT g1, g2, g3, g4 FROM tableG;

SELECT SUM(va1) FROM viewA GROUP BY va3;

この例では、Sybase IQ オプティマイザが GROUP BY を分割し、6 個の GROUP BY 演算子をクエリ・プランに挿入しています。これにより、集

合情報とデータを格納するために、クエリにより多くのテンポラリ・キャッシュが必要となります。システムが十分なキャッシュを割り付けられない場合、オプティマイザは GROUP BY を分割しません。

メモリに空きがある場合は、TEMP_CACHE_MEMORY_MB データベース・オプションを使用してテンポラリ・キャッシュのサイズを増やすことができます。バッファ・キャッシュのサイズの設定方法については、『Sybase IQ リファレンス・マニュアル』の「データベース・オプション」の「バッファ・キャッシュ・サイズの決定」(107 ページ ) および

「TEMP_CACHE_MEMORY_MB オプション」を参照してください。

パフォーマンス＆チューニング・ガイド 33

クエリ構築のヒント

分割 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 つの小さなテーブルと UNION ALL を使用して、unionTab ビューを作成します。

CREATE VIEW unionTab (v1 int, v2 int, v3 int, v4 int) AS SELECT a, b, c, d FROM tabA1 UNION ALL SELECT a, b, c, d FROM tabA2 UNION ALL SELECT a, b, c, d FROM tabA3 UNION ALL SELECT a, b, c, d FROM tabA4;

Sybase IQ オプティマイザは GROUP BY の処理を次のクエリに分割し、クエリのパフォーマンスを向上させます。

SELECT v1, v2, SUM(v3), COUNT(*) FROM unionTab GROUP BY v1, v2;

SELECT v3, SUM(v1*v2) FROM unionTab GROUP BY v3;

Adaptive Server Anywhere による処理を引き起こす条件

Sybase IQ アーキテクチャには、Adaptive Server Anywhere のルールに従ってクエリを処理する製品の部分が含まれています。CIS ( 以前は OMNI) 機能補正と呼ばれるこの機能を使用すると、Sybase IQ のセマンティックで直接サポートされないクエリを処理できますが、パフォーマンスが大幅に低下します。

CIS は次のクエリを傍受します。

• ユーザ定義関数を参照するクエリ

• データベース間のジョインまたはプロキシ・テーブルを含むクエリ

• 特定のシステム関数を含むクエリ

• カタログ・ストア・テーブルまたは SYSTEM dbspace で作成されたテーブ

ルを参照するクエリ

34 Sybase IQ

クエリ・プラン

第 3 章クエリと削除の最適化

Sybase IQ と Adaptive Server Anywhere の違いの詳細については、『Sybase IQ リファレンス・マニュアル』の「付録 A 他の Sybase データベースとの互換性」を参照してください。

最も効果的な構文を使用していなくても、正しいインデックスを作成していれば、通常は Sybase IQ クエリ・オプティマイザによって、最も効率的な方法でクエリを実行できます。もちろん、クエリを正しく設計することは重要です。クエリを計画する場合に、クエリの実行速度と得られる結果の正確さが主要な問題点となります。

クエリを実行する前に、Sybase IQ クエリ・オプティマイザはクエリ・プランを作成します。Sybase IQ では、これ以降の項で説明するオプションを使用して、クエリ・プランを調査および変更し、クエリを評価できます。このオプションを指定する方法の詳細については、『Sybase IQ リファレンス・マニュアル』を参照してください。

注意整数値を指定できるデータベース・オプションでは、小数の option-value

の設定が常に整数値にトランケートされます。たとえば、3.8 という値は 3 にトランケートされます。

クエリ評価オプション

次のオプションは、クエリ・プランの評価に役立ちます。これらのオプションの詳細については、『Sybase IQ リファレンス・マニュアル』を参照してください。

• INDEX_ADVISOR －このオプションを ON に設定すると、インデックス・アドバイザは、Sybase IQ クエリ・プランの一部として、またクエリ・プランが無効の場合には、Sybase IQ メッセージ・ログ・ファイル内の独立したメッセージとして、インデックスの推奨を出力します。これらのメッセージは、“Index Advisor” という文字列で始まります。この文字列を検索することで、Sybase IQ メッセージ・ファイルからこれらのメッセージをフィルタできます。このオプションはメッセージを OWNER.TABLE.COLUMN 形式で出力します。このオプションのデフォルト設定は OFF です。

『Sybase IQ リファレンス・マニュアル』の「sp_iqindexadvice プロシージャ」

も参照してください。

• INDEX_ADVISOR_MAX_ROWS －このオプションはインデックス・アドバイザによって格納されるメッセージの数を制限します。指定された制限値に達すると、INDEX_ADVISOR は新しいアドバイスの保存を停止しますが、既存のアドバイスのカウントとタイムスタンプの更新は続行します。

パフォーマンス＆チューニング・ガイド 35

クエリ・プラン

• 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 に設定すると、クエリの実行が終了した後でクエリ・プランが出力されます。これにより、クエリの各ノードから渡された実際のローの数など、追加情報をプランに含めることができます。このオプションを使用するには、QUERY_PLAN を ON にします。このオプションは、デフォルトでは OFF になっています。

• QUERY_PLAN_AS_HTML －このオプションは、We b ブラウザで表示できるように、HTML 形式のグラフィカルなクエリ・プランを生成します。 HTML 形式では、ノード間にハイパーリンクが設定されるため、.iqmsg ファイルのテキスト形式よりはるかに使いやすくなります。クエリ・プランのファイル名にクエリ名を含めるには、QUERY_NAME オプションを使用します。このオプションは、デフォルトでは OFF になっています。

• QUERY_PLAN_AS_HTML_DIRECTORY －このオプションを ON に設定し、QUERY_PLAN_AS_HTML_DIRECTORY でディレクトリが指定されている場合、Sybase IQ は指定されたディレクトリに HTML クエリ・プランを書き込みます。

• QUERY_TIMING －このオプションは、サブクエリのタイミング統計の収集などのクエリ・エンジンの反復的な機能を制御するのに使用します。非常に短い相関サブクエリの場合、各サブクエリを実行するタイミングを合わせる処理のために、全体のパフォーマンスが大幅に低下するため、このオプションは、通常、OFF ( デフォルト ) にします。

注意クエリ・プランを生成すると、.iqmsg ファイルに大量のテキストが追加

される場合があります。QUERY_PLAN が ON の場合で、特に QUERY_DETAIL が ON の場合は、IQMSG_LENGTH_MB を正の値に設定し、メッセージ・ログの循環を有効にすることをおすすめします。

36 Sybase IQ

クエリ・ツリー

オプティマイザは、クエリ内のデータの流れを表すクエリ「ツリー」を作成します。クエリ・プランでは、クエリ・ツリーが .iqmsg ファイル内にテキスト形式で表示されます。オプションで、グラフィカル形式のクエリ・ツリーも作成できます。

クエリ・ツリーはノードで構成されます。それぞれのノードは処理の段階を表します。ツリーの一番下のノードはリーフ・ノードです。各リーフ・ノードは、クエリ内のテーブルまたはプリジョイン・インデックス・セットを表します。

プランの最上部にあるのは、演算子ツリーのルートです。情報はテーブルから上方向に、ジョイン、ソート、フィルタ、格納、集合、サブクエリを表す演算子を通じて流れます。

HTML クエリ・プランの使用

クエリ・プランを初めて使用するときは、QUERY_PLAN_AS_HTML オプションを ON に設定することをおすすめします。このオプションを設定すると、 .iqmsg ファイルと同じディレクトリにグラフィカル版のクエリ・プランが作成されます。このファイルは、ほとんどの Web ブラウザで表示できます。

HTML クエリ・プランでは、ツリーの各ノードが詳細へのハイパーリンクになっています。各ボックスが上位のツリーへハイパーリンクされています。任意のノードをクリックし、プラン内をすばやく移動できます。

第 3 章クエリと削除の最適化

クエリ処理の制御

すべてのユーザが、特定のクエリの処理にかかる時間に制限を設定できます。 DBA 権限を持つユーザは、特定のユーザのクエリに他のクエリより高い優先度を与えることや、処理のアルゴリズムを変更し、クエリ処理の速度を操作することができます。この項で説明するオプションの詳細については、『Sybase IQ リファレンス・マニュアル』を参照してください。

クエリの時間制限の設定

MAX_QUERY_TIME オプションを設定すると、ユーザは長い時間がかかるクエリを禁止できます。指定した時間よりクエリの実行時間が長くかかった場合、Sybase IQ は適切なエラーを表示してクエリを停止します。

注意 Sybase IQ では、小数の option-value の設定がすべて整数値にトランケー

トされます。たとえば、3.8 という値は 3 にトランケートされます。

パフォーマンス＆チューニング・ガイド 37

クエリ処理の制御

クエリの優先度の設定

処理をキューで待機しているクエリは、そのクエリを送信したユーザの優先度、そしてクエリが送信された順序の順に実行されます。優先度の高いクエリがすべて実行されるまで、優先度の低いキューのクエリは実行されません。

次のオプションは、クエリにユーザ別の処理の優先度を割り当てます。

• IQGOVERN_PRIORITY －処理キューで待機しているクエリに数字の優先度 (1、2、または 3 で、1 が最も高い ) を割り当てます。

• IQGOVERN_MAX_PRIORITY － DBA はユーザまたはグループの IQGOVERN_PRIORITY に上限値を設定できます。

• IQ_GOVERN_PRIORITY_TIME －優先度の高い ( 優先度 1 の ) クエリが、指定した時間より長く -iqgovern キューで待機している場合に、優先度の高いユーザを開始できます。

クエリの優先度を調べるには、sp_iqcontext ストアド・プロシージャによって返される IQGovernPriority 属性を確認します。

クエリ最適化オプションの設定

次のオプションは、クエリの処理速度に影響を与えます。

• AGGREGATION_PREFERENCE －集合 (GROUP BY、DISTINCT、SET の各関数 ) を処理するためのアルゴリズムの選択を制御しますこのオプションは、主に内部用として設計されているため、経験のあるデータベース管理者のみが使用してください。

• DEFAULT_HAVING_SELECTIVITY －クエリ内のすべての HAVING 述部の選択性を設定します。これが、HAVING 句によってフィルタされるロー数についてのオプティマイザの見積もりに優先して使用されます。

• DEFAULT_LIKE_MATCH_SELECTIVITY － LIKE 'string%string' (% はワイルドカード文字 ) などの、汎用 LIKE 述部のデフォルトの選択性

を設定します。他の選択性情報が利用できず、照合文字列が一連の定数文字と 1 つのワイルドカードで始まっていない場合、オプティマイザはこのオプションを利用します。

• DEFAULT_LIKE_RANGE_SELECTIVITY －照合文字列が一連の定数文字と 1 つのワイルドカード文字 (%) でできている LIKE 'string%' 形式の先行定数 LIKE 述部のデフォルトの選択性を設定します。他の選択性情報が利用できない場合、オプティマイザはこのオプションを利用します。

• EARLY_PREDICATE_EXECUTION －ジョインの最適化の前に簡単なローカル述部を実行するかどうかを制御します。通常は、このオプションを変更しないでください。

38 Sybase IQ

第 3 章クエリと削除の最適化

• ENABLED_ORDERED_PUSHDOWN_INSERTION －クエリ・オプティマイザが、ジョイン・オプティマイザによって選択されたプッシュダウン・ジョイン用のセミジョイン述部に追加する方法を制御します。それらのセミジョインによって間接的に影響を受ける可能性のある中間のジョインを再分析します。通常は、このオプションを変更しないでください。

• IN_SUBQUERY_PREFERENCE － IN サブクエリを処理するためのアルゴリズムの選択を制御します。このオプションは、主に内部用として設計されているため、経験のあるデータベース管理者のみが使用してください。

• INDEX_PREFERENCE －クエリ処理に使用するインデックスを設定します。Sybase IQ オプティマイザは、通常最適なインデックスを使用して、ローカルな WHERE 句の述部など、1 つの IQ インデックスの範囲内で処理できる操作を実行します。このオプションは、テスト目的にオプティマイザの選択を無効にするために使用します。通常の使用の際はこのオプションの値を変更しないでください。

• JOIN_PREFERENCE －ジョインを処理するときのアルゴリズムの選択を制御します。このオプションは、主に内部用として設計されているため、経験のあるデータベース管理者のみが使用してください。

• JOIN_SIMPLIFICATION_THRESHOLD －ジョイン・オプティマイザの単純化が適用される前にジョインされるテーブルの最小数を制御します。通常は、この値を変更する必要はありません。

• MAX_HASH_ROWS －クエリ・オプティマイザがハッシュ・アルゴリズムを使用するときに考慮する最大ロー数の推測値を設定します。デフォルトは、1,250,000 のローです。たとえば、2 つのテーブル間にジョインがあり、両方のテーブルからジョインに入力されるロー数がこのオプションで設定された値を超えると、オプティマイザはハッシュ・ジョインを選択肢から外します。TEMP_CACHE_MEMORY_MB がユーザあたり 50 MB を超えるシステムの場合は、このオプションにさらに大きな値を設定します。

• MAX_JOIN_ENUMERATION －オプティマイザの単純化が適用された後で、ジョイン順のために最適化するテーブルの最大数を設定します。通常は、このオプションを設定する必要はありません。

述部ヒントの設定

Sybase IQ は、選択性、有用性、インデックス設定、実行モードなどの述部単位のヒントを指定できるヒント文字列をサポートします。

選択性は他の 3 つのクエリ最適化と組み合わせて設定できます。

• インデックス設定オプションに相当する機能の設定

• 有用性の設定 ( 述部の順序付け )

•1 つ以上の述部の遅延

パフォーマンス＆チューニング・ガイド 39

削除オペレーションの最適化

通常の状況では、評価を遅らせることにメリットはなく、クエリの処理が遅くなるだけです。ただし、これによって次の 4 つの動作のいずれかをクエリ内のもっと後ろに移動できます。

• 最適化の前

• 初回の「最初のフェッチ」時

•2 回目の「最初のフェッチ」時 ( 相関サブクエリの内部またはネストループ・プッシュダウン・ジョインの左側のみ )

• インデックスの不使用 ( 水平処理 )

構文、パラメータ、使用例については、『Sybase IQ リファレンス・マニュアル』の「第 3 章 SQL 言語の要素」の「ユーザ指定の条件ヒント文字列」を参照してください。

削除オペレーションの最適化

Sybase IQ は、削除オペレーションを処理するために次の 3 つのアルゴリズムから 1 つを選択します。

• スモール・デリート

スモール・デリートでは、非常に少数のグループからローを削除するときに最適なパフォーマンスが得られます。通常は、削除するローが 1 つだけか、HG (High_Group) インデックスを持つカラムに等号述部がある場合に選択されます。スモール・デリート・アルゴリズムは、HG にランダムにアクセスできます。最悪の場合、I/O はアクセスされるグループの数に比例します。

• ミッド・デリート

ミッド・デリートでは、いくつかのグループからローを削除するときに最適なパフォーマンスが得られます。ただし、それらのグループが十分に分散されているか、十分に少なくて、あまり多くの HG ページがアクセスされないことが条件です。ミッド・デリート・アルゴリズムは、HG への順序付けられたアクセスを提供します。最悪の場合、I/O はインデックス・ページ数によって制限されます。ミッド・デリートは、削除するレコードのソートという追加的なコストを伴います。

• ラージ・デリート

ラージ・デリートでは、多数のグループからローを削除するときに最適なパフォーマンスが得られます。ラージ・デリートでは、すべてのローが削除されるまで HG が順番にスキャンされます。最悪の場合、I/O はインデックス・ページ数によって制限されます。ラージ・デリートは並列処理ですが、並列処理はインデックスの内部構造および削除対象のグループの分散度によって制限されます。HG カラムの範囲述部を使用して、ラージ・デリートのスキャン範囲を狭めることができます。

40 Sybase IQ

削除コスト

12.6 より前の HG 削除コスト・モデルでは、最悪の場合の I/O パフォーマンスだけが考慮されていたため、たいていラージ・デリートが優先的に使用されていました。現在のコスト・モデルでは、I/O コスト、CPU コスト、使用可能なリソース、インデックス・メタデータ、並列度、クエリから使用できる述部など、多数の要素が考慮されます。

HG インデックスを持つカラムの述部を指定すると、コストが大幅に改善されます。HG コスト計算でラージ・デリート以外のアルゴリズムを選択するためには、削除によって影響を受ける重複しない個別の値の数を判定できる必要があります。個別カウント数は、初めはインデックス・グループの数および削除されるローの数より少ないものと見なされます。述部は個別カウント数の改善された見積もりや、正確な見積もりでさえも提供できます。

現在のコスト計算では、ラージ・デリートにおける範囲述部の効果を考慮していません。そのため、ラージ・デリートのほうが速いケースでミッド・デリートが選択されることもあります。そうしたケースでは、必要に応じて強制的にラージ・デリート・アルゴリズムを適用できます。これについては、次の項で説明します。

削除パフォーマンス・オプションの使用

HG_DELETE_METHOD オプションを使用すると、HG 削除パフォーマンスを制御できます。

HG_DELETE_METHOD オプションでは、指定した削除アルゴリズムを強制的に適用できます。

•1 = スモール・デリート

•2 = ラージ・デリート

第 3 章クエリと削除の最適化

•3 = ミッド・デリート

パフォーマンス＆チューニング・ガイド 41

削除オペレーションの最適化

42 Sybase IQ

第 4 章 OLAP の使用

この章について

内容

オンライン分析処理 (OLAP: Online Analytical Processing) は、リレーショナル・データベースに格納されている情報を効率的に分析するための手法です。 OLAP を使用すると、データをさまざまな次元で分析し、小計ローを含んだ結果セットを取得し、データを多次元キューブに編成するという処理をすべて１つの SQL クエリで行うことができます。また、フィルタを使用してデータを絞り込み、結果セットを迅速に返すことができます。この章では、 Sybase IQ がサポートする SQL/OLAP 関数について説明します。

注意以降で紹介する OLAP の例に出てくるテーブルは、asiqdemo データ

ベースに含まれています。

トピック名ページ

OLAP について GROUP BY 句の拡張機能

統計関数単純な集合関数ウィンドウ

ランク付け関数ウィンドウ集合関数統計集合関数分散統計関数

数値関数 OLAP の規則と制限その他の OLAP の例

OLAP 関数の BNF 文法

パフォーマンス＆チューニング・ガイド 43

OLAP について

1999 年の SQL 標準の改正によって、ANSI SQL 標準に複雑なデータ分析操作を行うための拡張機能が導入されました。Sybase IQ では、以前のリリースでこれらの SQL 拡張機能の一部が取り入れられていますが、Sybase IQ 12.7 では、これらの拡張機能が包括的にサポートされています。

この分析機能を使って複雑なデータ分析を 1 つの SQL 文で実行することができますが、これはオンライン分析処理 (OLAP) と呼ばれるソフトウェア・テクノロジに基づいています。OLAP の関数には、GROUP BY 句の拡張機能や、次のような統計関数が含まれています。

• GROUP BY 句の拡張機能－ CUBE、ROLLUP

• 統計関数

• 単純な集合－ AVG 、COUNT、MAX、 MIN、SUM、STDDEV、VARIANCE

注意 Grouping() 以外の単純な集合関数はすべて OLAP ウィンドウ関

数と併用できます。

• ウィンドウ関数

• ウィンドウでの集合－ AVG、COUNT、MAX, MIN、SUM

• ランク付け関数－ RANK、DENSE_RANK、PERCENT_RANK、

NTILE

• 統計関数－ STDDEV、STDDEV_SAMP、STDDEV_POP、

VARIANCE、VAR_SAMP、VA R_POP

• 分散統計関数－ PERCENTILE_CONT、PERCENTILE_DISC

• 数値関数－ WIDTH_BUCKET、CEIL、LN、EXP、POWER、SQRT、

FLOOR

データベース製品によっては、OLAP モジュールが独立しており、分析前にデータをデータベースから OLAP モジュールに移動しなければならないものもあります。一方、Sybase IQ では OLAP 機能がデータベースそのものに組み込まれているため、ストアド・プロシージャなどのデータベース機能との配備や統合を簡単かつシームレスに行うことができます。

44 Sybase IQ

OLAP の利点

OLAP の評価について

第 4 章 OLAP の使用

OLAP 関数を GROUPING、CUBE、ROLLUP という拡張機能と組み合わせて使用すると、2 つの大きな利点があります。第一に、多次元のデータ分析、データ・マイニング、時系列分析、傾向分析、コストの割り当て、ゴール・シーク、一時的な多次元構造変更、非手続き型モデリング、例外の警告を多くの場合 1 つの SQL 文で実行できます。第二に、OLAP のウィンドウおよびレポート集合関数では、ウィンドウという関係演算子を使用することができ、これはセルフジョインや相関サブクエリを使用するセマンティック的に等価なクエリよりも効率的に実行できます。OLAP を使用して取得した結果セットには小計ローを含めることができ、この結果セットを多次元キューブに編成することもできます。詳細については、「ウィンドウ」(62 ページ ) を参照してください。

さまざまな期間での移動平均と移動和を計算したり、選択したカラムの値が変化したときに集計とランクをリセットしたり、複雑な比率を単純な言葉で表現したりできます。1 つのクエリ式のスコープ内で、それぞれ独自のパーティショニング・ルールを持ついくつかの異なる OLAP 関数を定義することができます。

OLAP の評価は、最終的な結果に影響を及ぼすクエリ実行のいくつかのフェーズとして概念化できます。OLAP の実行フェーズは、クエリ内の対応する句によって識別されます。たとえば、SQL クエリの指定にウィンドウ関数が含まれている場合は、WHERE、JOIN、GROUP BY、HAVING 句が先に処理されます。GROUP BY 句でグループが定義された後、クエリの ORDER BY 句に含まれる最後の SELECT リストが評価される前に、パーティションが作成されます。

グループ化の際には、NULL 値はすべて同じグループと見なされます ( それぞれの NULL 値が等しくない場合でも同様です )。

HAVING 句は、WHERE 句に似ており、GROUP BY 句の結果に対するフィルタとして機能します。

ANSI SQL 標準に基づく SQL 文と SELECT、FROM、WHERE、GROUP BY、 HAVING 句を含んだ単純なクエリ仕様のセマンティックを考えてみます。

1 クエリにより、FROM 句のテーブル式を満たすロー・セットが取得され

ます。

2 WHERE 句の述部が、テーブルから取得したロー・セットに適用されます。

WHERE 句の条件を満たさない ( 条件が true にならない ) ローが除外され

ます。

3 残りの各ローについて、SELECT リストおよび GROUP BY 句に含まれて

いる式 ( 集合関数を除く ) が評価されます。

パフォーマンス＆チューニング・ガイド 45

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 ページ ) を参照してください。OLAP 構文の詳細については、「OLAP 関数の BNF 文法」(98 ページ ) も参照してください。

46 Sybase IQ

GROUP BY 句の拡張機能

GROUP BY 句の拡張機能により、次のような処理を行う複雑な SQL 文を書くことができます。

• 入力ローを複数の次元に分割し、結果グループの複数のサブセットを組み合わせる。

•“データ・キューブ ” を作成し、データ・マイニング分析のための疎密度の多次元結果セットを用意する。

• 元のグループを含んだ結果セットを作成する ( 必要に応じて、小計ローと合計ローを含める場合もある )。

ROLLUP や CUBE などの OLAP の Grouping() ( グループ化 ) 操作は、プレフィクスや小計ローとして概念化できます。

プレフィクス

GROUP BY 句を含むクエリでは、プレフィクスのリストが作成されます。プレフィクスとは、GROUP BY 句の項目のサブセットであり、クエリの GROUP BY 句の項目のうち最も右にある 1 つまたは複数の項目を除外することで作成されます。残りのカラムはプレフィクス・カラムと呼ばれます。

ROLLUP 例 1　次に示す ROLLUP のクエリの例では、GROUP BY のリストに

2 つの変数 (Ye ar と Quarter) が含まれています。

SELECT year (order_date) Year, quarter(order_date)

Quarter, COUNT(*) Orders FROM alt_sales_order GROUP BY ROLLUP(Year, Quarter) ORDER BY Year, Quarter

このクエリには次の 2 つのプレフィクスがあります。

• Quarter を除外するプレフィクス－プレフィクス・カラムには 1 つのカラム (Yea r) が含まれます。

第 4 章 OLAP の使用

• Quarter と Ye a r の両方を除外するプレフィクス－プレフィクス・カラムは存在しません。

注意 GROUP BY リストには、項目と同じ数のプレフィクスが含まれます。

パフォーマンス＆チューニング・ガイド 47

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 演算子によって作成された以外の NULL 0 ( 偽 ) 格納されていた NULL 0 ( 偽 )

ROLLUP は、まず GROUP BY 句に指定された標準的な集合関数値を計算します。次に、ROLLUP はグループ化を行うカラムのリストを右から左に移動し、より高いレベルの小計を連続して作成します。最後に総計が作成されます。グループ化するカラムの数が n 個の場合、ROLLUP は n+1 レベルの小計を作成します。

… ] ) ]

SQL 構文の例定義されるセット

GROUP BY ROLLUP (A, B, C); (A, B, C)

(A, B)

(A)

( )

ROLLUP と小計ロー

ROLLUP は、GROUP BY のクエリ・セットに対して UNION を行うのと同じ

ことです。次の 2 つのクエリの結果セットは等しくなります。GROUP BY (A, B) の結果セットは、A と B に定数が含まれているすべてのローについての小

計から成ります。UNION を可能にするために、カラム C には NULL が割り当てられます。

48 Sybase IQ

第 4 章 OLAP の使用

ROLLUP クエリの例 ROLLUP を使用せずに記述した同じ内容のクエリ

SELECT A, B, C,

SUM( D ) FROM T1 GROUP BY ROLLUP (A, B,

C);

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 ) ) )

小計ローはデータの分析に役立ちます。特に、データが大量にある場合、データにさまざまな次元がある場合、データがさまざまなテーブルに含まれている場合、あるいはまったく異なるデータベースに含まれている場合に威力を発揮します。たとえば販売マネージャが、売上高についてのレポートを営業担当者別、地域別、四半期別に整理して、売上パターンの理解に役立てることができます。データの小計は、販売マネージャが売上高の全体像をさまざまな視点から分析するのに役立ちます。販売マネージャが比較したいと考える基準に基づいて要約情報が提供されていれば、データの分析を容易に行うことができます。

OLAP を使用すると、ローおよびカラムの小計を分析、計算する処理をユーザの目から隠すことができます。図 4-2 に、Sybase IQ での小計の計算の概念を示します。

図 4-2: 小計

1 このステップで、まだ ROLLUP とは見なされない中間の結果セットが生

成されます。

2 小計が評価され、結果セットに付加されます。

3 クエリ内の ORDER BY 句に従ってローが並べられます。

NULL 値と小計ロー

GROUP BY 操作に対する入力のローに NULL が含まれているときは、その中

に、ROLLUP または CUBE 操作によって追加された小計ローと、最初の入力データの一部として NULL 値を含んでいるローが混在している可能性があります。

Grouping() 関数は、小計ローをその他のローから区別します。具体的には、 GROUP BY リストのカラムを引数として受け取り、そのカラムが小計ローであるために NULL になっている場合は 1 を返し、それ以外の場合は 0 を返します。

パフォーマンス＆チューニング・ガイド 49

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.emp_id = alt_sales_order.sales_rep WHERE employee.sex IN ('F') AND employee.state

IN ('TX', 'CA', 'NY') GROUP BY ROLLUP (Year, Employee) ORDER BY Year, Employee

このクエリの結果セットを次に示します。

emp_id year Orders GY GE

------ ---- ------ -- -NULL NULL 1 1 0 NULL NULL 165 1 1 1090 NULL 1 0 0 NULL 2000 98 1 0

667 2000 34 0 0

949 2000 31 0 0 1142 2000 33 0 0 NULL 2001 66 1 0

667 2001 20 0 0 949 2001 22 0 0 1142 2001 24 0 0

個々のプレフィクスについて、プレフィクス・カラムに同じ値が含まれているすべてのローに関する小計ローが作成されます。

ROLLUP の結果を具体的に説明するために、前述のクエリの例をもう一度詳しく見ていきます。

SELECT year (order_date) AS Year, quarter

(order_date) AS Quarter, COUNT (*) Orders

FROM sales_order

GROUP BY ROLLUP (Year, Quarter) ORDER BY Year, Quarter

このクエリでは、Year カラムを含んでいるプレフィクスにより、Year=2000 の合計ローと Year=2001 の合計ローが作成されます。このプレフィクスに関する 1 つの合計ローはカラムを含んでいません。これは、中間の結果セットに含まれているすべてのローの小計です。

50 Sybase IQ

第 4 章 OLAP の使用

小計ローの各カラムの値は、次のようになっています。

• プレフィクスに含まれているカラム－そのカラムの値です。たとえば前述のクエリでは、Year=2000 のローに関する小計ローの Yea r カラムの値は 2000 になります。

• プレフィクスから除外されたカラム－ NULL です。たとえば、Year カラムから成るプレフィクスにより生成された小計ローでは、Quarter カラムの値は NULL になります。

• 集合関数－除外されているカラムの値を計算した結果です。

小計値は、集計されたローではなく基本データのローに対して計算されます。多くの場合、たとえば SUM や COUNT などでは結果は等しくなりますが、AVG 、STDDEV、VARIANCE などの統計関数では結果が異なってくるため、この区別は重要です。

ROLLUP 演算子には次の制限があります。

• ROLLUP 演算子は、COUNT DISTINCT と SUM DISTINCT を除き、GROUP BY 句で使用可能なすべての集合関数をサポートしています。

• ROLLUP は SELECT 文でのみ使用できます。サブクエリでは ROLLUP を

使用できません。

•1 つの GROUP BY 句の中で複数の ROLLUP、CUBE、および GROUP BY カラムを組み合わせるグループ化の指定は、現時点ではサポートされていません。

• GROUP BY のキーに定数式を指定することはできません。

式の一般的なフォーマットについては、『Sybase IQ リファレンス・マニュアル』の「式」と「SQL 言語の要素」を参照してください。

ROLLUP 例 2　次は、ROLLUP と GROUPING の使用例です。GROUPING によっ

て作成される一連のマスク・カラムを表示します。カラム S、N、C に表示されている数字 0 と 1 は、GROUPING からの戻り値で ROLLUP の結果の値を表現しています。マスクが “011” であれば小計のローであり、“111” であれば総計のローであると特定できます。これを利用して、クエリの結果をプログラムで分析することが可能です。

SELECT size, name, color, SUM(quantity),

GROUPING(size) AS S, GROUPING(name) AS N,

GROUPING(color) AS C FROM product GROUP BY ROLLUP(size, name, color) HAVING (S=1 or N=1 or C=1) ORDER BY size, name, color;

このクエリの結果セットを次に示します。

size name color SUMSNC

---- ----- ------ ------

(NULL) (NULL) (NULL) 496 1 1 1

パフォーマンス＆チューニング・ガイド 51

GROUP BY 句の拡張機能

Large (NULL) (NULL) 71 0 1 1 Large Sweatshirt (NULL) 71 0 0 1 Medium (NULL) (NULL) 134 0 1 1 Medium Shorts (NULL) 80 0 0 1 Medium Tee Shirt (NULL) 54 0 0 1 One size fits all (NULL) (NULL) 263 0 1 1 One size fits all Baseball Cap (NULL) 124 0 0 1 One size fits all Tee Shirt (NULL) 75 0 0 1 One size fits all Visor (NULL) 64 0 0 1 Small (NULL) (NULL) 28 0 1 1 Small Tee Shirt (NULL) 28 0 1 1

ROLLUP 例 3　

次の例は、GROUPING を使用して、最初から格納されていた

NULL 値と ROLLUP 操作によって生成された “NULL” 値とを区別する方法を

示しています。このクエリで指定されているとおり、最初から格納されていた NULL 値はカラム prod_id に [NULL] として表示され、ROLLUP によって生成された “NULL” 値はカラム PROD_IDS で ALL に置き換えられます。

SELECT year(ship_date) AS Year, prod_id, SUM(quantity)

AS OSum, CASE WHEN GROUPING(Year) = 1 THEN 'ALL' ELSE CAST(Year AS char(8)) END, CASE WHEN GROUPING(prod_id) = 1 THEN 'ALL' ELSE CAST(prod_id

as char(8)) END FROM alt_sales_order_items GROUP BY ROLLUP(Year, prod_id) HAVING OSum > 36 ORDER BY Year, prod_id;

このクエリの結果セットを次に示します。

ship_date prod_id SUM SHIP_DATES PROD_IDS

--------- ------- --- ---------- --------

NULL NULL 28359 ALL ALL 2000 NULL 17642 2000 ALL 2000 300 1476 2000 300 2000 301 1440 2000 301 2000 302 1152 2000 302 2000 400 1946 2000 400 2000 401 1596 2000 401 2000 500 1704 2000 500 2000 501 1572 2000 501 2000 600 2124 2000 600 2000 601 1932 2000 601 2000 700 2700 2000 700 2001 NULL 10717 2001 ALL 2001 300 888 2001 300 2001 301 948 2001 301 2001 302 996 2001 302 2001 400 1332 2001 400 2001 401 1105 2001 401 2001 500 948 2001 500

52 Sybase IQ

第 4 章 OLAP の使用

2001 501 936 2001 501 2001 600 936 2001 600 2001 601 792 2001 601 2001 700 1836 2001 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 年 ) のすべての四半期の注文数の合計を示しています。このローは、Ye ar カラムと Quarter カラムの両方が NULL であり、すべてのカラムがプレフィクスから除外されています。

注意 ROLLUP 操作によって返される結果セットには、集合カラムを除くすべ

てのカラムが NULL であるローが必ず 1 つ含まれています。このローは、集合関数に対する全カラムの要約を表しています。たとえば、集合関数として SUM を使用している場合は、このローはすべての値の総計を表します。

ロー [2] は、2000 年および 2001 年の注文数の合計をそれぞれ示しています。どちらのローも、Quarter カラムの値は NULL になっています。このカラムの値を加算して、Year の小計を出しているためです。結果セットにこのようなローがいくつ含まれるかは、ROLLUP クエリに登場する変数の数によって決まります。

[3] としてマークされている残りのローは要約情報を示し、それぞれの年の各四半期の注文数の合計を表しています。

パフォーマンス＆チューニング・ガイド 53

GROUP BY 句の拡張機能

ROLLUP 例 5　この ROLLUP 操作の例では、年別、四半期別、地域別の注文数

を集計するというやや複雑な結果セットを返します。この例では、第 1 および第 2 四半期と 2 つの地域 ( カナダと東部地区 ) だけを分析します。

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 が含まれています。このローの Orders カラムの値は、カナダと東部地区の 2000 年および 2001 年の第 1 および第 2 四半期の注文数の合計を示しています。

[2] としてマークされているローは、それぞれの年 (2000 年と 2001 年 ) におけるカナダと東部地区の第 1 および第 2 四半期の注文数の合計を示しています。ロー [2] の値を足すと、ロー [1] に示されている総計に等しくなります。

54 Sybase IQ

第 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 ( 真 ) CUBE 演算子によって作成された以外の NULL 0 ( 偽 ) 格納されていた NULL 0 ( 偽 )

CUBE は、同じ階層の一部ではない次元を扱うときに特に威力を発揮します。

SQL 構文の例定義されるセット

GROUP BY CUBE (A, B, C); (A, B, C)

(A, B)

(A, C)

(A)

(B, C)

(B)

(C)

( )

CUBE 演算子には次の制限があります。

• CUBE 演算子は GROUP BY 句で使用可能なすべての集合関数をサポート

しますが、CUBE は現在 COUNT DISTINCT および SUM DISTINCT ではサポートされていません。

• CUBE は、現在、逆分散統計関数である PERCENTILE_CONT と PERCENTILE_DISC ではサポートされていません。

• CUBE は SELECT 文でのみ使用できます。CUBE を SELECT のサブクエリで使用することはできません。

•1 つの GROUP BY 句の中で ROLLUP、CUBE、GROUP BY カラムを組み合わせる GROUPING の指定は、現時点ではサポートされていません。

56 Sybase IQ

第 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 句だけを使用し、ローのグループ化に CUBE 演算子を使用していません。

SELECT state, sex as gender, dept_id, COUNT(*),

CAST(ROUND(AVG(salary),2) AS NUMERIC(18,2))

AS average FROM employee WHERE state IN ('MA' , 'CA') GROUP BY state, sex, dept_id ORDER BY 1,2;

このクエリの結果セットを次に示します。

state gender dept_id count(*) avg salary

----- ------ ------- -------- ---------CA F 200 2 58650.00 CA M 200 1 39300.00 MA F 500 4 29950.00 MA F 400 8 41959.88 MA F 300 7 59685.71 MA F 200 3 60451.00 MA F 100 6 58243.42 MA M 300 2 58850.00 MA M 500 5 36793.96 MA M 400 8 45321.47 MA M 100 13 58563.59 MA M 200 8 46810.63

パフォーマンス＆チューニング・ガイド 57

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 grouping(state) WHEN 1 THEN 'ALL' ELSE state

END AS c_state, case grouping(sex) WHEN 1 THEN 'ALL' ELSE sex end AS c_gender, case grouping(dept_id) WHEN 1 THEN 'ALL' ELSE cast(dept_id as char(4)) end AS c_dept, COUNT(*), CAST(ROUND(AVG(salary),2) AS

NUMERIC(18,2))AS AVERAGE FROM employee WHERE state IN ('MA' , 'CA') GROUP BY CUBE(state, sex, dept_id) ORDER BY 1,2,3;

このクエリの結果は次のとおりです。クエリで指定されているとおり、小計ローを示すために CUBE によって生成された NULL は、小計ロー内で ALL に置き換えられています。

state sex dept_id count avg salary

----- --- ------- ----- ----------

ALL ALL 100 19 58462.48 ALL ALL 200 14 50888.43 ALL ALL 300 9 59500.00 ALL ALL 400 16 43640.67 ALL ALL 500 9 33752.20 ALL ALL ALL 67 50160.38 ALL F 100 6 58243.42 ALL F 200 5 59730.60 ALL F 300 7 59685.71 ALL F 400 8 41959.88 ALL F 500 4 29950.00 ALL F ALL 30 50713.08 ALL M 100 13 58563.59 ALL M 200 9 45976.11 ALL M 300 2 58850.00 ALL M 400 8 45321.47 ALL M 500 5 36793.96 ALL M ALL 37 49712.25 CA ALL 200 3 52200.00

58 Sybase IQ

第 4 章 OLAP の使用

CA ALL ALL 3 52200.00 CA F 200 2 58650.00 CA F ALL 2 58650.00 CA M 200 1 39300.00 CA M ALL 1 39300.00 MA ALL 100 19 58462.48 MA ALL 200 11 50530.73 MA ALL 300 9 59500.00 MA ALL 400 16 43640.67 MA ALL 500 9 33752.20 MA ALL ALL 64 50064.78 MA F 100 6 58243.42 MA F 200 3 60451.00 MA F 300 7 59685.71 MA F 400 8 41959.88 MA F 500 4 29950.00 MA F ALL 28 50146.16 MA M 100 13 58563.59 MA M 200 8 46810.63 MA M 300 2 58850.00 MA M 400 8 45321.47 MA M 500 5 36793.96 MA M ALL 36 50001.48

CUBE 例 3　

この例のクエリは、注文数の合計を要約する結果セットを返し、次

に、年別および四半期別の注文数の小計を計算します。

注意比較する変数の数が増えると、キューブの計算のコストが急激に増大し

ます。

SELECT year(order_date) AS Year, quarter(order_date)

AS Quarter, COUNT(*) AS Orders FROM alt_sales_order GROUP BY CUBE(Year, Quarter) ORDER BY Year, Quarter

パフォーマンス＆チューニング・ガイド 59

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 つの引数を取る単項の統計集合関数には、STDDEV()、STDDEV_SAMP()、

STDDEV_POP()、VAR IANCE ()、VAR_SAMP()、および VA R_P OP() があります。

単純な集合関数でも単項の集合関数でも、データベース内のローのグループに関するデータを要約することができ、ウィンドウ指定と組み合わせて、処理の際に結果セットに対する移動ウィンドウを計算することができます。

注意集合関数 AVG、SUM、STDDEV、STDDEV_POP、STDDEV_SAMP、

VAR_POP、VAR_SAMP、VARIANCE は、バイナリ・データ型である BINARY と VARBINARY をサポートしていません。

単純な集合関数

単純な集合関数 (AVG、COUNT、MAX、 MIN、SUM など ) は、データベースに含まれるローのグループのデータを要約します。ローのグループを形成するには SELECT 文の GROUP BY 句を使用します。集合関数は、select リストと、 SELECT 文の HAVING 句および ORDER BY 句の中だけで使用できます。

注意 Grouping() 関数を除き、単純な集合関数と単項の集合関数はどちらも、

SQL クエリの指定に「ウィンドウ句」( ウィンドウ ) を組み込むウィンドウ関数として使用できます。これにより、処理時に結果セットに対して概念的に移動ウィンドウを作成することができます。詳細については、「ウィンドウ」(62

ページ ) を参照してください。

詳細については、Sybase IQ リファレンス・マニュアルの「第 5 章 SQL 関数」の「集合関数」を参照してください。

パフォーマンス＆チューニング・ガイド 61

統計関数

ウィンドウ

OLAP ウィンドウの 3 つの重要な側面

OLAP に関する ANSI SQL 拡張機能で導入された主な機能は、ウィンドウと呼ぶ構造です。このウィンドウ拡張機能により、ユーザはクエリの結果セット ( または、クエリの論理パーティション ) をパーティションと呼ばれるローのグループに分割し、現在のローについて集計するローのサブセットを決定することができます。

1 つのウィンドウには 3 つのクラスのウィンドウ関数として、ランク付け関数、ロー・ナンバリング関数、およびウィンドウ集合関数を使用できます。

<WINDOWED TABLE FUNCTION TYPE> ::=

<RANK FUNCTION TYPE> <LEFT PAREN> <RIGHT PAREN> | ROW_NUMBER <LEFT PAREN> <RIGHT PAREN> | <WINDOW AGGREGATE FUNCTION>

詳細については、「文法規則 6」(98 ページ ) を参照してください。

ウィンドウ拡張機能は、ウィンドウ名または指定に対するウィンドウ関数の種類を指定し、1 つのクエリ式のスコープ内のパーティション化された結果セットに適用されます。ウィンドウ・パーティションは、特殊な OVER 句の 1 つ以上のカラムで定義されている、クエリから返されるローのサブセットです。

olap_function() OVER (PARTITION BY col1, col2...)

ウィンドウ操作では、パーティション内の各ローのランク付け、パーティション内のローの値の分布、および同様の操作などの情報を設定できます。また、データの移動平均や合計を計算し、データおよび操作に対するそのデータの影響を評価する機能を拡張することもできます。

ウィンドウ・パーティションは、特殊な OVER() 句の 1 つ以上のカラムで定義されている、クエリから返されるローのサブセットです。

OLAP_FUNCTION() OVER (PARTITION BY col1, col2...)

OLAP ウィンドウは、ウィンドウ・パーティション、ウィンドウ順序、ウィンドウ・フレームという 3 つの重要な側面から成ります。それぞれの要素は、その時点でウィンドウ内で可視となるデータ・ローに大きな影響を与えます。また、OLAP の OVER 句は、次の 3 つの特徴的な機能により、OLAP 関数を他の統計関数やレポート関数から区別します。

• ウィンドウ・パーティションの定義 (PARTITION BY 句 )。詳細については、「ウィンドウ・パーティション」(64 ページ ) を参照してください。

• パーティション内でのローの順序付け (ORDER BY 句 )。詳細については、

「ウィンドウ順序」(64 ページ ) を参照してください。

• ウィンドウ・フレームの定義 (ROWS/RANGE 指定 )。「ウィンドウ・フレー

ム」(65 ページ )。

62 Sybase IQ

第 4 章 OLAP の使用

OLAP のウィンドウ指定に関して名前を指定することができます。冗長なウィンドウ定義を避けるために、この名前を使用して複数のウィンドウ関数を指定できます。その場合は、キーワード WINDOW の後に少なくとも 1 つのウィンドウ定義を指定します ( 複数指定する場合はカンマで区切ります )。ウィンドウ定義には、クエリ内でウィンドウを識別するための名前と、ウィンドウのパーティション、順序、フレームを定義するためのウィンドウ指定の詳細を含めます。

<WINDOW DEFINITION LIST> ::=

<WINDOW DEFINITION> [ { <COMMA> <WINDOW DEFINITION> } . . . ]

<WINDOW DEFINITION> ::=

<WINDOW SPECIFICATION DETAILS> ::=

[ <EXISTING WINDOW NAME> ] [ <WINDOW PARTITION CLAUSE> ] [ <WINDOW ORDER CLAUSE> ] [ <WINDOW FRAME CLAUSE> ]

ウィンドウ・パーティション内の各ローについて、ウィンドウ・フレームを定義することができます。ウィンドウ・フレームにより、パーティションの現在のローに対して計算を実行するときに使われるローの範囲を変更することができます。現在のローは、ウィンドウ・フレームの開始ポイントと終了ポイントを決定するための参照ポイントとなります。

ウィンドウのサイズは、物理的なローの数 ( ウィンドウ・フレーム単位 ROWS を定義するウィンドウ指定を使用 ) または論理的な数値の間隔 ( ウィンドウ・フレーム単位 RANGE を定義するウィンドウ指定を使用 ) に基づきます。詳細については、「ウィンドウ・フレーム」(65 ページ ) を参照してください。

OLAP のウィンドウ操作では、次のカテゴリの関数を使用できます。

• 「ランク付け関数」(75 ページ )

• 「ウィンドウ集合関数」(80 ページ )

• 「統計集合関数」(81 ページ )

• 「分散統計関数」(82 ページ )

パフォーマンス＆チューニング・ガイド 63

統計関数

ウィンドウ・パーティション

ウィンドウ・パーティションとは、 PARTITION BY 句を使用して、ユーザ指定の結果セット ( 入力ロー ) を分割することです。パーティションは、カンマで区切られた 1 つ以上の値の式によって定義されます。パーティションに分割されたデータは暗黙的にソートされ、デフォルトのソート順序は昇順 (ASC) になります。

ウィンドウ・パーティション句を指定しなかった場合は、入力が 1 つのパーティションとして扱われます。

注意統計関数に対してパーティションという用語を使用した場合は、結果

セットのローを PARTITION BY 句に基づいて分割することだけを意味します。

ウィンドウ・パーティションは任意の式に基づいて定義できます。また、ウィンドウ・パーティションの処理はグループ化の後に行われるので (GROUP BY 句が指定されている場合 )、SUM、AVG、VARIANCE などの集合関数の結果をパーティションの式で使用することができます。したがって、パーティションを使用すると、GROUP BY 句や ORDER BY 句とはまた別に、グループ化と順序付けの操作を実行することができます。たとえば、ある数量の最大 SUM を求めるなど、集合関数に対して集合関数を計算するクエリを記述できます。

GROUP BY 句がない場合でも、PAR TITION BY 句を指定できます。

<WINDOW PARTITION CLAUSE> ::=

PARTITION BY <WINDOW PARTITION EXPRESSION LIST>

ウィンドウ順序

ウィンドウ順序とは、ウィンドウ・パーティション内の結果 ( ロー ) をウィンドウ順序句に基づいて並べることです。ウィンドウ順序句には、1 つ以上の値の式をカンマ区切りで指定します。ウィンドウ順序句を指定しなかった場合は、入力ローが任意の順序で処理されることがあります。

OLAP のウィンドウ順序句は、非ウィンドウ・クエリの式に指定できる ORDER BY 句とは異なります。詳細については、「文法規則 31」(100 ページ ) を参照し

てください。

OLAP 関数で使用する ORDER BY 句は、通常はウィンドウ・パーティション内のローをソートするための式を定義しますが、PA RTITION B Y 句がなくても ORDER BY 句を使用することができます。その場合は、このソート指定によって、確実に意味のある ( かつ意図どおりの ) 順序で並べられた中間の結果セットに OLAP 関数を適用することができます。

64 Sybase IQ

第 4 章 OLAP の使用

OLAP のランク付け関数には順序の指定が必須であり、ランキング値の基準は、ランク付け関数の引数ではなく ORDER BY 句で指定します。OLAP の集合関数では、通常は ORDER BY 句の指定は必須ではありませんが、ウィンドウ・フレームを定義するときには必須とされています (「ウィンドウ・フレーム」

(65 ページ ) を参照してください )。これは、各フレームの適切な集合値を計算

する前に、パーティション内のローをソートしなければならないためです。

この ORDER BY 句には、昇順および降順のソートを定義するためのセマンティックと、NULL 値の取り扱いに関する規則を指定します。OLAP 関数は、デフォルトでは昇順 (最も小さい値が 1 番目にランク付けされる) を使用します。

これは SELECT 文の最後に指定する ORDER BY 句のデフォルト動作と同じですが、連続的な計算を行う場合にはわかりにくいかもしれません。OLAP の計算では、降順 ( 最も大きい値が 1 番目にランク付けされる ) でのソートが必要になることがよくあります。この要件を満たすには、ORDER BY 句に明示的に DESC キーワードを指定する必要があります。

注意ランク付け関数は、ソートされた入力のみを扱うように定義されている

ため、「ウィンドウ順序句」の指定を必要とします。「クエリ指定」の「order by 句」と同様に、デフォルトのソート順序は昇順です。

「ウィンドウ・フレーム単位」で RANGE を使用する場合も、「ウィンドウ順序句」

を指定する必要があります。RANGE の場合は、「ウィンドウ順序句」に 1 つの式のみを指定します。「ウィンドウ・フレーム」(65 ページ ) を参照してください。

ウィンドウ・フレーム

ランク付け関数を除く OLAP 集合関数では、ウィンドウ・フレーム句を使用してウィンドウ・フレームを定義することができます。ウィンドウ・フレーム句には、現在のローを基準としてウィンドウの開始位置と終了位置を指定します。

<WINDOW FRAME CLAUSE> ::=

これにより、パーティション全体の固定的な内容ではなく、移動するフレームの内容に対して OLAP 関数を計算できます。定義にもよりますが、パーティションには開始ローと終了ローがあり、ウィンドウ・フレームは開始ポイントからパーティションの終了位置に向けてスライドします。

パフォーマンス＆チューニング・ガイド 65

統計関数

図 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 を指定する場合、そのウィンドウには、現在のローに指定の数値を足した数よりも少ないローが含まれます。RANGE を指定する場合、そのウィンドウ値のデータ型は、ORDER BY 句のソート・キー式の型に対応している必要があります。指定できるソート・キー式は 1 つだけで、このソート・キー式のデータ型は「加算」を許可していなければなりません。

PREDCEEDING の値では、現在のロー以前にあるローの範囲または数を指定します。ROWS を指定する場合、その値には、ローの数を表す正の数を指定します。RANGE を指定する場合、そのウィンドウには、現在のローから指定の数値を引いた数よりも少ないローが含まれます。RANGE を指定する場合、そのウィンドウ値のデータ型は、ORDER BY 句のソート・キー式の型に対応している必要があります。指定できるソート・キー式は 1 つだけで、このソート・キー式のデータ型は「減算」を許可していなければなりません。1 つ目のバインドされたグループで CURRENT ROW または FOLLOWING の値を指定している場合は、2 つ目のバインドされたグループにこの句を指定することはできません。

66 Sybase IQ

CURRENT ROW の概念

第 4 章 OLAP の使用

BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING の組み合わせを使用すると、グループ化したクエリとのジョインを構築しなくても、パーティション全体についての集合を計算できます。パーティション全体についての集合は、レポート集合とも呼ばれます。

物理的な集合グループでは、現在のローに対する相対位置に基づき、隣接するローの数に応じて、ローを含めるか除外するかが判断されます。現在のローは、クエリの中間結果における次のローへの参照にすぎません。現在のローが前に進むと、ウィンドウ内に含まれる新しいロー・セットに基づいてウィンドウが再評価されます。現在のローをウィンドウ内に含めるという要件はありません。

ウィンドウ・フレーム句を指定しなかった場合のデフォルトのウィンドウ・フレームは、ウィンドウ順序句を指定しているかどうかによって異なります。

• ウィンドウ指定にウィンドウ順序句が含まれている場合は、ウィンドウの開始ポイントは UNBOUNDED PRECEDING、終了ポイントは CURRENT

ROW になり、累積値の計算に適した可変サイズのウィンドウになります。

• ウィンドウ指定にウィンドウ順序句が含まれていない場合は、ウィンドウの

開始ポイントは UNBOUNDED PRECEDING、終了ポイントは UNBOUNDED FOLLOWING になり、現在のローに関係なく固定サイズのウィンドウになり

ます。

注意ウィンドウ・フレーム句はランク付け関数とは併用できません。

ローベース ( ロー指定 ) または値ベース ( 範囲指定 ) のウィンドウ・フレーム単位を指定してウィンドウを定義することもできます。

<WINDOW FRAME UNIT> ::= ROWS | RANGE

ウィンドウ・フレーム句で BETWEEN を使用するときは、ウィンドウ・フレームの開始ポイントと終了ポイントを明示的に指定します。

ウィンドウ・フレーム句でこの 2 つの値のどちらか一方しか指定しなかった場合は、他方の値がデフォルトで CURRENT ROW になります。

ROWS

ウィンドウ・フレーム単位 ROWS では、現在のローの前後に指定の数のローを含んでいるウィンドウを定義します ( 現在のローは、ウィンドウの開始ポイントと終了ポイントを決定するための参照ポイントになります )。それぞれの分析計算は、パーティション内の現在のローに基づいて行われます。ローで表現されるウィンドウを使用して限定的な結果を生成するには、ユニークな順序付けの式を指定する必要があります。

パフォーマンス＆チューニング・ガイド 67

統計関数

どのウィンドウ・フレームでも、現在のローが参照ポイントになります。 SQL/OLAP の構文には、ローベースのウィンドウ・フレームを、現在のローの前または後にある任意の数のロー ( あるいは現在のローの前および後ろにある任意の数のロー ) として定義するためのメカニズムが用意されています。

ウィンドウ・フレーム単位の代表的な例を次に示します。

• Rows Between Unbounded Preceding and Current Row －各パーティションの先頭を開始ポイントとし、現在のローを終了ポイントとするウィンドウを指定します。累積和など、累積的な結果を計算するためのウィンドウを構築するときによく使用されます。

• Rows Between Unbounded Preceding and Unbounded Following －現在のローに関係なく、パーティション全体についての固定ウィンドウを指定します。そのため、ウィンドウ集合関数の値は、パーティションのすべてのローで等しくなります。

• Rows Between 1 Preceding and 1 Following － 3 つの隣接するロー ( 現在のローとその前および後のロー) を含む固定サイズの移動ウィンドウを指定します。このウィンドウ・フレーム単位を使用して、たとえば 3 日間または 3 か月間の移動平均を計算できます。詳細については、図 4-3 (66 ペー

ジ ) を参照してください。

ウィンドウ値にギャップがあると、ROWS を使用した場合に意味のない結果が生成されることがあるので注意してください。値セットが連続していない場合は、ROWS の代わりに RANGE を使用することを検討してください。RANGE に基づくウィンドウ定義では、重複する値を含んだ隣接ローが自動的に処理され、範囲内にギャップがあるときに他のローが含まれません。

注意移動ウィンドウでは、入力の最初のローの前、および入力の最後の

ローの後ろには、NULL 値を含むローが存在することが想定されます。つまり、3 つのローから成る移動ウィンドウの場合は、入力の最後のローを現在のローとして計算するときに、直前のローと NULL 値が計算に含まれます。

• Rows Between Current Row and Current Row －ウィンドウを現在のローのみに制限します。

• Rows Between 1 Preceding and 1 Preceding －現在のローの直前のローだけを含む単一ローのウィンドウを指定します。この指定を、現在のローのみに基づく値を計算する別のウィンドウ関数と組み合わせると、隣接するロー同士のデルタ ( 値の差分 ) を簡単に計算することができます。詳細については、「隣接ロー間のデルタの計算」(72 ページ ) を参照してください。

68 Sybase IQ

第 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] が除外される。

• ロー [4] が現在のローであるときは、ロー [1]、[2]、[3] が除外される。

• ロー [5] が現在のローであるときは、ロー [1]、[2]、[3]、[4] が除外される。

次の図では、この規則を具体的な値セットに適用し、OLAP の AVG 関数を使用して各ローの計算を行っています。スライド計算により、現在のローの位置に応じて、3 つまたはそれ以下のローを範囲として移動平均を算出しています。

次のクエリは、移動ウィンドウの定義の例を示しています。

SELECT dimension, measure,

AVG(measure) OVER(partition BY dimension

ORDER BY measure ROWS BETWEEN CURRENT ROW and 2 FOLLOWING) AS olap_avg

FROM ...

パフォーマンス＆チューニング・ガイド 69

統計関数

RANGE

平均値は次のようにして計算されています。

• ロー [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 になります。

範囲ベースのウィンドウ・フレーム　前述のローベースのウィンドウ・フレームの

例では、さまざまなローベースのウィンドウ・フレーム定義の中から 1 つを紹介しました。SQL/OLAP 構文では、また別の種類のウィンドウ・フレームとして、物理的なローのシーケンスではなく、値ベース ( または範囲ベース ) のロー・セットに基づいて境界を定義する方法が用意されています。

値ベースのウィンドウ・フレームは、ウィンドウ・パーティション内で、特定の範囲の数値を含んでいるローを定義します。OLAP 関数の ORDER BY 句では、範囲指定を適用する数値カラムを定義します。このカラムの現在のローの値が、範囲指定の基準となります。範囲指定ではロー指定と同じ構文を使用しますが、構文の解釈の仕方は異なります。

ウィンドウ・フレーム単位 RANGE では、特定の順序付けカラムについて現在のローを基準とする値範囲を指定し、その範囲内の値を持つローを検索して、ウィンドウ・フレームに含めます。これは論理的なオフセットに基づくウィンドウ・フレームと呼ばれ、“3 preceding” などの定数を指定することも、評価結果が数値定数となる任意の式を指定することもできます。RANGE に基づくウィンドウを使用するときは、ORDER BY 句に数値式を 1 つだけ指定します。

たとえば、次のように指定すると、year カラムに現在のローの前後数年に当たる値を含んでいるロー・セットをフレームとして定義できます。

ORDER BY year ASC range BETWEEN CURRENT ROW and 1 PRECEDING

このクエリ例の 1 PRECEDING という部分は、現在のローの year 値から 1 を減算することを意味しています。

70 Sybase IQ

第 4 章 OLAP の使用

このような範囲指定は内包的です。現在のローの year 値が 2000 である場合は、ウィンドウ・パーティション内で、year 値が 2000 および 1999 であるすべてのローがこのフレームに含まれることになります。パーティション内での各ローの物理的な位置は問われません。値ベースのフレームでは、ローを含めたり除外したりする規則が、ローベースのフレームの規則とは大きく異なります ( ローベースのフレームの規則は、ローの物理的なシーケンスに完全に依存しています )。

OLAP の AVG( ) 関数の例で考えてみます。次の部分的な結果セットは、値ベースのウィンドウ・フレームの概念を具体的に表しています。前述のように、このフレームには次のローが含まれます。

• 現在のローと同じ year 値を持つロー

• 現在のローから 1 を減算したのと同じ year 値を持つロー

次のクエリは、範囲ベースのウィンドウ・フレーム定義の例を示しています。

SELECT dimension, year, measure,

AVG(measure) OVER(PARTITION BY dimension

ORDER BY year ASC range BETWEEN CURRENT ROW and 1 PRECEDING)

FROM ...

as olap_avg

平均値は次のようにして計算されています。

• ロー [1] = 1999 のため、ロー [2] ～ [5] は除外。したがって AVG = 10,000/1

• ロー [2] = 2001 のため、ロー [1]、[4]、[5] は除外。したがって AVG = 6,000/2

• ロー [3] = 2001 のため、ロー [1]、[4]、[5] は除外。したがって AVG = 6,000/2

• ロー [4] = 2002 のため、ロー [1] は除外。したがって AVG = 21,000/4

• ロー [5] = 2002 のため、ロー [1] は除外。したがって AVG = 21,000/4

値ベースのフレームの昇順と降順　値ベースのウィンドウ・フレームを使用する

OLAP 関数の ORDER BY 句では、範囲指定の対象となる数値カラムを特定するだけではなく、ORDER BY 値のソート順序も宣言できます。次の指定により、直前の部分のソート順序 (ASC または DESC) を設定できます。

RANGE BETWEEN CURRENT ROW AND n FOLLOWING

パフォーマンス＆チューニング・ガイド 71

統計関数

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 値のソート順序は、値ベースのフレームに含まれるローの条

件をテストするときに重要な要素です。フレームに含まれるか除外されるかは、数値だけでは決まりません。

無制限ウィンドウの使用　次のクエリでは、すべての製品と全製品の総数から成

る結果セットが生成されます。

SELECT id, description, quantity,

SUM(quantity) OVER () AS total

FROM product;

隣接ロー間のデルタの計算　

現在のローと前のローをそれぞれ 1 つのウィンドウとして定義し、この 2 つのウィンドウを使用すると、隣接するロー間のデルタ ( つまり差分 ) を直接的に計算することができます。次のクエリ例と結果を確認してください。

SELECT emp_id, emp_lname, SUM(salary) OVER (ORDER BY

birth_date rows between current row and current row) AS curr, SUM(salary) OVER (ORDER BY birth_date rows between 1 preceding and 1 preceding) AS prev, (curr

-prev) as delta

FROM employee WHERE state IN ('MA', 'AZ') AND dept_id

=100

ORDER BY emp_id, emp_lname;

72 Sybase IQ

第 4 章 OLAP の使用

このクエリの結果セットを次に示します。

emp_id emp_lname curr prev delta

------ --------- --------- ---------- ---------102 Whitney 45700.000 64500.000 -18800.000 105 Cobb 62000.000 68400.000 -6400.000 160 Breault 57490.000 96300.000 -38810.000 243 Shishov 72995.000 59840.000 13155.000 247 Driscoll 48023.690 87900.000 -39876.310 249 Guevara 42998.000 48023.690 -5025.690 266 Gowda 59840.000 57490.000 2350.000 278 Melkisetian 48500.000 74500.000 -26000.000 316 Pastor 74500.000 62000.000 12500.000 445 Lull 87900.000 67890.000 20010.000 453 Rabkin 64500.000 42998.000 21502.000 479 Siperstein 39875.500 42500.000 -2624.500 501 Scott 96300.000 54900.000 41400.000 529 Sullivan 67890.000 72995.000 -5105.000 582 Samuels 37400.000 39875.500 -2475.500 604 Wang 68400.000 45700.000 22700.000 839 Marshall 42500.000 48500.000 -6000.000

1157 Soo 39075.000 37400.000 1675.000 1250 Diaz 54900.000

ここではウィンドウ関数 SUM() を使用していますが、ウィンドウの指定方法により、この合計には現在のローまたは前のローの salary 値だけが含まれています。また、結果セットの最初のローには前のローが存在しないため、最初のローの prev 値は NULL になります。したがって、delta も NULL になります。

ここまでの例では、OVER() 句と一緒に SUM() 集合関数を使用しました。

明示的なウィンドウ句とインラインのウィンドウ句

SQL OLAP では、クエリ内でウィンドウを指定する方法が 2 とおり用意されています。

• 明示的なウィンドウ句。HAVING 句の後でウィンドウを定義します。OLAP 関数を呼び出すときには、このようなウィンドウ句で定義したウィンドウを、ウィンドウの名前を指定して参照します。たとえば次のようにします。

SUM ( ...) OVER w2

パフォーマンス＆チューニング・ガイド 73

統計関数

• インラインのウィンドウ指定。クエリ式の SELECT リスト内でウィンドウを定義します。これにより、HAVING 句の後のウィンドウ句でウィンドウを定義し、それをウィンドウ関数呼び出しから名前で参照するという方法に加えて、関数呼び出しと一緒にウィンドウを定義するという方法が可能になります。

注意インラインのウィンドウ指定を使用する場合は、ウィンドウの名前

を指定できません。1 つの SELECT リスト内で複数のウィンドウ関数呼び出しが同じウィンドウを使用する場合には、ウィンドウ句で定義した名前付きウィンドウを参照するか、インラインのウィンドウ定義を繰り返す必要があります。

ウィンドウ関数の例　ウィンドウ関数の例を次に示します。このクエリでは、デー

タを部署別のパーティションに分け、在社年数が最も長い従業員を基点とした従業員の累積給与を計算して、結果セットを返します。この結果セットには、マサチューセッツ在住の従業員だけが含まれます。Sum_Salary カラムには、従業員の給与の累積和が含まれます。

SELECT dept_id, emp_lname, start_date, salary,

SUM(salary) OVER (PARTITION BY dept_id ORDER BY start_date rows between unbounded preceding and

current row) AS sum_salary FROM employee WHERE state IN ('MA') AND dept_id IN (100, 200) ORDER BY dept_id;

次の結果セットは部署別に分割されています。

dept_id emp_lname start_date salary sum_salary

------- --------- ----------- ------ ----------100 Whitney 1984-08-28 45700.000 45700.000 100 Cobb 1985-01-01 62000.000 107700.000 100 Breault 1985-06-17 57490.000 165190.000 100 Shishov 1986-06-07 72995.000 238185.000 100 Driscoll 1986-07-01 48023.690 286208.690 100 Guevara 1986-10-14 42998.000 329206.690 100 Gowda 1986-11-30 59840.000 389046.690 100 Melkisetian 1986-12-06 48500.000 437546.690 100 Pastor 1987-04-26 74500.000 512046.690 100 Lull 1987-06-15 87900.000 599946.690 100 Rabkin 1987-06-15 64500.000 664446.690 100 Siperstein 1987-07-23 39875.500 704322.190 100 Scott 1987-08-04 96300.000 800622.190 100 Sullivan 1988-02-03 67890.000 868512.190 100 Samuels 1988-03-23 37400.000 905912.190 100 Wang 1988-09-29 68400.000 974312.190 100 Marshall 1989-04-20 42500.000 1016812.190 100 Soo 1990-07-31 39075.000 1055887.190 100 Diaz 1990-08-19 54900.000 1110787.190

74 Sybase IQ

ランク付け関数

第 4 章 OLAP の使用

200 Dill 1985-12-06 54800.000 54800.000 200 Powell 1988-10-14 54600.000 109400.000 200 Poitras 1988-11-28 46200.000 155600.000 200 Singer 1989-06-01 34892.000 190492.000 200 Kelly 1989-10-01 87500.000 277992.000 200 Martel 1989-10-16 55700.000 333692.000 200 Sterling 1990-04-29 64900.000 398592.000 200 Chao 1990-05-13 33890.000 432482.000 200 Preston 1990-07-11 37803.000 470285.000 200 Goggin 1990-08-05 37900.000 508185.000 200 Pickett 1993-08-12 47653.000 555838.000

ランク付け関数を使用すると、データ・セットの値をランク付けされた順序のリストにまとめ、“ 今年度出荷された製品の中で売上合計が上位 10 位の製品名” または “15 社以上から受注した営業部員の上位 5%” といった質問に答えるクエリを 1 つの SQL 文で作成することができます。ランク付け関数には

RANK()、DENSE_RANK()、PERCENT_RANK()、NTILE() などがあり、 PART I T I O N B Y 句と一緒に使用します。

SQL/OLAP では、次の 4 つの関数がランク付け関数として分類されています。

<RANK FUNCTION TYPE> ::=

RANK | DENSE RANK | PERCENT RANK | NTILE

ランク付け関数を使用すると、クエリで指定された順序に基づいて、結果セット内の各ローのランク値を計算することができます。たとえば販売マネージャが、営業成績が最高または最低の営業部員、販売成績が最高または最低の販売地域、あるいは売上が最高または最低の製品を調べたい場合があります。この情報はランク付け関数によって入手できます。

RANK() 関数

RANK 関数は、ORDER BY 句で指定されたカラムについて、ローのパーティション内での現在のローのランクを表す数値を返します。パーティション内の最初のローが 1 位となり、25 のローを含むパーティションでは、パーティション内の最後のローが 25 位となります。RANK は構文変換として指定されており、実際に RANK を同等の構文に変換することも、変換を行った場合に返すはずの値と同等の結果を返すこともできます。

次の例に出てくる ws1 は、w1 という名前のウィンドウを定義するウィンドウ指定を表しています。

RANK() OVER ws

パフォーマンス＆チューニング・ガイド 75

統計関数

DENSE_RANK() 関数

これは次の指定に相当します。

( 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 リファレンス・マニュアル』の「第 5 章 SQL 関数」の「RANK 関

数 [ 統計 ]」も参照してください。

RANK 関数は同位のローがあるときに重複したランク値を割り当て非連続的なランキングを返しますが、DENSE_RANK 関数は抜けのないランキングを返します。同位のローに対しては同じように等しいランク値が割り当てられますが、このローのランクは、個々のローの順位ではなく、順序付けカラムに等しい値を含んでいるローの集まりの順位を表しています。RANK の例と同様に、順序付けカラムに 10、20、20、20、30 という値を含むローがある場合、1 つ目のローのランクは同じく 1 となり、2 つ目のローおよび 3 つ目、4 つ目のローのランクも同じく 2 となります。しかし、最後のローのランクは 5 ではなく 3 になります。

DENSE_RANK も、構文変換を通じて計算されます。

DENSE_RANK() OVER ws

これは次の指定に相当します。

COUNT ( DISTINCT ROW ( expr_1, . . ., expr_n ) )

OVER ( ws RANGE UNBOUNDED PRECEDING )

この例では、expr_1 から expr_n の部分が、ウィンドウ w1 のソート指定リストに含まれている値の式のリストを表しています。

『Sybase IQ リファレンス・マニュアル』の「第 5 章 SQL 関数」の「DENSE_RANK

関数 [ 統計 ]」も参照してください。

76 Sybase IQ

PERCENT_RANK() 関数

第 4 章 OLAP の使用

PERCENT_RANK 関数は、個別の順位ではなく、パーセンテージでのランク

を計算して、0 ～ 1 の小数値を返します。つまり、PERCENT_RANK が返すのはローの相対的なランクであり、この数値は、該当するウィンドウ・パーティション内での現在のローの相対位置を表します。たとえば、順序付けカラムの値がそれぞれ異なる 10 個のローがパーティションに含まれている場合、このパーティションの 3 つ目のローに対する PERCENT_RANK の値は 0.222 ... となります。パーティションの 1 つ目のローに続く 2/9 (22.222...%) のローをカバーしているためです。次の例に示すとおり、ローの PERCENT_RANK は、

「ローの RANK - 1」を「パーティション内のローの数 - 1」で割ったものとし

て定義されています (“ANT” は、REAL や DOUBLE PRECISION などの概数値の型を表します )。

PERCENT_RANK() OVER ws

これは次の指定に相当します。

CASE

WHEN COUNT (*) OVER ( ws RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) = 1 THEN CAST (0 AS ANT) ELSE

( CAST ( RANK () OVER ( ws ) AS ANT ) -1 / ( COUNT (*) OVER ( ws RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) - 1 )

END

『Sybase IQ リファレンス・マニュアル』の「第 5 章 SQL 関数」の

「PERCENT_RANK 関数 [ 統計 ]」も参照してください。

ランク付けの例

ランク付けの例 1　

次の SQL クエリでは、マサチューセッツ州在住の男性従業

員と女性従業員を取得し、給与を基準として降順にランク付けしています。

SELECT emp_lname, salary, sex, RANK() OVER (ORDER BY

salary DESC) AS Rank FROM employee WHERE state IN ('MA') AND dept_id =100 ORDER BY salary DESC;

このクエリの結果セットを次に示します。

emp_lname salary sex rank

--------- -------- --- ----

Scott 96300.000 M 1 Lull 87900.000 M 2 Pastor 74500.000 F 3 Shishov 72995.000 F 4 Wang 68400.000 M 5 Sullivan 67890.000 F 6 Rabkin 64500.000 M 7 Cobb 62000.000 M 8

パフォーマンス＆チューニング・ガイド 77

統計関数

Gowda 59840.000 M 9 Breault 57490.000 M 10 Diaz 54900.000 M 11 Melkisetian 48500.000 F 12 Driscoll 48023.690 M 13 Whitney 45700.000 F 14 Guevara 42998.000 M 15 Marshall 42500.000 M 16 Siperstein 39875.500 F 17 Soo 39075.000 M 18 Samuels 37400.000 M 19

ランク付けの例 2　

ランク付けの例 1 のクエリを基にして、データを性別のパー

ティションに分けることができます。次の例では、性別のパーティションに分けて、従業員の給与を降順にランク付けしています。

SELECT emp_lname, salary, sex, RANK() OVER (PARTITION

BY sex ORDER BY salary DESC) AS RANK

FROM employee WHERE state IN ('MA', 'AZ') AND dept_id

IN (100, 200)

ORDER BY sex, salary DESC;

このクエリの結果セットを次に示します。

emp_lname salary sex rank

--------- --------- --- ----

Kelly 87500.000 F 1 Pastor 74500.000 F 2 Shishov 72995.000 F 3 Sullivan 67890.000 F 4 Melkisetian 48500.000 F 5 Pickett 47653.000 F 6 Poitras 46200.000 F 7 Whitney 45700.000 F 8 Siperstein 39875.500 F 9 Scott 96300.000 M 1 Lull 87900.000 M 2 Wang 68400.000 M 3 Sterling 64900.000 M 4 Rabkin 64500.000 M 5 Cobb 62000.000 M 6 Gowda 59840.000 M 7 Breault 57490.000 M 8 Martel 55700.000 M 9 Diaz 54900.000 M 10 Dill 54800.000 M 11 Powell 54600.000 M 12 Driscoll 48023.690 M 13 Guevara 42998.000 M 14 Marshall 42500.000 M 15 Soo 39075.000 M 16 Goggin 37900.000 M 17 Preston 37803.000 M 18

78 Sybase IQ

第 4 章 OLAP の使用

Samuels 37400.000 M 19 Singer 34892.000 M 20 Chao 33890.000 M 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 salary sex percent

--------- --------- --- ----------

Savarino 72300.000 F 0.00 Smith 51411.000 F 0.33 Clark 45000.000 F 0.66 Garcia 39800.000 F 1.00

ランク付けの例 4　

PERCENT_RANK 関数を使用して、データ・セットにおける上位または下位のパーセンタイルを調べることができます。この例のクエリは、給与の額がデータ・セットの上位 5％に入る男性従業員を返します。

SELECT * FROM (SELECT emp_lname, salary, sex,

CAST(PERCENT_RANK() OVER (ORDER BY salary DESC) as numeric (4, 2)) AS percent FROM employee WHERE state IN ('MA') AND sex ='F' ) AS

DT where percent > 0.5 ORDER BY salary DESC;

このクエリの結果セットを次に示します。

emp_lname salary sex percent

--------- ---------- --- ---------

Whitney 45700.000 F 0.51 Barletta 45450.000 F 0.55 Higgins 43700.000 F 0.59 Siperstein 39875.500 F 0.62 Coe 36500.000 F 0.66 Espinoza 36490.000 F 0.70 Wetherby 35745.000 F 0.74 Braun 34300.000 F 0.77 Butterfield 34011.000 F 0.81 Bigelow 31200.000 F 0.85 Bertrand 29800.000 F 0.88 Lambert 29384.000 F 0.92 Kuo 28200.000 F 0.96 Romero 27500.000 F 1.00

パフォーマンス＆チューニング・ガイド 79

統計関数

ウィンドウ集合関数

ウィンドウ集合関数を使用すると、複数のレベルの集合を 1 つのクエリで計算できます。たとえば、支出が平均より少ない四半期をすべて列挙することができます。集合関数 (単純な集合関数 AV G、COUNT、MAX、MIN、SUM を含む ) を使用すると、1 つの文の中でさまざまなレベルで計算した結果を 1 つのローに書き出すことができます。これにより、ジョインや相関サブクエリを使用しなくても、集合値をグループ内のディテール・ローと比較することができます。

これらの関数を使用して、非集合値と集合値を比較することも可能です。たとえば、営業部員が特定の年にある製品に対して平均以上の注文を出した顧客の一覧を作成したり、販売マネージャが従業員の給与をその部署の平均給与と比較したりすることが考えられます。

SELECT 文の中で DISTINCT が指定されている場合は、ウィンドウ演算子の後に DISTINCT 操作が適用されます ( ウィンドウ演算子は、GROUP BY 句が処理された後、SELECT リストの項目やクエリの ORDER BY 句が評価される前に計算されます )。

ウィンドウ集合関数の例 1　この例のクエリは、平均販売数よりも多く売れた製

品の一覧を年別に示す結果セットを返します。

SELECT * FROM (SELECT year(order_date) AS Y, prod_id,

SUM(quantity) AS Q, CAST(AVG(SUM(quantity)) OVER

(PARTITION BY Y) AS numeric (8, 2)) AS Average FROM alt_sales_order S, alt_sales_order_items O WHERE S.id = O.id GROUP BY Y, O.prod_id ) AS derived_table

WHERE Q > Average ORDER BY Y, prod_id;

このクエリの結果セットを次に示します。

Year prod_id Q Average

---- ------- ---- ------2000 400 2030 1787.00 2000 600 2124 1787.00 2000 601 1932 1787.00 2000 700 2700 1787.00 2001 400 1248 1048.90 2001 401 1057 1048.90 2001 700 1836 1048.90

2000 年の平均注文数は 1,787 であり、4 つの製品 (700、601、600、400) が平均を上回っています。2001 年の平均注文数は 1,048 であり、3 つの製品が平均を上回っています。

80 Sybase IQ

第 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;

このクエリの結果セットを次に示します。どの部署にも、給与の額が平均を大きく上回っている従業員が 1 人以上いることがわかります。

Employee Dept Salary Average Std_Dev

-------- ---- -------- -------- --------

Lull 100 87900.00 58736.28 16829.59 Sheffield 100 87900.00 58736.28 16829.59 Scott 100 96300.00 58736.28 16829.59 Sterling 200 64900.00 48390.94 13869.59 Savarino 200 72300.00 48390.94 13869.59 Kelly 200 87500.00 48390.94 13869.59 Shea 300 138948.00 59500.00 30752.39 Blaikie 400 54900.00 43640.67 11194.02 Morris 400 61300.00 43640.67 11194.02 Evans 400 68940.00 43640.67 11194.02 Martinez 500 55500.80 33752.20 9084.49

従業員 Scott の給与は 96,300.00 ドルで、所属部署の平均給与は 58,736.28 ドルです。この部署の標準偏差は 16,829.00 なので、給与の額が 75,565.88 ドル (58736.28 + 16829.60 = 75565.88) 未満ならば、平均の 1 標準偏差以内の範囲に収まります。Scott の給与 96,300.00 ドルは、この数字を大きく超えています。

統計集合関数

ANSI SQL/OLAP 拡張機能には、数値データの統計的分析を行うための集合関数がこの他にも数多く用意されています。これには、分散、標準偏差、相関、直線回帰を計算するための関数も含まれます。

標準偏差と分散 SQL/OLAP の一般的な関数の中には、STDDEV、STDDEV_POP、

STDDEV_SAMP、VARIANCE、VAR _POP、VAR_SAMP のように、

1 つの引数を取る関数があります。

<SIMPLE WINDOW AGGREGATE FUNCTION TYPE> ::=

| STDDEV | STDDEV_POP | STDDEV_SAMP

| VARIANCE | VARIANCE_POP | VARIANCE_SAMP

パフォーマンス＆チューニング・ガイド 81

統計関数

• STDDEV_POP －グループまたはパーティションの各ロー (DISTINCT が指定されている場合は、重複が削除された後に残る各ロー ) に対して評価される「値の式」についての母標準偏差を計算します。これは、母分散の平方根として定義されます。

• STDDEV_SAMP －グループまたはパーティションの各ロー (DISTINCT が指定されている場合は、重複が削除された後に残る各ロー ) に対して評価される「値の式」についての母標準偏差を計算します。これは、標本分散の平方根として定義されます。

• VAR_ POP －グループまたはパーティションの各ロー (DISTINCT が指定されている場合は、重複が削除された後に残る各ロー ) に対して評価される「値の式」についての母分散を計算します。これは、「値の式」と「値の式の平均」との差の 2 乗和をグループまたはパーティション内の残りのローの数で割った値として定義されます。

• VAR_ SAMP －グループまたはパーティションの各ロー (DISTINCT が指定されている場合は、重複が削除された後に残る各ロー ) に対して評価される「値の式」の標本分散を計算します。これは、「値の式」の差の 2 乗和を、グループまたはパーティション内の残りのローの数より 1 少ない数で割った値として定義されます。

これらの関数と STDDEV および VARIANCE 関数は、クエリの ORDER BY 句の指定に従ってローのパーティションについての値を計算できる集合関数です。 MAX や MIN などのその他の基本的な集合関数と同様に、これらの関数は入力データ内の NULL 値を無視します。また、分析される式のドメインに関係なく、分散と標準偏差の計算では必ず IEEE の倍精度浮動小数点数が使用されます。分散関数または標準偏差関数への入力が空のデータ・セットである場合、これらの関数は結果として NULL を返します。VAR_SAMP 関数は 1 つのローに対して計算を行うと NULL を返しますが、VAR_P OP は値 0 を返します。

分散統計関数

SQL/OLAP には、順序付きセットを取り扱う関数がいくつか定義されています。

PERCENTILE_CONT と PERCENTILE_DISC という 2 つの逆分散統計関数があります。これらの統計関数は、パーセンタイル値を引数として受け取り、WITHIN GROUP 句で指定されたデータのグループまたはデータ・セット全体に対して処理を行います。

これらの関数は、グループごとに 1 つの値を返します PERCENTILE_DISC

( 不連続 ) の場合、結果のデータ型は、WITHIN GROUP 句に指定した ORDER BY の項目のデータ型と同じになります。PERCENTILE_CONT ( 連続 ) では、

結果のデータ型は、numeric (WITHIN GROUP 句の ORDER BY 項目が numeric の場合 ) または double (ORDER BY 項目が整数または浮動小数点の場合 ) となります。

82 Sybase IQ

第 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 句は、クエリの結果を並べ替えて、関数が結果を計算するためのデータ・セットを形成します。

expression2 には、カラム参照を含む 1 つの式でソートを指定します。このソート式に、複数の式やランク付け統計関数、set 関数、またはサブクエリを指定することはできません。

ASC と DESC のパラメータでは、昇順または降順の順序付けシーケンスを指定します。昇順がデフォルトです。

逆分散統計関数は、サブクエリ、HAVING 句、ビュー、union で使用することが可能です。逆分散統計関数は、分析を行わない単純な集合関数が使用されるところであれば、どこでも使用できます。逆分散統計関数は、データ・セット内の NULL 値を無視します。

PERCENTILE_CONT 例　この例では、PERCENTILE_CONT 関数を使用して、

各地域の自動車販売の 10 番目のパーセンタイル値を求めます。次のようなデータ・セットを使用します。

sales region dealer_name

----- --------- -----------

900 Northeast Boston 800 Northeast Worcester 800 Northeast Providence 700 Northeast Lowell 540 Northeast Natick 500 Northeast New Haven 450 Northeast Hartford 800 Northwest SF 600 Northwest Seattle 500 Northwest Portland 400 Northwest Dublin 500 South Houston 400 South Austin 300 South Dallas 200 South Dover

パフォーマンス＆チューニング・ガイド 83

統計関数

次のクエリ例では、SELECT 文に PERCENTILE_CONT 関数を含めています。

SELECT region, PERCENTILE_CONT(0.1) WITHIN GROUP ( ORDER BY sales DESC ) FROM carSales GROUP BY region;

この SELECT 文の結果には、各地域の自動車販売の 10 番目のパーセンタイル値が一覧表示されます。

region percentile_cont

--------- --------------Northeast 840 Northwest 740 South 470

PERCENTILE_DISC 例　

この例では、PERCENTILE_DISC 関数を使用して、各地域の自動車販売の 10 番目のパーセンタイル値を求めます。次のようなデータ・セットを使用します。

sales region dealer_name

----- --------- -----------

次のクエリ例では、SELECT 文に PERCENTILE_DISC 関数を含めています。

SELECT region, PERCENTILE_DISC(0.1) WITHIN GROUP

(ORDER BY sales DESC )

FROM carSales GROUP BY region;

この SELECT 文の結果には、各地域の自動車販売の 10 番目のパーセンタイル値が一覧表示されます。

region percentile_cont

--------- ---------------

Northeast 900 Northwest 800 South 500

84 Sybase IQ

Sybase IQ 12.7 User Manual

Specifications and Main Features

Frequently Asked Questions

User Manual

はじめに

前提条件

テーブル情報の表示

クエリ結果の順序付け

カラムとローの選択

探索条件の使用

集約データの取得

分析データの取得

重複したローの削除

第 2 章テーブルのジョイン

外積を使用したテーブルのジョイン

ジョインの制限

テーブル間の関係

ジョイン演算子

アドホック・ジョインとジョイン・インデックスの使用

ジョインとデータ型

ストアまたはデータベース間ジョインのサポート

リモート・データベースと異種データベースのクエリ

サブクエリによるジョインの置き換え

第 3 章クエリと削除の最適化

クエリ構築のヒント

クエリ・プラン

クエリ処理の制御

削除オペレーションの最適化

第 4 章 OLAP の使用

OLAP について

GROUP BY 句の拡張機能

統計関数

Sybase IQ 12.7 User Manual

Specifications and Main Features

Frequently Asked Questions

User Manual

はじめに

前提条件

テーブル情報の表示

クエリ結果の順序付け

カラムとローの選択

探索条件の使用

集約データの取得

分析データの取得

重複したローの削除

第 2 章 テーブルのジョイン

外積を使用したテーブルのジョイン

ジョインの制限

テーブル間の関係

ジョイン演算子

アドホック・ジョインとジョイン・インデックスの使用

ジョインとデータ型

ストアまたはデータベース間ジョインのサポート

リモート・データベースと異種データベースのクエリ

サブクエリによるジョインの置き換え

第 3 章 クエリと削除の最適化

クエリ構築のヒント

クエリ・プラン

クエリ処理の制御

削除オペレーションの最適化

第 4 章 OLAP の使用

OLAP について

GROUP BY 句の拡張機能

統計関数

第 2 章テーブルのジョイン

第 3 章クエリと削除の最適化