セットを使用した作業
ここでは、Business Intelligence でセットを作成して使用する方法について説明します。
"BI サンプルのアクセス方法" も参照してください。
セットの概要
セットにはゼロ個以上の要素が含まれ、一般的な種類の要素として、メンバ、スカラ値、タプルの 3 つがあります (タプルの詳細は、"タプルとキューブ" を参照してください)。
セットは、MDX クエリの軸に対して使用するだけでなく、他のセットの構築にも使用します。MDX セットは空でもかまいませんが、そのような空のセットを軸として使用することはできません。
セット式の作成
set expression の一般的な構文は以下のとおりです。
{expression1, expression2, ...}
このリストには、要素を任意の数だけ含めることができます。InterSystems MDX では、リスト内の要素が 1 つだけの場合、中括弧を省略できます。
各セット要素は以下のいずれかになります。
-
member expression。以下のいずれかです。
-
単一メンバへの名前による明示的な参照。
-
単一メンバを返す MDX 関数を使用する式。
-
-
セットを返す MDX 関数 (MEMBERS など) を使用する式。
-
同じレベル内のメンバの範囲 ("レベルを使用した作業" で説明)。
member1:membern
-
スカラ値。以下のいずれかです。
-
メジャーへの参照。式 MEASURES.[Avg Test Score] はスカラ値です。この式では、すべてのコンテキストにおいて、数値または NULL のいずれかが返されます。
-
37 や "label" などの数値定数または文字列定数。
-
(37+3)/2 などの数値式。
-
パーセンテージ・リテラル。例えば、10% です。
数値とパーセント記号の間にスペースを入れてはいけません。
-
スカラ値を返す MDX 関数を使用する式。
例えば、PROPERTIES 関数はスカラ値を返します。この関数の概要は、"レベルを使用した作業" を参照してください。
-
完全な詳細は、"InterSystems MDX リファレンス" を参照してください。
名前付きセットの作成
セットを作成して名前を割り当て、そのセットをさまざまな方法で再利用できるようにしておくと便利です。また多くの場合、クエリの構文は、名前付きセットを使用すると読みやすくなります。
クエリ内で 1 つ以上の名前付きセットを作成するには、以下のコマンドを使用します。
WITH with_clause1 with_clause2 ... SELECT query_details
以下は、この指定の説明です。
-
各式 with_clause1、with_clause2 (以降同様) の構文は以下のとおりです。
SET set_name AS 'set_expression'
-
query_details は MDX クエリです。
このようにすると、他のセット式を使用できるあらゆる場所で、名前を指定してその名前付きセットを参照することができます。
以下はその例です。
WITH SET testset AS '{homed.city.members}'
SELECT MEASURES.[%COUNT] ON 0, testset ON 1 FROM demomdx
%COUNT
1 Cedar Falls 184
2 Centerville 194
3 Cypress 134
4 Elm Heights 146
5 Juniper 176
6 Magnolia 169
7 Pine 118
8 Redwood 182
9 Spruce 197
この名前付きセットは、クエリを範囲とする名前付きセットであり、その範囲はクエリです。セッションを範囲とする名前付きセットの詳細は、"InterSystems MDX リファレンス" の "CREATE SET 文" を参照してください。
キューブには、あらゆるクエリで使用できる追加の名前付きセットが含まれることがあります。"InterSystems Business Intelligence のモデルの定義" を参照してください。
セット内のメンバの順序
すべてのセットにはそれぞれ固有の順序があります (最初のメンバ、2 番目のメンバなど)。
セットを返す MDX 関数を使用すると、その関数によってそのセット内のメンバの順序が決定されます。MDX 関数では、可能な限り、キューブ定義の指定に従って、メンバの本来の順序が使用されます。
例えば、MEMBERS 関数は、レベルのメンバを、キューブ定義に指定されている順序で返します (通常は、レベルに応じてアルファベット順または日付順です)。
前のセクションの説明どおりにセットを作成する場合、要素をリストする順序によって、セット内のメンバの順序が制御されます。例えば、以下のようにセットを指定するとします。
{gend.gender.MEMBERS,allerd.allergies.MEMBERS}
このセットは、Gender ディメンジョンのメンバと、それに続く Allergies ディメンジョンのメンバで構成されます。
サブセットの選択
SUBSET は、位置に基づいて、指定されたセットからメンバのセットを返します。セット、開始位置、および返すメンバの数を指定します。開始位置は 0 です。以下はその例です。
SELECT MEASURES.[%COUNT] ON 0,SUBSET(homed.city.MEMBERS,0,3) ON 1 FROM demomdx
%COUNT
1 Cedar Falls 110
2 Centerville 99
3 Cypress 112
EXCEPT 関数には、サブセットを取得する別の方法が用意されています。次のセクションを参照してください。
"クエリのフィルタ処理" も参照してください。
セットの並べ替え
ここでは、セットの並べ替え方法を説明します。以下のトピックについて説明します。
メジャー値によるセットの並べ替え
メンバを特定のメジャー値によって並べ替えると役立つことがよくあります。例えば、応答の順に部門を並べ替えることによって、最も応答の遅い部門を確認できるようにする必要がある場合が考えられます。または、総売上高の順に製品を並べ替えることによって、上位ランクの製品を確認できるようにする場合もあります。
指定した順序でセットを返すには、ORDER 関数を使用します。この関数には、セット・メンバの順序を決定する際に使用される値を指定する引数 (通常はメジャーへの参照) を使用します。以下はその例です。
SELECT MEASURES.[avg test score] ON 0,
ORDER(homed.city.MEMBERS,MEASURES.[avg test score],BDESC) ON 1 FROM demomdx
Avg Test Score
1 Juniper 75.08
2 Redwood 75.07
3 Cedar Falls 75.03
4 Elm Heights 74.96
5 Pine 74.76
6 Spruce 74.47
7 Magnolia 74.13
8 Cypress 73.96
9 Centerville 73.79
3 番目のオプションの引数は、以下のいずれかになります。
-
ASC (既定) — この引数を使用すると、該当する場合は、階層を保持したまま昇順で並べ替えが行われます。
-
DESC — この引数を使用すると、該当する場合は、階層を保持したまま降順で並べ替えが行われます。
-
BASC — この引数を使用すると、階層は保持されず、すべてのメンバが昇順で並べ替えられます。
-
BDESC — この引数を使用すると、階層は保持されず、すべてのメンバが降順で並べ替えられます。
例えば、以下のクエリでは階層は保持されません。
SELECT MEASURES.[avg test score] ON 0,
ORDER(homed.MEMBERS,MEASURES.[avg testscore],BDESC) ON 1 FROM demomdx
Avg Test Score
1 Juniper 75.08
2 Redwood 75.07
3 32007 75.07
4 Cedar Falls 75.03
5 38928 75.00
6 Elm Heights 74.96
7 32006 74.78
8 Pine 74.76
9 Spruce 74.47
10 34577 74.28
11 Magnolia 74.13
12 Cypress 73.96
13 Centerville 73.79
14 36711 73.79
これに対して、以下のクエリでは階層が保持されます。
SELECT MEASURES.[avg test score] ON 0,
ORDER(homed.MEMBERS,MEASURES.[avg testscore],DESC) ON 1 FROM demomdx
Avg Test Score
1 32007 75.07
2 Redwood 75.07
3 38928 75.00
4 Cedar Falls 75.03
5 Elm Heights 74.96
6 32006 74.78
7 Juniper 75.08
8 Spruce 74.47
9 34577 74.28
10 Pine 74.76
11 Magnolia 74.13
12 Cypress 73.96
13 36711 73.79
14 Centerville 73.79
上位または下位のサブセットの選択
何らかの方法で項目を並べ替えた後、上位または下位のサブセットを選択できると便利です (上位 5 つなど)。そのためには、以下の MDX 関数を使用します。
-
メンバ数を指定することで、HEAD はセットの最初の部分、TAIL はセットの最後の部分を返します。以下はその例です。
SELECT MEASURES.[%COUNT] ON 0,HEAD(homed.city.MEMBERS,3) ON 1 FROM demomdx %COUNT 1 Cedar Falls 110 2 Centerville 99 3 Cypress 112
返されたセットのメンバの順序は、元のセットの順序と同じです。
-
TOPCOUNT は HEAD、BOTTOMCOUNT は TAIL に似ていますが、サブセットを抽出する前のセットの並べ替え方法を指定するオプションの引数も含みます。
例えば、以下のクエリでは、患者数に基づいて上位の市町村が返されます。
SELECT MEASURES.[%COUNT] ON 0,TOPCOUNT(homed.city.MEMBERS,4,MEASURES.[%COUNT]) ON 1 FROM demomdx %COUNT 1 Juniper 122 2 Pine 121 3 Elm Heights 118 4 Magnolia 114
別の例を示します。
SELECT MEASURES.[avg test score] ON 0,TOPCOUNT(homed.city.MEMBERS,5,MEASURES.[avg test score]) ON 1 FROM demomdx Avg Test Score 1 Juniper 75.08 2 Redwood 75.07 3 Cedar Falls 75.03 4 Elm Heights 74.96 5 Pine 74.76
-
TOPSUM は TOPCOUNT、BOTTOMSUM は BOTTOMCOUNT に似ています。ただし、返すメンバの数を指定する代わりに、メンバ全体の合計に適用される切り捨て値を指定します。例えば、売上で上位 500 万ドルを占める製品を取得することができます。
-
TOPPERCENT は TOPCOUNT、BOTTOMPERCENT は BOTTOMCOUNT に似ています。ただし、返すメンバの数を指定する代わりに、メンバ全体の合計に適用される切り捨てパーセンテージを指定します。例えば、売上の上位 10 パーセントを占める製品を取得することができます。
階層順の適用
HIERARCHIZE 関数は、同じディメンジョンからメンバのセットを受け入れ、それらのメンバを含むセットを階層順に (階層で指定された順序で) 返します。以下はその例です。
SELECT MEASURES.[%COUNT] ON 0,
HIERARCHIZE({homed.36711,homed.38928,homed.[elm heights],homed.Spruce}) ON 1 FROM demomdx
%COUNT
1 36711 99
2 Spruce 93
3 38928 228
4 Elm Heights 118
これらのメンバがディメンジョン内の異なる階層に属する場合、それぞれの階層は任意の順序で返されます。
セットの組み合わせ
セットは、MDX クエリのビルディング・ブロックです。MDX クエリを記述する際には、各軸に対して使用するセットを指定する必要があります。セットを組み合わせるために使用できる以下の MDX 関数がサポートされています。
-
UNION は、2 つのセットを組み合わせ (重複するメンバがあれば、必要に応じて破棄)、それらのセットのすべてのメンバを含むセットを返します。以下はその例です。
WITH SET set1 AS '{allerd.eggs,allerd.soy,allerd.wheat}' SET set2 AS '{allerd.[dairy products],allerd.pollen,allerd.soy,allerd.wheat}' SELECT MEASURES.[%COUNT] ON 0, UNION(set1,set2) ON 1 FROM demomdx %COUNT 1 eggs 32 2 soy 36 3 wheat 52
このクエリでは、UNION を ALL キーワードと組み合わせて使用していないため、重複は削除されます。
-
INTERSECT は、2つのセットを検証し、両方のセット内にあるすべてのメンバを含むセットを返します。必要に応じて、重複を保持することも可能です。以下はその例です。
WITH SET set1 AS 'TOPCOUNT(homed.city.members,5,MEASURES.[avg allergy count])' SET set2 AS 'TOPCOUNT(homed.city.members,5,MEASURES.[avg age])' SELECT MEASURES.[%COUNT] ON 0, INTERSECT(set1,set2) ON 1 FROM demomdx %COUNT 1 Magnolia 114 2 Redwood 111 3 Cypress 112 4 Cedar Falls 110
-
EXCEPT は、2 つのセットを検証し、1 番目のセット内にあるメンバのうち、2 番目のセット内にも存在するものを削除します。必要に応じて、重複を保持することも可能です。以下はその例です。
WITH SET set1 AS '{allerd.eggs,allerd.eggs,allerd.soy,allerd.wheat}' SET set2 AS '{allerd.[diary products],allerd.pollen,allerd.wheat}' SELECT MEASURES.[%COUNT] ON 0, EXCEPT(set1,set2) ON 1 FROM demomdx %COUNT 1 eggs 32 2 soy 36
"%NOT 最適化" も参照してください。
-
CROSSJOIN は、2 つのセットのクロス積で構成されるセットを返します。両方のセットがメンバで構成されることが可能です。または、一方のセットがメンバで構成され、他方のセットがメジャーで構成されることも可能です。メジャーが両方のセットに含まれる場合は、分析エンジンによって [Two measures cannot be crossjoined] というエラーが出力されます。以下はその例です。
SELECT MEASURES.[%COUNT] ON 0, CROSSJOIN(diagd.diagnoses.MEMBERS, aged.[age group].MEMBERS) ON 1 FROM demomdx %COUNT 1 None->0 to 29 389 2 None->30 to 59 333 3 None->60+ 106 4 asthma->0 to 29 40 5 asthma->30 to 59 39 6 asthma->60+ 11 7 CHD->0 to 29 * 8 CHD->30 to 59 12 9 CHD->60+ 25 10 diabetes->0 to 29 1 11 diabetes->30 to 59 20 12 diabetes->60+ 24 13 osteoporosis->0 to 29 * 14 osteoporosis->30 to 59 * 15 osteoporosis->60+ 22
MDX シェルでは、NULL 値はアスタリスク (*) として表示されます。NULL 値の抑制の詳細は、"セットからの NULL 要素の削除" を参照してください。
また、 NONEMPTYCROSSJOIN 関数も参照してください。
メンバのセットを返す前述の関数とは異なり、CROSSJOIN は一連のタプルを返す (NONEMPTYCROSSJOIN と同様) ことにも注意してください。タプルの詳細は、"タプルとキューブ" を参照してください。
メジャー値またはプロパティ値によるセットのフィルタ処理
セット内のメンバのメジャー値を検証し、それらの値を使用してセットをフィルタ処理することもできます。このためには、FILTER 関数を使用します。
FILTER 関数は、セットと論理式を使用します。セットが検証され、指定された論理式がメンバごとに True となるサブセットが返されます。この論理式は通常、メジャー値を定数と比較したり、別のメジャー値と比較したりします。以下はその例です。
SELECT MEASURES.[%COUNT] ON 0, FILTER(homed.city.MEMBERS,MEASURES.[%COUNT]>115) ON 1 FROM demomdx
%COUNT
1 Elm Heights 118
2 Juniper 122
3 Pine 121
このフィルタ処理は集約レベルで生じると理解しておいてください。つまり、メジャー値はクエリ内の可能なメンバごとに計算されます。FILTER 関数では、それらの集約値が検討され、必要に応じてメンバが削除されます。
以下のように、同じ関数をメンバのプロパティと組み合わせて使用することができます。
SELECT homed.h1.CURRENTMEMBER.PROPERTIES("Population") ON 0,
FILTER(homed.city.MEMBERS,homed.h1.CURRENTMEMBER.PROPERTIES("Population")>20000) ON 1 FROM demomdx
ZIP
1 Cedar Falls 90,000
2 Centerville 49,000
3 Elm Heights 33,194
4 Redwood 29,192
セットからの NULL 要素の削除
場合によっては、セットに NULL 要素が含まれることがあります。例えば、CROSSJOIN 関数は、(前のセクションで示したように) NULL 要素を返す可能性があります。
NON EMPTY キーワードをセット式の前に置くと、NULL 要素が抑制されます。以下はその例です。
SELECT MEASURES.[%COUNT] ON 0,NON EMPTY CROSSJOIN(diagd.diagnoses.MEMBERS,
aged.[age group].MEMBERS) ON 1 FROM demomdx
%COUNT
1 None->0 to 29 389
2 None->30 to 59 333
3 None->60+ 106
4 asthma->0 to 29 40
5 asthma->30 to 59 39
6 asthma->60+ 11
7 CHD->30 to 59 12
8 CHD->60+ 25
9 diabetes->0 to 29 1
10 diabetes->30 to 59 20
11 diabetes->60+ 24
12 osteoporosis->60+ 22
重複の削除
セットを組み合わせるときには、重複を削除することができます。これは特に、複数の手順でセットの作成および組み合わせを行った場合に有効です。結果のセットに重複が含まれないようにするには、DISTINCT 関数を使用します。
例えば、他の市町村との比較に必要な基準として、特定の市町村をクエリで返す必要があるとします。基準の市町村を表示し、さらに指定した患者数を持つ市町村のセットを表示するという、以下のクエリを考えてみます。
WITH SET refcity AS '{homed.juniper}' SELECT MEASURES.[%COUNT] ON 0,
{refcity,FILTER(homed.city.MEMBERS,MEASURES.[%COUNT]>115)} ON 1 FROM demomdx
%COUNT
1 Juniper 122
2 Elm Heights 118
3 Juniper 122
4 Pine 121
以下のクエリと比較してみてください。そちらでは、重複する基準の市町村が削除されます。
WITH SET refcity AS '{homed.juniper}' SELECT MEASURES.[%COUNT] ON 0,
DISTINCT({refcity,FILTER(homed.city.MEMBERS,MEASURES.[%COUNT]>115)}) ON 1 FROM demomdx
%COUNT
1 Juniper 122
2 Elm Heights 118
3 Pine 121
セットの要素のカウント
セットの要素をカウントするには、COUNT 関数を使用します。以下はその例です。
SELECT COUNT(docd.doctor.MEMBERS) ON 0 FROM demomdx
COUNT
41
既定では、COUNT はあらゆる空の要素を考慮して、空以外の要素と合せてカウントします。2 番目の引数として EXCLUDEEMPTY キーワードを使用すると、この関数は空以外の要素の数を返します。
このことを確認するために、まず以下のクエリを考えてみます。
SELECT aged.[age group].MEMBERS ON 0, diagd.diagnoses.MEMBERS ON 1 FROM demomdx WHERE MEASURES.[%COUNT]
0 to 29 30 to 59 60+
1 None 389 333 106
2 asthma 40 39 11
3 CHD * 12 25
4 diabetes 1 20 24
5 osteoporosis * * 22
以下のクエリでは、Diagnoses レベルのメンバの数がカウントされ、WHERE 節を使用して 0 ~ 29 歳の年齢グループの患者のみが取得されます。
WITH SET myset AS 'diagd.diagnoses.MEMBERS' SELECT COUNT(myset) ON 0 FROM demomdx WHERE aged.[0 to 29]
COUNT
5
このクエリでは、COUNT が 5 を返します。これは、空のメンバも考慮しているためです。一方、以下では空のメンバは考慮されていません。
WITH SET myset AS 'diagd.diagnoses.MEMBERS' SELECT COUNT(myset,EXCLUDEEMPTY) ON 0 FROM demomdx WHERE aged.[0 to 29]
COUNT
3