1CREATE TABLE t1 (c1 INT, c2 VARCHAR(30)); 2PREPARE populate_table FROM "INSERT into t1 values (1, 'manual_insert_1'), 3 (4, 'manual_insert_2')"; 4INSERT INTO t1 SELECT row_number() over(), "should_not_add_any_rows" FROM t1; 5INSERT INTO t1 SELECT 1 + row_number() over(), "should_not_add_any_rows" FROM t1; 6EXECUTE populate_table; 7INSERT INTO t1 SELECT 10 + row_number() over(), "should repeat 2 times [11-12]" FROM t1; 8SELECT c1, c2 FROM t1 ORDER BY c2, c1; 9c1 c2 101 manual_insert_1 114 manual_insert_2 1211 should repeat 2 times [11-12] 1312 should repeat 2 times [11-12] 14DELETE FROM t1; 15EXECUTE populate_table; 16INSERT INTO t1 17SELECT 10 + (dense_rank() over(order by c1)), "dense_rank_insert" from t1; 18SELECT c1, c2 FROM t1 ORDER BY c2, c1; 19c1 c2 2011 dense_rank_insert 2112 dense_rank_insert 221 manual_insert_1 234 manual_insert_2 24DELETE FROM t1; 25EXECUTE populate_table; 26INSERT INTO t1 27SELECT 100 + (rank() over(order by c1)), "rank_insert" from t1; 28SELECT c1, c2 FROM t1 ORDER BY c2, c1; 29c1 c2 301 manual_insert_1 314 manual_insert_2 32101 rank_insert 33102 rank_insert 34DELETE FROM t1; 35EXECUTE populate_table; 36INSERT INTO t1 37SELECT 100 + (ntile(10) over(order by c1)), "ntile_insert" from t1; 38SELECT c1, c2 FROM t1 ORDER BY c2, c1; 39c1 c2 401 manual_insert_1 414 manual_insert_2 42101 ntile_insert 43102 ntile_insert 44DELETE FROM t1; 45EXECUTE populate_table; 46INSERT INTO t1 47SELECT 1000 + (percent_rank() over(order by c1)), "percent_rank_insert" from t1; 48SELECT c1, c2 FROM t1 ORDER BY c2, c1; 49c1 c2 501 manual_insert_1 514 manual_insert_2 521000 percent_rank_insert 531001 percent_rank_insert 54DELETE FROM t1; 55EXECUTE populate_table; 56INSERT INTO t1 57SELECT 1000 + (count(*) over(order by c1)), "count_insert" from t1; 58SELECT c1, c2 FROM t1 ORDER BY c2, c1; 59c1 c2 601001 count_insert 611002 count_insert 621 manual_insert_1 634 manual_insert_2 64DELETE FROM t1; 65EXECUTE populate_table; 66# 67# Test how avg interacts when the results need to be rounded. 68# 69SELECT 1000 + (avg(c1) over(order by c1)) as avg_expr, c1, "This will be inserted into t1" from t1; 70avg_expr c1 This will be inserted into t1 711001.0000 1 This will be inserted into t1 721002.5000 4 This will be inserted into t1 73INSERT INTO t1 74SELECT 1000 + (avg(c1) over(order by c1)), "avg_insert" from t1; 75SELECT c1, c2 FROM t1 ORDER BY c2, c1; 76c1 c2 771001 avg_insert 781003 avg_insert 791 manual_insert_1 804 manual_insert_2 81DELETE FROM t1; 82EXECUTE populate_table; 83INSERT INTO t1 84SELECT 1000 + (sum(c1) over(order by c1)), "sum_insert" from t1; 85SELECT c1, c2 86FROM t1 87ORDER BY c2, c1; 88c1 c2 891 manual_insert_1 904 manual_insert_2 911001 sum_insert 921005 sum_insert 93DROP table t1; 94