These Aggregate functions are aggregate functions as available on in stream views and pipes. Aggregate functions serve the purpose of aggregating data and giving it more eaningfull meaning. They are usually a common feature in any data based environment. In database management, for instance aggregate functions are used where multiple rows are grouped together to form a singe value or to find out more significant meaning, represented by a single value on a set of values.Aggregate functions are also available on pipes and views in PhixFlow. The following composes a set of available functions (all aggregate function below expect a single argument - field name):aggregate data, often grouped by other attributes, to give totals, max, min, avg values etc. for each key value, e.g. for each customer, region.
average| Average | This function returns the arithmetic average value of |
the set of numbers. It takes one argument (a name of a field to aggregate on) of numeric type: Long, Float or Integer. It is only available if field on which it is applied, is of such type.maximuman attribute from records with the same key value. In detail, it returns the sum of all values for a key value, divided by the number of values that are not null. It is only available for numeric attributes. |
| Count | This function returns the |
maximum numeric value out of the set of numeric, dates as well as string values. It takes one argument (a name of a field to aggregate on) of type, either: Long, Float, Integer or a String. In case of a String, it will return a "maximum string", that is a string that would be the last string out of the whole set of strings, if they were sorted in alphabetical order. In case of a date - it will return the latest date.minimum| number of non-null values of an attribute from records with the same key value. |
| Distinct | This function returns the number of unique values of an attribute from records with the same key value. |
| Maximum | This function returns the |
minimum numeric out the set of numeric, dates as well as string values. It takes one argument (a name of a field to aggregate on) of type, either: Long, Float, Integer or a String. In case of a String, it will return a "minimum string", that is a string that would be the first string out of the whole set of strings, if they were sorted in alphabetical order. In case of a date - it will return the earliest date.| sum | This function returns the sum calculated by suming all the values in a given set of values. It is applicable only (and only then it is available) if the name of the field passed to this function represents numeric field of type: Long, Float or Integer. |
percent | This function expects a name of numeric field to calculate percentage of sum of numerical values of each group in relation to a whole set of grouped values. It is therefore necessary to use this function in conjunction with grouping attribute, as otherwise only default grouping would be applied and this would present no meaningful result, as it would always default to 100% (each record would have been in its own group). If however we assume having a file with 2 records, like soan attribute from records with the same key value. It is available for numeric, string and date/time attributes. Strings are ordered alphabetically, with the shortest strings first. |
| Minimum | This function returns the minimum value of an attribute from records with the same key value. It is available for numeric, string and date/time attributes. Strings are ordered alphabetically, with the shortest strings first. |
| Percent | This function can be applied to a numeric field, to return the percentage of the sum of values for each key value in relation to the total sum of values. You should use this function in conjunction with one or more grouping attributes - each combination of values from these grouping attributes gives a key value. For example, you have a stream with 2 fields: | Number | Label |
|---|
| 12 | A | | 14 | B | | 13 | C | | 10 | A | | 5 | A | | 2 | B |
and |
we also assume grouping by "Label", our percent function would return (if applied o "Number" attribute), the following result in a view (assuming we also selected Label attribute to be shown in our view and rounding to 2 decimal places)you create a grid view: - dragging in the attribute
Label - which automatically becomes a grouping attribute when one or more of the other attributes are aggregated - dragging in the attribute
Number, and applying the aggregate function Percent - setting the rounding on the stream view attribute
Number to 2 decimal places
This will give the view: | Number | Label |
|---|
| 48.22 | A | | 28.57 | B | | 23.21 | C |
|
The above are percentages of values assigned to given grouping parameters (A, B, C) as a percentage portion of the whole, computed as
|
| Sum | This function returns the sum of all |
such .| distinct | Returns the number of unique values for the given attribute. |
count | Counts the number of elementsof an attribute from records with the same key value. It is only available for numeric attributes. |