Downsampling Overview: Reduce Time Series Data with Aggregation - Lindorm

A downsampling query aggregates time series data over fixed time windows to reduce the sample rate — useful for dashboards, trend analysis, and long-term storage.

Prerequisites

Before you begin, ensure that you have:

A LindormTSDB instance running version 3.4.15 or later. Downsampling queries are supported only by LindormTSDB.

Note

For instructions on checking and upgrading your LindormTSDB version, see Release notes of LindormTSDB and Upgrade the minor engine version of a Lindorm instance.

Syntax

select_sample_by_statement ::=  SELECT ( select_clause | '*' )
                                FROM table_identifier
                                WHERE where_clause
                                SAMPLE BY time_interval [ OFFSET offset_interval ] [ FILL fill_option ]
select_clause              ::=  selector [ AS identifier ] ( ',' selector [ AS identifier ] )
selector                   ::=  tag_identifier | time | function_identifier '(' field_identifier [ ',' function_args ] ')'
where_clause               ::=  relation ( AND relation )* (OR relation)*
relation                   ::=  ( field_identifier | tag_identifier ) operator term
operator                   ::=  '=' | '<' | '>' | '<=' | '>=' | '!=' | IN | CONTAINS | CONTAINS KEY
time_interval              ::= interval units | 0
offset_interval            ::= interval units

Key clauses:

SAMPLE BY time_interval — divides each time series into equal-width time windows and applies aggregate functions to each window.
OFFSET offset_interval — shifts all window boundaries by the specified amount. Use this to align windows to a local timezone or a non-UTC boundary.
FILL fill_option — fills empty windows (those with no data points) with a specified value. See Interpolation for available options.

Supported aggregate functions

SAMPLE BY operates on each individual time series. For more information about the time series data model, see Data model.

Function	Description
SUM	Sum of values within each time window
AVG	Mean of values within each time window
COUNT	Count of values within each time window
MIN	Minimum value within each time window
MAX	Maximum value within each time window
FIRST	First value within each time window
LAST	Last value within each time window
PERCENTILE	Percentile value within each time window
LATEST	Latest value within the entire time range
RATE	Rate of change from the previous row's value
DELTA	Difference from the previous row's value

Column rules:

Tag columns in SELECT do not require an aggregate function.
All field columns in SELECT must use an aggregate function.

Limitations

Field-column filtering is not supported

SAMPLE BY filters by tag columns only. Filter conditions on field columns are silently ignored, which may cause the returned data to differ from what you expect.

To filter by a field column, wrap the downsampling query in an outer query and apply the filter there. The following example uses LATEST to return the two most recent temperature readings per window, then filters results where temperature > 45.0 in the outer query:

SELECT device_id, region, time, temperature
FROM (
    SELECT device_id, region, time, latest(temperature, 2) AS temperature
    FROM sensor
    WHERE device_id IN ('F07A1260', 'F07A1261')
    SAMPLE BY 0
)
WHERE temperature > 45.0;

Nesting rules

SAMPLE BY queries cannot contain nested subqueries.
SAMPLE BY queries can be used as subqueries inside an outer query.
GROUP BY, ORDER BY, and LIMIT OFFSET cannot be used directly in a SAMPLE BY query. Nest the SAMPLE BY query as a subquery and apply these clauses in the outer query.

Examples

The following examples use a sensor table with this structure and data:

-- Create the sensor table
CREATE TABLE sensor (
    device_id   VARCHAR NOT NULL,
    region      VARCHAR NOT NULL,
    time        TIMESTAMP NOT NULL,
    temperature DOUBLE,
    humidity    BIGINT,
    PRIMARY KEY(device_id, region, time)
);

-- Insert sample data
UPSERT INTO sensor(device_id, region, time, temperature, humidity) VALUES('F07A1260','north-cn','2021-01-01 09:00:00',0,9);
UPSERT INTO sensor(device_id, region, time, temperature, humidity) VALUES('F07A1260','north-cn','2021-01-01 12:01:00',1,45);
UPSERT INTO sensor(device_id, region, time, temperature, humidity) VALUES('F07A1260','north-cn','2021-01-01 14:03:00',2,46);
UPSERT INTO sensor(device_id, region, time, temperature, humidity) VALUES('F07A1260','north-cn','2021-01-01 20:00:00',10,47);
UPSERT INTO sensor(device_id, region, time, temperature, humidity) VALUES('F07A1261','north-cn','2021-02-10 12:00:30',3,40);
UPSERT INTO sensor(device_id, region, time, temperature, humidity) VALUES('F07A1261','north-cn','2021-03-01 12:01:00',4,41);
UPSERT INTO sensor(device_id, region, time, temperature, humidity) VALUES('F07A1261','north-cn','2021-03-08 12:08:00',5,42);
UPSERT INTO sensor(device_id, region, time, temperature, humidity) VALUES('F07A1261','north-cn','2021-05-01 13:00:00',6,43);

