Ordinamento dei raggruppamenti e limitazione dei dati
Se si desidera seguire gli esempi, vedere per visualizzare i dati di esempio e utilizzare gli script per caricare i dati di esempio per i test. Gli script creano le tabelle utilizzate negli esempi e caricano i dati nelle tabelle.
Se si desidera seguire gli esempi, vedere Dati di esempio per eseguire query per visualizzare dati di esempio e imparare a utilizzare OCI Console per creare le tabelle di esempio e caricare i dati utilizzando i file JSON.
Argomenti correlati
Risultati ordine
Utilizzare la clausola ORDER BY per ordinare i risultati in base a qualsiasi colonna, chiave primaria o chiave non primaria.
SELECT bag.ticketNo, bag.fullName
FROM BaggageInfo bag
ORDER BY bag.fullName
Spiegazione: si sta ordinando il numero di biglietto dei passeggeri nello schema BaggageInfo
in base al nome completo dei passeggeri in ordine crescente.
{"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
vengono filtrati in base all'ultima stazione visualizzata e i risultati filtrati vengono ordinati in base all'ultima ora visualizzata e al nome completo dei passeggeri in ordine decrescente. A tale scopo, utilizzare la clausola ORDER BY.
Nota
È possibile utilizzare più colonne per ordinare l'output della query.{"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"}
Risultati limite e offset
Utilizzare la clausola LIMIT
per limitare il numero di risultati restituiti da un'istruzione SELECT. Ad esempio, se in una tabella sono presenti 1000 righe, limitare il numero di righe da restituire specificando un valore LIMIT. Si consiglia di utilizzare LIMIT e OFFSET con una clausola ORDER BY. Altrimenti, i risultati vengono restituiti in ordine casuale, producendo risultati imprevedibili.
Un buon caso d'uso / esempio di utilizzo di LIMIT e OFFSET è il paging dell'applicazione dei risultati. Si supponga, ad esempio, che l'applicazione desideri visualizzare 4 risultati per pagina. È possibile utilizzare il limite e l'offset per implementare il paging senza conservazione dello stato nell'applicazione. Se si stanno mostrando n (ad esempio 4 ) risultati per pagina, quindi i risultati per la pagina m (ad esempio 2) vengono visualizzati, quindi offset sarebbe (n * m-1) che è 4 in questo esempio e il limite sarebbe n (che è 4 qui).
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
Spiegazione: i dati nella tabella BaggageInfo
vengono filtrati in base all'ultima stazione visualizzata e il risultato viene ordinato in base all'ultima ora visualizzata. Utilizzare un array non nido per appiattire i dati. L'array bagInfo
viene appiattito e l'ora dell'ultima visualizzazione viene recuperata. È sufficiente visualizzare le prime 4 righe del set di risultati.
{"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
Spiegazione: i dati nella tabella BaggageInfo
vengono filtrati in base all'ultima stazione visualizzata e il risultato viene ordinato in base all'ultima ora visualizzata. Utilizzare un array non nido per appiattire i dati. È necessario visualizzare il contenuto della seconda pagina, in modo da impostare un OFFSET 4. Anche se si LIMITA a 4 righe, viene visualizzata una sola riga poiché il set di risultati totale è solo 5. I primi vengono saltati e il quinto viene visualizzato.
{"fullName":"Lorenzo Phil","tagNum":["17657806240001","17657806340001"],
"flt_time":"2019-03-12T16:05:00Z"}
Raggruppamento dei risultati
Utilizzare la clausola GROUP BY per raggruppare i risultati in base a una o più colonne di tabella. In genere, una clausola GROUP BY viene utilizzata insieme a un'espressione di aggregazione come COUNT, SUM e AVG.
SELECT bag.confNo,
count(bag.bagInfo) AS TOTAL_BAGS
FROM BaggageInfo bag
GROUP BY bag.confNo
Spiegazione: ogni passeggero dispone di un codice di prenotazione (confNo
). Un passeggero può avere più di un bagaglio. Qui si raggruppano i dati in base al codice di prenotazione e si ottiene il conteggio dell'array bagInfo
che dà il numero di borse per prenotazione.
{"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
Spiegazione: si desidera ottenere il conteggio totale dei bagagli provenienti da ogni aeroporto. Tuttavia, non si vuole prendere in considerazione gli aeroporti che fanno parte del transito. Raggruppare i dati con i valori dell'origine di volo del primo record dell'array flightLegs
( poiché il primo record è l'origine). Si determina quindi il conteggio dei bagagli.
{"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}
Aggregazione dei risultati
Utilizzare le funzioni di aggregazione e sequenza incorporate per trovare informazioni quali un conteggio, una somma, una media, un minimo o un massimo.
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
Spiegazione: in un'applicazione di tracciamento dei bagagli delle compagnie aeree, è possibile ottenere il conteggio totale dei bagagli registrati che si stima arrivino in un determinato aeroporto e orario. Per ogni tappa del volo, il campo estimatedArrival
nell'array flightLegs
della tabella BaggageInfo
contiene l'ora di arrivo dei bagagli registrati e il campo fltRouteDest
contiene il codice dell'aeroporto di destinazione. Nella query precedente, per determinare il numero totale di check-out che arrivano all'aeroporto LAX in un determinato momento, è innanzitutto necessario raggruppare i dati con il valore dell'ora di arrivo stimata utilizzando la clausola GROUP BY. Nel gruppo, selezionare solo le righe con l'aeroporto di destinazione LAX. È quindi possibile determinare il conteggio dei sacchetti per le righe risultanti utilizzando la funzione di conteggio.
Qui è possibile confrontare le date in formato stringa in formato ISO-8601 a causa del criterio di ordinamento naturale delle stringhe senza doverle convertire in tipi di dati di indicatore orario. $bag.bagInfo.flightLegs.estimatedArrival
e $bag.bagInfo.flightLegs.fltRouteDest
sono sequenze. Poiché l'espressione di confronto '=' non può operare su sequenze di più elementi, viene utilizzato l'operatore di confronto sequenza '=any' per confrontare i campi estimatedArrival
e 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
Spiegazione: nell'applicazione di tracciamento dei bagagli delle compagnie aeree, è utile visualizzare un messaggio di ricerca rapida relativo al conteggio dei voli, al numero di bagagli registrati e ai dettagli di instradamento di un viaggio imminente per un passeggero. L'array bagInfo
contiene i dettagli del bagaglio registrato del passeggero. La dimensione dell'array bagInfo
determina il numero di bagagli registrati per passeggero. L'array flightLegs
nel bagInfo
include i dettagli del volo corrispondenti a ciascuna tappa del viaggio. Il campo di instradamento include i codici aeroportuali di tutti i frammenti di viaggio. È possibile determinare il numero di voli contando i campi flightNo
nell'array flightLegs
. Se un passeggero ha più di un bagaglio registrato, nell'array bagInfo
sarà presente più di un elemento, uno per ogni sacchetto. In questi casi, l'array flightLegs
in tutti gli elementi del campo bagInfo
dei dati di un passeggero conterrà gli stessi valori. Questo perché la destinazione di tutti i bagagli registrati per un passeggero sarà la stessa. Durante il conteggio dei campi flightNo
, è necessario considerare solo un elemento dell'array bagInfo
per evitare la duplicazione dei risultati. In questa query viene considerato solo il primo elemento, ovvero bagInfo[0]
. Poiché l'array flightLegs
dispone di un campo flightNo
per ogni frammento di viaggio, si tratta di una sequenza e si determina il conteggio dei campi flightNo
per passeggero utilizzando la funzione seq_count
.
Utilizzare l'istruzione CASE per introdurre messaggi diversi in base al conteggio dei voli. Per facilità d'uso, nella query vengono considerati solo tre transiti.
{"fullName":"Lorenzo Phil","routing":"SFO/IST/ATH/JTR","BAGS":2,"FlightInfo":"You have three flights to catch"}
Esempi di utilizzo dell'API QueryRequest
È possibile utilizzare l'API QueryRequest
per raggruppare e ordinare i dati, nonché recuperarli da una tabella NoSQL.
Per eseguire la query, utilizzare 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);
Per eseguire la query, utilizzare il metodo 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)
Per eseguire una query, utilizzare la funzione 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)
Per eseguire una query, utilizzare il metodo 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);
Per eseguire una query, è possibile chiamare il metodo QueryAsync
o chiamare il metodo GetQueryAsyncEnumerable
e ripetere l'iterazione sull'enumerabile asincrono risultante.
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);
Ordinamento dei raggruppamenti e limitazione dei dati