1. SQLリファレンス・ガイド
  2. SQL問合せ管理
  3. SELECT式
  4. 集計関数の使用

集計関数の使用

組込み集計関数を使用して、カウント、合計、平均、最小、最大などの情報を確認できます。

次の関数は、セマンティクスが標準SQLのものと似ているため、SQL集計関数と呼ばれます。これらの関数はグループ化と連携して機能し、グループの行全体の値を集計します。集計関数は、SELECT句またはORDER BY句でのみ使用でき、ネストはできません。

例のとおりにする場合は、例で使用する表のトピックの説明に従って表を作成しデータを挿入します。

次の集計関数がサポートされています。

  • long count(*)
  • long count(any*)
  • number sum(any*)
  • number avg(any*)
  • any_atomic min(any*)
  • any_atomic max(any*)
  • ARRAY(any) array_collect(DISTINCT any*)
  • ARRAY(any) array_collect(any*)
  • long count(DISTINCT any*)

ノート:

すべてのSQL集計関数の名前は、大文字と小文字が区別されます。

count(*)関数

count star関数では、グループ内の行数が返されます。

構文:
long count(*)
セマンティクス:

count star関数では、問合せでフェッチされたレコードの数が計算されます。

戻り型: long

例6-14 レコードに連絡先詳細が含まれている乗客の合計数を調べる

SELECT count(*) AS COUNT_PASSENGER 
FROM BaggageInfo bag
WHERE length(contactPhone) > 0

説明: 航空会社手荷物追跡アプリケーションでは、連絡先詳細を教えた乗客の合計数を計算できます。BaggageInfo表内のcontactPhoneフィールドには、乗客の連絡先詳細が含まれています。count star関数を使用して、contactPhoneエントリがある乗客レコードの数を特定します。連絡先詳細にはNULL値と空文字列が含まれている可能性があります。WHERE句でlength関数を使用すると、このような行をカウントから除外できます。contactPhoneに空の値またはNULL値が含まれている場合は、length関数でNULL値が返されます。値比較を適用して、0より大きい値が生じる行のみを選択してから、count star関数を使用して結果となる行の合計数を特定します。

出力:
{"COUNT_PASSENGER":21}

例6-15 2019年2月1日に到着した受託手荷物の数を調べる

SELECT count(*) AS COUNT_BAGS
FROM BaggageInfo bag,
EXTRACT(DAY FROM CAST (bag.bagInfo[0].bagArrivalDate AS Timestamp(0))) $t1,
EXTRACT(MONTH FROM CAST (bag.bagInfo[0].bagArrivalDate AS Timestamp(0))) $t2,
EXTRACT(YEAR FROM CAST (bag.bagInfo[0].bagArrivalDate AS Timestamp(0))) $t3
WHERE $t3=2019 AND $t2=02 AND $t1=01

説明: 航空会社手荷物追跡アプリケーションでは、特定の日付の受託手荷物の合計数を取得できます。BaggageInfo表内のbagArrivalDateフィールドには、乗客の受託手荷物の到着日が含まれています。前述の問合せでは、bagArrivalDateが2019年2月1日である行の数をカウントして、指定した日付の受託手荷物の数をフェッチしています。ここではいくつかのフィルタ条件を使用して、タイムスタンプの日付部分のみを抽出しています。最初にCAST演算子を使用してbagArrivalDateをタイムスタンプに変換し、次に、EXTRACT式を使用してそのタイムスタンプから日、月および年の詳細を抽出しています。値比較を使用して、その日、月および年が目的の日付値(つまり、それぞれ01、02および2019)に対応しているかどうかを確認しています。その後、論理演算子ANDを使用して、3つすべての条件に一致する行のみを選択し、count関数を使用して結果となる行をカウントしています。

出力:
{"COUNT_BAGS":1}

count関数

count関数では、グループ内の各行でその入力式が計算され、その入力式の評価によって返された非NULL値がすべてカウントされます。

構文:
long count(any*)
セマンティクス:

any: count関数で、入力引数としてすべてのパラメータ・タイプが受け入れられます。

戻り型: long

count star関数は、結果となる問合せの行をカウントする必要がある場合に使用できます。他の入力引数は受け入れられません。一方、count関数は、入力式の結果を列挙する必要がある場合に使用できます。

例6-16 LAX空港に特定の時間に到着する予定の受託手荷物の合計数を調べる

