对分组和限制数据进行排序

排序结果

使用 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 的一个很好的用例/示例是结果的应用程序分页。例如，假设您的应用程序希望每页显示 4 个结果。可以使用限制和偏移在应用程序中实施无状态分页。如果每页显示 n 个（例如 4 个）结果，则将显示第 m 页（例如 2 个）的结果，在此示例中，偏移量将为 (n*m-1)，即 4，限制为 n（此处为 4 个）。

示例 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：应用程序可以在页面上显示 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 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）结合使用。

示例 1：显示每个保留的包数。

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}

示例 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}

累积结果

使用内置的聚合和序列聚合函数查找诸如计数、总和、平均值、最小值或最大值之类的信息。

示例 1：查找估计在特定时间到达 LAX 机场的已检查包的总数。

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}

示例 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。

Download the full code GroupSortData.java from the examples here.

 //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() 方法。

Download the full code GroupSortData.py from the examples here.

# 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 函数。

Download the full code GroupSortData.go from the examples here.

 //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 方法。

JavaScript: Download the full code GroupSortData.js from the examples here.

  //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);
   }
}

TypeScript: Download the full code GroupSortData.ts from the examples here.

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 方法并迭代生成的异步可枚举。

Download the full code GroupSortData.cs from the examples here.

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);