この章では、Oracle Continuous Query Language (Oracle CQL)に含まれる組込みの集計関数のリファレンスを提供します。組込みの集計関数は、問合せが返すすべての値の集計操作を行います。
詳細は、1.1.11項「関数」を参照してください。
この章の内容は次のとおりです。
表9-1に、Oracle CQLに用意されている組込みの集計関数を示します。
選択した重複タプルの各セットで1つのコピーのみが返されるようにするには、distinct
を指定します。重複タプルとはselectリスト内の各式と一致する値を持つタプルです。詳細は、aggr_distinct_exprを参照してください。
Oracle Event Processing は、ネストされた集計はサポートしていません。
注意: 組込み関数名では大文字と小文字が区別されるため、表示されている方(小文字)を使用してください。 |
注意: ストリーム入力の例で、h で始まる行(h 3800 など)はハートビート入力タプルです。これは、ハートビート値よりも小さいタイムスタンプを持つこれ以上の入力がないことをOracle Event Processingに通知します。 |
詳細は、次を参照してください。
Oracle CQLでは、where
句はgroup by
句およびhaving
句の前に適用されます。したがって、例9-1のOracle CQL文は無効です。
例9-1 countの無効な使用
<query id="q1"><![CDATA[ select * from InputChanel[rows 4 slide 4] as ic where count(*) = 4 ]]></query>
例9-2に示すようなOracle CQL文を使用する必要があります。
例9-2 countの有効な使用
<query id="q1"><![CDATA[ select * from InputChanel[rows 4 slide 4] as ic, count(*) as myCount having myCount = 4 ]]></query>
詳細は、次を参照してください。
例
例9-3
の問合せfloat_avgと例9-4
のデータ・ストリームS3があるとします。ストリームS3
のスキーマは(c1 float)
です。この問合せは、例9-5に示すリレーションを返します。平均値が数字でない場合、avg
関数はNaN
の結果を返します。詳細は、2.3.2項「数値リテラル」を参照してください。
目的
count
は、問合せが返すタプル数をint
の値として返します。
表9-2に示すように、戻り値は引数によって異なります。
表9-2 COUNT集計関数の戻り値
入力引数 | 戻り値 |
---|---|
|
|
|
重複とnullを含む、すべてのタプルの数。 |
|
相関変数 |
|
|
count
は、nullを返しません。
目的
first
は、指定されたパターンに最初に一致する、指定されたストリーム要素の値を返します。
指定されるストリーム要素の型は、次のいずれかになります。
bigint
integer
byte
char
float
interval
timestamp
この関数の戻り値の型は、指定されたストリーム要素の型によって異なります。
この関数は、ピリオドで区切られた次の値で構成される1つの引数を使用します。
identifier1
: DEFINE
句で指定されたパターンの名前。
identifier2
: CREATE STREAM
文で指定されたストリーム要素の名前。
例
例9-9
の問合せq9と例9-10
のデータ・ストリームS0があるとします。ストリームS0
のスキーマは(c1 integer, c2 float)
です。この例では、パターンC
をC.c1 = 7
と定義します。また、firstc
をfirst(C.c2)
と定義します。つまり、最初にc1 = 7
になった場合に、firstc
は値c2
と等しくなります。この問合せは、例9-11に示すリレーションを返します。
例9-9 first関数の問合せ
<query id="q9"><![CDATA[ select T.firstc, T.lastc, T.Ac1, T.Bc1, T.avgCc1, T.Dc1 from S0 MATCH_RECOGNIZE ( MEASURES first(C.c2) as firstc, last(C.c2) as lastc, avg(C.c1) as avgCc1, A.c1 as Ac1, B.c1 as Bc1, D.c1 as Dc1 PATTERN(A B C* D) DEFINE A as A.c1 = 30, B as B.c2 = 10.0, C as C.c1 = 7, D as D.c1 = 40 ) as T ]]></query>
目的
last
は、指定されたパターンに最後に一致する、指定されたストリーム要素の値を返します。
指定されるストリーム要素の型は、次のいずれかになります。
bigint
integer
byte
char
float
interval
timestamp
この関数の戻り値の型は、指定されたストリーム要素の型によって異なります。
この関数は、ピリオドで区切られた次の値で構成される1つの引数を使用します。
identifier1
: DEFINE
句で指定されたパターンの名前。
identifier2
: CREATE STREAM
文で指定されたストリーム要素の名前。
例
例9-12
の問合せq9と例9-13
のデータ・ストリームS0があるとします。ストリームS1
のスキーマは(c1 integer, c2 float)
です。この例では、パターンC
をC.c1 = 7
と定義します。また、lastc
をlast(C.c2)
と定義します。つまり、最後にc1 = 7
になった場合に、lastc
は値c2
と等しくなります。この問合せは、例9-14に示すリレーションを返します。
例9-12 last関数の問合せ
<query id="q9"><![CDATA[ select T.firstc, T.lastc, T.Ac1, T.Bc1, T.avgCc1, T.Dc1 from S0 MATCH_RECOGNIZE ( MEASURES first(C.c2) as firstc, last(C.c2) as lastc, avg(C.c1) as avgCc1, A.c1 as Ac1, B.c1 as Bc1, D.c1 as Dc1 PATTERN(A B C* D) DEFINE A as A.c1 = 30, B as B.c2 = 10.0, C as C.c1 = 7, D as D.c1 = 40 ) as T ]]></query>
目的
listagg
は、その関数の引数に相当するJavaの値を含むjava.util.List
を返します。
関数の引数としてユーザー定義のクラスが使用される場合、そのクラスはequals
メソッドを実装する必要があります。
例
例9-15「listaggの問合せ」では、ビューv1で例9-16「listaggの例の入力」の第3列の値をjava.util.List
(デフォルトの戻り型)およびjava.util.LinkedHashSet
に集計します。問合せq1では、例9-17「listaggリレーションの出力」での出力を生成するために、それぞれのサイズを選択します。
例9-15 listaggの問合せ
<view id="v1"><![CDATA[ ISTREAM(select c1, listAgg(c3) as l1, java.util.LinkedHashSet(listAgg(c3)) as set1 from S1 group by c1) ]]></view> <query id="q1"><![CDATA[ select v1.l1.size(), v1.set1.size() from v1 ]]></query>
例
例9-18
の問合せtest_max_timestampと例9-19
のデータ・ストリームS15があるとします。ストリームS15
のスキーマは(c1 int, c2 timestamp)
です。この問合せは、例9-20に示すリレーションを返します。
例9-18 max関数の問合せ
<query id="test_max_timestamp"><![CDATA[ select max(c2) from S15[range 2] ]]></query>
例9-19 max関数のストリーム入力
Timestamp Tuple 10 1,"08/07/2004 11:13:48" 2000 ,"08/07/2005 11:13:48" 3400 3,"08/07/2006 11:13:48" 4700 ,"08/07/2007 11:13:48" h 8000 h 200000000
例9-20 max関数のリレーション出力
Timestamp Tuple Kind Tuple 0: + 10: - 10: + 08/07/2004 11:13:48 2000: - 08/07/2004 11:13:48 2000: + 08/07/2005 11:13:48 2010: - 08/07/2005 11:13:48 2010: + 08/07/2005 11:13:48 3400: - 08/07/2005 11:13:48 3400: + 08/07/2006 11:13:48 4000: - 08/07/2006 11:13:48 4000: + 08/07/2006 11:13:48 4700: - 08/07/2006 11:13:48 4700: + 08/07/2007 11:13:48 5400: - 08/07/2007 11:13:48 5400: + 08/07/2007 11:13:48 6700: - 08/07/2007 11:13:48 6700: +
例
例9-21
の問合せtest_min_timestampと例9-22
のデータ・ストリームS15があるとします。ストリームS15
のスキーマは(c1 int, c2 timestamp)
です。この問合せは、例9-23に示すリレーションを返します。
例9-21 min関数の問合せ
<query id="test_min_timestamp"><![CDATA[ select min(c2) from S15[range 2] ]]></query>
例9-22 min関数のストリーム入力
Timestamp Tuple 10 1,"08/07/2004 11:13:48" 2000 ,"08/07/2005 11:13:48" 3400 3,"08/07/2006 11:13:48" 4700 ,"08/07/2007 11:13:48" h 8000 h 200000000
例9-23 min関数のリレーション出力
Timestamp Tuple Kind Tuple 0: + 10: - 10: + 08/07/2004 11:13:48 2000: - 08/07/2004 11:13:48 2000: + 08/07/2004 11:13:48 2010: - 08/07/2004 11:13:48 2010: + 08/07/2005 11:13:48 3400: - 08/07/2005 11:13:48 3400: + 08/07/2005 11:13:48 4000: - 08/07/2005 11:13:48 4000: + 08/07/2006 11:13:48 4700: - 08/07/2006 11:13:48 4700: + 08/07/2006 11:13:48 5400: - 08/07/2006 11:13:48 5400: + 08/07/2007 11:13:48 6700: - 08/07/2007 11:13:48 6700: +
例
例9-24
の問合せq3と例9-25
のデータ・ストリームS1があるとします。ストリームS1
のスキーマは(c1 integer, c2 bigint)
です。この問合せは、例9-26に示すリレーションを返します。range
の詳細は、「範囲ベースのストリームからリレーションへのウィンドウ演算子」を参照してください。
目的
xmlagg
は、集計されたXMLドキュメントとしてXMLフラグメントの集合を返します。nullを返す引数は結果から削除されます。
ORDER BY
句を使用すると、フラグメントの順序を制御できます。詳細は、20.2.9項「問合せ結果のソート」を参照してください。
例
この項では、次のxmlagg
の例を示します。
xmlagg関数とxmlelement関数
例9-27
の問合せtkdata67_q1と例9-28の入力リレーションがあるとします。ストリームtkdata67_S0
のスキーマは(c1 integer, c2 float)
です。この問合せでは、xmlelement
を使用してストリーム要素からXMLフラグメントを作成し、xmlagg
を使用してそれらのXMLフラグメントをXMLドキュメントに集計します。この問合せは、例9-29に示すリレーションを返します。
xmlelement
の詳細は、「xmlelement_expr」を参照してください。
例9-27 xmlaggの問合せ
<query id="tkdata67_q1"><![CDATA[ select c1, xmlagg(xmlelement("c2",c2)) from tkdata67_S0[rows 10] group by c1 ]]></query>
例9-28 xmlaggのリレーション入力
Timestamp Tuple 1000 15, 0.1 1000 20, 0.14 1000 15, 0.2 4000 20, 0.3 10000 15, 0.04 h 12000
例9-29 xmlaggのリレーション出力
Timestamp Tuple Kind Tuple 1000: + 15,<c2>0.1</c2> <c2>0.2</c2> 1000: + 20,<c2>0.14</c2> 4000: - 20,<c2>0.14</c2> 4000: + 20,<c2>0.14</c2> <c2>0.3</c2> 10000: - 15,<c2>0.1</c2> <c2>0.2</c2> 10000: + 15,<c2>0.1</c2> <c2>0.2</c2> <c2>0.04</c2>
xmlagg関数とORDER BY句
例9-30
の問合せtkxmlAgg_q5と例9-31の入力リレーションがあるとします。ストリームtkxmlAgg_S1
のスキーマは(c1 int, c2 xmltype)
です。この問合せでは、xmltype
ストリーム要素を選択し、XMLAGGを使用してそれらをXMLドキュメントに集計します。また、ORDER BY
句を使用してXMLフラグメントを並べ替えます。この問合せは、例9-32に示すリレーションを返します。
例9-30 xmlaggとORDER BYの問合せ
<query id="tkxmlAgg_q5"><![CDATA[ select xmlagg(c2), xmlagg(c2 order by c1) from tkxmlAgg_S1[range 2] ]]></query>
例9-31 xmlaggとORDER BYのリレーション入力
Timestamp Tuple 1000 1, "<a>hello</a>" 2000 10, "<b>hello1</b>" 3000 15, "<PDRecord><PDName>hello</PDName></PDRecord>" 4000 5, "<PDRecord><PDName>hello</PDName><PDName>hello1</PDName></PDRecord>" 5000 51, "<PDRecord><PDId>6</PDId><PDName>hello1</PDName></PDRecord>" 6000 15, "<PDRecord><PDId>46</PDId><PDName>hello2</PDName></PDRecord>" 7000 55, "<PDRecord><PDId>6</PDId><PDName>hello2</PDName><PDName>hello3</PDName></PDRecord>"
例9-32 xmlaggとORDER BYのリレーション出力
Timestamp Tuple Kind Tuple 0: + 1000: - 1000: + <a>hello</a> ,<a>hello</a> 2000: - <a>hello</a> ,<a>hello</a> 2000: + <a>hello</a> <b>hello1</b> ,<a>hello</a> <b>hello1</b> 3000: - <a>hello</a> <b>hello1</b> ,<a>hello</a> <b>hello1</b> 3000: + <b>hello1</b> <PDRecord> <PDName>hello</PDName> </PDRecord> ,<b>hello1</b> <PDRecord> <PDName>hello</PDName> </PDRecord> 4000: - <b>hello1</b> <PDRecord> <PDName>hello</PDName> </PDRecord> ,<b>hello1</b> <PDRecord> <PDName>hello</PDName> </PDRecord> 4000: + <PDRecord> <PDName>hello</PDName> </PDRecord> <PDRecord> <PDName>hello</PDName> <PDName>hello1</PDName> </PDRecord> ,<PDRecord> <PDName>hello</PDName> <PDName>hello1</PDName> </PDRecord> <PDRecord> <PDName>hello</PDName> </PDRecord> 5000: - <PDRecord> <PDName>hello</PDName> </PDRecord> <PDRecord> <PDName>hello</PDName> <PDName>hello1</PDName> </PDRecord> ,<PDRecord> <PDName>hello</PDName> <PDName>hello1</PDName> </PDRecord> <PDRecord> <PDName>hello</PDName> </PDRecord> 5000: + <PDRecord> <PDName>hello</PDName> <PDName>hello1</PDName> </PDRecord> <PDRecord> <PDId>6</PDId> <PDName>hello1</PDName> </PDRecord> ,<PDRecord> <PDName>hello</PDName> <PDName>hello1</PDName> </PDRecord> <PDRecord> <PDId>6</PDId> <PDName>hello1</PDName> </PDRecord> 6000: - <PDRecord> <PDName>hello</PDName> <PDName>hello1</PDName> </PDRecord> <PDRecord> <PDId>6</PDId> <PDName>hello1</PDName> </PDRecord> ,<PDRecord> <PDName>hello</PDName> <PDName>hello1</PDName> </PDRecord> <PDRecord> <PDId>6</PDId> <PDName>hello1</PDName> </PDRecord> 6000: + <PDRecord> <PDId>6</PDId> <PDName>hello1</PDName> </PDRecord> <PDRecord> <PDId>46</PDId> <PDName>hello2</PDName> </PDRecord> ,<PDRecord> <PDId>46</PDId> <PDName>hello2</PDName> </PDRecord> <PDRecord> <PDId>6</PDId> <PDName>hello1</PDName> </PDRecord> 7000: - <PDRecord> <PDId>6</PDId> <PDName>hello1</PDName> </PDRecord> <PDRecord> <PDId>46</PDId> <PDName>hello2</PDName> </PDRecord> ,<PDRecord> <PDId>46</PDId> <PDName>hello2</PDName> </PDRecord> <PDRecord> <PDId>6</PDId> <PDName>hello1</PDName> </PDRecord>