# Copyright 2015 - 2019 The Cockroach Authors. All rights reserved. # Copyright Materialize, Inc. and contributors. All rights reserved. # # Use of this software is governed by the Business Source License # included in the LICENSE file at the root of this repository. # # As of the Change Date specified in that file, in accordance with # the Business Source License, use of this software will be governed # by the Apache License, Version 2.0. # # This file is derived from the logic test suite in CockroachDB. The # original file was retrieved on June 10, 2019 from: # # https://github.com/cockroachdb/cockroach/blob/d2f7fbf5dd1fc1a099bbad790a2e1f7c60a66cc3/pkg/sql/logictest/testdata/logic_test/window # # The original source code is subject to the terms of the Apache # 2.0 license, a copy of which can be found in the LICENSE file at the # root of this repository. # not supported yet halt mode cockroach statement ok CREATE TABLE kv ( -- don't add column "a" k INT PRIMARY KEY, v INT, w INT, f FLOAT, d DECIMAL, s STRING, b BOOL, FAMILY (k, v, w, f, b), FAMILY (d), FAMILY (s) ) statement OK INSERT INTO kv VALUES (1, 2, 3, 1.0, 1, 'a', true), (3, 4, 5, 2, 8, 'a', true), (5, NULL, 5, 9.9, -321, NULL, false), (6, 2, 3, 4.4, 4.4, 'b', true), (7, 2, 2, 6, 7.9, 'b', true), (8, 4, 2, 3, 3, 'A', false) query error window functions are not allowed in GROUP BY SELECT * FROM kv GROUP BY v, count(w) OVER () query error window functions are not allowed in GROUP BY SELECT count(w) OVER () FROM kv GROUP BY 1 query error window functions are not allowed in RETURNING INSERT INTO kv (k, v) VALUES (99, 100) RETURNING sum(v) OVER () query error window functions are not allowed in LIMIT SELECT sum(v) FROM kv GROUP BY k LIMIT sum(v) OVER () query error window functions are not allowed in OFFSET SELECT sum(v) FROM kv GROUP BY k LIMIT 1 OFFSET sum(v) OVER () query error window functions are not allowed in VALUES INSERT INTO kv (k, v) VALUES (99, count(1) OVER ()) query error window functions are not allowed in WHERE SELECT k FROM kv WHERE avg(k) OVER () > 1 query error window functions are not allowed in HAVING SELECT 1 FROM kv GROUP BY 1 HAVING sum(1) OVER (PARTITION BY 1) > 1 query R SELECT avg(k) OVER () FROM kv ORDER BY 1 ---- 5 5 5 5 5 5 query R SELECT avg(k) OVER (PARTITION BY v) FROM kv ORDER BY 1 ---- 4.6666666666666666667 4.6666666666666666667 4.6666666666666666667 5 5.5 5.5 query R SELECT avg(k) OVER (PARTITION BY w) FROM kv ORDER BY 1 ---- 3.5 3.5 4 4 7.5 7.5 query R SELECT avg(k) OVER (PARTITION BY b) FROM kv ORDER BY 1 ---- 4.25 4.25 4.25 4.25 6.5 6.5 query R SELECT avg(k) OVER (PARTITION BY w, b) FROM kv ORDER BY 1 ---- 3 3.5 3.5 5 7 8 query R SELECT avg(k) OVER (PARTITION BY kv.*) FROM kv ORDER BY 1 ---- 1 3 5 6 7 8 query R SELECT avg(k) OVER (ORDER BY w) FROM kv ORDER BY 1 ---- 5 5 5.5 5.5 7.5 7.5 query R SELECT avg(k) OVER (ORDER BY b) FROM kv ORDER BY 1 ---- 5 5 5 5 6.5 6.5 query R SELECT avg(k) OVER (ORDER BY w, b) FROM kv ORDER BY 1 ---- 5 5.4 5.5 5.5 7.5 8 query R SELECT avg(k) OVER (ORDER BY 1-w) FROM kv ORDER BY 1 ---- 3.75 3.75 4 4 5 5 query R SELECT avg(k) OVER (ORDER BY kv.*) FROM kv ORDER BY 1 ---- 1 2 3 3.75 4.4 5 query R SELECT avg(k) OVER (ORDER BY w DESC) FROM kv ORDER BY 1 ---- 3.75 3.75 4 4 5 5 query R SELECT avg(k) OVER (PARTITION BY v ORDER BY w) FROM kv ORDER BY 1 ---- 4.6666666666666666667 4.6666666666666666667 5 5.5 7 8 query R SELECT avg(k) OVER w FROM kv WINDOW w AS (PARTITION BY v ORDER BY w) ORDER BY 1 ---- 4.6666666666666666667 4.6666666666666666667 5 5.5 7 8 query R SELECT avg(k) OVER (w) FROM kv WINDOW w AS (PARTITION BY v ORDER BY w) ORDER BY 1 ---- 4.6666666666666666667 4.6666666666666666667 5 5.5 7 8 query R SELECT avg(k) OVER (w ORDER BY w) FROM kv WINDOW w AS (PARTITION BY v) ORDER BY 1 ---- 4.6666666666666666667 4.6666666666666666667 5 5.5 7 8 query IIIRRTBR colnames SELECT *, avg(k) OVER (w ORDER BY w) FROM kv WINDOW w AS (PARTITION BY v) ORDER BY 1 ---- k v w f d s b avg 1 2 3 1 1 a true 4.6666666666666666667 3 4 5 2 8 a true 5.5 5 NULL 5 9.9 -321 NULL false 5 6 2 3 4.4 4.4 b true 4.6666666666666666667 7 2 2 6 7.9 b true 7 8 4 2 3 3 A false 8 query IIIRRTBR colnames SELECT *, avg(k) OVER w FROM kv WINDOW w AS (PARTITION BY v ORDER BY w) ORDER BY avg(k) OVER w, k ---- k v w f d s b avg 1 2 3 1 1 a true 4.6666666666666666667 6 2 3 4.4 4.4 b true 4.6666666666666666667 5 NULL 5 9.9 -321 NULL false 5 3 4 5 2 8 a true 5.5 7 2 2 6 7.9 b true 7 8 4 2 3 3 A false 8 query IIIRRTB colnames SELECT * FROM kv WINDOW w AS (PARTITION BY v ORDER BY w) ORDER BY avg(k) OVER w DESC, k ---- k v w f d s b 8 4 2 3 3 A false 7 2 2 6 7.9 b true 3 4 5 2 8 a true 5 NULL 5 9.9 -321 NULL false 1 2 3 1 1 a true 6 2 3 4.4 4.4 b true query error window "w" is already defined SELECT avg(k) OVER w FROM kv WINDOW w AS (), w AS () query error window "x" does not exist SELECT avg(k) OVER x FROM kv WINDOW w AS () query error window "x" does not exist SELECT avg(k) OVER (x) FROM kv WINDOW w AS () query error cannot override PARTITION BY clause of window "w" SELECT avg(k) OVER (w PARTITION BY v) FROM kv WINDOW w AS () query error cannot override PARTITION BY clause of window "w" SELECT avg(k) OVER (w PARTITION BY v) FROM kv WINDOW w AS (PARTITION BY v) query error cannot override ORDER BY clause of window "w" SELECT avg(k) OVER (w ORDER BY v) FROM kv WINDOW w AS (ORDER BY v) query error column "a" does not exist SELECT avg(k) OVER (PARTITION BY a) FROM kv query error column "a" does not exist SELECT avg(k) OVER (ORDER BY a) FROM kv # TODO(justin): this should have pgcode 42803 but CBO currently doesn't get # it right. query error window functions are not allowed in aggregate SELECT avg(avg(k) OVER ()) FROM kv ORDER BY 1 query R SELECT avg(avg(k)) OVER () FROM kv ORDER BY 1 ---- 5 query RR SELECT avg(k) OVER (), avg(v) OVER () FROM kv ORDER BY 1 ---- 5 2.3333333333333333333 5 2.3333333333333333333 5 2.3333333333333333333 5 2.3333333333333333333 5 2.3333333333333333333 5 2.3333333333333333333 query error OVER specified, but now\(\) is neither a window function nor an aggregate function SELECT now() OVER () FROM kv ORDER BY 1 query error window function rank\(\) requires an OVER clause SELECT rank() FROM kv query error unknown signature: rank\(int\) SELECT rank(22) FROM kv query error window function calls cannot be nested SELECT avg(avg(k) OVER ()) OVER () FROM kv ORDER BY 1 query error OVER specified, but round\(\) is neither a window function nor an aggregate function SELECT round(avg(k) OVER ()) OVER () FROM kv ORDER BY 1 query R SELECT round(avg(k) OVER (PARTITION BY v ORDER BY w)) FROM kv ORDER BY 1 ---- 5 5 5 6 7 8 query R SELECT avg(f) OVER (PARTITION BY v ORDER BY w) FROM kv ORDER BY 1 ---- 2.5 3 3.8 3.8 6 9.9 query R SELECT avg(d) OVER (PARTITION BY v ORDER BY w) FROM kv ORDER BY 1 ---- -321 3 4.4333333333333333333 4.4333333333333333333 5.5 7.9 query R SELECT avg(d) OVER (PARTITION BY w ORDER BY v) FROM kv ORDER BY 1 ---- -321 -156.5 2.7 2.7 5.45 7.9 query R SELECT (avg(d) OVER (PARTITION BY v ORDER BY w) + avg(d) OVER (PARTITION BY v ORDER BY w)) FROM kv ORDER BY 1 ---- -642 6 8.8666666666666666666 8.8666666666666666666 11.0 15.8 query R SELECT (avg(d) OVER (PARTITION BY v ORDER BY w) + avg(d) OVER (PARTITION BY w ORDER BY v)) FROM kv ORDER BY 1 ---- -642 -151.0 7.1333333333333333333 7.1333333333333333333 8.45 15.8 query R SELECT avg(d) OVER (PARTITION BY v) FROM kv WHERE FALSE ORDER BY 1 ---- query R SELECT avg(d) OVER (PARTITION BY v, v, v, v, v, v, v, v, v, v) FROM kv WHERE FALSE ORDER BY 1 ---- query R SELECT avg(d) OVER (PARTITION BY v, v, v, v, v, v, v, v, v, v) FROM kv WHERE k = 3 ORDER BY 1 ---- 8 query IT SELECT k, concat_agg(s) OVER (PARTITION BY k ORDER BY w) FROM kv ORDER BY 1 ---- 1 a 3 a 5 NULL 6 b 7 b 8 A query IT SELECT k, concat_agg(s) OVER (PARTITION BY v ORDER BY w, k) FROM kv ORDER BY 1 ---- 1 ba 3 Aa 5 NULL 6 bab 7 b 8 A query IB SELECT k, bool_and(b) OVER (PARTITION BY v ORDER BY w) FROM kv ORDER BY 1 ---- 1 true 3 false 5 false 6 true 7 true 8 false query IB SELECT k, bool_or(b) OVER (PARTITION BY v ORDER BY w) FROM kv ORDER BY 1 ---- 1 true 3 true 5 false 6 true 7 true 8 false query II SELECT k, count(d) OVER (PARTITION BY v ORDER BY w) FROM kv ORDER BY 1 ---- 1 3 3 2 5 1 6 3 7 1 8 1 query II SELECT k, count(*) OVER (PARTITION BY v ORDER BY w) FROM kv ORDER BY 1 ---- 1 3 3 2 5 1 6 3 7 1 8 1 query IR SELECT k, max(d) OVER (PARTITION BY v ORDER BY w) FROM kv ORDER BY 1 ---- 1 7.9 3 8 5 -321 6 7.9 7 7.9 8 3 query IR SELECT k, min(d) OVER (PARTITION BY v ORDER BY w) FROM kv ORDER BY 1 ---- 1 1 3 3 5 -321 6 1 7 7.9 8 3 query IR SELECT k, pow(max(d) OVER (PARTITION BY v), k::DECIMAL) FROM kv ORDER BY 1 ---- 1 7.9 3 512 5 -3408200705601 6 243087.455521 7 1920390.8986159 8 16777216 query IR SELECT k, max(d) OVER (PARTITION BY v) FROM kv ORDER BY 1 ---- 1 7.9 3 8 5 -321 6 7.9 7 7.9 8 8 query IR SELECT k, sum(d) OVER (PARTITION BY v ORDER BY w) FROM kv ORDER BY 1 ---- 1 13.3 3 11 5 -321 6 13.3 7 7.9 8 3 query IR SELECT k, variance(d) OVER (PARTITION BY v ORDER BY w) FROM kv ORDER BY 1 ---- 1 11.903333333333333333 3 12.5 5 NULL 6 11.903333333333333333 7 NULL 8 NULL query IR SELECT k, stddev(d) OVER (PARTITION BY v ORDER BY w) FROM kv ORDER BY 1 ---- 1 3.4501207708330056852 3 3.5355339059327376220 5 NULL 6 3.4501207708330056852 7 NULL 8 NULL query IR SELECT k, stddev(d) OVER w FROM kv WINDOW w as (PARTITION BY v) ORDER BY variance(d) OVER w, k ---- 5 NULL 1 3.4501207708330056852 6 3.4501207708330056852 7 3.4501207708330056852 3 3.5355339059327376220 8 3.5355339059327376220 query IRIR SELECT * FROM (SELECT k, d, v, stddev(d) OVER (PARTITION BY v) FROM kv) sub ORDER BY variance(d) OVER (PARTITION BY v), k ---- 5 -321 NULL NULL 1 1 2 3.4501207708330056852 6 4.4 2 3.4501207708330056852 7 7.9 2 3.4501207708330056852 3 8 4 3.5355339059327376220 8 3 4 3.5355339059327376220 query IR SELECT k, max(stddev) OVER (ORDER BY d) FROM (SELECT k, d, stddev(d) OVER (PARTITION BY v) as stddev FROM kv) sub ORDER BY 2, k ---- 5 NULL 1 3.4501207708330056852 3 3.5355339059327376220 6 3.5355339059327376220 7 3.5355339059327376220 8 3.5355339059327376220 query IR SELECT k, max(stddev) OVER (ORDER BY d DESC) FROM (SELECT k, d, stddev(d) OVER (PARTITION BY v) as stddev FROM kv) sub ORDER BY 2, k ---- 1 3.5355339059327376220 3 3.5355339059327376220 5 3.5355339059327376220 6 3.5355339059327376220 7 3.5355339059327376220 8 3.5355339059327376220 query IRIII SELECT k, (rank() OVER wind + avg(w) OVER wind), w, (v + row_number() OVER wind), v FROM kv WINDOW wind AS (ORDER BY k) ORDER BY 1 ---- 1 4 3 3 2 3 6 5 6 4 5 7.3333333333333333333 5 NULL NULL 6 8 3 6 2 7 8.6 2 7 2 8 9.3333333333333333333 2 10 4 query TIRRI SELECT s, w + k, (sum(w) OVER wind + avg(d) OVER wind), (min(w) OVER wind + d), v FROM kv WINDOW wind AS (ORDER BY w, k) ORDER BY k ---- a 4 10.9666666666666666667 3 2 a 8 19.86 10 4 NULL 10 -29.45 -319 NULL b 9 14.075 6.4 2 b 9 9.9 9.9 2 A 10 9.45 5 4 query IIII SELECT k, v + w, round(rank() OVER wind + lead(k, 3, v) OVER wind + lag(w, 1, 2) OVER wind + f::DECIMAL + avg(d) OVER wind)::INT, round(row_number() OVER wind::FLOAT + round(f) + dense_rank() OVER wind::FLOAT)::INT FROM kv WINDOW wind as (PARTITION BY v ORDER BY k) ORDER BY k ---- 1 5 7 3 3 9 17 4 5 NULL NULL 12 6 5 14 8 7 4 18 12 8 6 20 7 query II SELECT (rank() OVER wind + lead(k, 3, v) OVER wind + lag(w, 1, 2) OVER wind), (row_number() OVER wind + dense_rank() OVER wind) FROM kv WINDOW wind as (PARTITION BY v ORDER BY k) ORDER BY k ---- 5 2 7 2 NULL 2 7 4 8 6 11 4 query RIR SELECT (round(avg(k) OVER w1 + sum(w) OVER w2) + row_number() OVER w2 + d + min(d) OVER w3 + f::DECIMAL) AS big_sum, v + w AS v_plus_w, (rank() OVER w3 + first_value(d) OVER w1 + nth_value(k, 2) OVER w1) AS small_sum FROM kv WINDOW w1 AS (PARTITION BY b ORDER BY k), w2 AS (PARTITION BY w ORDER BY k), w3 AS (PARTITION BY v ORDER BY k) ORDER BY k ---- 8 5 NULL 26 9 5 -615.1 NULL NULL 20.8 5 6 21.9 4 7 22 6 -311 query RI SELECT round(row_number() OVER w1 + lead(k, v, w) OVER w2 + avg(k) OVER w1), (lag(k, 1) OVER w1 + v + rank() OVER w2 + min(k) OVER w1) FROM kv WINDOW w1 AS (PARTITION BY w ORDER BY k), w2 AS (PARTITION BY b ORDER BY k) ORDER BY k ---- 8 NULL 9 NULL NULL NULL 9 7 10 NULL 12 20 query R SELECT f::DECIMAL + round(max(k) * w * avg(d) OVER wind) + (lead(f, 2, 17::FLOAT) OVER wind::DECIMAL / d * row_number() OVER wind) FROM kv GROUP BY k, w, f, d WINDOW wind AS (ORDER BY k) ORDER BY k ---- 13.9 71.10 -2590.156074766355140186916 -1376.87272727272727272728 -822.2405063291139240505 -753.9999999999999999998 query R SELECT round(max(w) * w * avg(w) OVER wind) + (lead(w, 2, 17) OVER wind::DECIMAL / w * row_number() OVER wind) FROM kv GROUP BY w WINDOW wind AS (PARTITION BY w) ORDER BY 1 ---- 16.5 32.6666666666666666667 128.4 query IRRIRIR SELECT k, avg(d) OVER w1, avg(d) OVER w2, row_number() OVER w2, sum(f) OVER w1, row_number() OVER w1, sum(f) OVER w2 FROM kv WINDOW w1 AS (ORDER BY k), w2 AS (ORDER BY w, k) ORDER BY k ---- 1 1 3.9666666666666666667 3 1 1 10 3 4.5 4.86 5 3 2 16.4 5 -104 -49.45 6 12.9 3 26.3 6 -76.9 4.075 4 17.3 4 14.4 7 -59.94 7.9 1 23.3 5 6 8 -49.45 5.45 2 26.3 6 9 query R SELECT round((avg(d) OVER wind) * max(k) + (lag(d, 1, 42.0) OVER wind) * max(d)) FROM kv GROUP BY d, k WINDOW wind AS (ORDER BY k) ORDER BY k ---- 43 22 -3088 -1874 -385 -372 query RR SELECT avg(k) OVER w, avg(k) OVER w + 1 FROM kv WINDOW w AS (PARTITION BY v ORDER BY w) ORDER BY k ---- 4.6666666666666666667 5.6666666666666666667 5.5 6.5 5 6 4.6666666666666666667 5.6666666666666666667 7 8 8 9 statement OK INSERT INTO kv VALUES (9, 2, 9, .1, DEFAULT, DEFAULT, DEFAULT), (10, 4, 9, .2, DEFAULT, DEFAULT, DEFAULT), (11, NULL, 9, .3, DEFAULT, DEFAULT, DEFAULT) query II SELECT k, row_number() OVER (ORDER BY k) FROM kv ORDER BY 1 ---- 1 1 3 2 5 3 6 4 7 5 8 6 9 7 10 8 11 9 query III SELECT k, v, row_number() OVER (PARTITION BY v ORDER BY k) FROM kv ORDER BY 1 ---- 1 2 1 3 4 1 5 NULL 1 6 2 2 7 2 3 8 4 2 9 2 4 10 4 3 11 NULL 2 query IIII SELECT k, v, w, row_number() OVER (PARTITION BY v ORDER BY w, k) FROM kv ORDER BY 1 ---- 1 2 3 2 3 4 5 2 5 NULL 5 1 6 2 3 3 7 2 2 1 8 4 2 1 9 2 9 4 10 4 9 3 11 NULL 9 2 query IIII SELECT k, v, w, v - w + 2 + row_number() OVER (PARTITION BY v ORDER BY w, k) FROM kv ORDER BY 1 ---- 1 2 3 3 3 4 5 3 5 NULL 5 NULL 6 2 3 4 7 2 2 3 8 4 2 5 9 2 9 -1 10 4 9 0 11 NULL 9 NULL query II SELECT k, row_number() OVER (PARTITION BY k) FROM kv ORDER BY 1 ---- 1 1 3 1 5 1 6 1 7 1 8 1 9 1 10 1 11 1 query IIII SELECT k, v, w, v - w + 2 + row_number() OVER (PARTITION BY v, k ORDER BY w) FROM kv ORDER BY 1 ---- 1 2 3 2 3 4 5 2 5 NULL 5 NULL 6 2 3 2 7 2 2 3 8 4 2 5 9 2 9 -4 10 4 9 -2 11 NULL 9 NULL query RIII SELECT avg(k), max(v), min(w), 2 + row_number() OVER () FROM kv ORDER BY 1 ---- 6.6666666666666666667 4 2 3 query II SELECT k, rank() OVER () FROM kv ORDER BY 1 ---- 1 1 3 1 5 1 6 1 7 1 8 1 9 1 10 1 11 1 query III SELECT k, v, rank() OVER (PARTITION BY v) FROM kv ORDER BY 1 ---- 1 2 1 3 4 1 5 NULL 1 6 2 1 7 2 1 8 4 1 9 2 1 10 4 1 11 NULL 1 query IIII SELECT k, v, w, rank() OVER (PARTITION BY v ORDER BY w) FROM kv ORDER BY 1 ---- 1 2 3 2 3 4 5 2 5 NULL 5 1 6 2 3 2 7 2 2 1 8 4 2 1 9 2 9 4 10 4 9 3 11 NULL 9 2 query IRI SELECT k, (rank() OVER w + avg(w) OVER w), k FROM kv WINDOW w AS (PARTITION BY v ORDER BY w) ORDER BY 1 ---- 1 4.6666666666666666667 1 3 5.5 3 5 6 5 6 4.6666666666666666667 6 7 3 7 8 3 8 9 8.25 9 10 8.3333333333333333333 10 11 9 11 query IRI SELECT k, (avg(w) OVER w + rank() OVER w), k FROM kv WINDOW w AS (PARTITION BY v ORDER BY w) ORDER BY 1 ---- 1 4.6666666666666666667 1 3 5.5 3 5 6 5 6 4.6666666666666666667 6 7 3 7 8 3 8 9 8.25 9 10 8.3333333333333333333 10 11 9 11 query II SELECT k, dense_rank() OVER () FROM kv ORDER BY 1 ---- 1 1 3 1 5 1 6 1 7 1 8 1 9 1 10 1 11 1 query III SELECT k, v, dense_rank() OVER (PARTITION BY v) FROM kv ORDER BY 1 ---- 1 2 1 3 4 1 5 NULL 1 6 2 1 7 2 1 8 4 1 9 2 1 10 4 1 11 NULL 1 query IIII SELECT k, v, w, dense_rank() OVER (PARTITION BY v ORDER BY w) FROM kv ORDER BY 1 ---- 1 2 3 2 3 4 5 2 5 NULL 5 1 6 2 3 2 7 2 2 1 8 4 2 1 9 2 9 3 10 4 9 3 11 NULL 9 2 query IR SELECT k, percent_rank() OVER () FROM kv ORDER BY 1 ---- 1 0 3 0 5 0 6 0 7 0 8 0 9 0 10 0 11 0 query IIR SELECT k, v, percent_rank() OVER (PARTITION BY v) FROM kv ORDER BY 1 ---- 1 2 0 3 4 0 5 NULL 0 6 2 0 7 2 0 8 4 0 9 2 0 10 4 0 11 NULL 0 query IIIR SELECT k, v, w, percent_rank() OVER (PARTITION BY v ORDER BY w) FROM kv ORDER BY 1 ---- 1 2 3 0.333333333333333 3 4 5 0.5 5 NULL 5 0 6 2 3 0.333333333333333 7 2 2 0 8 4 2 0 9 2 9 1 10 4 9 1 11 NULL 9 1 query IR SELECT k, cume_dist() OVER () FROM kv ORDER BY 1 ---- 1 1 3 1 5 1 6 1 7 1 8 1 9 1 10 1 11 1 query IIR SELECT k, v, cume_dist() OVER (PARTITION BY v) FROM kv ORDER BY 1 ---- 1 2 1 3 4 1 5 NULL 1 6 2 1 7 2 1 8 4 1 9 2 1 10 4 1 11 NULL 1 query IIIR SELECT k, v, w, cume_dist() OVER (PARTITION BY v ORDER BY w) FROM kv ORDER BY 1 ---- 1 2 3 0.75 3 4 5 0.666666666666667 5 NULL 5 0.5 6 2 3 0.75 7 2 2 0.25 8 4 2 0.333333333333333 9 2 9 1 10 4 9 1 11 NULL 9 1 query error argument of ntile\(\) must be greater than zero SELECT k, ntile(-10) OVER () FROM kv ORDER BY 1 query error argument of ntile\(\) must be greater than zero SELECT k, ntile(0) OVER () FROM kv ORDER BY 1 query II SELECT k, ntile(NULL::INT) OVER () FROM kv ORDER BY 1 ---- 1 NULL 3 NULL 5 NULL 6 NULL 7 NULL 8 NULL 9 NULL 10 NULL 11 NULL query II SELECT k, ntile(1) OVER () FROM kv ORDER BY 1 ---- 1 1 3 1 5 1 6 1 7 1 8 1 9 1 10 1 11 1 query II SELECT k, ntile(4) OVER (ORDER BY k) FROM kv ORDER BY 1 ---- 1 1 3 1 5 1 6 2 7 2 8 3 9 3 10 4 11 4 query II SELECT k, ntile(20) OVER (ORDER BY k) FROM kv ORDER BY 1 ---- 1 1 3 2 5 3 6 4 7 5 8 6 9 7 10 8 11 9 # The value of 'w' in the first row will be 3. query II SELECT k, ntile(w) OVER (ORDER BY k) FROM kv ORDER BY 1 ---- 1 1 3 1 5 1 6 2 7 2 8 2 9 3 10 3 11 3 query III SELECT k, v, ntile(3) OVER (PARTITION BY v ORDER BY k) FROM kv ORDER BY 1 ---- 1 2 1 3 4 1 5 NULL 1 6 2 1 7 2 2 8 4 2 9 2 3 10 4 3 11 NULL 2 query IIII SELECT k, v, w, ntile(6) OVER (PARTITION BY v ORDER BY w, k) FROM kv ORDER BY 1 ---- 1 2 3 2 3 4 5 2 5 NULL 5 1 6 2 3 3 7 2 2 1 8 4 2 1 9 2 9 4 10 4 9 3 11 NULL 9 2 query II SELECT k, ntile(w) OVER (PARTITION BY k) FROM kv ORDER BY 1 ---- 1 1 3 1 5 1 6 1 7 1 8 1 9 1 10 1 11 1 query III SELECT k, v, ntile(3) OVER (PARTITION BY v, k) FROM kv ORDER BY 1 ---- 1 2 1 3 4 1 5 NULL 1 6 2 1 7 2 1 8 4 1 9 2 1 10 4 1 11 NULL 1 query IIII SELECT k, v, w, ntile(6) OVER (PARTITION BY v, k ORDER BY w) FROM kv ORDER BY 1 ---- 1 2 3 1 3 4 5 1 5 NULL 5 1 6 2 3 1 7 2 2 1 8 4 2 1 9 2 9 1 10 4 9 1 11 NULL 9 1 query II SELECT k, lag(9) OVER (ORDER BY k) FROM kv ORDER BY 1 ---- 1 NULL 3 9 5 9 6 9 7 9 8 9 9 9 10 9 11 9 query II SELECT k, lead(9) OVER (ORDER BY k) FROM kv ORDER BY 1 ---- 1 9 3 9 5 9 6 9 7 9 8 9 9 9 10 9 11 NULL query II SELECT k, lag(k) OVER (ORDER BY k) FROM kv ORDER BY 1 ---- 1 NULL 3 1 5 3 6 5 7 6 8 7 9 8 10 9 11 10 query II SELECT k, lag(k) OVER (PARTITION BY v ORDER BY w, k) FROM kv ORDER BY 1 ---- 1 7 3 8 5 NULL 6 1 7 NULL 8 NULL 9 6 10 3 11 5 query II SELECT k, lead(k) OVER (ORDER BY k) FROM kv ORDER BY 1 ---- 1 3 3 5 5 6 6 7 7 8 8 9 9 10 10 11 11 NULL query II SELECT k, lead(k) OVER (PARTITION BY v ORDER BY w, k) FROM kv ORDER BY 1 ---- 1 6 3 10 5 11 6 9 7 1 8 3 9 NULL 10 NULL 11 NULL query II SELECT k, lag(k, 3) OVER (ORDER BY k) FROM kv ORDER BY 1 ---- 1 NULL 3 NULL 5 NULL 6 1 7 3 8 5 9 6 10 7 11 8 query II SELECT k, lag(k, 3) OVER (PARTITION BY v ORDER BY w) FROM kv ORDER BY 1 ---- 1 NULL 3 NULL 5 NULL 6 NULL 7 NULL 8 NULL 9 7 10 NULL 11 NULL query II SELECT k, lead(k, 3) OVER (ORDER BY k) FROM kv ORDER BY 1 ---- 1 6 3 7 5 8 6 9 7 10 8 11 9 NULL 10 NULL 11 NULL query II SELECT k, lead(k, 3) OVER (PARTITION BY v ORDER BY w) FROM kv ORDER BY 1 ---- 1 NULL 3 NULL 5 NULL 6 NULL 7 9 8 NULL 9 NULL 10 NULL 11 NULL query II SELECT k, lag(k, -5) OVER (ORDER BY k) FROM kv ORDER BY 1 ---- 1 8 3 9 5 10 6 11 7 NULL 8 NULL 9 NULL 10 NULL 11 NULL query II SELECT k, lead(k, -5) OVER (ORDER BY k) FROM kv ORDER BY 1 ---- 1 NULL 3 NULL 5 NULL 6 NULL 7 NULL 8 1 9 3 10 5 11 6 query II SELECT k, lag(k, 0) OVER () FROM kv ORDER BY 1 ---- 1 1 3 3 5 5 6 6 7 7 8 8 9 9 10 10 11 11 query II SELECT k, lead(k, 0) OVER () FROM kv ORDER BY 1 ---- 1 1 3 3 5 5 6 6 7 7 8 8 9 9 10 10 11 11 query II SELECT k, lag(k, NULL::INT) OVER () FROM kv ORDER BY 1 ---- 1 NULL 3 NULL 5 NULL 6 NULL 7 NULL 8 NULL 9 NULL 10 NULL 11 NULL query II SELECT k, lead(k, NULL::INT) OVER () FROM kv ORDER BY 1 ---- 1 NULL 3 NULL 5 NULL 6 NULL 7 NULL 8 NULL 9 NULL 10 NULL 11 NULL query II SELECT k, lag(k, w) OVER (ORDER BY k) FROM kv ORDER BY 1 ---- 1 NULL 3 NULL 5 NULL 6 1 7 5 8 6 9 NULL 10 NULL 11 NULL query II SELECT k, lag(k, w) OVER (PARTITION BY v ORDER BY w, k) FROM kv ORDER BY 1 ---- 1 NULL 3 NULL 5 NULL 6 NULL 7 NULL 8 NULL 9 NULL 10 NULL 11 NULL query II SELECT k, lead(k, w) OVER (ORDER BY k) FROM kv ORDER BY 1 ---- 1 6 3 9 5 10 6 9 7 9 8 10 9 NULL 10 NULL 11 NULL query II SELECT k, lead(k, w) OVER (PARTITION BY v ORDER BY w, k) FROM kv ORDER BY 1 ---- 1 NULL 3 NULL 5 NULL 6 NULL 7 6 8 10 9 NULL 10 NULL 11 NULL query error unknown signature: lag\(int, int, string\) SELECT k, lag(k, 1, 'FOO') OVER () FROM kv ORDER BY 1 query error unknown signature: lead\(int, int, string\) SELECT k, lead(k, 1, 'FOO') OVER () FROM kv ORDER BY 1 query error unknown signature: lag\(int, int, string\) SELECT k, lag(k, 1, s) OVER () FROM kv ORDER BY 1 query error unknown signature: lead\(int, int, string\) SELECT k, lead(k, 1, s) OVER () FROM kv ORDER BY 1 query II SELECT k, lag(k, 3, -99) OVER (ORDER BY k) FROM kv ORDER BY 1 ---- 1 -99 3 -99 5 -99 6 1 7 3 8 5 9 6 10 7 11 8 query II SELECT k, lead(k, 3, -99) OVER (ORDER BY k) FROM kv ORDER BY 1 ---- 1 6 3 7 5 8 6 9 7 10 8 11 9 -99 10 -99 11 -99 query II SELECT k, lag(k, 3, v) OVER (ORDER BY k) FROM kv ORDER BY 1 ---- 1 2 3 4 5 NULL 6 1 7 3 8 5 9 6 10 7 11 8 query II SELECT k, lead(k, 3, v) OVER (ORDER BY k) FROM kv ORDER BY 1 ---- 1 6 3 7 5 8 6 9 7 10 8 11 9 2 10 4 11 NULL query II SELECT k, (lag(k, 5, w) OVER w + lead(k, 3, v) OVER w) FROM kv WINDOW w AS (ORDER BY k) ORDER BY 1 ---- 1 9 3 12 5 13 6 12 7 12 8 12 9 5 10 9 11 NULL query II SELECT k, lag(k) OVER (PARTITION BY k) FROM kv ORDER BY 1 ---- 1 NULL 3 NULL 5 NULL 6 NULL 7 NULL 8 NULL 9 NULL 10 NULL 11 NULL query II SELECT k, lead(k) OVER (PARTITION BY k) FROM kv ORDER BY 1 ---- 1 NULL 3 NULL 5 NULL 6 NULL 7 NULL 8 NULL 9 NULL 10 NULL 11 NULL query II SELECT k, lag(k, 0) OVER (PARTITION BY k) FROM kv ORDER BY 1 ---- 1 1 3 3 5 5 6 6 7 7 8 8 9 9 10 10 11 11 query II SELECT k, lead(k, 0) OVER (PARTITION BY k) FROM kv ORDER BY 1 ---- 1 1 3 3 5 5 6 6 7 7 8 8 9 9 10 10 11 11 query II SELECT k, lag(k, -5) OVER (PARTITION BY k) FROM kv ORDER BY 1 ---- 1 NULL 3 NULL 5 NULL 6 NULL 7 NULL 8 NULL 9 NULL 10 NULL 11 NULL query II SELECT k, lead(k, -5) OVER (PARTITION BY k) FROM kv ORDER BY 1 ---- 1 NULL 3 NULL 5 NULL 6 NULL 7 NULL 8 NULL 9 NULL 10 NULL 11 NULL query II SELECT k, lag(k, w - w) OVER (PARTITION BY k) FROM kv ORDER BY 1 ---- 1 1 3 3 5 5 6 6 7 7 8 8 9 9 10 10 11 11 query II SELECT k, lead(k, w - w) OVER (PARTITION BY k) FROM kv ORDER BY 1 ---- 1 1 3 3 5 5 6 6 7 7 8 8 9 9 10 10 11 11 query II SELECT k, lag(k, 3, -99) OVER (PARTITION BY k) FROM kv ORDER BY 1 ---- 1 -99 3 -99 5 -99 6 -99 7 -99 8 -99 9 -99 10 -99 11 -99 query II SELECT k, lead(k, 3, -99) OVER (PARTITION BY k) FROM kv ORDER BY 1 ---- 1 -99 3 -99 5 -99 6 -99 7 -99 8 -99 9 -99 10 -99 11 -99 query II SELECT k, lag(k, 3, v) OVER (PARTITION BY k) FROM kv ORDER BY 1 ---- 1 2 3 4 5 NULL 6 2 7 2 8 4 9 2 10 4 11 NULL query II SELECT k, lead(k, 3, v) OVER (PARTITION BY k) FROM kv ORDER BY 1 ---- 1 2 3 4 5 NULL 6 2 7 2 8 4 9 2 10 4 11 NULL query II SELECT k, first_value(NULL::INT) OVER () FROM kv ORDER BY 1 ---- 1 NULL 3 NULL 5 NULL 6 NULL 7 NULL 8 NULL 9 NULL 10 NULL 11 NULL query II SELECT k, first_value(1) OVER () FROM kv ORDER BY 1 ---- 1 1 3 1 5 1 6 1 7 1 8 1 9 1 10 1 11 1 query IR SELECT k, first_value(199.9 * 23.3) OVER () FROM kv ORDER BY 1 ---- 1 4657.67 3 4657.67 5 4657.67 6 4657.67 7 4657.67 8 4657.67 9 4657.67 10 4657.67 11 4657.67 query II SELECT k, first_value(v) OVER (ORDER BY k) FROM kv ORDER BY 1 ---- 1 2 3 2 5 2 6 2 7 2 8 2 9 2 10 2 11 2 query IIII SELECT k, v, w, first_value(w) OVER (PARTITION BY v ORDER BY w) FROM kv ORDER BY 1 ---- 1 2 3 2 3 4 5 2 5 NULL 5 5 6 2 3 2 7 2 2 2 8 4 2 2 9 2 9 2 10 4 9 2 11 NULL 9 5 query IIII SELECT k, v, w, first_value(w) OVER (PARTITION BY v ORDER BY w DESC) FROM kv ORDER BY 1 ---- 1 2 3 9 3 4 5 9 5 NULL 5 9 6 2 3 9 7 2 2 9 8 4 2 9 9 2 9 9 10 4 9 9 11 NULL 9 9 query II SELECT k, first_value(v) OVER (PARTITION BY k) FROM kv ORDER BY 1 ---- 1 2 3 4 5 NULL 6 2 7 2 8 4 9 2 10 4 11 NULL query II SELECT k, last_value(NULL::INT) OVER () FROM kv ORDER BY 1 ---- 1 NULL 3 NULL 5 NULL 6 NULL 7 NULL 8 NULL 9 NULL 10 NULL 11 NULL query II SELECT k, last_value(1) OVER () FROM kv ORDER BY 1 ---- 1 1 3 1 5 1 6 1 7 1 8 1 9 1 10 1 11 1 query IR SELECT k, last_value(199.9 * 23.3) OVER () FROM kv ORDER BY 1 ---- 1 4657.67 3 4657.67 5 4657.67 6 4657.67 7 4657.67 8 4657.67 9 4657.67 10 4657.67 11 4657.67 query II SELECT k, last_value(v) OVER (ORDER BY k) FROM kv ORDER BY 1 ---- 1 2 3 4 5 NULL 6 2 7 2 8 4 9 2 10 4 11 NULL query IIII SELECT k, v, w, last_value(w) OVER (PARTITION BY v ORDER BY w) FROM kv ORDER BY 1 ---- 1 2 3 3 3 4 5 5 5 NULL 5 5 6 2 3 3 7 2 2 2 8 4 2 2 9 2 9 9 10 4 9 9 11 NULL 9 9 query IIII SELECT k, v, w, last_value(w) OVER (PARTITION BY v ORDER BY w DESC) FROM kv ORDER BY 1 ---- 1 2 3 3 3 4 5 5 5 NULL 5 5 6 2 3 3 7 2 2 2 8 4 2 2 9 2 9 9 10 4 9 9 11 NULL 9 9 query II SELECT k, last_value(v) OVER (PARTITION BY k) FROM kv ORDER BY 1 ---- 1 2 3 4 5 NULL 6 2 7 2 8 4 9 2 10 4 11 NULL query error unknown signature: nth_value\(int, string\) SELECT k, nth_value(v, 'FOO') OVER () FROM kv ORDER BY 1 query error argument of nth_value\(\) must be greater than zero SELECT k, nth_value(v, -99) OVER () FROM kv ORDER BY 1 query error argument of nth_value\(\) must be greater than zero SELECT k, nth_value(v, 0) OVER () FROM kv ORDER BY 1 query II SELECT k, nth_value(NULL::INT, 5) OVER () FROM kv ORDER BY 1 ---- 1 NULL 3 NULL 5 NULL 6 NULL 7 NULL 8 NULL 9 NULL 10 NULL 11 NULL query II SELECT k, nth_value(1, 3) OVER () FROM kv ORDER BY 1 ---- 1 1 3 1 5 1 6 1 7 1 8 1 9 1 10 1 11 1 query II SELECT k, nth_value(1, 33) OVER () FROM kv ORDER BY 1 ---- 1 NULL 3 NULL 5 NULL 6 NULL 7 NULL 8 NULL 9 NULL 10 NULL 11 NULL query IR SELECT k, nth_value(199.9 * 23.3, 7) OVER () FROM kv ORDER BY 1 ---- 1 4657.67 3 4657.67 5 4657.67 6 4657.67 7 4657.67 8 4657.67 9 4657.67 10 4657.67 11 4657.67 query II SELECT k, nth_value(v, 8) OVER (ORDER BY k) FROM kv ORDER BY 1 ---- 1 NULL 3 NULL 5 NULL 6 NULL 7 NULL 8 NULL 9 NULL 10 4 11 4 query IIII SELECT k, v, w, nth_value(w, 2) OVER (PARTITION BY v ORDER BY w) FROM kv ORDER BY 1 ---- 1 2 3 3 3 4 5 5 5 NULL 5 NULL 6 2 3 3 7 2 2 NULL 8 4 2 NULL 9 2 9 3 10 4 9 5 11 NULL 9 9 query IIII SELECT k, v, w, nth_value(w, 2) OVER (PARTITION BY v ORDER BY w DESC) FROM kv ORDER BY 1 ---- 1 2 3 3 3 4 5 5 5 NULL 5 5 6 2 3 3 7 2 2 3 8 4 2 5 9 2 9 NULL 10 4 9 NULL 11 NULL 9 NULL query II SELECT k, nth_value(v, k) OVER (ORDER BY k) FROM kv ORDER BY 1 ---- 1 2 3 NULL 5 NULL 6 NULL 7 NULL 8 NULL 9 NULL 10 NULL 11 NULL query II SELECT k, nth_value(v, v) OVER (ORDER BY k) FROM kv ORDER BY 1 ---- 1 NULL 3 NULL 5 NULL 6 4 7 4 8 2 9 4 10 2 11 NULL query II SELECT k, nth_value(v, 1) OVER (PARTITION BY k) FROM kv ORDER BY 1 ---- 1 2 3 4 5 NULL 6 2 7 2 8 4 9 2 10 4 11 NULL query II SELECT k, nth_value(v, v) OVER (PARTITION BY k) FROM kv ORDER BY 1 ---- 1 NULL 3 NULL 5 NULL 6 NULL 7 NULL 8 NULL 9 NULL 10 NULL 11 NULL statement ok INSERT INTO kv VALUES (12, -1, DEFAULT, DEFAULT, DEFAULT, DEFAULT) query error argument of nth_value\(\) must be greater than zero SELECT k, nth_value(v, v) OVER () FROM kv ORDER BY 1 statement ok DELETE FROM kv WHERE k = 12 query error FILTER specified, but rank is not an aggregate function SELECT k, rank() FILTER (WHERE k=1) OVER () FROM kv # Issue materialize#14606: correctly handle aggregation functions above the windowing level query I SELECT max(i) * (row_number() OVER (ORDER BY max(i))) FROM (SELECT 1 AS i, 2 AS j) GROUP BY j ---- 1 query R SELECT (1/j) * max(i) * (row_number() OVER (ORDER BY max(i))) FROM (SELECT 1 AS i, 2 AS j) GROUP BY j ---- 0.5 query R SELECT max(i) * (1/j) * (row_number() OVER (ORDER BY max(i))) FROM (SELECT 1 AS i, 2 AS j) GROUP BY j ---- 0.5 # regression test for materialize#23798 until materialize#10495 is fixed. statement error function reserved for internal use SELECT final_variance(1.2, 1.2, 123) OVER (PARTITION BY k) FROM kv statement ok CREATE TABLE products ( group_id serial PRIMARY KEY, group_name VARCHAR (255) NOT NULL, product_name VARCHAR (255) NOT NULL, price DECIMAL (11, 2), priceInt INT, priceFloat FLOAT, pDate DATE, pTime TIME, pTimestamp TIMESTAMP, pTimestampTZ TIMESTAMPTZ, pInterval INTERVAL ) statement ok INSERT INTO products (group_name, product_name, price, priceInt, priceFloat, pDate, pTime, pTimestamp, pTimestampTZ, pInterval) VALUES ('Smartphone', 'Microsoft Lumia', 200, 200, 200, '2018-07-30', TIME '01:23:45', TIMESTAMP '2018-07-30 01:23:45', TIMESTAMPTZ '2018-07-30 01:23:45', INTERVAL '1 months 2 days 3 hours 4 minutes 5 seconds'), ('Smartphone', 'HTC One', 400, 400, 400, '2018-07-31', TIME '12:34:56', TIMESTAMP '2018-07-31 12:34:56', TIMESTAMPTZ '2018-07-31 12:34:56', INTERVAL '1 days 2 hours 3 minutes 4 seconds'), ('Smartphone', 'Nexus', 500, 500, 500, '2018-07-30', TIME '11:23:45', TIMESTAMP '2018-07-30 11:23:45', TIMESTAMPTZ '2018-07-30 11:23:45', INTERVAL '1 hours 2 minutes 3 seconds'), ('Smartphone', 'iPhone', 900, 900, 900, '2018-07-31', TIME '07:34:56', TIMESTAMP '2018-07-31 07:34:56', TIMESTAMPTZ '2018-07-31 07:34:56', INTERVAL '1 minutes 2 seconds'), ('Laptop', 'HP Elite', 1200, 1200, 1200, '2018-07-30', TIME '01:23:45', TIMESTAMP '2018-07-30 01:23:45', TIMESTAMPTZ '2018-07-30 01:23:45', INTERVAL '1 months 2 days 3 hours 4 minutes 5 seconds'), ('Laptop', 'Lenovo Thinkpad', 700, 700, 700, '2018-07-31', TIME '12:34:56', TIMESTAMP '2018-07-31 12:34:56', TIMESTAMPTZ '2018-07-31 12:34:56', INTERVAL '1 days 2 hours 3 minutes 4 seconds'), ('Laptop', 'Sony VAIO', 700, 700, 700, '2018-07-30', TIME '11:23:45', TIMESTAMP '2018-07-30 11:23:45', TIMESTAMPTZ '2018-07-30 11:23:45', INTERVAL '1 hours 2 minutes 3 seconds'), ('Laptop', 'Dell', 800, 800, 800, '2018-07-31', TIME '07:34:56', TIMESTAMP '2018-07-31 07:34:56', TIMESTAMPTZ '2018-07-31 07:34:56', INTERVAL '1 minutes 2 seconds'), ('Tablet', 'iPad', 700, 700, 700, '2018-07-30', TIME '01:23:45', TIMESTAMP '2018-07-30 01:23:45', TIMESTAMPTZ '2018-07-30 01:23:45', INTERVAL '1 months 2 days 3 hours 4 minutes 5 seconds'), ('Tablet', 'Kindle Fire', 150, 150, 150, '2018-07-31', TIME '12:34:56', TIMESTAMP '2018-07-31 12:34:56', TIMESTAMPTZ '2018-07-31 12:34:56', INTERVAL '1 days 2 hours 3 minutes 4 seconds'), ('Tablet', 'Samsung', 200, 200, 200, '2018-07-30', TIME '11:23:45', TIMESTAMP '2018-07-30 11:23:45', TIMESTAMPTZ '2018-07-30 11:23:45', INTERVAL '1 hours 2 minutes 3 seconds') statement error cannot copy window "w" because it has a frame clause SELECT avg(price) OVER (w) FROM products WINDOW w AS (ROWS 1 PRECEDING) statement error cannot copy window "w" because it has a frame clause SELECT avg(price) OVER (w ORDER BY price) FROM products WINDOW w AS (ROWS 1 PRECEDING) statement error frame starting offset must not be null SELECT avg(price) OVER (ROWS NULL PRECEDING) FROM products statement error frame starting offset must not be null SELECT avg(price) OVER (ROWS BETWEEN NULL PRECEDING AND 1 FOLLOWING) FROM products statement error frame starting offset must not be negative SELECT price, avg(price) OVER (PARTITION BY price ROWS -1 PRECEDING) AS avg_price FROM products statement error frame starting offset must not be negative SELECT price, avg(price) OVER w AS avg_price FROM products WINDOW w AS (PARTITION BY price ROWS -1 PRECEDING) statement error frame ending offset must not be null SELECT avg(price) OVER (ROWS BETWEEN 1 PRECEDING AND NULL FOLLOWING) FROM products statement error frame ending offset must not be negative SELECT price, avg(price) OVER (PARTITION BY price ROWS BETWEEN 1 FOLLOWING AND -1 FOLLOWING) AS avg_price FROM products statement error frame ending offset must not be negative SELECT price, avg(price) OVER w AS avg_price FROM products WINDOW w AS (PARTITION BY price ROWS BETWEEN 1 FOLLOWING AND -1 FOLLOWING) statement error frame ending offset must not be negative SELECT product_name, price, min(price) OVER (PARTITION BY group_name ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS min_over_three, max(price) OVER (PARTITION BY group_name ROWS BETWEEN UNBOUNDED PRECEDING AND -1 FOLLOWING) AS max_over_partition FROM products ORDER BY group_id statement error incompatible window frame start type: decimal SELECT avg(price) OVER (PARTITION BY group_name ROWS 1.5 PRECEDING) AS avg_price FROM products statement error incompatible window frame start type: decimal SELECT avg(price) OVER w AS avg_price FROM products WINDOW w AS (PARTITION BY group_name ROWS 1.5 PRECEDING) statement error incompatible window frame start type: decimal SELECT avg(price) OVER (PARTITION BY group_name ROWS BETWEEN 1.5 PRECEDING AND UNBOUNDED FOLLOWING) AS avg_price FROM products statement error incompatible window frame start type: decimal SELECT avg(price) OVER w AS avg_price FROM products WINDOW w AS (PARTITION BY group_name ROWS BETWEEN 1.5 PRECEDING AND UNBOUNDED FOLLOWING) statement error incompatible window frame end type: decimal SELECT avg(price) OVER (PARTITION BY group_name ROWS BETWEEN UNBOUNDED PRECEDING AND 1.5 FOLLOWING) AS avg_price FROM products statement error incompatible window frame end type: decimal SELECT avg(price) OVER w AS avg_price FROM products WINDOW w AS (PARTITION BY group_name ROWS BETWEEN UNBOUNDED PRECEDING AND 1.5 FOLLOWING) query TRT SELECT product_name, price, first_value(product_name) OVER w AS first FROM products WHERE price = 200 OR price = 700 WINDOW w as (PARTITION BY price ORDER BY product_name RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) ORDER BY price, product_name ---- Microsoft Lumia 200.00 Microsoft Lumia Samsung 200.00 Microsoft Lumia Lenovo Thinkpad 700.00 Lenovo Thinkpad Sony VAIO 700.00 Lenovo Thinkpad iPad 700.00 Lenovo Thinkpad query TRT SELECT product_name, price, last_value(product_name) OVER w AS last FROM products WHERE price = 200 OR price = 700 WINDOW w as (PARTITION BY price ORDER BY product_name RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) ORDER BY price, product_name ---- Microsoft Lumia 200.00 Samsung Samsung 200.00 Samsung Lenovo Thinkpad 700.00 iPad Sony VAIO 700.00 iPad iPad 700.00 iPad query TRT SELECT product_name, price, nth_value(product_name, 2) OVER w AS second FROM products WHERE price = 200 OR price = 700 WINDOW w as (PARTITION BY price ORDER BY product_name RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) ORDER BY price, product_name ---- Microsoft Lumia 200.00 Samsung Samsung 200.00 NULL Lenovo Thinkpad 700.00 Sony VAIO Sony VAIO 700.00 iPad iPad 700.00 NULL query TTRR SELECT product_name, group_name, price, avg(price) OVER (PARTITION BY group_name ORDER BY price, product_name ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS avg_of_three FROM products ORDER BY group_name, price, product_name ---- Lenovo Thinkpad Laptop 700.00 700.00 Sony VAIO Laptop 700.00 733.33333333333333333 Dell Laptop 800.00 900.00 HP Elite Laptop 1200.00 1000.00 Microsoft Lumia Smartphone 200.00 300.00 HTC One Smartphone 400.00 366.66666666666666667 Nexus Smartphone 500.00 600.00 iPhone Smartphone 900.00 700.00 Kindle Fire Tablet 150.00 175.00 Samsung Tablet 200.00 350.00 iPad Tablet 700.00 450.00 query TTRR SELECT product_name, group_name, price, avg(priceFloat) OVER (PARTITION BY group_name ORDER BY price, product_name ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS avg_of_three_floats FROM products ORDER BY group_name, price, product_name ---- Lenovo Thinkpad Laptop 700.00 700 Sony VAIO Laptop 700.00 733.333333333333 Dell Laptop 800.00 900 HP Elite Laptop 1200.00 1000 Microsoft Lumia Smartphone 200.00 300 HTC One Smartphone 400.00 366.666666666667 Nexus Smartphone 500.00 600 iPhone Smartphone 900.00 700 Kindle Fire Tablet 150.00 175 Samsung Tablet 200.00 350 iPad Tablet 700.00 450 query TTRR SELECT product_name, group_name, price, avg(priceInt) OVER (PARTITION BY group_name ORDER BY price, product_name ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS avg_of_three_ints FROM products ORDER BY group_name, price, product_name ---- Lenovo Thinkpad Laptop 700.00 700 Sony VAIO Laptop 700.00 733.33333333333333333 Dell Laptop 800.00 900 HP Elite Laptop 1200.00 1000 Microsoft Lumia Smartphone 200.00 300 HTC One Smartphone 400.00 366.66666666666666667 Nexus Smartphone 500.00 600 iPhone Smartphone 900.00 700 Kindle Fire Tablet 150.00 175 Samsung Tablet 200.00 350 iPad Tablet 700.00 450 query TTRR SELECT group_name, product_name, price, avg(price) OVER (PARTITION BY group_name ORDER BY group_id ROWS (SELECT count(*) FROM PRODUCTS WHERE price = 200) PRECEDING) AS running_avg_of_three FROM products ORDER BY group_id ---- Smartphone Microsoft Lumia 200.00 200.00 Smartphone HTC One 400.00 300.00 Smartphone Nexus 500.00 366.66666666666666667 Smartphone iPhone 900.00 600.00 Laptop HP Elite 1200.00 1200.00 Laptop Lenovo Thinkpad 700.00 950.00 Laptop Sony VAIO 700.00 866.66666666666666667 Laptop Dell 800.00 733.33333333333333333 Tablet iPad 700.00 700.00 Tablet Kindle Fire 150.00 425.00 Tablet Samsung 200.00 350.00 query TTRR SELECT group_name, product_name, price, sum(price) OVER (PARTITION BY group_name ORDER BY group_id ROWS 2 PRECEDING) AS running_sum FROM products ORDER BY group_id ---- Smartphone Microsoft Lumia 200.00 200.00 Smartphone HTC One 400.00 600.00 Smartphone Nexus 500.00 1100.00 Smartphone iPhone 900.00 1800.00 Laptop HP Elite 1200.00 1200.00 Laptop Lenovo Thinkpad 700.00 1900.00 Laptop Sony VAIO 700.00 2600.00 Laptop Dell 800.00 2200.00 Tablet iPad 700.00 700.00 Tablet Kindle Fire 150.00 850.00 Tablet Samsung 200.00 1050.00 query TTRT SELECT group_name, product_name, price, array_agg(price) OVER (PARTITION BY group_name ORDER BY group_id ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) AS array_agg_price FROM products ORDER BY group_id ---- Smartphone Microsoft Lumia 200.00 {200.00,400.00,500.00} Smartphone HTC One 400.00 {200.00,400.00,500.00,900.00} Smartphone Nexus 500.00 {400.00,500.00,900.00} Smartphone iPhone 900.00 {500.00,900.00} Laptop HP Elite 1200.00 {1200.00,700.00,700.00} Laptop Lenovo Thinkpad 700.00 {1200.00,700.00,700.00,800.00} Laptop Sony VAIO 700.00 {700.00,700.00,800.00} Laptop Dell 800.00 {700.00,800.00} Tablet iPad 700.00 {700.00,150.00,200.00} Tablet Kindle Fire 150.00 {700.00,150.00,200.00} Tablet Samsung 200.00 {150.00,200.00} query TTRR SELECT group_name, product_name, price, avg(price) OVER (PARTITION BY group_name RANGE UNBOUNDED PRECEDING) AS avg_price FROM products ORDER BY group_id ---- Smartphone Microsoft Lumia 200.00 500.00 Smartphone HTC One 400.00 500.00 Smartphone Nexus 500.00 500.00 Smartphone iPhone 900.00 500.00 Laptop HP Elite 1200.00 850.00 Laptop Lenovo Thinkpad 700.00 850.00 Laptop Sony VAIO 700.00 850.00 Laptop Dell 800.00 850.00 Tablet iPad 700.00 350.00 Tablet Kindle Fire 150.00 350.00 Tablet Samsung 200.00 350.00 query TTRT SELECT group_name, product_name, price, min(price) OVER (PARTITION BY group_name ROWS BETWEEN 1 PRECEDING AND 2 PRECEDING) AS min_over_empty_frame FROM products ORDER BY group_id ---- Smartphone Microsoft Lumia 200.00 NULL Smartphone HTC One 400.00 NULL Smartphone Nexus 500.00 NULL Smartphone iPhone 900.00 NULL Laptop HP Elite 1200.00 NULL Laptop Lenovo Thinkpad 700.00 NULL Laptop Sony VAIO 700.00 NULL Laptop Dell 800.00 NULL Tablet iPad 700.00 NULL Tablet Kindle Fire 150.00 NULL Tablet Samsung 200.00 NULL query TRRR SELECT product_name, price, min(price) OVER (PARTITION BY group_name ORDER BY group_id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS min_over_three, max(price) OVER (PARTITION BY group_name ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS max_over_partition FROM products ORDER BY group_id ---- Microsoft Lumia 200.00 200.00 900.00 HTC One 400.00 200.00 900.00 Nexus 500.00 400.00 900.00 iPhone 900.00 500.00 900.00 HP Elite 1200.00 700.00 1200.00 Lenovo Thinkpad 700.00 700.00 1200.00 Sony VAIO 700.00 700.00 1200.00 Dell 800.00 700.00 1200.00 iPad 700.00 150.00 700.00 Kindle Fire 150.00 150.00 700.00 Samsung 200.00 150.00 700.00 query TTRT SELECT group_name, product_name, price, min(price) OVER (PARTITION BY group_name ROWS CURRENT ROW) AS min_over_single_row FROM products ORDER BY group_id ---- Smartphone Microsoft Lumia 200.00 200.00 Smartphone HTC One 400.00 400.00 Smartphone Nexus 500.00 500.00 Smartphone iPhone 900.00 900.00 Laptop HP Elite 1200.00 1200.00 Laptop Lenovo Thinkpad 700.00 700.00 Laptop Sony VAIO 700.00 700.00 Laptop Dell 800.00 800.00 Tablet iPad 700.00 700.00 Tablet Kindle Fire 150.00 150.00 Tablet Samsung 200.00 200.00 query TTRR SELECT group_name, product_name, price, avg(price) OVER (PARTITION BY group_name ORDER BY group_id ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS running_avg FROM products ORDER BY group_id ---- Smartphone Microsoft Lumia 200.00 600.00 Smartphone HTC One 400.00 700.00 Smartphone Nexus 500.00 900.00 Smartphone iPhone 900.00 NULL Laptop HP Elite 1200.00 733.33333333333333333 Laptop Lenovo Thinkpad 700.00 750.00 Laptop Sony VAIO 700.00 800.00 Laptop Dell 800.00 NULL Tablet iPad 700.00 175.00 Tablet Kindle Fire 150.00 200.00 Tablet Samsung 200.00 NULL query TRRRRR SELECT product_name, price, min(price) OVER (PARTITION BY group_name ORDER BY group_id ROWS UNBOUNDED PRECEDING), max(price) OVER (PARTITION BY group_name ORDER BY group_id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING), sum(price) OVER (PARTITION BY group_name ORDER BY group_id ROWS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING), avg(price) OVER (PARTITION BY group_name ROWS CURRENT ROW) FROM products ORDER BY group_id ---- Microsoft Lumia 200.00 200.00 400.00 2000.00 200.00 HTC One 400.00 200.00 500.00 2000.00 400.00 Nexus 500.00 200.00 900.00 1800.00 500.00 iPhone 900.00 200.00 900.00 1400.00 900.00 HP Elite 1200.00 1200.00 1200.00 3400.00 1200.00 Lenovo Thinkpad 700.00 700.00 1200.00 3400.00 700.00 Sony VAIO 700.00 700.00 1200.00 2200.00 700.00 Dell 800.00 700.00 1200.00 1500.00 800.00 iPad 700.00 700.00 700.00 1050.00 700.00 Kindle Fire 150.00 150.00 700.00 1050.00 150.00 Samsung 200.00 150.00 700.00 350.00 200.00 query RRR SELECT avg(price) OVER w1, avg(price) OVER w2, avg(price) OVER w1 FROM products WINDOW w1 AS (PARTITION BY group_name ORDER BY group_id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING), w2 AS (ORDER BY group_id ROWS 1 PRECEDING) ORDER BY group_id ---- 300.00 200.00 300.00 366.66666666666666667 300.00 366.66666666666666667 600.00 450.00 600.00 700.00 700.00 700.00 950.00 1050.00 950.00 866.66666666666666667 950.00 866.66666666666666667 733.33333333333333333 700.00 733.33333333333333333 750.00 750.00 750.00 425.00 750.00 425.00 350.00 425.00 350.00 175.00 175.00 175.00 # In the following 4 tests, since ORDER BY is omitted, all rows are peers, so frame includes all the rows for every row. query TTRR SELECT group_name, product_name, price, sum(price) OVER (RANGE CURRENT ROW) FROM products ORDER BY group_id ---- Smartphone Microsoft Lumia 200.00 6450.00 Smartphone HTC One 400.00 6450.00 Smartphone Nexus 500.00 6450.00 Smartphone iPhone 900.00 6450.00 Laptop HP Elite 1200.00 6450.00 Laptop Lenovo Thinkpad 700.00 6450.00 Laptop Sony VAIO 700.00 6450.00 Laptop Dell 800.00 6450.00 Tablet iPad 700.00 6450.00 Tablet Kindle Fire 150.00 6450.00 Tablet Samsung 200.00 6450.00 query TTRR SELECT group_name, product_name, price, sum(price) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) FROM products ORDER BY group_id ---- Smartphone Microsoft Lumia 200.00 6450.00 Smartphone HTC One 400.00 6450.00 Smartphone Nexus 500.00 6450.00 Smartphone iPhone 900.00 6450.00 Laptop HP Elite 1200.00 6450.00 Laptop Lenovo Thinkpad 700.00 6450.00 Laptop Sony VAIO 700.00 6450.00 Laptop Dell 800.00 6450.00 Tablet iPad 700.00 6450.00 Tablet Kindle Fire 150.00 6450.00 Tablet Samsung 200.00 6450.00 query TTRR SELECT group_name, product_name, price, sum(price) OVER (RANGE BETWEEN CURRENT ROW AND CURRENT ROW) FROM products ORDER BY group_id ---- Smartphone Microsoft Lumia 200.00 6450.00 Smartphone HTC One 400.00 6450.00 Smartphone Nexus 500.00 6450.00 Smartphone iPhone 900.00 6450.00 Laptop HP Elite 1200.00 6450.00 Laptop Lenovo Thinkpad 700.00 6450.00 Laptop Sony VAIO 700.00 6450.00 Laptop Dell 800.00 6450.00 Tablet iPad 700.00 6450.00 Tablet Kindle Fire 150.00 6450.00 Tablet Samsung 200.00 6450.00 query TTRR SELECT group_name, product_name, price, sum(price) OVER (RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM products ORDER BY group_id ---- Smartphone Microsoft Lumia 200.00 6450.00 Smartphone HTC One 400.00 6450.00 Smartphone Nexus 500.00 6450.00 Smartphone iPhone 900.00 6450.00 Laptop HP Elite 1200.00 6450.00 Laptop Lenovo Thinkpad 700.00 6450.00 Laptop Sony VAIO 700.00 6450.00 Laptop Dell 800.00 6450.00 Tablet iPad 700.00 6450.00 Tablet Kindle Fire 150.00 6450.00 Tablet Samsung 200.00 6450.00 statement error aggregate functions are not allowed in FILTER SELECT count(*) FILTER (WHERE count(*) > 5) OVER () FROM products statement error window functions are not allowed in FILTER SELECT count(*) FILTER (WHERE count(*) OVER () > 5) OVER () FROM products statement error incompatible FILTER expression type: int SELECT count(*) FILTER (WHERE 1) OVER () FROM products statement error syntax error at or near "filter" SELECT price FILTER (WHERE price=1) OVER () FROM products query II SELECT count(*) FILTER (WHERE true) OVER (), count(*) FILTER (WHERE false) OVER () FROM products ---- 11 0 11 0 11 0 11 0 11 0 11 0 11 0 11 0 11 0 11 0 11 0 query RRRR SELECT avg(price) FILTER (WHERE price > 300) OVER w1, sum(price) FILTER (WHERE group_name = 'Smartphone') OVER w2, avg(price) FILTER (WHERE price = 200 OR price = 700) OVER w1, avg(price) FILTER (WHERE price < 900) OVER w2 FROM products WINDOW w1 AS (ORDER BY group_id), w2 AS (PARTITION BY group_name ORDER BY price, group_id) ORDER BY group_id ---- NULL 200.00 200.00 200.00 400.00 600.00 200.00 300.00 450.00 1100.00 200.00 366.66666666666666667 600.00 2000.00 200.00 366.66666666666666667 750.00 NULL 200.00 733.33333333333333333 740.00 NULL 450.00 700.00 733.33333333333333333 NULL 533.33333333333333333 700.00 742.85714285714285714 NULL 533.33333333333333333 733.33333333333333333 737.50 NULL 575.00 350.00 737.50 NULL 575.00 150.00 737.50 NULL 500.00 175.00 statement error RANGE with offset PRECEDING/FOLLOWING requires exactly one ORDER BY column SELECT sum(price) OVER (RANGE 100 PRECEDING) FROM products statement error RANGE with offset PRECEDING/FOLLOWING requires exactly one ORDER BY column SELECT sum(price) OVER (ORDER BY price, priceint RANGE 100 PRECEDING) FROM products statement error invalid preceding or following size in window function SELECT sum(price) OVER (ORDER BY pdate RANGE '-1 days' PRECEDING) FROM products statement error invalid preceding or following size in window function SELECT sum(price) OVER (ORDER BY ptime RANGE BETWEEN '-1 hours' PRECEDING AND '1 hours' FOLLOWING) FROM products statement error invalid preceding or following size in window function SELECT sum(price) OVER (ORDER BY ptime RANGE BETWEEN '1 hours' PRECEDING AND '-1 hours' FOLLOWING) FROM products statement error incompatible window frame start type: decimal SELECT sum(price) OVER (ORDER BY ptimestamp RANGE 123.4 PRECEDING) FROM products statement error incompatible window frame start type: int SELECT sum(price) OVER (ORDER BY ptimestamptz RANGE BETWEEN 123 PRECEDING AND CURRENT ROW) FROM products statement error could not parse "1 days" as type decimal SELECT sum(price) OVER (ORDER BY price RANGE BETWEEN 123.4 PRECEDING AND '1 days' FOLLOWING) FROM products statement error RANGE with offset PRECEDING/FOLLOWING is not supported for column type varchar SELECT sum(price) OVER (ORDER BY product_name RANGE 'foo' PRECEDING) FROM products query TTRR SELECT group_name, product_name, price, sum(priceint) OVER (PARTITION BY group_name ORDER BY priceint RANGE 200 PRECEDING) FROM products ORDER BY group_name, priceint, group_id ---- Laptop Lenovo Thinkpad 700.00 1400 Laptop Sony VAIO 700.00 1400 Laptop Dell 800.00 2200 Laptop HP Elite 1200.00 1200 Smartphone Microsoft Lumia 200.00 200 Smartphone HTC One 400.00 600 Smartphone Nexus 500.00 900 Smartphone iPhone 900.00 900 Tablet Kindle Fire 150.00 150 Tablet Samsung 200.00 350 Tablet iPad 700.00 700 query TTRR SELECT group_name, product_name, price, sum(price) OVER (PARTITION BY group_name ORDER BY price RANGE 200.002 PRECEDING) FROM products ORDER BY group_name, price, group_id ---- Laptop Lenovo Thinkpad 700.00 1400.00 Laptop Sony VAIO 700.00 1400.00 Laptop Dell 800.00 2200.00 Laptop HP Elite 1200.00 1200.00 Smartphone Microsoft Lumia 200.00 200.00 Smartphone HTC One 400.00 600.00 Smartphone Nexus 500.00 900.00 Smartphone iPhone 900.00 900.00 Tablet Kindle Fire 150.00 150.00 Tablet Samsung 200.00 350.00 Tablet iPad 700.00 700.00 query TTRR SELECT group_name, product_name, price, sum(pricefloat) OVER (PARTITION BY group_name ORDER BY pricefloat RANGE BETWEEN 200.01 PRECEDING AND 99.99 FOLLOWING) FROM products ORDER BY group_name, priceint, group_id ---- Laptop Lenovo Thinkpad 700.00 1400 Laptop Sony VAIO 700.00 1400 Laptop Dell 800.00 2200 Laptop HP Elite 1200.00 1200 Smartphone Microsoft Lumia 200.00 200 Smartphone HTC One 400.00 600 Smartphone Nexus 500.00 900 Smartphone iPhone 900.00 900 Tablet Kindle Fire 150.00 350 Tablet Samsung 200.00 350 Tablet iPad 700.00 700 query TTRR SELECT group_name, product_name, price, sum(price) OVER (PARTITION BY group_name ORDER BY price RANGE BETWEEN 99.99 PRECEDING AND 100.00 PRECEDING) FROM products ORDER BY group_name, priceint, group_id ---- Laptop Lenovo Thinkpad 700.00 NULL Laptop Sony VAIO 700.00 NULL Laptop Dell 800.00 NULL Laptop HP Elite 1200.00 NULL Smartphone Microsoft Lumia 200.00 NULL Smartphone HTC One 400.00 NULL Smartphone Nexus 500.00 NULL Smartphone iPhone 900.00 NULL Tablet Kindle Fire 150.00 NULL Tablet Samsung 200.00 NULL Tablet iPad 700.00 NULL query TTRR SELECT group_name, product_name, price, sum(priceint) OVER (PARTITION BY group_name ORDER BY priceint RANGE BETWEEN 300 PRECEDING AND 50 PRECEDING) FROM products ORDER BY group_name, priceint, group_id ---- Laptop Lenovo Thinkpad 700.00 NULL Laptop Sony VAIO 700.00 NULL Laptop Dell 800.00 1400 Laptop HP Elite 1200.00 NULL Smartphone Microsoft Lumia 200.00 NULL Smartphone HTC One 400.00 200 Smartphone Nexus 500.00 600 Smartphone iPhone 900.00 NULL Tablet Kindle Fire 150.00 NULL Tablet Samsung 200.00 150 Tablet iPad 700.00 NULL query TTRR SELECT group_name, product_name, price, sum(priceint) OVER (PARTITION BY group_name ORDER BY priceint RANGE BETWEEN 50 FOLLOWING AND 300 FOLLOWING) FROM products ORDER BY group_name, priceint, group_id ---- Laptop Lenovo Thinkpad 700.00 800 Laptop Sony VAIO 700.00 800 Laptop Dell 800.00 NULL Laptop HP Elite 1200.00 NULL Smartphone Microsoft Lumia 200.00 900 Smartphone HTC One 400.00 500 Smartphone Nexus 500.00 NULL Smartphone iPhone 900.00 NULL Tablet Kindle Fire 150.00 200 Tablet Samsung 200.00 NULL Tablet iPad 700.00 NULL query TRR SELECT group_name, price, sum(price) OVER (PARTITION BY group_name ORDER BY price RANGE BETWEEN 49.999 FOLLOWING AND 300.001 FOLLOWING) FROM products ORDER BY group_name, price, group_id ---- Laptop 700.00 800.00 Laptop 700.00 800.00 Laptop 800.00 NULL Laptop 1200.00 NULL Smartphone 200.00 900.00 Smartphone 400.00 500.00 Smartphone 500.00 NULL Smartphone 900.00 NULL Tablet 150.00 200.00 Tablet 200.00 NULL Tablet 700.00 NULL query TRR SELECT group_name, price, sum(pricefloat) OVER (PARTITION BY group_name ORDER BY pricefloat RANGE BETWEEN 50 FOLLOWING AND 300 FOLLOWING) FROM products ORDER BY group_name, price, group_id ---- Laptop 700.00 800 Laptop 700.00 800 Laptop 800.00 NULL Laptop 1200.00 NULL Smartphone 200.00 900 Smartphone 400.00 500 Smartphone 500.00 NULL Smartphone 900.00 NULL Tablet 150.00 200 Tablet 200.00 NULL Tablet 700.00 NULL query TTRR SELECT group_name, product_name, price, nth_value(pricefloat, 2) OVER (PARTITION BY group_name ORDER BY pricefloat RANGE BETWEEN 1.23 FOLLOWING AND 500.23 FOLLOWING) FROM products ORDER BY group_name, pricefloat, group_id ---- Laptop Lenovo Thinkpad 700.00 1200 Laptop Sony VAIO 700.00 1200 Laptop Dell 800.00 NULL Laptop HP Elite 1200.00 NULL Smartphone Microsoft Lumia 200.00 500 Smartphone HTC One 400.00 900 Smartphone Nexus 500.00 NULL Smartphone iPhone 900.00 NULL Tablet Kindle Fire 150.00 NULL Tablet Samsung 200.00 NULL Tablet iPad 700.00 NULL query TTTRR SELECT group_name, product_name, pdate, price, sum(price) OVER (ORDER BY pdate RANGE '1 days' PRECEDING) FROM products ORDER BY pdate, group_id ---- Smartphone Microsoft Lumia 2018-07-30 00:00:00 +0000 +0000 200.00 3500.00 Smartphone Nexus 2018-07-30 00:00:00 +0000 +0000 500.00 3500.00 Laptop HP Elite 2018-07-30 00:00:00 +0000 +0000 1200.00 3500.00 Laptop Sony VAIO 2018-07-30 00:00:00 +0000 +0000 700.00 3500.00 Tablet iPad 2018-07-30 00:00:00 +0000 +0000 700.00 3500.00 Tablet Samsung 2018-07-30 00:00:00 +0000 +0000 200.00 3500.00 Smartphone HTC One 2018-07-31 00:00:00 +0000 +0000 400.00 6450.00 Smartphone iPhone 2018-07-31 00:00:00 +0000 +0000 900.00 6450.00 Laptop Lenovo Thinkpad 2018-07-31 00:00:00 +0000 +0000 700.00 6450.00 Laptop Dell 2018-07-31 00:00:00 +0000 +0000 800.00 6450.00 Tablet Kindle Fire 2018-07-31 00:00:00 +0000 +0000 150.00 6450.00 query TTRR SELECT product_name, ptime, price, avg(price) OVER (ORDER BY ptime RANGE BETWEEN '1 hours 15 minutes' PRECEDING AND '1 hours 15 minutes' FOLLOWING) FROM products ORDER BY ptime, group_id ---- Microsoft Lumia 0000-01-01 01:23:45 +0000 UTC 200.00 700.00 HP Elite 0000-01-01 01:23:45 +0000 UTC 1200.00 700.00 iPad 0000-01-01 01:23:45 +0000 UTC 700.00 700.00 iPhone 0000-01-01 07:34:56 +0000 UTC 900.00 850.00 Dell 0000-01-01 07:34:56 +0000 UTC 800.00 850.00 Nexus 0000-01-01 11:23:45 +0000 UTC 500.00 441.66666666666666667 Sony VAIO 0000-01-01 11:23:45 +0000 UTC 700.00 441.66666666666666667 Samsung 0000-01-01 11:23:45 +0000 UTC 200.00 441.66666666666666667 HTC One 0000-01-01 12:34:56 +0000 UTC 400.00 441.66666666666666667 Lenovo Thinkpad 0000-01-01 12:34:56 +0000 UTC 700.00 441.66666666666666667 Kindle Fire 0000-01-01 12:34:56 +0000 UTC 150.00 441.66666666666666667 query TTTRR SELECT group_name, product_name, ptime, price, min(price) OVER (PARTITION BY group_name ORDER BY ptime RANGE BETWEEN '1 hours' FOLLOWING AND UNBOUNDED FOLLOWING) FROM products ORDER BY group_name, ptime ---- Laptop HP Elite 0000-01-01 01:23:45 +0000 UTC 1200.00 700.00 Laptop Dell 0000-01-01 07:34:56 +0000 UTC 800.00 700.00 Laptop Sony VAIO 0000-01-01 11:23:45 +0000 UTC 700.00 700.00 Laptop Lenovo Thinkpad 0000-01-01 12:34:56 +0000 UTC 700.00 NULL Smartphone Microsoft Lumia 0000-01-01 01:23:45 +0000 UTC 200.00 400.00 Smartphone iPhone 0000-01-01 07:34:56 +0000 UTC 900.00 400.00 Smartphone Nexus 0000-01-01 11:23:45 +0000 UTC 500.00 400.00 Smartphone HTC One 0000-01-01 12:34:56 +0000 UTC 400.00 NULL Tablet iPad 0000-01-01 01:23:45 +0000 UTC 700.00 150.00 Tablet Samsung 0000-01-01 11:23:45 +0000 UTC 200.00 150.00 Tablet Kindle Fire 0000-01-01 12:34:56 +0000 UTC 150.00 NULL query TTTRR SELECT group_name, product_name, ptimestamp, price, first_value(price) OVER (PARTITION BY group_name ORDER BY ptimestamp RANGE BETWEEN '12 hours' PRECEDING AND '6 hours' FOLLOWING) FROM products ORDER BY group_name, ptimestamp ---- Laptop HP Elite 2018-07-30 01:23:45 +0000 +0000 1200.00 1200.00 Laptop Sony VAIO 2018-07-30 11:23:45 +0000 +0000 700.00 1200.00 Laptop Dell 2018-07-31 07:34:56 +0000 +0000 800.00 800.00 Laptop Lenovo Thinkpad 2018-07-31 12:34:56 +0000 +0000 700.00 800.00 Smartphone Microsoft Lumia 2018-07-30 01:23:45 +0000 +0000 200.00 200.00 Smartphone Nexus 2018-07-30 11:23:45 +0000 +0000 500.00 200.00 Smartphone iPhone 2018-07-31 07:34:56 +0000 +0000 900.00 900.00 Smartphone HTC One 2018-07-31 12:34:56 +0000 +0000 400.00 900.00 Tablet iPad 2018-07-30 01:23:45 +0000 +0000 700.00 700.00 Tablet Samsung 2018-07-30 11:23:45 +0000 +0000 200.00 700.00 Tablet Kindle Fire 2018-07-31 12:34:56 +0000 +0000 150.00 150.00 query TTTRR SELECT group_name, product_name, ptimestamptz, price, avg(price) OVER (PARTITION BY group_name ORDER BY ptimestamptz RANGE BETWEEN '1 days 12 hours' PRECEDING AND CURRENT ROW) FROM products ORDER BY group_name, ptimestamptz ---- Laptop HP Elite 2018-07-30 01:23:45 +0000 UTC 1200.00 1200.00 Laptop Sony VAIO 2018-07-30 11:23:45 +0000 UTC 700.00 950.00 Laptop Dell 2018-07-31 07:34:56 +0000 UTC 800.00 900.00 Laptop Lenovo Thinkpad 2018-07-31 12:34:56 +0000 UTC 700.00 850.00 Smartphone Microsoft Lumia 2018-07-30 01:23:45 +0000 UTC 200.00 200.00 Smartphone Nexus 2018-07-30 11:23:45 +0000 UTC 500.00 350.00 Smartphone iPhone 2018-07-31 07:34:56 +0000 UTC 900.00 533.33333333333333333 Smartphone HTC One 2018-07-31 12:34:56 +0000 UTC 400.00 500.00 Tablet iPad 2018-07-30 01:23:45 +0000 UTC 700.00 700.00 Tablet Samsung 2018-07-30 11:23:45 +0000 UTC 200.00 450.00 Tablet Kindle Fire 2018-07-31 12:34:56 +0000 UTC 150.00 350.00 query TTRR SELECT product_name, pinterval, price, avg(price) OVER (ORDER BY pinterval RANGE BETWEEN '2 hours 34 minutes 56 seconds' PRECEDING AND '3 months' FOLLOWING) FROM products ORDER BY pinterval, group_id ---- iPhone 00:01:02 900.00 586.36363636363636364 Dell 00:01:02 800.00 586.36363636363636364 Nexus 01:02:03 500.00 586.36363636363636364 Sony VAIO 01:02:03 700.00 586.36363636363636364 Samsung 01:02:03 200.00 586.36363636363636364 HTC One 1 day 02:03:04 400.00 558.33333333333333333 Lenovo Thinkpad 1 day 02:03:04 700.00 558.33333333333333333 Kindle Fire 1 day 02:03:04 150.00 558.33333333333333333 Microsoft Lumia 1 mon 2 days 03:04:05 200.00 700.00 HP Elite 1 mon 2 days 03:04:05 1200.00 700.00 iPad 1 mon 2 days 03:04:05 700.00 700.00 query TTRR SELECT group_name, product_name, price, sum(priceint) OVER (PARTITION BY group_name ORDER BY priceint DESC RANGE 200 PRECEDING) FROM products ORDER BY group_name, priceint DESC, group_id ---- Laptop HP Elite 1200.00 1200 Laptop Dell 800.00 800 Laptop Lenovo Thinkpad 700.00 2200 Laptop Sony VAIO 700.00 2200 Smartphone iPhone 900.00 900 Smartphone Nexus 500.00 500 Smartphone HTC One 400.00 900 Smartphone Microsoft Lumia 200.00 600 Tablet iPad 700.00 700 Tablet Samsung 200.00 200 Tablet Kindle Fire 150.00 350 query TTRR SELECT group_name, product_name, price, sum(price) OVER (PARTITION BY group_name ORDER BY price DESC RANGE 200.002 PRECEDING) FROM products ORDER BY group_name, price DESC, group_id ---- Laptop HP Elite 1200.00 1200.00 Laptop Dell 800.00 800.00 Laptop Lenovo Thinkpad 700.00 2200.00 Laptop Sony VAIO 700.00 2200.00 Smartphone iPhone 900.00 900.00 Smartphone Nexus 500.00 500.00 Smartphone HTC One 400.00 900.00 Smartphone Microsoft Lumia 200.00 600.00 Tablet iPad 700.00 700.00 Tablet Samsung 200.00 200.00 Tablet Kindle Fire 150.00 350.00 query TTRR SELECT group_name, product_name, price, sum(pricefloat) OVER (PARTITION BY group_name ORDER BY pricefloat DESC RANGE BETWEEN 200.01 PRECEDING AND 99.99 FOLLOWING) FROM products ORDER BY group_name, priceint DESC, group_id ---- Laptop HP Elite 1200.00 1200 Laptop Dell 800.00 800 Laptop Lenovo Thinkpad 700.00 2200 Laptop Sony VAIO 700.00 2200 Smartphone iPhone 900.00 900 Smartphone Nexus 500.00 500 Smartphone HTC One 400.00 900 Smartphone Microsoft Lumia 200.00 600 Tablet iPad 700.00 700 Tablet Samsung 200.00 350 Tablet Kindle Fire 150.00 350 query TTRR SELECT group_name, product_name, price, sum(price) OVER (PARTITION BY group_name ORDER BY price DESC RANGE BETWEEN 99.99 PRECEDING AND 100.00 PRECEDING) FROM products ORDER BY group_name, priceint DESC, group_id ---- Laptop HP Elite 1200.00 NULL Laptop Dell 800.00 NULL Laptop Lenovo Thinkpad 700.00 NULL Laptop Sony VAIO 700.00 NULL Smartphone iPhone 900.00 NULL Smartphone Nexus 500.00 NULL Smartphone HTC One 400.00 NULL Smartphone Microsoft Lumia 200.00 NULL Tablet iPad 700.00 NULL Tablet Samsung 200.00 NULL Tablet Kindle Fire 150.00 NULL query TTRR SELECT group_name, product_name, price, sum(priceint) OVER (PARTITION BY group_name ORDER BY priceint DESC RANGE BETWEEN 300 PRECEDING AND 50 PRECEDING) FROM products ORDER BY group_name, priceint DESC, group_id ---- Laptop HP Elite 1200.00 NULL Laptop Dell 800.00 NULL Laptop Lenovo Thinkpad 700.00 800 Laptop Sony VAIO 700.00 800 Smartphone iPhone 900.00 NULL Smartphone Nexus 500.00 NULL Smartphone HTC One 400.00 500 Smartphone Microsoft Lumia 200.00 900 Tablet iPad 700.00 NULL Tablet Samsung 200.00 NULL Tablet Kindle Fire 150.00 200 query TTRR SELECT group_name, product_name, price, sum(priceint) OVER (PARTITION BY group_name ORDER BY priceint DESC RANGE BETWEEN 50 FOLLOWING AND 300 FOLLOWING) FROM products ORDER BY group_name, priceint DESC, group_id ---- Laptop HP Elite 1200.00 NULL Laptop Dell 800.00 1400 Laptop Lenovo Thinkpad 700.00 NULL Laptop Sony VAIO 700.00 NULL Smartphone iPhone 900.00 NULL Smartphone Nexus 500.00 600 Smartphone HTC One 400.00 200 Smartphone Microsoft Lumia 200.00 NULL Tablet iPad 700.00 NULL Tablet Samsung 200.00 150 Tablet Kindle Fire 150.00 NULL query TRR SELECT group_name, price, sum(price) OVER (PARTITION BY group_name ORDER BY price DESC RANGE BETWEEN 49.999 FOLLOWING AND 300.001 FOLLOWING) FROM products ORDER BY group_name, price DESC, group_id ---- Laptop 1200.00 NULL Laptop 800.00 1400.00 Laptop 700.00 NULL Laptop 700.00 NULL Smartphone 900.00 NULL Smartphone 500.00 600.00 Smartphone 400.00 200.00 Smartphone 200.00 NULL Tablet 700.00 NULL Tablet 200.00 150.00 Tablet 150.00 NULL query TRR SELECT group_name, price, sum(pricefloat) OVER (PARTITION BY group_name ORDER BY pricefloat DESC RANGE BETWEEN 50 FOLLOWING AND 300 FOLLOWING) FROM products ORDER BY group_name, price DESC, group_id ---- Laptop 1200.00 NULL Laptop 800.00 1400 Laptop 700.00 NULL Laptop 700.00 NULL Smartphone 900.00 NULL Smartphone 500.00 600 Smartphone 400.00 200 Smartphone 200.00 NULL Tablet 700.00 NULL Tablet 200.00 150 Tablet 150.00 NULL query TTRR SELECT group_name, product_name, price, nth_value(pricefloat, 2) OVER (PARTITION BY group_name ORDER BY pricefloat DESC RANGE BETWEEN 1.23 FOLLOWING AND 500.23 FOLLOWING) FROM products ORDER BY group_name, pricefloat DESC, group_id ---- Laptop HP Elite 1200.00 700 Laptop Dell 800.00 700 Laptop Lenovo Thinkpad 700.00 NULL Laptop Sony VAIO 700.00 NULL Smartphone iPhone 900.00 400 Smartphone Nexus 500.00 200 Smartphone HTC One 400.00 NULL Smartphone Microsoft Lumia 200.00 NULL Tablet iPad 700.00 NULL Tablet Samsung 200.00 NULL Tablet Kindle Fire 150.00 NULL query TTTRR SELECT group_name, product_name, pdate, price, sum(price) OVER (ORDER BY pdate DESC RANGE '1 days' PRECEDING) FROM products ORDER BY pdate DESC, group_id ---- Smartphone HTC One 2018-07-31 00:00:00 +0000 +0000 400.00 2950.00 Smartphone iPhone 2018-07-31 00:00:00 +0000 +0000 900.00 2950.00 Laptop Lenovo Thinkpad 2018-07-31 00:00:00 +0000 +0000 700.00 2950.00 Laptop Dell 2018-07-31 00:00:00 +0000 +0000 800.00 2950.00 Tablet Kindle Fire 2018-07-31 00:00:00 +0000 +0000 150.00 2950.00 Smartphone Microsoft Lumia 2018-07-30 00:00:00 +0000 +0000 200.00 6450.00 Smartphone Nexus 2018-07-30 00:00:00 +0000 +0000 500.00 6450.00 Laptop HP Elite 2018-07-30 00:00:00 +0000 +0000 1200.00 6450.00 Laptop Sony VAIO 2018-07-30 00:00:00 +0000 +0000 700.00 6450.00 Tablet iPad 2018-07-30 00:00:00 +0000 +0000 700.00 6450.00 Tablet Samsung 2018-07-30 00:00:00 +0000 +0000 200.00 6450.00 query TTRR SELECT product_name, ptime, price, avg(price) OVER (ORDER BY ptime DESC RANGE BETWEEN '1 hours 15 minutes' PRECEDING AND '1 hours 15 minutes' FOLLOWING) FROM products ORDER BY ptime DESC, group_id ---- HTC One 0000-01-01 12:34:56 +0000 UTC 400.00 441.66666666666666667 Lenovo Thinkpad 0000-01-01 12:34:56 +0000 UTC 700.00 441.66666666666666667 Kindle Fire 0000-01-01 12:34:56 +0000 UTC 150.00 441.66666666666666667 Nexus 0000-01-01 11:23:45 +0000 UTC 500.00 441.66666666666666667 Sony VAIO 0000-01-01 11:23:45 +0000 UTC 700.00 441.66666666666666667 Samsung 0000-01-01 11:23:45 +0000 UTC 200.00 441.66666666666666667 iPhone 0000-01-01 07:34:56 +0000 UTC 900.00 850.00 Dell 0000-01-01 07:34:56 +0000 UTC 800.00 850.00 Microsoft Lumia 0000-01-01 01:23:45 +0000 UTC 200.00 700.00 HP Elite 0000-01-01 01:23:45 +0000 UTC 1200.00 700.00 iPad 0000-01-01 01:23:45 +0000 UTC 700.00 700.00 query TTTRR SELECT group_name, product_name, ptime, price, min(price) OVER (PARTITION BY group_name ORDER BY ptime DESC RANGE BETWEEN '1 hours' FOLLOWING AND UNBOUNDED FOLLOWING) FROM products ORDER BY group_name, ptime DESC ---- Laptop Lenovo Thinkpad 0000-01-01 12:34:56 +0000 UTC 700.00 700.00 Laptop Sony VAIO 0000-01-01 11:23:45 +0000 UTC 700.00 800.00 Laptop Dell 0000-01-01 07:34:56 +0000 UTC 800.00 1200.00 Laptop HP Elite 0000-01-01 01:23:45 +0000 UTC 1200.00 NULL Smartphone HTC One 0000-01-01 12:34:56 +0000 UTC 400.00 200.00 Smartphone Nexus 0000-01-01 11:23:45 +0000 UTC 500.00 200.00 Smartphone iPhone 0000-01-01 07:34:56 +0000 UTC 900.00 200.00 Smartphone Microsoft Lumia 0000-01-01 01:23:45 +0000 UTC 200.00 NULL Tablet Kindle Fire 0000-01-01 12:34:56 +0000 UTC 150.00 200.00 Tablet Samsung 0000-01-01 11:23:45 +0000 UTC 200.00 700.00 Tablet iPad 0000-01-01 01:23:45 +0000 UTC 700.00 NULL query TTTRR SELECT group_name, product_name, ptimestamp, price, first_value(price) OVER (PARTITION BY group_name ORDER BY ptimestamp DESC RANGE BETWEEN '12 hours' PRECEDING AND '6 hours' FOLLOWING) FROM products ORDER BY group_name, ptimestamp DESC ---- Laptop Lenovo Thinkpad 2018-07-31 12:34:56 +0000 +0000 700.00 700.00 Laptop Dell 2018-07-31 07:34:56 +0000 +0000 800.00 700.00 Laptop Sony VAIO 2018-07-30 11:23:45 +0000 +0000 700.00 700.00 Laptop HP Elite 2018-07-30 01:23:45 +0000 +0000 1200.00 700.00 Smartphone HTC One 2018-07-31 12:34:56 +0000 +0000 400.00 400.00 Smartphone iPhone 2018-07-31 07:34:56 +0000 +0000 900.00 400.00 Smartphone Nexus 2018-07-30 11:23:45 +0000 +0000 500.00 500.00 Smartphone Microsoft Lumia 2018-07-30 01:23:45 +0000 +0000 200.00 500.00 Tablet Kindle Fire 2018-07-31 12:34:56 +0000 +0000 150.00 150.00 Tablet Samsung 2018-07-30 11:23:45 +0000 +0000 200.00 200.00 Tablet iPad 2018-07-30 01:23:45 +0000 +0000 700.00 200.00 query TTTRR SELECT group_name, product_name, ptimestamptz, price, avg(price) OVER (PARTITION BY group_name ORDER BY ptimestamptz DESC RANGE BETWEEN '1 days 12 hours' PRECEDING AND CURRENT ROW) FROM products ORDER BY group_name, ptimestamptz DESC ---- Laptop Lenovo Thinkpad 2018-07-31 12:34:56 +0000 UTC 700.00 700.00 Laptop Dell 2018-07-31 07:34:56 +0000 UTC 800.00 750.00 Laptop Sony VAIO 2018-07-30 11:23:45 +0000 UTC 700.00 733.33333333333333333 Laptop HP Elite 2018-07-30 01:23:45 +0000 UTC 1200.00 850.00 Smartphone HTC One 2018-07-31 12:34:56 +0000 UTC 400.00 400.00 Smartphone iPhone 2018-07-31 07:34:56 +0000 UTC 900.00 650.00 Smartphone Nexus 2018-07-30 11:23:45 +0000 UTC 500.00 600.00 Smartphone Microsoft Lumia 2018-07-30 01:23:45 +0000 UTC 200.00 500.00 Tablet Kindle Fire 2018-07-31 12:34:56 +0000 UTC 150.00 150.00 Tablet Samsung 2018-07-30 11:23:45 +0000 UTC 200.00 175.00 Tablet iPad 2018-07-30 01:23:45 +0000 UTC 700.00 350.00 query TTRR SELECT product_name, pinterval, price, avg(price) OVER (ORDER BY pinterval DESC RANGE BETWEEN '2 hours 34 minutes 56 seconds' PRECEDING AND '3 months' FOLLOWING) FROM products ORDER BY pinterval DESC, group_id ---- Microsoft Lumia 1 mon 2 days 03:04:05 200.00 586.36363636363636364 HP Elite 1 mon 2 days 03:04:05 1200.00 586.36363636363636364 iPad 1 mon 2 days 03:04:05 700.00 586.36363636363636364 HTC One 1 day 02:03:04 400.00 543.75 Lenovo Thinkpad 1 day 02:03:04 700.00 543.75 Kindle Fire 1 day 02:03:04 150.00 543.75 Nexus 01:02:03 500.00 620.00 Sony VAIO 01:02:03 700.00 620.00 Samsung 01:02:03 200.00 620.00 iPhone 00:01:02 900.00 620.00 Dell 00:01:02 800.00 620.00 query TRTT SELECT group_name, price, product_name, array_agg(product_name) OVER (PARTITION BY group_name ORDER BY price, group_id) FROM products ORDER BY group_id ---- Smartphone 200.00 Microsoft Lumia {"Microsoft Lumia"} Smartphone 400.00 HTC One {"Microsoft Lumia","HTC One"} Smartphone 500.00 Nexus {"Microsoft Lumia","HTC One",Nexus} Smartphone 900.00 iPhone {"Microsoft Lumia","HTC One",Nexus,iPhone} Laptop 1200.00 HP Elite {"Lenovo Thinkpad","Sony VAIO",Dell,"HP Elite"} Laptop 700.00 Lenovo Thinkpad {"Lenovo Thinkpad"} Laptop 700.00 Sony VAIO {"Lenovo Thinkpad","Sony VAIO"} Laptop 800.00 Dell {"Lenovo Thinkpad","Sony VAIO",Dell} Tablet 700.00 iPad {"Kindle Fire",Samsung,iPad} Tablet 150.00 Kindle Fire {"Kindle Fire"} Tablet 200.00 Samsung {"Kindle Fire",Samsung} query TT SELECT product_name, array_agg(product_name) OVER (ORDER BY group_id) FROM products ORDER BY group_id ---- Microsoft Lumia {"Microsoft Lumia"} HTC One {"Microsoft Lumia","HTC One"} Nexus {"Microsoft Lumia","HTC One",Nexus} iPhone {"Microsoft Lumia","HTC One",Nexus,iPhone} HP Elite {"Microsoft Lumia","HTC One",Nexus,iPhone,"HP Elite"} Lenovo Thinkpad {"Microsoft Lumia","HTC One",Nexus,iPhone,"HP Elite","Lenovo Thinkpad"} Sony VAIO {"Microsoft Lumia","HTC One",Nexus,iPhone,"HP Elite","Lenovo Thinkpad","Sony VAIO"} Dell {"Microsoft Lumia","HTC One",Nexus,iPhone,"HP Elite","Lenovo Thinkpad","Sony VAIO",Dell} iPad {"Microsoft Lumia","HTC One",Nexus,iPhone,"HP Elite","Lenovo Thinkpad","Sony VAIO",Dell,iPad} Kindle Fire {"Microsoft Lumia","HTC One",Nexus,iPhone,"HP Elite","Lenovo Thinkpad","Sony VAIO",Dell,iPad,"Kindle Fire"} Samsung {"Microsoft Lumia","HTC One",Nexus,iPhone,"HP Elite","Lenovo Thinkpad","Sony VAIO",Dell,iPad,"Kindle Fire",Samsung} statement error frame starting offset must not be null SELECT avg(price) OVER (GROUPS NULL PRECEDING) FROM products statement error frame starting offset must not be null SELECT avg(price) OVER (GROUPS BETWEEN NULL PRECEDING AND 1 FOLLOWING) FROM products statement error frame starting offset must not be negative SELECT price, avg(price) OVER (PARTITION BY price GROUPS -1 PRECEDING) AS avg_price FROM products statement error frame starting offset must not be negative SELECT price, avg(price) OVER w AS avg_price FROM products WINDOW w AS (PARTITION BY price GROUPS -1 PRECEDING) statement error frame ending offset must not be null SELECT avg(price) OVER (GROUPS BETWEEN 1 PRECEDING AND NULL FOLLOWING) FROM products statement error frame ending offset must not be negative SELECT price, avg(price) OVER (PARTITION BY price GROUPS BETWEEN 1 FOLLOWING AND -1 FOLLOWING) AS avg_price FROM products statement error frame ending offset must not be negative SELECT price, avg(price) OVER w AS avg_price FROM products WINDOW w AS (PARTITION BY price GROUPS BETWEEN 1 FOLLOWING AND -1 FOLLOWING) statement error frame ending offset must not be negative SELECT product_name, price, min(price) OVER (PARTITION BY group_name GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS min_over_three, max(price) OVER (PARTITION BY group_name GROUPS BETWEEN UNBOUNDED PRECEDING AND -1 FOLLOWING) AS max_over_partition FROM products ORDER BY group_id statement error incompatible window frame start type: decimal SELECT avg(price) OVER (PARTITION BY group_name GROUPS 1.5 PRECEDING) AS avg_price FROM products statement error incompatible window frame start type: decimal SELECT avg(price) OVER w AS avg_price FROM products WINDOW w AS (PARTITION BY group_name GROUPS 1.5 PRECEDING) statement error incompatible window frame start type: decimal SELECT avg(price) OVER (PARTITION BY group_name GROUPS BETWEEN 1.5 PRECEDING AND UNBOUNDED FOLLOWING) AS avg_price FROM products statement error incompatible window frame start type: decimal SELECT avg(price) OVER w AS avg_price FROM products WINDOW w AS (PARTITION BY group_name GROUPS BETWEEN 1.5 PRECEDING AND UNBOUNDED FOLLOWING) statement error incompatible window frame end type: decimal SELECT avg(price) OVER (PARTITION BY group_name GROUPS BETWEEN UNBOUNDED PRECEDING AND 1.5 FOLLOWING) AS avg_price FROM products statement error incompatible window frame end type: decimal SELECT avg(price) OVER w AS avg_price FROM products WINDOW w AS (PARTITION BY group_name GROUPS BETWEEN UNBOUNDED PRECEDING AND 1.5 FOLLOWING) query RRRRR SELECT price, sum(price) OVER (ORDER BY price GROUPS UNBOUNDED PRECEDING), sum(price) OVER (ORDER BY price GROUPS 100 PRECEDING), sum(price) OVER (ORDER BY price GROUPS 1 PRECEDING), sum(price) OVER (ORDER BY group_name GROUPS CURRENT ROW) FROM products ORDER BY price, group_id ---- 150.00 150.00 150.00 150.00 1050.00 200.00 550.00 550.00 550.00 2000.00 200.00 550.00 550.00 550.00 1050.00 400.00 950.00 950.00 800.00 2000.00 500.00 1450.00 1450.00 900.00 2000.00 700.00 3550.00 3550.00 2600.00 3400.00 700.00 3550.00 3550.00 2600.00 3400.00 700.00 3550.00 3550.00 2600.00 1050.00 800.00 4350.00 4350.00 2900.00 3400.00 900.00 5250.00 5250.00 1700.00 2000.00 1200.00 6450.00 6450.00 2100.00 3400.00 query RIRRRRRR SELECT price, dense_rank() OVER w, avg(price) OVER (w GROUPS BETWEEN UNBOUNDED PRECEDING AND 100 PRECEDING), avg(price) OVER (w GROUPS BETWEEN UNBOUNDED PRECEDING AND 2 PRECEDING), avg(price) OVER (w GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), avg(price) OVER (w GROUPS BETWEEN UNBOUNDED PRECEDING AND 2 FOLLOWING), avg(price) OVER (w GROUPS BETWEEN UNBOUNDED PRECEDING AND 100 FOLLOWING), avg(price) OVER (w GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM products WINDOW w AS (ORDER BY price) ORDER BY price ---- 150.00 1 NULL NULL 150.00 237.50 586.36363636363636364 586.36363636363636364 200.00 2 NULL NULL 183.33333333333333333 290.00 586.36363636363636364 586.36363636363636364 200.00 2 NULL NULL 183.33333333333333333 290.00 586.36363636363636364 586.36363636363636364 400.00 3 NULL 150.00 237.50 443.75 586.36363636363636364 586.36363636363636364 500.00 4 NULL 183.33333333333333333 290.00 483.33333333333333333 586.36363636363636364 586.36363636363636364 700.00 5 NULL 237.50 443.75 525.00 586.36363636363636364 586.36363636363636364 700.00 5 NULL 237.50 443.75 525.00 586.36363636363636364 586.36363636363636364 700.00 5 NULL 237.50 443.75 525.00 586.36363636363636364 586.36363636363636364 800.00 6 NULL 290.00 483.33333333333333333 586.36363636363636364 586.36363636363636364 586.36363636363636364 900.00 7 NULL 443.75 525.00 586.36363636363636364 586.36363636363636364 586.36363636363636364 1200.00 8 NULL 483.33333333333333333 586.36363636363636364 586.36363636363636364 586.36363636363636364 586.36363636363636364 query RIRRRRRR SELECT price, dense_rank() OVER w, avg(price) OVER (w GROUPS BETWEEN 4 PRECEDING AND 100 PRECEDING), avg(price) OVER (w GROUPS BETWEEN 3 PRECEDING AND 2 PRECEDING), avg(price) OVER (w GROUPS BETWEEN 2 PRECEDING AND CURRENT ROW), avg(price) OVER (w GROUPS BETWEEN 1 PRECEDING AND 2 FOLLOWING), avg(price) OVER (w GROUPS BETWEEN 1 PRECEDING AND 100 FOLLOWING), avg(price) OVER (w GROUPS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING) FROM products WINDOW w AS (ORDER BY price) ORDER BY price ---- 150.00 1 NULL NULL 150.00 237.50 586.36363636363636364 586.36363636363636364 200.00 2 NULL NULL 183.33333333333333333 290.00 586.36363636363636364 586.36363636363636364 200.00 2 NULL NULL 183.33333333333333333 290.00 586.36363636363636364 586.36363636363636364 400.00 3 NULL 150.00 237.50 485.71428571428571429 630.00 630.00 500.00 4 NULL 183.33333333333333333 325.00 633.33333333333333333 737.50 737.50 700.00 5 NULL 266.66666666666666667 600.00 716.66666666666666667 785.71428571428571429 785.71428571428571429 700.00 5 NULL 266.66666666666666667 600.00 716.66666666666666667 785.71428571428571429 785.71428571428571429 700.00 5 NULL 266.66666666666666667 600.00 716.66666666666666667 785.71428571428571429 785.71428571428571429 800.00 6 NULL 450.00 680.00 833.33333333333333333 833.33333333333333333 833.33333333333333333 900.00 7 NULL 650.00 760.00 966.66666666666666667 966.66666666666666667 966.66666666666666667 1200.00 8 NULL 725.00 966.66666666666666667 1050.00 1050.00 1050.00 query RIRRRRRRR SELECT price, dense_rank() OVER w, avg(price) OVER (w GROUPS BETWEEN 0 PRECEDING AND 0 PRECEDING), avg(price) OVER (w GROUPS BETWEEN 0 PRECEDING AND CURRENT ROW), avg(price) OVER (w GROUPS BETWEEN 0 PRECEDING AND 0 FOLLOWING), avg(price) OVER (w GROUPS BETWEEN CURRENT ROW AND CURRENT ROW), avg(price) OVER (w GROUPS BETWEEN CURRENT ROW AND 2 FOLLOWING), avg(price) OVER (w GROUPS BETWEEN CURRENT ROW AND 100 FOLLOWING), avg(price) OVER (w GROUPS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM products WINDOW w AS (ORDER BY price) ORDER BY price ---- 150.00 1 150.00 150.00 150.00 150.00 237.50 586.36363636363636364 586.36363636363636364 200.00 2 200.00 200.00 200.00 200.00 325.00 630.00 630.00 200.00 2 200.00 200.00 200.00 200.00 325.00 630.00 630.00 400.00 3 400.00 400.00 400.00 400.00 600.00 737.50 737.50 500.00 4 500.00 500.00 500.00 500.00 680.00 785.71428571428571429 785.71428571428571429 700.00 5 700.00 700.00 700.00 700.00 760.00 833.33333333333333333 833.33333333333333333 700.00 5 700.00 700.00 700.00 700.00 760.00 833.33333333333333333 833.33333333333333333 700.00 5 700.00 700.00 700.00 700.00 760.00 833.33333333333333333 833.33333333333333333 800.00 6 800.00 800.00 800.00 800.00 966.66666666666666667 966.66666666666666667 966.66666666666666667 900.00 7 900.00 900.00 900.00 900.00 1050.00 1050.00 1050.00 1200.00 8 1200.00 1200.00 1200.00 1200.00 1200.00 1200.00 1200.00 query RIRRRRRR SELECT price, dense_rank() OVER w, avg(price) OVER (w GROUPS BETWEEN 3 FOLLOWING AND 100 FOLLOWING), avg(price) OVER (w GROUPS BETWEEN 3 FOLLOWING AND 1 FOLLOWING), avg(price) OVER (w GROUPS BETWEEN 2 FOLLOWING AND 6 FOLLOWING), avg(price) OVER (w GROUPS BETWEEN 3 FOLLOWING AND 3 FOLLOWING), avg(price) OVER (w GROUPS BETWEEN 0 FOLLOWING AND 4 FOLLOWING), avg(price) OVER (w GROUPS BETWEEN 5 FOLLOWING AND UNBOUNDED FOLLOWING) FROM products WINDOW w AS (ORDER BY price) ORDER BY price ---- 150.00 1 785.71428571428571429 NULL 671.42857142857142857 500.00 443.75 966.66666666666666667 200.00 2 833.33333333333333333 NULL 785.71428571428571429 700.00 525.00 1050.00 200.00 2 833.33333333333333333 NULL 785.71428571428571429 700.00 525.00 1050.00 400.00 3 966.66666666666666667 NULL 833.33333333333333333 800.00 671.42857142857142857 1200.00 500.00 4 1050.00 NULL 966.66666666666666667 900.00 785.71428571428571429 NULL 700.00 5 1200.00 NULL 1050.00 1200.00 833.33333333333333333 NULL 700.00 5 1200.00 NULL 1050.00 1200.00 833.33333333333333333 NULL 700.00 5 1200.00 NULL 1050.00 1200.00 833.33333333333333333 NULL 800.00 6 NULL NULL 1200.00 NULL 966.66666666666666667 NULL 900.00 7 NULL NULL NULL NULL 1050.00 NULL 1200.00 8 NULL NULL NULL NULL 1200.00 NULL query TTRRR SELECT group_name, product_name, price, avg(price) OVER (PARTITION BY group_name ORDER BY price GROUPS BETWEEN CURRENT ROW AND 3 FOLLOWING), avg(price) OVER (ORDER BY price GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM products ORDER BY group_id ---- Smartphone Microsoft Lumia 200.00 500.00 586.36363636363636364 Smartphone HTC One 400.00 600.00 586.36363636363636364 Smartphone Nexus 500.00 700.00 586.36363636363636364 Smartphone iPhone 900.00 900.00 586.36363636363636364 Laptop HP Elite 1200.00 1200.00 586.36363636363636364 Laptop Lenovo Thinkpad 700.00 850.00 586.36363636363636364 Laptop Sony VAIO 700.00 850.00 586.36363636363636364 Laptop Dell 800.00 1000.00 586.36363636363636364 Tablet iPad 700.00 700.00 586.36363636363636364 Tablet Kindle Fire 150.00 350.00 586.36363636363636364 Tablet Samsung 200.00 450.00 586.36363636363636364 query TTRRR SELECT group_name, product_name, price, avg(price) OVER (GROUPS BETWEEN 1 PRECEDING AND 2 PRECEDING), avg(price) OVER (ORDER BY price GROUPS BETWEEN CURRENT ROW AND CURRENT ROW) FROM products ORDER BY group_id ---- Smartphone Microsoft Lumia 200.00 NULL 200.00 Smartphone HTC One 400.00 NULL 400.00 Smartphone Nexus 500.00 NULL 500.00 Smartphone iPhone 900.00 NULL 900.00 Laptop HP Elite 1200.00 NULL 1200.00 Laptop Lenovo Thinkpad 700.00 NULL 700.00 Laptop Sony VAIO 700.00 NULL 700.00 Laptop Dell 800.00 NULL 800.00 Tablet iPad 700.00 NULL 700.00 Tablet Kindle Fire 150.00 NULL 150.00 Tablet Samsung 200.00 NULL 200.00 # Test for cockroach#32702 statement ok CREATE TABLE x (a INT) statement ok INSERT INTO x VALUES (1), (2), (3) query IT SELECT a, json_agg(a) OVER (ORDER BY a) FROM x ORDER BY a ---- 1 [1] 2 [1, 2] 3 [1, 2, 3] # Test for cockroach#35267 query I SELECT row_number() OVER (PARTITION BY s) FROM (SELECT sum(a) AS s FROM (SELECT a FROM x UNION ALL SELECT a FROM x) GROUP BY a) ---- 1 1 1 # Tests for cockroach#32050 statement error window function calls cannot be nested SELECT sum(a) OVER (PARTITION BY count(a) OVER ()) FROM x statement error window function calls cannot be nested SELECT sum(a) OVER (ORDER BY count(a) OVER ()) FROM x statement error window function calls cannot be nested SELECT sum(a) OVER (PARTITION BY count(a) OVER () + 1) FROM x statement error window function calls cannot be nested SELECT sum(a) OVER (ORDER BY count(a) OVER () + 1) FROM x # TODO(justin): blocked by cockroach#37134. # statement error more than one row returned by a subquery used as an expression # SELECT sum(a) OVER (PARTITION BY (SELECT count(*) FROM x GROUP BY a)) FROM x query I SELECT sum(a) OVER (PARTITION BY (SELECT count(*) FROM x GROUP BY a LIMIT 1))::INT FROM x ---- 6 6 6 # Regression test for materialize#27293 - make sure comparing two tuple types when # generating window functions expressions doesn't panic. query II SELECT min(a) OVER (PARTITION BY (a, a)) AS min, max(a) OVER (PARTITION BY (a, a)) AS max FROM (SELECT 1 AS a) ---- 1 1 query II SELECT min(a) OVER (PARTITION BY (())) AS min, max(a) OVER (PARTITION BY (())) AS max FROM (SELECT 1 AS a) ---- 1 1 query T SELECT string_agg('foo', s) OVER () FROM (SELECT * FROM kv LIMIT 1) ---- foo # Regression test for cockroach#37201. query I SELECT jsonb_agg(a) OVER (ORDER BY a GROUPS BETWEEN 5 FOLLOWING AND UNBOUNDED FOLLOWING) FROM x ---- NULL NULL NULL statement ok CREATE TABLE abc (a INT PRIMARY KEY, b INT, c INT) statement ok INSERT INTO abc VALUES (1, 10, 20), (2, 10, 20), (3, 10, 20), (4, 10, 30), (5, 10, 30), (6, 10, 30) query TTTTTTTTTTTT rowsort SELECT avg(a) OVER (), avg(a) OVER (ORDER BY a), avg(a) OVER (ORDER BY b), avg(a) OVER (ORDER BY c), avg(b) OVER (), avg(b) OVER (ORDER BY a), avg(b) OVER (ORDER BY b), avg(b) OVER (ORDER BY c), avg(c) OVER (), avg(c) OVER (ORDER BY a), avg(c) OVER (ORDER BY b), avg(c) OVER (ORDER BY c) FROM abc ---- 3.5 1 3.5 2 10 10 10 10 25 20 25 20 3.5 1.5 3.5 2 10 10 10 10 25 20 25 20 3.5 2 3.5 2 10 10 10 10 25 20 25 20 3.5 2.5 3.5 3.5 10 10 10 10 25 22.5 25 25 3.5 3 3.5 3.5 10 10 10 10 25 24 25 25 3.5 3.5 3.5 3.5 10 10 10 10 25 25 25 25 query TTTTTTTTTTTT rowsort SELECT avg(a) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), avg(a) OVER (ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), avg(a) OVER (ORDER BY b RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), avg(a) OVER (ORDER BY c RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), avg(b) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), avg(b) OVER (ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), avg(b) OVER (ORDER BY b RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), avg(b) OVER (ORDER BY c RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), avg(c) OVER (RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), avg(c) OVER (ORDER BY a RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), avg(c) OVER (ORDER BY b RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING), avg(c) OVER (ORDER BY c RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM abc ---- 3.5 3.5 3.5 3.5 10 10 10 10 25 25 25 25 3.5 3.5 3.5 3.5 10 10 10 10 25 25 25 25 3.5 3.5 3.5 3.5 10 10 10 10 25 25 25 25 3.5 3.5 3.5 3.5 10 10 10 10 25 25 25 25 3.5 3.5 3.5 3.5 10 10 10 10 25 25 25 25 3.5 3.5 3.5 3.5 10 10 10 10 25 25 25 25