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. 您可以使用的標準彙總函式為 AllAnyAverage 、、、 CountLongCount Max MinSum 函數。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 子句開始查詢,則結果會是單一值,這是子句中指定之彙總函式的結果 IntoIf 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 ,則查詢會傳回具有個別屬性的單一類型,以參考子句中每個彙總函式的結果 IntoIf 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 子句包含為查詢中的其他子句,在查詢集合中傳回的型別將會有個別的屬性來參考子句中每個彙總函式的結果 IntoIf 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

以下是可搭配子句使用的標準彙總函式 AggregateThe 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)

AverageAverage

計算集合中所有專案的平均值,或計算集合中所有元素的提供運算式。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)

CountCount

計算集合中的元素數目。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 By Group JoinRefers to query results that are grouped as a result of a Group By or Group Join clause. Group函數只在 Into 或子句的子句中有效 Group By Group JoinThe Group function is valid only in the Into clause of a Group By or Group Join clause. 如需詳細資訊和範例,請參閱Group By 子句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. 擴充方法有兩個多載 MedianThere 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 會使用參考 lambda 運算式的第二個參數, Func(Of T, Double) 以投影類型的值(從集合)做為類型的對應值 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. 然後,它會將中間值的計算委派給方法的其他多載 MedianIt 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 呼叫方法的泛型多載 MedianThe 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