github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/window/window.sql (about) 1 drop table if exists t1; 2 create table t1 (a int, b datetime); 3 insert into t1 values(1, '2020-11-11'), (2, '2020-11-12'), (3, '2020-11-13'); 4 select sum(a) over(partition by a order by b range between interval 1 day preceding and interval 2 day following) from t1; 5 drop table t1; 6 7 drop table if exists t1; 8 create table t1 (a int, b date); 9 insert into t1 values(1, '2020-11-11'), (2, '2020-11-12'), (3, '2020-11-13'), (1, '2020-11-11'), (2, '2020-11-12'), (3, '2020-11-13'), (1, '2020-11-11'), (2, '2020-11-12'), (3, '2020-11-13'); 10 select max(a) over(order by b range between interval 1 day preceding and interval 2 day following) from t1; 11 drop table t1; 12 13 drop table if exists t1; 14 create table t1 (a int, b time); 15 insert into t1 values(1, 112233), (2, 122233), (3, 132233), (1, 112233), (2, 122233), (3, 132233), (1, 112233), (2, 122233), (3, 132233); 16 select min(a) over(order by b range between interval 1 hour preceding and current row) from t1; 17 drop table t1; 18 19 drop table if exists t1; 20 create table t1 (a int, b timestamp); 21 insert into t1 values(1, '2020-11-11'), (2, '2020-11-12'), (3, '2020-11-13'), (1, '2020-11-11'), (2, '2020-11-12'), (3, '2020-11-13'), (1, '2020-11-11'), (2, '2020-11-12'), (3, '2020-11-13'); 22 select count(*) over(order by b range current row) from t1; 23 drop table t1; 24 25 drop table if exists t1; 26 create table t1 (a int, b int, c int); 27 insert into t1 values(1, 2, 1), (3, 4, 2), (5, 6, 3), (7, 8, 4), (3, 4, 5), (3, 4, 6), (3, 4, 7); 28 select a, rank() over (partition by a) from t1 group by a, c; 29 select a, c, rank() over (partition by a order by c) from t1 group by a, c; 30 select a, c, rank() over (partition by a order by c) from t1 group by a, c; 31 select a, c, b, rank() over (partition by a, c, b) from t1; 32 select a, b, rank() over (partition by a, b) from t1; 33 select a, c, sum(a) over (), sum(c) over () from t1; 34 select a, c, sum(a) over (order by c), sum(c) over (order by a) from t1; 35 select a, sum(b), sum(sum(b)) over (partition by a), sum(sum(b)) over (partition by c) from t1 group by a, c; 36 select a, sum(b), rank() over (partition by a +1), rank() over (partition by c), c from t1 group by a, c; 37 select a, sum(b), sum(sum(b)) over (partition by a) as o from t1 group by a, c; 38 select a, sum(b), cast(sum(sum(b)) over (partition by a+1 order by a+1 rows between 2 preceding and CURRENT row) as float) as o from t1 group by a, c; 39 select a, sum(b), sum(sum(b)) over (partition by a rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) from 40 t1 group by a, c; 41 select a, sum(a) over (partition by c order by b range BETWEEN 3 preceding and 4 following), c, b from t1; 42 select a, sum(a) over (order by a) from t1; 43 select a, rank() over (partition by a) from t1; 44 select a, rank() over () from t1; 45 select a, sum(a) over (partition by a rows current row) from t1; 46 select c, sum(c) over (order by c range between 1 preceding and 1 following) from t1; 47 select c, sum(100) over (order by c range between 1 preceding and 1 following), a, b from t1; 48 select c, sum(null) over (order by c range between 1 preceding and 1 following), a, b from t1; 49 select a, b, c, rank() over (partition by a, b order by c) from t1; 50 select a, c, rank() over(partition by a order by c rows current row) from t1; 51 select a, row_number() over (partition by a) from t1 group by a, c; 52 select a, c, row_number() over (partition by a order by c) from t1 group by a, c; 53 select a, c, row_number() over (partition by a order by c) from t1 group by a, c; 54 select a, c, b, row_number() over (partition by a, c, b) from t1; 55 select a, b, row_number() over (partition by a, b) from t1; 56 select a, sum(b), row_number() over (partition by a +1), row_number() over (partition by c), c from t1 group by a, c; 57 select a, row_number() over (partition by a) from t1; 58 select a, row_number() over () from t1; 59 select a, b, c, row_number() over (partition by a, b order by c) from t1; 60 select a, c, row_number() over(partition by a order by c rows current row) from t1; 61 select a, dense_rank() over (partition by a) from t1 group by a, c; 62 select a, c, dense_rank() over (partition by a order by c) from t1 group by a, c; 63 select a, c, dense_rank() over (partition by a order by c) from t1 group by a, c; 64 select a, c, b, dense_rank() over (partition by a, c, b) from t1; 65 select a, b, dense_rank() over (partition by a, b) from t1; 66 select a, sum(b), dense_rank() over (partition by a +1), dense_rank() over (partition by c), c from t1 group by a, c; 67 select a, dense_rank() over (partition by a) from t1; 68 select a, dense_rank() over () from t1; 69 select a, b, c, dense_rank() over (partition by a, b order by c) from t1; 70 select a, c, dense_rank() over(partition by a order by c rows current row) from t1; 71 select a, c, rank() over(order by a), row_number() over(order by a), dense_rank() over(order by a) from t1; 72 drop table t1; 73 74 drop table if exists t1; 75 create table t1 (a int, b decimal(7, 2)); 76 insert into t1 values(1, 12.12), (2, 123.13), (3, 456.66), (4, 1111.34); 77 select a, sum(b) over (partition by a order by a) from t1; 78 drop table t1; 79 80 drop table if exists wf01; 81 create table wf01(i int,j int); 82 insert into wf01 values(1,1); 83 insert into wf01 values(1,4); 84 insert into wf01 values(1,2); 85 insert into wf01 values(1,4); 86 select * from wf01; 87 select i, j, sum(i+j) over (order by j desc rows unbounded preceding) foo from wf01; 88 select i, j, sum(i+j) over (order by j desc rows between 2 preceding and 2 following) as foo from wf01; 89 select i, j, sum(i+j) over (order by j desc rows unbounded preceding) foo from wf01 order by foo; 90 select i, j, sum(i+j) over (order by j desc rows unbounded preceding) foo from wf01 order by foo desc; 91 drop table wf01; 92 93 drop table if exists wf08; 94 create table wf08(d decimal(10,2), date date); 95 insert into wf08 values (10.4, '2002-06-09'); 96 insert into wf08 values (20.5, '2002-06-09'); 97 insert into wf08 values (10.4, '2002-06-10'); 98 insert into wf08 values (3, '2002-06-09'); 99 insert into wf08 values (40.2, '2015-08-01'); 100 insert into wf08 values (40.2, '2002-06-09'); 101 insert into wf08 values (5, '2015-08-01'); 102 select * from (select rank() over (order by d) as `rank`, d, date from wf08) alias order by `rank`, d, date; 103 select * from (select dense_rank() over (order by d) as `d_rank`, d, date from wf08) alias order by `d_rank`, d, date; 104 drop table wf08; 105 106 drop table if exists wf07; 107 create table wf07 (user_id integer not null, date date); 108 insert into wf07 values (1, '2002-06-09'); 109 insert into wf07 values (2, '2002-06-09'); 110 insert into wf07 values (1, '2002-06-09'); 111 insert into wf07 values (3, '2002-06-09'); 112 insert into wf07 values (4, '2002-06-09'); 113 insert into wf07 values (4, '2002-06-09'); 114 insert into wf07 values (5, '2002-06-09'); 115 select rank() over () r from wf07; 116 select dense_rank() over () r from wf07; 117 drop table wf07; 118 119 drop table if exists wf12; 120 create table wf12(d double); 121 insert into wf12 values (1.7976931348623157e+307); 122 insert into wf12 values (1); 123 select d, sum(d) over (rows between current row and 1 following) from wf12; 124 drop table wf12; 125 126 drop table if exists wf06; 127 create table wf06 (id integer, sex char(1)); 128 insert into wf06 values (1, 'm'); 129 insert into wf06 values (2, 'f'); 130 insert into wf06 values (3, 'f'); 131 insert into wf06 values (4, 'f'); 132 insert into wf06 values (5, 'm'); 133 drop table if exists wf07; 134 create table wf07 (user_id integer not null, date date); 135 insert into wf07 values (1, '2002-06-09'); 136 insert into wf07 values (2, '2002-06-09'); 137 insert into wf07 values (1, '2002-06-09'); 138 insert into wf07 values (3, '2002-06-09'); 139 insert into wf07 values (4, '2002-06-09'); 140 insert into wf07 values (4, '2002-06-09'); 141 insert into wf07 values (5, '2002-06-09'); 142 select id value, sum(id) over (rows unbounded preceding) from wf06 inner join wf07 on wf07.user_id = wf06.id; 143 drop table wf06; 144 drop table wf07; 145 146 drop table if exists row01; 147 create table row01(i int,j int); 148 insert into row01 values(1,1); 149 insert into row01 values(1,4); 150 insert into row01 values(1,2); 151 insert into row01 values(1,4); 152 select i, j, sum(i+j) over (order by j rows between 2 preceding and 1 preceding) foo from row01 order by foo desc; 153 select i, j, sum(i+j) over (order by j rows between 2 following and 1 following) foo from row01 order by foo desc; 154 drop table row01; 155 156 drop table if exists test01; 157 create table test01(i int, j int); 158 insert into test01 values (1,null); 159 insert into test01 values (1,null); 160 insert into test01 values (1,1); 161 insert into test01 values (1,null); 162 insert into test01 values (1,2); 163 insert into test01 values (2,1); 164 insert into test01 values (2,2); 165 insert into test01 values (2,null); 166 insert into test01 values (2,null); 167 select i, j, min(j) over (partition by i order by j rows unbounded preceding) from test01; 168 drop table test01; 169 170 drop table if exists double01; 171 create table double01(d double); 172 insert into double01 values (2); 173 insert into double01 values (2); 174 insert into double01 values (3); 175 insert into double01 values (1); 176 insert into double01 values (1); 177 insert into double01 values (1.2); 178 insert into double01 values (null); 179 insert into double01 values (null); 180 select d, sum(d) over (partition by d order by d), avg(d) over (order by d rows between 1 preceding and 1 following) from double01; 181 select d, sum(d) over (partition by d order by d), avg(d) over (order by d rows between 2 preceding and 1 following) from double01; 182 drop table double01; 183 184 drop table if exists wf01; 185 create table wf01(d float); 186 insert into wf01 values (10); 187 insert into wf01 values (1); 188 insert into wf01 values (2); 189 insert into wf01 values (3); 190 insert into wf01 values (4); 191 insert into wf01 values (5); 192 insert into wf01 values (6); 193 insert into wf01 values (7); 194 insert into wf01 values (8); 195 insert into wf01 values (9); 196 select d, sum(d) over (order by d range between current row and 2 following), avg(d) over (order by d range between current row and 2 following) from wf01; 197 select d, sum(d) over (order by d range between 2 preceding and 2 following), avg(d) over (order by d range between current row and 2 following) from wf01; 198 drop table wf01; 199 200 drop table if exists dense_rank01; 201 create table dense_rank01 (id integer, sex char(1)); 202 insert into dense_rank01 values (1, 'm'); 203 insert into dense_rank01 values (2, 'f'); 204 insert into dense_rank01 values (3, 'f'); 205 insert into dense_rank01 values (4, 'f'); 206 insert into dense_rank01 values (5, 'm'); 207 select sex, id, rank() over (partition by sex order by id desc) from dense_rank01; 208 select sex, id, dense_rank() over (partition by sex order by id desc) from dense_rank01; 209 drop table dense_rank01; 210 211 drop table if exists sales; 212 create table sales (customer_id varchar(1), order_date date, product_id integer); 213 insert into sales(customer_id, order_date, product_id) values ('a', '2021-01-01', '1'), ('a', '2021-01-01', '2'), ('a', '2021-01-07', '2'), ('a', '2021-01-10', '3'), ('a', '2021-01-11', '3'), ('a', '2021-01-11', '3'),('b', '2021-01-01', '2'),('b', '2021-01-02', '2'),('b', '2021-01-04', '1'),('b', '2021-01-11', '1'),('b', '2021-01-16', '3'),('b', '2021-02-01', '3'),('c', '2021-01-01', '3'),('c', '2021-01-01', '3'),('c', '2021-01-07', '3'); 214 drop table if exists menu; 215 create table menu (product_id integer,product_name varchar(5),price integer); 216 insert into menu(product_id, product_name, price) values ('1', 'sushi', '10'),('2', 'curry', '15'),('3', 'ramen', '12'); 217 with ordered_sales as (select sales.customer_id, sales.order_date, menu.product_name,dense_rank() over (partition by sales.customer_id order by sales.order_date) as `rank` from sales inner join menu on sales.product_id = menu.product_id) select customer_id, product_name from ordered_sales where `rank` = 1 group by customer_id, product_name; 218 drop table sales; 219 220 drop table if exists test01; 221 create table test01(i int, j int); 222 insert into test01 values (1,null); 223 insert into test01 values (1,null); 224 insert into test01 values (1,1); 225 insert into test01 values (1,null); 226 insert into test01 values (1,2); 227 insert into test01 values (2,1); 228 insert into test01 values (2,2); 229 insert into test01 values (2,null); 230 insert into test01 values (2,null); 231 select rank() over (order by t0.a) as b from (select i as a from test01) as t0; 232 select rank() over(order by j) as col, j from test01; 233 drop table test01; 234 235 drop table if exists wf14; 236 create table wf14 (id integer, sex char(1)); 237 insert into wf14 values (1, 'm'); 238 insert into wf14 values (2, 'f'); 239 insert into wf14 values (3, 'f'); 240 insert into wf14 values (4, 'f'); 241 insert into wf14 values (5, 'm'); 242 insert into wf14 values (10, null); 243 insert into wf14 values (11, null); 244 insert into wf14 values (10, null); 245 select id, sex, sum(id) over (partition by sex order by id rows between 2 preceding and 1 following) as a from wf14; 246 select id, sex, sum(id) over (partition by sex order by id rows between 2 preceding and 1 following) as a from wf14; 247 select id, sex, sum(id) over (partition by sex order by id rows between 2 preceding and 1 following) as a from wf14; 248 drop table wf14; 249 250 -- @suit 251 -- @case 252 -- @desc:window function 253 -- @label:bvt 254 255 drop database if exists test; 256 create database test; 257 use test; 258 259 -- partition by follows the bool type 260 drop table if exists bool01; 261 create table bool01(col1 int,col2 bool,col3 datetime); 262 insert into bool01 values(1, true, '2023-05-16 00:12:12'); 263 insert into bool01 values(2, false, '1997-01-13 12:12:00'); 264 insert into bool01 values(3, true, '2000-10-10 11:11:11'); 265 insert into bool01 values(4, false, '1020-10-01 01:01:01'); 266 insert into bool01 values(5, null, null); 267 insert into bool01 values(6, null, '1997-11-10 10:10:10'); 268 select * from bool01; 269 select rank() over (partition by col2 order by col1), sum(col1) over (partition by col2 order by col3) from bool01; 270 select dense_rank() over (partition by col2 order by col1), sum(col1) over (partition by col2 order by col3) from bool01; 271 272 drop table bool01; 273 274 -- partition by follows char/varchar/text 275 drop table varchar01 if exists; 276 create table varchar01(col1 int, col2 varchar(12) primary key); 277 insert into varchar01 values(1, 'dhwenfewrfew'); 278 insert into varchar01 values(2, 'wyeuijdew'); 279 insert into varchar01 values(3, '数据库'); 280 insert into varchar01 values(4, 'hejwkvrewvre'); 281 insert into varchar01 values(5, '**&'); 282 insert into varchar01 values(6, '12345'); 283 insert into varchar01 values(7, 'database'); 284 select *, rank() over (partition by col2 order by col1) as tmp from varchar01; 285 select dense_rank() over (partition by col2 order by col1) as tmp from varchar01; 286 drop table varchar01; 287 288 drop table if exists char01; 289 create table char01 (col1 integer, col2 char(1)); 290 create table char01 (col1 integer, col2 char(1)); 291 insert into char01 values (1, 'm'); 292 insert into char01 values (2, 'f'); 293 insert into char01 values (3, 'f'); 294 insert into char01 values (4, 'f'); 295 insert into char01 values (5, 'm'); 296 select * from char01; 297 select *, rank() over (partition by col2 order by col1) as tmp from char01; 298 select dense_rank() over (partition by col2 order by col1) as tmp from char01; 299 drop table char01; 300 301 drop table if exists text01; 302 create table text01(col1 int, col2 text); 303 insert into text01 values(1, 'vdjnekwvrewvrjewkrmbew bkejwkvmekrememwkvrewvrew re'); 304 insert into text01 values(2, 'vdjnekwvrewvrjewkrmbew bkejwkvmekrememwkvrewvrew re'); 305 insert into text01 values(3, null); 306 insert into text01 values(4, '数据库，数据库，数据库，mo，mo，mo!'); 307 insert into text01 values(5, null); 308 insert into text01 values(6, '数据库，数据库，数据库，mo，mo，mo!'); 309 insert into text01 values(7, null); 310 select * from text01; 311 select *, rank() over (partition by col2 order by col1) as tmp from text01; 312 select dense_rank() over (partition by col2 order by col1) as tmp from text01; 313 drop table text01; 314 315 -- partition by and order by follows int 316 drop table if exists int01; 317 create table int01(col1 tinyint unsigned, col2 int, col3 timestamp); 318 insert into int01 values(100, 100, '2023-05-16 00:12:12'); 319 insert into int01 values(98, -10, '2023-05-16 00:12:12'); 320 insert into int01 values(100, null, '1997-05-16 00:12:12'); 321 insert into int01 values(null, 100, '2023-05-16 00:12:12'); 322 insert into int01 values(0, null, '1997-05-16 00:12:12'); 323 insert into int01 values(null, null, null); 324 select * from int01; 325 select col1, avg(col2) over (partition by col1 order by col2) as tmp from int01; 326 select col1, sum(col2) over (partition by col2 order by col1) as tmp from int01; 327 select col1, max(col1) over (partition by col1 rows between 1 preceding and 1 following) from int01; 328 select col1, min(col2) over (partition by col3 order by col2) from int01; 329 drop table int01; 330 331 -- partition by and order by follows float 332 drop table if exists float01; 333 create table float01(col1 float, col2 date); 334 insert into float01 values(12434321313.213213,'2020-01-01'); 335 insert into float01 values(null,'1997-01-13'); 336 insert into float01 values(-12434321313.213213,'1000-10-10'); 337 insert into float01 values(null,'2020-01-01'); 338 insert into float01 values(null,null); 339 insert into float01 values(12434321313.213213,null); 340 insert into float01 values(0,'1997-01-13'); 341 insert into float01 values(0,'1000-12-12'); 342 insert into float01 values(12434321313.213213,null); 343 select * from float01; 344 select col2, avg(col1) over (partition by col1 order by col2) as tmp from float01; 345 select col2, sum(col1) over (partition by col2 order by col1) as tmp from float01; 346 select col2, max(col1) over (partition by col1 rows between 1 preceding and 1 following) from float01; 347 select col2, min(col1) over (partition by col2 order by col2) from float01; 348 drop table float01; 349 350 -- partition by and order by follows double 351 drop table if exists double01; 352 create table double01(d double); 353 insert into double01 values (2); 354 insert into double01 values (2); 355 insert into double01 values (3); 356 insert into double01 values (1); 357 insert into double01 values (1); 358 insert into double01 values (1.2); 359 insert into double01 values (null); 360 insert into double01 values (null); 361 select * from double01; 362 select d, sum(d) over (partition by d order by d), avg(d) over (order by d) from double01; 363 select d, sum(d) over (partition by d order by d), avg(d) over (order by d rows between 1 preceding and 1 following) from double01; 364 select d, max(d) over (partition by d) from double01; 365 select d, sum(d) over (partition by d order by d) from double01; 366 truncate double01; 367 select * from double01; 368 insert into double01 values (1.7976931348623157e+307); 369 insert into double01 values (1); 370 select * from double01; 371 select d, sum(d) over (rows between current row and 1 following) from double01; 372 drop table double01; 373 374 -- partition by and order by follows decimal128 375 drop table if exists decimal01; 376 create table decimal01(d decimal(38,3)); 377 insert into decimal01 values (28888888888888888888888888888888888.1234); 378 insert into decimal01 values (99999999999999999999999999999999999.83293323); 379 insert into decimal01 values (0); 380 insert into decimal01 values (-7841512312154312313158786541.342152121242143); 381 insert into decimal01 values (-7841512312154312313158786541.342152121242143); 382 insert into decimal01 values (99999999999999999999999999999999999.83293323); 383 insert into decimal01 values (null); 384 insert into decimal01 values (null); 385 select * from decimal01; 386 387 select max(d) over (partition by d order by d) from decimal01; 388 select min(d) over (partition by d order by d) from decimal01; 389 select avg(d) over (partition by d) from decimal01; 390 -- @bvt:issue#10043 391 select sum(d) over (partition by d order by d rows between 1 preceding and 1 following) from decimal01; 392 -- @bvt:issue 393 drop table decimal01; 394 395 -- partition by and order by follows date 396 drop table if exists date01; 397 create table date01(id date); 398 insert into date01 values ('2002-06-09'); 399 insert into date01 values ('2002-06-09'); 400 insert into date01 values ('2002-06-10'); 401 insert into date01 values ('2002-06-09'); 402 insert into date01 values ('2015-08-01'); 403 insert into date01 values ('2002-06-09'); 404 insert into date01 values ('2015-08-01'); 405 406 select id, rank() over () from date01; 407 select id, dense_rank() over (order by id) from date01; 408 select id, max(id) over (order by id rows 2 preceding) from date01; 409 select min(id) over (partition by id order by id range interval 2 day preceding) from date01; 410 select id, count(id) over (order by id rows between 2 preceding and 1 following) from date01; 411 select id, count(id) over (order by date_add(id,interval 3 day) rows between 2 preceding and 1 following) from date01; 412 413 drop table date01; 414 415 -- check that sum stays that same when it sees null values 416 drop table if exists test01; 417 create table test01(i int, j int); 418 insert into test01 values (1,null); 419 insert into test01 values (1,null); 420 insert into test01 values (1,1); 421 insert into test01 values (1,null); 422 insert into test01 values (1,2); 423 insert into test01 values (2,1); 424 insert into test01 values (2,2); 425 insert into test01 values (2,null); 426 insert into test01 values (2,null); 427 select * from test01; 428 select i, j, sum(j) over (partition by i order by j rows unbounded preceding) from test01; 429 select i, j, avg(j) over (partition by i order by j rows unbounded preceding) from test01; 430 select i, j, max(j) over (partition by i order by j rows unbounded preceding) from test01; 431 select i, j, min(j) over (partition by i order by j rows unbounded preceding) from test01; 432 drop table test01; 433 434 -- rows unbounded preceding,rows unbounded following,current row 435 drop table if exists row01; 436 create table row01(i int,j int); 437 insert into row01 values(1,1); 438 insert into row01 values(1,4); 439 insert into row01 values(1,2); 440 insert into row01 values(1,4); 441 select * from row01; 442 443 -- single partition 444 select i, j, sum(i+j) over (rows unbounded preceding) foo from row01; 445 select i, j, sum(i+j) over (rows between unbounded preceding and current row) foo from row01; 446 select i, j, sum(i+j) over (rows unbounded preceding) foo from row01 order by foo; 447 select i, j, sum(i+j) over (rows unbounded preceding) foo from row01 order by foo desc; 448 449 select i, j, sum(i+j) over (order by j desc rows unbounded preceding) foo from row01; 450 select i, j, sum(i+j) over (order by j desc rows between 2 preceding and 2 following) as foo from row01; 451 select i, j, sum(i+j) over (order by j desc rows unbounded preceding) foo from row01 order by foo; 452 select i, j, sum(i+j) over (order by j desc rows unbounded preceding) foo from row01 order by foo desc; 453 454 -- with limit 455 select i, j, sum(i+j) over (rows unbounded preceding) foo from row01 order by foo desc limit 3; 456 -- with order by 457 select i, j, sum(i+j) over (order by j rows unbounded preceding) foo from row01; 458 select i, j, sum(i+j) over (order by j rows unbounded preceding) foo from row01 order by foo; 459 select i, j, sum(i+j) over (order by j rows unbounded preceding) foo from row01 order by foo desc; 460 461 select i, j, sum(i+j) over (order by j rows between 2 preceding and 1 preceding) foo from row01 order by foo desc; 462 select i, j, sum(i+j) over (order by j rows between 2 following and 1 following) foo from row01 order by foo desc; 463 464 -- abnormal test 465 select i, j, sum(i+j) over (order by j rows between -1 following and 1 following) foo from row01 order by foo desc; 466 select i, j, sum(i+j) over (order by j rows between 2 preceding and -10 following) foo from row01 order by foo desc; 467 drop table row01; 468 469 -- order by i rows between 2 preceding and 2 following 470 drop table if exists wf02; 471 create table wf02 (i int) ; 472 insert into wf02 (i) values (1); 473 insert into wf02 (i) values (2); 474 insert into wf02 (i) values (3); 475 insert into wf02 (i) values (4); 476 insert into wf02 (i) values (5); 477 select * from wf02; 478 select i, sum(i) over (rows between 0 preceding and 2 following) from wf02; 479 select i, sum(i) over (order by i rows between 2 preceding and 2 following) from wf02 limit 3; 480 select i, sum(i * 20) over (rows between 2 preceding and 2 following) from wf02 order by i desc limit 3; 481 select i, avg(i) over (rows between 2 preceding and 2 following) from wf02; 482 select i, avg(i + 100) over (rows between 2 preceding and 2 following) from wf02; 483 select i, sum(i) over (rows between 1 preceding and 2 following) from wf02; 484 485 drop table wf02; 486 487 -- order by and group by 488 drop table if exists og01; 489 create table og01(i int, j int, k int); 490 insert into og01 values (1,1,1); 491 insert into og01 values (1,4,1); 492 insert into og01 values (1,2,1); 493 insert into og01 values (1,4,1); 494 insert into og01 values (1,1,2); 495 insert into og01 values (1,4,2); 496 insert into og01 values (1,2,2); 497 insert into og01 values (1,4,2); 498 insert into og01 values (1,1,3); 499 insert into og01 values (1,4,3); 500 insert into og01 values (1,2,3); 501 insert into og01 values (1,4,3); 502 insert into og01 values (1,1,4); 503 insert into og01 values (1,4,4); 504 insert into og01 values (1,2,4); 505 insert into og01 values (1,4,4); 506 select * from og01; 507 508 select k, sum(k) over (rows unbounded preceding) wf from og01; 509 -- combined with group by 510 select k, min(i), sum(j), sum(k) over (rows unbounded preceding) wf from og01 group by (k); 511 select k, min(i), sum(j), sum(k) over (rows unbounded preceding) wf from og01 group by (k) order by wf desc; 512 513 select k, sum(k) over (rows unbounded preceding) foo from og01 group by (k); 514 select k, avg(distinct j), sum(k) over (rows unbounded preceding) foo from og01 group by (k); 515 516 -- expression argument to sum 517 select k, sum(k+1) over (rows unbounded preceding) foo from og01 group by (k); 518 select k, sum(k+1) over (order by k desc rows unbounded preceding) foo from og01 group by (k); 519 drop table og01; 520 521 drop table if exists og02; 522 create table og02 (id integer, sex char(1)); 523 insert into og02 values (1, 'm'); 524 insert into og02 values (2, 'f'); 525 insert into og02 values (3, 'f'); 526 insert into og02 values (4, 'f'); 527 insert into og02 values (5, 'm'); 528 insert into og02 values (10, null); 529 insert into og02 values (11, null); 530 select * from og02; 531 532 drop table if exists og03; 533 create table og03(c char(1)); 534 insert into og03 values ('m'); 535 select * from og03; 536 537 select sex, avg(id), row_number() over (partition by sex) from og02 538 group by sex order by sex desc; 539 540 select sex, avg(id), row_number() over (partition by sex) from og02 541 group by sex order by sex desc; 542 543 select sex, avg(id), sum(avg(id) + 10) over (rows unbounded preceding) from og02 544 group by sex order by sex desc; 545 546 select sex, avg(id), row_number() over (partition by sex) from og02 547 group by sex having sex='m' or sex is null order by sex desc; 548 549 select sex, avg(id), sum(avg(id)) over (rows unbounded preceding) from og02 550 group by sex having sex='m' or sex='f' or sex is null 551 order by sex desc; 552 553 -- having using subquery 554 select sex, avg(id), row_number() over (partition by sex) from og02 555 group by sex having sex=(select c from og03 limit 1) or sex is null 556 order by sex desc; 557 558 select sex, avg(id), sum(avg(id)) over (rows unbounded preceding) from og02 559 group by sex having sex=(select c from og03 limit 1) or sex='f' or sex is null 560 order by sex desc; 561 562 -- sum 563 select sex, avg(id), sum(avg(id)) over (order by sex rows unbounded preceding) from og02 564 group by sex 565 order by sex desc; 566 567 select sex, avg(id), sum(avg(id)) over (order by sex rows unbounded preceding) from og02 568 group by sex having sex=(select c from og03 limit 1) or sex='f' or sex is null 569 order by sex desc; 570 571 drop table og02; 572 drop table og03; 573 574 -- The date function in the window is nested with the date column in the table; 575 drop table if exists date02; 576 create table date02(col1 date,col2 datetime, col3 time, col4 timestamp); 577 insert into date02 values ('2002-06-09','1997-01-13 00:00:00','12:00:59','2023-05-16 00:12:12'); 578 insert into date02 values ('2002-06-09','2020-02-20 00:00:00','11:12:12','2023-05-18 12:12:12'); 579 insert into date02 values ('2002-06-10','1997-01-13 00:00:00','12:00:59','2023-05-16 00:12:12'); 580 insert into date02 values ('2002-06-09','2020-02-20 00:00:00','11:12:12','2023-05-16 00:12:12'); 581 insert into date02 values ('2015-08-01',null,null,'2023-05-18 12:12:12'); 582 insert into date02 values ('2002-06-09',null,'01:01:01',null); 583 insert into date02 values ('2015-08-01','1990-01-01 01:02:03',null,null); 584 select * from date02; 585 586 -- nested with time function in windows: 587 select dense_rank() over (partition by col1 order by date_format(col1,'%m-%d-%Y')) from date02; 588 select max(col2) over (partition by col3 order by date(col2) desc) from date02; 589 select rank() over (order by col1 range interval 2 day preceding) from date02; 590 select max(col3) over (order by date_add(col2,interval 2 minute) rows between 2 preceding and 1 following) from date02; 591 select min(col3) over (partition by col4 order by date_sub(col2,interval 2 minute) rows between 2 preceding and 1 following) from date02; 592 select max(col3) over (order by year(col2) rows between current row and unbounded following) from date02; 593 select dense_rank() over (order by month(col3)) from date02; 594 drop table date02; 595 596 -- rank,dense_rank 597 drop table if exists dense_rank01; 598 create table dense_rank01 (id integer, sex char(1)); 599 insert into dense_rank01 values (1, 'm'); 600 insert into dense_rank01 values (2, 'f'); 601 insert into dense_rank01 values (3, 'f'); 602 insert into dense_rank01 values (4, 'f'); 603 insert into dense_rank01 values (5, 'm'); 604 select * from dense_rank01; 605 606 drop table if exists dense_rank02; 607 create table dense_rank02 (user_id integer not null, date date); 608 insert into dense_rank02 values (1, '2002-06-09'); 609 insert into dense_rank02 values (2, '2002-06-09'); 610 insert into dense_rank02 values (1, '2002-06-09'); 611 insert into dense_rank02 values (3, '2002-06-09'); 612 insert into dense_rank02 values (4, '2002-06-09'); 613 insert into dense_rank02 values (4, '2002-06-09'); 614 insert into dense_rank02 values (5, '2002-06-09'); 615 select * from dense_rank02; 616 617 -- rank, dense_rank 618 select rank() over (order by user_id) r from dense_rank02; 619 select dense_rank() over (order by user_id) r from dense_rank02; 620 621 -- same, without order by 622 select rank() over () r from dense_rank02; 623 select dense_rank() over () r from dense_rank02; 624 625 -- with order by 626 select id, sex, rank() over (order by sex rows unbounded preceding) from dense_rank01 order by id; 627 select id, sex, dense_rank() over (order by sex rows unbounded preceding) from dense_rank01 order by id; 628 629 select sex, rank() over (order by sex desc rows unbounded preceding) `rank`, avg(distinct id) as uids from dense_rank01 u, dense_rank02 630 where dense_rank02.user_id = u.id group by sex order by sex; 631 select sex, dense_rank() over (order by sex desc rows unbounded preceding) `rank`, avg(distinct id) as uids from dense_rank01 u, dense_rank02 632 where dense_rank02.user_id = u.id group by sex order by sex; 633 634 -- window desc ordering by group by 635 select sex, avg(id) as uids, rank() over (order by avg(id)) `rank` from dense_rank01 u, dense_rank02 636 where dense_rank02.user_id = u.id group by sex; 637 select sex, avg(id) as uids, dense_rank() over (order by avg(id)) `rank` from dense_rank01 u, dense_rank02 638 where dense_rank02.user_id = u.id group by sex; 639 640 -- window ordering by distinct group by 641 select sex, avg(distinct id) as uids, rank() over (order by avg(distinct id) desc) `rank` from dense_rank01 u, dense_rank02 642 where dense_rank02.user_id = u.id group by sex 643 order by sex; 644 select sex, avg(distinct id) as uids, dense_rank() over (order by avg(distinct id) desc) `p_rank` from dense_rank01 u, dense_rank02 645 where dense_rank02.user_id = u.id group by sex 646 order by sex; 647 648 -- window ordering by group by, final order by 649 select sex, avg(id) as uids, rank() over (order by avg(id) desc) `rank` from dense_rank01 u, dense_rank02 650 where dense_rank02.user_id = u.id group by sex 651 order by `rank` desc; 652 653 -- sorted result 654 select sex, avg(id) as uids, dense_rank() over (order by avg(id) desc) `p_rank` 655 from dense_rank01 u, dense_rank02 656 where dense_rank02.user_id = u.id group by sex 657 order by `p_rank` desc; 658 659 -- echo with nulls 660 insert into dense_rank01 values (10, null); 661 insert into dense_rank01 values (11, null); 662 663 select id, sex, rank() over (order by sex rows unbounded preceding)from dense_rank01 order by id; 664 select id, sex, dense_rank() over (order by sex rows unbounded preceding) from dense_rank01 order by id; 665 select id, sex, rank() over (order by sex desc rows unbounded preceding) from dense_rank01 order by id; 666 667 -- left join, right join, inner join, natural join, full join 668 select id value, 669 sum(id) over (rows unbounded preceding) 670 from dense_rank01 left join dense_rank02 on dense_rank02.user_id = dense_rank01.id; 671 672 select id value, 673 sum(id) over (rows unbounded preceding) 674 from dense_rank01 right join dense_rank02 on dense_rank02.user_id = dense_rank01.id; 675 676 select id value, 677 sum(id) over (rows unbounded preceding) 678 from dense_rank01 inner join dense_rank02 on dense_rank02.user_id = dense_rank01.id; 679 680 select id value, 681 sum(id) over (partition by id order by id rows unbounded preceding) 682 from dense_rank01 natural join dense_rank02; 683 684 select id value, 685 sum(id) over (partition by id order by id rows unbounded preceding) 686 from dense_rank01 full join dense_rank02; 687 688 689 -- aggregate with group by in window's order by clause 690 select sex, avg(id), rank() over (order by avg(id) desc) from dense_rank01 group by sex order by sex; 691 select sex, dense_rank() over (order by avg(id) desc) from dense_rank01 group by sex order by sex; 692 select sex, rank() over (order by avg(id) desc) from dense_rank01 group by sex order by sex; 693 694 -- implicit group aggregate arguments to window function and in 695 -- window's order by clause 696 select rank() over (order by avg(id)) from dense_rank01; 697 select dense_rank() over (order by avg(id)) from dense_rank01; 698 select avg(id), rank() over (order by avg(id)) from dense_rank01; 699 select avg(id), dense_rank() over (order by avg(id)) from dense_rank01; 700 select avg(id), sum(avg(id)) over (order by avg(id) rows unbounded preceding) from dense_rank01; 701 702 -- echo several partitions, several window functions over the same window 703 select sex, id, rank() over (partition by sex order by id desc) from dense_rank01; 704 select sex, id, dense_rank() over (partition by sex order by id desc) from dense_rank01; 705 select sex, id, rank() over (partition by sex order by id asc) from dense_rank01; 706 select sex, id, dense_rank() over (partition by sex order by id asc) from dense_rank01; 707 select sex, id, sum(id) over (partition by sex order by id asc rows unbounded preceding) summ, 708 rank() over (partition by sex order by id asc rows unbounded preceding) `rank` from dense_rank01; 709 select sex, id, sum(id) over (partition by sex order by id asc rows unbounded preceding) summ, 710 dense_rank() over (partition by sex order by id asc rows unbounded preceding) `d_rank` from dense_rank01; 711 select sex, id, sum(id) over (partition by sex order by id asc rows unbounded preceding) summ, 712 rank() over (partition by sex order by id asc rows unbounded preceding) `rank` from dense_rank01 713 order by summ; 714 select sex, id, sum(id) over (partition by sex order by id asc rows unbounded preceding) summ, 715 dense_rank() over (partition by sex order by id asc rows unbounded preceding) `p_rank` from dense_rank01 716 order by summ; 717 718 -- error test:window specification's order by or partition by cannot reference select list aliases 719 select sex, avg(distinct id),rank() over (order by uids desc) `uids` 720 from dense_rank01 u, dense_rank01 where dense_rank01.user_id = u.id group by sex 721 order by sex; 722 select sex, avg(distinct id),rank() over (order by uids desc) `uids` 723 from dense_rank01 u, dense_rank02 where dense_rank02.user_id = u.id 724 group by sex order by sex; 725 726 drop table dense_rank01; 727 drop table dense_rank02; 728 729 drop table if exists dense_rank03; 730 create table dense_rank03(d decimal(10,2), date date); 731 insert into dense_rank03 values (10.4, '2002-06-09'); 732 insert into dense_rank03 values (20.5, '2002-06-09'); 733 insert into dense_rank03 values (10.4, '2002-06-10'); 734 insert into dense_rank03 values (3, '2002-06-09'); 735 insert into dense_rank03 values (40.2, '2015-08-01'); 736 insert into dense_rank03 values (40.2, '2002-06-09'); 737 insert into dense_rank03 values (5, '2015-08-01'); 738 select * from dense_rank03; 739 740 select * from (select rank() over (order by d) as `rank`, d, date from dense_rank03) alias order by `rank`, d, date; 741 select * from (select dense_rank() over (order by d) as `d_rank`, d, date from dense_rank03) alias order by `d_rank`, d, date; 742 select * from (select rank() over (order by date) as `rank`, date, d from dense_rank03) alias order by `rank`, d desc; 743 select * from (select dense_rank() over (order by date) as `p_rank`, date, d from dense_rank03) alias order by `p_rank`, d desc; 744 drop table dense_rank03; 745 746 -- order by + rank with more than one ordering expression 747 drop table if exists rank01; 748 create table rank01(i int, j int, k int); 749 insert into rank01 values (1,1,1); 750 insert into rank01 values (1,1,2); 751 insert into rank01 values (1,1,2); 752 insert into rank01 values (1,2,1); 753 insert into rank01 values (1,2,2); 754 insert into rank01 values (2,1,1); 755 insert into rank01 values (2,1,1); 756 insert into rank01 values (2,1,2); 757 insert into rank01 values (2,2,1); 758 insert into rank01 values (2,2,2); 759 select * from rank01; 760 select *, rank() over (order by i,j,k) as o_ijk, 761 rank() over (order by j) as o_j, 762 rank() over (order by k,j) as o_kj from rank01 order by i,j,k; 763 drop table rank01; 764 765 -- row_number tests 766 drop table if exists row_number01; 767 create table row_number01 (id integer, sex char(1)); 768 insert into row_number01 values (1, 'm'); 769 insert into row_number01 values (2, 'f'); 770 insert into row_number01 values (3, 'f'); 771 insert into row_number01 values (4, 'f'); 772 insert into row_number01 values (5, 'm'); 773 select * from row_number01; 774 drop table if exists row_number02; 775 create table row_number02 (user_id integer not null, date date); 776 insert into row_number02 values (1, '2002-06-09'); 777 insert into row_number02 values (2, '2002-06-09'); 778 insert into row_number02 values (1, '2002-06-09'); 779 insert into row_number02 values (3, '2002-06-09'); 780 insert into row_number02 values (4, '2002-06-09'); 781 insert into row_number02 values (4, '2002-06-09'); 782 insert into row_number02 values (5, '2002-06-09'); 783 select * from row_number02; 784 select user_id, row_number() over (partition by user_id) from row_number02 row_number01; 785 select sex, id, date, row_number() over (partition by date order by id) as row_no, rank() over (partition by date order by id) as `rank` from row_number01,row_number02 786 where row_number01.id=row_number02.user_id; 787 788 -- window function in subquery 789 select date,id, rank() over (partition by date order by id) as `rank` from row_number01,row_number02; 790 select * from (select date,id, rank() over (partition by date order by id) as `rank` from row_number01,row_number02) alias; 791 select * from (select date,id, dense_rank() over (partition by date order by id) as `p_rank` from row_number01,row_number02) t; 792 793 -- multiple windows 794 select row_number01.*, rank() over (order by sex rows unbounded preceding), sum(id) over (order by sex,id rows unbounded preceding) from row_number01; 795 select row_number01.*, dense_rank() over (order by sex rows unbounded preceding), sum(id) over (order by sex,id rows unbounded preceding) from row_number01; 796 select * from (select row_number01.*, sum(id) over (rows unbounded preceding), rank() over (order by sex rows unbounded preceding) from row_number01) alias order by id; 797 select * from (select row_number01.*, sum(id) over (rows unbounded preceding), dense_rank() over (order by sex rows unbounded preceding) from row_number01) alias order by id; 798 799 -- sorted results 800 select row_number01.*, sum(id) over (order by id rows unbounded preceding), 801 rank() over (order by sex,id rows between 1 preceding and 2 following), 802 row_number() over (order by sex,id rows unbounded preceding) 803 from row_number01; 804 select row_number01.*, sum(id) over (order by id rows unbounded preceding), 805 dense_rank() over (order by sex,id rows between 1 preceding and 2 following) 806 from row_number01; 807 808 -- sum, avg, count with frames 809 select sum(id),avg(id) over (partition by sex), count(id) over (partition by sex) from row_number01; 810 select * from (select id, sum(id) over (partition by sex), count(*) over (partition by sex), sex from row_number01 alias order by id) alias; 811 select sum(id) over (partition by sex) from row_number01; 812 select id, sum(id) over (partition by sex order by id 813 rows between 2 preceding and 1 following), sex from row_number01; 814 815 -- try the same as a view 816 create view v as select id, sum(id) over (partition by sex order by id rows between 2 preceding and 1 following), sex from row_number01; 817 show create view v; 818 select * from v; 819 820 drop view v; 821 drop table row_number01; 822 drop table row_number02; 823 824 -- avg for moving range frame 825 drop table if exists wf01; 826 create table wf01(d float); 827 insert into wf01 values (10); 828 insert into wf01 values (1); 829 insert into wf01 values (2); 830 insert into wf01 values (3); 831 insert into wf01 values (4); 832 insert into wf01 values (5); 833 insert into wf01 values (6); 834 insert into wf01 values (7); 835 insert into wf01 values (8); 836 insert into wf01 values (9); 837 select * from wf01; 838 839 select d, sum(d) over (order by d range between 2 preceding and current row), 840 avg(d) over (order by d range between 2 preceding and current row) from wf01; 841 select d, sum(d) over (order by d range between 1 preceding and 2 following), 842 avg(d) over (order by d range between 2 preceding and 3 following) from wf01; 843 select d, sum(d) over (order by d range between 2 preceding and current row), 844 avg(d) over (order by d range between 1 preceding and current row) from wf01; 845 select d, sum(d) over (order by d range between 2 preceding and 2 following), 846 avg(d) over (order by d range between 1 preceding and 2 following) from wf01; 847 select d, sum(d) over (order by d range between current row and 0 following), 848 avg(d) over (order by d range between current row and 2 following) from wf01; 849 select d, sum(d) over (order by d range between 2 preceding and 2 following), 850 avg(d) over (order by d range between current row and 2 following) from wf01; 851 852 -- get more duplicates and hence peer sets 853 insert into wf01 select * from wf01; 854 select * from wf01; 855 select d, sum(d) over (order by d range between 2 preceding and current row), 856 avg(d) over (order by d range between 1 preceding and current row) from wf01; 857 select d, sum(d) over (order by d range between 2 preceding and 2 following), 858 avg(d) over (order by d range between 3 preceding and 2 following) from wf01; 859 select d, sum(d) over (order by d range between 2 preceding and current row), 860 avg(d) over (order by d range between 2 preceding and current row) from wf01; 861 select d, sum(d) over (order by d range between 1 preceding and 2 following), 862 avg(d) over (order by d range between 2 preceding and 2 following) from wf01; 863 select d, sum(d) over (order by d range between current row and 2 following), 864 avg(d) over (order by d range between current row and 2 following) from wf01; 865 select d, sum(d) over (order by d range between current row and 2 following), 866 avg(d) over (order by d range between current row and 2 following) from wf01; 867 drop table wf01; 868 869 -- sum with frames in combination with non-framing window functions 870 -- row_number and rank 871 drop table if exists wf02; 872 create table wf02 (id integer, sex varchar(10)); 873 insert into wf02 values (1, 'moolol'); 874 insert into wf02 values (2, 'fdhsajhd'); 875 insert into wf02 values (3, 'fdhsajhd'); 876 insert into wf02 values (4, 'fdhsajhd'); 877 insert into wf02 values (5, 'moolol'); 878 insert into wf02 values (10, null); 879 insert into wf02 values (11, null); 880 select * from wf02; 881 882 select row_number() over (partition by sex order by id rows between unbounded preceding and unbounded following), id, 883 sum(id) over (partition by sex order by id rows between 1 following and 2 following), sex from wf02; 884 select row_number() over (partition by sex order by id rows between 1 following and 2 following), sum(id) over (partition by sex order by id 885 rows between 1 following and 2 following) from wf02; 886 887 insert into wf02 values (10, null); 888 select rank() over (partition by sex order by id), id, sum(id) over (partition by sex order by id) as abc, sex from wf02; 889 890 select id, sex, sum(id) over (partition by sex order by id rows between 2 preceding and 1 following) as a from wf02; 891 select id, sex, sum(id) over (partition by sex order by id rows between 2 preceding and 1 following) as a, 892 row_number() over (partition by sex order by id rows between 2 preceding and 1 following) as b, 893 rank() over (partition by sex order by id rows between 2 preceding and 1 following) as c from wf02; 894 select id, sex, sum(id) over (partition by sex order by id rows between 2 preceding and 1 following) as a, 895 row_number() over (partition by sex order by id rows between 2 preceding and 1 following) as b from wf02; 896 select row_number() over (partition by sex order by id rows between unbounded preceding and unbounded following), id, 897 sex from wf02; 898 select row_number() over (partition by sex order by id rows between 1 preceding and 2 following), sum(id) over (partition by sex order by id 899 rows between 1 preceding and 2 following) from wf02; 900 901 drop table wf02; 902 903 -- windows function with cte and referenced in the external select column 904 drop table if exists cte01; 905 drop table if exists cte02; 906 907 create table cte01 ( 908 customer_id varchar(1), 909 order_date date, 910 product_id integer 911 ); 912 913 insert into cte01 values('a', '2021-01-01', '1'); 914 insert into cte01 values('a', '2021-01-01', '2'); 915 insert into cte01 values('a', '2021-01-07', '2'); 916 insert into cte01 values('a', '2021-01-10', '3'); 917 insert into cte01 values('a', '2021-01-11', '3'); 918 insert into cte01 values('a', '2021-01-11', '3'); 919 insert into cte01 values('b', '2021-01-01', '2'); 920 insert into cte01 values('b', '2021-01-02', '2'); 921 insert into cte01 values('b', '2021-01-04', '1'); 922 insert into cte01 values('b', '2021-01-11', '1'); 923 insert into cte01 values('b', '2021-01-16', '3'); 924 insert into cte01 values('b', '2021-02-01', '3'); 925 insert into cte01 values('c', '2021-01-01', '3'); 926 insert into cte01 values('c', '2021-01-01', '3'); 927 insert into cte01 values('c', '2021-01-07', '3'); 928 select * from cte01; 929 930 create table cte02 ( 931 product_id integer, 932 product_name varchar(5), 933 price integer 934 ); 935 936 insert into cte02 values('1', 'sushi', '10'); 937 insert into cte02 values('2', 'curry', '15'); 938 insert into cte02 values('3', 'ramen', '12'); 939 select * from cte02; 940 941 with test as ( 942 select cte01.customer_id, cte01.order_date, cte02.product_name, dense_rank() over (partition by cte01.customer_id 943 order by cte01.order_date) as `rank` from cte01 inner join cte02 on cte01.product_id = cte02.product_id) 944 select customer_id, product_name from test where `rank` = 1 group by customer_id, product_name; 945 946 947 with test as ( 948 select cte01.customer_id, cte01.order_date, cte02.product_name, rank() over (partition by cte01.customer_id 949 order by cte01.order_date) as `rank` from cte01 inner join cte02 on cte01.product_id = cte02.product_id) 950 select customer_id, product_name from test where `rank` = 1 group by customer_id, product_name; 951 952 with test as ( 953 select cte01.customer_id, cte01.order_date, cte02.product_name, row_number() over (partition by cte01.customer_id 954 order by cte01.order_date) as `rank` from cte01 inner join cte02 on cte01.product_id = cte02.product_id) 955 select customer_id, product_name from test where `rank` = 1 group by customer_id, product_name; 956 957 drop table cte01; 958 drop table cte02; 959 960 -- Large data volume 961 drop table if exists td; 962 create table td(d int); 963 964 insert into td(d) values (10),(1),(2),(3),(4),(5),(6),(7),(8),(9); 965 insert into td(d) select d+10 from td; 966 insert into td(d) select d+20 from td; 967 insert into td(d) select d+30 from td; 968 insert into td(d) select d+40 from td; 969 insert into td(d) select d+50 from td; 970 insert into td(d) select d+60 from td; 971 insert into td(d) select d+70 from td; 972 insert into td(d) select d+80 from td; 973 insert into td(d) select d+90 from td; 974 975 insert into td(d) select d+100 from td; 976 insert into td(d) select d+200 from td; 977 insert into td(d) select d+300 from td; 978 insert into td(d) select d+400 from td; 979 insert into td(d) select d+500 from td; 980 insert into td(d) select d+600 from td; 981 insert into td(d) select d+700 from td; 982 insert into td(d) select d+800 from td; 983 insert into td(d) select d+900 from td; 984 insert into td(d) select d+1000 from td; 985 insert into td(d) select d+2000 from td; 986 insert into td(d) select d+3000 from td; 987 select count(*) from td; 988 989 -- @bvt:issue#13008 990 select avg(d) over (order by d range between 2 preceding and 2 following) from td limit 10; 991 -- @bvt:issue 992 select sum(d) over (order by d rows between 10 preceding and 10 following) from td limit 10; 993 select d,min(d) over (partition by d%7 order by d rows between 2 preceding and 1 following) from td limit 10; 994 drop table td; 995 996 drop table if exists `c`; 997 create table `c` ( 998 `pk` int(11) not null auto_increment, 999 `col_int` int(11) not null, 1000 `col_date` date not null, 1001 `col_datetime` datetime not null, 1002 `col_time` time not null, 1003 `col_varchar` varchar(15) not null, 1004 primary key (`pk`), 1005 unique key `col_date_key` (`col_date`), 1006 unique key `col_date_key_2` (`col_date`,`col_datetime`), 1007 key `col_int_key_1` (`col_int`,`col_date`), 1008 key `col_int_key_2` (`col_int`,`col_time`), 1009 key `col_int_key_3` (`col_int`,`col_datetime`) 1010 ); 1011 1012 insert into `c` (`pk`, `col_int`, `col_date`, `col_datetime`, `col_time`, `col_varchar`) 1013 values (1, 9, '2009-11-04', '2006-10-12 19:52:02', '18:19:40', 'a'), 1014 (2, 4, '2009-05-21', '2005-09-13 00:00:00', '07:45:25', 'tef'), 1015 (3, 0, '1900-01-01', '2002-09-03 04:42:41', '13:17:14', 'efqsd'), 1016 (4, 149, '2000-11-05', '2007-02-08 07:29:31', '10:38:21', 'fqsdk'), 1017 (5, 8, '2001-06-12', '2000-11-07 15:28:31', '23:04:47', 'qsdksji'), 1018 (6, 8, '2002-06-07', '2007-09-19 02:35:12', '07:33:31', 'sdks'), 1019 (7, 5, '2008-06-02', '1900-01-01 00:00:00', '14:41:02', 'dksjij'), 1020 (8, 7, '2000-07-26', '2007-11-27 00:19:33', '23:30:25', 'sjijcsz'), 1021 (9, 8, '2008-09-16', '2004-12-17 11:22:46', '06:11:14', 'i'), 1022 (10, 104, '2002-03-06', '2007-02-04 13:09:16', '22:24:50', 'jcszxw'), 1023 (11, 1, '2004-01-10', '2008-03-19 08:36:41', '00:03:00', 'csz'), 1024 (12, 4, '2002-02-21', '2008-03-27 03:09:30', '06:52:39', 'szxwbjj'), 1025 (13, 8, '2004-07-01', '2001-10-20 06:42:39', '08:49:41', 'xwb'), 1026 (14, 7, '2008-08-13', '2002-04-05 00:00:00', '05:52:03', 'wbjjvvk'), 1027 (15, 8, '2008-12-18', '1900-01-01 00:00:00', '00:00:00', 'bj'), 1028 (16, 6, '2002-08-03', '2008-04-14 09:20:36', '00:00:00', 'jjvvk'), 1029 (17, 97, '2001-06-11', '2002-11-07 00:00:00', '13:30:55', 'j'); 1030 1031 drop table if exists `dd`; 1032 create table `dd` ( 1033 `pk` int(11) not null auto_increment, 1034 `col_int` int(11) not null, 1035 `col_date` date not null, 1036 `col_datetime` datetime not null, 1037 `col_time` time not null, 1038 `col_varchar` varchar(15) not null, 1039 primary key (`pk`), 1040 unique key `col_date_key` (`col_date`), 1041 unique key `col_date_key_1` (`col_date`,`col_time`,`col_datetime`), 1042 key `col_int_key` (`col_int`), 1043 key `col_time_key` (`col_time`), 1044 key `col_datetime_key` (`col_datetime`), 1045 key `col_int_key_5` (`col_int`), 1046 key `col_int_key_6` (`col_int`), 1047 key `col_int_key_7` (`col_int`,`col_date`), 1048 key `col_int_key_8` (`col_int`,`col_time`), 1049 key `col_int_key_9` (`col_int`,`col_datetime`)); 1050 1051 insert into `dd` (`pk`, `col_int`, `col_date`, `col_datetime`, `col_time`, `col_varchar`) 1052 values (10,7,'1992-01-01','2000-02-09 06:46:23','03:56:10','i'), 1053 (11,5,'2008-12-11','2004-03-07 18:05:11','00:00:00','jrll'), 1054 (12,7,'2005-11-18','2001-01-18 08:29:29','20:17:57','rllqunt'), 1055 (13,9,'2009-02-08','2005-10-25 00:00:00','08:09:49','l'), 1056 (14,3,'2002-05-26','2009-09-01 10:19:05','09:40:42','lq'), 1057 (15,66,'2002-03-10','2002-09-06 04:43:02','08:28:55','quntp'), 1058 (16,3,'2003-07-07','2006-04-07 00:00:00','20:12:00','untppi'), 1059 (17,95,'2006-06-22','2004-05-08 00:00:00','18:50:24','ntppirz'), 1060 (18,7,'2004-01-21','2000-01-23 03:34:04','17:01:57','tppirzd'), 1061 (19,5,'2001-05-01','2005-12-26 20:42:01','15:11:27','pirzdp'), 1062 (20,8,'2008-12-15','1900-01-01 00:00:00','05:49:51','irzd'), 1063 (21,3,'2000-08-28','2003-02-28 16:30:52','14:58:44','zdphpdu'), 1064 (22,96,'2008-06-08','2005-09-15 03:55:22','02:20:01','dp'), 1065 (23,9,'2002-04-02','2001-01-08 10:44:10','19:03:57','p'), 1066 (24,3,'2005-03-04','2001-03-23 00:00:00','00:27:13','h'), 1067 (25,8,'2001-01-21','2004-03-02 00:00:00','13:39:32','pduhwq'), 1068 (26,8,'2006-10-05','1900-01-01 00:00:00','08:06:08','uhwqh'), 1069 (27,4,'2001-12-26','2006-10-24 05:59:20','16:15:34','hwqh'), 1070 (28,7,'1900-01-01','2005-06-14 00:00:00','12:04:50','wqhnsm'), 1071 (29,6,'2007-12-02','2001-08-25 03:00:31','00:00:00','qh'), 1072 (30,4,'2009-02-06','2001-06-14 19:13:14','06:00:42','nsmu'), 1073 (31,9,'2007-01-15','2006-12-18 07:54:16','11:18:35','smujjj'), 1074 (32,5,'2004-11-07','2000-09-18 04:53:37','16:20:06','muj'), 1075 (33,1,'2003-12-07','2002-08-18 04:47:11','01:41:35','jj'), 1076 (34,1,'2008-09-07','2000-10-14 16:58:18','17:42:13','jbld'), 1077 (35,5,'2005-03-08','2008-11-22 16:40:01','00:59:59','bldnki'), 1078 (36,181,'2006-11-18','1900-01-01 00:00:00','00:00:00','nkiws'), 1079 (37,5,'2007-01-26','2008-01-21 00:00:00','02:16:04','kiwsr'), 1080 (38,1,'2003-08-24','1900-01-01 00:00:00','00:00:00','iwsrsx'), 1081 (39,162,'2001-12-01','2008-05-17 00:00:00','14:34:36','srsxnd'), 1082 (40,8,'2003-07-02','2000-06-07 00:00:00','23:02:05','r'), 1083 (41,2,'2007-03-01','2009-01-03 12:22:04','00:00:00','sxndo'), 1084 (42,7,'2009-08-04','2009-10-05 04:15:15','00:00:00','xndolp'), 1085 (43,119,'2000-05-03','2002-02-17 23:12:12','23:23:35','olpujd'), 1086 (44,3,'2001-05-18','2008-03-27 11:51:54','11:26:20','lp'), 1087 (45,119,'2004-02-22','1900-01-01 00:00:00','00:00:00','pu'), 1088 (46,8,'2002-07-15','2008-08-24 21:36:28','12:51:37','dnozrhh'), 1089 (47,2,'2008-04-22','2005-01-12 08:50:22','20:55:45','no'), 1090 (48,4,'2006-06-01','2000-04-20 00:00:00','13:02:05','ozrhhcx'), 1091 (49,8,'2009-09-12','2000-02-16 03:57:05','17:04:35','zrhhcxs'), 1092 (50,6,'2009-01-06','1900-01-01 00:00:00','05:15:45','rhhcxsx'), 1093 (51,6,'2008-07-13','2002-04-27 14:13:27','00:00:00','hhcxsxw'), 1094 (52,8,'2002-03-15','2008-01-17 20:30:57','07:09:22','hcxsxw'), 1095 (53,6,'2007-10-14','2006-10-11 22:48:02','06:11:59','cxs'), 1096 (54,1,'2008-07-23','2005-09-11 07:19:40','03:05:06','x'), 1097 (55,1,'2007-05-22','2002-11-24 16:25:27','10:10:42','s'), 1098 (56,6,'2008-01-08','2005-06-09 01:11:17','06:03:27','w'), 1099 (57,9,'2006-10-18','1900-01-01 00:00:00','00:00:00','uju'), 1100 (58,7,'2000-07-22','1900-01-01 00:00:00','00:00:00','ju'), 1101 (59,6,'2004-07-21','2009-10-25 16:05:29','11:04:39','ul'), 1102 (60,2,'2001-10-03','2002-06-13 11:41:55','10:20:49','lpjd'), 1103 (61,8,'2002-08-17','1900-01-01 00:00:00','00:00:00','jdz'), 1104 (62,0,'2009-11-10','2000-05-04 05:15:19','00:00:00','zvkpaij'), 1105 (63,6,'2005-06-26','2002-08-19 00:00:00','09:21:09','vkpaij'), 1106 (64,6,'2000-06-04','2002-03-22 04:37:00','00:00:00','kp'), 1107 (65,9,'2005-10-02','2009-01-10 09:03:59','04:56:37','paiju'), 1108 (66,0,'2009-11-13','1900-01-01 00:00:00','00:00:00','aij'), 1109 (67,0,'2006-11-26','2001-09-21 00:00:00','08:16:28','ijurspr'), 1110 (68,6,'2007-09-24','2003-08-27 05:11:09','19:55:11','j'), 1111 (69,0,'2009-01-24','1900-01-01 00:00:00','11:25:58','urspr'), 1112 (70,5,'2001-06-22','2005-07-07 00:00:00','14:38:03','rsprn'), 1113 (71,4,'2006-07-18','2000-07-16 06:17:20','15:32:00','sprnw'), 1114 (72,5,'2009-05-12','2007-07-26 00:00:00','09:25:59','rnwgrp'); 1115 1116 drop table if exists `e`; 1117 create table `e` ( 1118 `pk` int(11) not null auto_increment, 1119 `col_int` int(11) not null, 1120 `col_date` date not null, 1121 `col_datetime` datetime not null, 1122 `col_time` time not null, 1123 `col_varchar` varchar(15) not null, 1124 primary key (`pk`), 1125 unique key `col_date` (`col_date`,`col_time`,`col_datetime`), 1126 unique key `col_varchar_key_2` (`col_varchar`(5)), 1127 unique key `col_int_key_1` (`col_int`,`col_varchar`(5)), 1128 unique key `col_int_key_2` (`col_int`,`col_varchar`(5),`col_date`,`col_time`,`col_datetime`), 1129 key `col_int_key` (`col_int`), 1130 key `col_time_key` (`col_time`), 1131 key `col_datetime_key` (`col_datetime`), 1132 key `col_int_key_7` (`col_int`,`col_date`), 1133 key `col_int_key_8` (`col_int`,`col_time`), 1134 key `col_int_key_9` (`col_int`,`col_datetime`)); 1135 1136 insert into `e` (`pk`, `col_int`, `col_date`, `col_datetime`, `col_time`, `col_varchar`) 1137 values (1, 202, '1997-01-13', '2008-11-25 09:14:26', '07:23:12', 'en'), 1138 (2, 4, '2005-07-10', '2005-03-15 22:48:25', '23:28:02', 'nchyhu'), 1139 (3, 7, '2005-06-09', '2006-11-22 00:00:00', '10:51:23', 'chy'), 1140 (4, 2, '2007-12-08', '2007-11-01 09:02:50', '01:12:13', 'hyhu'), 1141 (5, 7, '2007-12-22', '2001-04-08 00:00:00', '06:34:46', 'yhuoo'), 1142 (6, 1, '1900-01-01', '2001-11-27 19:47:15', '10:16:53', 'huoo'), 1143 (7, 7, '2002-10-07', '2009-09-15 04:42:26', '07:07:58', 'uoowit'), 1144 (8, 7, '2005-01-09', '2001-08-12 02:07:43', '06:15:07', 'oo'), 1145 (9, 3, '2007-10-12', '2009-05-09 17:06:27', '00:00:00', 'ow'), 1146 (10, 3, '2004-01-22', '1900-01-01 00:00:00', '06:41:21', 'wityzg'), 1147 (11, 5, '2007-10-11', '2000-03-03 23:40:04', '22:28:00', 'ityzg'), 1148 (12, 8, '2001-08-19', '2005-10-18 17:41:54', '04:47:49', 'tyz'), 1149 (13, 9, '2001-02-12', '2000-03-23 23:22:54', '03:24:01', 'gktbkjr'), 1150 (14, 0, '2000-07-14', '2007-01-25 11:00:51', '14:37:06', 'ktbkjrk'), 1151 (15, 4, '2007-11-14', '2003-12-21 10:46:23', '05:53:49', 'tbkjrkm'), 1152 (16, 9, '2004-01-25', '2003-09-02 01:45:27', '00:00:00', 'k'), 1153 (17, 2, '2003-12-15', '2009-05-28 08:03:38', '23:41:09', 'j'), 1154 (18, 4, '2002-01-25', '2003-10-23 18:22:15', '09:26:45', 'kmqm'), 1155 (19, 0, '2009-09-08', '2001-12-28 00:00:00', '17:04:03', 'mq'), 1156 (20, 7, '2008-03-15', '2005-05-06 19:42:18', '02:15:17', 'mkn'), 1157 (21, 0, '2005-11-10', '2003-03-05 00:00:00', '00:00:00', 'knbtoe'), 1158 (22, 1, '2008-11-12', '2001-12-26 16:47:05', '19:09:36', 'n'), 1159 (23, 2, '2007-11-22', '2003-02-09 00:00:00', '07:55:11', 'btoer'), 1160 (24, 4, '2002-04-25', '2008-10-13 00:00:00', '11:24:50', 'toe'), 1161 (25, 4, '2004-02-14', '2001-07-16 16:05:48', '08:46:01', 'oervq'), 1162 (26, 4, '2004-04-21', '2004-04-23 14:00:22', '20:16:19', 'rvqlzs'), 1163 (27, 3, '2003-03-26', '2002-11-10 08:15:17', '13:03:14', 'vqlzs'), 1164 (28, 0, '2007-06-18', '2006-06-24 03:59:58', '06:11:33', 'qlzsva'), 1165 (29, 5, '2006-12-09', '2008-04-08 18:06:18', '09:40:31', 'lzsvasu'), 1166 (30, 8, '2001-10-01', '2000-10-12 16:32:35', '03:34:01', 'zsvasu'), 1167 (31, 6, '2001-01-07', '2005-09-11 10:09:54', '00:00:00', 'svas'), 1168 (32, 0, '2007-11-02', '2009-09-10 01:44:18', '12:23:27', 'v'), 1169 (33, 9, '2005-07-23', '2002-10-20 21:55:02', '05:12:10', 'surqdhu'), 1170 (34, 4, '2003-09-13', '2009-11-03 09:54:42', '20:54:06', 'urqdh'), 1171 (35, 165, '2001-05-14', '2002-10-19 00:00:00', '00:00:00', 'rqd'), 1172 (36, 2, '2006-07-04', '2008-10-26 00:00:00', '00:59:06', 'qdhu'), 1173 (37, 6, '2001-08-15', '2002-08-14 14:52:08', '07:22:34', 'dhu'), 1174 (38, 5, '2000-04-27', '2007-06-10 00:00:00', '11:27:19', 'hu4332cjx'), 1175 (39, 9, '2007-10-13', '2002-07-07 04:10:43', '10:03:09', 'uc'), 1176 (40, 214, '2004-02-06', '2007-08-15 13:56:29', '23:00:35', 'cjxd'), 1177 (41, 194, '2008-12-27', '1900-01-01 00:00:00', '11:59:05', 'jx'), 1178 (42, 1, '2002-08-16', '2000-08-11 11:34:38', '21:39:43', 'xdo'), 1179 (43, 220, '2001-06-17', '1900-01-01 00:00:00', '00:00:00', 'oyg'), 1180 (44, 9, '2002-10-16', '2008-12-07 23:41:33', '00:00:00', 'gx'), 1181 (45, 248, '2008-04-06', '1900-01-01 00:00:00', '12:32:24', 'x'), 1182 (46, 0, '2000-07-08', '2001-12-27 19:38:22', '00:00:00', 'vgqmw'), 1183 (47, 0, '2005-03-16', '1900-01-01 00:00:00', '06:22:01', 'qmwcid'), 1184 (48, 4, '2002-06-19', '2007-03-08 02:43:50', '07:00:21', 'mwc'), 1185 (49, 3, '2005-11-25', '2001-11-14 17:21:32', '17:59:20', 'wcidtu'), 1186 (50, 7, '2007-07-08', '1900-01-01 00:00:00', '01:58:05', 'cidtum'), 1187 (51, 7, '2000-06-20', '2004-07-20 11:05:12', '22:24:24', 'dtumxwc'), 1188 (52, 5, '2006-03-28', '2008-08-15 08:28:18', '04:22:26', 'tumxwc'), 1189 (53, 1, '2004-03-05', '1900-01-01 00:00:00', '00:00:00', 'umxwcf'), 1190 (54, 0, '2009-05-10', '2004-01-28 15:16:19', '11:46:32', 'mxwcft'), 1191 (55, 67, '2004-04-18', '2001-06-23 00:00:00', '20:12:09', 'xwcfted'), 1192 (56, 204, '2008-01-10', '2009-02-12 07:59:52', '13:58:17', 'wc'), 1193 (57, 9, '2000-07-12', '2004-12-10 07:32:31', '04:04:48', 'ftedx'), 1194 (58, 5, '2001-06-16', '2006-09-06 12:15:44', '10:14:16', 't'), 1195 (59, 6, '2000-02-20', '2003-09-13 14:23:06', '21:22:20', 'dx'), 1196 (60, 6, '2001-02-07', '2004-01-18 00:00:00', '10:15:21', 'xqyciak'), 1197 (61, 1, '2008-12-24', '2004-04-02 07:16:01', '16:30:10', 'qy'), 1198 (62, 1, '2009-12-14', '2000-01-04 14:51:24', '03:57:54', 'y'), 1199 (63, 5, '2008-03-07', '2001-06-24 00:00:00', '06:41:05', 'ciak'), 1200 (64, 4, '2005-01-19', '2001-06-02 03:41:12', '00:00:00', 'iakh'), 1201 (65, 4, '2003-02-10', '1900-01-01 00:00:00', '08:51:25', 'ak'), 1202 (66, 9, '2005-12-25', '2007-07-13 14:26:05', '14:32:55', 'hxptz'), 1203 (67, 4, '2003-10-13', '2008-03-20 21:14:50', '00:21:31', 'xptzfp'), 1204 (68, 3, '2001-08-03', '1900-01-01 00:00:00', '00:00:00', 'ptzfpjw'), 1205 (69, 0, '2006-04-01', '1900-01-01 00:00:00', '11:26:05', 'tzfpjwr'), 1206 (70, 2, '2003-12-27', '2002-05-09 18:39:28', '05:28:11', 'wrgeo'), 1207 (71, 100, '2001-10-25', '2006-01-13 00:00:00', '04:35:51', 'r'), 1208 (72, 37, '2006-09-12', '2003-12-04 05:20:00', '06:10:43', 'geo'), 1209 (73, 5, '2003-06-04', '2003-07-21 11:43:03', '17:26:47', 'eozxnby'), 1210 (74, 6, '2009-11-13', '2006-12-24 00:00:00', '22:34:54', 'oz'), 1211 (75, 1, '2006-08-13', '2005-08-25 00:00:00', '21:27:38', 'zxnbyc'), 1212 (76, 7, '2007-07-09', '2003-10-16 01:16:30', '03:14:14', 'xnbycjz'), 1213 (77, 6, '2000-01-07', '2001-06-22 00:00:00', '00:00:00', 'nby'), 1214 (78, 5, '2004-12-21', '2004-09-01 18:53:04', '16:06:30', 'bycj'), 1215 (79, 0, '2003-10-14', '2000-04-13 05:21:03', '19:04:51', 'ycjzxie'); 1216 1217 with test01 as ( 1218 select `e`.col_int, `c`.col_varchar, row_number() over (partition by `e`.col_int 1219 order by `e`.col_date) as `rank` from `e` inner join `c` on `c`.col_int = `e`.col_int) 1220 select col_int as a from test where `rank` = 1 group by col_int; 1221 1222 with test02 as ( 1223 select `dd`.col_int, `c`.col_datetime, rank() over (partition by `dd`.col_int 1224 order by `dd`.col_date) as `rank` from `dd` left join `c` on `c`.col_int = `dd`.col_int) 1225 select col_int as a from test02 where `rank` = 1 group by col_int; 1226 1227 with test03 as ( 1228 select `dd`.col_int, `e`.col_varchar, dense_rank() over (partition by `dd`.col_int 1229 order by `dd`.col_datetime) as `rank` from `dd` left join `e` on `e`.col_int = `dd`.col_int) 1230 select col_int as a from test03 where `rank` = 1 group by col_int; 1231 1232 select `c`.col_int,`c`.col_datetime, `dd`.col_time, row_number() over (partition by `c`.col_int 1233 order by `dd`.col_time) as `rank` from `dd` left join `c` on `c`.col_int = `dd`.col_int; 1234 1235 select `c`.col_int,`c`.col_datetime, `dd`.col_time, sum(`c`.col_int) over (partition by `c`.col_int 1236 order by `dd`.col_time) as `rank` from `dd` left join `c` on `c`.col_int = `dd`.col_int; 1237 1238 select `c`.col_int,`c`.col_datetime, `dd`.col_time, avg(`dd`.col_int) over (partition by `c`.col_int 1239 order by `dd`.col_time) as `rank` from `dd` left join `c` on `c`.col_int = `dd`.col_int; 1240 1241 select `c`.col_int,`dd`.col_time, min(`dd`.col_int) over (partition by `c`.col_int 1242 order by `dd`.col_time) as `rank` from `dd` left join `c` on `c`.col_int = `dd`.col_int; 1243 drop table `c`; 1244 drop table `dd`; 1245 drop table `e`; 1246 drop table if exists t2; 1247 CREATE TABLE t2 (a int, b int); 1248 insert into t2 values(1,1); 1249 insert into t2 values(2,2); 1250 insert into t2 values(3,3); 1251 insert into t2 values(4,4); 1252 insert into t2 values(1,1); 1253 insert into t2 values(5,5); 1254 insert into t2 values(6,6); 1255 select a,min(a) over (partition by a) ,sum(a+1) over (partition by a) from t2; 1256 drop table t2; 1257 1258 1259 -- order by 1260 drop table if exists int_8; 1261 create table int_8 (id tinyint, sex varchar(10)); 1262 insert into int_8 values (-1, 'moolol'); 1263 insert into int_8 values (-128, 'fdhsajhd'); 1264 insert into int_8 values (32, 'fdhsajhd'); 1265 insert into int_8 values (-1, 'fdhsajhd'); 1266 insert into int_8 values (23, 'moolol'); 1267 insert into int_8 values (127, null); 1268 insert into int_8 values (-128, null); 1269 select * from int_8; 1270 select row_number() over (partition by sex order by id rows between unbounded preceding and unbounded following) as a, id, 1271 sum(id) over (partition by sex order by id rows between 1 following and 2 following) as b from int_8; 1272 select row_number() over (partition by sex order by id rows between 1 following and 2 following) as a, sum(id) over (partition by sex order by id 1273 rows between 1 following and 2 following) as b from int_8; 1274 drop table int_8; 1275 1276 drop table if exists int_16; 1277 create table int_16(col1 smallint,col2 bool,col3 datetime); 1278 insert into int_16 values(-32768, true, '2023-05-16 00:12:12'); 1279 insert into int_16 values(22201, false, '1997-01-13 12:12:00'); 1280 insert into int_16 values(-32768, true, '2000-10-10 11:11:11'); 1281 insert into int_16 values(4, false, '1020-10-01 01:01:01'); 1282 insert into int_16 values(32767, null, null); 1283 select max(col1) over (partition by col2 order by col1 rows between 2 preceding and 3 following) as col1 from int_16; 1284 select dense_rank() over (partition by col2 order by col1 rows between 2 preceding and 3 following) as col1 from int_16; 1285 drop table int_16; 1286 1287 drop table if exists int_32; 1288 create table int_32(i int, j int, k int); 1289 insert into int_32 values (-2147483648, 1, 1); 1290 insert into int_32 values (-2147483648, 1, 2); 1291 insert into int_32 values (2147483647, 1, 2); 1292 insert into int_32 values (2147483647, 2, 1); 1293 insert into int_32 values (13289392, 2, 2); 1294 insert into int_32 values (23289483, 1, 1); 1295 insert into int_32 values (-3824, 1, 1); 1296 insert into int_32 values (2438294, 1, 2); 1297 insert into int_32 values (-3824, 2, 1); 1298 select * from int_32; 1299 select *, rank() over (order by i,j,k rows between 2 preceding and 3 following) as o_ijk, 1300 min(i) over (order by j rows between 4 preceding and 5 following) as o_j, 1301 rank() over (order by k,j rows between 1 preceding and 1 following) as o_kj from int_32 order by i,j,k; 1302 drop table int_32; 1303 1304 drop table if exists int_64; 1305 create table int_64(i bigint unsigned, j int, k int); 1306 insert into int_64 values (18446744073709551614, 1, 1); 1307 insert into int_64 values (18446744073709551614, 1, 2); 1308 insert into int_64 values (2147483647, 1, 2); 1309 insert into int_64 values (2147483647, 2, 1); 1310 insert into int_64 values (0, 2, 2); 1311 insert into int_64 values (0, 1, 1); 1312 select * from int_64; 1313 select *, rank() over (order by i,j,k rows between 2 preceding and 3 following) as o_ijk, 1314 min(i) over (order by j rows between 4 preceding and 5 following) as o_j, 1315 rank() over (order by k,j rows between 1 preceding and 1 following) as o_kj from int_64 order by i,j,k; 1316 drop table int_64; 1317 1318 drop table if exists uint_8; 1319 create table uint_8 (col1 tinyint unsigned, col2 varchar(10)); 1320 insert into uint_8 values (1, 'moolol'); 1321 insert into uint_8 values (128, 'fdhsajhd'); 1322 insert into uint_8 values (32, 'fdhsajhd'); 1323 insert into uint_8 values (1, 'fdhsajhd'); 1324 insert into uint_8 values (23, 'moolol'); 1325 insert into uint_8 values (255, null); 1326 insert into uint_8 values (128, null); 1327 select * from uint_8; 1328 select row_number() over (partition by col2 order by col1 rows between unbounded preceding and unbounded following) as a, 1329 sum(col1) over (partition by col2 order by col1 rows between 1 following and 2 following) as b from uint_8; 1330 select dense_rank() over (partition by col2 order by col1) as col1, sum(col1) over (partition by col2 order by col1) as col2 from uint_8; 1331 drop table uint_8; 1332 1333 drop table if exists uint_16; 1334 create table uint_16(col1 smallint unsigned,col2 bool,col3 datetime); 1335 insert into uint_16 values(0, true, '2023-05-16 00:12:12'); 1336 insert into uint_16 values(0, false, '1997-01-13 12:12:00'); 1337 insert into uint_16 values(65535, true, '2000-10-10 11:11:11'); 1338 insert into uint_16 values(4, false, '1020-10-01 01:01:01'); 1339 insert into uint_16 values(null, null, null); 1340 insert into uint_16 values(65535, null, '1997-11-10 10:10:10'); 1341 select * from uint_16; 1342 select max(col1) over (partition by col2 order by col1 rows between unbounded preceding and unbounded following) as col1 from uint_16; 1343 select dense_rank() over (partition by col2 order by col1 rows between unbounded preceding and unbounded following) as col1 from uint_16; 1344 drop table uint_16; 1345 1346 drop table if exists uint_32; 1347 create table uint_32(i int unsigned, j int, k int); 1348 insert into uint_32 values (4294967295, 1, 1); 1349 insert into uint_32 values (4294967295, 1, 2); 1350 insert into uint_32 values (2147483647, 1, 2); 1351 insert into uint_32 values (2147483647, 2, 1); 1352 insert into uint_32 values (13289392, 2, 2); 1353 insert into uint_32 values (23289483, 1, 1); 1354 insert into uint_32 values (3824, 1, 1); 1355 insert into uint_32 values (2438294, 1, 2); 1356 insert into uint_32 values (3824, 2, 1); 1357 select * from uint_32; 1358 select max(i) over (order by i,j,k rows between 1 preceding and 2 following) as o_ijk, 1359 dense_rank() over (order by i rows between unbounded preceding and unbounded following) as o_j, 1360 rank() over (order by k,j rows between unbounded preceding and unbounded following) as o_kj from uint_32 order by i,j,k; 1361 drop table uint_32; 1362 1363 drop table if exists uint_64; 1364 create table uint_64(i bigint unsigned, j bigint unsigned, k decimal); 1365 insert into uint_64 values (18446744073709551615, 2147483647, 123213.99898); 1366 insert into uint_64 values (4294967295, 2147483647, 2); 1367 insert into uint_64 values (18446744073709551615, 1, 2); 1368 insert into uint_64 values (2147483647, 23289483, 123213.99898); 1369 insert into uint_64 values (13289392, 2, 2); 1370 insert into uint_64 values (18446744073709551615, 23289483, 1); 1371 insert into uint_64 values (3824, 13289392, 123213.99898); 1372 insert into uint_64 values (2438294, 1, 2); 1373 insert into uint_64 values (3824, 13289392, 1); 1374 select * from uint_64; 1375 select rank() over (order by i,j,k rows between unbounded preceding and unbounded following) as o_ijk, 1376 max(i) over (order by i rows between 10 preceding and 2 following) as o_j, 1377 rank() over (order by k,j rows between unbounded preceding and unbounded following) as o_kj from uint_64 order by i,j,k; 1378 drop table uint_64; 1379 1380 drop table if exists decimal_64; 1381 create table decimal_64(col1 decimal(18,10), col2 char(10)); 1382 insert into decimal_64 values (23189723.2314892238902, 'male'); 1383 insert into decimal_64 values (23189723.2314892238902, 'female'); 1384 insert into decimal_64 values (3278.3243214124242, 'male'); 1385 insert into decimal_64 (col1, col2) values (32134243.2143243242142, 'male'); 1386 insert into decimal_64 values (-23189723.2314892238902, 'male'); 1387 insert into decimal_64 values (-3278.3243214124242, 'female'); 1388 insert into decimal_64 (col1, col2) values (32134243.2143243242142, 'male'); 1389 select * from decimal_64; 1390 select max(col1) over (order by col1 rows between 1 preceding and 0 following) as newcol1 from decimal_64; 1391 select min(col1) over (partition by col2 order by col1 rows between unbounded preceding and unbounded following) as newcol from decimal_64 limit 4; 1392 drop table decimal_64; 1393 1394 drop table if exists decimal_128; 1395 create table decimal_128(col1 decimal(38,10), col2 char(10)); 1396 insert into decimal_128 values (2318972338274832748378887878.2314892238902, 'male'); 1397 insert into decimal_128 values (2318972338274832748378887878.23148922389, 'female'); 1398 insert into decimal_128 values (-3278234242342349090943024982.3243214124242, 'male'); 1399 insert into decimal_128 (col1, col2) values (32134243.2143243242142, 'male'); 1400 insert into decimal_128 values (-23189723.2314892238902, 'male'); 1401 insert into decimal_128 values (-3278234242342349090943024982.3243214124242, 'female'); 1402 insert into decimal_128 (col1, col2) values (32134243.2143243242142, 'male'); 1403 select * from decimal_128; 1404 select min(col1) over (order by col1 rows between 1 preceding and 0 following) as newcol1 from decimal_128; 1405 select dense_rank() over (order by col1 rows between 1 preceding and 2 following) as newcol2 from decimal_128; 1406 select row_number() over (partition by col2 order by col1 rows between unbounded preceding and unbounded following) as newcol from decimal_128; 1407 drop table decimal_128; 1408 1409 drop table if exists time01; 1410 create table time01 (col1 time, col2 timestamp); 1411 insert into time01 values ('12:1b2:12', '2023-05-16 00:12:12'); 1412 insert into time01 values ('23:23:59', '2019-05-16 23:23:59'); 1413 insert into time01 values ('12:12:12', '1997-01-13 01:02:03'); 1414 insert into time01 values ('23:23:59', '2023-05-16 00:12:12'); 1415 insert into time01 values ('01:02:03', '2019-05-16 23:23:59'); 1416 insert into time01 values (null, null); 1417 select * from time01; 1418 select rank() over (order by col1 rows between 1 preceding and 0 following) as newcol1 from time01; 1419 select dense_rank() over (order by col1 rows between 1 preceding and 2 following) as newcol2 from time01; 1420 select row_number() over (partition by col2 order by col1 rows between unbounded preceding and unbounded following) as newcol from time01; 1421 select rank() over (partition by col1 order by col2 rows between 0 preceding and 0 following) as newcol from time01; 1422 select max(col2) over (partition by col1 order by col2 rows between 3 preceding and 3 following) as newcol from time01; 1423 drop table time01; 1424 1425 drop table if exists window01; 1426 create table window01(col1 int, col2 varchar(20)); 1427 insert into window01 values(1,'老师'); 1428 insert into window01 values(2,'医生'); 1429 insert into window01 values(3,'工人'); 1430 insert into window01 values(10,'学生'); 1431 insert into window01 values(20,'学生'); 1432 insert into window01 values(12,'学生'); 1433 insert into window01 values(21,'老师'); 1434 insert into window01 values(100,'老师'); 1435 insert into window01 values(200,'工人'); 1436 1437 select col2, avg(col1) over (partition by col2 order by col1 desc) from window01; 1438 select col2, col1, sum(col1) over (partition by col2 order by col1 desc) from window01; 1439 select col2, col1, min(col1) over (partition by col2 order by col1 desc) from window01; 1440 select col2, col1, max(col1) over (partition by col2 order by col1 desc) from window01; 1441 select col2, col1, count(col1) over (partition by col2 order by col1 desc) from window01; 1442 drop database test; 1443