Data partitioning queries overview
A data partitioning query groups data by a specified dimension. Each group forms an independent data space, and subsequent computation operations run within each space separately. The main benefits are parallel computation, clearer query logic, and more controllable result sets. Different partitions can be processed concurrently, complex queries can be decomposed into independent computation units, and returned partitions can be limited to manage result size.
The PARTITION BY clause
Syntax structure
PARTITION BY part_list. The part_list can be a column name, a constant, a scalar function, or any combination of these.
Processing flow
The data partitioning clause sits after the WHERE clause. It divides table data by the specified dimension, and each partition runs the assigned computation. The computation itself is defined by subsequent clauses: a Window clause, a GROUP BY clause, or the SELECT clause. The data partitioning clause can be used together with a Window partitioning clause or a GROUP BY clause; in that case, the subsequent clause operates on each partition independently.
Basic example
SELECT location, avg(voltage) FROM meters PARTITION BY location;
This query groups results by location and returns the average voltage for each location.
Combining PARTITION BY with window queries
When you need to run time-window aggregation on each device separately, combine PARTITION BY with a Window clause.
Example:
SELECT tbname, _wstart, _wend, avg(voltage)
FROM meters
WHERE ts >= "2022-01-01T00:00:00+08:00"
AND ts < "2022-01-01T00:05:00+08:00"
PARTITION BY tbname
INTERVAL(1m, 5s)
SLIMIT 2;
Execution flow: WHERE filtering, PARTITION BY splitting, INTERVAL windowing, SLIMIT limiting.
SLIMIT and SOFFSET
SLIMIT limits the number of partitions returned:
SELECT tbname, avg(voltage) FROM meters PARTITION BY tbname SLIMIT 1;
SOFFSET specifies how many partitions to skip:
SLIMIT 3 SOFFSET 5
Combining SLIMIT and SOFFSET enables partition-level pagination.
Common partitioning dimensions
Partition by Subtable name:
SELECT tbname, COUNT(*), AVG(voltage)
FROM meters
WHERE ts >= '2022-01-01 00:00:00'
PARTITION BY tbname;
Partition by Tag:
PARTITION BY location
Partition by expression:
PARTITION BY group_id % 3
Differences between PARTITION BY and GROUP BY
The two clauses differ in several ways:
| Aspect | PARTITION BY | GROUP BY |
|---|---|---|
| Position in query | After WHERE | After the Window clause |
| Compatibility with Window clause | Can be used together | Cannot be used together |
| Computation model | Each partition computed independently | Global aggregation |
| Result form | Multiple partition result sets | A single aggregated result |
Advanced application scenarios
Device-level time-window analysis: Run time-window aggregation on each device independently using INTERVAL(1h) with SLIMIT 10.
Regional group statistics: Group by region to compute device count, average voltage, and maximum voltage.
Multi-dimension partitioning: Partition by multiple expressions simultaneously, such as partitioning by both location and group_id.
Performance optimization recommendations
Choose partitioning dimensions wisely. Pick columns with high cardinality. Tag columns are ideal choices.
Use SLIMIT to control results. For large-scale data queries, limit the number of partitions. Combine with SOFFSET for pagination. Avoid returning more partitions than the application can process efficiently in a single query.
Apply time-range filtering. Specify a time range in the WHERE clause. This reduces the data volume in each partition and improves query efficiency.
Important notes
Clause order:
SELECT ... FROM ... WHERE ... PARTITION BY ... [Window clause] [SLIMIT ...]
PARTITION BY cannot be used with GROUP BY at the same time. Choose the grouping method that fits your needs.
PARTITION BY does not guarantee result order. Use ORDER BY when sorted results are required.
Summary
Data partitioning queries are a key feature of TDengine. The PARTITION BY clause lets you flexibly split data by dimension for parallel computation and detailed analysis. Combined with Window clauses and SLIMIT limits, you can build effective time-series data analysis queries. This is especially useful when querying large volumes of device data in IoT and industrial scenarios.
