Aggregation Function

Aggregation functions are essential for summarizing data in meaningful ways when creating a measure. These functions operate on a set of values and return a single summarized result.

Supported Aggregation Functions

ReOrc supports the following aggregation functions:

1. SUM

Calculates the total sum of a numeric field.

Example:

SUM(revenue)

Returns the total revenue across all records.

2. COUNT

Counts the number of records.

Example:

COUNT(order_id)

Returns the total number of orders.

3. COUNT DISTINCT

Counts the number of unique values in a field.

Example:

COUNT_DISTINCT(customer_id)

Returns the number of unique customers.

4. AVG

Computes the average value of a numeric field.

Example:

AVG(order_amount)

Returns the average order amount.

5. MIN

Finds the smallest value in a field.

Example:

MIN(price)

Returns the lowest product price.

6. MAX

Finds the largest value in a field.

Example:

MAX(price)

Returns the highest product price.

7. CUSTOM

Allows users to define their own aggregation logic using formulas.

Example:

SUM(revenue) / COUNT(order_id)

Calculates the average revenue per order.

Usage in Measures

When defining a measure, an aggregation function must be specified to determine how the data should be summarized. Below is an example of how an aggregation function is used to define a measure:

measures:
  - name: total_revenue
    label: "Total Revenue"
    type: number
    aggregation: SUM
    expression: "SUM(revenue)"

This measure calculates the total revenue by summing up all revenue values.