結果のソート、グループ化および制限

例のとおりに操作する場合は、スクリプトbaggageschema_loaddata.sqlをダウンロードして、次に示すように実行します。このスクリプトにより、例で使用する表が作成され、表にデータがロードされます。

KVSTOREまたはKVLiteを起動し、SQL.shellを開きます。

java -jar lib/kvstore.jar kvlite -secure-config disable
java -jar lib/sql.jar -helper-hosts localhost:5000 -store kvstore

loadコマンドを使用して、スクリプトを実行します。

load -file baggageschema_loaddata.sql

結果の順序付け

ORDER BY句を使用して、任意の列、主キーまたは主キー以外のキーで結果を順序付けします。

例1: すべての乗客のチケット番号をフルネームでソートします。

SELECT bag.ticketNo, bag.fullName 
FROM BaggageInfo bag 
ORDER BY bag.fullName

説明: 乗客のフルネームに基づいて、BaggageInfoスキーマ内の乗客のチケット番号を昇順にソートします。

出力:

{"ticketNo":1762344493810,"fullName":"Adam Phillips"}
{"ticketNo":1762392135540,"fullName":"Adelaide Willard"}
{"ticketNo":1762376407826,"fullName":"Dierdre Amador"}
{"ticketNo":1762355527825,"fullName":"Doris Martin"}
{"ticketNo":1762324912391,"fullName":"Elane Lemons"}
{"ticketNo":1762350390409,"fullName":"Fallon Clements"}
{"ticketNo":1762341772625,"fullName":"Gerard Greene"}
{"ticketNo":176234463813,"fullName":"Henry Jenkins"}
{"ticketNo":1762383911861,"fullName":"Joanne Diaz"}
{"ticketNo":1762377974281,"fullName":"Kendal Biddle"}
{"ticketNo":1762355854464,"fullName":"Lisbeth Wampler"}
{"ticketNo":1762320369957,"fullName":"Lorenzo Phil"}
{"ticketNo":1762320569757,"fullName":"Lucinda Beckman"}
{"ticketNo":1762340683564,"fullName":"Mary Watson"}
{"ticketNo":1762330498104,"fullName":"Michelle Payne"}
{"ticketNo":1762348904343,"fullName":"Omar Harvey"}
{"ticketNo":1762399766476,"fullName":"Raymond Griffin"}
{"ticketNo":1762311547917,"fullName":"Rosalia Triplett"}
{"ticketNo":1762357254392,"fullName":"Teena Colley"}
{"ticketNo":1762390789239,"fullName":"Zina Christenson"}
{"ticketNo":1762340579411,"fullName":"Zulema Martindale"}

例2: 最後に確認されたステーションがMELである乗客(名前でソート)について、最後に確認された時刻(最新の最初)で乗客の詳細(フルネーム、タグ番号)をフェッチします。

SELECT bag.fullName, bag.bagInfo[].tagNum, 
bag.bagInfo[].lastSeenTimeGmt
FROM BaggageInfo bag 
WHERE bag.bagInfo[].lastSeenStation=any "MEL"
ORDER BY bag.bagInfo[].lastSeenTimeGmt DESC

説明: 最初に、最後に確認されたステーションに基づいてBaggageInfo表内のデータをフィルタ処理し、最後に確認された時刻および乗客のフルネームに基づいて、フィルタされた結果を降順にソートします。これを行うには、ORDER BY句を使用します。

ノート:

複数の列を使用して、問合せの出力をソートできます。

出力:

{"fullName":"Adam Phillips","tagNum":"17657806255240","lastSeenTimeGmt":"2019-02-01T16:13:00Z"}
{"fullName":"Zina Christenson","tagNum":"17657806228676","lastSeenTimeGmt":"2019-02-04T10:08:00Z"}
{"fullName":"Joanne Diaz","tagNum":"17657806292518","lastSeenTimeGmt":"2019-02-16T16:13:00Z"}
{"fullName":"Zulema Martindale","tagNum":"17657806288937","lastSeenTimeGmt":"2019-02-25T20:15:00Z"}

結果の制限およびオフセットの指定