SELECT $estdate as ARRIVALDATE, 
count($flight) AS COUNT
FROM BaggageInfo $bag, 
$bag.bagInfo.flightLegs.estimatedArrival $estdate,
$bag.bagInfo.flightLegs.flightNo $flight,  
$bag.bagInfo.flightLegs.fltRouteDest $flt_dest 
WHERE $estdate =any "2019-02-01T11:00:00Z" AND $flt_dest =any "LAX"
GROUP BY $estdate

説明: 航空会社手荷物追跡アプリケーションでは、特定の空港に特定の時間に到着する予定の受託手荷物の合計数を取得できます。フライト区間ごとに、BaggageInfo表のflightLegs配列内のestimatedArrivalフィールドに受託手荷物の到着時間が含まれており、fltRouteDestフィールドに到着地空港コードが含まれています。前述の問合せでは、指定の時間にLAX空港に到着する受託手荷物の合計数を調べるために、まずGROUP BY句を使用して、到着予定時刻の値でデータをグループ化しています。そのグループから、到着地空港がLAXである行のみを選択しています。次に、count関数を使用して、結果となる行の手荷物数を調べています。

ここでは、タイムスタンプ・データ型にキャストせずに、文字列の自然なソート順序により、文字列書式がISO-8601書式である日付を比較できます。$bag.bagInfo.flightLegs.estimatedArrivalおよび$bag.bagInfo.flightLegs.fltRouteDestは順序です。比較演算子'='は複数項目の順序には効果がないため、かわりに順序比較演算子'=any'を使用してestimatedArrivalフィールドとfltRouteDestフィールドを比較します。

出力:
{"ARRIVALDATE":"2019-02-01T11:00:00Z","COUNT":2}

例6-17 TVストリーミング・アプリケーションで特定の番組の視聴者の数を調べる

SELECT count($a.contentStreamed[$element.showName = "Bienvenu"]) AS WATCHERS
FROM stream_acct $s, $s.acct_data $a

説明: TVストリーミング・アプリケーションでは、加入者が視聴している番組のリストがcontentStreamed配列に格納されています。その配列の各要素は、1つの番組に対応しています。showNameフィールドには、各番組の名前が含まれています。特定の番組の視聴者の数をフェッチするために、count関数で配列フィルタ・ステップ式を使用しています。加入者ごとに、指定した番組(この例ではBienvenu)とshowNameフィールドが一致するかどうかをチェックし、count関数を使用して該当する加入者の合計数をカウントしています。

出力:
{"WATCHERS":4}

sum関数

sum関数は、グループの各行の入力式を計算し、入力式のこれらの評価によって返されたすべての数値を合計します。固定スキーマの場合、sum関数では、数値以外のフィールドを合計しようとするとエラーが返されます。スキーマレスJSONの場合、sum関数では、数値以外の値はスキップされます。入力式のすべての値が数値以外である場合は、NULL値が返されます。sum関数の結果となる値の型は、入力項目のタイプに応じて、long、doubleまたはnumberになります。
  • number型の入力項目が1つ以上ある場合、結果はnumberになります。
  • double型またはfloat型の項目が1つ以上ある場合、結果はdoubleになり、それ以外の場合、結果はlong型になります。
  • 入力項目でlong、doubleおよびnumberが混在している場合、結果はdouble型になります。
  • sum関数の入力によって数値が返されない場合、結果はNULLになります。
構文:
long sum(any*)
セマンティクス:

any: sum関数で、入力引数としてすべてのパラメータ・タイプが受け入れられます。

戻り型: long

例6-18 TVストリーミング・アプリケーションで加入者が番組を視聴していた時間の合計を調べる

SELECT sum($content.seriesInfo[].episodes[].minWatched) AS MINSWATCHED
FROM stream_acct $s, $s.acct_data.contentStreamed[] $content
WHERE acct_id = 1 AND $content.showName = "At the Ranch"

説明: TVストリーミング・アプリケーションでは、各加入者が番組を視聴していた時間の合計を計算できます。各加入者は一意のアカウントIDに関連付けられており、それはstream_acct表のacct_idフィールドに格納されています。番組の情報は、contentStreamed配列に格納されています。showNameフィールドには番組の名前が含まれており、minWatchedフィールドには番組の各シーズンでのエピソードごとの経過時間が格納されています。この問合せでは、sum関数で、アカウントIDが1の加入者について、At the Ranchという番組の、すべてのシーズンでのすべてのエピソードのminWatchedフィールドの値を加算して、合計視聴時間を計算しています。

