Aggregate 子句 (Visual Basic)Aggregate Clause (Visual Basic)

適用於一或多個彙總函式集合。Applies one or more aggregate functions to a collection.

語法Syntax

Aggregate element [As type] In collection _  
  [, element2 [As type2] In collection2, [...]]  
  [ clause ]  
  Into expressionList  

組件Parts

詞彙Term 定義Definition
element 必要項。Required. 變數,可用來逐一查看集合的元素。Variable used to iterate through the elements of the collection.
type 選擇性。Optional. element 的類型。The type of element. 如果未不指定任何類型,類型element推斷從collectionIf no type is specified, the type of element is inferred from collection.
collection 必要項。Required. 指的是要處理的集合。Refers to the collection to operate on.
clause 選擇性。Optional. 一或多個查詢子句,例如Where子句,來限定要套用的彙總子句或子句的查詢結果。One or more query clauses, such as a Where clause, to refine the query result to apply the aggregate clause or clauses to.
expressionList 必要項。Required. 一或多個以逗點分隔運算式可識別要套用至集合的彙總函式。One or more comma-delimited expressions that identify an aggregate function to apply to the collection. 您可以將別名套用至指定成員名稱的查詢結果的彙總函式。You can apply an alias to an aggregate function to specify a member name for the query result. 如果未不提供任何別名,則會使用彙總函式的名稱。If no alias is supplied, the name of the aggregate function is used. 如需範例,請參閱本主題稍後的彙總函式的相關章節。For examples, see the section about aggregate functions later in this topic.

備註Remarks

Aggregate子句可以用來在您的查詢中包含彙總函式。The Aggregate clause can be used to include aggregate functions in your queries. 彙總函式會執行檢查及計算值的集合,並傳回單一值。Aggregate functions perform checks and computations over a set of values and return a single value. 您可以使用查詢結果型別的成員,以存取計算的值。You can access the computed value by using a member of the query result type. 您可以使用標準彙總函式AllAnyAverageCountLongCountMaxMin,以及Sum函式。The standard aggregate functions that you can use are the All, Any, Average, Count, LongCount, Max, Min, and Sum functions. 這些函式是熟悉的開發人員已熟悉 SQL 中的彙總。These functions are familiar to developers who are familiar with aggregates in SQL. 它們都是以本主題的下一節所述。They are described in the following section of this topic.

查詢結果中包含的彙總函式的結果為查詢結果型別的欄位。The result of an aggregate function is included in the query result as a field of the query result type. 您可以提供彙總函式的結果,以指定的查詢結果型別,用以保存彙總值的成員名稱的別名。You can supply an alias for the aggregate function result to specify the name of the member of the query result type that will hold the aggregate value. 如果未不提供任何別名,則會使用彙總函式的名稱。If no alias is supplied, the name of the aggregate function is used.

Aggregate子句可以開始查詢,或者它可以包含在查詢中的其他子句。The Aggregate clause can begin a query, or it can be included as an additional clause in a query. 如果Aggregate子句開始查詢,則結果為單一值中指定的彙總函式的結果Into子句。If the Aggregate clause begins a query, the result is a single value that is the result of the aggregate function specified in the Into clause. 如果一個以上的彙總函式中指定Into子句,查詢會傳回個別屬性來參考每個彙總函式的結果的單一類型Into子句。If more than one aggregate function is specified in the Into clause, the query returns a single type with a separate property to reference the result of each aggregate function in the Into clause. 如果Aggregate子句會包含在查詢中的其他子句,查詢集合中傳回的型別就會有不同的屬性來參考每個彙總函式的結果Into子句。If the Aggregate clause is included as an additional clause in a query, the type returned in the query collection will have a separate property to reference the result of each aggregate function in the Into clause.

彙總函式Aggregate Functions

以下是適用於標準彙總函式Aggregate子句。The following are the standard aggregate functions that can be used with the Aggregate clause.

全部All

會傳回true如果在集合中的所有項目符合指定的條件; 否則會傳回falseReturns true if all elements in the collection satisfy a specified condition; otherwise returns false. 以下是一個範例:The following is an example:

