Aggregation query basics
An aggregation query summarizes a dataset and returns a single result. In time-series database (TSDB) scenarios, common use cases include calculating averages, maximum and minimum values, counting records, grouping statistics by dimension, and computing data distribution characteristics.
GROUP BY rule: The SELECT list can only contain constants, aggregate functions, expressions that match those in the GROUP BY clause, and expressions that include the above.
Basic example:
SELECT groupid, avg(voltage) FROM meters WHERE ts >= "2022-01-01T00:00:00+08:00" GROUP BY groupid;
This computes the average voltage grouped by groupid. GROUP BY does not guarantee result ordering. Use ORDER BY when sorted results are needed.
Built-in aggregate functions overview
Basic statistical functions: AVG (average), COUNT (row count), SUM, MAX (maximum value), MIN (minimum value).
Advanced statistical functions: STDDEV (standard deviation), SPREAD (range, maximum minus minimum), PERCENTILE (exact percentile), APERCENTILE (approximate percentile).
Time-series-specific functions: ELAPSED (continuous time length within a period), LEASTSQUARES (linear equation fitting), HYPERLOGLOG (cardinality estimation, standard error approximately 0.81%), HISTOGRAM (data distribution by interval).
Common aggregate function usage
AVG:
SELECT AVG(voltage) FROM meters WHERE ts >= '2022-01-01 00:00:00';
You can group by dimensions such as groupid and location.
COUNT:
SELECT COUNT(*) FROM meters WHERE ts >= '2022-01-01 00:00:00';
SELECT tbname, COUNT(*) FROM meters WHERE ts >= '2022-01-01 00:00:00' PARTITION BY tbname;
SUM:
SELECT SUM(current), AVG(current), MAX(current), MIN(current) FROM meters WHERE ts >= '2022-01-01 00:00:00';
SPREAD:
SELECT SPREAD(voltage) FROM meters WHERE ts >= '2022-01-01 00:00:00';
STDDEV:
SELECT STDDEV(voltage) FROM meters WHERE ts >= '2022-01-01 00:00:00';
Percentile functions
PERCENTILE:
SELECT PERCENTILE(voltage, 50) FROM meters;
Calculates the median voltage.
SELECT PERCENTILE(voltage, 95) FROM meters;
Calculates the 95th percentile.
APERCENTILE: Approximate percentile calculation for large datasets. It improves performance on large datasets but returns an approximate result.
LEASTSQUARES linear fitting
SELECT LEASTSQUARES(voltage, 0, 1) FROM meters;
Parameter description: the first parameter is the column to fit, the second is the initial value of the independent variable, and the third is the step size of the independent variable.
HYPERLOGLOG cardinality estimation
This algorithm keeps memory usage low, with a standard error of 0.81%.
SELECT HYPERLOGLOG(voltage) FROM meters;
HISTOGRAM data distribution
SELECT HISTOGRAM(voltage, 200, 250, 10) FROM meters;
Counts voltage distribution by specified intervals.
Aggregation query best practices
Use WHERE filtering with a time range to improve query efficiency. Combine multiple aggregate functions:
SELECT COUNT(*), AVG(voltage), MAX(voltage), MIN(voltage), STDDEV(voltage)
FROM meters
WHERE ts >= '2022-01-01 00:00:00'
GROUP BY groupid;
Filter groups with HAVING:
HAVING AVG(voltage) > 240
Sort results:
ORDER BY avg_v DESC
Aggregating directly on Supertables
You can run aggregation queries directly on a Supertable without creating Subtables first. TDengine automatically handles the aggregation of all Subtable data.
Summary
Aggregation queries are central to time-series analysis. TDengine provides aggregate functions that range from basic COUNT, AVG, and SUM through advanced PERCENTILE and LEASTSQUARES. Applied correctly, they surface patterns and trends across large volumes of time-series data.