出力:
{"MINSWATCHED":225}

avg関数

avg (平均)関数は、グループの各行の入力式を計算し、入力式のこれらの評価によって返されたすべての数値を合計およびカウントします。数値以外の値はスキップされます。入力式で数値が返されない場合は、エラー・メッセージが返されます。結果の値は、合計をカウントで除算した値です。この値の型は、入力項目の型に応じて、doubleまたはnumberになります。
  • number型の入力項目が1つ以上ある場合、結果はnumberになり、それ以外の場合、結果はdoubleになります。
  • avg関数の入力によって数値が返されない場合、結果はNULLになります。
構文:
number avg(any*)
セマンティクス:

any: avg関数で、入力引数としてすべてのパラメータ・タイプが受け入れられます。

戻り型: number

例6-19 加入者による番組視聴時間の平均を調べる

SELECT avg($content.seriesInfo[].episodes[].minWatched) AS AVERAGETIME
FROM stream_acct $s, $s.acct_data.contentStreamed[] $content
WHERE acct_id = 1 AND $content.showName = "At the Ranch"

説明: TVストリーミング・アプリケーションでは、各加入者が番組を視聴していた時間の平均を計算できます。各加入者は一意のアカウントIDに関連付けられており、それはstream_acct表のacct_idフィールドに格納されています。番組の情報は、contentStreamed配列に格納されています。showNameフィールドにはシリーズの名前が含まれており、minWatchedフィールドには番組の各シーズンでのエピソードごとの経過時間が格納されています。この問合せでは、minWatchedフィールドでavg関数を使用して、アカウントIDが1の加入者がAt the Ranchという番組を視聴していた時間の平均を計算しています。このavg関数では、最初に、指定した番組について、minWatchedフィールドの値を加算することで合計視聴時間を計算しています。次に、その合計をminWatchedフィールドの数で除算して平均値を計算しています。

出力:
{"AVERAGETIME":56.25}

min関数

min関数では、グループ内の各行で入力式が評価され、その結果であるすべての値の中で最も小さい値が返されます。入力式は次のように評価されます。
  1. コンパイル中に、入力式によって返された値が、順序比較が定義されていない型(たとえば、RECORD、MAP、BINARYまたはFIXED_BINARY)に属していると判断された場合は、エラーが表示されます。それ以外の場合、各グループの最小値はNULLに初期化されます。
  2. Mが現在の最小値、Nが次の入力値であるとします。MとNを、値比較演算子を使用して比較します。MがNULLの場合、MはNに設定されます。そうではなく、NがMより小さい場合は、Nが最小値になることができるため、MがNに設定され、Nが次の入力値に設定されます。これは、入力式内のすべての値を比較し最小値を確定するまで続きます。値を比較できない場合は、次の順序が使用されます。
    numeric values < timestamps < strings and enums < booleans
  3. Nがレコード、マップ、配列、バイナリ値、固定バイナリ値、NULLまたはJSON NULLである場合は、それがスキップされ、次の入力値が考慮されます。
構文:
any_atomic min(any*)
セマンティクス:

any: min関数で、入力引数としてすべてのパラメータ・タイプが受け入れられます。

戻り型: アトミック・データ型

max関数

max関数では、グループ内の各行で入力式が評価され、その結果であるすべての順序の中で最も大きい値が返されます。具体的なルールはmax関数と同じですが、NがスキップされずMより大きい場合は、現在の最大値Mが次の入力値Nで置換されます。

構文:
any_atomic max(any*)
セマンティクス:

any: max関数で、入力引数としてすべてのパラメータ・タイプが受け入れられます。

戻り型: アトミック・データ型

例6-20 特定の番組について最短視聴時間と最長視聴時間を調べる

SELECT min($content.seriesInfo[].episodes[].lengthMin) AS MINTIME
max($content.seriesInfo[].episodes[].lengthMin) AS MAXTIME
FROM stream_acct $s, $s.acct_data.contentStreamed[] $content
WHERE $content.showName = "At the Ranch"
説明: TVストリーミング・アプリケーションでは、番組の最短時間と最長時間を調べることができます。stream_acct表内のlengthMinフィールドには、番組の各エピソードの長さが格納されています。この問合せでは、次のものを使用します。
  • lengthMinフィールドでmin関数を使用して、視聴時間が最も短い、番組At the Ranchのエピソードの時間をフェッチします。
  • lengthMinフィールドでmax関数を使用して、視聴時間が最も長い、番組At the Ranchのエピソードの時間をフェッチします。