Dim customerList1 = Aggregate order In orders
                    Into AllOrdersOver100 = All(order.Total >= 100)

任何Any

會傳回true如果在集合中的任何項目符合指定的條件; 否則會傳回falseReturns true if any element in the collection satisfies a specified condition; otherwise returns false. 以下是一個範例:The following is an example:

Dim customerList2 = From cust In customers
                    Aggregate order In cust.Orders
                    Into AnyOrderOver500 = Any(order.Total >= 500)

平均Average

計算集合中的所有項目的平均值,或計算提供的運算式,針對集合中的所有項目。Computes the average of all elements in the collection, or computes a supplied expression for all elements in the collection. 以下是一個範例:The following is an example:

Dim customerOrderAverage = Aggregate order In orders
                           Into Average(order.Total)

計數Count

計算集合中的項目數。Counts the number of elements in the collection. 您可以提供選擇性Boolean運算式來計算只在集合中符合條件的項目數目。You can supply an optional Boolean expression to count only the number of elements in the collection that satisfy a condition. 以下是一個範例:The following is an example:

Dim customerOrderAfter1996 = From cust In customers
                             Aggregate order In cust.Orders
                             Into Count(order.OrderDate > #12/31/1996#)

群組Group

查詢結果的群組是指Group ByGroup Join子句。Refers to query results that are grouped as a result of a Group By or Group Join clause. Group函式是僅適用於Into子句Group ByGroup Join子句。The Group function is valid only in the Into clause of a Group By or Group Join clause. 如需詳細資訊和範例,請參閱 < 群組的子句Group Join 子句For more information and examples, see Group By Clause and Group Join Clause.

LongCountLongCount

計算集合中的項目數。Counts the number of elements in the collection. 您可以提供選擇性Boolean運算式來計算只在集合中符合條件的項目數目。You can supply an optional Boolean expression to count only the number of elements in the collection that satisfy a condition. 傳回結果做LongReturns the result as a Long. 如需範例,請參閱Count彙總函式。For an example, see the Count aggregate function.

最大Max

從集合中的最大值或是計算集合中的所有項目的而提供的運算式。Computes the maximum value from the collection, or computes a supplied expression for all elements in the collection. 以下是一個範例:The following is an example:

Dim customerMaxOrder = Aggregate order In orders
                       Into MaxOrder = Max(order.Total)

最小Min

計算集合的最小值或計算提供的運算式,針對集合中的所有項目。Computes the minimum value from the collection, or computes a supplied expression for all elements in the collection. 以下是一個範例:The following is an example:

Dim customerMinOrder = From cust In customers
                       Aggregate order In cust.Orders
                       Into MinOrder = Min(order.Total)

SumSum

集合中的所有項目的總和或是計算集合中的所有項目的而提供的運算式。Computes the sum of all elements in the collection, or computes a supplied expression for all elements in the collection. 以下是一個範例:The following is an example:

Dim customerTotals = From cust In customers
                     Aggregate order In cust.Orders
                     Into Sum(order.Total)

範例Example

下列範例示範如何使用Aggregate子句,以套用彙總函式,將查詢結果。The following example shows how to use the Aggregate clause to apply aggregate functions to a query result.

Public Sub AggregateSample()
  Dim customers = GetCustomerList()

  Dim customerOrderTotal =
      From cust In customers
      Aggregate order In cust.Orders
      Into Sum(order.Total), MaxOrder = Max(order.Total),
      MinOrder = Min(order.Total), Avg = Average(order.Total)

  For Each customer In customerOrderTotal
    Console.WriteLine(customer.cust.CompanyName & vbCrLf &
                     vbTab & "Sum = " & customer.Sum & vbCrLf &
                     vbTab & "Min = " & customer.MinOrder & vbCrLf &
                     vbTab & "Max = " & customer.MaxOrder & vbCrLf &
                     vbTab & "Avg = " & customer.Avg.ToString("#.##"))
  Next
End Sub

建立使用者定義彙總函式Creating User-Defined Aggregate Functions

您也可以加入擴充方法,以查詢運算式中包含您自己自訂的彙總函式IEnumerable<T>型別。You can include your own custom aggregate functions in a query expression by adding extension methods to the IEnumerable<T> type. 計算或參考您彙總函式的可列舉集合上的作業,接著可以執行您的自訂方法。Your custom method can then perform a calculation or operation on the enumerable collection that has referenced your aggregate function. 如需擴充方法的詳細資訊,請參閱擴充方法For more information about extension methods, see Extension Methods.

例如,下列範例顯示自訂的彙總函式會計算數字集合的中間值。For example, the following example shows a custom aggregate function that calculates the median value of a collection of numbers. 有兩個多載Median擴充方法。There are two overloads of the Median extension method. 第一個多載接受,做為輸入,型別集合IEnumerable(Of Double)The first overload accepts, as input, a collection of type IEnumerable(Of Double). 如果Median彙總函式會呼叫類型的查詢欄位Double,會呼叫這個方法。If the Median aggregate function is called for a query field of type Double, this method will be called. 第二個多載Median方法可以傳遞任何泛型型別。The second overload of the Median method can be passed any generic type. 泛型多載Median方法會採用第二個參數會參考Func(Of T, Double)投影的類型 (集合) 的值類型的對應值的 lambda 運算式DoubleThe generic overload of the Median method takes a second parameter that references the Func(Of T, Double) lambda expression to project a value for a type (from a collection) as the corresponding value of type Double. 然後它會委派其他多載的中間值的計算Median方法。It then delegates the calculation of the median value to the other overload of the Median method. 如需 Lambda 運算式的詳細資訊,請參閱 Lambda 運算式For more information about lambda expressions, see Lambda Expressions.

Imports System.Runtime.CompilerServices

Module UserDefinedAggregates

    ' Calculate the median value for a collection of type Double.
    <Extension()>
    Function Median(ByVal values As IEnumerable(Of Double)) As Double
        If values.Count = 0 Then
            Throw New InvalidOperationException("Cannot compute median for an empty set.")
        End If

        Dim sortedList = From number In values
                         Order By number

        Dim medianValue As Double

        Dim itemIndex = CInt(Int(sortedList.Count / 2))

        If sortedList.Count Mod 2 = 0 Then
            ' Even number of items in list.
            medianValue = ((sortedList(itemIndex) + sortedList(itemIndex - 1)) / 2)
        Else
            ' Odd number of items in list.
            medianValue = sortedList(itemIndex)
        End If

        Return medianValue
    End Function

    ' "Cast" the collection of generic items as type Double and call the 
    ' Median() method to calculate the median value.
    <Extension()>
    Function Median(Of T)(ByVal values As IEnumerable(Of T),
                          ByVal selector As Func(Of T, Double)) As Double
        Return (From element In values Select selector(element)).Median()
    End Function

End Module

下列範例顯示範例查詢來呼叫Median彙總類型的集合上的函式Integer,和型別集合DoubleThe following example shows sample queries that call the Median aggregate function on a collection of type Integer, and a collection of type Double. 查詢會呼叫Median彙總類型的集合上的函式Double呼叫的多載Median做為輸入,接受型別集合的方式來DoubleThe query that calls the Median aggregate function on the collection of type Double calls the overload of the Median method that accepts, as input, a collection of type Double. 查詢會呼叫Median彙總類型的集合上的函式Integer呼叫的泛型多載Median方法。The query that calls the Median aggregate function on the collection of type Integer calls the generic overload of the Median method.

Module Module1

    Sub Main()
        Dim numbers1 = {1, 2, 3, 4, 5}

        Dim query1 = Aggregate num In numbers1 Into Median(num)

        Console.WriteLine("Median = " & query1)

        Dim numbers2 = {1.9, 2, 8, 4, 5.7, 6, 7.2, 0}

        Dim query2 = Aggregate num In numbers2 Into Median()

        Console.WriteLine("Median = " & query2)
    End Sub

End Module

另請參閱See also