gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/window2.test (about) 1 # 2018 May 19 2 # 3 # The author disclaims copyright to this source code. In place of 4 # a legal notice, here is a blessing: 5 # 6 # May you do good and not evil. 7 # May you find forgiveness for yourself and forgive others. 8 # May you share freely, never taking more than you give. 9 # 10 #*********************************************************************** 11 # This file implements regression tests for SQLite library. 12 # 13 14 #################################################### 15 # DO NOT EDIT! THIS FILE IS AUTOMATICALLY GENERATED! 16 #################################################### 17 18 set testdir [file dirname $argv0] 19 source $testdir/tester.tcl 20 set testprefix window2 21 22 ifcapable !windowfunc { finish_test ; return } 23 do_execsql_test 1.0 { 24 DROP TABLE IF EXISTS t1; 25 CREATE TABLE t1(a INTEGER PRIMARY KEY, b TEXT, c TEXT, d INTEGER); 26 INSERT INTO t1 VALUES(1, 'odd', 'one', 1); 27 INSERT INTO t1 VALUES(2, 'even', 'two', 2); 28 INSERT INTO t1 VALUES(3, 'odd', 'three', 3); 29 INSERT INTO t1 VALUES(4, 'even', 'four', 4); 30 INSERT INTO t1 VALUES(5, 'odd', 'five', 5); 31 INSERT INTO t1 VALUES(6, 'even', 'six', 6); 32 } {} 33 34 do_execsql_test 1.1 { 35 SELECT c, sum(d) OVER (PARTITION BY b ORDER BY c) FROM t1; 36 } {four 4 six 10 two 12 five 5 one 6 three 9} 37 38 do_execsql_test 1.2 { 39 SELECT sum(d) OVER () FROM t1; 40 } {21 21 21 21 21 21} 41 42 do_execsql_test 1.3 { 43 SELECT sum(d) OVER (PARTITION BY b) FROM t1; 44 } {12 12 12 9 9 9} 45 46 #========================================================================== 47 48 do_execsql_test 2.1 { 49 SELECT a, sum(d) OVER ( 50 ORDER BY d 51 ROWS BETWEEN 1000 PRECEDING AND 1 FOLLOWING 52 ) FROM t1 53 } {1 3 2 6 3 10 4 15 5 21 6 21} 54 55 do_execsql_test 2.2 { 56 SELECT a, sum(d) OVER ( 57 ORDER BY d 58 ROWS BETWEEN 1000 PRECEDING AND 1000 FOLLOWING 59 ) FROM t1 60 } {1 21 2 21 3 21 4 21 5 21 6 21} 61 62 do_execsql_test 2.3 { 63 SELECT a, sum(d) OVER ( 64 ORDER BY d 65 ROWS BETWEEN 1 PRECEDING AND 1000 FOLLOWING 66 ) FROM t1 67 } {1 21 2 21 3 20 4 18 5 15 6 11} 68 69 do_execsql_test 2.4 { 70 SELECT a, sum(d) OVER ( 71 ORDER BY d 72 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING 73 ) FROM t1 74 } {1 3 2 6 3 9 4 12 5 15 6 11} 75 76 do_execsql_test 2.5 { 77 SELECT a, sum(d) OVER ( 78 ORDER BY d 79 ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING 80 ) FROM t1 81 } {1 1 2 3 3 5 4 7 5 9 6 11} 82 83 do_execsql_test 2.6 { 84 SELECT a, sum(d) OVER ( 85 PARTITION BY b 86 ORDER BY d 87 ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING 88 ) FROM t1 89 } {2 6 4 12 6 10 1 4 3 9 5 8} 90 91 do_execsql_test 2.7 { 92 SELECT a, sum(d) OVER ( 93 PARTITION BY b 94 ORDER BY d 95 ROWS BETWEEN 0 PRECEDING AND 0 FOLLOWING 96 ) FROM t1 97 } {2 2 4 4 6 6 1 1 3 3 5 5} 98 99 do_execsql_test 2.8 { 100 SELECT a, sum(d) OVER ( 101 ORDER BY d 102 ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING 103 ) FROM t1 104 } {1 6 2 9 3 12 4 15 5 11 6 6} 105 106 do_execsql_test 2.9 { 107 SELECT a, sum(d) OVER ( 108 ORDER BY d 109 ROWS BETWEEN UNBOUNDED PRECEDING AND 2 FOLLOWING 110 ) FROM t1 111 } {1 6 2 10 3 15 4 21 5 21 6 21} 112 113 do_execsql_test 2.10 { 114 SELECT a, sum(d) OVER ( 115 ORDER BY d 116 ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING 117 ) FROM t1 118 } {1 6 2 9 3 12 4 15 5 11 6 6} 119 120 do_execsql_test 2.11 { 121 SELECT a, sum(d) OVER ( 122 ORDER BY d 123 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 124 ) FROM t1 125 } {1 1 2 3 3 6 4 9 5 12 6 15} 126 127 do_execsql_test 2.13 { 128 SELECT a, sum(d) OVER ( 129 ORDER BY d 130 ROWS BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING 131 ) FROM t1 132 } {1 21 2 21 3 21 4 20 5 18 6 15} 133 134 do_execsql_test 2.14 { 135 SELECT a, sum(d) OVER ( 136 ORDER BY d 137 ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING 138 ) FROM t1 139 } {1 {} 2 1 3 3 4 6 5 9 6 12} 140 141 do_execsql_test 2.15 { 142 SELECT a, sum(d) OVER ( 143 PARTITION BY b 144 ORDER BY d 145 ROWS BETWEEN 1 PRECEDING AND 0 PRECEDING 146 ) FROM t1 147 } {2 2 4 6 6 10 1 1 3 4 5 8} 148 149 do_execsql_test 2.16 { 150 SELECT a, sum(d) OVER ( 151 PARTITION BY b 152 ORDER BY d 153 ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING 154 ) FROM t1 155 } {2 {} 4 2 6 4 1 {} 3 1 5 3} 156 157 do_execsql_test 2.17 { 158 SELECT a, sum(d) OVER ( 159 PARTITION BY b 160 ORDER BY d 161 ROWS BETWEEN 1 PRECEDING AND 2 PRECEDING 162 ) FROM t1 163 } {2 {} 4 {} 6 {} 1 {} 3 {} 5 {}} 164 165 do_execsql_test 2.18 { 166 SELECT a, sum(d) OVER ( 167 PARTITION BY b 168 ORDER BY d 169 ROWS BETWEEN UNBOUNDED PRECEDING AND 2 PRECEDING 170 ) FROM t1 171 } {2 {} 4 {} 6 2 1 {} 3 {} 5 1} 172 173 do_execsql_test 2.19 { 174 SELECT a, sum(d) OVER ( 175 PARTITION BY b 176 ORDER BY d 177 ROWS BETWEEN 1 FOLLOWING AND 3 FOLLOWING 178 ) FROM t1 179 } {2 10 4 6 6 {} 1 8 3 5 5 {}} 180 181 do_execsql_test 2.20 { 182 SELECT a, sum(d) OVER ( 183 ORDER BY d 184 ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING 185 ) FROM t1 186 } {1 5 2 7 3 9 4 11 5 6 6 {}} 187 188 do_execsql_test 2.21 { 189 SELECT a, sum(d) OVER ( 190 ORDER BY d 191 ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING 192 ) FROM t1 193 } {1 20 2 18 3 15 4 11 5 6 6 {}} 194 195 do_execsql_test 2.22 { 196 SELECT a, sum(d) OVER ( 197 PARTITION BY b 198 ORDER BY d 199 ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING 200 ) FROM t1 201 } {2 10 4 6 6 {} 1 8 3 5 5 {}} 202 203 do_execsql_test 2.23 { 204 SELECT a, sum(d) OVER ( 205 ORDER BY d 206 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING 207 ) FROM t1 208 } {1 21 2 20 3 18 4 15 5 11 6 6} 209 210 do_execsql_test 2.24 { 211 SELECT a, sum(d) OVER ( 212 PARTITION BY a%2 213 ORDER BY d 214 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING 215 ) FROM t1 216 } {2 12 4 10 6 6 1 9 3 8 5 5} 217 218 do_execsql_test 2.25 { 219 SELECT a, sum(d) OVER ( 220 ORDER BY d 221 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 222 ) FROM t1 223 } {1 21 2 21 3 21 4 21 5 21 6 21} 224 225 do_execsql_test 2.26 { 226 SELECT a, sum(d) OVER ( 227 PARTITION BY b 228 ORDER BY d 229 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 230 ) FROM t1 231 } {2 12 4 12 6 12 1 9 3 9 5 9} 232 233 do_execsql_test 2.27 { 234 SELECT a, sum(d) OVER ( 235 ORDER BY d 236 ROWS BETWEEN CURRENT ROW AND CURRENT ROW 237 ) FROM t1 238 } {1 1 2 2 3 3 4 4 5 5 6 6} 239 240 do_execsql_test 2.28 { 241 SELECT a, sum(d) OVER ( 242 PARTITION BY b 243 ORDER BY d 244 ROWS BETWEEN CURRENT ROW AND CURRENT ROW 245 ) FROM t1 246 } {2 2 4 4 6 6 1 1 3 3 5 5} 247 248 do_execsql_test 2.29 { 249 SELECT a, sum(d) OVER ( 250 ORDER BY d 251 RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING 252 ) FROM t1 253 } {1 21 2 20 3 18 4 15 5 11 6 6} 254 255 do_execsql_test 2.30 { 256 SELECT a, sum(d) OVER ( 257 ORDER BY b 258 RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING 259 ) FROM t1 260 } {2 21 4 21 6 21 1 9 3 9 5 9} 261 262 do_execsql_test 3.1 { 263 SELECT a, sum(d) OVER ( 264 PARTITION BY b ORDER BY d 265 RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING 266 ) FROM t1 267 } {2 12 4 10 6 6 1 9 3 8 5 5} 268 269 do_execsql_test 3.2 { 270 SELECT a, sum(d) OVER ( 271 ORDER BY b 272 RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING 273 ) FROM t1 274 } {2 21 4 21 6 21 1 9 3 9 5 9} 275 276 do_execsql_test 3.3 { 277 SELECT a, sum(d) OVER ( 278 ORDER BY d 279 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 280 ) FROM t1 281 } {1 21 2 21 3 21 4 21 5 21 6 21} 282 283 do_execsql_test 3.4 { 284 SELECT a, sum(d) OVER ( 285 ORDER BY d/2 286 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 287 ) FROM t1 288 } {1 1 2 3 3 6 4 10 5 15 6 21} 289 290 #========================================================================== 291 292 do_execsql_test 4.0 { 293 DROP TABLE IF EXISTS t2; 294 CREATE TABLE t2(a INTEGER PRIMARY KEY, b INTEGER); 295 INSERT INTO t2(a, b) VALUES 296 (1,0), (2,74), (3,41), (4,74), (5,23), (6,99), (7,26), (8,33), (9,2), 297 (10,89), (11,81), (12,96), (13,59), (14,38), (15,68), (16,39), (17,62), 298 (18,91), (19,46), (20,6), (21,99), (22,97), (23,27), (24,46), (25,78), 299 (26,54), (27,97), (28,8), (29,67), (30,29), (31,93), (32,84), (33,77), 300 (34,23), (35,16), (36,16), (37,93), (38,65), (39,35), (40,47), (41,7), 301 (42,86), (43,74), (44,61), (45,91), (46,85), (47,24), (48,85), (49,43), 302 (50,59), (51,12), (52,32), (53,56), (54,3), (55,91), (56,22), (57,90), 303 (58,55), (59,15), (60,28), (61,89), (62,25), (63,47), (64,1), (65,56), 304 (66,40), (67,43), (68,56), (69,16), (70,75), (71,36), (72,89), (73,98), 305 (74,76), (75,81), (76,4), (77,94), (78,42), (79,30), (80,78), (81,33), 306 (82,29), (83,53), (84,63), (85,2), (86,87), (87,37), (88,80), (89,84), 307 (90,72), (91,41), (92,9), (93,61), (94,73), (95,95), (96,65), (97,13), 308 (98,58), (99,96), (100,98), (101,1), (102,21), (103,74), (104,65), (105,35), 309 (106,5), (107,73), (108,11), (109,51), (110,87), (111,41), (112,12), (113,8), 310 (114,20), (115,31), (116,31), (117,15), (118,95), (119,22), (120,73), 311 (121,79), (122,88), (123,34), (124,8), (125,11), (126,49), (127,34), 312 (128,90), (129,59), (130,96), (131,60), (132,55), (133,75), (134,77), 313 (135,44), (136,2), (137,7), (138,85), (139,57), (140,74), (141,29), (142,70), 314 (143,59), (144,19), (145,39), (146,26), (147,26), (148,47), (149,80), 315 (150,90), (151,36), (152,58), (153,47), (154,9), (155,72), (156,72), (157,66), 316 (158,33), (159,93), (160,75), (161,64), (162,81), (163,9), (164,23), (165,37), 317 (166,13), (167,12), (168,14), (169,62), (170,91), (171,36), (172,91), 318 (173,33), (174,15), (175,34), (176,36), (177,99), (178,3), (179,95), (180,69), 319 (181,58), (182,52), (183,30), (184,50), (185,84), (186,10), (187,84), 320 (188,33), (189,21), (190,39), (191,44), (192,58), (193,30), (194,38), 321 (195,34), (196,83), (197,27), (198,82), (199,17), (200,7); 322 } {} 323 324 do_execsql_test 4.1 { 325 SELECT a, sum(b) OVER ( 326 PARTITION BY (b%10) 327 ORDER BY b 328 ) FROM t2 ORDER BY a; 329 } {1 0 2 754 3 251 4 754 5 101 6 1247 7 132 8 266 9 6 10 950 330 11 667 12 1052 13 535 14 128 15 428 16 250 17 336 18 1122 331 19 368 20 6 21 1247 22 1000 23 92 24 368 25 584 26 320 332 27 1000 28 24 29 478 30 133 31 1049 32 1090 33 632 34 101 333 35 54 36 54 37 1049 38 450 39 145 40 354 41 21 42 764 334 43 754 44 424 45 1122 46 930 47 42 48 930 49 352 50 535 335 51 42 52 118 53 536 54 6 55 1122 56 86 57 770 58 255 59 50 336 60 52 61 950 62 75 63 354 64 2 65 536 66 160 67 352 68 536 337 69 54 70 675 71 276 72 950 73 868 74 678 75 667 76 4 338 77 1184 78 160 79 120 80 584 81 266 82 133 83 405 84 468 339 85 6 86 806 87 166 88 500 89 1090 90 552 91 251 92 27 340 93 424 94 687 95 1215 96 450 97 32 98 360 99 1052 100 868 341 101 2 102 66 103 754 104 450 105 145 106 5 107 687 108 24 342 109 302 110 806 111 251 112 42 113 24 114 30 115 128 116 128 343 117 50 118 1215 119 86 120 687 121 683 122 672 123 178 124 24 344 125 24 126 299 127 178 128 770 129 535 130 1052 131 270 345 132 255 133 675 134 632 135 266 136 6 137 21 138 930 139 411 346 140 754 141 133 142 340 143 535 144 46 145 250 146 132 347 147 132 148 354 149 500 150 770 151 276 152 360 153 354 348 154 27 155 552 156 552 157 602 158 266 159 1049 160 675 349 161 384 162 667 163 27 164 101 165 166 166 32 167 42 168 18 350 169 336 170 1122 171 276 172 1122 173 266 174 50 175 178 351 176 276 177 1247 178 6 179 1215 180 604 181 360 182 212 352 183 120 184 210 185 1090 186 10 187 1090 188 266 189 66 353 190 250 191 266 192 360 193 120 194 128 195 178 196 770 354 197 92 198 634 199 38 200 21} 355 356 do_execsql_test 4.2 { 357 SELECT a, sum(b) OVER ( 358 PARTITION BY (b%10) 359 ORDER BY b 360 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 361 ) FROM t2 ORDER BY a; 362 } {1 0 2 754 3 251 4 754 5 101 6 1247 7 132 8 266 9 6 10 950 363 11 667 12 1052 13 535 14 128 15 428 16 250 17 336 18 1122 364 19 368 20 6 21 1247 22 1000 23 92 24 368 25 584 26 320 365 27 1000 28 24 29 478 30 133 31 1049 32 1090 33 632 34 101 366 35 54 36 54 37 1049 38 450 39 145 40 354 41 21 42 764 367 43 754 44 424 45 1122 46 930 47 42 48 930 49 352 50 535 368 51 42 52 118 53 536 54 6 55 1122 56 86 57 770 58 255 59 50 369 60 52 61 950 62 75 63 354 64 2 65 536 66 160 67 352 68 536 370 69 54 70 675 71 276 72 950 73 868 74 678 75 667 76 4 371 77 1184 78 160 79 120 80 584 81 266 82 133 83 405 84 468 372 85 6 86 806 87 166 88 500 89 1090 90 552 91 251 92 27 373 93 424 94 687 95 1215 96 450 97 32 98 360 99 1052 100 868 374 101 2 102 66 103 754 104 450 105 145 106 5 107 687 108 24 375 109 302 110 806 111 251 112 42 113 24 114 30 115 128 116 128 376 117 50 118 1215 119 86 120 687 121 683 122 672 123 178 124 24 377 125 24 126 299 127 178 128 770 129 535 130 1052 131 270 378 132 255 133 675 134 632 135 266 136 6 137 21 138 930 139 411 379 140 754 141 133 142 340 143 535 144 46 145 250 146 132 380 147 132 148 354 149 500 150 770 151 276 152 360 153 354 381 154 27 155 552 156 552 157 602 158 266 159 1049 160 675 382 161 384 162 667 163 27 164 101 165 166 166 32 167 42 168 18 383 169 336 170 1122 171 276 172 1122 173 266 174 50 175 178 384 176 276 177 1247 178 6 179 1215 180 604 181 360 182 212 385 183 120 184 210 185 1090 186 10 187 1090 188 266 189 66 386 190 250 191 266 192 360 193 120 194 128 195 178 196 770 387 197 92 198 634 199 38 200 21} 388 389 do_execsql_test 4.3 { 390 SELECT b, sum(b) OVER ( 391 ORDER BY b 392 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 393 ) FROM t2 ORDER BY b; 394 } {0 0 1 1 1 2 2 4 2 6 2 8 3 11 3 14 4 18 5 23 6 29 7 36 395 7 43 7 50 8 58 8 66 8 74 9 83 9 92 9 101 10 111 11 122 396 11 133 12 145 12 157 12 169 13 182 13 195 14 209 15 224 397 15 239 15 254 16 270 16 286 16 302 17 319 19 338 20 358 398 21 379 21 400 22 422 22 444 23 467 23 490 23 513 24 537 399 25 562 26 588 26 614 26 640 27 667 27 694 28 722 29 751 400 29 780 29 809 30 839 30 869 30 899 31 930 31 961 32 993 401 33 1026 33 1059 33 1092 33 1125 33 1158 34 1192 34 1226 402 34 1260 34 1294 35 1329 35 1364 36 1400 36 1436 36 1472 403 36 1508 37 1545 37 1582 38 1620 38 1658 39 1697 39 1736 404 39 1775 40 1815 41 1856 41 1897 41 1938 42 1980 43 2023 405 43 2066 44 2110 44 2154 46 2200 46 2246 47 2293 47 2340 406 47 2387 47 2434 49 2483 50 2533 51 2584 52 2636 53 2689 407 54 2743 55 2798 55 2853 56 2909 56 2965 56 3021 57 3078 408 58 3136 58 3194 58 3252 58 3310 59 3369 59 3428 59 3487 409 59 3546 60 3606 61 3667 61 3728 62 3790 62 3852 63 3915 410 64 3979 65 4044 65 4109 65 4174 66 4240 67 4307 68 4375 411 69 4444 70 4514 72 4586 72 4658 72 4730 73 4803 73 4876 412 73 4949 74 5023 74 5097 74 5171 74 5245 74 5319 75 5394 413 75 5469 75 5544 76 5620 77 5697 77 5774 78 5852 78 5930 414 79 6009 80 6089 80 6169 81 6250 81 6331 81 6412 82 6494 415 83 6577 84 6661 84 6745 84 6829 84 6913 85 6998 85 7083 416 85 7168 86 7254 87 7341 87 7428 88 7516 89 7605 89 7694 417 89 7783 90 7873 90 7963 90 8053 91 8144 91 8235 91 8326 418 91 8417 91 8508 93 8601 93 8694 93 8787 94 8881 95 8976 419 95 9071 95 9166 96 9262 96 9358 96 9454 97 9551 97 9648 420 98 9746 98 9844 99 9943 99 10042 99 10141} 421 422 do_execsql_test 4.4 { 423 SELECT b, sum(b) OVER ( 424 ORDER BY b 425 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 426 ) FROM t2 ORDER BY b; 427 } {0 10141 1 10141 1 10141 2 10141 2 10141 2 10141 3 10141 428 3 10141 4 10141 5 10141 6 10141 7 10141 7 10141 7 10141 429 8 10141 8 10141 8 10141 9 10141 9 10141 9 10141 10 10141 430 11 10141 11 10141 12 10141 12 10141 12 10141 13 10141 13 10141 431 14 10141 15 10141 15 10141 15 10141 16 10141 16 10141 16 10141 432 17 10141 19 10141 20 10141 21 10141 21 10141 22 10141 22 10141 433 23 10141 23 10141 23 10141 24 10141 25 10141 26 10141 26 10141 434 26 10141 27 10141 27 10141 28 10141 29 10141 29 10141 29 10141 435 30 10141 30 10141 30 10141 31 10141 31 10141 32 10141 33 10141 436 33 10141 33 10141 33 10141 33 10141 34 10141 34 10141 34 10141 437 34 10141 35 10141 35 10141 36 10141 36 10141 36 10141 36 10141 438 37 10141 37 10141 38 10141 38 10141 39 10141 39 10141 39 10141 439 40 10141 41 10141 41 10141 41 10141 42 10141 43 10141 43 10141 440 44 10141 44 10141 46 10141 46 10141 47 10141 47 10141 47 10141 441 47 10141 49 10141 50 10141 51 10141 52 10141 53 10141 54 10141 442 55 10141 55 10141 56 10141 56 10141 56 10141 57 10141 58 10141 443 58 10141 58 10141 58 10141 59 10141 59 10141 59 10141 59 10141 444 60 10141 61 10141 61 10141 62 10141 62 10141 63 10141 64 10141 445 65 10141 65 10141 65 10141 66 10141 67 10141 68 10141 69 10141 446 70 10141 72 10141 72 10141 72 10141 73 10141 73 10141 73 10141 447 74 10141 74 10141 74 10141 74 10141 74 10141 75 10141 75 10141 448 75 10141 76 10141 77 10141 77 10141 78 10141 78 10141 79 10141 449 80 10141 80 10141 81 10141 81 10141 81 10141 82 10141 83 10141 450 84 10141 84 10141 84 10141 84 10141 85 10141 85 10141 85 10141 451 86 10141 87 10141 87 10141 88 10141 89 10141 89 10141 89 10141 452 90 10141 90 10141 90 10141 91 10141 91 10141 91 10141 91 10141 453 91 10141 93 10141 93 10141 93 10141 94 10141 95 10141 95 10141 454 95 10141 96 10141 96 10141 96 10141 97 10141 97 10141 98 10141 455 98 10141 99 10141 99 10141 99 10141} 456 457 do_execsql_test 4.5 { 458 SELECT b, sum(b) OVER ( 459 ORDER BY b 460 RANGE BETWEEN CURRENT ROW AND CURRENT ROW 461 ) FROM t2 ORDER BY b; 462 } {0 0 1 2 1 2 2 6 2 6 2 6 3 6 3 6 4 4 5 5 6 6 7 21 463 7 21 7 21 8 24 8 24 8 24 9 27 9 27 9 27 10 10 11 22 464 11 22 12 36 12 36 12 36 13 26 13 26 14 14 15 45 15 45 465 15 45 16 48 16 48 16 48 17 17 19 19 20 20 21 42 21 42 466 22 44 22 44 23 69 23 69 23 69 24 24 25 25 26 78 26 78 467 26 78 27 54 27 54 28 28 29 87 29 87 29 87 30 90 30 90 468 30 90 31 62 31 62 32 32 33 165 33 165 33 165 33 165 33 165 469 34 136 34 136 34 136 34 136 35 70 35 70 36 144 36 144 470 36 144 36 144 37 74 37 74 38 76 38 76 39 117 39 117 39 117 471 40 40 41 123 41 123 41 123 42 42 43 86 43 86 44 88 44 88 472 46 92 46 92 47 188 47 188 47 188 47 188 49 49 50 50 51 51 473 52 52 53 53 54 54 55 110 55 110 56 168 56 168 56 168 57 57 474 58 232 58 232 58 232 58 232 59 236 59 236 59 236 59 236 475 60 60 61 122 61 122 62 124 62 124 63 63 64 64 65 195 65 195 476 65 195 66 66 67 67 68 68 69 69 70 70 72 216 72 216 72 216 477 73 219 73 219 73 219 74 370 74 370 74 370 74 370 74 370 478 75 225 75 225 75 225 76 76 77 154 77 154 78 156 78 156 479 79 79 80 160 80 160 81 243 81 243 81 243 82 82 83 83 84 336 480 84 336 84 336 84 336 85 255 85 255 85 255 86 86 87 174 481 87 174 88 88 89 267 89 267 89 267 90 270 90 270 90 270 482 91 455 91 455 91 455 91 455 91 455 93 279 93 279 93 279 483 94 94 95 285 95 285 95 285 96 288 96 288 96 288 97 194 484 97 194 98 196 98 196 99 297 99 297 99 297} 485 486 do_execsql_test 4.6.1 { 487 SELECT b, sum(b) OVER ( 488 RANGE BETWEEN CURRENT ROW AND CURRENT ROW 489 ) FROM t2 ORDER BY b; 490 } {0 10141 1 10141 1 10141 2 10141 2 10141 2 10141 3 10141 491 3 10141 4 10141 5 10141 6 10141 7 10141 7 10141 7 10141 492 8 10141 8 10141 8 10141 9 10141 9 10141 9 10141 10 10141 493 11 10141 11 10141 12 10141 12 10141 12 10141 13 10141 13 10141 494 14 10141 15 10141 15 10141 15 10141 16 10141 16 10141 16 10141 495 17 10141 19 10141 20 10141 21 10141 21 10141 22 10141 22 10141 496 23 10141 23 10141 23 10141 24 10141 25 10141 26 10141 26 10141 497 26 10141 27 10141 27 10141 28 10141 29 10141 29 10141 29 10141 498 30 10141 30 10141 30 10141 31 10141 31 10141 32 10141 33 10141 499 33 10141 33 10141 33 10141 33 10141 34 10141 34 10141 34 10141 500 34 10141 35 10141 35 10141 36 10141 36 10141 36 10141 36 10141 501 37 10141 37 10141 38 10141 38 10141 39 10141 39 10141 39 10141 502 40 10141 41 10141 41 10141 41 10141 42 10141 43 10141 43 10141 503 44 10141 44 10141 46 10141 46 10141 47 10141 47 10141 47 10141 504 47 10141 49 10141 50 10141 51 10141 52 10141 53 10141 54 10141 505 55 10141 55 10141 56 10141 56 10141 56 10141 57 10141 58 10141 506 58 10141 58 10141 58 10141 59 10141 59 10141 59 10141 59 10141 507 60 10141 61 10141 61 10141 62 10141 62 10141 63 10141 64 10141 508 65 10141 65 10141 65 10141 66 10141 67 10141 68 10141 69 10141 509 70 10141 72 10141 72 10141 72 10141 73 10141 73 10141 73 10141 510 74 10141 74 10141 74 10141 74 10141 74 10141 75 10141 75 10141 511 75 10141 76 10141 77 10141 77 10141 78 10141 78 10141 79 10141 512 80 10141 80 10141 81 10141 81 10141 81 10141 82 10141 83 10141 513 84 10141 84 10141 84 10141 84 10141 85 10141 85 10141 85 10141 514 86 10141 87 10141 87 10141 88 10141 89 10141 89 10141 89 10141 515 90 10141 90 10141 90 10141 91 10141 91 10141 91 10141 91 10141 516 91 10141 93 10141 93 10141 93 10141 94 10141 95 10141 95 10141 517 95 10141 96 10141 96 10141 96 10141 97 10141 97 10141 98 10141 518 98 10141 99 10141 99 10141 99 10141} 519 520 do_execsql_test 4.6.2 { 521 SELECT b, sum(b) OVER () FROM t2 ORDER BY b; 522 } {0 10141 1 10141 1 10141 2 10141 2 10141 2 10141 3 10141 523 3 10141 4 10141 5 10141 6 10141 7 10141 7 10141 7 10141 524 8 10141 8 10141 8 10141 9 10141 9 10141 9 10141 10 10141 525 11 10141 11 10141 12 10141 12 10141 12 10141 13 10141 13 10141 526 14 10141 15 10141 15 10141 15 10141 16 10141 16 10141 16 10141 527 17 10141 19 10141 20 10141 21 10141 21 10141 22 10141 22 10141 528 23 10141 23 10141 23 10141 24 10141 25 10141 26 10141 26 10141 529 26 10141 27 10141 27 10141 28 10141 29 10141 29 10141 29 10141 530 30 10141 30 10141 30 10141 31 10141 31 10141 32 10141 33 10141 531 33 10141 33 10141 33 10141 33 10141 34 10141 34 10141 34 10141 532 34 10141 35 10141 35 10141 36 10141 36 10141 36 10141 36 10141 533 37 10141 37 10141 38 10141 38 10141 39 10141 39 10141 39 10141 534 40 10141 41 10141 41 10141 41 10141 42 10141 43 10141 43 10141 535 44 10141 44 10141 46 10141 46 10141 47 10141 47 10141 47 10141 536 47 10141 49 10141 50 10141 51 10141 52 10141 53 10141 54 10141 537 55 10141 55 10141 56 10141 56 10141 56 10141 57 10141 58 10141 538 58 10141 58 10141 58 10141 59 10141 59 10141 59 10141 59 10141 539 60 10141 61 10141 61 10141 62 10141 62 10141 63 10141 64 10141 540 65 10141 65 10141 65 10141 66 10141 67 10141 68 10141 69 10141 541 70 10141 72 10141 72 10141 72 10141 73 10141 73 10141 73 10141 542 74 10141 74 10141 74 10141 74 10141 74 10141 75 10141 75 10141 543 75 10141 76 10141 77 10141 77 10141 78 10141 78 10141 79 10141 544 80 10141 80 10141 81 10141 81 10141 81 10141 82 10141 83 10141 545 84 10141 84 10141 84 10141 84 10141 85 10141 85 10141 85 10141 546 86 10141 87 10141 87 10141 88 10141 89 10141 89 10141 89 10141 547 90 10141 90 10141 90 10141 91 10141 91 10141 91 10141 91 10141 548 91 10141 93 10141 93 10141 93 10141 94 10141 95 10141 95 10141 549 95 10141 96 10141 96 10141 96 10141 97 10141 97 10141 98 10141 550 98 10141 99 10141 99 10141 99 10141} 551 552 do_execsql_test 4.6.3 { 553 SELECT b, sum(b) OVER ( 554 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 555 ) FROM t2 ORDER BY b; 556 } {0 10141 1 10141 1 10141 2 10141 2 10141 2 10141 3 10141 557 3 10141 4 10141 5 10141 6 10141 7 10141 7 10141 7 10141 558 8 10141 8 10141 8 10141 9 10141 9 10141 9 10141 10 10141 559 11 10141 11 10141 12 10141 12 10141 12 10141 13 10141 13 10141 560 14 10141 15 10141 15 10141 15 10141 16 10141 16 10141 16 10141 561 17 10141 19 10141 20 10141 21 10141 21 10141 22 10141 22 10141 562 23 10141 23 10141 23 10141 24 10141 25 10141 26 10141 26 10141 563 26 10141 27 10141 27 10141 28 10141 29 10141 29 10141 29 10141 564 30 10141 30 10141 30 10141 31 10141 31 10141 32 10141 33 10141 565 33 10141 33 10141 33 10141 33 10141 34 10141 34 10141 34 10141 566 34 10141 35 10141 35 10141 36 10141 36 10141 36 10141 36 10141 567 37 10141 37 10141 38 10141 38 10141 39 10141 39 10141 39 10141 568 40 10141 41 10141 41 10141 41 10141 42 10141 43 10141 43 10141 569 44 10141 44 10141 46 10141 46 10141 47 10141 47 10141 47 10141 570 47 10141 49 10141 50 10141 51 10141 52 10141 53 10141 54 10141 571 55 10141 55 10141 56 10141 56 10141 56 10141 57 10141 58 10141 572 58 10141 58 10141 58 10141 59 10141 59 10141 59 10141 59 10141 573 60 10141 61 10141 61 10141 62 10141 62 10141 63 10141 64 10141 574 65 10141 65 10141 65 10141 66 10141 67 10141 68 10141 69 10141 575 70 10141 72 10141 72 10141 72 10141 73 10141 73 10141 73 10141 576 74 10141 74 10141 74 10141 74 10141 74 10141 75 10141 75 10141 577 75 10141 76 10141 77 10141 77 10141 78 10141 78 10141 79 10141 578 80 10141 80 10141 81 10141 81 10141 81 10141 82 10141 83 10141 579 84 10141 84 10141 84 10141 84 10141 85 10141 85 10141 85 10141 580 86 10141 87 10141 87 10141 88 10141 89 10141 89 10141 89 10141 581 90 10141 90 10141 90 10141 91 10141 91 10141 91 10141 91 10141 582 91 10141 93 10141 93 10141 93 10141 94 10141 95 10141 95 10141 583 95 10141 96 10141 96 10141 96 10141 97 10141 97 10141 98 10141 584 98 10141 99 10141 99 10141 99 10141} 585 586 do_execsql_test 4.6.4 { 587 SELECT b, sum(b) OVER ( 588 RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING 589 ) FROM t2 ORDER BY b; 590 } {0 10141 1 10141 1 10141 2 10141 2 10141 2 10141 3 10141 591 3 10141 4 10141 5 10141 6 10141 7 10141 7 10141 7 10141 592 8 10141 8 10141 8 10141 9 10141 9 10141 9 10141 10 10141 593 11 10141 11 10141 12 10141 12 10141 12 10141 13 10141 13 10141 594 14 10141 15 10141 15 10141 15 10141 16 10141 16 10141 16 10141 595 17 10141 19 10141 20 10141 21 10141 21 10141 22 10141 22 10141 596 23 10141 23 10141 23 10141 24 10141 25 10141 26 10141 26 10141 597 26 10141 27 10141 27 10141 28 10141 29 10141 29 10141 29 10141 598 30 10141 30 10141 30 10141 31 10141 31 10141 32 10141 33 10141 599 33 10141 33 10141 33 10141 33 10141 34 10141 34 10141 34 10141 600 34 10141 35 10141 35 10141 36 10141 36 10141 36 10141 36 10141 601 37 10141 37 10141 38 10141 38 10141 39 10141 39 10141 39 10141 602 40 10141 41 10141 41 10141 41 10141 42 10141 43 10141 43 10141 603 44 10141 44 10141 46 10141 46 10141 47 10141 47 10141 47 10141 604 47 10141 49 10141 50 10141 51 10141 52 10141 53 10141 54 10141 605 55 10141 55 10141 56 10141 56 10141 56 10141 57 10141 58 10141 606 58 10141 58 10141 58 10141 59 10141 59 10141 59 10141 59 10141 607 60 10141 61 10141 61 10141 62 10141 62 10141 63 10141 64 10141 608 65 10141 65 10141 65 10141 66 10141 67 10141 68 10141 69 10141 609 70 10141 72 10141 72 10141 72 10141 73 10141 73 10141 73 10141 610 74 10141 74 10141 74 10141 74 10141 74 10141 75 10141 75 10141 611 75 10141 76 10141 77 10141 77 10141 78 10141 78 10141 79 10141 612 80 10141 80 10141 81 10141 81 10141 81 10141 82 10141 83 10141 613 84 10141 84 10141 84 10141 84 10141 85 10141 85 10141 85 10141 614 86 10141 87 10141 87 10141 88 10141 89 10141 89 10141 89 10141 615 90 10141 90 10141 90 10141 91 10141 91 10141 91 10141 91 10141 616 91 10141 93 10141 93 10141 93 10141 94 10141 95 10141 95 10141 617 95 10141 96 10141 96 10141 96 10141 97 10141 97 10141 98 10141 618 98 10141 99 10141 99 10141 99 10141} 619 620 do_execsql_test 4.7.1 { 621 SELECT b, sum(b) OVER ( 622 ROWS BETWEEN CURRENT ROW AND CURRENT ROW 623 ) FROM t2 ORDER BY 1, 2; 624 } {0 0 1 1 1 1 2 2 2 2 2 2 3 3 3 3 4 4 5 5 6 6 7 7 7 7 625 7 7 8 8 8 8 8 8 9 9 9 9 9 9 10 10 11 11 11 11 12 12 626 12 12 12 12 13 13 13 13 14 14 15 15 15 15 15 15 16 16 627 16 16 16 16 17 17 19 19 20 20 21 21 21 21 22 22 22 22 628 23 23 23 23 23 23 24 24 25 25 26 26 26 26 26 26 27 27 629 27 27 28 28 29 29 29 29 29 29 30 30 30 30 30 30 31 31 630 31 31 32 32 33 33 33 33 33 33 33 33 33 33 34 34 34 34 631 34 34 34 34 35 35 35 35 36 36 36 36 36 36 36 36 37 37 632 37 37 38 38 38 38 39 39 39 39 39 39 40 40 41 41 41 41 633 41 41 42 42 43 43 43 43 44 44 44 44 46 46 46 46 47 47 634 47 47 47 47 47 47 49 49 50 50 51 51 52 52 53 53 54 54 635 55 55 55 55 56 56 56 56 56 56 57 57 58 58 58 58 58 58 636 58 58 59 59 59 59 59 59 59 59 60 60 61 61 61 61 62 62 637 62 62 63 63 64 64 65 65 65 65 65 65 66 66 67 67 68 68 638 69 69 70 70 72 72 72 72 72 72 73 73 73 73 73 73 74 74 639 74 74 74 74 74 74 74 74 75 75 75 75 75 75 76 76 77 77 640 77 77 78 78 78 78 79 79 80 80 80 80 81 81 81 81 81 81 641 82 82 83 83 84 84 84 84 84 84 84 84 85 85 85 85 85 85 642 86 86 87 87 87 87 88 88 89 89 89 89 89 89 90 90 90 90 643 90 90 91 91 91 91 91 91 91 91 91 91 93 93 93 93 93 93 644 94 94 95 95 95 95 95 95 96 96 96 96 96 96 97 97 97 97 645 98 98 98 98 99 99 99 99 99 99} 646 647 do_execsql_test 4.7.2 { 648 SELECT b, sum(b) OVER ( 649 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 650 ) FROM t2 ORDER BY 1, 2; 651 } {0 0 1 3379 1 5443 2 372 2 4473 2 7074 3 2916 3 9096 4 4049 652 5 5643 6 1047 7 2205 7 7081 7 10141 8 1553 8 5926 8 6422 653 9 4883 9 7932 9 8497 10 9544 11 5727 11 6433 12 2825 12 5918 654 12 8582 13 5190 13 8570 14 8596 15 3189 15 6023 15 8924 655 16 1942 16 1958 16 3590 17 10134 19 7474 20 5946 21 5464 656 21 9682 22 3029 22 6140 23 212 23 1926 23 8520 24 2626 657 25 3331 26 337 26 7539 26 7565 27 1270 27 10035 28 3217 658 29 1649 29 4355 29 7326 30 4215 30 9400 30 9853 31 5977 659 31 6008 32 2857 33 370 33 4326 33 8175 33 8909 33 9661 660 34 6414 34 6516 34 8958 34 9925 35 2151 35 5638 36 3701 661 36 7818 36 8785 36 8994 37 4597 37 8557 38 735 38 9891 39 842 662 39 7513 39 9721 40 3475 41 115 41 4874 41 5906 42 4185 663 43 2754 43 3518 44 7072 44 9765 46 1041 46 1316 47 2198 664 47 3378 47 7612 47 7923 49 6482 50 9450 51 5778 52 9370 665 53 4408 54 1448 55 3174 55 6876 56 2913 56 3435 56 3574 666 57 7223 58 5248 58 7876 58 9318 58 9823 59 697 59 2813 667 59 6665 59 7455 60 6821 61 2426 61 4944 62 904 62 8658 668 63 4471 64 8407 65 2116 65 5177 65 5603 66 8142 67 1620 669 68 803 69 9260 70 7396 72 4833 72 8004 72 8076 73 5017 670 73 5716 73 6213 74 74 74 189 74 2365 74 5538 74 7297 75 3665 671 75 6951 75 8343 76 3964 77 1903 77 7028 78 1394 78 4293 672 79 6292 80 4677 80 7692 81 542 81 4045 81 8488 82 10117 673 83 10008 84 1826 84 4761 84 9534 84 9628 85 2602 85 2711 674 85 7166 86 2291 87 4560 87 5865 88 6380 89 461 89 3306 675 89 3790 90 3119 90 6606 90 7782 91 995 91 2517 91 3007 676 91 8749 91 8876 93 1742 93 2051 93 8268 94 4143 95 5112 677 95 6118 95 9191 96 638 96 5344 96 6761 97 1243 97 1545 678 98 3888 98 5442 99 311 99 1146 99 9093} 679 680 do_execsql_test 4.7.3 { 681 SELECT b, sum(b) OVER ( 682 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 683 ) FROM t2 ORDER BY 1, 2; 684 } {0 10141 1 10141 1 10141 2 10141 2 10141 2 10141 3 10141 685 3 10141 4 10141 5 10141 6 10141 7 10141 7 10141 7 10141 686 8 10141 8 10141 8 10141 9 10141 9 10141 9 10141 10 10141 687 11 10141 11 10141 12 10141 12 10141 12 10141 13 10141 13 10141 688 14 10141 15 10141 15 10141 15 10141 16 10141 16 10141 16 10141 689 17 10141 19 10141 20 10141 21 10141 21 10141 22 10141 22 10141 690 23 10141 23 10141 23 10141 24 10141 25 10141 26 10141 26 10141 691 26 10141 27 10141 27 10141 28 10141 29 10141 29 10141 29 10141 692 30 10141 30 10141 30 10141 31 10141 31 10141 32 10141 33 10141 693 33 10141 33 10141 33 10141 33 10141 34 10141 34 10141 34 10141 694 34 10141 35 10141 35 10141 36 10141 36 10141 36 10141 36 10141 695 37 10141 37 10141 38 10141 38 10141 39 10141 39 10141 39 10141 696 40 10141 41 10141 41 10141 41 10141 42 10141 43 10141 43 10141 697 44 10141 44 10141 46 10141 46 10141 47 10141 47 10141 47 10141 698 47 10141 49 10141 50 10141 51 10141 52 10141 53 10141 54 10141 699 55 10141 55 10141 56 10141 56 10141 56 10141 57 10141 58 10141 700 58 10141 58 10141 58 10141 59 10141 59 10141 59 10141 59 10141 701 60 10141 61 10141 61 10141 62 10141 62 10141 63 10141 64 10141 702 65 10141 65 10141 65 10141 66 10141 67 10141 68 10141 69 10141 703 70 10141 72 10141 72 10141 72 10141 73 10141 73 10141 73 10141 704 74 10141 74 10141 74 10141 74 10141 74 10141 75 10141 75 10141 705 75 10141 76 10141 77 10141 77 10141 78 10141 78 10141 79 10141 706 80 10141 80 10141 81 10141 81 10141 81 10141 82 10141 83 10141 707 84 10141 84 10141 84 10141 84 10141 85 10141 85 10141 85 10141 708 86 10141 87 10141 87 10141 88 10141 89 10141 89 10141 89 10141 709 90 10141 90 10141 90 10141 91 10141 91 10141 91 10141 91 10141 710 91 10141 93 10141 93 10141 93 10141 94 10141 95 10141 95 10141 711 95 10141 96 10141 96 10141 96 10141 97 10141 97 10141 98 10141 712 98 10141 99 10141 99 10141 99 10141} 713 714 do_execsql_test 4.7.4 { 715 SELECT b, sum(b) OVER ( 716 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING 717 ) FROM t2 ORDER BY 1, 2; 718 } {0 10141 1 4699 1 6763 2 3069 2 5670 2 9771 3 1048 3 7228 719 4 6096 5 4503 6 9100 7 7 7 3067 7 7943 8 3727 8 4223 8 8596 720 9 1653 9 2218 9 5267 10 607 11 3719 11 4425 12 1571 12 4235 721 12 7328 13 1584 13 4964 14 1559 15 1232 15 4133 15 6967 722 16 6567 16 8199 16 8215 17 24 19 2686 20 4215 21 480 21 4698 723 22 4023 22 7134 23 1644 23 8238 23 9952 24 7539 25 6835 724 26 2602 26 2628 26 9830 27 133 27 8898 28 6952 29 2844 725 29 5815 29 8521 30 318 30 771 30 5956 31 4164 31 4195 32 7316 726 33 513 33 1265 33 1999 33 5848 33 9804 34 250 34 1217 34 3659 727 34 3761 35 4538 35 8025 36 1183 36 1392 36 2359 36 6476 728 37 1621 37 5581 38 288 38 9444 39 459 39 2667 39 9338 40 6706 729 41 4276 41 5308 41 10067 42 5998 43 6666 43 7430 44 420 730 44 3113 46 8871 46 9146 47 2265 47 2576 47 6810 47 7990 731 49 3708 50 741 51 4414 52 823 53 5786 54 8747 55 3320 55 7022 732 56 6623 56 6762 56 7284 57 2975 58 376 58 881 58 2323 58 4951 733 59 2745 59 3535 59 7387 59 9503 60 3380 61 5258 61 7776 734 62 1545 62 9299 63 5733 64 1798 65 4603 65 5029 65 8090 735 66 2065 67 8588 68 9406 69 950 70 2815 72 2137 72 2209 736 72 5380 73 4001 73 4498 73 5197 74 2918 74 4677 74 7850 737 74 10026 74 10141 75 1873 75 3265 75 6551 76 6253 77 3190 738 77 8315 78 5926 78 8825 79 3928 80 2529 80 5544 81 1734 739 81 6177 81 9680 82 106 83 216 84 597 84 691 84 5464 84 8399 740 85 3060 85 7515 85 7624 86 7936 87 4363 87 5668 88 3849 741 89 6440 89 6924 89 9769 90 2449 90 3625 90 7112 91 1356 742 91 1483 91 7225 91 7715 91 9237 93 1966 93 8183 93 8492 743 94 6092 95 1045 95 4118 95 5124 96 3476 96 4893 96 9599 744 97 8693 97 8995 98 4797 98 6351 99 1147 99 9094 99 9929} 745 746 do_execsql_test 4.8.1 { 747 SELECT b, sum(b) OVER ( 748 ORDER BY a 749 ROWS BETWEEN CURRENT ROW AND CURRENT ROW 750 ) FROM t2 ORDER BY 1, 2; 751 } {0 0 1 1 1 1 2 2 2 2 2 2 3 3 3 3 4 4 5 5 6 6 7 7 7 7 752 7 7 8 8 8 8 8 8 9 9 9 9 9 9 10 10 11 11 11 11 12 12 753 12 12 12 12 13 13 13 13 14 14 15 15 15 15 15 15 16 16 754 16 16 16 16 17 17 19 19 20 20 21 21 21 21 22 22 22 22 755 23 23 23 23 23 23 24 24 25 25 26 26 26 26 26 26 27 27 756 27 27 28 28 29 29 29 29 29 29 30 30 30 30 30 30 31 31 757 31 31 32 32 33 33 33 33 33 33 33 33 33 33 34 34 34 34 758 34 34 34 34 35 35 35 35 36 36 36 36 36 36 36 36 37 37 759 37 37 38 38 38 38 39 39 39 39 39 39 40 40 41 41 41 41 760 41 41 42 42 43 43 43 43 44 44 44 44 46 46 46 46 47 47 761 47 47 47 47 47 47 49 49 50 50 51 51 52 52 53 53 54 54 762 55 55 55 55 56 56 56 56 56 56 57 57 58 58 58 58 58 58 763 58 58 59 59 59 59 59 59 59 59 60 60 61 61 61 61 62 62 764 62 62 63 63 64 64 65 65 65 65 65 65 66 66 67 67 68 68 765 69 69 70 70 72 72 72 72 72 72 73 73 73 73 73 73 74 74 766 74 74 74 74 74 74 74 74 75 75 75 75 75 75 76 76 77 77 767 77 77 78 78 78 78 79 79 80 80 80 80 81 81 81 81 81 81 768 82 82 83 83 84 84 84 84 84 84 84 84 85 85 85 85 85 85 769 86 86 87 87 87 87 88 88 89 89 89 89 89 89 90 90 90 90 770 90 90 91 91 91 91 91 91 91 91 91 91 93 93 93 93 93 93 771 94 94 95 95 95 95 95 95 96 96 96 96 96 96 97 97 97 97 772 98 98 98 98 99 99 99 99 99 99} 773 774 do_execsql_test 4.8.2 { 775 SELECT b, sum(b) OVER ( 776 ORDER BY a 777 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 778 ) FROM t2 ORDER BY 1, 2; 779 } {0 0 1 3379 1 5443 2 372 2 4473 2 7074 3 2916 3 9096 4 4049 780 5 5643 6 1047 7 2205 7 7081 7 10141 8 1553 8 5926 8 6422 781 9 4883 9 7932 9 8497 10 9544 11 5727 11 6433 12 2825 12 5918 782 12 8582 13 5190 13 8570 14 8596 15 3189 15 6023 15 8924 783 16 1942 16 1958 16 3590 17 10134 19 7474 20 5946 21 5464 784 21 9682 22 3029 22 6140 23 212 23 1926 23 8520 24 2626 785 25 3331 26 337 26 7539 26 7565 27 1270 27 10035 28 3217 786 29 1649 29 4355 29 7326 30 4215 30 9400 30 9853 31 5977 787 31 6008 32 2857 33 370 33 4326 33 8175 33 8909 33 9661 788 34 6414 34 6516 34 8958 34 9925 35 2151 35 5638 36 3701 789 36 7818 36 8785 36 8994 37 4597 37 8557 38 735 38 9891 39 842 790 39 7513 39 9721 40 3475 41 115 41 4874 41 5906 42 4185 791 43 2754 43 3518 44 7072 44 9765 46 1041 46 1316 47 2198 792 47 3378 47 7612 47 7923 49 6482 50 9450 51 5778 52 9370 793 53 4408 54 1448 55 3174 55 6876 56 2913 56 3435 56 3574 794 57 7223 58 5248 58 7876 58 9318 58 9823 59 697 59 2813 795 59 6665 59 7455 60 6821 61 2426 61 4944 62 904 62 8658 796 63 4471 64 8407 65 2116 65 5177 65 5603 66 8142 67 1620 797 68 803 69 9260 70 7396 72 4833 72 8004 72 8076 73 5017 798 73 5716 73 6213 74 74 74 189 74 2365 74 5538 74 7297 75 3665 799 75 6951 75 8343 76 3964 77 1903 77 7028 78 1394 78 4293 800 79 6292 80 4677 80 7692 81 542 81 4045 81 8488 82 10117 801 83 10008 84 1826 84 4761 84 9534 84 9628 85 2602 85 2711 802 85 7166 86 2291 87 4560 87 5865 88 6380 89 461 89 3306 803 89 3790 90 3119 90 6606 90 7782 91 995 91 2517 91 3007 804 91 8749 91 8876 93 1742 93 2051 93 8268 94 4143 95 5112 805 95 6118 95 9191 96 638 96 5344 96 6761 97 1243 97 1545 806 98 3888 98 5442 99 311 99 1146 99 9093} 807 808 do_execsql_test 4.8.3 { 809 SELECT b, sum(b) OVER ( 810 ORDER BY a 811 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 812 ) FROM t2 ORDER BY 1, 2; 813 } {0 10141 1 10141 1 10141 2 10141 2 10141 2 10141 3 10141 814 3 10141 4 10141 5 10141 6 10141 7 10141 7 10141 7 10141 815 8 10141 8 10141 8 10141 9 10141 9 10141 9 10141 10 10141 816 11 10141 11 10141 12 10141 12 10141 12 10141 13 10141 13 10141 817 14 10141 15 10141 15 10141 15 10141 16 10141 16 10141 16 10141 818 17 10141 19 10141 20 10141 21 10141 21 10141 22 10141 22 10141 819 23 10141 23 10141 23 10141 24 10141 25 10141 26 10141 26 10141 820 26 10141 27 10141 27 10141 28 10141 29 10141 29 10141 29 10141 821 30 10141 30 10141 30 10141 31 10141 31 10141 32 10141 33 10141 822 33 10141 33 10141 33 10141 33 10141 34 10141 34 10141 34 10141 823 34 10141 35 10141 35 10141 36 10141 36 10141 36 10141 36 10141 824 37 10141 37 10141 38 10141 38 10141 39 10141 39 10141 39 10141 825 40 10141 41 10141 41 10141 41 10141 42 10141 43 10141 43 10141 826 44 10141 44 10141 46 10141 46 10141 47 10141 47 10141 47 10141 827 47 10141 49 10141 50 10141 51 10141 52 10141 53 10141 54 10141 828 55 10141 55 10141 56 10141 56 10141 56 10141 57 10141 58 10141 829 58 10141 58 10141 58 10141 59 10141 59 10141 59 10141 59 10141 830 60 10141 61 10141 61 10141 62 10141 62 10141 63 10141 64 10141 831 65 10141 65 10141 65 10141 66 10141 67 10141 68 10141 69 10141 832 70 10141 72 10141 72 10141 72 10141 73 10141 73 10141 73 10141 833 74 10141 74 10141 74 10141 74 10141 74 10141 75 10141 75 10141 834 75 10141 76 10141 77 10141 77 10141 78 10141 78 10141 79 10141 835 80 10141 80 10141 81 10141 81 10141 81 10141 82 10141 83 10141 836 84 10141 84 10141 84 10141 84 10141 85 10141 85 10141 85 10141 837 86 10141 87 10141 87 10141 88 10141 89 10141 89 10141 89 10141 838 90 10141 90 10141 90 10141 91 10141 91 10141 91 10141 91 10141 839 91 10141 93 10141 93 10141 93 10141 94 10141 95 10141 95 10141 840 95 10141 96 10141 96 10141 96 10141 97 10141 97 10141 98 10141 841 98 10141 99 10141 99 10141 99 10141} 842 843 do_execsql_test 4.8.4 { 844 SELECT b, sum(b) OVER ( 845 ORDER BY a 846 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING 847 ) FROM t2 ORDER BY 1, 2; 848 } {0 10141 1 4699 1 6763 2 3069 2 5670 2 9771 3 1048 3 7228 849 4 6096 5 4503 6 9100 7 7 7 3067 7 7943 8 3727 8 4223 8 8596 850 9 1653 9 2218 9 5267 10 607 11 3719 11 4425 12 1571 12 4235 851 12 7328 13 1584 13 4964 14 1559 15 1232 15 4133 15 6967 852 16 6567 16 8199 16 8215 17 24 19 2686 20 4215 21 480 21 4698 853 22 4023 22 7134 23 1644 23 8238 23 9952 24 7539 25 6835 854 26 2602 26 2628 26 9830 27 133 27 8898 28 6952 29 2844 855 29 5815 29 8521 30 318 30 771 30 5956 31 4164 31 4195 32 7316 856 33 513 33 1265 33 1999 33 5848 33 9804 34 250 34 1217 34 3659 857 34 3761 35 4538 35 8025 36 1183 36 1392 36 2359 36 6476 858 37 1621 37 5581 38 288 38 9444 39 459 39 2667 39 9338 40 6706 859 41 4276 41 5308 41 10067 42 5998 43 6666 43 7430 44 420 860 44 3113 46 8871 46 9146 47 2265 47 2576 47 6810 47 7990 861 49 3708 50 741 51 4414 52 823 53 5786 54 8747 55 3320 55 7022 862 56 6623 56 6762 56 7284 57 2975 58 376 58 881 58 2323 58 4951 863 59 2745 59 3535 59 7387 59 9503 60 3380 61 5258 61 7776 864 62 1545 62 9299 63 5733 64 1798 65 4603 65 5029 65 8090 865 66 2065 67 8588 68 9406 69 950 70 2815 72 2137 72 2209 866 72 5380 73 4001 73 4498 73 5197 74 2918 74 4677 74 7850 867 74 10026 74 10141 75 1873 75 3265 75 6551 76 6253 77 3190 868 77 8315 78 5926 78 8825 79 3928 80 2529 80 5544 81 1734 869 81 6177 81 9680 82 106 83 216 84 597 84 691 84 5464 84 8399 870 85 3060 85 7515 85 7624 86 7936 87 4363 87 5668 88 3849 871 89 6440 89 6924 89 9769 90 2449 90 3625 90 7112 91 1356 872 91 1483 91 7225 91 7715 91 9237 93 1966 93 8183 93 8492 873 94 6092 95 1045 95 4118 95 5124 96 3476 96 4893 96 9599 874 97 8693 97 8995 98 4797 98 6351 99 1147 99 9094 99 9929} 875 876 877 do_test 4.9 { 878 set myres {} 879 foreach r [db eval {SELECT 880 rank() OVER win AS rank, 881 cume_dist() OVER win AS cume_dist FROM t1 882 WINDOW win AS (ORDER BY 1);}] { 883 lappend myres [format %.4f [set r]] 884 } 885 set res2 {1.0000 1.0000 1.0000 1.0000 1.0000 1.0000 1.0000 1.0000 1.0000 1.0000 1.0000 1.0000} 886 set i 0 887 foreach r [set myres] r2 [set res2] { 888 if {[set r]<([set r2]-0.0001) || [set r]>([set r2]+0.0001)} { 889 error "list element [set i] does not match: got=[set r] expected=[set r2]" 890 } 891 incr i 892 } 893 set {} {} 894 } {} 895 896 do_execsql_test 4.10 { 897 SELECT count(*) OVER (ORDER BY b) FROM t1 898 } {3 3 3 6 6 6} 899 900 do_execsql_test 4.11 { 901 SELECT count(distinct a) FILTER (WHERE b='odd') FROM t1 902 } {3} 903 904 #========================================================================== 905 906 do_execsql_test 5.0 { 907 DROP TABLE IF EXISTS t1; 908 CREATE TABLE t1(x INTEGER, y INTEGER); 909 INSERT INTO t1 VALUES(10, 1); 910 INSERT INTO t1 VALUES(20, 2); 911 INSERT INTO t1 VALUES(3, 3); 912 INSERT INTO t1 VALUES(2, 4); 913 INSERT INTO t1 VALUES(1, 5); 914 } {} 915 916 917 do_test 5.1 { 918 set myres {} 919 foreach r [db eval {SELECT avg(x) OVER (ORDER BY y) AS z FROM t1 ORDER BY z;}] { 920 lappend myres [format %.4f [set r]] 921 } 922 set res2 {7.2000 8.7500 10.0000 11.0000 15.0000} 923 set i 0 924 foreach r [set myres] r2 [set res2] { 925 if {[set r]<([set r2]-0.0001) || [set r]>([set r2]+0.0001)} { 926 error "list element [set i] does not match: got=[set r] expected=[set r2]" 927 } 928 incr i 929 } 930 set {} {} 931 } {} 932 933 #========================================================================== 934 935 do_execsql_test 6.0 { 936 DROP TABLE IF EXISTS t0; 937 CREATE TABLE t0(c0 INTEGER UNIQUE); 938 INSERT INTO t0 VALUES(0); 939 } {} 940 941 do_execsql_test 6.1 { 942 SELECT DENSE_RANK() OVER(), LAG(0) OVER() FROM t0; 943 } {1 {}} 944 945 do_execsql_test 6.2 { 946 SELECT * FROM t0 WHERE 947 (0, t0.c0) IN (SELECT DENSE_RANK() OVER(), LAG(0) OVER() FROM t0); 948 } {} 949 950 #========================================================================== 951 952 do_execsql_test 7.0 { 953 DROP TABLE IF EXISTS t1; 954 CREATE TABLE t1(a INTEGER, b INTEGER, c INTEGER); 955 INSERT INTO t1 VALUES(1, 1, 1); 956 INSERT INTO t1 VALUES(1, 2, 2); 957 INSERT INTO t1 VALUES(3, 3, 3); 958 INSERT INTO t1 VALUES(3, 4, 4); 959 } {} 960 961 do_execsql_test 7.1 { 962 SELECT c, sum(c) OVER win1 FROM t1 963 WINDOW win1 AS (ORDER BY b) 964 } {1 1 2 3 3 6 4 10} 965 966 do_execsql_test 7.2 { 967 SELECT c, sum(c) OVER win1 FROM t1 968 WINDOW win1 AS (PARTITION BY 1 ORDER BY b) 969 } {1 1 2 3 3 6 4 10} 970 971 do_execsql_test 7.3 { 972 SELECT c, sum(c) OVER win1 FROM t1 973 WINDOW win1 AS (ORDER BY 1) 974 } {1 10 2 10 3 10 4 10} 975 976 finish_test