GROUP BY句

GROUP BY句はSELECT文で使用して、複数行にわたるデータを収集し、結果を1つ以上の列または式でグループ化します。GROUP BY句は、多くの場合、集計関数とともに使用します。Oracle NoSQL Databaseでは、集計関数が行の各グループに適用され、グループごとに1行が返されます。

構文

groupby_clause ::= GROUP BY expression ("," expression)*

セマンティクス

各(グループ化)式は、最大で1つのアトミック値を返す必要があります。グループ化式が入力行に対して空の結果を返す場合、その行はスキップされます。グループ化値間の等価性は、=演算子のセマンティクスに従って定義されます。ただし、2つのNULL値は等しいとみなされます。値の比較演算子の項を参照してください。次に、グループごとに、GROUP BY句によって単一のレコードが作成されて返されます。この句にN個のグループ化式がある場合は、返されたレコードの最初のN個のフィールドに、グループ化式の値が格納されます。残りのM個のフィールド(m >= 0)には、ゼロ個以上の集計関数の結果が格納されます。一般に集計関数は、グループの行を反復し、該当する各行の式を評価し、返された値をグループごとに1つの値に集計します。Oracle NoSQL Databaseでは、集計関数の使用の項で説明されているような多くの集計関数がサポートされます。

構文的には、集計関数は実際にはGROUP BY句にリストされませんが、SELECT句には表示されます。実際、集計関数はSELECT句またはORDER BY句にのみ表示され、ネストすることはできません。ただし、意味的にはSELECTリストまたはORDER BYリストに表示される各集計関数は、実際にはGROUP BY句によって評価されます。SELECT句に集計関数が含まれていても、SELECT式にGROUP BY句が含まれていない場合は、FROM句またはWHERE句で生成されたすべての行が1つのグループとみなされ、集計関数がこの1つのグループ全体に対して評価されます。

GROUP BY句の実装は、索引ベースまたは汎用です。索引ベースのグループ化が可能なのは、グループ化式の値で行をソートする索引が存在する場合のみです。より正確には、e1、e2、…、eN (eiはi番目の式(iは1、2、3、...Nの範囲の数値))をGROUP BY句に表示されるとおり(左から右)にグループ化式とします。次に、索引ベースのグループ化の場合、1、2、...、Nの各iについてeiがi番目の索引フィールドの定義に一致する索引(主キー索引または既存の2次索引の1つ)が存在する必要があります。このような索引が存在しない場合または問合せオプティマイザによって選択されていない場合、GROUP BYは汎用になります。汎用GROUP BYは、ハッシュ表を使用して同じグループに属する行を検索し、結果をアプリケーションに返す前にすべてのグループを格納します。ハッシュ表はクライアント・ドライバ・メモリーに格納されます(サイズが制限されたローカル・ハッシュ表もサーバーで使用できます)。その結果、汎用GROUP BYは大量のドライバ・メモリーを消費する可能性があります。一方、索引ベースのグループ化は、索引によって提供される行ソートを利用して、中間結果のマテリアライズおよびキャッシュを回避します。したがって、GROUP BY問合せで使用するのに適切な索引を作成することをお薦めします。問合せの最適化での索引の使用を参照してください。最後に、索引ベースのグループ化を使用する場合、グループ化SELECT式の結果はグループ化式に基づいて順序付けられることに注意してください。

例6-10 ユーザー・データを保持するアプリケーションの年齢によるグループ化

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

説明: この問合せでは、GROUP BY句を使用してユーザーを年齢でグループ化します。グループごとに、問合せにより、グループ内のユーザーの関連する年齢と平均収入が返されます。集計関数countを使用して各年齢グループのユーザーをカウントし、avgを使用して平均収入を計算します。

出力:

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

例6-11 航空会社アプリケーションでの各乗客の荷物数の表示

SELECT 
bag.confNo,
count(bag.bagInfo.id) AS TOTAL_BAGS 
FROM Baggageinfo bag 
GROUP BY bag.confNo

この問合せでは、GROUP BY句を使用して、confNoに基づいてデータをグループ化します。乗客ごとの荷物数をフェッチするには、count集計関数を使用して、各confNoに関連付けられているbagInfo.idフィールドの数を取得します。

出力:

{"confNo":"FH7G1W","TOTAL_BAGS":1}
{"confNo":"PQ1M8N","TOTAL_BAGS":1}
{"confNo":"XT6K7M","TOTAL_BAGS":1}
{"confNo":"DN3I4Q","TOTAL_BAGS":1}
{"confNo":"QB1O0J","TOTAL_BAGS":1}
{"confNo":"TX1P7E","TOTAL_BAGS":1}
{"confNo":"CG6O1M","TOTAL_BAGS":1}
{"confNo":"OH2F8U","TOTAL_BAGS":1}
{"confNo":"BO5G3H","TOTAL_BAGS":1}
{"confNo":"ZG8Z5N","TOTAL_BAGS":1}
{"confNo":"LE6J4Z","TOTAL_BAGS":1}
{"confNo":"XT1O7T","TOTAL_BAGS":1}
{"confNo":"QI3V6Q","TOTAL_BAGS":2}
{"confNo":"RL3J4Q","TOTAL_BAGS":1}
{"confNo":"HJ4J4P","TOTAL_BAGS":1}
{"confNo":"CR2C8MY","TOTAL_BAGS":1}
{"confNo":"LN0C8R","TOTAL_BAGS":1}
{"confNo":"MZ2S5R","TOTAL_BAGS":1}
{"confNo":"KN4D1L","TOTAL_BAGS":1}
{"confNo":"MC0E7R","TOTAL_BAGS":1}

例6-12 航空会社アプリケーションでの各空港からの合計荷物(トランジットの荷物を除く)の選択

SELECT $flt_src as SOURCE, 
count(*) as COUNT 
FROM BaggageInfo $bag, 
$bag.bagInfo.flightLegs[0].fltRouteSrc $flt_src 
GROUP BY $flt_src

説明: BaggageInfo表のflightLegs配列のfltRouteSrcフィールドには、乗客レコード内の起点ステーションの詳細が含まれています。

前述の問合せでは、各空港からの荷物の合計数を取得します。ただし、トランジットで経由する空港は考慮しません。flightLegs配列の最初の要素のフライト出発地の値でデータをグループ化します(最初の要素が出発地であるため)。次に、count関数を使用して荷物の数を決定します。

出力:

{"SOURCE":"SFO","COUNT":6}
{"SOURCE":"BZN","COUNT":1}
{"SOURCE":"GRU","COUNT":1}
{"SOURCE":"LAX","COUNT":1}
{"SOURCE":"YYZ","COUNT":1}
{"SOURCE":"MEL","COUNT":1}
{"SOURCE":"MIA","COUNT":4}
{"SOURCE":"MSQ","COUNT":2}
{"SOURCE":"MXP","COUNT":2}
{"SOURCE":"JFK","COUNT":3}

JSONコレクション表:

次の例では、JSONコレクション表にGROUP BY式を適用します。ショッピング・アプリケーション用に作成されたJSONコレクション表のサンプル行について考えてみます。

{"contactPhone":"1617114988","address":{"Dropbox":"Presidency College","city":"Kansas City","state":"Alabama","zip":95065},"cart":[{"item":"A4 sheets","priceperunit":500,"quantity":2},{"item":"Mobile Holder","priceperunit":700,"quantity":1}],"firstName":"Lorenzo","lastName":"Phil","notify":"yes","orders":[{"EstDelivery":"2023-11-15","item":"AG Novels 1","orderID":"101200","priceperunit":950,"status":"Preparing to dispatch"},{"EstDelivery":"2023-11-01","item":"Wallpaper","orderID":"101200","priceperunit":950,"status":"Transit"}]}

例6-13 storeAcct表から、状態ごとに処理されたオーダー数の統計をフェッチします。

SELECT s.address.state, count(s.orders[]) AS TOTAL_ORDERS 
FROM storeAcct s 
WHERE size(s.orders) >= 1
GROUP BY s.address.state

説明: storeAcctテーブルでは、買物客の住所がaddressフィールドに格納され、購入したアイテムがorders配列に格納されます。状態ごとのオーダー・リクエスト数をフェッチするには、GROUP BY式を使用して買物客のデータをstateフィールドでグループ化します。orders配列内のアイテムが1つ以上あるオーダーのみを選択して、値を持つオーダーをカウントします。

ノート:

配列の要素を集計するには、配列コンストラクタ[]を使用して、ordersフィールドが配列であることを示す必要があります。

出力:

{
  "state" : "Alabama",
  "TOTAL_ORDERS" : 4
}
{
  "state" : "TX",
  "TOTAL_ORDERS" : 2
}