LIMIT句を使用して、SELECT文から戻される結果の数を制限できます。たとえば、表に1000行ある場合、LIMIT値を指定して返される行の数を制限します。LIMITおよびOFFSETをORDER BY句とともに使用することをお薦めします。そうしない場合、結果はランダムな順序で戻され、結果が予測できなくなります。

LIMITおよびOFFSETを使用する場合の適切なユースケース/例は、結果のアプリケーション・ページングです。たとえば、アプリケーションで1ページ当たり4つの結果を表示するとします。制限およびオフセットを使用して、アプリケーションでステートレス・ページングを実装できます。1ページ当たりn (たとえば4)の結果を表示する場合、m (たとえば2)ページの結果が表示されて、オフセットは(n*m-1) (この例では4)となり、制限はn (ここでは4)になります。

例1: アプリケーションで1ページに4つの結果を表示できます。最後に確認されたステーションがJTRである乗客の、最初のページでアプリケーションによってフェッチされた詳細をフェッチします。

SELECT $bag.fullName, $bag.bagInfo.tagNum, $flt_time
FROM BaggageInfo $bag, 
$bag.bagInfo[].lastSeenTimeGmt $flt_time
WHERE $bag.bagInfo[].lastSeenStation=any "JTR" 
ORDER BY $flt_time LIMIT 4

説明: 最後に確認されたステーションに基づいてBaggageInfo表のデータをフィルタ処理し、最後に確認された時刻に基づいて結果をソートします。ネストされていない配列を使用して、データをフラット化します。つまり、bagInfo配列はフラット化され、最後に確認された時刻がフェッチされます。結果セットの最初の4行のみを表示する必要があります。

出力:

{"fullName":"Michelle Payne","tagNum":"17657806247861","flt_time":"2019-02-02T23:59:00Z"}
{"fullName":"Gerard Greene","tagNum":"1765780626568","flt_time":"2019-03-07T16:01:00Z"}
{"fullName":"Lorenzo Phil","tagNum":["17657806240001","17657806340001"],"flt_time":"2019-03-12T15:05:00Z"}
{"fullName":"Lucinda Beckman","tagNum":"17657806240001","flt_time":"2019-03-12T15:05:00Z"}

例2: アプリケーションで、1ページに4つの結果を表示できます。最後に確認されたステーションがJTRである乗客の、2ページ目でアプリケーションによってフェッチされた詳細をフェッチします。

SELECT $bag.fullName, $bag.bagInfo.tagNum, $flt_time 
FROM BaggageInfo $bag, 
$bag.bagInfo[].lastSeenTimeGmt $flt_time 
WHERE $bag.bagInfo[].lastSeenStation=any "JTR" 
ORDER BY $flt_time LIMIT 4 OFFSET 4

説明: 最後に確認されたステーションに基づいてBaggageInfo表のデータをフィルタ処理し、最後に確認された時刻に基づいて結果をソートします。ネストされていない配列を使用して、データをフラット化します。2ページ目の内容を表示する必要があるため、OFFSET 4を設定します。4行に制限されますが、合計結果セットは5行のみであるため、1行のみが表示されます。最初の複数個がスキップされ、5番目が表示されます。

出力:

{"fullName":"Lorenzo Phil","tagNum":["17657806240001","17657806340001"],
"flt_time":"2019-03-12T16:05:00Z"}

結果のグループ化

GROUP BY句を使用して、1つ以上の表の列ごとに結果をグループ化します。通常、GROUP BY句は、COUNT、SUM、AVGなどの集計式と組み合せて使用されます。

例1: 各予約の手荷物の数を表示します。

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

説明: 乗客ごとに1つの予約コード(confNo)があります。乗客は、複数の手荷物を所持することがあります。ここでは、予約コードに基づいてデータをグループ化し、予約ごとの手荷物の数を示すbagInfo配列の数を取得します。

出力:

{"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}

例2: 各空港から発生した合計手荷物(トランジットの手荷物を除く)を選択します。

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

説明: 各空港から発生した手荷物の合計数を取得する必要があります。ただし、トランジットで経由する空港は考慮しません。したがって、flightLegs配列の最初のレコードのフライト出発地の値でデータをグループ化します(最初のレコードが出発地であるため)。これにより、手荷物の数を決定します。

出力:

{"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}