Aggregate Functions
All aggregate functions are grouped together into SQL.agg. Some functions have multiple API names.
The name of the field is automatically derived by default though it can be customized with an alias.
Alternate API may result in a different default name.
SQL Frames currently provides the following aggregate functions.
| SQL | Aggregate Function | Comments |
|---|---|---|
| ANY_VALUE | anyValue | return any value (non-deterministic) |
| COUNT | count | |
| SUM | sum | |
| AVG | avg mean | |
| MIN | min | |
| MAX | max | |
| MEDIAN | median | |
| MODE | mode | returns an array as there can be multiple modes |
| PERCENTILE | percentile | |
| VARIANCE | variance | |
| VAR_SAMP | varSamp varianceSample | |
| VAR_POP | varPop variancePopulation | |
| STDDEV | stddev | |
| STDDEV_SAMP | stddevSamp | |
| STDDEV_POP | stddevPop | |
| COVAR_POP | covarPop | |
| COVAR_SAMP | covarSamp | |
| CORR | corr | |
| REGR_* | regr regrCount regrSlope regrIntercept | regr returns an object with all the individual components of the linear regression analysis |
| LISTAGG | listagg | string concatenation |
| COLLECT | collect | list concatenation |
| prod | product of values like in Excel |
Distinct Aggregates
Some aggregates operate on distinct values within the aggregating set.
| SQL | API |
|---|---|
| COUNT DISTINCT | countd |
| SUM DISTINCT | sumd |
| AVG DISTINCT | avgd |
| LISTAGG DISTINCT | listd |
| COLLECT DISTINCT | collectd |
Ordered Aggregates
Following aggregates compute relative values of ordered data sets.
| SQL | API |
|---|---|
| RANK | rank |
| DENSE_RANK | denseRank |
| CUME_DIST | cumeDist |
| PERCENT_RANK | percentRank |
FIRST & LAST Aggregates
Some aggregates have FIRST and LAST qualifiers. That is, data is first ordered based on a sort criteria and then the FIRST or LAST ranking rows of this ordered set are used to perform the aggregation.
The following functions have corresponding FIRST/LAST implementations.
| SQL | API |
|---|---|
| count | countf, countl |
| SUM | sumf, suml |
| AVG | avgf, avgl |
| MIN | minf, minl |
| MAX | maxf, maxl |
| VARIANCE | --TODO-- |
| STDDEV | --TODO-- |
GROUPING_ID
There may be a need to have different logic for base partition vs rollup partitions.
It is possible to accomplish such requirement using the SQL.groupingId() API as shown below.
The requirement is to us the Unit Price as per the Item Type but for higher levels compute it using a formula such as Total Revenue/Total Quantity.