对分组和限制数据进行排序
如果要随示例一起执行操作,请参见以查看示例数据并使用脚本加载示例数据进行测试。这些脚本将创建示例中使用的表,并将数据加载到表中。
如果要随示例一起操作,请参阅用于运行查询的示例数据以查看示例数据,并了解如何使用 OCI 控制台创建示例表并使用 JSON 文件加载数据。
排序结果
使用 ORDER BY 子句可按任何列(主键或非主键)对结果进行排序。
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"}
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 的一个很好的用例/示例是结果的应用程序分页。例如,假设您的应用程序希望每页显示 4 个结果。可以使用限制和偏移在应用程序中实施无状态分页。如果每页显示 n 个(例如 4 个)结果,则将显示第 m 页(例如 2 个)的结果,在此示例中,偏移量将为 (n*m-1),即 4,限制为 n(此处为 4 个)。
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"}
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
表中的数据,并根据上次看到的时间对结果进行排序。使用无嵌套数组来展平数据。您需要显示第二页的内容,因此设置 OFFSET 4。尽管限制为 4 行,但仅显示一行,因为总结果集仅为 5。将跳过前几项,显示第五项。
{"fullName":"Lorenzo Phil","tagNum":["17657806240001","17657806340001"],
"flt_time":"2019-03-12T16:05:00Z"}
分组结果
使用 GROUP BY 子句可以按一个或多个表列对结果进行分组。通常,GROUP BY 子句与聚合表达式(如 COUNT、SUM 和 AVG)结合使用。
SELECT bag.confNo,
count(bag.bagInfo) AS TOTAL_BAGS
FROM BaggageInfo bag
GROUP BY bag.confNo
解释:每位乘客都有一个预订代码 (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}
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}
累积结果
使用内置的聚合和序列聚合函数查找诸如计数、总和、平均值、最小值或最大值之类的信息。
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}
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
解释:在航空公司行李跟踪应用程序中,显示有关乘客即将到来的旅行的航班数量、托运行李数量和路线详细信息的快速查找消息很有帮助。bagInfo
阵列保存乘客的已检查包详细信息。bagInfo
数组的大小确定每位乘客的托运行李数量。bagInfo
中的 flightLegs
阵列包括与每个行程相对应的飞行详细信息。路由字段包括所有旅行碎片的机场代码。您可以通过计算 flightLegs
数组中的 flightNo
字段来确定航班数。如果乘客有多个托运行李,则 bagInfo
阵列中将有多个元素,每个包对应一个元素。在这种情况下,乘客数据 bagInfo
字段的所有元素中的 flightLegs
数组将包含相同的值。这是因为乘客的所有托运行李的目的地将是相同的。计算 flightNo
字段时,必须仅考虑 bagInfo
数组的一个元素,以避免结果重复。在此查询中,仅考虑第一个元素,即 bagInfo[0]
。由于 flightLegs
数组具有每个旅行片段的 flightNo
字段,因此它是一个序列,您可以使用 seq_count
函数确定每位乘客的 flightNo
字段计数。
使用 CASE 语句可以根据航班计数引入不同的消息。为了便于使用,查询中仅考虑三个传输。
{"fullName":"Lorenzo Phil","routing":"SFO/IST/ATH/JTR","BAGS":2,"FlightInfo":"You have three flights to catch"}
使用 QueryRequest API 的示例
可以使用 QueryRequest
API 对数据进行分组和排序,还可以从 NoSQL 表中提取数据。
要执行查询,请使用 NoSQLHandle.query()
API。
//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);
要执行查询,请使用 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)
要执行查询,请使用 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)
要执行查询,请使用 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);
要执行查询,可以调用 QueryAsync
方法或调用 GetQueryAsyncEnumerable
方法并迭代生成的异步可枚举。
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);