github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/window/time_window.result (about) 1 drop database if exists time_window; 2 create database time_window; 3 use time_window; 4 drop table if exists time_window01; 5 create table time_window01 (ts timestamp, col2 int); 6 insert into time_window01 values ('2021-01-12 00:00:00.000', 12); 7 insert into time_window01 values ('2020-01-12 12:00:12.000', 24); 8 insert into time_window01 values ('2021-01-12 00:00:00.000', 34); 9 insert into time_window01 values ('2020-01-12 12:00:12.000', 20); 10 select * from time_window01; 11 ts col2 12 2021-01-12 00:00:00 12 13 2020-01-12 12:00:12 24 14 2021-01-12 00:00:00 34 15 2020-01-12 12:00:12 20 16 select _wstart, _wend, max(col2), min(col2) from time_window01 where ts > '2020-01-11 12:00:12.000' and ts < '2021-01-13 00:00:00.000' interval(ts, 100, day) fill(prev); 17 not supported: time_window01.ts is not primary key in time window 18 drop table time_window01; 19 drop table if exists time_window02; 20 create table time_window02 (ts timestamp primary key , col2 bool); 21 insert into time_window02 values ('2023-10-26 10:00:00.000', false); 22 insert into time_window02 values ('2023-10-26 10:10:00.000', true); 23 insert into time_window02 values ('2023-10-26 10:20:00.000', null); 24 insert into time_window02 values ('2023-10-26 10:30:00.000', true); 25 select * from time_window02; 26 ts col2 27 2023-10-26 10:00:00 false 28 2023-10-26 10:10:00 true 29 2023-10-26 10:20:00 null 30 2023-10-26 10:30:00 true 31 select _wstart, _wend, max(col2), min(col2) from time_window02 where ts > '2020-01-11 12:00:12.000' and ts < '2024-01-13 00:00:00.000' interval(ts, 10, second) fill(prev); 32 _wstart _wend max(col2) min(col2) 33 2023-10-26 10:00:00 2023-10-26 10:00:10 false false 34 2023-10-26 10:10:00 2023-10-26 10:10:10 true true 35 2023-10-26 10:20:00 2023-10-26 10:20:10 true true 36 2023-10-26 10:30:00 2023-10-26 10:30:10 true true 37 drop table time_window02; 38 drop table if exists time_window03; 39 create table time_window03 (ts datetime primary key , col2 int); 40 insert into time_window03 values ('2021-01-12 00:00:00', 12); 41 insert into time_window03 values ('2020-01-12 12:00:12', 24); 42 insert into time_window03 values ('2021-01-14 00:00:00', 34); 43 insert into time_window03 values ('2020-01-16 12:00:12', 20); 44 select * from time_window03; 45 ts col2 46 2021-01-12 00:00:00 12 47 2020-01-12 12:00:12 24 48 2021-01-14 00:00:00 34 49 2020-01-16 12:00:12 20 50 select _wstart, _wend, max(col2), min(col2) from time_window03 where ts > '2020-01-11 12:00:12.000' and ts < '2021-01-13 00:00:00.000' interval(ts, 100, day) fill(prev); 51 not supported: the type of time_window03.ts must be timestamp in time window 52 drop table time_window03; 53 drop table if exists time_window04; 54 create table time_window04 (ts timestamp primary key, col2 char, col3 varchar(10), col4 text); 55 insert into time_window04 values ('2023-10-26 10:00:00.000', 'a', 'b', 'djiweijwfcjwefwq'); 56 insert into time_window04 values ('2020-01-12 12:00:12.000', '1', '2', 'efwq3232e数据库系统'); 57 insert into time_window04 values ('2021-01-12 00:00:00.000', '是', 'srewrew', null); 58 insert into time_window04 values ('2023-10-26 10:30:00.000', 'w', null, null); 59 select * from time_window04; 60 ts col2 col3 col4 61 2023-10-26 10:00:00 a b djiweijwfcjwefwq 62 2020-01-12 12:00:12 1 2 efwq3232e数据库系统 63 2021-01-12 00:00:00 是 srewrew null 64 2023-10-26 10:30:00 w null null 65 select _wstart, _wend, max(col2), min(col3), max() from time_window04 where ts > '2020-01-11 12:00:12.000' and ts < '2021-01-13 00:00:00.000' interval(ts, 100, day) fill(prev); 66 SQL parser error: You have an error in your SQL syntax; check the manual that corresponds to your MatrixOne server version for the right syntax to use. syntax error at line 1 column 50 near ") from time_window04 where ts > '2020-01-11 12:00:12.000' and ts < '2021-01-13 00:00:00.000' interval(ts, 100, day) fill(prev);"; 67 drop table time_window04; 68 drop table if exists time_window05; 69 create table time_window05 (ts datetime primary key , col2 decimal); 70 insert into time_window05 values ('2022-10-10', 4324.43423); 71 insert into time_window05 values ('2022-10-12', -4324.43423); 72 select * from time_window05; 73 ts col2 74 2022-10-10 00:00:00 4324 75 2022-10-12 00:00:00 -4324 76 select _wstart, _wend, max(col2) from time_window05 where ts > '2020-01-11 12:00:12.000' and ts < '2021-01-13 00:00:00.000' interval(ts, 100, day) fill(prev); 77 not supported: the type of time_window05.ts must be timestamp in time window 78 drop table time_window05; 79 drop table if exists time_window06; 80 create table time_window06 (ts timestamp, col2 int); 81 insert into time_window06 values ('2020-01-01 10:00:00.000', 212332); 82 insert into time_window06 values ('2020-01-02 12:00:00.000', -3890232); 83 insert into time_window06 values ('2020-01-04 09:00:00.000', null); 84 select * from time_window06; 85 ts col2 86 2020-01-01 10:00:00 212332 87 2020-01-02 12:00:00 -3890232 88 2020-01-04 09:00:00 null 89 select _wstart, _wend, max(col2) from time_window05 where ts > '2020-01-11 12:00:12.000' and ts < '2021-01-13 00:00:00.000' interval(ts, 100, day) fill(prev); 90 SQL parser error: table "time_window05" does not exist 91 drop table time_window06; 92 drop table if exists time_window07; 93 create table time_window07 (ts timestamp primary key, col2 smallint unsigned); 94 insert into time_window07 values ('2020-01-01 10:00:00.000', 127); 95 insert into time_window07 values ('2020-01-02 12:00:00.000', 0); 96 select _wstart, _wend, max(col2) from time_window07 where ts > '2020-01-01 00:00:00.000' and ts < '2021-01-13 00:00:00.000' interval(ts, 0.1, second) fill(next); 97 SQL parser error: You have an error in your SQL syntax; check the manual that corresponds to your MatrixOne server version for the right syntax to use. syntax error at line 1 column 140 near " 0.1, second) fill(next);"; 98 drop table time_window07; 99 drop table if exists time_window08; 100 create table time_window08 (ts timestamp primary key, col2 smallint unsigned); 101 insert into time_window08 values ('2020-01-01 10:00:00.000', 127); 102 insert into time_window08 values ('2020-01-02 12:00:00.000', 0); 103 insert into time_window08 values ('2020-01-03 12:00:00.000', 22); 104 select _wstart, _wend, max(col2) from time_window08 where ts > '2020-01-01 00:00:00.000' and ts < '2021-01-13 00:00:00.000' interval(ts, 1, day) sliding(2,day); 105 invalid input: sliding value should be smaller than the interval value 106 107 drop table time_window08; 108 drop table if exists int01; 109 create table int01 (ts timestamp primary key , col2 tinyint unsigned, col3 smallint, col4 bigint unsigned); 110 insert into int01 values ('2020-01-01 10:00:00.000', 127, null, 32151654354); 111 insert into int01 values ('2020-01-02 12:00:00.000', 0, null, 9223372036854775807); 112 insert into int01 values ('2020-01-03 13:00:00.000', 64, null, null); 113 insert into int01 values ('2020-01-04 09:00:00.000', 100, -1921, 32173892173092); 114 insert into int01 values ('2020-01-05 09:00:00.000', 200, 0, 37219739821); 115 insert into int01 values ('2020-01-06 09:00:00.000', 200, 0, 294095); 116 select * from int01; 117 ts col2 col3 col4 118 2020-01-01 10:00:00 127 null 32151654354 119 2020-01-02 12:00:00 0 null 9223372036854775807 120 2020-01-03 13:00:00 64 null null 121 2020-01-04 09:00:00 100 -1921 32173892173092 122 2020-01-05 09:00:00 200 0 37219739821 123 2020-01-06 09:00:00 200 0 294095 124 select _wstart, _wend, max(col2), min(col3), avg(col4) from int01 where ts > '2020-01-01 00:00:00.000' and ts < '2021-01-13 00:00:00.000' interval(ts, 1, day); 125 _wstart _wend max(col2) min(col3) avg(col4) 126 2020-01-01 00:00:00 2020-01-02 00:00:00 127 null 3.2151654354E10 127 2020-01-02 00:00:00 2020-01-03 00:00:00 0 null 9.223372036854776E18 128 2020-01-03 00:00:00 2020-01-04 00:00:00 64 null null 129 2020-01-04 00:00:00 2020-01-05 00:00:00 100 -1921 3.2173892173092E13 130 2020-01-05 00:00:00 2020-01-06 00:00:00 200 0 3.7219739821E10 131 2020-01-06 00:00:00 2020-01-07 00:00:00 200 0 294095.0 132 select _wstart, _wend, max(col2), min(col3), avg(col4) from int01 where ts > '2020-01-01 00:00:00.000' and ts < '2021-01-13 00:00:00.000' interval(ts, 1, day) fill(prev); 133 _wstart _wend max(col2) min(col3) avg(col4) 134 2020-01-01 00:00:00 2020-01-02 00:00:00 127 null 3.2151654354E10 135 2020-01-02 00:00:00 2020-01-03 00:00:00 0 null 9.223372036854776E18 136 2020-01-03 00:00:00 2020-01-04 00:00:00 64 null 9.223372036854776E18 137 2020-01-04 00:00:00 2020-01-05 00:00:00 100 -1921 3.2173892173092E13 138 2020-01-05 00:00:00 2020-01-06 00:00:00 200 0 3.7219739821E10 139 2020-01-06 00:00:00 2020-01-07 00:00:00 200 0 294095.0 140 select _wstart, _wend, max(col2), min(col3), avg(col4) from int01 where ts > '2020-01-01 00:00:00.000' and ts < '2021-01-13 00:00:00.000' interval(ts, 1, day) fill(next); 141 _wstart _wend max(col2) min(col3) avg(col4) 142 2020-01-01 00:00:00 2020-01-02 00:00:00 127 -1921 3.2151654354E10 143 2020-01-02 00:00:00 2020-01-03 00:00:00 0 -1921 9.223372036854776E18 144 2020-01-03 00:00:00 2020-01-04 00:00:00 64 -1921 3.2173892173092E13 145 2020-01-04 00:00:00 2020-01-05 00:00:00 100 -1921 3.2173892173092E13 146 2020-01-05 00:00:00 2020-01-06 00:00:00 200 0 3.7219739821E10 147 2020-01-06 00:00:00 2020-01-07 00:00:00 200 0 294095.0 148 select _wstart, _wend, max(col2), min(col3), avg(col4) from int01 where ts > '2020-01-01 00:00:00.000' and ts < '2021-01-13 00:00:00.000' interval(ts, 1, day) fill(linear); 149 _wstart _wend max(col2) min(col3) avg(col4) 150 2020-01-01 00:00:00 2020-01-02 00:00:00 127 null 3.2151654354E10 151 2020-01-02 00:00:00 2020-01-03 00:00:00 0 null 9.223372036854776E18 152 2020-01-03 00:00:00 2020-01-04 00:00:00 64 null 4.6117021053734748E18 153 2020-01-04 00:00:00 2020-01-05 00:00:00 100 -1921 3.2173892173092E13 154 2020-01-05 00:00:00 2020-01-06 00:00:00 200 0 3.7219739821E10 155 2020-01-06 00:00:00 2020-01-07 00:00:00 200 0 294095.0 156 select _wstart, _wend, max(col2), min(col3), avg(col4) from int01 where ts > '2020-01-01 00:00:00.000' and ts < '2021-01-13 00:00:00.000' interval(ts, 1, day) fill(none); 157 _wstart _wend max(col2) min(col3) avg(col4) 158 2020-01-01 00:00:00 2020-01-02 00:00:00 127 null 3.2151654354E10 159 2020-01-02 00:00:00 2020-01-03 00:00:00 0 null 9.223372036854776E18 160 2020-01-03 00:00:00 2020-01-04 00:00:00 64 null null 161 2020-01-04 00:00:00 2020-01-05 00:00:00 100 -1921 3.2173892173092E13 162 2020-01-05 00:00:00 2020-01-06 00:00:00 200 0 3.7219739821E10 163 2020-01-06 00:00:00 2020-01-07 00:00:00 200 0 294095.0 164 select _wstart, _wend, max(col2), min(col3), avg(col4) from int01 where ts > '2020-01-01 00:00:00.000' and ts < '2021-01-13 00:00:00.000' interval(ts, 1, day) fill(VALUE,200); 165 _wstart _wend max(col2) min(col3) avg(col4) 166 2020-01-01 00:00:00 2020-01-02 00:00:00 127 200 3.2151654354E10 167 2020-01-02 00:00:00 2020-01-03 00:00:00 0 200 9.223372036854776E18 168 2020-01-03 00:00:00 2020-01-04 00:00:00 64 200 200.0 169 2020-01-04 00:00:00 2020-01-05 00:00:00 100 -1921 3.2173892173092E13 170 2020-01-05 00:00:00 2020-01-06 00:00:00 200 0 3.7219739821E10 171 2020-01-06 00:00:00 2020-01-07 00:00:00 200 0 294095.0 172 select _wstart, _wend, max(col2), min(col3), avg(col4) from int01 where ts > '2020-01-01 00:00:00.000' and ts < '2021-01-13 00:00:00.000' interval(ts, 24, hour); 173 _wstart _wend max(col2) min(col3) avg(col4) 174 2020-01-01 00:00:00 2020-01-02 00:00:00 127 null 3.2151654354E10 175 2020-01-02 00:00:00 2020-01-03 00:00:00 0 null 9.223372036854776E18 176 2020-01-03 00:00:00 2020-01-04 00:00:00 64 null null 177 2020-01-04 00:00:00 2020-01-05 00:00:00 100 -1921 3.2173892173092E13 178 2020-01-05 00:00:00 2020-01-06 00:00:00 200 0 3.7219739821E10 179 2020-01-06 00:00:00 2020-01-07 00:00:00 200 0 294095.0 180 select _wstart, _wend, sum(col2), sum(col3), sum(col4) from int01 where ts > '2020-01-01 00:00:00.000' and ts < '2021-01-13 00:00:00.000' interval(ts, 13, hour); 181 _wstart _wend sum(col2) sum(col3) sum(col4) 182 2019-12-31 22:00:00 2020-01-01 11:00:00 127 null 32151654354 183 2020-01-02 00:00:00 2020-01-02 13:00:00 0 null 9223372036854775807 184 2020-01-03 02:00:00 2020-01-03 15:00:00 64 null null 185 2020-01-04 04:00:00 2020-01-04 17:00:00 100 -1921 32173892173092 186 2020-01-05 06:00:00 2020-01-05 19:00:00 200 0 37219739821 187 2020-01-06 08:00:00 2020-01-06 21:00:00 200 0 294095 188 select _wstart, _wend, sum(col2), sum(col3), sum(col4) from int01 where ts > '2020-01-01 00:00:00.000' and ts < '2021-01-13 00:00:00.000' interval(ts, 2, day); 189 _wstart _wend sum(col2) sum(col3) sum(col4) 190 2020-01-01 00:00:00 2020-01-03 00:00:00 127 null 9223372069006430161 191 2020-01-03 00:00:00 2020-01-05 00:00:00 164 -1921 32173892173092 192 2020-01-05 00:00:00 2020-01-07 00:00:00 400 0 37220033916 193 select _wstart, _wend, count(col2) from int01 where ts > '2020-01-01 00:00:00.000' and ts < '2021-01-13 00:00:00.000' interval(ts, 1, day) fill(none); 194 _wstart _wend count(col2) 195 2020-01-01 00:00:00 2020-01-02 00:00:00 1 196 2020-01-02 00:00:00 2020-01-03 00:00:00 1 197 2020-01-03 00:00:00 2020-01-04 00:00:00 1 198 2020-01-04 00:00:00 2020-01-05 00:00:00 1 199 2020-01-05 00:00:00 2020-01-06 00:00:00 1 200 2020-01-06 00:00:00 2020-01-07 00:00:00 1 201 drop table int01; 202 drop table if exists int02; 203 create table int02 (ts timestamp primary key, col2 float, col3 double, col4 decimal(30,10)); 204 insert into int02 values ('2023-10-27 00:00:00.000', 321421.42314, 38021.324143214, 321798372091.324213421421342); 205 insert into int02 values ('2023-10-27 01:01:00.000', -2379823.0, 214214.32143214, -321798372091.324213421421342); 206 insert into int02 values ('2023-10-27 01:59:12.123', 321421.42314, null, 321798372091.324213421421342); 207 insert into int02 values ('2023-10-27 02:10:12.123', 321421.42314, -38021.111, null); 208 insert into int02 values ('2023-10-27 02:39:12.123', 0, 38021.324143214, 321798372091.324213421421342); 209 insert into int02 values ('2023-10-27 03:02:12.123', -321.42314832, 43824.43543, null); 210 insert into int02 values ('2023-10-27 12:10:12.110', 0, 0, 0); 211 insert into int02 values ('2023-10-27 12:33:12.110', 321421.42314, 2222, 3211.324213487734324535432523421421342); 212 insert into int02 values ('2023-10-27 13:33:12.110', 8430923, 332, 0); 213 insert into int02 values ('2023-10-27 13:33:15.110', 2141243.423141234213421421, 38021.324143214, 321798372091.324213421421342); 214 insert into int02 values ('2023-10-27 23:33:15.110', 321421.42314, 38021.324143214, 321798372091.324213421421342); 215 insert into int02 values ('2023-10-27 22:33:15.110', 321421.42314, null, null); 216 select * from int02; 217 ts col2 col3 col4 218 2023-10-27 00:00:00 321421.44 38021.324143214 321798372091.3242134214 219 2023-10-27 01:01:00 -2379823.0 214214.32143214 -321798372091.3242134214 220 2023-10-27 01:59:12 321421.44 null 321798372091.3242134214 221 2023-10-27 02:10:12 321421.44 -38021.111 null 222 2023-10-27 02:39:12 0.0 38021.324143214 321798372091.3242134214 223 2023-10-27 03:02:12 -321.42316 43824.43543 null 224 2023-10-27 12:10:12 0.0 0.0 0E-10 225 2023-10-27 12:33:12 321421.44 2222.0 3211.3242134877 226 2023-10-27 13:33:12 8430923.0 332.0 0E-10 227 2023-10-27 13:33:15 2141243.5 38021.324143214 321798372091.3242134214 228 2023-10-27 23:33:15 321421.44 38021.324143214 321798372091.3242134214 229 2023-10-27 22:33:15 321421.44 null null 230 select _wstart, _wend, max(col2), min(col3), min(col4) from int02 where ts >= '2023-10-27 00:00:00.000' and ts < '2023-10-27 23:00:00.000' interval(ts, 1, hour) fill(none); 231 _wstart _wend max(col2) min(col3) min(col4) 232 2023-10-27 00:00:00 2023-10-27 01:00:00 321421.44 38021.324143214 321798372091.3242134214 233 2023-10-27 01:00:00 2023-10-27 02:00:00 321421.44 214214.32143214 -321798372091.3242134214 234 2023-10-27 02:00:00 2023-10-27 03:00:00 321421.44 -38021.111 321798372091.3242134214 235 2023-10-27 03:00:00 2023-10-27 04:00:00 -321.42316 43824.43543 null 236 2023-10-27 12:00:00 2023-10-27 13:00:00 321421.44 0.0 0E-10 237 2023-10-27 13:00:00 2023-10-27 14:00:00 8430923.0 332.0 0E-10 238 2023-10-27 22:00:00 2023-10-27 23:00:00 321421.44 null null 239 select _wstart, _wend, max(col2), min(col3), min(col4) from int02 where ts > '2023-10-27 00:00:00.000' and ts < '2023-10-27 23:00:00.000' interval(ts, 10, minute) fill(next); 240 _wstart _wend max(col2) min(col3) min(col4) 241 2023-10-27 01:00:00 2023-10-27 01:10:00 -2379823.0 214214.32143214 -321798372091.3242134214 242 2023-10-27 01:50:00 2023-10-27 02:00:00 321421.44 -38021.111 321798372091.3242134214 243 2023-10-27 02:10:00 2023-10-27 02:20:00 321421.44 -38021.111 321798372091.3242134214 244 2023-10-27 02:30:00 2023-10-27 02:40:00 0.0 38021.324143214 321798372091.3242134214 245 2023-10-27 03:00:00 2023-10-27 03:10:00 -321.42316 43824.43543 0E-10 246 2023-10-27 12:10:00 2023-10-27 12:20:00 0.0 0.0 0E-10 247 2023-10-27 12:30:00 2023-10-27 12:40:00 321421.44 2222.0 3211.3242134877 248 2023-10-27 13:30:00 2023-10-27 13:40:00 8430923.0 332.0 0E-10 249 2023-10-27 22:30:00 2023-10-27 22:40:00 321421.44 null null 250 select _wstart, _wend, sum(col2), sum(col3), sum(col4) from int02 where ts > '2023-10-27 00:00:00.000' and ts < '2023-10-27 23:00:00.000' interval(ts, 60, minute) fill(next); 251 _wstart _wend sum(col2) sum(col3) sum(col4) 252 2023-10-27 01:00:00 2023-10-27 02:00:00 -2058401.5625 214214.32143214 0E-10 253 2023-10-27 02:00:00 2023-10-27 03:00:00 321421.4375 0.2131432140013203 321798372091.3242134214 254 2023-10-27 03:00:00 2023-10-27 04:00:00 -321.42315673828125 43824.43543 3211.3242134877 255 2023-10-27 12:00:00 2023-10-27 13:00:00 321421.4375 2222.0 3211.3242134877 256 2023-10-27 13:00:00 2023-10-27 14:00:00 1.05721665E7 38353.324143214 321798372091.3242134214 257 2023-10-27 22:00:00 2023-10-27 23:00:00 321421.4375 null null 258 select _wstart, _wend, avg(col2), avg(col3), avg(col4) from int02 where ts > '2023-10-27 00:00:00.000' and ts < '2023-10-27 23:00:00.000' interval(ts, 20, minute); 259 _wstart _wend avg(col2) avg(col3) avg(col4) 260 2023-10-27 01:00:00 2023-10-27 01:20:00 -2379823.0 214214.32143214 -321798372091.324213421400 261 2023-10-27 01:40:00 2023-10-27 02:00:00 321421.4375 null 321798372091.324213421400 262 2023-10-27 02:00:00 2023-10-27 02:20:00 321421.4375 -38021.111 null 263 2023-10-27 02:20:00 2023-10-27 02:40:00 0.0 38021.324143214 321798372091.324213421400 264 2023-10-27 03:00:00 2023-10-27 03:20:00 -321.42315673828125 43824.43543 null 265 2023-10-27 12:00:00 2023-10-27 12:20:00 0.0 0.0 0E-12 266 2023-10-27 12:20:00 2023-10-27 12:40:00 321421.4375 2222.0 3211.324213487700 267 2023-10-27 13:20:00 2023-10-27 13:40:00 5286083.25 19176.662071607 160899186045.662106710700 268 2023-10-27 22:20:00 2023-10-27 22:40:00 321421.4375 null null 269 select _wstart, _wend, count(col2), count(col3), count(col4) from int02 where ts > '2023-10-27 00:00:00.000' and ts < '2023-10-27 23:00:00.000' interval(ts, 30, minute) fill(prev); 270 _wstart _wend count(col2) count(col3) count(col4) 271 2023-10-27 01:00:00 2023-10-27 01:30:00 1 1 1 272 2023-10-27 01:30:00 2023-10-27 02:00:00 1 0 1 273 2023-10-27 02:00:00 2023-10-27 02:30:00 1 1 0 274 2023-10-27 02:30:00 2023-10-27 03:00:00 1 1 1 275 2023-10-27 03:00:00 2023-10-27 03:30:00 1 1 0 276 2023-10-27 12:00:00 2023-10-27 12:30:00 1 1 1 277 2023-10-27 12:30:00 2023-10-27 13:00:00 1 1 1 278 2023-10-27 13:30:00 2023-10-27 14:00:00 2 2 2 279 2023-10-27 22:30:00 2023-10-27 23:00:00 1 0 0 280 drop table int02; 281 drop table if exists sliding_window01; 282 create table sliding_window01 (ts timestamp(3) primary key , col2 double); 283 insert into sliding_window01 values ('2023-08-01 00:00:00', 25.0); 284 insert into sliding_window01 values ('2023-08-01 00:05:00', 26.0); 285 insert into sliding_window01 values ('2023-08-01 00:15:00', 28.0); 286 insert into sliding_window01 values ('2023-08-01 00:20:00', 30.0); 287 insert into sliding_window01 values ('2023-08-01 00:25:00', 27.0); 288 insert into sliding_window01 values ('2023-08-01 00:30:00', null); 289 insert into sliding_window01 values ('2023-08-01 00:35:00', null); 290 insert into sliding_window01 values ('2023-08-01 00:40:00', 28); 291 insert into sliding_window01 values ('2023-08-01 00:45:00', 38); 292 insert into sliding_window01 values ('2023-08-01 00:50:00', 31); 293 insert into sliding_window01 values ('2023-07-31 23:55:00', 22); 294 select * from sliding_window01; 295 ts col2 296 2023-08-01 00:00:00 25.0 297 2023-08-01 00:05:00 26.0 298 2023-08-01 00:15:00 28.0 299 2023-08-01 00:20:00 30.0 300 2023-08-01 00:25:00 27.0 301 2023-08-01 00:30:00 null 302 2023-08-01 00:35:00 null 303 2023-08-01 00:40:00 28.0 304 2023-08-01 00:45:00 38.0 305 2023-08-01 00:50:00 31.0 306 2023-07-31 23:55:00 22.0 307 select _wstart, _wend, max(col2), min(col2) from sliding_window01 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); 308 _wstart _wend max(col2) min(col2) 309 2023-08-01 00:00:00 2023-08-01 00:10:00 26.0 26.0 310 2023-08-01 00:05:00 2023-08-01 00:15:00 26.0 26.0 311 2023-08-01 00:10:00 2023-08-01 00:20:00 28.0 28.0 312 2023-08-01 00:15:00 2023-08-01 00:25:00 30.0 28.0 313 2023-08-01 00:20:00 2023-08-01 00:30:00 30.0 27.0 314 2023-08-01 00:25:00 2023-08-01 00:35:00 27.0 27.0 315 2023-08-01 00:30:00 2023-08-01 00:40:00 null null 316 2023-08-01 00:35:00 2023-08-01 00:45:00 28.0 28.0 317 2023-08-01 00:40:00 2023-08-01 00:50:00 38.0 28.0 318 2023-08-01 00:45:00 2023-08-01 00:55:00 38.0 38.0 319 select _wstart, _wend, max(col2), min(col2) from sliding_window01 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) fill(null); 320 _wstart _wend max(col2) min(col2) 321 2023-08-01 00:00:00 2023-08-01 00:10:00 26.0 26.0 322 2023-08-01 00:05:00 2023-08-01 00:15:00 26.0 26.0 323 2023-08-01 00:10:00 2023-08-01 00:20:00 28.0 28.0 324 2023-08-01 00:15:00 2023-08-01 00:25:00 30.0 28.0 325 2023-08-01 00:20:00 2023-08-01 00:30:00 30.0 27.0 326 2023-08-01 00:25:00 2023-08-01 00:35:00 27.0 27.0 327 2023-08-01 00:30:00 2023-08-01 00:40:00 null null 328 2023-08-01 00:35:00 2023-08-01 00:45:00 28.0 28.0 329 2023-08-01 00:40:00 2023-08-01 00:50:00 38.0 28.0 330 2023-08-01 00:45:00 2023-08-01 00:55:00 38.0 38.0 331 select _wstart, _wend, count(col2), avg(col2) from sliding_window01 where ts > "2023-08-01 00:00:00.000" and ts < "2023-08-01 00:50:00.000" interval(ts, 10, minute) sliding(10, minute) fill(next); 332 _wstart _wend count(col2) avg(col2) 333 2023-08-01 00:00:00 2023-08-01 00:10:00 1 26.0 334 2023-08-01 00:10:00 2023-08-01 00:20:00 1 28.0 335 2023-08-01 00:20:00 2023-08-01 00:30:00 2 28.5 336 2023-08-01 00:30:00 2023-08-01 00:40:00 0 33.0 337 2023-08-01 00:40:00 2023-08-01 00:50:00 2 33.0 338 select _wstart, _wend, count(col2), avg(col2) from sliding_window01 where ts > "2023-08-01 00:00:00.000" and ts < "2023-08-01 00:50:00.000" interval(ts, 10, minute) sliding(6, minute) fill(value,1000); 339 _wstart _wend count(col2) avg(col2) 340 2023-08-01 00:00:00 2023-08-01 00:10:00 1 26.0 341 2023-08-01 00:06:00 2023-08-01 00:16:00 1 28.0 342 2023-08-01 00:12:00 2023-08-01 00:22:00 2 29.0 343 2023-08-01 00:18:00 2023-08-01 00:28:00 2 28.5 344 2023-08-01 00:24:00 2023-08-01 00:34:00 1 27.0 345 2023-08-01 00:30:00 2023-08-01 00:40:00 0 1000.0 346 2023-08-01 00:36:00 2023-08-01 00:46:00 2 33.0 347 2023-08-01 00:42:00 2023-08-01 00:52:00 1 38.0 348 select _wstart, _wend, sum(col2) from sliding_window01 where ts > "2023-08-01 00:00:00.000" and ts < "2023-08-01 00:50:00.000" interval(ts, 1, hour) sliding(10, minute) fill(none); 349 _wstart _wend sum(col2) 350 2023-08-01 00:00:00 2023-08-01 01:00:00 177.0 351 2023-08-01 00:10:00 2023-08-01 01:10:00 151.0 352 drop table sliding_window01; 353 drop table if exists sliding_window02; 354 create table sliding_window02 (ts timestamp primary key , col2 double); 355 insert into sliding_window02 values ('2023-08-01 00:01:01.000',37281932.32143214); 356 insert into sliding_window02 values ('2023-08-01 00:01:02.000',-328934.324); 357 insert into sliding_window02 values ('2023-08-01 00:01:03.000',-23.23232); 358 insert into sliding_window02 values ('2023-08-01 00:01:04.000',null); 359 select * from sliding_window02; 360 ts col2 361 2023-08-01 00:01:01 3.728193232143214E7 362 2023-08-01 00:01:02 -328934.324 363 2023-08-01 00:01:03 -23.23232 364 2023-08-01 00:01:04 null 365 select _wstart, _wend, max(col2), min(col2), sum(col2) from sliding_window02 where ts > "2023-08-01 00:01:00.000" and ts < "2023-08-01 00:01:10.000" interval(ts, 1, second) fill(none); 366 _wstart _wend max(col2) min(col2) sum(col2) 367 2023-08-01 00:01:01 2023-08-01 00:01:02 3.728193232143214E7 3.728193232143214E7 3.728193232143214E7 368 2023-08-01 00:01:02 2023-08-01 00:01:03 -328934.324 -328934.324 -328934.324 369 2023-08-01 00:01:03 2023-08-01 00:01:04 -23.23232 -23.23232 -23.23232 370 2023-08-01 00:01:04 2023-08-01 00:01:05 null null null 371 select _wstart, _wend, max(col2), min(col2), sum(col2) from sliding_window02 where ts >= "2023-08-01 00:01:00.000" and ts <= "2023-08-01 00:01:10.000" interval(ts, 2, second); 372 _wstart _wend max(col2) min(col2) sum(col2) 373 2023-08-01 00:01:00 2023-08-01 00:01:02 3.728193232143214E7 3.728193232143214E7 3.728193232143214E7 374 2023-08-01 00:01:02 2023-08-01 00:01:04 -23.23232 -328934.324 -328957.55632000003 375 2023-08-01 00:01:04 2023-08-01 00:01:06 null null null 376 select _wstart, _wend, max(col2), min(col2), sum(col2) from sliding_window02 where ts >= "2023-08-01 00:01:00.000" and ts <= "2023-08-01 00:01:10.000" interval(ts, 2, second) fill(prev); 377 _wstart _wend max(col2) min(col2) sum(col2) 378 2023-08-01 00:01:00 2023-08-01 00:01:02 3.728193232143214E7 3.728193232143214E7 3.728193232143214E7 379 2023-08-01 00:01:02 2023-08-01 00:01:04 -23.23232 -328934.324 -328957.55632000003 380 2023-08-01 00:01:04 2023-08-01 00:01:06 -23.23232 -328934.324 -328957.55632000003 381 select _wstart, _wend, count(col2), avg(col2) from sliding_window02 where ts > "2023-08-01 00:01:00.000" and ts < "2023-08-01 00:01:10.000" interval(ts, 2, second) fill(none); 382 _wstart _wend count(col2) avg(col2) 383 2023-08-01 00:01:00 2023-08-01 00:01:02 1 3.728193232143214E7 384 2023-08-01 00:01:02 2023-08-01 00:01:04 2 -164478.77816000002 385 2023-08-01 00:01:04 2023-08-01 00:01:06 0 null 386 select _wstart, _wend, count(col2), avg(col2) from sliding_window02 where ts > "2023-08-01 00:01:00.000" and ts < "2023-08-01 00:01:10.000" interval(ts, 2, second) fill(linear); 387 _wstart _wend count(col2) avg(col2) 388 2023-08-01 00:01:00 2023-08-01 00:01:02 1 3.728193232143214E7 389 2023-08-01 00:01:02 2023-08-01 00:01:04 2 -164478.77816000002 390 2023-08-01 00:01:04 2023-08-01 00:01:06 0 null 391 select * from sliding_window02; 392 ts col2 393 2023-08-01 00:01:01 3.728193232143214E7 394 2023-08-01 00:01:02 -328934.324 395 2023-08-01 00:01:03 -23.23232 396 2023-08-01 00:01:04 null 397 drop table sliding_window02; 398 drop table if exists sliding_window03; 399 create table sliding_window03 (ts timestamp primary key , col2 float); 400 insert into sliding_window03 values ('2023-08-01 00:01:01.000', 32412.3421); 401 insert into sliding_window03 values ('2023-08-01 00:01:03.000', -23.23232); 402 insert into sliding_window03 values ('2023-08-01 00:01:04.000', -3289.328939201); 403 select * from sliding_window03; 404 ts col2 405 2023-08-01 00:01:01 32412.342 406 2023-08-01 00:01:03 -23.23232 407 2023-08-01 00:01:04 -3289.3289 408 prepare s1 from 'select _wstart, _wend, max(col2), min(col2), sum(col2), count(col2), avg(col2) from sliding_window03 where ts > "2023-08-01 00:01:00.000" and ts < "2023-08-01 00:01:10.000" interval(ts, 1, second) fill(none);'; 409 execute s1; 410 _wstart _wend max(col2) min(col2) sum(col2) count(col2) avg(col2) 411 2023-08-01 00:01:01 2023-08-01 00:01:02 32412.342 32412.342 32412.341796875 1 32412.341796875 412 2023-08-01 00:01:03 2023-08-01 00:01:04 -23.23232 -23.23232 -23.23232078552246 1 -23.23232078552246 413 2023-08-01 00:01:04 2023-08-01 00:01:05 -3289.3289 -3289.3289 -3289.328857421875 1 -3289.328857421875 414 prepare s2 from 'select _wstart, _wend, max(col2), min(col2), sum(col2), count(col2), avg(col2) from sliding_window03 where ts > "2023-08-01 00:01:00.000" and ts < "2023-08-01 00:01:10.000" interval(ts, 1, second) sliding(2, second) fill(none);'; 415 execute s2; 416 invalid input: sliding value should be smaller than the interval value 417 drop table sliding_window03; 418 drop table if exists sliding_window04; 419 create table sliding_window04 (ts timestamp primary key , col2 double); 420 insert into sliding_window04 values ('2023-08-01 00:01:01.000', 121432421.32142314); 421 insert into sliding_window04 values ('2023-08-01 00:01:03.000', null); 422 insert into sliding_window04 values ('2023-08-01 00:01:04.000', 32151323.32151251252512); 423 insert into sliding_window04 values ('2023-08-01 00:01:05.000', -38298432.32143214231); 424 insert into sliding_window04 values ('2023-08-01 00:01:06.000', 0); 425 insert into sliding_window04 values ('2023-08-01 00:01:07.111', 0); 426 insert into sliding_window04 values ('2023-08-01 00:01:08.123', 38298392.32142142); 427 select * from sliding_window04; 428 ts col2 429 2023-08-01 00:01:01 1.2143242132142314E8 430 2023-08-01 00:01:03 null 431 2023-08-01 00:01:04 3.2151323321512513E7 432 2023-08-01 00:01:05 -3.829843232143214E7 433 2023-08-01 00:01:06 0.0 434 2023-08-01 00:01:07 0.0 435 2023-08-01 00:01:08 3.829839232142142E7 436 select _wstart, _wend, count(col2), avg(col2) from sliding_window04 where ts >= "2023-08-01 00:01:00.000" and ts <= "2023-08-01 00:01:10.000" interval(ts, 1, second) sliding(1, second) fill(prev); 437 _wstart _wend count(col2) avg(col2) 438 2023-08-01 00:01:01 2023-08-01 00:01:02 1 1.2143242132142314E8 439 2023-08-01 00:01:03 2023-08-01 00:01:04 0 1.2143242132142314E8 440 2023-08-01 00:01:04 2023-08-01 00:01:05 1 3.2151323321512513E7 441 2023-08-01 00:01:05 2023-08-01 00:01:06 1 -3.829843232143214E7 442 2023-08-01 00:01:06 2023-08-01 00:01:07 1 0.0 443 2023-08-01 00:01:07 2023-08-01 00:01:08 1 0.0 444 2023-08-01 00:01:08 2023-08-01 00:01:09 1 3.829839232142142E7 445 select _wstart, _wend, count(col2), avg(col2) from sliding_window04 where ts >= "2023-08-01 00:01:00.000" and ts <= "2023-08-01 00:01:10.000" interval(ts, 2, second) sliding(2, second) fill(next); 446 _wstart _wend count(col2) avg(col2) 447 2023-08-01 00:01:00 2023-08-01 00:01:02 1 1.2143242132142314E8 448 2023-08-01 00:01:02 2023-08-01 00:01:04 0 -3073554.4999598153 449 2023-08-01 00:01:04 2023-08-01 00:01:06 2 -3073554.4999598153 450 2023-08-01 00:01:06 2023-08-01 00:01:08 2 0.0 451 2023-08-01 00:01:08 2023-08-01 00:01:10 1 3.829839232142142E7 452 drop table sliding_window04; 453 drop table if exists sliding_window05; 454 create table sliding_window05 (ts timestamp primary key , col2 int); 455 insert into sliding_window05 values ('2018-01-13 00:01:01.000', 100); 456 insert into sliding_window05 values ('2018-10-13 00:01:02.000', 37213); 457 insert into sliding_window05 values ('2019-01-29 00:10:01.000', -2146261); 458 insert into sliding_window05 values ('2019-12-13 00:11:57.000', 0); 459 insert into sliding_window05 values ('2019-12-13 00:12:58.000', 21132); 460 insert into sliding_window05 values ('2019-02-13 00:13:51.000', null); 461 insert into sliding_window05 values ('2019-12-13 00:13:59.000', null); 462 insert into sliding_window05 values ('2020-12-13 00:21:59.000', null); 463 insert into sliding_window05 values ('2021-12-13 12:12:59.000', null); 464 insert into sliding_window05 values ('2021-12-14 00:11:59.000', -328193471); 465 insert into sliding_window05 values ('2022-07-17 00:04:12.000', -3281891); 466 select * from sliding_window05; 467 ts col2 468 2018-01-13 00:01:01 100 469 2018-10-13 00:01:02 37213 470 2019-01-29 00:10:01 -2146261 471 2019-12-13 00:11:57 0 472 2019-12-13 00:12:58 21132 473 2019-02-13 00:13:51 null 474 2019-12-13 00:13:59 null 475 2020-12-13 00:21:59 null 476 2021-12-13 12:12:59 null 477 2021-12-14 00:11:59 -328193471 478 2022-07-17 00:04:12 -3281891 479 select _wstart, _wend, sum(col2) from sliding_window05 where ts >= '1997-01-13 00:00:0.000' and ts <= '2022-07-18 00:04:12.123' interval(ts, 365, day) sliding(200, day) fill(VALUE, 100); 480 _wstart _wend sum(col2) 481 2017-08-30 00:00:00 2018-08-30 00:00:00 100 482 2018-03-18 00:00:00 2019-03-18 00:00:00 -2109048 483 2018-10-04 00:00:00 2019-10-04 00:00:00 -2109048 484 2019-04-22 00:00:00 2020-04-21 00:00:00 21132 485 2019-11-08 00:00:00 2020-11-07 00:00:00 21132 486 2020-05-26 00:00:00 2021-05-26 00:00:00 100 487 2020-12-12 00:00:00 2021-12-12 00:00:00 100 488 2021-06-30 00:00:00 2022-06-30 00:00:00 -328193471 489 2022-01-16 00:00:00 2023-01-16 00:00:00 -3281891 490 select _wstart, _wend, avg(col2) from sliding_window05 where ts >= '1997-01-13 00:00:0.000' and ts <= '2022-07-18 00:04:12.123' interval(ts, 100, day) sliding(100, day) fill(linear); 491 _wstart _wend avg(col2) 492 2018-01-07 00:00:00 2018-04-17 00:00:00 100.0 493 2018-07-26 00:00:00 2018-11-03 00:00:00 37213.0 494 2018-11-03 00:00:00 2019-02-11 00:00:00 -2146261.0 495 2019-02-11 00:00:00 2019-05-22 00:00:00 -1067847.5 496 2019-12-08 00:00:00 2020-03-17 00:00:00 10566.0 497 2020-10-03 00:00:00 2021-01-11 00:00:00 -1.640914525E8 498 2021-11-07 00:00:00 2022-02-15 00:00:00 -3.28193471E8 499 2022-05-26 00:00:00 2022-09-03 00:00:00 -3281891.0 500 select _wstart, _wend, sum(col2) from sliding_window05 where ts >= '1998-01-29 00:10:01.000' and ts <= '2022-07-17 00:04:12.000' interval(ts, 200, day) sliding(100, day) fill(linear); 501 _wstart _wend sum(col2) 502 2017-09-29 00:00:00 2018-04-17 00:00:00 100 503 2018-01-07 00:00:00 2018-07-26 00:00:00 100 504 2018-04-17 00:00:00 2018-11-03 00:00:00 37213 505 2018-07-26 00:00:00 2019-02-11 00:00:00 -2109048 506 2018-11-03 00:00:00 2019-05-22 00:00:00 -2146261 507 2019-02-11 00:00:00 2019-08-30 00:00:00 -1062565 508 2019-08-30 00:00:00 2020-03-17 00:00:00 21132 509 2019-12-08 00:00:00 2020-06-25 00:00:00 21132 510 2020-06-25 00:00:00 2021-01-11 00:00:00 -164086170 511 2020-10-03 00:00:00 2021-04-21 00:00:00 -164086170 512 2021-07-30 00:00:00 2022-02-15 00:00:00 -328193471 513 2021-11-07 00:00:00 2022-05-26 00:00:00 -328193471 514 2022-02-15 00:00:00 2022-09-03 00:00:00 -3281891 515 2022-05-26 00:00:00 2022-12-12 00:00:00 -3281891 516 drop table sliding_window05; 517 drop table if exists temporary01; 518 create temporary table temporary01 (ts timestamp primary key, col2 bigint); 519 insert into temporary01 values ('2022-07-17 00:04:12.000', -2147483647); 520 insert into temporary01 values ('2022-08-17 12:23:12.000', null); 521 insert into temporary01 values ('2022-02-15 00:23:12.000', 100); 522 insert into temporary01 values ('2022-09-17 00:23:12.000', 324421432); 523 insert into temporary01 values ('2022-08-27 00:11:12.000', -32434); 524 insert into temporary01 values ('2023-01-01 12:12:12.000', -232); 525 insert into temporary01 values ('2024-03-04 13:14:56.000', -3892323); 526 insert into temporary01 values ('2024-12-12 01:34:46.000', 0); 527 insert into temporary01 values ('2020-10-10 09:09:09.000', null); 528 select * from temporary01; 529 ts col2 530 2020-10-10 09:09:09 null 531 2022-02-15 00:23:12 100 532 2022-07-17 00:04:12 -2147483647 533 2022-08-17 12:23:12 null 534 2022-08-27 00:11:12 -32434 535 2022-09-17 00:23:12 324421432 536 2023-01-01 12:12:12 -232 537 2024-03-04 13:14:56 -3892323 538 2024-12-12 01:34:46 0 539 select _wstart, _wend, sum(col2) from temporary01 where ts >= '2020-10-10 09:09:09' and ts <= '2024-12-12 01:34:46' interval(ts, 365, day) sliding(200, day); 540 _wstart _wend sum(col2) 541 2020-08-29 00:00:00 2021-08-29 00:00:00 null 542 2021-03-17 00:00:00 2022-03-17 00:00:00 100 543 2021-10-03 00:00:00 2022-10-03 00:00:00 -1823094549 544 2022-04-21 00:00:00 2023-04-21 00:00:00 -1823094881 545 2022-11-07 00:00:00 2023-11-07 00:00:00 -232 546 2023-05-26 00:00:00 2024-05-25 00:00:00 -3892323 547 2023-12-12 00:00:00 2024-12-11 00:00:00 -3892323 548 2024-06-29 00:00:00 2025-06-29 00:00:00 0 549 select _wstart, _wend, avg(col2) from temporary01 where ts >= '2020-10-10 09:09:09' and ts <= '2024-12-12 01:34:46' interval(ts, 365, day) sliding(4800, hour); 550 _wstart _wend avg(col2) 551 2020-08-29 00:00:00 2021-08-29 00:00:00 null 552 2021-03-17 00:00:00 2022-03-17 00:00:00 100.0 553 2021-10-03 00:00:00 2022-10-03 00:00:00 -4.5577363725E8 554 2022-04-21 00:00:00 2023-04-21 00:00:00 -4.5577372025E8 555 2022-11-07 00:00:00 2023-11-07 00:00:00 -232.0 556 2023-05-26 00:00:00 2024-05-25 00:00:00 -3892323.0 557 2023-12-12 00:00:00 2024-12-11 00:00:00 -3892323.0 558 2024-06-29 00:00:00 2025-06-29 00:00:00 0.0 559 select _wstart, _wend, max(col2) from temporary01 where ts >= '2020-10-10 09:09:09' and ts <= '2024-12-12 01:34:46' interval(ts, 100, day) sliding(20, day) limit 10; 560 _wstart _wend max(col2) 561 2020-10-03 00:00:00 2021-01-11 00:00:00 null 562 2021-11-27 00:00:00 2022-03-07 00:00:00 100 563 2021-12-17 00:00:00 2022-03-27 00:00:00 100 564 2022-01-06 00:00:00 2022-04-16 00:00:00 100 565 2022-01-26 00:00:00 2022-05-06 00:00:00 100 566 2022-02-15 00:00:00 2022-05-26 00:00:00 100 567 2022-04-16 00:00:00 2022-07-25 00:00:00 -2147483647 568 2022-05-06 00:00:00 2022-08-14 00:00:00 -2147483647 569 2022-05-26 00:00:00 2022-09-03 00:00:00 -32434 570 2022-06-15 00:00:00 2022-09-23 00:00:00 324421432 571 select _wstart, _wend, max(col2) from temporary01 where ts >= '2020-10-10 09:09:09' and ts <= '2024-12-12 01:34:46' interval(ts, 100, day) sliding(28800, minute) limit 10; 572 _wstart _wend max(col2) 573 2020-10-03 00:00:00 2021-01-11 00:00:00 null 574 2021-11-27 00:00:00 2022-03-07 00:00:00 100 575 2021-12-17 00:00:00 2022-03-27 00:00:00 100 576 2022-01-06 00:00:00 2022-04-16 00:00:00 100 577 2022-01-26 00:00:00 2022-05-06 00:00:00 100 578 2022-02-15 00:00:00 2022-05-26 00:00:00 100 579 2022-04-16 00:00:00 2022-07-25 00:00:00 -2147483647 580 2022-05-06 00:00:00 2022-08-14 00:00:00 -2147483647 581 2022-05-26 00:00:00 2022-09-03 00:00:00 -32434 582 2022-06-15 00:00:00 2022-09-23 00:00:00 324421432 583 select _wstart, _wend, max(col2) from temporary01 where ts >= '2020-10-10 09:09:09' and ts <= '2024-12-12 01:34:46' interval(ts, 100, day) sliding(20, day) fill(none); 584 _wstart _wend max(col2) 585 2020-10-03 00:00:00 2021-01-11 00:00:00 null 586 2021-11-27 00:00:00 2022-03-07 00:00:00 100 587 2021-12-17 00:00:00 2022-03-27 00:00:00 100 588 2022-01-06 00:00:00 2022-04-16 00:00:00 100 589 2022-01-26 00:00:00 2022-05-06 00:00:00 100 590 2022-02-15 00:00:00 2022-05-26 00:00:00 100 591 2022-04-16 00:00:00 2022-07-25 00:00:00 -2147483647 592 2022-05-06 00:00:00 2022-08-14 00:00:00 -2147483647 593 2022-05-26 00:00:00 2022-09-03 00:00:00 -32434 594 2022-06-15 00:00:00 2022-09-23 00:00:00 324421432 595 2022-07-05 00:00:00 2022-10-13 00:00:00 324421432 596 2022-07-25 00:00:00 2022-11-02 00:00:00 324421432 597 2022-08-14 00:00:00 2022-11-22 00:00:00 324421432 598 2022-09-03 00:00:00 2022-12-12 00:00:00 324421432 599 2022-10-13 00:00:00 2023-01-21 00:00:00 -232 600 2022-11-02 00:00:00 2023-02-10 00:00:00 -232 601 2022-11-22 00:00:00 2023-03-02 00:00:00 -232 602 2022-12-12 00:00:00 2023-03-22 00:00:00 -232 603 2023-01-01 00:00:00 2023-04-11 00:00:00 -232 604 2023-12-07 00:00:00 2024-03-16 00:00:00 -3892323 605 2023-12-27 00:00:00 2024-04-05 00:00:00 -3892323 606 2024-01-16 00:00:00 2024-04-25 00:00:00 -3892323 607 2024-02-05 00:00:00 2024-05-15 00:00:00 -3892323 608 2024-02-25 00:00:00 2024-06-04 00:00:00 -3892323 609 2024-09-12 00:00:00 2024-12-21 00:00:00 0 610 2024-10-02 00:00:00 2025-01-10 00:00:00 0 611 select _wstart, _wend, max(col2) from temporary01 where ts >= '2020-10-10 09:09:09' and ts <= '2024-12-12 01:34:46' interval(ts, 100, day) sliding(28800, minute) fill(prev); 612 _wstart _wend max(col2) 613 2020-10-03 00:00:00 2021-01-11 00:00:00 null 614 2021-11-27 00:00:00 2022-03-07 00:00:00 100 615 2021-12-17 00:00:00 2022-03-27 00:00:00 100 616 2022-01-06 00:00:00 2022-04-16 00:00:00 100 617 2022-01-26 00:00:00 2022-05-06 00:00:00 100 618 2022-02-15 00:00:00 2022-05-26 00:00:00 100 619 2022-04-16 00:00:00 2022-07-25 00:00:00 -2147483647 620 2022-05-06 00:00:00 2022-08-14 00:00:00 -2147483647 621 2022-05-26 00:00:00 2022-09-03 00:00:00 -32434 622 2022-06-15 00:00:00 2022-09-23 00:00:00 324421432 623 2022-07-05 00:00:00 2022-10-13 00:00:00 324421432 624 2022-07-25 00:00:00 2022-11-02 00:00:00 324421432 625 2022-08-14 00:00:00 2022-11-22 00:00:00 324421432 626 2022-09-03 00:00:00 2022-12-12 00:00:00 324421432 627 2022-10-13 00:00:00 2023-01-21 00:00:00 -232 628 2022-11-02 00:00:00 2023-02-10 00:00:00 -232 629 2022-11-22 00:00:00 2023-03-02 00:00:00 -232 630 2022-12-12 00:00:00 2023-03-22 00:00:00 -232 631 2023-01-01 00:00:00 2023-04-11 00:00:00 -232 632 2023-12-07 00:00:00 2024-03-16 00:00:00 -3892323 633 2023-12-27 00:00:00 2024-04-05 00:00:00 -3892323 634 2024-01-16 00:00:00 2024-04-25 00:00:00 -3892323 635 2024-02-05 00:00:00 2024-05-15 00:00:00 -3892323 636 2024-02-25 00:00:00 2024-06-04 00:00:00 -3892323 637 2024-09-12 00:00:00 2024-12-21 00:00:00 0 638 2024-10-02 00:00:00 2025-01-10 00:00:00 0 639 select _wstart, _wend, max(col2) from temporary01 where ts >= '2020-10-10 09:09:09' and ts <= '2024-12-12 01:34:46' interval(ts, 100, day) sliding(20, day) fill(next); 640 _wstart _wend max(col2) 641 2020-10-03 00:00:00 2021-01-11 00:00:00 100 642 2021-11-27 00:00:00 2022-03-07 00:00:00 100 643 2021-12-17 00:00:00 2022-03-27 00:00:00 100 644 2022-01-06 00:00:00 2022-04-16 00:00:00 100 645 2022-01-26 00:00:00 2022-05-06 00:00:00 100 646 2022-02-15 00:00:00 2022-05-26 00:00:00 100 647 2022-04-16 00:00:00 2022-07-25 00:00:00 -2147483647 648 2022-05-06 00:00:00 2022-08-14 00:00:00 -2147483647 649 2022-05-26 00:00:00 2022-09-03 00:00:00 -32434 650 2022-06-15 00:00:00 2022-09-23 00:00:00 324421432 651 2022-07-05 00:00:00 2022-10-13 00:00:00 324421432 652 2022-07-25 00:00:00 2022-11-02 00:00:00 324421432 653 2022-08-14 00:00:00 2022-11-22 00:00:00 324421432 654 2022-09-03 00:00:00 2022-12-12 00:00:00 324421432 655 2022-10-13 00:00:00 2023-01-21 00:00:00 -232 656 2022-11-02 00:00:00 2023-02-10 00:00:00 -232 657 2022-11-22 00:00:00 2023-03-02 00:00:00 -232 658 2022-12-12 00:00:00 2023-03-22 00:00:00 -232 659 2023-01-01 00:00:00 2023-04-11 00:00:00 -232 660 2023-12-07 00:00:00 2024-03-16 00:00:00 -3892323 661 2023-12-27 00:00:00 2024-04-05 00:00:00 -3892323 662 2024-01-16 00:00:00 2024-04-25 00:00:00 -3892323 663 2024-02-05 00:00:00 2024-05-15 00:00:00 -3892323 664 2024-02-25 00:00:00 2024-06-04 00:00:00 -3892323 665 2024-09-12 00:00:00 2024-12-21 00:00:00 0 666 2024-10-02 00:00:00 2025-01-10 00:00:00 0 667 select _wstart, _wend, max(col2) from temporary01 where ts >= '2020-10-10 09:09:09' and ts <= '2024-12-12 01:34:46' interval(ts, 100, day) sliding(48800, minute) fill (VALUE,10000); 668 _wstart _wend max(col2) 669 2020-10-03 00:00:00 2021-01-11 00:00:00 10000 670 2021-11-13 16:00:00 2022-02-21 16:00:00 100 671 2021-12-17 13:20:00 2022-03-27 13:20:00 100 672 2022-01-20 10:40:00 2022-04-30 10:40:00 100 673 2022-05-02 02:40:00 2022-08-10 02:40:00 -2147483647 674 2022-06-05 00:00:00 2022-09-13 00:00:00 -32434 675 2022-07-08 21:20:00 2022-10-16 21:20:00 324421432 676 2022-08-11 18:40:00 2022-11-19 18:40:00 324421432 677 2022-09-14 16:00:00 2022-12-23 16:00:00 324421432 678 2022-10-18 13:20:00 2023-01-26 13:20:00 -232 679 2022-11-21 10:40:00 2023-03-01 10:40:00 -232 680 2022-12-25 08:00:00 2023-04-04 08:00:00 -232 681 2023-11-29 05:20:00 2024-03-08 05:20:00 -3892323 682 2024-01-02 02:40:00 2024-04-11 02:40:00 -3892323 683 2024-02-05 00:00:00 2024-05-15 00:00:00 -3892323 684 2024-09-29 05:20:00 2025-01-07 05:20:00 0 685 2024-11-02 02:40:00 2025-02-10 02:40:00 0 686 select _wstart, _wend, min(col2) from temporary01 where ts >= '2020-10-10 09:09:09' and ts <= '2024-12-12 01:34:46' interval(ts, 365, day) sliding(200, day) fill (linear); 687 688 drop table temporary01; 689 drop table if exists temporary02; 690 create table temporary02 (ts timestamp primary key , col2 double, col3 double, col4 float); 691 insert into temporary02 values ('2023-11-01 00:00:01.000', 100.23242134, 73823.90902, 28392432); 692 insert into temporary02 values ('2023-11-01 00:00:03.000', null, null, null); 693 insert into temporary02 values ('2023-11-01 00:00:04.000', -3921.32421, -3290.32, null); 694 insert into temporary02 values ('2023-11-01 00:00:05.000', -4324, 432424.4324234, 0); 695 insert into temporary02 values ('2023-11-01 00:00:06.000', -38249382324324.990, 4032.432, 90909); 696 insert into temporary02 values ('2023-11-01 00:00:07.000', 212112.3233, 9302, -392032); 697 insert into temporary02 values ('2023-11-01 00:00:08.000', 0, 0, 0); 698 insert into temporary02 values ('2023-11-01 00:00:10.000', 0, null, null); 699 insert into temporary02 values ('2023-11-01 00:00:11.000', 9999.999, 382.343, -32932); 700 insert into temporary02 values ('2023-11-01 00:00:12.000', null, null, -23.4324324); 701 insert into temporary02 values ('2023-11-01 00:01:13.000', -0.1, 328903.32334, 909); 702 insert into temporary02 values ('2023-11-01 00:13:14.000', null, 392432.4324, 8932); 703 select * from temporary02; 704 ts col2 col3 col4 705 2023-11-01 00:00:01 100.23242134 73823.90902 2.8392432E7 706 2023-11-01 00:00:03 null null null 707 2023-11-01 00:00:04 -3921.32421 -3290.32 null 708 2023-11-01 00:00:05 -4324.0 432424.4324234 0.0 709 2023-11-01 00:00:06 -3.824938232432499E13 4032.432 90909.0 710 2023-11-01 00:00:07 212112.3233 9302.0 -392032.0 711 2023-11-01 00:00:08 0.0 0.0 0.0 712 2023-11-01 00:00:10 0.0 null null 713 2023-11-01 00:00:11 9999.999 382.343 -32932.0 714 2023-11-01 00:00:12 null null -23.432432 715 2023-11-01 00:01:13 -0.1 328903.32334 909.0 716 2023-11-01 00:13:14 null 392432.4324 8932.0 717 select _wstart, _wend, max(col2), min(col3) from temporary02 where ts >= '2023-11-01 00:00:01.000' and ts <= '2023-11-01 00:13:14.000' interval(ts, 3, second); 718 _wstart _wend max(col2) min(col3) 719 2023-11-01 00:00:00 2023-11-01 00:00:03 100.23242134 73823.90902 720 2023-11-01 00:00:03 2023-11-01 00:00:06 -3921.32421 -3290.32 721 2023-11-01 00:00:06 2023-11-01 00:00:09 212112.3233 0.0 722 2023-11-01 00:00:09 2023-11-01 00:00:12 9999.999 382.343 723 2023-11-01 00:00:12 2023-11-01 00:00:15 null null 724 2023-11-01 00:01:12 2023-11-01 00:01:15 -0.1 328903.32334 725 2023-11-01 00:13:12 2023-11-01 00:13:15 null 392432.4324 726 select _wstart, _wend, avg(col2), count(col4) from temporary02 where ts >= '2023-11-01 00:00:01.000' and ts <= '2023-11-01 00:13:14.000' interval(ts, 2, second); 727 _wstart _wend avg(col2) count(col4) 728 2023-11-01 00:00:00 2023-11-01 00:00:02 100.23242134 1 729 2023-11-01 00:00:02 2023-11-01 00:00:04 null 0 730 2023-11-01 00:00:04 2023-11-01 00:00:06 -4122.662105 1 731 2023-11-01 00:00:06 2023-11-01 00:00:08 -1.9124691056106336E13 2 732 2023-11-01 00:00:08 2023-11-01 00:00:10 0.0 1 733 2023-11-01 00:00:10 2023-11-01 00:00:12 4999.9995 1 734 2023-11-01 00:00:12 2023-11-01 00:00:14 null 1 735 2023-11-01 00:01:12 2023-11-01 00:01:14 -0.1 1 736 2023-11-01 00:13:14 2023-11-01 00:13:16 null 1 737 select _wstart, _wend, avg(col2), count(col4) from temporary02 where ts >= '2023-11-01 00:00:01.000' and ts <= '2023-11-01 00:13:14.000' interval(ts, 2, second) fill(none); 738 _wstart _wend avg(col2) count(col4) 739 2023-11-01 00:00:00 2023-11-01 00:00:02 100.23242134 1 740 2023-11-01 00:00:02 2023-11-01 00:00:04 null 0 741 2023-11-01 00:00:04 2023-11-01 00:00:06 -4122.662105 1 742 2023-11-01 00:00:06 2023-11-01 00:00:08 -1.9124691056106336E13 2 743 2023-11-01 00:00:08 2023-11-01 00:00:10 0.0 1 744 2023-11-01 00:00:10 2023-11-01 00:00:12 4999.9995 1 745 2023-11-01 00:00:12 2023-11-01 00:00:14 null 1 746 2023-11-01 00:01:12 2023-11-01 00:01:14 -0.1 1 747 2023-11-01 00:13:14 2023-11-01 00:13:16 null 1 748 select _wstart, _wend, avg(col2), count(col4) from temporary02 where ts >= '2023-11-01 00:00:01.000' and ts <= '2023-11-01 00:13:14.000' interval(ts, 2, second) fill(prev); 749 _wstart _wend avg(col2) count(col4) 750 2023-11-01 00:00:00 2023-11-01 00:00:02 100.23242134 1 751 2023-11-01 00:00:02 2023-11-01 00:00:04 100.23242134 0 752 2023-11-01 00:00:04 2023-11-01 00:00:06 -4122.662105 1 753 2023-11-01 00:00:06 2023-11-01 00:00:08 -1.9124691056106336E13 2 754 2023-11-01 00:00:08 2023-11-01 00:00:10 0.0 1 755 2023-11-01 00:00:10 2023-11-01 00:00:12 4999.9995 1 756 2023-11-01 00:00:12 2023-11-01 00:00:14 4999.9995 1 757 2023-11-01 00:01:12 2023-11-01 00:01:14 -0.1 1 758 2023-11-01 00:13:14 2023-11-01 00:13:16 -0.1 1 759 select _wstart, _wend, avg(col2), count(col4) from temporary02 where ts >= '2023-11-01 00:00:01.000' and ts <= '2023-11-01 00:13:14.000' interval(ts, 2, second) fill(linear); 760 _wstart _wend avg(col2) count(col4) 761 2023-11-01 00:00:00 2023-11-01 00:00:02 100.23242134 1 762 2023-11-01 00:00:02 2023-11-01 00:00:04 -2011.21484183 0 763 2023-11-01 00:00:04 2023-11-01 00:00:06 -4122.662105 1 764 2023-11-01 00:00:06 2023-11-01 00:00:08 -1.9124691056106336E13 2 765 2023-11-01 00:00:08 2023-11-01 00:00:10 0.0 1 766 2023-11-01 00:00:10 2023-11-01 00:00:12 4999.9995 1 767 2023-11-01 00:00:12 2023-11-01 00:00:14 2499.9497499999998 1 768 2023-11-01 00:01:12 2023-11-01 00:01:14 -0.1 1 769 2023-11-01 00:13:14 2023-11-01 00:13:16 null 1 770 select _wstart, _wend, sum(col3), count(col4) from temporary02 where ts >= '2023-11-01 00:00:01.000' and ts <= '2023-11-01 00:13:14.000' interval(ts, 1, minute); 771 _wstart _wend sum(col3) count(col4) 772 2023-11-01 00:00:00 2023-11-01 00:01:00 516674.7964434 7 773 2023-11-01 00:01:00 2023-11-01 00:02:00 328903.32334 1 774 2023-11-01 00:13:00 2023-11-01 00:14:00 392432.4324 1 775 select _wstart, _wend, sum(col3), count(col4) from temporary02 where ts >= '2023-11-01 00:00:01.000' and ts <= '2023-11-01 00:13:14.000' interval(ts, 60, second); 776 _wstart _wend sum(col3) count(col4) 777 2023-11-01 00:00:00 2023-11-01 00:01:00 516674.7964434 7 778 2023-11-01 00:01:00 2023-11-01 00:02:00 328903.32334 1 779 2023-11-01 00:13:00 2023-11-01 00:14:00 392432.4324 1 780 select _wstart, _wend, sum(col3), count(col4) from temporary02 where ts >= '2023-11-01 00:00:01.000' and ts <= '2023-11-01 00:13:14.000' interval(ts, 5, second) sliding(4, second) fill(value, 2190); 781 _wstart _wend sum(col3) count(col4) 782 2023-11-01 00:00:00 2023-11-01 00:00:05 70533.58902 1 783 2023-11-01 00:00:04 2023-11-01 00:00:09 442468.54442339996 4 784 2023-11-01 00:00:08 2023-11-01 00:00:13 382.343 3 785 2023-11-01 00:00:12 2023-11-01 00:00:17 2190.0 1 786 2023-11-01 00:01:12 2023-11-01 00:01:17 328903.32334 1 787 2023-11-01 00:13:12 2023-11-01 00:13:17 392432.4324 1 788 drop table temporary02; 789 drop table if exists external01; 790 create external table external01(ts timestamp primary key,col1 tinyint default null,col2 smallint default null,col3 int default null,col4 bigint default null,col5 tinyint unsigned default null,col6 smallint unsigned default null,col7 int unsigned default null,col8 bigint unsigned default null,col9 float default null,col10 double default null)infile{"filepath"='$resources/external_table_file/time_window.csv'} fields terminated by ',' enclosed by '\"' lines terminated by '\n'; 791 select _wstart, _wend, max(col2), min(col3), avg(col4) from external01 where ts >= '2014-01-01 00:01:17' and ts <= '2014-01-01 00:14:58' interval(ts, 3, minute); 792 _wstart _wend max(col2) min(col3) avg(col4) 793 2014-01-01 00:00:00 2014-01-01 00:03:00 20830 -563708311 7.5995421344092E17 794 2014-01-01 00:03:00 2014-01-01 00:06:00 31411 -1952462290 -3.2704837977826857E18 795 2014-01-01 00:12:00 2014-01-01 00:15:00 22609 -1679063036 3.1372962123550147E18 796 select _wstart, _wend, count(col2), sum(col6), avg(col7) from external01 where ts >= '2014-01-01 00:01:17' and ts <= '2014-01-01 00:14:58' interval(ts, 10, minute) sliding(5,minute); 797 _wstart _wend count(col2) sum(col6) avg(col7) 798 2014-01-01 00:00:00 2014-01-01 00:10:00 6 305259 2.183710815E9 799 2014-01-01 00:05:00 2014-01-01 00:15:00 5 214104 2.759324657E9 800 2014-01-01 00:10:00 2014-01-01 00:20:00 4 154055 2.87065871975E9 801 select _wstart, _wend, count(col2), sum(col10) from external01 where ts >= '2014-01-01 00:01:17' and ts <= '2014-01-01 00:14:58' interval(ts, 15, minute) sliding(10,minute); 802 _wstart _wend count(col2) sum(col10) 803 2014-01-01 00:00:00 2014-01-01 00:15:00 10 48.22709999999999 804 2014-01-01 00:10:00 2014-01-01 00:25:00 4 16.676199999999998 805 select _wstart, _wend, count(col2), sum(col10) from external01 where ts >= '2014-01-01 00:01:17' and ts <= '2014-01-01 00:14:58' interval(ts, 15, minute) sliding(10,minute) fill(value,1000); 806 _wstart _wend count(col2) sum(col10) 807 2014-01-01 00:00:00 2014-01-01 00:15:00 10 48.22709999999999 808 2014-01-01 00:10:00 2014-01-01 00:25:00 4 16.676199999999998 809 drop table external01; 810 drop table if exists tt1; 811 create table tt1(ts timestamp primary key, a int); 812 select max(a) as enable, min(a) as collation from tt1 interval(ts, 1, minute); 813 enable 814 drop database time_window;