この章では、Oracle Continuous Query Language (Oracle CQL)に含まれる組込みの集計関数のリファレンスを提供します。組込みの集計関数は、問合せが返すすべての値の集計操作を行います。
詳細は、1.1.11項「関数」を参照してください。
この章の内容は次のとおりです。
表9-1に、Oracle CQLに用意されている組込みの集計関数を示します。
選択した重複タプルの各セットで1つのコピーのみが返されるようにするには、distinct
を指定します。重複タプルとはselectリスト内の各式と一致する値を持つタプルです。詳細は、aggr_distinct_exprを参照してください。
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>
詳細は、次を参照してください。
構文
目的
avg
は、expr
の平均値を返します。
この関数は、bigint
、float
またはint
データ型を引数として使用します。また、引数の数値データ型に関係なくfloat
を返します。
例
例9-3の問合せfloat_avg
と例9-4のデータ・ストリームS3
があるとします。ストリームS3
のスキーマは(c1 float)
です。この問合せは、例9-5に示すリレーションを返します。平均値が数字でない場合、avg
関数はNaN
の結果を返します。詳細は、2.3.2項「数値リテラル」を参照してください。
構文
(arith_expr::=、attr::=、identifier::=)
目的
count
は、問合せが返すタプル数をint
の値として返します。
表9-2に示すように、戻り値は引数によって異なります。
表9-2 COUNT集計関数の戻り値
入力引数 | 戻り値 |
---|---|
|
|
|
重複とnullを含む、すべてのタプルの数。 |
|
相関変数 |
|
|
count
は、nullを返しません。
例
例9-6の問合せq2
と例9-7のデータ・ストリームS2
があるとします。ストリームS2
のスキーマは(c1 integer, c2 integer)
です。例9-8は、問合せで返されるリレーションを示します。
例9-8 count関数のリレーション出力
Timestamp Tuple Kind Tuple -9223372036854775808: + 0,0 1000: - 0,0 1000: + 1,1 2000: - 1,1 2000: + 1,2 3000: - 1,2 3000: + 2,3 6000: - 2,3
詳細は、次を参照してください。
構文
目的
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>
構文
目的
max
は、expr
の最大値を返します。そのデータ型は引数のデータ型によって異なります。
例
例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: +
構文
目的
min
は、expr
の最小値を返します。そのデータ型は引数のデータ型によって異なります。
例
例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: +
構文
目的
sum
は、expr
の値の合計を返します。この関数は、bigint
、float
またはinteger
式を引数として使用します。また、引数の数値データ型と同じデータ型を返します。
例
例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>