CREATE TABLE window_test(id Int64, value Int64, partition Int64, msg String) Engine=MergeTree ORDER BY id;

INSERT INTO window_test SELECT number, rand(1) % 500, number % 3000,randomPrintableASCII(2) FROM numbers(50000000);

-- query0: sipHash64 over window
SELECT id, AVG(value) OVER (ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS frame, sipHash64(frame) FROM window_test FORMAT Null;

-- query1: aggregate (by id) over window
SELECT id AS key, sipHash64(sum(frame)) AS value FROM (SELECT id, AVG(value) OVER (ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS frame FROM window_test) GROUP BY key ORDER BY key, value FORMAT Null;

-- query2: aggregate (by id % 100000) over window
SELECT id % 100000 AS key, sipHash64(sum(frame)) AS value FROM (SELECT id, AVG(value) OVER (ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS frame FROM window_test) GROUP BY key ORDER BY key, value FORMAT Null;

-- query3: aes enc/dec over window
WITH 'xxxxyyyyxxxxyyyyxxxxyyyyxxxxyyyy' AS cipherKey SELECT id, AVG(value) OVER (ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS frame, toString(frame) AS str,encrypt('aes-256-ofb', str, cipherKey) AS enc, decrypt('aes-256-ofb', str, cipherKey) AS dec FROM window_test FORMAT Null;

-- query4: window with partitions
SELECT id, AVG(value) OVER (PARTITION by partition ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS frame FROM window_test ORDER BY id FORMAT Null;

-- query5: window with partitions
SELECT DISTINCT AVG(value) OVER (PARTITION by partition ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS frame FROM window_test ORDER BY frame FORMAT Null;