github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/opt/exec/execbuilder/testdata/window (about) 1 # LogicTest: local 2 3 statement ok 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 statement OK 18 INSERT INTO kv VALUES 19 (1, 2, 3, 1.0, 1, 'a', true), 20 (3, 4, 5, 2, 8, 'a', true), 21 (5, NULL, 5, 9.9, -321, NULL, false), 22 (6, 2, 3, 4.4, 4.4, 'b', true), 23 (7, 2, 2, 6, 7.9, 'b', true), 24 (8, 4, 2, 3, 3, 'A', false) 25 26 statement ok 27 SET tracing = on,kv,results; SELECT k, stddev(d) OVER w FROM kv WINDOW w as (PARTITION BY v) ORDER BY variance(d) OVER w, k; SET tracing = off 28 29 query T rowsort 30 SELECT message FROM [SHOW KV TRACE FOR SESSION] WITH ORDINALITY 31 WHERE message LIKE 'fetched:%' OR message LIKE 'output row%' 32 ORDER BY message LIKE 'fetched:%' DESC, ordinality ASC 33 ---- 34 fetched: /kv/primary/1/v -> /2 35 fetched: /kv/primary/1/d -> 1 36 fetched: /kv/primary/1/s -> 'a' 37 fetched: /kv/primary/3/v -> /4 38 fetched: /kv/primary/3/d -> 8 39 fetched: /kv/primary/3/s -> 'a' 40 fetched: /kv/primary/5 -> NULL 41 fetched: /kv/primary/5/d -> -321 42 fetched: /kv/primary/6/v -> /2 43 fetched: /kv/primary/6/d -> 4.4 44 fetched: /kv/primary/6/s -> 'b' 45 fetched: /kv/primary/7/v -> /2 46 fetched: /kv/primary/7/d -> 7.9 47 fetched: /kv/primary/7/s -> 'b' 48 fetched: /kv/primary/8/v -> /4 49 fetched: /kv/primary/8/d -> 3 50 fetched: /kv/primary/8/s -> 'A' 51 output row: [5 NULL] 52 output row: [1 3.4501207708330056852] 53 output row: [6 3.4501207708330056852] 54 output row: [7 3.4501207708330056852] 55 output row: [3 3.5355339059327376220] 56 output row: [8 3.5355339059327376220] 57 58 query TTTTT 59 EXPLAIN (VERBOSE) SELECT ntile(1) OVER () FROM kv 60 ---- 61 · distributed false · · 62 · vectorized true · · 63 render · · (ntile) · 64 │ render 0 ntile · · 65 └── window · · (ntile_1_arg1, ntile) · 66 │ window 0 ntile(@1) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) · · 67 │ render 1 ntile(@1) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) · · 68 └── render · · (ntile_1_arg1) · 69 │ render 0 1 · · 70 └── scan · · () · 71 · table kv@primary · · 72 · spans FULL SCAN · · 73 74 query TTTTT 75 EXPLAIN (VERBOSE) SELECT nth_value(1, 2) OVER () FROM kv 76 ---- 77 · distributed false · · 78 · vectorized true · · 79 render · · (nth_value) · 80 │ render 0 nth_value · · 81 └── window · · (nth_value_1_arg1, nth_value_1_arg2, nth_value) · 82 │ window 0 nth_value(@1, @2) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) · · 83 │ render 2 nth_value(@1, @2) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) · · 84 └── render · · (nth_value_1_arg1, nth_value_1_arg2) · 85 │ render 0 1 · · 86 │ render 1 2 · · 87 └── scan · · () · 88 · table kv@primary · · 89 · spans FULL SCAN · · 90 91 statement error column "v" must appear in the GROUP BY clause or be used in an aggregate function 92 EXPLAIN (VERBOSE) SELECT max(v) OVER (), min(v) FROM kv ORDER BY 1 93 94 query TTT 95 EXPLAIN SELECT k, stddev(d) OVER w FROM kv WINDOW w as (PARTITION BY v) ORDER BY variance(d) OVER w, k 96 ---- 97 · distributed false 98 · vectorized true 99 render · · 100 └── sort · · 101 │ order +variance,+k 102 └── render · · 103 └── window · · 104 └── scan · · 105 · table kv@primary 106 · spans FULL SCAN 107 108 query TTTTT 109 EXPLAIN (TYPES) SELECT k, stddev(d) OVER w FROM kv WINDOW w as (PARTITION BY v) ORDER BY variance(d) OVER w, k 110 ---- 111 · distributed false · · 112 · vectorized true · · 113 render · · (k int, stddev decimal) · 114 │ render 0 (k)[int] · · 115 │ render 1 (stddev)[decimal] · · 116 └── sort · · (k int, stddev decimal, variance decimal) +variance,+k 117 │ order +variance,+k · · 118 └── render · · (k int, stddev decimal, variance decimal) · 119 │ render 0 (k)[int] · · 120 │ render 1 (stddev)[decimal] · · 121 │ render 2 (variance)[decimal] · · 122 └── window · · (k int, v int, d decimal, stddev decimal, variance decimal) · 123 │ window 0 (stddev((@3)[decimal]) OVER (PARTITION BY (@2)[int] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))[decimal] · · 124 │ window 1 (variance((@3)[decimal]) OVER (PARTITION BY (@2)[int] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))[decimal] · · 125 │ render 3 (stddev((@3)[decimal]) OVER (PARTITION BY (@2)[int] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))[decimal] · · 126 │ render 4 (variance((@3)[decimal]) OVER (PARTITION BY (@2)[int] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))[decimal] · · 127 └── scan · · (k int, v int, d decimal) · 128 · table kv@primary · · 129 · spans FULL SCAN · · 130 131 query TTTTT 132 EXPLAIN (TYPES) SELECT k, stddev(d) OVER (PARTITION BY v, 'a') FROM kv ORDER BY variance(d) OVER (PARTITION BY v, 100), k 133 ---- 134 · distributed false · · 135 · vectorized true · · 136 render · · (k int, stddev decimal) · 137 │ render 0 (k)[int] · · 138 │ render 1 (stddev)[decimal] · · 139 └── sort · · (k int, stddev decimal, variance decimal) +variance,+k 140 │ order +variance,+k · · 141 └── render · · (k int, stddev decimal, variance decimal) · 142 │ render 0 (k)[int] · · 143 │ render 1 (stddev)[decimal] · · 144 │ render 2 (variance)[decimal] · · 145 └── window · · (k int, v int, d decimal, stddev decimal, variance decimal) · 146 │ window 0 (variance((@3)[decimal]) OVER (PARTITION BY (@2)[int] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))[decimal] · · 147 │ render 4 (variance((@3)[decimal]) OVER (PARTITION BY (@2)[int] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))[decimal] · · 148 └── window · · (k int, v int, d decimal, stddev decimal) · 149 │ window 0 (stddev((@3)[decimal]) OVER (PARTITION BY (@2)[int] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))[decimal] · · 150 │ render 3 (stddev((@3)[decimal]) OVER (PARTITION BY (@2)[int] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))[decimal] · · 151 └── scan · · (k int, v int, d decimal) · 152 · table kv@primary · · 153 · spans FULL SCAN · · 154 155 query TTTTT 156 EXPLAIN (TYPES,NONORMALIZE) SELECT k, stddev(d) OVER (PARTITION BY v, 'a') FROM kv ORDER BY k 157 ---- 158 · distributed false · · 159 · vectorized true · · 160 sort · · (k int, stddev decimal) +k 161 │ order +k · · 162 └── render · · (k int, stddev decimal) · 163 │ render 0 (k)[int] · · 164 │ render 1 (stddev)[decimal] · · 165 └── window · · (k int, v int, d decimal, stddev decimal) · 166 │ window 0 (stddev((@3)[decimal]) OVER (PARTITION BY (@2)[int] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))[decimal] · · 167 │ render 3 (stddev((@3)[decimal]) OVER (PARTITION BY (@2)[int] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))[decimal] · · 168 └── scan · · (k int, v int, d decimal) · 169 · table kv@primary · · 170 · spans FULL SCAN · · 171 172 query TTTTT 173 EXPLAIN (TYPES) SELECT k, k + stddev(d) OVER (PARTITION BY v, 'a') FROM kv ORDER BY variance(d) OVER (PARTITION BY v, 100), k 174 ---- 175 · distributed false · · 176 · vectorized true · · 177 render · · (k int, "?column?" decimal) · 178 │ render 0 (k)[int] · · 179 │ render 1 ("?column?")[decimal] · · 180 └── sort · · ("?column?" decimal, k int, variance decimal) +variance,+k 181 │ order +variance,+k · · 182 └── render · · ("?column?" decimal, k int, variance decimal) · 183 │ render 0 ((k)[int] + (stddev)[decimal])[decimal] · · 184 │ render 1 (k)[int] · · 185 │ render 2 (variance)[decimal] · · 186 └── window · · (k int, v int, d decimal, stddev decimal, variance decimal) · 187 │ window 0 (variance((@3)[decimal]) OVER (PARTITION BY (@2)[int] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))[decimal] · · 188 │ render 4 (variance((@3)[decimal]) OVER (PARTITION BY (@2)[int] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))[decimal] · · 189 └── window · · (k int, v int, d decimal, stddev decimal) · 190 │ window 0 (stddev((@3)[decimal]) OVER (PARTITION BY (@2)[int] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))[decimal] · · 191 │ render 3 (stddev((@3)[decimal]) OVER (PARTITION BY (@2)[int] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))[decimal] · · 192 └── scan · · (k int, v int, d decimal) · 193 · table kv@primary · · 194 · spans FULL SCAN · · 195 196 query TTTTT 197 EXPLAIN (TYPES) SELECT max(k), max(k) + stddev(d) OVER (PARTITION BY v, 'a') FROM kv GROUP BY d, v ORDER BY variance(d) OVER (PARTITION BY v, 100) 198 ---- 199 · distributed false · · 200 · vectorized true · · 201 render · · (max int, "?column?" decimal) · 202 │ render 0 (max)[int] · · 203 │ render 1 ("?column?")[decimal] · · 204 └── sort · · ("?column?" decimal, max int, variance decimal) +variance 205 │ order +variance · · 206 └── render · · ("?column?" decimal, max int, variance decimal) · 207 │ render 0 ((max)[int] + (stddev)[decimal])[decimal] · · 208 │ render 1 (max)[int] · · 209 │ render 2 (variance)[decimal] · · 210 └── window · · (v int, d decimal, max int, stddev decimal, variance decimal) · 211 │ window 0 (variance((@2)[decimal]) OVER (PARTITION BY (@1)[int] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))[decimal] · · 212 │ render 4 (variance((@2)[decimal]) OVER (PARTITION BY (@1)[int] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))[decimal] · · 213 └── window · · (v int, d decimal, max int, stddev decimal) · 214 │ window 0 (stddev((@2)[decimal]) OVER (PARTITION BY (@1)[int] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))[decimal] · · 215 │ render 3 (stddev((@2)[decimal]) OVER (PARTITION BY (@1)[int] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))[decimal] · · 216 └── group · · (v int, d decimal, max int) · 217 │ aggregate 0 v · · 218 │ aggregate 1 d · · 219 │ aggregate 2 max(k) · · 220 │ group by v, d · · 221 └── scan · · (k int, v int, d decimal) · 222 · table kv@primary · · 223 · spans FULL SCAN · · 224 225 query TTTTT 226 EXPLAIN (TYPES) SELECT max(k), stddev(d) OVER (PARTITION BY v, 'a') FROM kv GROUP BY d, v ORDER BY 1 227 ---- 228 · distributed false · · 229 · vectorized true · · 230 sort · · (max int, stddev decimal) +max 231 │ order +max · · 232 └── render · · (max int, stddev decimal) · 233 │ render 0 (max)[int] · · 234 │ render 1 (stddev)[decimal] · · 235 └── window · · (v int, d decimal, max int, stddev decimal) · 236 │ window 0 (stddev((@2)[decimal]) OVER (PARTITION BY (@1)[int] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))[decimal] · · 237 │ render 3 (stddev((@2)[decimal]) OVER (PARTITION BY (@1)[int] RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))[decimal] · · 238 └── group · · (v int, d decimal, max int) · 239 │ aggregate 0 v · · 240 │ aggregate 1 d · · 241 │ aggregate 2 max(k) · · 242 │ group by v, d · · 243 └── scan · · (k int, v int, d decimal) · 244 · table kv@primary · · 245 · spans FULL SCAN · · 246 247 # Partition 248 249 query TTTTT 250 EXPLAIN (VERBOSE) SELECT lag(1) OVER (PARTITION BY 2), lead(2) OVER (PARTITION BY 1) FROM kv 251 ---- 252 · distributed false · · 253 · vectorized true · · 254 render · · (lag, lead) · 255 │ render 0 lag · · 256 │ render 1 lead · · 257 └── window · · (lag, lag_1_arg1, lag_1_arg3, lag_1_partition_1, lead) · 258 │ window 0 lead(@4, @2, @3) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) · · 259 │ render 4 lead(@4, @2, @3) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) · · 260 └── render · · (lag, lag_1_arg1, lag_1_arg3, lag_1_partition_1) · 261 │ render 0 lag · · 262 │ render 1 lag_1_arg1 · · 263 │ render 2 lag_1_arg3 · · 264 │ render 3 lag_1_partition_1 · · 265 └── window · · (lag_1_arg1, lag_1_arg3, lag_1_partition_1, lag) · 266 │ window 0 lag(@1, @1, @2) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) · · 267 │ render 3 lag(@1, @1, @2) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) · · 268 └── render · · (lag_1_arg1, lag_1_arg3, lag_1_partition_1) · 269 │ render 0 1 · · 270 │ render 1 CAST(NULL AS INT8) · · 271 │ render 2 2 · · 272 └── scan · · () · 273 · table kv@primary · · 274 · spans FULL SCAN · · 275 276 # Ordering 277 278 query TTTTT 279 EXPLAIN (VERBOSE) SELECT k, v, rank() OVER (ORDER BY k) FROM kv ORDER BY 1 280 ---- 281 · distributed false · · 282 · vectorized true · · 283 sort · · (k, v, rank) +k 284 │ order +k · · 285 └── window · · (k, v, rank) · 286 │ window 0 rank() OVER (ORDER BY @1 ASC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) · · 287 │ render 2 rank() OVER (ORDER BY @1 ASC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) · · 288 └── scan · · (k, v) · 289 · table kv@primary · · 290 · spans FULL SCAN · · 291 292 293 # Frames 294 295 query TTTTT 296 EXPLAIN (VERBOSE) SELECT avg(k) OVER () FROM kv 297 ---- 298 · distributed false · · 299 · vectorized true · · 300 render · · (avg) · 301 │ render 0 avg · · 302 └── window · · (k, avg) · 303 │ window 0 avg(@1) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) · · 304 │ render 1 avg(@1) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) · · 305 └── scan · · (k) · 306 · table kv@primary · · 307 · spans FULL SCAN · · 308 309 query TTTTT 310 EXPLAIN (VERBOSE) SELECT avg(k) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM kv 311 ---- 312 · distributed false · · 313 · vectorized true · · 314 render · · (avg) · 315 │ render 0 avg · · 316 └── window · · (k, avg) · 317 │ window 0 avg(@1) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) · · 318 │ render 1 avg(@1) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) · · 319 └── scan · · (k) · 320 · table kv@primary · · 321 · spans FULL SCAN · · 322 323 query TTTTT 324 EXPLAIN (VERBOSE) SELECT avg(k) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM kv 325 ---- 326 · distributed false · · 327 · vectorized true · · 328 render · · (avg) · 329 │ render 0 avg · · 330 └── window · · (k, avg) · 331 │ window 0 avg(@1) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) · · 332 │ render 1 avg(@1) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) · · 333 └── scan · · (k) · 334 · table kv@primary · · 335 · spans FULL SCAN · · 336 337 query TTTTT 338 EXPLAIN (VERBOSE) 339 SELECT 340 avg(k) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), 341 avg(k) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 342 avg(k) OVER (RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), 343 avg(k) OVER (RANGE BETWEEN CURRENT ROW AND CURRENT ROW), 344 avg(k) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), 345 avg(k) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 346 avg(k) OVER (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), 347 avg(k) OVER (ROWS BETWEEN CURRENT ROW AND CURRENT ROW), 348 avg(k) OVER (ORDER BY k GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), 349 avg(k) OVER (ORDER BY k GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 350 avg(k) OVER (ORDER BY k GROUPS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING), 351 avg(k) OVER (ORDER BY k GROUPS BETWEEN CURRENT ROW AND CURRENT ROW) 352 FROM 353 kv 354 ---- 355 · distributed false · · 356 · vectorized true · · 357 render · · (avg, avg, avg, avg, avg, avg, avg, avg, avg, avg, avg, avg) · 358 │ render 0 avg · · 359 │ render 1 avg · · 360 │ render 2 avg · · 361 │ render 3 avg · · 362 │ render 4 avg · · 363 │ render 5 avg · · 364 │ render 6 avg · · 365 │ render 7 avg · · 366 │ render 8 avg · · 367 │ render 9 avg · · 368 │ render 10 avg · · 369 │ render 11 avg · · 370 └── window · · (k, avg, avg, avg, avg, avg, avg, avg, avg, avg, avg, avg, avg) · 371 │ window 0 avg(@1) OVER (ORDER BY @1 ASC GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) · · 372 │ window 1 avg(@1) OVER (ORDER BY @1 ASC GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) · · 373 │ window 2 avg(@1) OVER (ORDER BY @1 ASC GROUPS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) · · 374 │ window 3 avg(@1) OVER (ORDER BY @1 ASC GROUPS BETWEEN CURRENT ROW AND CURRENT ROW) · · 375 │ render 9 avg(@1) OVER (ORDER BY @1 ASC GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) · · 376 │ render 10 avg(@1) OVER (ORDER BY @1 ASC GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) · · 377 │ render 11 avg(@1) OVER (ORDER BY @1 ASC GROUPS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) · · 378 │ render 12 avg(@1) OVER (ORDER BY @1 ASC GROUPS BETWEEN CURRENT ROW AND CURRENT ROW) · · 379 └── window · · (k, avg, avg, avg, avg, avg, avg, avg, avg) · 380 │ window 0 avg(@1) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) · · 381 │ window 1 avg(@1) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) · · 382 │ window 2 avg(@1) OVER (RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) · · 383 │ window 3 avg(@1) OVER (RANGE BETWEEN CURRENT ROW AND CURRENT ROW) · · 384 │ window 4 avg(@1) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) · · 385 │ window 5 avg(@1) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) · · 386 │ window 6 avg(@1) OVER (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) · · 387 │ window 7 avg(@1) OVER (ROWS BETWEEN CURRENT ROW AND CURRENT ROW) · · 388 │ render 1 avg(@1) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) · · 389 │ render 2 avg(@1) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) · · 390 │ render 3 avg(@1) OVER (RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) · · 391 │ render 4 avg(@1) OVER (RANGE BETWEEN CURRENT ROW AND CURRENT ROW) · · 392 │ render 5 avg(@1) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) · · 393 │ render 6 avg(@1) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) · · 394 │ render 7 avg(@1) OVER (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) · · 395 │ render 8 avg(@1) OVER (ROWS BETWEEN CURRENT ROW AND CURRENT ROW) · · 396 └── scan · · (k) · 397 · table kv@primary · · 398 · spans FULL SCAN · · 399 400 query TTTTT 401 EXPLAIN (VERBOSE) 402 SELECT 403 avg(v) OVER ( 404 PARTITION BY 405 w 406 ROWS 407 BETWEEN (SELECT count(*) FROM kv) PRECEDING AND 1 FOLLOWING 408 ) 409 FROM 410 kv 411 ---- 412 · distributed false · · 413 · vectorized false · · 414 root · · (avg) · 415 ├── render · · (avg) · 416 │ │ render 0 avg · · 417 │ └── window · · (v, w, avg) · 418 │ │ window 0 avg(@1) OVER (PARTITION BY @2 ROWS BETWEEN @S1 PRECEDING AND 1 FOLLOWING) · · 419 │ │ render 2 avg(@1) OVER (PARTITION BY @2 ROWS BETWEEN @S1 PRECEDING AND 1 FOLLOWING) · · 420 │ └── scan · · (v, w) · 421 │ table kv@primary · · 422 │ spans FULL SCAN · · 423 └── subquery · · · · 424 │ id @S1 · · 425 │ original sql (SELECT count(*) FROM kv) · · 426 │ exec mode one row · · 427 └── group · · (count_rows) · 428 │ aggregate 0 count_rows() · · 429 │ scalar · · · 430 └── scan · · () · 431 · table kv@primary · · 432 · spans FULL SCAN · · 433 434 query TTTTT 435 EXPLAIN (VERBOSE) 436 SELECT 437 avg(k) OVER (RANGE UNBOUNDED PRECEDING EXCLUDE CURRENT ROW), 438 avg(k) OVER (RANGE UNBOUNDED PRECEDING EXCLUDE GROUP), 439 avg(k) OVER (RANGE UNBOUNDED PRECEDING EXCLUDE TIES), 440 avg(k) OVER (RANGE UNBOUNDED PRECEDING EXCLUDE NO OTHERS) 441 FROM 442 kv 443 ---- 444 · distributed false · · 445 · vectorized true · · 446 render · · (avg, avg, avg, avg) · 447 │ render 0 avg · · 448 │ render 1 avg · · 449 │ render 2 avg · · 450 │ render 3 avg · · 451 └── window · · (k, avg, avg, avg, avg) · 452 │ window 0 avg(@1) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW) · · 453 │ window 1 avg(@1) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE GROUP) · · 454 │ window 2 avg(@1) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) · · 455 │ window 3 avg(@1) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) · · 456 │ render 1 avg(@1) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW) · · 457 │ render 2 avg(@1) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE GROUP) · · 458 │ render 3 avg(@1) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) · · 459 │ render 4 avg(@1) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) · · 460 └── scan · · (k) · 461 · table kv@primary · · 462 · spans FULL SCAN · ·