Calculate Average of Field Values in an Array
Calculate Average of Field Values in a flat array using the array:reduceRow() function
Query
array:reduceRow("ages[]", var=x, function=avg(x))Introduction
The array:reduceRow() function can be used together
with the aggregate function avg() as the function
argument to calculate the average of field values in a flat array.
In this example, the array:reduceRow() function is
used to calculate the average age of the field
ages and return the result in a
field named _reduceRow._avg.
Example incoming data might look like this:
| ages[0] | ages[1] | ages[2] |
|---|---|---|
| 16 | 32 | 64 |
| 15 | 30 | 45 |
| 1 | 2 | 4 |
| 89 | 57 | 67 |
Step-by-Step
Starting with the source repository events.
- flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 0[\Add Field/] result{{Result Set}} repo --> 0 0 --> result style 0 fill:#ff0000,stroke-width:4px,stroke:#000;logscale
array:reduceRow("ages[]", var=x, function=avg(x))Produces two events, calculating the average value across the ages[] array for each event. The results are placed into the _avg field for each new event.
Event Result set.
Summary and Results
The query is used to calculate averages for a given array for each event
and is a shorthand version of using array:eval()
specifically for processing each event.
Sample output from the incoming example data:
| ages[0] | ages[1] | ages[2] | _avg |
|---|---|---|---|
| 16 | 32 | 64 | 37.333 |
| 15 | 30 | 45 | 30 |
| 1 | 2 | 4 | 2.67 |
| 89 | 57 | 67 | 71 |
Note that the evaluation is per event, for example per row of the
overall table of values across the array over all events. To calculate
values across the column of values, use
array:reduceColumn().