github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/optbuilder/testdata/window (about) 1 # Adapted from the window logic test. 2 3 exec-ddl 4 CREATE TABLE kv ( 5 k INT PRIMARY KEY, 6 v INT, 7 w INT, 8 f FLOAT, 9 d DECIMAL, 10 s STRING, 11 b BOOL, 12 FAMILY (k, v, w, f, b), 13 FAMILY (d), 14 FAMILY (s) 15 ) 16 ---- 17 18 build 19 SELECT * FROM kv GROUP BY v, rank() OVER () 20 ---- 21 error (42P20): rank(): window functions are not allowed in GROUP BY 22 23 build 24 SELECT sum(rank() over ()) FROM kv 25 ---- 26 error (42P20): sum(): rank(): window functions are not allowed in aggregate 27 28 build 29 SELECT rank() OVER () FROM kv GROUP BY 1 30 ---- 31 error (42P20): rank(): window functions are not allowed in GROUP BY 32 33 build 34 SELECT count(w) OVER () FROM kv GROUP BY 1 35 ---- 36 error (42P20): count(): window functions are not allowed in GROUP BY 37 38 build 39 SELECT avg(avg(k) OVER ()) OVER () FROM kv 40 ---- 41 error (42P20): avg(): avg(): window function calls cannot be nested 42 43 build 44 INSERT INTO kv (k, v) VALUES (99, 100) RETURNING sum(v) OVER () 45 ---- 46 error (42P20): sum(): window functions are not allowed in RETURNING 47 48 build 49 SELECT sum(v) FROM kv GROUP BY k LIMIT sum(v) OVER () 50 ---- 51 error (42703): column "v" does not exist 52 53 build 54 SELECT sum(v) FROM kv GROUP BY k LIMIT 1 OFFSET sum(v) OVER () 55 ---- 56 error (42703): column "v" does not exist 57 58 build 59 INSERT INTO kv (k, v) VALUES (99, count(1) OVER ()) 60 ---- 61 error (42P20): count(): window functions are not allowed in VALUES 62 63 build 64 SELECT k FROM kv WHERE avg(k) OVER () > 1 65 ---- 66 error (42P20): avg(): window functions are not allowed in WHERE 67 68 build 69 SELECT avg(DISTINCT k) OVER () FROM kv 70 ---- 71 error (0A000): DISTINCT is not implemented for window functions 72 73 build 74 SELECT 1 FROM kv GROUP BY 1 HAVING sum(1) OVER (PARTITION BY 1) > 1 75 ---- 76 error (42P20): sum(): window functions are not allowed in HAVING 77 78 build 79 SELECT lag('foo'::string) OVER (), lag(1) OVER () FROM kv 80 ---- 81 project 82 ├── columns: lag:8 lag:9 83 └── window partition=() 84 ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 lag:8 lag:9 lag_1_arg1:10!null lag_1_arg2:11!null lag_1_arg3:12 lag_2_arg3:13 85 ├── project 86 │ ├── columns: lag_1_arg1:10!null lag_1_arg2:11!null lag_1_arg3:12 lag_2_arg3:13 k:1!null v:2 w:3 f:4 d:5 s:6 b:7 87 │ ├── scan kv 88 │ │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 89 │ └── projections 90 │ ├── 'foo'::STRING [as=lag_1_arg1:10] 91 │ ├── 1 [as=lag_1_arg2:11] 92 │ ├── NULL::STRING [as=lag_1_arg3:12] 93 │ └── NULL::INT8 [as=lag_2_arg3:13] 94 └── windows 95 ├── lag [as=lag:8] 96 │ ├── lag_1_arg1:10 97 │ ├── lag_1_arg2:11 98 │ └── lag_1_arg3:12 99 └── lag [as=lag:9] 100 ├── lag_1_arg2:11 101 ├── lag_1_arg2:11 102 └── lag_2_arg3:13 103 104 build 105 SELECT count(*) OVER () FROM kv 106 ---- 107 project 108 ├── columns: count:8 109 └── window partition=() 110 ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 count_rows:8 111 ├── scan kv 112 │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 113 └── windows 114 └── count-rows [as=count_rows:8] 115 116 build 117 SELECT lag((SELECT k FROM kv kv2 WHERE kv2.k = kv.k)) OVER () FROM kv 118 ---- 119 project 120 ├── columns: lag:15 121 └── window partition=() 122 ├── columns: kv.k:1!null kv.v:2 kv.w:3 kv.f:4 kv.d:5 kv.s:6 kv.b:7 lag:15 lag_1_arg1:16 lag_1_arg2:17!null lag_1_arg3:18 123 ├── project 124 │ ├── columns: lag_1_arg1:16 lag_1_arg2:17!null lag_1_arg3:18 kv.k:1!null kv.v:2 kv.w:3 kv.f:4 kv.d:5 kv.s:6 kv.b:7 125 │ ├── scan kv 126 │ │ └── columns: kv.k:1!null kv.v:2 kv.w:3 kv.f:4 kv.d:5 kv.s:6 kv.b:7 127 │ └── projections 128 │ ├── subquery [as=lag_1_arg1:16] 129 │ │ └── max1-row 130 │ │ ├── columns: kv2.k:8!null 131 │ │ └── project 132 │ │ ├── columns: kv2.k:8!null 133 │ │ └── select 134 │ │ ├── columns: kv2.k:8!null kv2.v:9 kv2.w:10 kv2.f:11 kv2.d:12 kv2.s:13 kv2.b:14 135 │ │ ├── scan kv2 136 │ │ │ └── columns: kv2.k:8!null kv2.v:9 kv2.w:10 kv2.f:11 kv2.d:12 kv2.s:13 kv2.b:14 137 │ │ └── filters 138 │ │ └── kv2.k:8 = kv.k:1 139 │ ├── 1 [as=lag_1_arg2:17] 140 │ └── NULL::INT8 [as=lag_1_arg3:18] 141 └── windows 142 └── lag [as=lag:15] 143 ├── lag_1_arg1:16 144 ├── lag_1_arg2:17 145 └── lag_1_arg3:18 146 147 build 148 SELECT lag(1) OVER (), lead(1) OVER () FROM kv 149 ---- 150 project 151 ├── columns: lag:8 lead:9 152 └── window partition=() 153 ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 lag:8 lead:9 lag_1_arg1:10!null lag_1_arg3:11 154 ├── project 155 │ ├── columns: lag_1_arg1:10!null lag_1_arg3:11 k:1!null v:2 w:3 f:4 d:5 s:6 b:7 156 │ ├── scan kv 157 │ │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 158 │ └── projections 159 │ ├── 1 [as=lag_1_arg1:10] 160 │ └── NULL::INT8 [as=lag_1_arg3:11] 161 └── windows 162 ├── lag [as=lag:8] 163 │ ├── lag_1_arg1:10 164 │ ├── lag_1_arg1:10 165 │ └── lag_1_arg3:11 166 └── lead [as=lead:9] 167 ├── lag_1_arg1:10 168 ├── lag_1_arg1:10 169 └── lag_1_arg3:11 170 171 build 172 SELECT 173 lag(1) OVER (PARTITION BY k, v), 174 lag(1) OVER (PARTITION BY k), 175 lag(1) OVER (PARTITION BY v), 176 lead(1) OVER (PARTITION BY k), 177 lead(1) OVER (PARTITION BY v) 178 FROM kv 179 ---- 180 project 181 ├── columns: lag:8 lag:9 lag:10 lead:11 lead:12 182 └── window partition=(2) 183 ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 lag:8 lag:9 lag:10 lead:11 lead:12 lag_1_arg1:13!null lag_1_arg3:14 184 ├── window partition=(1) 185 │ ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 lag:8 lag:9 lead:11 lag_1_arg1:13!null lag_1_arg3:14 186 │ ├── window partition=(1,2) 187 │ │ ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 lag:8 lag_1_arg1:13!null lag_1_arg3:14 188 │ │ ├── project 189 │ │ │ ├── columns: lag_1_arg1:13!null lag_1_arg3:14 k:1!null v:2 w:3 f:4 d:5 s:6 b:7 190 │ │ │ ├── scan kv 191 │ │ │ │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 192 │ │ │ └── projections 193 │ │ │ ├── 1 [as=lag_1_arg1:13] 194 │ │ │ └── NULL::INT8 [as=lag_1_arg3:14] 195 │ │ └── windows 196 │ │ └── lag [as=lag:8] 197 │ │ ├── lag_1_arg1:13 198 │ │ ├── lag_1_arg1:13 199 │ │ └── lag_1_arg3:14 200 │ └── windows 201 │ ├── lag [as=lag:9] 202 │ │ ├── lag_1_arg1:13 203 │ │ ├── lag_1_arg1:13 204 │ │ └── lag_1_arg3:14 205 │ └── lead [as=lead:11] 206 │ ├── lag_1_arg1:13 207 │ ├── lag_1_arg1:13 208 │ └── lag_1_arg3:14 209 └── windows 210 ├── lag [as=lag:10] 211 │ ├── lag_1_arg1:13 212 │ ├── lag_1_arg1:13 213 │ └── lag_1_arg3:14 214 └── lead [as=lead:12] 215 ├── lag_1_arg1:13 216 ├── lag_1_arg1:13 217 └── lag_1_arg3:14 218 219 build 220 SELECT 221 lag(1) OVER (PARTITION BY k, v), 222 lag(1) OVER (PARTITION BY k ORDER BY v), 223 lag(1) OVER (PARTITION BY v ORDER BY f), 224 lead(1) OVER (PARTITION BY k ORDER BY v), 225 lead(1) OVER (PARTITION BY v) 226 FROM kv 227 ---- 228 project 229 ├── columns: lag:8 lag:9 lag:10 lead:11 lead:12 230 └── window partition=(2) 231 ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 lag:8 lag:9 lag:10 lead:11 lead:12 lag_1_arg1:13!null lag_1_arg3:14 232 ├── window partition=(2) ordering=+4 233 │ ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 lag:8 lag:9 lag:10 lead:11 lag_1_arg1:13!null lag_1_arg3:14 234 │ ├── window partition=(1) ordering=+2 235 │ │ ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 lag:8 lag:9 lead:11 lag_1_arg1:13!null lag_1_arg3:14 236 │ │ ├── window partition=(1,2) 237 │ │ │ ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 lag:8 lag_1_arg1:13!null lag_1_arg3:14 238 │ │ │ ├── project 239 │ │ │ │ ├── columns: lag_1_arg1:13!null lag_1_arg3:14 k:1!null v:2 w:3 f:4 d:5 s:6 b:7 240 │ │ │ │ ├── scan kv 241 │ │ │ │ │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 242 │ │ │ │ └── projections 243 │ │ │ │ ├── 1 [as=lag_1_arg1:13] 244 │ │ │ │ └── NULL::INT8 [as=lag_1_arg3:14] 245 │ │ │ └── windows 246 │ │ │ └── lag [as=lag:8] 247 │ │ │ ├── lag_1_arg1:13 248 │ │ │ ├── lag_1_arg1:13 249 │ │ │ └── lag_1_arg3:14 250 │ │ └── windows 251 │ │ ├── lag [as=lag:9] 252 │ │ │ ├── lag_1_arg1:13 253 │ │ │ ├── lag_1_arg1:13 254 │ │ │ └── lag_1_arg3:14 255 │ │ └── lead [as=lead:11] 256 │ │ ├── lag_1_arg1:13 257 │ │ ├── lag_1_arg1:13 258 │ │ └── lag_1_arg3:14 259 │ └── windows 260 │ └── lag [as=lag:10] 261 │ ├── lag_1_arg1:13 262 │ ├── lag_1_arg1:13 263 │ └── lag_1_arg3:14 264 └── windows 265 └── lead [as=lead:12] 266 ├── lag_1_arg1:13 267 ├── lag_1_arg1:13 268 └── lag_1_arg3:14 269 270 build 271 SELECT lag(1, 2) OVER (), lead(1, 2) OVER () FROM kv 272 ---- 273 project 274 ├── columns: lag:8 lead:9 275 └── window partition=() 276 ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 lag:8 lead:9 lag_1_arg1:10!null lag_1_arg2:11!null lag_1_arg3:12 277 ├── project 278 │ ├── columns: lag_1_arg1:10!null lag_1_arg2:11!null lag_1_arg3:12 k:1!null v:2 w:3 f:4 d:5 s:6 b:7 279 │ ├── scan kv 280 │ │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 281 │ └── projections 282 │ ├── 1 [as=lag_1_arg1:10] 283 │ ├── 2 [as=lag_1_arg2:11] 284 │ └── NULL::INT8 [as=lag_1_arg3:12] 285 └── windows 286 ├── lag [as=lag:8] 287 │ ├── lag_1_arg1:10 288 │ ├── lag_1_arg2:11 289 │ └── lag_1_arg3:12 290 └── lead [as=lead:9] 291 ├── lag_1_arg1:10 292 ├── lag_1_arg2:11 293 └── lag_1_arg3:12 294 295 build 296 SELECT lag(1, 2, 3) OVER (), lead(1, 2, 3) OVER () FROM kv 297 ---- 298 project 299 ├── columns: lag:8 lead:9 300 └── window partition=() 301 ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 lag:8 lead:9 lag_1_arg1:10!null lag_1_arg2:11!null lag_1_arg3:12!null 302 ├── project 303 │ ├── columns: lag_1_arg1:10!null lag_1_arg2:11!null lag_1_arg3:12!null k:1!null v:2 w:3 f:4 d:5 s:6 b:7 304 │ ├── scan kv 305 │ │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 306 │ └── projections 307 │ ├── 1 [as=lag_1_arg1:10] 308 │ ├── 2 [as=lag_1_arg2:11] 309 │ └── 3 [as=lag_1_arg3:12] 310 └── windows 311 ├── lag [as=lag:8] 312 │ ├── lag_1_arg1:10 313 │ ├── lag_1_arg2:11 314 │ └── lag_1_arg3:12 315 └── lead [as=lead:9] 316 ├── lag_1_arg1:10 317 ├── lag_1_arg2:11 318 └── lag_1_arg3:12 319 320 build 321 SELECT avg(k) OVER () FROM kv 322 ---- 323 project 324 ├── columns: avg:8 325 └── window partition=() 326 ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 avg:8 327 ├── scan kv 328 │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 329 └── windows 330 └── avg [as=avg:8] 331 └── k:1 332 333 build 334 SELECT x FROM (SELECT avg(k) OVER () AS x FROM kv) 335 ---- 336 project 337 ├── columns: x:8 338 └── window partition=() 339 ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 avg:8 340 ├── scan kv 341 │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 342 └── windows 343 └── avg [as=avg:8] 344 └── k:1 345 346 build 347 SELECT avg(DISTINCT k) OVER () FROM kv 348 ---- 349 error (0A000): DISTINCT is not implemented for window functions 350 351 build 352 SELECT avg(k) FILTER (WHERE k > 5) OVER () FROM kv 353 ---- 354 project 355 ├── columns: avg:8 356 └── window partition=() 357 ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 avg:8 avg_1_filter:9!null 358 ├── project 359 │ ├── columns: avg_1_filter:9!null k:1!null v:2 w:3 f:4 d:5 s:6 b:7 360 │ ├── scan kv 361 │ │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 362 │ └── projections 363 │ └── k:1 > 5 [as=avg_1_filter:9] 364 └── windows 365 └── agg-filter [as=avg:8] 366 ├── avg 367 │ └── k:1 368 └── avg_1_filter:9 369 370 build 371 SELECT avg(k), max(v) OVER () FROM kv ORDER BY 1 372 ---- 373 error (42803): column "v" must appear in the GROUP BY clause or be used in an aggregate function 374 375 build 376 SELECT avg(k) OVER () FROM kv ORDER BY 1 377 ---- 378 sort 379 ├── columns: avg:8 380 ├── ordering: +8 381 └── project 382 ├── columns: avg:8 383 └── window partition=() 384 ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 avg:8 385 ├── scan kv 386 │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 387 └── windows 388 └── avg [as=avg:8] 389 └── k:1 390 391 build 392 SELECT k, v, rank() OVER w FROM kv WINDOW w AS () 393 ---- 394 project 395 ├── columns: k:1!null v:2 rank:8 396 └── window partition=() 397 ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 rank:8 398 ├── scan kv 399 │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 400 └── windows 401 └── rank [as=rank:8] 402 403 build 404 SELECT k, v, first_value(v) OVER () FROM kv 405 ---- 406 project 407 ├── columns: k:1!null v:2 first_value:8 408 └── window partition=() 409 ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 first_value:8 410 ├── scan kv 411 │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 412 └── windows 413 └── first-value [as=first_value:8] 414 └── v:2 415 416 build 417 SELECT avg(k), max(v), min(w), 2 + row_number() OVER () FROM kv ORDER BY 1 418 ---- 419 project 420 ├── columns: avg:8 max:9 min:10 "?column?":12 421 ├── ordering: +8 422 ├── window partition=() 423 │ ├── columns: avg:8 max:9 min:10 row_number:11 424 │ ├── scalar-group-by 425 │ │ ├── columns: avg:8 max:9 min:10 426 │ │ ├── project 427 │ │ │ ├── columns: k:1!null v:2 w:3 428 │ │ │ └── scan kv 429 │ │ │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 430 │ │ └── aggregations 431 │ │ ├── avg [as=avg:8] 432 │ │ │ └── k:1 433 │ │ ├── max [as=max:9] 434 │ │ │ └── v:2 435 │ │ └── min [as=min:10] 436 │ │ └── w:3 437 │ └── windows 438 │ └── row-number [as=row_number:11] 439 └── projections 440 └── 2 + row_number:11 [as="?column?":12] 441 442 build 443 SELECT k, rank() OVER (), dense_rank() OVER (), percent_rank() OVER (), cume_dist() OVER () FROM kv 444 ---- 445 project 446 ├── columns: k:1!null rank:8 dense_rank:9 percent_rank:10 cume_dist:11 447 └── window partition=() 448 ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 rank:8 dense_rank:9 percent_rank:10 cume_dist:11 449 ├── scan kv 450 │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 451 └── windows 452 ├── rank [as=rank:8] 453 ├── dense-rank [as=dense_rank:9] 454 ├── percent-rank [as=percent_rank:10] 455 └── cume-dist [as=cume_dist:11] 456 457 build 458 SELECT k, rank() OVER (), rank() OVER () FROM kv 459 ---- 460 project 461 ├── columns: k:1!null rank:8 rank:8 462 └── window partition=() 463 ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 rank:8 464 ├── scan kv 465 │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 466 └── windows 467 └── rank [as=rank:8] 468 469 build 470 SELECT k, rank() OVER (), row_number() OVER () FROM kv 471 ---- 472 project 473 ├── columns: k:1!null rank:8 row_number:9 474 └── window partition=() 475 ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 rank:8 row_number:9 476 ├── scan kv 477 │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 478 └── windows 479 ├── rank [as=rank:8] 480 └── row-number [as=row_number:9] 481 482 build 483 SELECT k, rank() OVER (), row_number() OVER () FROM kv ORDER BY 1 484 ---- 485 sort 486 ├── columns: k:1!null rank:8 row_number:9 487 ├── ordering: +1 488 └── project 489 ├── columns: k:1!null rank:8 row_number:9 490 └── window partition=() 491 ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 rank:8 row_number:9 492 ├── scan kv 493 │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 494 └── windows 495 ├── rank [as=rank:8] 496 └── row-number [as=row_number:9] 497 498 build 499 SELECT k, v, rank() OVER (PARTITION BY v) FROM kv ORDER BY 1 500 ---- 501 sort 502 ├── columns: k:1!null v:2 rank:8 503 ├── ordering: +1 504 └── project 505 ├── columns: k:1!null v:2 rank:8 506 └── window partition=(2) 507 ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 rank:8 508 ├── scan kv 509 │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 510 └── windows 511 └── rank [as=rank:8] 512 513 build 514 SELECT k, row_number() OVER (PARTITION BY v), rank() OVER (PARTITION BY v) FROM kv ORDER BY 1 515 ---- 516 sort 517 ├── columns: k:1!null row_number:8 rank:9 518 ├── ordering: +1 519 └── project 520 ├── columns: k:1!null row_number:8 rank:9 521 └── window partition=(2) 522 ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 row_number:8 rank:9 523 ├── scan kv 524 │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 525 └── windows 526 ├── row-number [as=row_number:8] 527 └── rank [as=rank:9] 528 529 build 530 SELECT k, v, ntile(1) OVER () FROM kv 531 ---- 532 project 533 ├── columns: k:1!null v:2 ntile:8 534 └── window partition=() 535 ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 ntile:8 ntile_1_arg1:9!null 536 ├── project 537 │ ├── columns: ntile_1_arg1:9!null k:1!null v:2 w:3 f:4 d:5 s:6 b:7 538 │ ├── scan kv 539 │ │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 540 │ └── projections 541 │ └── 1 [as=ntile_1_arg1:9] 542 └── windows 543 └── ntile [as=ntile:8] 544 └── ntile_1_arg1:9 545 546 build 547 SELECT k, v, ntile(1) OVER (), ntile(50) OVER () FROM kv 548 ---- 549 project 550 ├── columns: k:1!null v:2 ntile:8 ntile:9 551 └── window partition=() 552 ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 ntile:8 ntile:9 ntile_1_arg1:10!null ntile_2_arg1:11!null 553 ├── project 554 │ ├── columns: ntile_1_arg1:10!null ntile_2_arg1:11!null k:1!null v:2 w:3 f:4 d:5 s:6 b:7 555 │ ├── scan kv 556 │ │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 557 │ └── projections 558 │ ├── 1 [as=ntile_1_arg1:10] 559 │ └── 50 [as=ntile_2_arg1:11] 560 └── windows 561 ├── ntile [as=ntile:8] 562 │ └── ntile_1_arg1:10 563 └── ntile [as=ntile:9] 564 └── ntile_2_arg1:11 565 566 build 567 SELECT k, v, nth_value('foo', 1) OVER () FROM kv 568 ---- 569 project 570 ├── columns: k:1!null v:2 nth_value:8 571 └── window partition=() 572 ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 nth_value:8 nth_value_1_arg1:9!null nth_value_1_arg2:10!null 573 ├── project 574 │ ├── columns: nth_value_1_arg1:9!null nth_value_1_arg2:10!null k:1!null v:2 w:3 f:4 d:5 s:6 b:7 575 │ ├── scan kv 576 │ │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 577 │ └── projections 578 │ ├── 'foo' [as=nth_value_1_arg1:9] 579 │ └── 1 [as=nth_value_1_arg2:10] 580 └── windows 581 └── nth-value [as=nth_value:8] 582 ├── nth_value_1_arg1:9 583 └── nth_value_1_arg2:10 584 585 build 586 SELECT k, v, nth_value(1, k) OVER () FROM kv 587 ---- 588 project 589 ├── columns: k:1!null v:2 nth_value:8 590 └── window partition=() 591 ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 nth_value:8 nth_value_1_arg1:9!null 592 ├── project 593 │ ├── columns: nth_value_1_arg1:9!null k:1!null v:2 w:3 f:4 d:5 s:6 b:7 594 │ ├── scan kv 595 │ │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 596 │ └── projections 597 │ └── 1 [as=nth_value_1_arg1:9] 598 └── windows 599 └── nth-value [as=nth_value:8] 600 ├── nth_value_1_arg1:9 601 └── k:1 602 603 # Partitions 604 605 build 606 SELECT row_number() OVER (PARTITION BY rank() OVER ()) FROM kv 607 ---- 608 error (42P20): rank(): window function calls cannot be nested 609 610 build 611 SELECT row_number() OVER (PARTITION BY rank() OVER () + 1) FROM kv 612 ---- 613 error (42P20): rank(): window function calls cannot be nested 614 615 build 616 SELECT row_number() OVER (PARTITION BY v) FROM kv 617 ---- 618 project 619 ├── columns: row_number:8 620 └── window partition=(2) 621 ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 row_number:8 622 ├── scan kv 623 │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 624 └── windows 625 └── row-number [as=row_number:8] 626 627 build 628 SELECT v, row_number() OVER (PARTITION BY v) FROM kv 629 ---- 630 project 631 ├── columns: v:2 row_number:8 632 └── window partition=(2) 633 ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 row_number:8 634 ├── scan kv 635 │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 636 └── windows 637 └── row-number [as=row_number:8] 638 639 build 640 SELECT v, row_number() OVER (PARTITION BY v+1) FROM kv 641 ---- 642 project 643 ├── columns: v:2 row_number:8 644 └── window partition=(9) 645 ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 row_number:8 row_number_1_partition_1:9 646 ├── project 647 │ ├── columns: row_number_1_partition_1:9 k:1!null v:2 w:3 f:4 d:5 s:6 b:7 648 │ ├── scan kv 649 │ │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 650 │ └── projections 651 │ └── v:2 + 1 [as=row_number_1_partition_1:9] 652 └── windows 653 └── row-number [as=row_number:8] 654 655 build 656 SELECT v, row_number() OVER (PARTITION BY avg(k)) FROM kv GROUP BY v 657 ---- 658 project 659 ├── columns: v:2 row_number:9 660 └── window partition=(8) 661 ├── columns: v:2 avg:8!null row_number:9 662 ├── group-by 663 │ ├── columns: v:2 avg:8!null 664 │ ├── grouping columns: v:2 665 │ ├── project 666 │ │ ├── columns: k:1!null v:2 667 │ │ └── scan kv 668 │ │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 669 │ └── aggregations 670 │ └── avg [as=avg:8] 671 │ └── k:1 672 └── windows 673 └── row-number [as=row_number:9] 674 675 # TODO(justin): expand these tuples. 676 build 677 SELECT k, row_number() OVER (PARTITION BY (k, v)) FROM kv 678 ---- 679 project 680 ├── columns: k:1!null row_number:8 681 └── window partition=(1,2) 682 ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 row_number:8 683 ├── scan kv 684 │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 685 └── windows 686 └── row-number [as=row_number:8] 687 688 build 689 SELECT k, row_number() OVER (PARTITION BY kv.*) FROM kv 690 ---- 691 project 692 ├── columns: k:1!null row_number:8 693 └── window partition=(1-7) 694 ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 row_number:8 695 ├── scan kv 696 │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 697 └── windows 698 └── row-number [as=row_number:8] 699 700 build 701 SELECT row_number() OVER (PARTITION BY v), rank() OVER (PARTITION BY v, f) FROM kv 702 ---- 703 project 704 ├── columns: row_number:8 rank:9 705 └── window partition=(2,4) 706 ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 row_number:8 rank:9 707 ├── window partition=(2) 708 │ ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 row_number:8 709 │ ├── scan kv 710 │ │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 711 │ └── windows 712 │ └── row-number [as=row_number:8] 713 └── windows 714 └── rank [as=rank:9] 715 716 # Ordering 717 718 build 719 SELECT row_number() OVER (ORDER BY v) FROM kv 720 ---- 721 project 722 ├── columns: row_number:8 723 └── window partition=() ordering=+2 724 ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 row_number:8 725 ├── scan kv 726 │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 727 └── windows 728 └── row-number [as=row_number:8] 729 730 build 731 SELECT k, v, rank() OVER (ORDER BY k) FROM kv ORDER BY 1 732 ---- 733 sort 734 ├── columns: k:1!null v:2 rank:8 735 ├── ordering: +1 736 └── project 737 ├── columns: k:1!null v:2 rank:8 738 └── window partition=() ordering=+1 739 ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 rank:8 740 ├── scan kv 741 │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 742 └── windows 743 └── rank [as=rank:8] 744 745 # Ensure tuples in orderings get expanded. 746 747 build 748 SELECT 749 k, 750 v, 751 rank() OVER (ORDER BY (v, w)), 752 row_number() OVER (ORDER BY (v, w), f), 753 dense_rank() OVER (ORDER BY f, (v, w)) 754 FROM kv ORDER BY 1 755 ---- 756 sort 757 ├── columns: k:1!null v:2 rank:8 row_number:9 dense_rank:10 758 ├── ordering: +1 759 └── project 760 ├── columns: k:1!null v:2 rank:8 row_number:9 dense_rank:10 761 └── window partition=() ordering=+4,+2,+3 762 ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 rank:8 row_number:9 dense_rank:10 763 ├── window partition=() ordering=+2,+3,+4 764 │ ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 rank:8 row_number:9 765 │ ├── window partition=() ordering=+2,+3 766 │ │ ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 rank:8 767 │ │ ├── scan kv 768 │ │ │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 769 │ │ └── windows 770 │ │ └── rank [as=rank:8] 771 │ └── windows 772 │ └── row-number [as=row_number:9] 773 └── windows 774 └── dense-rank [as=dense_rank:10] 775 776 build 777 SELECT k, v, w, v - w + 2 + row_number() OVER (PARTITION BY v, k ORDER BY w) FROM kv ORDER BY 1 778 ---- 779 sort 780 ├── columns: k:1!null v:2 w:3 "?column?":9 781 ├── ordering: +1 782 └── project 783 ├── columns: "?column?":9 k:1!null v:2 w:3 784 ├── window partition=(1,2) ordering=+3 785 │ ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 row_number:8 786 │ ├── scan kv 787 │ │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 788 │ └── windows 789 │ └── row-number [as=row_number:8] 790 └── projections 791 └── ((v:2 - w:3) + 2) + row_number:8 [as="?column?":9] 792 793 build 794 SELECT 795 DISTINCT ON (row_number() OVER (ORDER BY (k, v))) w 796 FROM 797 kv 798 ORDER BY 799 row_number() OVER (ORDER BY (k, v)) DESC 800 ---- 801 distinct-on 802 ├── columns: w:3 [hidden: row_number:8] 803 ├── grouping columns: row_number:8 804 ├── ordering: -8 805 ├── sort 806 │ ├── columns: w:3 row_number:8 807 │ ├── ordering: -8 808 │ └── project 809 │ ├── columns: w:3 row_number:8 810 │ └── window partition=() ordering=+1,+2 811 │ ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 row_number:8 812 │ ├── scan kv 813 │ │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 814 │ └── windows 815 │ └── row-number [as=row_number:8] 816 └── aggregations 817 └── first-agg [as=w:3] 818 └── w:3 819 820 # Frames 821 822 # Default is unbounded to current-row 823 build 824 SELECT avg(k) OVER () FROM kv 825 ---- 826 project 827 ├── columns: avg:8 828 └── window partition=() 829 ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 avg:8 830 ├── scan kv 831 │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 832 └── windows 833 └── avg [as=avg:8] 834 └── k:1 835 836 build 837 SELECT avg(k) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED PRECEDING) FROM kv 838 ---- 839 error (42601): at or near "preceding": syntax error: frame end cannot be UNBOUNDED PRECEDING 840 841 build 842 SELECT avg(k) OVER (RANGE BETWEEN UNBOUNDED FOLLOWING AND UNBOUNDED FOLLOWING) FROM kv 843 ---- 844 error (42601): at or near "following": syntax error: frame start cannot be UNBOUNDED FOLLOWING 845 846 build 847 SELECT 848 avg(k) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), 849 avg(k) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 850 avg(k) OVER (RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), 851 avg(k) OVER (RANGE BETWEEN CURRENT ROW AND CURRENT ROW) 852 FROM kv 853 ---- 854 project 855 ├── columns: avg:8 avg:9 avg:10 avg:11 856 └── window partition=() 857 ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 avg:8 avg:9 avg:10 avg:11 858 ├── scan kv 859 │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 860 └── windows 861 ├── avg [as=avg:8, frame="range from unbounded to unbounded"] 862 │ └── k:1 863 ├── avg [as=avg:9] 864 │ └── k:1 865 ├── avg [as=avg:10, frame="range from current-row to unbounded"] 866 │ └── k:1 867 └── avg [as=avg:11, frame="range from current-row to current-row"] 868 └── k:1 869 870 build 871 SELECT 872 avg(k) OVER (ORDER BY v RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), 873 avg(k) OVER (ORDER BY v RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 874 avg(k) OVER (ORDER BY v RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), 875 avg(k) OVER (ORDER BY v RANGE BETWEEN CURRENT ROW AND CURRENT ROW) 876 FROM kv 877 ---- 878 project 879 ├── columns: avg:8 avg:9 avg:10 avg:11 880 └── window partition=() ordering=+2 881 ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 avg:8 avg:9 avg:10 avg:11 882 ├── scan kv 883 │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 884 └── windows 885 ├── avg [as=avg:8, frame="range from unbounded to unbounded"] 886 │ └── k:1 887 ├── avg [as=avg:9] 888 │ └── k:1 889 ├── avg [as=avg:10, frame="range from current-row to unbounded"] 890 │ └── k:1 891 └── avg [as=avg:11, frame="range from current-row to current-row"] 892 └── k:1 893 894 build 895 SELECT 896 avg(k) OVER (RANGE UNBOUNDED PRECEDING), 897 avg(k) OVER (RANGE CURRENT ROW) 898 FROM kv 899 ---- 900 project 901 ├── columns: avg:8 avg:9 902 └── window partition=() 903 ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 avg:8 avg:9 904 ├── scan kv 905 │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 906 └── windows 907 ├── avg [as=avg:8] 908 │ └── k:1 909 └── avg [as=avg:9, frame="range from current-row to current-row"] 910 └── k:1 911 912 # TODO(justin): add support for offset expressions. 913 build 914 SELECT avg(k) OVER (ORDER BY v RANGE BETWEEN k - 10 PRECEDING AND CURRENT ROW) FROM kv 915 ---- 916 error (42P10): argument of RANGE must not contain variables 917 918 build 919 SELECT avg(k) OVER (ORDER BY v RANGE BETWEEN UNBOUNDED PRECEDING AND 10 FOLLOWING) FROM kv 920 ---- 921 project 922 ├── columns: avg:8 923 └── window partition=() ordering=+2 924 ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 avg:8 925 ├── scan kv 926 │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 927 └── windows 928 └── window-to-offset [as=avg:8, frame="range from unbounded to offset"] 929 ├── avg 930 │ └── k:1 931 └── 10 932 933 build 934 SELECT 935 avg(v) OVER (PARTITION BY w ORDER BY f RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS avg_price 936 FROM kv 937 ---- 938 project 939 ├── columns: avg_price:8 940 └── window partition=(3) ordering=+4 941 ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 avg:8 942 ├── scan kv 943 │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 944 └── windows 945 └── window-to-offset [as=avg:8, frame="range from offset to offset"] 946 ├── window-from-offset 947 │ ├── avg 948 │ │ └── v:2 949 │ └── 1.0 950 └── 1.0 951 952 build 953 SELECT 954 avg(v) OVER (PARTITION BY w ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS avg_price 955 FROM kv 956 ---- 957 project 958 ├── columns: avg_price:8 959 └── window partition=(3) 960 ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 avg:8 961 ├── scan kv 962 │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 963 └── windows 964 └── window-to-offset [as=avg:8, frame="rows from offset to offset"] 965 ├── window-from-offset 966 │ ├── avg 967 │ │ └── v:2 968 │ └── 1 969 └── 1 970 971 build 972 SELECT 973 avg(v) OVER ( 974 PARTITION BY 975 w 976 ROWS 977 BETWEEN (SELECT count(*) FROM kv) PRECEDING AND 1 FOLLOWING 978 ) 979 FROM 980 kv 981 ---- 982 project 983 ├── columns: avg:16 984 └── window partition=(3) 985 ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 avg:16 986 ├── scan kv 987 │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 988 └── windows 989 └── window-to-offset [as=avg:16, frame="rows from offset to offset"] 990 ├── window-from-offset 991 │ ├── avg 992 │ │ └── v:2 993 │ └── subquery 994 │ └── max1-row 995 │ ├── columns: count_rows:15!null 996 │ └── scalar-group-by 997 │ ├── columns: count_rows:15!null 998 │ ├── project 999 │ │ └── scan kv 1000 │ │ └── columns: k:8!null v:9 w:10 f:11 d:12 s:13 b:14 1001 │ └── aggregations 1002 │ └── count-rows [as=count_rows:15] 1003 └── 1 1004 1005 # TODO(justin): consider adding rules to hoist correlated subqueries in the 1006 # OFFSET positions. 1007 build 1008 SELECT 1009 avg(v) OVER ( 1010 PARTITION BY 1011 w 1012 ROWS 1013 BETWEEN (SELECT count(*) FROM kv inner_table WHERE inner_table.k = outer_table.v) PRECEDING AND 1 FOLLOWING 1014 ) 1015 FROM 1016 kv outer_table 1017 ---- 1018 error (42P10): argument of ROWS must not contain variables 1019 1020 build 1021 SELECT 1022 avg(v) OVER (ORDER BY v GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING) 1023 FROM kv 1024 ---- 1025 project 1026 ├── columns: avg:8 1027 └── window partition=() ordering=+2 1028 ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 avg:8 1029 ├── scan kv 1030 │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 1031 └── windows 1032 └── window-to-offset [as=avg:8, frame="groups from offset to offset"] 1033 ├── window-from-offset 1034 │ ├── avg 1035 │ │ └── v:2 1036 │ └── 1 1037 └── 1 1038 1039 build 1040 SELECT 1041 avg(v) OVER (ORDER BY k GROUPS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING) 1042 FROM kv 1043 ---- 1044 project 1045 ├── columns: avg:8 1046 └── window partition=() ordering=+1 1047 ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 avg:8 1048 ├── scan kv 1049 │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 1050 └── windows 1051 └── window-from-offset [as=avg:8, frame="groups from offset to unbounded"] 1052 ├── avg 1053 │ └── v:2 1054 └── 1 1055 1056 build 1057 SELECT avg(v) OVER (ORDER BY f RANGE BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) FROM kv 1058 ---- 1059 project 1060 ├── columns: avg:8 1061 └── window partition=() ordering=+4 1062 ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 avg:8 1063 ├── scan kv 1064 │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 1065 └── windows 1066 └── window-from-offset [as=avg:8, frame="range from offset to unbounded"] 1067 ├── avg 1068 │ └── v:2 1069 └── 1.0 1070 1071 build 1072 SELECT 1073 avg(v) OVER (ORDER BY f RANGE BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING), 1074 avg(v) OVER (ORDER BY d RANGE BETWEEN 2 FOLLOWING AND UNBOUNDED FOLLOWING) 1075 FROM kv 1076 ---- 1077 project 1078 ├── columns: avg:8 avg:9 1079 └── window partition=() ordering=+5 1080 ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 avg:8 avg:9 1081 ├── window partition=() ordering=+4 1082 │ ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 avg:8 1083 │ ├── scan kv 1084 │ │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 1085 │ └── windows 1086 │ └── window-from-offset [as=avg:8, frame="range from offset to unbounded"] 1087 │ ├── avg 1088 │ │ └── v:2 1089 │ └── 1.0 1090 └── windows 1091 └── window-from-offset [as=avg:9, frame="range from offset to unbounded"] 1092 ├── avg 1093 │ └── v:2 1094 └── 2 1095 1096 # Ensure window functions get appropriately deduplicated. 1097 1098 build 1099 SELECT 1100 avg(k) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), 1101 avg(k) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 1102 avg(k) OVER (RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), 1103 avg(k) OVER (RANGE BETWEEN CURRENT ROW AND CURRENT ROW), 1104 avg(k) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), 1105 avg(k) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 1106 avg(k) OVER (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), 1107 avg(k) OVER (ROWS BETWEEN CURRENT ROW AND CURRENT ROW), 1108 avg(k) OVER (ORDER BY v GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), 1109 avg(k) OVER (ORDER BY v GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 1110 avg(k) OVER (ORDER BY v GROUPS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), 1111 avg(k) OVER (ORDER BY v GROUPS BETWEEN CURRENT ROW AND CURRENT ROW), 1112 1 + avg(k) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), 1113 1 + avg(k) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 1114 1 + avg(k) OVER (RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), 1115 1 + avg(k) OVER (RANGE BETWEEN CURRENT ROW AND CURRENT ROW), 1116 1 + avg(k) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), 1117 1 + avg(k) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 1118 1 + avg(k) OVER (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), 1119 1 + avg(k) OVER (ROWS BETWEEN CURRENT ROW AND CURRENT ROW), 1120 1 + avg(k) OVER (ORDER BY v GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), 1121 1 + avg(k) OVER (ORDER BY v GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 1122 1 + avg(k) OVER (ORDER BY v GROUPS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), 1123 1 + avg(k) OVER (ORDER BY v GROUPS BETWEEN CURRENT ROW AND CURRENT ROW) 1124 FROM 1125 kv 1126 ---- 1127 project 1128 ├── columns: avg:8 avg:9 avg:10 avg:11 avg:12 avg:13 avg:14 avg:15 avg:16 avg:17 avg:18 avg:19 "?column?":20 "?column?":21 "?column?":22 "?column?":23 "?column?":24 "?column?":25 "?column?":26 "?column?":27 "?column?":28 "?column?":29 "?column?":30 "?column?":31 1129 ├── window partition=() ordering=+2 1130 │ ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 avg:8 avg:9 avg:10 avg:11 avg:12 avg:13 avg:14 avg:15 avg:16 avg:17 avg:18 avg:19 1131 │ ├── window partition=() 1132 │ │ ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 avg:8 avg:9 avg:10 avg:11 avg:12 avg:13 avg:14 avg:15 1133 │ │ ├── scan kv 1134 │ │ │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 1135 │ │ └── windows 1136 │ │ ├── avg [as=avg:8, frame="range from unbounded to unbounded"] 1137 │ │ │ └── k:1 1138 │ │ ├── avg [as=avg:9] 1139 │ │ │ └── k:1 1140 │ │ ├── avg [as=avg:10, frame="range from current-row to unbounded"] 1141 │ │ │ └── k:1 1142 │ │ ├── avg [as=avg:11, frame="range from current-row to current-row"] 1143 │ │ │ └── k:1 1144 │ │ ├── avg [as=avg:12, frame="rows from unbounded to unbounded"] 1145 │ │ │ └── k:1 1146 │ │ ├── avg [as=avg:13, frame="rows from unbounded to current-row"] 1147 │ │ │ └── k:1 1148 │ │ ├── avg [as=avg:14, frame="rows from current-row to unbounded"] 1149 │ │ │ └── k:1 1150 │ │ └── avg [as=avg:15, frame="rows from current-row to current-row"] 1151 │ │ └── k:1 1152 │ └── windows 1153 │ ├── avg [as=avg:16, frame="groups from unbounded to unbounded"] 1154 │ │ └── k:1 1155 │ ├── avg [as=avg:17, frame="groups from unbounded to current-row"] 1156 │ │ └── k:1 1157 │ ├── avg [as=avg:18, frame="groups from current-row to unbounded"] 1158 │ │ └── k:1 1159 │ └── avg [as=avg:19, frame="groups from current-row to current-row"] 1160 │ └── k:1 1161 └── projections 1162 ├── 1 + avg:8 [as="?column?":20] 1163 ├── 1 + avg:9 [as="?column?":21] 1164 ├── 1 + avg:10 [as="?column?":22] 1165 ├── 1 + avg:11 [as="?column?":23] 1166 ├── 1 + avg:12 [as="?column?":24] 1167 ├── 1 + avg:13 [as="?column?":25] 1168 ├── 1 + avg:14 [as="?column?":26] 1169 ├── 1 + avg:15 [as="?column?":27] 1170 ├── 1 + avg:16 [as="?column?":28] 1171 ├── 1 + avg:17 [as="?column?":29] 1172 ├── 1 + avg:18 [as="?column?":30] 1173 └── 1 + avg:19 [as="?column?":31] 1174 1175 build 1176 SELECT 1177 avg(k) OVER (ORDER BY v RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), 1178 avg(k) OVER (ORDER BY v RANGE BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING), 1179 avg(k) OVER (ORDER BY v RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING), 1180 avg(k) OVER (ORDER BY v RANGE BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING), 1181 1 + avg(k) OVER (ORDER BY v RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), 1182 1 + avg(k) OVER (ORDER BY v RANGE BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING), 1183 1 + avg(k) OVER (ORDER BY v RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING), 1184 1 + avg(k) OVER (ORDER BY v RANGE BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING) 1185 FROM 1186 kv 1187 ---- 1188 project 1189 ├── columns: avg:8 avg:9 avg:10 avg:11 "?column?":12 "?column?":13 "?column?":14 "?column?":15 1190 ├── window partition=() ordering=+2 1191 │ ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 avg:8 avg:9 avg:10 avg:11 1192 │ ├── scan kv 1193 │ │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 1194 │ └── windows 1195 │ ├── avg [as=avg:8, frame="range from unbounded to unbounded"] 1196 │ │ └── k:1 1197 │ ├── window-from-offset [as=avg:9, frame="range from offset to unbounded"] 1198 │ │ ├── avg 1199 │ │ │ └── k:1 1200 │ │ └── 1 1201 │ ├── window-to-offset [as=avg:10, frame="range from offset to offset"] 1202 │ │ ├── window-from-offset 1203 │ │ │ ├── avg 1204 │ │ │ │ └── k:1 1205 │ │ │ └── 1 1206 │ │ └── 1 1207 │ └── window-to-offset [as=avg:11, frame="range from unbounded to offset"] 1208 │ ├── avg 1209 │ │ └── k:1 1210 │ └── 1 1211 └── projections 1212 ├── 1 + avg:8 [as="?column?":12] 1213 ├── 1 + avg:9 [as="?column?":13] 1214 ├── 1 + avg:10 [as="?column?":14] 1215 └── 1 + avg:11 [as="?column?":15] 1216 1217 # Filter. 1218 1219 build 1220 SELECT rank() FILTER (WHERE k = 1) OVER () FROM kv 1221 ---- 1222 error (42809): FILTER specified but rank() is not an aggregate function 1223 1224 build 1225 SELECT avg(f) FILTER (WHERE k = 1) OVER () FROM kv 1226 ---- 1227 project 1228 ├── columns: avg:8 1229 └── window partition=() 1230 ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 avg:8 avg_1_filter:9!null 1231 ├── project 1232 │ ├── columns: avg_1_filter:9!null k:1!null v:2 w:3 f:4 d:5 s:6 b:7 1233 │ ├── scan kv 1234 │ │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 1235 │ └── projections 1236 │ └── k:1 = 1 [as=avg_1_filter:9] 1237 └── windows 1238 └── agg-filter [as=avg:8] 1239 ├── avg 1240 │ └── f:4 1241 └── avg_1_filter:9 1242 1243 build 1244 SELECT count(*) FILTER (WHERE true) OVER (), count(*) FILTER (WHERE false) OVER () FROM kv 1245 ---- 1246 project 1247 ├── columns: count:8 count:9 1248 └── window partition=() 1249 ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 count:8 count:9 count_1_arg1:10!null count_2_filter:11!null 1250 ├── project 1251 │ ├── columns: count_1_arg1:10!null count_2_filter:11!null k:1!null v:2 w:3 f:4 d:5 s:6 b:7 1252 │ ├── scan kv 1253 │ │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 1254 │ └── projections 1255 │ ├── true [as=count_1_arg1:10] 1256 │ └── false [as=count_2_filter:11] 1257 └── windows 1258 ├── agg-filter [as=count:8] 1259 │ ├── count 1260 │ │ └── count_1_arg1:10 1261 │ └── count_1_arg1:10 1262 └── agg-filter [as=count:9] 1263 ├── count 1264 │ └── count_1_arg1:10 1265 └── count_2_filter:11 1266 1267 build 1268 SELECT 1269 count(*) FILTER (WHERE false) OVER (), 1270 count(*) FILTER (WHERE false) OVER (), 1271 avg(k) FILTER (WHERE false) OVER () 1272 FROM 1273 kv 1274 ---- 1275 project 1276 ├── columns: count:8 count:8 avg:9 1277 └── window partition=() 1278 ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 count:8 avg:9 count_1_arg1:10!null count_1_filter:11!null 1279 ├── project 1280 │ ├── columns: count_1_arg1:10!null count_1_filter:11!null k:1!null v:2 w:3 f:4 d:5 s:6 b:7 1281 │ ├── scan kv 1282 │ │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 1283 │ └── projections 1284 │ ├── true [as=count_1_arg1:10] 1285 │ └── false [as=count_1_filter:11] 1286 └── windows 1287 ├── agg-filter [as=count:8] 1288 │ ├── count 1289 │ │ └── count_1_arg1:10 1290 │ └── count_1_filter:11 1291 └── agg-filter [as=avg:9] 1292 ├── avg 1293 │ └── k:1 1294 └── count_1_filter:11 1295 1296 # Named windows. 1297 1298 build 1299 SELECT 1300 rank() OVER (w) 1301 FROM kv WINDOW w as (ORDER BY v) 1302 ---- 1303 project 1304 ├── columns: rank:8 1305 └── window partition=() ordering=+2 1306 ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 rank:8 1307 ├── scan kv 1308 │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 1309 └── windows 1310 └── rank [as=rank:8] 1311 1312 build 1313 SELECT 1314 rank() OVER (w) 1315 FROM kv WINDOW w as (ORDER BY v) 1316 ---- 1317 project 1318 ├── columns: rank:8 1319 └── window partition=() ordering=+2 1320 ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 rank:8 1321 ├── scan kv 1322 │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 1323 └── windows 1324 └── rank [as=rank:8] 1325 1326 build 1327 SELECT 1328 rank() OVER (w), 1329 rank() OVER w, 1330 row_number() OVER (w2), 1331 row_number() OVER (w2 ORDER BY f), 1332 row_number() OVER w2 1333 FROM kv WINDOW w as (ORDER BY v), w2 as (PARTITION BY v) 1334 ---- 1335 project 1336 ├── columns: rank:8 rank:9 row_number:10 row_number:11 row_number:12 1337 └── window partition=(2) ordering=+4 1338 ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 rank:8 rank:9 row_number:10 row_number:11 row_number:12 1339 ├── window partition=(2) 1340 │ ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 rank:8 rank:9 row_number:10 row_number:12 1341 │ ├── window partition=() ordering=+2 1342 │ │ ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 rank:8 rank:9 1343 │ │ ├── scan kv 1344 │ │ │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 1345 │ │ └── windows 1346 │ │ ├── rank [as=rank:8] 1347 │ │ └── rank [as=rank:9] 1348 │ └── windows 1349 │ ├── row-number [as=row_number:10] 1350 │ └── row-number [as=row_number:12] 1351 └── windows 1352 └── row-number [as=row_number:11] 1353 1354 build 1355 SELECT 1356 rank() OVER w 1357 FROM kv WINDOW w as (ORDER BY v), w2 as (PARTITION BY v) 1358 ---- 1359 project 1360 ├── columns: rank:8 1361 └── window partition=() ordering=+2 1362 ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 rank:8 1363 ├── scan kv 1364 │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 1365 └── windows 1366 └── rank [as=rank:8] 1367 1368 build 1369 SELECT 1370 rank() OVER w 1371 FROM kv WINDOW w as (ORDER BY v) 1372 ORDER BY rank() OVER w 1373 ---- 1374 sort 1375 ├── columns: rank:8 1376 ├── ordering: +8 1377 └── project 1378 ├── columns: rank:8 1379 └── window partition=() ordering=+2 1380 ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 rank:8 1381 ├── scan kv 1382 │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 1383 └── windows 1384 └── rank [as=rank:8] 1385 1386 build 1387 SELECT 1388 rank() OVER w 1389 FROM kv 1390 WHERE rank() OVER w < 4 1391 WINDOW w as (ORDER BY v) 1392 ---- 1393 error (42P20): rank(): window functions are not allowed in WHERE 1394 1395 # Window names don't descend through scopes. 1396 build 1397 SELECT 1398 (SELECT rank() OVER w) 1399 FROM kv WINDOW w as (ORDER BY v) 1400 ---- 1401 error (42704): window "w" does not exist 1402 1403 # Window names may only be used a single time. 1404 build 1405 SELECT 1406 rank() OVER (w) 1407 FROM kv WINDOW w as (ORDER BY v), w as (PARTITION BY v) 1408 ---- 1409 error (42P20): window "w" is already defined 1410 1411 # Rules for overriding a window definition. 1412 build 1413 SELECT rank() OVER (w ORDER BY f) FROM kv WINDOW w as (ORDER BY v) 1414 ---- 1415 error (42P20): cannot override ORDER BY clause of window "w" 1416 1417 build 1418 SELECT rank() OVER (w PARTITION BY f) FROM kv WINDOW w as (ORDER BY v) 1419 ---- 1420 error (42P20): cannot override PARTITION BY clause of window "w" 1421 1422 build 1423 SELECT rank() OVER (w) FROM kv WINDOW w as (ORDER BY v RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 1424 ---- 1425 error (42P20): cannot copy window "w" because it has a frame clause 1426 1427 build 1428 SELECT rank() OVER (ORDER BY k GROUPS k PRECEDING) FROM kv 1429 ---- 1430 error (42P10): argument of GROUPS must not contain variables 1431 1432 build 1433 SELECT 1434 rank() OVER (RANGE UNBOUNDED PRECEDING EXCLUDE CURRENT ROW), 1435 rank() OVER (RANGE UNBOUNDED PRECEDING EXCLUDE GROUP), 1436 rank() OVER (RANGE UNBOUNDED PRECEDING EXCLUDE TIES), 1437 rank() OVER (RANGE UNBOUNDED PRECEDING EXCLUDE NO OTHERS) 1438 FROM kv 1439 ---- 1440 project 1441 ├── columns: rank:8 rank:9 rank:10 rank:11 1442 └── window partition=() 1443 ├── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 rank:8 rank:9 rank:10 rank:11 1444 ├── scan kv 1445 │ └── columns: k:1!null v:2 w:3 f:4 d:5 s:6 b:7 1446 └── windows 1447 ├── rank [as=rank:8, frame="range from unbounded to current-row exclude current row"] 1448 ├── rank [as=rank:9, frame="range from unbounded to current-row exclude group"] 1449 ├── rank [as=rank:10, frame="range from unbounded to current-row exclude ties"] 1450 └── rank [as=rank:11] 1451 1452 exec-ddl 1453 CREATE TABLE table1 (col5 CHAR, col8 INT2); 1454 ---- 1455 1456 build 1457 SELECT 1458 min(tab_536191.col5) OVER ( 1459 ROWS BETWEEN tab_536191.col8 FOLLOWING AND 1 FOLLOWING 1460 ) 1461 FROM 1462 table1 AS tab_536191 1463 GROUP BY 1464 tab_536191.col8, tab_536191.col5 1465 ---- 1466 error (42P10): argument of ROWS must not contain variables