github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/time_window/time_window.sql (about)

     1  drop table if exists sensor_data;
     2  CREATE TABLE sensor_data (ts timestamp(3) primary key, temperature FLOAT);
     3  INSERT INTO sensor_data VALUES('2023-08-01 00:00:00', 25.0);
     4  INSERT INTO sensor_data VALUES('2023-08-01 00:05:00', 26.0);
     5  INSERT INTO sensor_data VALUES('2023-08-01 00:15:00', 28.0);
     6  INSERT INTO sensor_data VALUES('2023-08-01 00:20:00', 30.0);
     7  INSERT INTO sensor_data VALUES('2023-08-01 00:25:00', 27.0);
     8  INSERT INTO sensor_data VALUES('2023-08-01 00:30:00', null);
     9  INSERT INTO sensor_data VALUES('2023-08-01 00:35:00', null);
    10  INSERT INTO sensor_data VALUES('2023-08-01 00:40:00', 28);
    11  INSERT INTO sensor_data VALUES('2023-08-01 00:45:00', 38);
    12  INSERT INTO sensor_data VALUES('2023-08-01 00:50:00', 31);
    13  insert into sensor_data values('2023-07-31 23:55:00', 22);
    14  select _wstart, _wend, max(temperature), _wstart from sensor_data where ts > "2023-08-01 00:00:00.000" and ts < "2023-08-01 00:50:00" interval(ts, 10, minute) sliding(5, minute) fill(linear);
    15  select _wstart, _wend, max(temperature), min(temperature) from sensor_data where ts > "2023-08-01 00:00:00.000" and ts < "2023-08-01 00:50:00" interval(ts, 10, minute) sliding(5, minute) fill(prev);
    16  select _wstart, _wend, max(temperature), sum(temperature) from sensor_data where ts > "2023-08-01 00:00:00.000" and ts < "2023-08-01 00:50:00" interval(ts, 10, minute) sliding(5, minute) fill(next);
    17  select _wstart, _wend, max(temperature), count(temperature) from sensor_data where ts > "2023-08-01 00:00:00.000" and ts < "2023-08-01 00:50:00" interval(ts, 10, minute) sliding(5, minute) fill(next);
    18  select _wstart, _wend, max(temperature), sum(temperature) from sensor_data where ts > "2023-08-01 00:00:00.000" and ts < "2023-08-01 00:50:00" interval(ts, 10, minute) sliding(5, minute) fill(linear);
    19  select _wstart, _wend, count(temperature), sum(temperature) from sensor_data where ts > "2023-08-01 00:00:00.000" and ts < "2023-08-01 00:50:00" interval(ts, 10, minute) sliding(5, minute) fill(next);
    20  select _wstart, _wend, max(temperature), min(temperature) from sensor_data where ts > "2023-08-01 00:00:00.000" and ts < "2023-08-01 00:50:00" interval(ts, 10, minute) sliding(5, minute) fill(value, 123);
    21  select _wstart, _wend, max(temperature) from sensor_data interval(ts, 1, minute);
    22  select _wstart, _wend, max(temperature) from sensor_data interval(ts, 2, minute);
    23  select _wstart, _wend, max(temperature) from sensor_data interval(ts, 3, minute);
    24  select _wstart, _wend, max(temperature) from sensor_data interval(ts, 4, minute);
    25  select _wstart, _wend, max(temperature) from sensor_data interval(ts, 5, minute);
    26  select _wstart, _wend, max(temperature) from sensor_data interval(ts, 10, minute);
    27  select _wstart, _wend, max(temperature) from sensor_data interval(ts, 10, minute) sliding(5, minute);
    28  select _wstart, _wend, max(temperature) from sensor_data where ts > "2023-08-01 00:00:00.000" and ts < "2023-08-01 00:50:00.000" interval(ts, 10, minute) sliding(5, minute);
    29  select _wstart, _wend, max(temperature) from sensor_data interval(ts, 1, second);
    30  select _wstart, _wend, max(temperature) from sensor_data interval(ts, 2, hour);
    31  select _wstart, _wend, max(temperature) from sensor_data interval(ts, 3, day);