Interface IAggregationCommand

Commands for specifying predefined column aggregation functions (Average, Count, CountDistinct, CountRows, First, Last, Max, Min and Sum) and columns for use with several AggregateTransformFactory members.

Also see the examples in Dataflow Aggregations.

Inherited Members

IFluentInterface.GetType()

IFluentInterface.GetHashCode()

IFluentInterface.ToString()

IFluentInterface.Equals(Object)

Namespace: actionETL

Assembly: actionETL.dll

Syntax

public interface IAggregationCommand : IFluentInterface

Remarks

The aggregation functions follow the SQL-92 standard:

Aggregation without grouping will always produce one output row, including for an empty input set
On an empty input set, Count aggregations return 0, whereas the other aggregations return null
With grouping, an empty input set will not produce any output rows
Count aggregations support nullable input column types. Non-Count aggregations support nullable input column value types if both input and output columns are nullable.
null input column values are not included in aggregations (CountRows however doesn't look at input column values at all)
If the aggregation result type (usually the same as the input column type), can be implicitly cast to the output column type, then they can have different types, e.g. long to double
Sum and Average additions are checked for overflow, Count additions are not

Methods

Average(String)

Calculates the average of a numerical column. Any nulls are ignored.

Declaration

IAggregationCommand Average(string columnName)

Parameters

Type	Name	Description
String	columnName	Name of the input and output columns.

Returns

Type	Description
IAggregationCommand	The instance itself, for chaining multiple commands together.

Exceptions

Type	Condition
ArgumentException	`columnName` - Cannot output to the same column twice. Could not find column name in type. Input and output columns must both be either nullable or not nullable.
ArgumentNullException	`columnName`

Average(String, String)

Calculates the average of a numerical column. Any nulls are ignored.

Declaration

IAggregationCommand Average(string inputColumnName, string outputColumnName)

Parameters

Type	Name	Description
String	inputColumnName	Name of the input column.
String	outputColumnName	Name of the output column.

Returns

Type	Description
IAggregationCommand	The instance itself, for chaining multiple commands together.

Exceptions

Type	Condition
ArgumentException	`outputColumnName` - Cannot output to the same column twice. Could not find column name in type. Cannot implicitly cast from input type to output type. `inputColumnName` and `outputColumnName` must both be either nullable or not nullable.
ArgumentNullException	`inputColumnName`

Count(String)

Calculates the number of non-null values in a column.

Declaration

IAggregationCommand Count(string columnName)

Parameters

Type	Name	Description
String	columnName	Name of the input and output columns.

Returns

Type	Description
IAggregationCommand	The instance itself, for chaining multiple commands together.

Exceptions

Type	Condition
ArgumentException	`columnName` - Cannot output to the same column twice. Could not find column name in type. Input and output columns must both be either nullable or not nullable.
ArgumentNullException	`columnName`

Count(String, String)

Calculates the number of non-null values in a column.

Declaration

IAggregationCommand Count(string inputColumnName, string outputColumnName)

Parameters

Type	Name	Description
String	inputColumnName	Name of the input column.
String	outputColumnName	Name of the output column.

Returns

Type	Description
IAggregationCommand	The instance itself, for chaining multiple commands together.

Exceptions

Type	Condition
ArgumentException	`outputColumnName` - Cannot output to the same column twice. Could not find column name in type. Cannot implicitly cast from input type to output type. `inputColumnName` and `outputColumnName` must both be either nullable or not nullable.
ArgumentNullException	`inputColumnName`

CountDistinct(String)

Calculates the number of distinct non-null values in a column.

Note that by default on .NET Framework, maximum array size is 2GB, which in turn with a 64-bit application limits the number of unique groupings to a maximum of 47.9 million, and the CountDistinct(String) number of distinct values per column to a maximum of just over 89 million. You can remove these limits by enabling support for larger arrays, as described in <gcAllowVeryLargeObjects> Element.

.NET 6+ on the other hand supports >2GB arrays by default.

Declaration

IAggregationCommand CountDistinct(string columnName)

Parameters

Type	Name	Description
String	columnName	Name of the input and output columns.

Returns

Type	Description
IAggregationCommand	The instance itself, for chaining multiple commands together.

Exceptions

Type	Condition
ArgumentException	`columnName` - Cannot output to the same column twice. Could not find column name in type. Input and output columns must both be either nullable or not nullable.
ArgumentNullException	`columnName`

CountDistinct(String, String)

Calculates the number of distinct non-null values in a column.

Note that by default on .NET Framework, maximum array size is 2GB, which in turn with a 64-bit application limits the number of unique groupings to a maximum of 47.9 million, and the CountDistinct(String, String) number of distinct values per column to a maximum of just over 89 million. You can remove these limits by enabling support for larger arrays, as described in <gcAllowVeryLargeObjects> Element.

.NET 6+ on the other hand supports >2GB arrays by default.

Declaration

IAggregationCommand CountDistinct(string inputColumnName, string outputColumnName)

Parameters

Type	Name	Description
String	inputColumnName	Name of the input column.
String	outputColumnName	Name of the output column.

Returns

Type	Description
IAggregationCommand	The instance itself, for chaining multiple commands together.

Exceptions

Type	Condition
ArgumentException	`outputColumnName` - Cannot output to the same column twice. Could not find column name in type. Cannot implicitly cast from input type to output type. `inputColumnName` and `outputColumnName` must both be either nullable or not nullable.
ArgumentNullException	`inputColumnName`

CountRows(String)

Calculates the number of incoming rows. Note that this can include rows with null column values.

Also see RowsTaken and RowsSent.

Declaration

IAggregationCommand CountRows(string outputColumnName)

Parameters

Type	Name	Description
String	outputColumnName	Name of the output column.

Returns

Type	Description
IAggregationCommand	The instance itself, for chaining multiple commands together.

Exceptions

Type	Condition
ArgumentException	`outputColumnName` - Cannot output to the same column twice. Could not find column name in type.
ArgumentNullException	`outputColumnName`

First(String)

Sets the column value from the first row.

Declaration

IAggregationCommand First(string columnName)

Parameters

Type	Name	Description
String	columnName	Name of the input and output columns.

Returns

Type	Description
IAggregationCommand	The instance itself, for chaining multiple commands together.

Exceptions

Type	Condition
ArgumentException	`columnName` - Cannot output to the same column twice. Could not find column name in type. Input and output columns must both be either nullable or not nullable.
ArgumentNullException	`columnName`

First(String, String)

Sets the column value from the first row.

Declaration

IAggregationCommand First(string inputColumnName, string outputColumnName)

Parameters

Type	Name	Description
String	inputColumnName	Name of the input column.
String	outputColumnName	Name of the output column.

Returns

Type	Description
IAggregationCommand	The instance itself, for chaining multiple commands together.

Exceptions

Type	Condition
ArgumentException	`outputColumnName` - Cannot output to the same column twice. Could not find column name in type. Cannot implicitly cast from input type to output type. `inputColumnName` and `outputColumnName` must both be either nullable or not nullable.
ArgumentNullException	`inputColumnName`

Last(String)

Sets the column value from the last row.

Declaration

IAggregationCommand Last(string columnName)

Parameters

Type	Name	Description
String	columnName	Name of the input and output columns.

Returns

Type	Description
IAggregationCommand	The instance itself, for chaining multiple commands together.

Exceptions

Type	Condition
ArgumentException	`columnName` - Cannot output to the same column twice. Could not find column name in type. Input and output columns must both be either nullable or not nullable.
ArgumentNullException	`columnName`

Last(String, String)

Sets the column value from the last row.

Declaration

IAggregationCommand Last(string inputColumnName, string outputColumnName)

Parameters

Type	Name	Description
String	inputColumnName	Name of the input column.
String	outputColumnName	Name of the output column.

Returns

Type	Description
IAggregationCommand	The instance itself, for chaining multiple commands together.

Exceptions

Type	Condition
ArgumentException	`outputColumnName` - Cannot output to the same column twice. Could not find column name in type. Cannot implicitly cast from input type to output type. `inputColumnName` and `outputColumnName` must both be either nullable or not nullable.
ArgumentNullException	`inputColumnName`

Max(String)

Calculates the maximum value in a numerical column. Any nulls are ignored.

Declaration

IAggregationCommand Max(string columnName)

Parameters

Type	Name	Description
String	columnName	Name of the input and output columns.

Returns

Type	Description
IAggregationCommand	The instance itself, for chaining multiple commands together.

Exceptions

Type	Condition
ArgumentException	`columnName` - Cannot output to the same column twice. Could not find column name in type. Input and output columns must both be either nullable or not nullable.
ArgumentNullException	`columnName`

Max(String, String)

Calculates the maximum value in a numerical column. Any nulls are ignored.

Declaration

IAggregationCommand Max(string inputColumnName, string outputColumnName)

Parameters

Type	Name	Description
String	inputColumnName	Name of the input column.
String	outputColumnName	Name of the output column.

Returns

Type	Description
IAggregationCommand	The instance itself, for chaining multiple commands together.

Exceptions

Type	Condition
ArgumentException	`outputColumnName` - Cannot output to the same column twice. Could not find column name in type. Cannot implicitly cast from input type to output type. `inputColumnName` and `outputColumnName` must both be either nullable or not nullable.
ArgumentNullException	`inputColumnName`

Min(String)

Calculates the minimum value in a numerical column. Any nulls are ignored.

Declaration

IAggregationCommand Min(string columnName)

Parameters

Type	Name	Description
String	columnName	Name of the input and output columns.

Returns

Type	Description
IAggregationCommand	The instance itself, for chaining multiple commands together.

Exceptions

Type	Condition
ArgumentException	`columnName` - Cannot output to the same column twice. Could not find column name in type. Input and output columns must both be either nullable or not nullable.
ArgumentNullException	`columnName`

Min(String, String)

Calculates the minimum value in a numerical column. Any nulls are ignored.

Declaration

IAggregationCommand Min(string inputColumnName, string outputColumnName)

Parameters

Type	Name	Description
String	inputColumnName	Name of the input column.
String	outputColumnName	Name of the output column.

Returns

Type	Description
IAggregationCommand	The instance itself, for chaining multiple commands together.

Exceptions

Type	Condition
ArgumentException	`outputColumnName` - Cannot output to the same column twice. Could not find column name in type. Cannot implicitly cast from input type to output type. `inputColumnName` and `outputColumnName` must both be either nullable or not nullable.
ArgumentNullException	`inputColumnName`

Sum(String)

Calculates the sum of a numerical column. Any nulls are ignored.

Declaration

IAggregationCommand Sum(string columnName)

Parameters

Type	Name	Description
String	columnName	Name of the input and output columns.

Returns

Type	Description
IAggregationCommand	The instance itself, for chaining multiple commands together.

Exceptions

Type	Condition
ArgumentException	`columnName` - Cannot output to the same column twice. Could not find column name in type. Input and output columns must both be either nullable or not nullable.
ArgumentNullException	`columnName`

Sum(String, String)

Calculates the sum of a numerical column. Any nulls are ignored.

Declaration

IAggregationCommand Sum(string inputColumnName, string outputColumnName)

Parameters

Type	Name	Description
String	inputColumnName	Name of the input column.
String	outputColumnName	Name of the output column.

Returns

Type	Description
IAggregationCommand	The instance itself, for chaining multiple commands together.

Exceptions

Type	Condition
ArgumentException	`outputColumnName` - Cannot output to the same column twice. Could not find column name in type. Cannot implicitly cast from input type to output type. `inputColumnName` and `outputColumnName` must both be either nullable or not nullable.
ArgumentNullException	`inputColumnName`