出力:
{"MINTIME":45,"MAXTIME":85}

例6-21 集計関数 - ユーザー・データ表からユーザーの年齢および平均収入をフェッチする

CREATE INDEX idx11 ON users (age);

SELECT 
age, count(*) AS count, avg(income) AS income 
FROM users 
GROUP BY age;

説明: ユーザー・データを保持するアプリケーションについて考えてみます。例で使用する表の項でユーザー・データ・アプリケーション表を参照してください。users表には、年齢グループが同一のユーザーの複数のレコードが含まれています。集計関数を使用して、該当する加入者の数とその平均収入を取得します。前述の問合せでは、ユーザーが年齢別にグループ化され、年齢ごとに、年齢グループが同一のユーザーの数と、その平均収入が返されます。

出力:
{"age":22,"count":2,"income":50000.0}
{"age":45,"count":1,"income":75000.0}

array_collect(DISTINCT any*)関数

array_collect(DISTINCT any*)関数は、グループの各行の入力式を計算し、結果として生成されるNULL以外のすべての値を配列に収集します。

構文:
ARRAY(any) array_collect(DISTINCT any*)
セマンティクス:

array_collect関数は、グループの各行の入力式を計算します。入力式は、SELECT式以外の任意の種類の式にすることができます。結果として得られるすべての個別の値(NULL値を除く)が配列に収集されます。この関数は作成された配列を返します。

ノート:

DISTINCTは、値比較演算子のセマンティクスを使用して、値が等価であるかどうかを比較します。ただし、次の例外があります。文字列は(列挙やタイムスタンプではなく)文字列のみ、列挙は(文字列ではなく)列挙のみ、およびタイムスタンプは(文字列ではなく)タイムスタンプのみと比較できます。

例6-22 TVストリーミング・アプリケーションで、すべての国で視聴された一連の異なる番組IDを(配列として)返します。

SELECT acct.acct_data.country,
array_collect(distinct acct.acct_data.contentStreamed.showId) AS shows 
FROM stream_acct acct group by acct.acct_data.country;

説明: TVストリーミングアプリケーションは、世界中の顧客が見ている様々な番組をストリーミングします。ここでは、各国の個別の番組IDのリストが必要です。国に基づいてデータをグループ化し、各国の番組IDを配列としてリストします。distinct演算子を使用して重複した番組IDを排除します。

出力:
{"country":"USA","shows":[16,15]}
{"country":"France","shows":[15]}
{"country":"Germany","shows":[26,15]}

例6-23 TVストリーミング・アプリケーションで、すべての国の顧客が視聴した一連の異なるジャンルを(配列として)返します。

SELECT acct.acct_data.country,
array_collect(distinct acct.acct_data.contentStreamed.genres[]) AS genres 
FROM stream_acct acct group by acct.acct_data.country;
説明: TVストリーミングアプリケーションは、世界中の顧客が見ている様々な番組をストリーミングします。ここでは、すべての国の顧客が見ている番組の個別ジャンルのリストが必要です。国に基づいてデータをグループ化し、各国について、acct.acct_data.contentStreamed.genres[]を使用してすべての番組のジャンルを1つの配列にフェッチします。distinct演算子で重複するジャンルを排除します。

ノート:

前述の問合せでは、すべてのジャンルの詳細を単一の配列でフェッチするために、acct.acct_data.contentStreamed.genres[]を使用しています。acct.acct_data.contentStreamed.genresで[]を省略すると、次に示すようにジャンルの詳細は行ごとに個別の配列としてフェッチされ、distinctは個別の配列にのみ適用され、セット全体には適用されません。
SELECT acct.acct_data.country,
array_collect(distinct acct.acct_data.contentStreamed.genres) AS genres
FROM stream_acct acct group by acct.acct_data.country

{"country":"USA","genres":[["comedy","drama","danish"],["comedy","french"]]}
{"country":"France","genres":[["comedy","french"]]}
{"country":"Germany","genres":[["action","crime","spanish"],["comedy","french"]]}