Basic downsampling

Note

SAMPLE BY queries cannot contain nested subqueries, but can be nested as subqueries within other queries.

Example 1: 8-hour windows with default UTC alignment

SELECT device_id, region, time, count(humidity) AS count_humidity
FROM sensor
WHERE device_id = 'F07A1260'
SAMPLE BY 8h;

Result:

+-----------+----------+---------------------------+----------------+
| device_id |  region  |           time            | count_humidity |
+-----------+----------+---------------------------+----------------+
| F07A1260  | north-cn | 2021-01-01T08:00:00+08:00 | 3              |
| F07A1260  | north-cn | 2021-01-01T16:00:00+08:00 | 1              |
+-----------+----------+---------------------------+----------------+

Windows snap to UTC epoch boundaries by default (midnight UTC = 08:00 UTC+8). The three data points recorded between 09:00 and 14:03 fall in the first window; the 20:00 point falls in the second.

Example 2: 8-hour windows with a 3-hour offset

SELECT device_id, region, time, count(humidity) AS count_humidity
FROM sensor
WHERE device_id = 'F07A1260'
SAMPLE BY 8h OFFSET 3h;

Result:

+-----------+----------+---------------------------+----------------+
| device_id |  region  |           time            | count_humidity |
+-----------+----------+---------------------------+----------------+
| F07A1260  | north-cn | 2021-01-01T03:00:00+08:00 | 1              |
| F07A1260  | north-cn | 2021-01-01T11:00:00+08:00 | 2              |
| F07A1260  | north-cn | 2021-01-01T19:00:00+08:00 | 1              |
+-----------+----------+---------------------------+----------------+

The 3-hour offset shifts all window boundaries by 3 hours, producing windows at 03:00, 11:00, and 19:00 (UTC+8).

Example 3: 24-hour windows aligned to local midnight (UTC+8)

To align 24-hour windows to 00:00 in the UTC+8 timezone, apply a 16-hour offset (24 - 8 = 16):

SELECT device_id, region, time, count(humidity) AS count_humidity
FROM sensor
WHERE device_id = 'F07A1260'
SAMPLE BY 24h OFFSET 16h;

Result:

+-----------+----------+---------------------------+----------------+
| device_id | region   | time                      | count_humidity |
+-----------+----------+---------------------------+----------------+
| F07A1260  | north-cn | 2021-01-01T00:00:00+08:00 | 4              |
+-----------+----------+---------------------------+----------------+

All four data points for January 1 fall in the single daily window starting at 00:00 UTC+8.

Combining downsampling with GROUP BY, ORDER BY, and LIMIT OFFSET

GROUP BY, ORDER BY, and LIMIT OFFSET cannot be applied directly to a SAMPLE BY query. Nest the SAMPLE BY query as a subquery and apply these clauses in the outer query.

Example 4: Maximum average humidity per device

SELECT device_id, max(avg_humidity) AS max_humidity
FROM (
    SELECT device_id, region, time, avg(humidity) AS avg_humidity
    FROM sensor
    SAMPLE BY 8h
)
GROUP BY device_id;

Result:

+-----------+--------------+
| device_id | max_humidity |
+-----------+--------------+
| F07A1261  | 43.000000    |
| F07A1260  | 47.000000    |
+-----------+--------------+

Example 5: Paginating downsampled results with LIMIT OFFSET

SELECT device_id, region, avg_humidity
FROM (
    SELECT device_id, region, time, avg(humidity) AS avg_humidity
    FROM sensor
    SAMPLE BY 8h
)
LIMIT 1 OFFSET 1;

Result:

+-----------+----------+--------------+
| device_id |  region  | avg_humidity |
+-----------+----------+--------------+
| F07A1261  | north-cn | 40.000000    |
+-----------+----------+--------------+

Transform functions on downsampled data

RATE and DELTA can be applied to an aggregate function to compute per-window derivatives on the downsampled result.

Example 6: Rate of change of average humidity across 2-hour windows

SELECT device_id, region, time, rate(avg(humidity)) AS rate_humidity
FROM sensor
WHERE device_id = 'F07A1260'
SAMPLE BY 2h;

