Aggregate Functions
# StreamSQL Aggregate Functions
Aggregate functions perform calculations on a set of values and return a single value. Aggregate functions can only be used in the following expressions:
- SELECT list in SELECT statements (subqueries or outer queries)
- HAVING clause
# SUM - Sum Function
Syntax: sum(col)
Description: Returns the sum of numeric values in a group. NULL values are ignored.
Incremental Calculation: ✅ Supported
Example:
SELECT device, sum(temperature) as total_temp
FROM stream
GROUP BY device, TumblingWindow('10s')
2
3
# AVG - Average Function
Syntax: avg(col)
Description: Returns the average of numeric values in a group. NULL values are ignored.
Incremental Calculation: ✅ Supported
Example:
SELECT device, avg(temperature) as avg_temp
FROM stream
GROUP BY device, TumblingWindow('10s')
2
3
# COUNT - Count Function
Syntax: count(*)
Description: Returns the number of rows in a group.
Incremental Calculation: ✅ Supported
Example:
SELECT device, count(*) as record_count
FROM stream
GROUP BY device, TumblingWindow('10s')
2
3
# MIN - Minimum Function
Syntax: min(col)
Description: Returns the minimum value in a group. NULL values are ignored.
Incremental Calculation: ✅ Supported
Example:
SELECT device, min(temperature) as min_temp
FROM stream
GROUP BY device, TumblingWindow('10s')
2
3
# MAX - Maximum Function
Syntax: max(col)
Description: Returns the maximum value in a group. NULL values are ignored.
Incremental Calculation: ✅ Supported
Example:
SELECT device, max(temperature) as max_temp
FROM stream
GROUP BY device, TumblingWindow('10s')
2
3
# COLLECT - Collect Function
Syntax: collect(col)
Description: Collects all column values from messages in the current window into an array.
Incremental Calculation: ✅ Supported
Example:
SELECT device, collect(temperature) as temp_values
FROM stream
GROUP BY device, TumblingWindow('10s')
2
3
# LAST_VALUE - Last Value Function
Syntax: last_value(col)
Description: Returns the value from the last row in a group.
Incremental Calculation: ✅ Supported
Example:
SELECT device, last_value(temperature) as last_temp
FROM stream
GROUP BY device, TumblingWindow('10s')
2
3
# MERGE_AGG - Merge Aggregate Function
Syntax: merge_agg(col)
Description: Merges values in a group into a single value. For object types, merges all key-value pairs; for other types, concatenates with commas.
Incremental Calculation: ✅ Supported
Example:
SELECT device, merge_agg(status) as all_status
FROM stream
GROUP BY device, TumblingWindow('10s')
2
3
# DEDUPLICATE - Deduplication Function
Syntax: deduplicate(col, false)
Description: Returns deduplicated results for the current group, typically used in windows. The second parameter specifies whether to return all results.
Incremental Calculation: ✅ Supported
Example:
SELECT device, deduplicate(temperature, true) as unique_temps
FROM stream
GROUP BY device, TumblingWindow('10s')
2
3
# STDDEV - Standard Deviation Function
Syntax: stddev(col)
Description: Returns the population standard deviation of all values in a group. NULL values are ignored.
Incremental Calculation: ✅ Supported (optimized using Welford's algorithm)
Example:
SELECT device, stddev(temperature) as temp_stddev
FROM stream
GROUP BY device, TumblingWindow('10s')
2
3
# STDDEVS - Sample Standard Deviation Function
Syntax: stddevs(col)
Description: Returns the sample standard deviation of all values in a group. NULL values are ignored.
Incremental Calculation: ✅ Supported (optimized using Welford's algorithm)
Example:
SELECT device, stddevs(temperature) as temp_sample_stddev
FROM stream
GROUP BY device, TumblingWindow('10s')
2
3
# VAR - Variance Function
Syntax: var(col)
Description: Returns the population variance of all values in a group. NULL values are ignored.
Incremental Calculation: ✅ Supported (optimized using Welford's algorithm)
Example:
SELECT device, var(temperature) as temp_variance
FROM stream
GROUP BY device, TumblingWindow('10s')
2
3
# VARS - Sample Variance Function
Syntax: vars(col)
Description: Returns the sample variance of all values in a group. NULL values are ignored.
Incremental Calculation: ✅ Supported (optimized using Welford's algorithm)
Example:
SELECT device, vars(temperature) as temp_sample_variance
FROM stream
GROUP BY device, TumblingWindow('10s')
2
3
# MEDIAN - Median Function
Syntax: median(col)
Description: Returns the median of all values in a group. NULL values are ignored.
Incremental Calculation: ✅ Supported
Example:
SELECT device, median(temperature) as temp_median
FROM stream
GROUP BY device, TumblingWindow('10s')
2
3
# PERCENTILE - Percentile Function
Syntax: percentile(col, 0.5)
Description: Returns the specified percentile of all values in a group. The second parameter specifies the percentile value, ranging from 0.0 to 1.0.
Incremental Calculation: ✅ Supported
Example:
SELECT device, percentile(temperature, 0.95) as temp_p95
FROM stream
GROUP BY device, TumblingWindow('10s')
2
3
# 📚 Related Documentation
- Analytical Functions - Learn detailed usage of analytical functions
- Window Functions - Learn detailed usage of window functions
- SQL Reference - View complete SQL syntax reference