前述の例では、データが国に基づいてグループ化され、各国について、顧客が視聴したジャンルが行レベルの配列としてフェッチされていることがわかります。たとえば、countryがUSAの場合、ジャンルの2つの配列が表示されています。DISTINCTは配列レベルで適用されます。配列が同じ場合のみ、DISTINCTは1つの配列のみをフェッチします。それ以外の場合は、前述の結果に示すようにすべての配列がフェッチして返されます。

出力:
{"country":"USA","genres":["drama","danish","comedy","french"]}
{"country":"France","genres":["comedy","french"]}
{"country":"Germany","genres":["spanish","comedy","action","crime","french"]}

array_collect(any*)関数

array_collect(any*)関数は、グループの各行の入力式を計算し、結果として生成されるNULL以外のすべての値を配列に収集します。

構文:
ARRAY(any) array_collect(any*)
セマンティクス:

array_collect関数は、グループの各行の入力式を計算します。入力式は、SELECT式以外の任意の種類の式にすることができます。この関数は、結果のすべての値(NULL値を除く)を配列に収集し、移入された配列を返します。array_collect関数では、重複する値を配列に挿入できます。

例6-24 TVストリーミング・アプリケーションで、米国およびドイツの顧客の名と姓を(配列として)返します。

SELECT acct.acct_data.country,
array_collect(
{"firstName":acct.acct_data.firstName,
"lastName":acct.acct_data.lastName}) as user_info
FROM stream_acct acct WHERE 
acct.acct_data.country IN ('USA','Germany') 
group by acct.acct_data.country;

説明: TVストリーミングアプリケーションは、世界中の顧客が見ている様々な番組をストリーミングします。ここでは、米国およびドイツの顧客のユーザー情報(名と姓)を取得します。国に基づいてデータをグループ化し、2つの国(米国およびドイツ)のデータのみをフィルタ処理します。これら2つの国ごとに、すべての顧客の名と姓を取得して、それを1つの配列に移入します。

出力:
{"country":"USA","user_info":[{"firstName":"John","lastName":"Lewis"}]}
{"country":"Germany","user_info":[{"firstName":"Angela","lastName":"Mercel"}]}

long count(DISTINCT any*)関数

count関数は、グループの各行の入力式を計算し、入力式によって返されたNULL以外のすべての個別の値をカウントします。

構文:
long count(DISTINCT any*)
セマンティクス:
count関数は、グループの各行の入力式を計算します。入力式は、副問合せ以外の任意の種類の式にすることができます。この関数は、入力式によって返されたNULL以外の個別の値をすべてカウントします。count関数の戻り型はlongです。

ノート:

DISTINCTは、値比較演算子のセマンティクスを使用して、値が等価であるかどうかを比較します。ただし、次の例外があります。文字列は(列挙やタイムスタンプではなく)文字列のみ、列挙は(文字列ではなく)列挙のみ、およびタイムスタンプは(文字列ではなく)タイムスタンプのみと比較できます。

例6-25 TVストリーミング・アプリケーションで、顧客が視聴したすべてのジャンルの個別の番組IDの数を返します。

SELECT $genre, count(distinct $content.showId) AS show_count 
FROM stream_acct acct, acct.acct_data.contentStreamed[] as $content, 
$content.genres[] as $genre, $content.showId as $showid group by $genre;
説明: TVストリーミングアプリケーションは、世界中の顧客が見ている様々な番組をストリーミングします。ここでは、顧客が視聴したすべてのジャンルの個別の番組の数が必要です。ストリーミングされたコンテンツの詳細は、acct.acct_data.contentStreamed[]を使用して単一の配列にフェッチされます。ジャンルに基づいてデータをグループ化し、すべてのジャンルについて、showidのリストを配列としてフェッチします。distinct演算子を使用して重複した番組IDを排除します。その後、すべてのジャンルの個別の番組IDの数が返されます。

ノート:

前述の問合せで、acct.acct_data.contentStreamedの[]を省略すると、ストリーミングされたコンテンツの詳細が各行の個別の配列としてフェッチされ、count(distinct )は個別の配列にのみ適用されて、セット全体には適用されません。
出力:
{"genres":"crime","show_count":1}
{"genres":"action","show_count":1}
{"genres":"comedy","show_count":2}
{"genres":"spanish","show_count":1}
{"genres":"french","show_count":1}
{"genres":"drama","show_count":1}
{"genres":"danish","show_count":1}