Tri du regroupement et de la limitation des données
Si vous voulez suivre les exemples, voir pour voir des données-échantillons et utiliser les scripts pour charger des données-échantillons à des fins de test. Les scripts créent les tables utilisées dans les exemples et chargent les données dans les tables.
Si vous voulez suivre les exemples, voir Exemples de données pour exécuter des interrogations pour voir des exemples de données et apprendre à utiliser la console OCI pour créer les exemples de tables et charger des données à l'aide de fichiers JSON.
Rubriques connexes
Tri des résultats
Utilisez la clause ORDER BY pour trier les résultats par colonne, clé primaire ou clé non principale.
SELECT bag.ticketNo, bag.fullName
FROM BaggageInfo bag
ORDER BY bag.fullName
Explication : Vous triez le nombre de passagers dans le schéma BaggageInfo
en fonction du nom complet des passagers par ordre croissant.
{"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"}
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
en fonction de la dernière station vue et vous triez les résultats filtrés en fonction de la dernière heure vue et du nom complet des passagers par ordre décroissant. Pour ce faire, utilisez la clause ORDER BY.
Note :
Vous pouvez utiliser plusieurs colonnes pour trier la sortie de l'interrogation.{"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"}
Limiter et compenser les résultats
Utilisez la clause LIMIT
pour limiter le nombre de résultats retournés par un énoncé SELECT. Par exemple, s'il y a 1000 lignes dans une table, limitez le nombre de lignes à renvoyer en spécifiant une valeur LIMIT. Il est recommandé d'utiliser LIMIT et OFFSET avec une clause ORDER BY. Sinon, les résultats sont renvoyés dans un ordre aléatoire, produisant des résultats imprévisibles.
Un bon cas d'utilisation / exemple d'utilisation de LIMIT et OFFSET est la pagination des résultats de l'application. Par exemple, votre application souhaite afficher 4 résultats par page. Vous pouvez utiliser la limite et le décalage pour mettre en oeuvre la pagination sans état dans l'application. Si vous affichez n (par exemple 4 ) résultats par page, alors les résultats pour la page m (par exemple 2) sont affichés, alors le décalage serait (n*m-1) qui est 4 dans cet exemple et la limite serait n (qui est 4 ici).
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
Explication : Vous filtrez les données de la table BaggageInfo
en fonction de la dernière station vue et vous triez les résultats en fonction de la dernière heure vue. Vous utilisez un tableau anesthésiant pour aplanir vos données. C'est-à-dire que le tableau bagInfo
est aplati et que l'heure de la dernière consultation est extraite. Il vous suffit d'afficher les 4 premières lignes du jeu de résultats.
{"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"}
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
Explication : Vous filtrez les données de la table BaggageInfo
en fonction de la dernière station vue et vous triez les résultats en fonction de la dernière heure vue. Vous utilisez un tableau anesthésiant pour aplanir vos données. Vous devez afficher le contenu de la deuxième page afin de définir un OFFSET 4. Bien que la limite soit de 4 lignes, une seule ligne est affichée car le jeu de résultats total est de 5. Les premiers sont ignorés et le cinquième est affiché.
{"fullName":"Lorenzo Phil","tagNum":["17657806240001","17657806340001"],
"flt_time":"2019-03-12T16:05:00Z"}
Résultats du regroupement
Utilisez la clause GROUP BY pour regrouper les résultats par une ou plusieurs colonnes de table. En général, une clause GROUP BY est utilisée conjointement avec une expression d'agrégation telle que COUNT, SUM et AVG.
SELECT bag.confNo,
count(bag.bagInfo) AS TOTAL_BAGS
FROM BaggageInfo bag
GROUP BY bag.confNo
Explication : Chaque passager a un code de réservation (confNo
). Un passager peut avoir plus d'un bagage. Ici, vous regroupez les données en fonction du code de réservation et vous obtenez le décompte du tableau bagInfo
qui donne le nombre de sacs par réservation.
{"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}
SELECT $flt_src as SOURCE,
count(*) as COUNT
FROM BaggageInfo $bag,
$bag.bagInfo.flightLegs[0].fltRouteSrc $flt_src
GROUP BY $flt_src
Explication : Vous voulez obtenir le nombre total de bagages provenant de chaque aéroport. Cependant, vous ne voulez pas considérer les aéroports qui font partie du transit. Vous regroupez donc les données avec les valeurs de source de vol du premier enregistrement du tableau flightLegs
(le premier enregistrement étant la source). Vous déterminez ensuite le nombre de bagages.
{"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}
Résultats agrégés
Utilisez les fonctions d'agrégation et de séquence intégrées pour rechercher des informations telles qu'un nombre, une somme, une moyenne, un minimum ou un maximum.
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
Explication : Dans une application de suivi des bagages de compagnie aérienne, vous pouvez obtenir le nombre total de bagages enregistrés qui sont estimés arriver à un aéroport et à une heure particuliers. Pour chaque étape de vol, le champ estimatedArrival
dans le tableau flightLegs
du tableau BaggageInfo
contient l'heure d'arrivée des bagages enregistrés et le champ fltRouteDest
contient le code de l'aéroport de destination. Dans la requête ci-dessus, pour déterminer le nombre total de bagages enregistrés arrivant à l'aéroport LAX à un moment donné, vous devez d'abord regrouper les données avec la valeur de l'heure d'arrivée estimée en utilisant la clause GROUP BY. Dans le groupe, vous sélectionnez uniquement les enregistrements dont l'aéroport de destination est LAX. Vous déterminez ensuite le nombre d'ensembles de blocs contigus pour les lignes résultantes à l'aide de la fonction de compt
Ici, vous pouvez comparer les dates au format chaîne au format ISO-8601 en raison de l'ordre de tri naturel des chaînes sans avoir à les convertir en types de données d'horodatage. $bag.bagInfo.flightLegs.estimatedArrival
et $bag.bagInfo.flightLegs.fltRouteDest
sont des séquences. Comme l'expression de comparaison '=' ne peut pas fonctionner sur des séquences de plus d'un élément, l'opérateur de comparaison de séquences '=any' est utilisé à la place pour comparer les champs estimatedArrival
et fltRouteDest
.
{"ARRIVALDATE":"2019-02-01T11:00:00Z","COUNT":2}
SELECT fullName,
b.baginfo[0].routing,
size(baginfo) AS BAGS,
CASE
WHEN seq_count(b.bagInfo[0].flightLegs.flightNo) = 1
THEN "You have one flight to catch"
WHEN seq_count(b.bagInfo[0].flightLegs.flightNo) = 2
THEN "You have two flights to catch"
WHEN seq_count(b.bagInfo[0].flightLegs.flightNo) = 3
THEN "You have three flights to catch"
ELSE "You do not have any travel listed today"
END AS FlightInfo
FROM BaggageInfo b
WHERE ticketNo = 1762320369957
Explication : Dans l'application de suivi des bagages de la compagnie aérienne, il est utile d'afficher un message de consultation rapide concernant le nombre de vols, le nombre de bagages enregistrés et les détails d'acheminement d'un voyage à venir pour un passager. Le tableau bagInfo
contient les détails des bagages enregistrés du passager. La taille du tableau bagInfo
détermine le nombre de bagages enregistrés par passager. Le tableau flightLegs
dans bagInfo
inclut les détails de vol correspondant à chaque portion de route. Le champ de routage comprend les codes d'aéroport de tous les fragments de voyage. Vous pouvez déterminer le nombre de vols en comptant les champs flightNo
dans le tableau flightLegs
. Si un passager a plusieurs bagages enregistrés, il y aura plus d'un élément dans le tableau bagInfo
, un pour chaque sac. Dans de tels cas, le tableau flightLegs
dans tous les éléments du champ bagInfo
des données d'un passager contiendra les mêmes valeurs. En effet, la destination de tous les bagages enregistrés pour un passager sera la même. Lors du comptage des champs flightNo
, vous devez prendre en compte un seul élément du tableau bagInfo
pour éviter la duplication des résultats. Dans cette interrogation, vous ne prenez en compte que le premier élément, à savoir bagInfo[0]
. Comme le tableau flightLegs
comporte un champ flightNo
pour chaque fragment de voyage, il s'agit d'une séquence et vous déterminez le nombre de champs flightNo
par passager à l'aide de la fonction seq_count
.
Vous utilisez l'instruction CASE pour introduire différents messages en fonction du nombre de vols. Pour faciliter l'utilisation, seules trois transits sont pris en compte dans la requête.
{"fullName":"Lorenzo Phil","routing":"SFO/IST/ATH/JTR","BAGS":2,"FlightInfo":"You have three flights to catch"}
Exemples utilisant l'API QueryRequest
Vous pouvez utiliser l'API QueryRequest
pour regrouper et trier des données et les extraire d'une table NoSQL.
Pour exécuter une interrogation, utilisez l'API NoSQLHandle.query()
.
//Fetch rows from the table
private static void fetchRows(NoSQLHandle handle,String sqlstmt) throws Exception {
try (
QueryRequest queryRequest = new QueryRequest().setStatement(sqlstmt);
QueryIterableResult results = handle.queryIterable(queryRequest)){
for (MapValue res : results) {
System.out.println("\t" + res);
}
}
}
String orderby_stmt="SELECT bag.fullName, bag.bagInfo[].tagNum,bag.bagInfo[].lastSeenTimeGmt FROM BaggageInfo bag "+
"WHERE bag.bagInfo[].lastSeenStation=any \"MEL\" ORDER BY bag.bagInfo[].lastSeenTimeGmt DESC";
System.out.println("Using ORDER BY to sort data ");
fetchRows(handle,orderby_stmt);
String sortlimit_stmt="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";
System.out.println("Using ORDER BY and LIMIT to sort and limit data ");
fetchRows(handle,sortlimit_stmt);
String groupsortlimit_stmt="SELECT $flt_src as SOURCE,count(*) as COUNT FROM BaggageInfo $bag, "+
"$bag.bagInfo.flightLegs[0].fltRouteSrc $flt_src GROUP BY $flt_src";
System.out.println("Using GROUP BY, ORDER BY and LIMIT to group, sort and limit data ");
fetchRows(handle,groupsortlimit_stmt);
Pour exécuter votre interrogation, utilisez la méthode borneo.NoSQLHandle.query()
.
# Fetch data from the table
def fetch_data(handle,sqlstmt):
request = QueryRequest().set_statement(sqlstmt)
print('Query results for: ' + sqlstmt)
result = handle.query(request)
for r in result.get_results():
print('\t' + str(r))
orderby_stmt = '''SELECT bag.fullName, bag.bagInfo[].tagNum,bag.bagInfo[].lastSeenTimeGmt FROM BaggageInfo bag
WHERE bag.bagInfo[].lastSeenStation=any \"MEL\" ORDER BY bag.bagInfo[].lastSeenTimeGmt DESC'''
print('Using ORDER BY to sort data:')
fetch_data(handle,orderby_stmt)
sortlimit_stmt = '''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'''
print('Using ORDER BY and LIMIT to sort and limit data:')
fetch_data(handle,sortlimit_stmt)
groupsortlimit_stmt = '''SELECT $flt_src as SOURCE, count(*) as COUNT FROM BaggageInfo $bag,
$bag.bagInfo.flightLegs[0].fltRouteSrc $flt_src GROUP BY $flt_src'''
print('Using GROUP BY, ORDER BY and LIMIT to group, sort and limit data:')
fetch_data(handle,groupsortlimit_stmt)
Pour exécuter une interrogation, utilisez la fonction Client.Query
.
//fetch data from the table
func fetchData(client *nosqldb.Client, err error, tableName string, querystmt string)(){
prepReq := &nosqldb.PrepareRequest{
Statement: querystmt,
}
prepRes, err := client.Prepare(prepReq)
if err != nil {
fmt.Printf("Prepare failed: %v\n", err)
return
}
queryReq := &nosqldb.QueryRequest{
PreparedStatement: &prepRes.PreparedStatement, }
var results []*types.MapValue
for {
queryRes, err := client.Query(queryReq)
if err != nil {
fmt.Printf("Query failed: %v\n", err)
return
}
res, err := queryRes.GetResults()
if err != nil {
fmt.Printf("GetResults() failed: %v\n", err)
return
}
results = append(results, res...)
if queryReq.IsDone() {
break
}
}
for i, r := range results {
fmt.Printf("\t%d: %s\n", i+1, jsonutil.AsJSON(r.Map()))
}
}
orderby_stmt := `SELECT bag.fullName, bag.bagInfo[].tagNum,bag.bagInfo[].lastSeenTimeGmt FROM BaggageInfo bag
WHERE bag.bagInfo[].lastSeenStation=any "MEL" ORDER BY bag.bagInfo[].lastSeenTimeGmt DESC`
fmt.Printf("Using ORDER BY to sort data::\n")
fetchData(client, err,tableName,orderby_stmt)
sortlimit_stmt := `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`
fmt.Printf("Using ORDER BY and LIMIT to sort and limit data::\n")
fetchData(client, err,tableName,sortlimit_stmt)
groupsortlimit_stmt := `SELECT $flt_src as SOURCE, count(*) as COUNT FROM BaggageInfo $bag,
$bag.bagInfo.flightLegs[0].fltRouteSrc $flt_src GROUP BY $flt_src`
fmt.Printf("Using GROUP BY, ORDER BY and LIMIT to group, sort and limit data::\n")
fetchData(client, err,tableName,groupsortlimit_stmt)
Pour exécuter une interrogation, utilisez la méthode query
.
//fetches data from the table
async function fetchData(handle,querystmt) {
const opt = {};
try {
do {
const result = await handle.query(querystmt, opt);
for(let row of result.rows) {
console.log(' %O', row);
}
opt.continuationKey = result.continuationKey;
} while(opt.continuationKey);
} catch(error) {
console.error(' Error: ' + error.message);
}
}
interface StreamInt {
acct_Id: Integer;
profile_name: String;
account_expiry: TIMESTAMP;
acct_data: JSON;
}
/* fetches data from the table */
async function fetchData(handle: NoSQLClient,querystmt: string) {
const opt = {};
try {
do {
const result = await handle.query<StreamInt>(querystmt, opt);
for(let row of result.rows) {
console.log(' %O', row);
}
opt.continuationKey = result.continuationKey;
} while(opt.continuationKey);
} catch(error) {
console.error(' Error: ' + error.message);
}
}
const orderby_stmt = `SELECT bag.fullName, bag.bagInfo[].tagNum,bag.bagInfo[].lastSeenTimeGmt FROM BaggageInfo bag
WHERE bag.bagInfo[].lastSeenStation=any \"MEL\" ORDER BY bag.bagInfo[].lastSeenTimeGmt DESC`
console.log("Using ORDER BY to sort data");
await fetchData(handle,orderby_stmt);
const sortlimit_stmt = `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`
console.log("Using ORDER BY and LIMIT to sort and limit data");
await fetchData(handle,sortlimit_stmt);
const groupsortlimit_stmt = `SELECT $flt_src as SOURCE, count(*) as COUNT FROM BaggageInfo $bag,
$bag.bagInfo.flightLegs[0].fltRouteSrc $flt_src GROUP BY $flt_src`
console.log("Using GROUP BY, ORDER BY and LIMIT to group, sort and limit data");
await fetchData(handle,groupsortlimit_stmt);
Pour exécuter une interrogation, vous pouvez appeler la méthode QueryAsync
ou appeler la méthode GetQueryAsyncEnumerable
et effectuer une itération sur l'énumération asynchrone obtenue.
private static async Task fetchData(NoSQLClient client,String querystmt){
var queryEnumerable = client.GetQueryAsyncEnumerable(querystmt);
await DoQuery(queryEnumerable);
}
private static async Task DoQuery(IAsyncEnumerable<QueryResult<RecordValue>> queryEnumerable){
Console.WriteLine(" Query results:");
await foreach (var result in queryEnumerable) {
foreach (var row in result.Rows)
{
Console.WriteLine();
Console.WriteLine(row.ToJsonString());
}
}
}
private const string orderby_stmt =@"SELECT bag.fullName, bag.bagInfo[].tagNum,bag.bagInfo[].lastSeenTimeGmt
FROM BaggageInfo bag WHERE bag.bagInfo[].lastSeenStation=any ""MEL""
ORDER BY bag.bagInfo[].lastSeenTimeGmt DESC";
Console.WriteLine("\nUsing ORDER BY to sort data!");
await fetchData(client,orderby_stmt);
private const string sortlimit_stmt =@"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";
Console.WriteLine("\nUsing ORDER BY and LIMIT to sort and limit data!");
await fetchData(client,sortlimit_stmt);
private const string groupsortlimit_stmt =@"SELECT $flt_src as SOURCE, count(*) as COUNT FROM BaggageInfo $bag,
$bag.bagInfo.flightLegs[0].fltRouteSrc $flt_src GROUP BY $flt_src" ;
Console.WriteLine("\nUsing GROUP BY, ORDER BY and LIMIT to group, sort and limit data:");
await fetchData(client,groupsortlimit_stmt);
Tri du regroupement et de la limitation des données