Aggregate Functions (Lakehouse v1)
Aggregate functions are essential tools in SQL that allow you to perform calculations on a set of values and return a single result.
These functions help you extract and summarize data from databases to gain valuable insights.
| Function Name | What It Does |
|---|---|
| ANY | Checks if any row meets the specified condition |
| APPROX_COUNT_DISTINCT | Estimates the number of distinct values with HyperLogLog |
| ARG_MAX | Finds the arg value for the maximum val value |
| ARG_MIN | Finds the arg value for the minimum val value |
| AVG_IF | Calculates the average for rows meeting a condition |
| ARRAY_AGG | Converts all the values of a column to an Array |
| AVG | Calculates the average value of a specific column |
| COUNT_DISTINCT | Counts the number of distinct values in a column |
| COUNT_IF | Counts rows meeting a specified condition |
| COUNT | Counts the number of rows that meet certain criteria |
| COVAR_POP | Returns the population covariance of a set of number pairs |
| COVAR_SAMP | Returns the sample covariance of a set of number pairs |
| GROUP_ARRAY_MOVING_AVG | Returns an array with elements calculates the moving average of input values |
| GROUP_ARRAY_MOVING_SUM | Returns an array with elements calculates the moving sum of input values |
| KURTOSIS | Calculates the excess kurtosis of a set of values |
| MAX_IF | Finds the maximum value for rows meeting a condition |
| MAX | Finds the largest value in a specific column |
| MEDIAN | Calculates the median value of a specific column |
| MEDIAN_TDIGEST | Calculates the median value of a specific column using t-digest algorithm |
| MIN_IF | Finds the minimum value for rows meeting a condition |
| MIN | Finds the smallest value in a specific column |
| QUANTILE_CONT | Calculates the interpolated quantile for a specific column |
| QUANTILE_DISC | Calculates the quantile for a specific column |
| QUANTILE_TDIGEST | Calculates the quantile using t-digest algorithm |
| QUANTILE_TDIGEST_WEIGHTED | Calculates the quantile with weighted using t-digest algorithm |
| RETENTION | Calculates retention for a set of events |
| SKEWNESS | Calculates the skewness of a set of values |
| STDDEV_POP | Calculates the population standard deviation of a column |
| STDDEV_SAMP | Calculates the sample standard deviation of a column |
| STRING_AGG | Converts all the non-NULL values to String, separated by the delimiter |
| SUM_IF | Adds up the values meeting a condition of a specific column |
| SUM | Adds up the values of a specific column |
| WINDOW_FUNNEL | Analyzes user behavior in a time-ordered sequence of events |