Result:

+-----------+----------+---------------------------+---------------+
| device_id |  region  |           time            | rate_humidity |
+-----------+----------+---------------------------+---------------+
| F07A1260  | north-cn | 2021-01-01T12:00:00+08:00 | 0.002500      |
| F07A1260  | north-cn | 2021-01-01T14:00:00+08:00 | 0.000139      |
| F07A1260  | north-cn | 2021-01-01T20:00:00+08:00 | 0.000046      |
+-----------+----------+---------------------------+---------------+

Example 7: Delta (difference) of average humidity across 2-hour windows

SELECT device_id, region, time, delta(avg(humidity)) AS humidity
FROM sensor
WHERE device_id = 'F07A1260'
SAMPLE BY 2h;

Result:

+-----------+----------+---------------------------+-----------+
| device_id |  region  |           time            | humidity  |
+-----------+----------+---------------------------+-----------+
| F07A1260  | north-cn | 2021-01-01T12:00:00+08:00 | 36.000000 |
| F07A1260  | north-cn | 2021-01-01T14:00:00+08:00 | 1.000000  |
| F07A1260  | north-cn | 2021-01-01T20:00:00+08:00 | 1.000000  |
+-----------+----------+---------------------------+-----------+

Interpolation

After downsampling, time windows that contain no data points are omitted from the result by default. Use the FILL clause to insert a value for each empty window.

How it works: Consider a time series with data at t+0, t+20, and t+30. With a 10-unit window, the window starting at t+10 is empty. Without FILL, the result contains three rows. With FILL 1, a row with value 1 is added at t+10.

The following fill policies are available:

Fill policy	Behavior
`none`	Default. Empty windows are omitted from the result.
`zero`	Fills empty windows with `0`.
`linear`	Fills empty windows with a linearly interpolated value between the surrounding data points.
`previous`	Fills empty windows with the previous window's value.
`near`	Fills empty windows with the nearest adjacent value.
`after`	Fills empty windows with the next window's value.
`fixed`	Fills empty windows with a specified static value.

Interpolation examples

Example 8: Fill with a static value

SELECT *
FROM (
    SELECT device_id, region, time, avg(humidity) AS humidity
    FROM sensor
    WHERE device_id = 'F07A1260'
    SAMPLE BY 2h FILL 1
)
ORDER BY device_id;

Result:

+-----------+----------+---------------------------+-----------+
| device_id |  region  |           time            | humidity  |
+-----------+----------+---------------------------+-----------+
| F07A1260  | north-cn | 2021-01-01T08:00:00+08:00 | 9.000000  |
| F07A1260  | north-cn | 2021-01-01T10:00:00+08:00 | 1.000000  |
| F07A1260  | north-cn | 2021-01-01T12:00:00+08:00 | 45.000000 |
| F07A1260  | north-cn | 2021-01-01T14:00:00+08:00 | 46.000000 |
| F07A1260  | north-cn | 2021-01-01T16:00:00+08:00 | 1.000000  |
| F07A1260  | north-cn | 2021-01-01T18:00:00+08:00 | 1.000000  |
| F07A1260  | north-cn | 2021-01-01T20:00:00+08:00 | 47.000000 |
+-----------+----------+---------------------------+-----------+

The 10:00, 16:00, and 18:00 windows had no data and are filled with 1.

Example 9: Fill with the next value

SELECT *
FROM (
    SELECT device_id, region, time, avg(humidity) AS humidity
    FROM sensor
    WHERE device_id = 'F07A1260'
    SAMPLE BY 2h FILL after
)
ORDER BY device_id;

Result:

+-----------+----------+---------------------------+-----------+
| device_id |  region  |           time            | humidity  |
+-----------+----------+---------------------------+-----------+
| F07A1260  | north-cn | 2021-01-01T08:00:00+08:00 | 9.000000  |
| F07A1260  | north-cn | 2021-01-01T10:00:00+08:00 | 45.000000 |
| F07A1260  | north-cn | 2021-01-01T12:00:00+08:00 | 45.000000 |
| F07A1260  | north-cn | 2021-01-01T14:00:00+08:00 | 46.000000 |
| F07A1260  | north-cn | 2021-01-01T16:00:00+08:00 | 47.000000 |
| F07A1260  | north-cn | 2021-01-01T18:00:00+08:00 | 47.000000 |
| F07A1260  | north-cn | 2021-01-01T20:00:00+08:00 | 47.000000 |
+-----------+----------+---------------------------+-----------+

Empty windows are filled with the value of the next non-empty window.