sqlite/libsql
0
0
Fork 0
mirror of https://github.com/tursodatabase/libsql.git synced 2024-12-12 15:33:44 +00:00
Code
libsql/libsql-wal/tests/assets/fixtures/window8.test
ad hoc ad79ef48a2
libsql wal tests (#1408) 
* add tests

* add bench
2024-05-24 13:59:17 +00:00

2055 lines
71 KiB
Plaintext
Raw Permalink Blame History

DROP TABLE IF EXISTS t3;
CREATE TABLE t3(a TEXT, b TEXT, c INTEGER);
INSERT INTO t3 VALUES
('HH', 'bb', 355), ('CC', 'aa', 158), ('BB', 'aa', 399),
('FF', 'bb', 938), ('HH', 'aa', 480), ('FF', 'bb', 870),
('JJ', 'aa', 768), ('JJ', 'aa', 899), ('GG', 'bb', 929),
('II', 'bb', 421), ('GG', 'bb', 844), ('FF', 'bb', 574),
('CC', 'bb', 822), ('GG', 'bb', 938), ('BB', 'aa', 660),
('HH', 'aa', 979), ('BB', 'bb', 792), ('DD', 'aa', 845),
('JJ', 'bb', 354), ('FF', 'bb', 295), ('JJ', 'aa', 234),
('BB', 'bb', 840), ('AA', 'aa', 934), ('EE', 'aa', 113),
('AA', 'bb', 309), ('BB', 'aa', 412), ('AA', 'aa', 911),
('AA', 'bb', 572), ('II', 'aa', 398), ('II', 'bb', 250),
('II', 'aa', 652), ('BB', 'bb', 633), ('AA', 'aa', 239),
('FF', 'aa', 670), ('BB', 'bb', 705), ('HH', 'bb', 963),
('CC', 'bb', 346), ('II', 'bb', 671), ('BB', 'aa', 247),
('AA', 'aa', 223), ('GG', 'aa', 480), ('HH', 'aa', 790),
('FF', 'aa', 208), ('BB', 'bb', 711), ('EE', 'aa', 777),
('DD', 'bb', 716), ('CC', 'aa', 759), ('CC', 'aa', 430),
('CC', 'aa', 607), ('DD', 'bb', 794), ('GG', 'aa', 148),
('GG', 'aa', 634), ('JJ', 'bb', 257), ('DD', 'bb', 959),
('FF', 'bb', 726), ('BB', 'aa', 762), ('JJ', 'bb', 336),
('GG', 'aa', 335), ('HH', 'bb', 330), ('GG', 'bb', 160),
('JJ', 'bb', 839), ('FF', 'aa', 618), ('BB', 'aa', 393),
('EE', 'bb', 629), ('FF', 'aa', 667), ('AA', 'bb', 870),
('FF', 'bb', 102), ('JJ', 'aa', 113), ('DD', 'aa', 224),
('AA', 'bb', 627), ('HH', 'bb', 730), ('II', 'bb', 443),
('HH', 'bb', 133), ('EE', 'bb', 252), ('II', 'bb', 805),
('BB', 'bb', 786), ('EE', 'bb', 768), ('HH', 'bb', 683),
('DD', 'bb', 238), ('DD', 'aa', 256);
SELECT a, b, sum(c) OVER (ORDER BY a GROUPS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a,b GROUPS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, rank() OVER (ORDER BY a GROUPS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, max(c) OVER (ORDER BY a,b GROUPS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, min(c) OVER (ORDER BY a,b GROUPS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a GROUPS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING EXCLUDE CURRENT ROW) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a,b GROUPS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING EXCLUDE CURRENT ROW) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b,
sum(c) OVER (ORDER BY a GROUPS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING EXCLUDE CURRENT ROW),
sum(c) OVER (ORDER BY a GROUPS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING ),
sum(c) OVER (ORDER BY a,b GROUPS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING EXCLUDE CURRENT ROW),
sum(c) OVER (ORDER BY a,b GROUPS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING )
FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a,b GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, rank() OVER (ORDER BY a GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, max(c) OVER (ORDER BY a,b GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, min(c) OVER (ORDER BY a,b GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a,b GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b,
sum(c) OVER (ORDER BY a GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW),
sum(c) OVER (ORDER BY a GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ),
sum(c) OVER (ORDER BY a,b GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW),
sum(c) OVER (ORDER BY a,b GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a GROUPS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a,b GROUPS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, rank() OVER (ORDER BY a GROUPS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, max(c) OVER (ORDER BY a,b GROUPS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, min(c) OVER (ORDER BY a,b GROUPS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a GROUPS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING EXCLUDE CURRENT ROW) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a,b GROUPS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING EXCLUDE CURRENT ROW) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b,
sum(c) OVER (ORDER BY a GROUPS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING EXCLUDE CURRENT ROW),
sum(c) OVER (ORDER BY a GROUPS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING ),
sum(c) OVER (ORDER BY a,b GROUPS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING EXCLUDE CURRENT ROW),
sum(c) OVER (ORDER BY a,b GROUPS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING )
FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a,b GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, rank() OVER (ORDER BY a GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, max(c) OVER (ORDER BY a,b GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, min(c) OVER (ORDER BY a,b GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a,b GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b,
sum(c) OVER (ORDER BY a GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW),
sum(c) OVER (ORDER BY a GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ),
sum(c) OVER (ORDER BY a,b GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW),
sum(c) OVER (ORDER BY a,b GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )
FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a GROUPS BETWEEN 1 PRECEDING AND 2 PRECEDING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a,b GROUPS BETWEEN 1 PRECEDING AND 2 PRECEDING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, rank() OVER (ORDER BY a GROUPS BETWEEN 1 PRECEDING AND 2 PRECEDING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, max(c) OVER (ORDER BY a,b GROUPS BETWEEN 1 PRECEDING AND 2 PRECEDING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, min(c) OVER (ORDER BY a,b GROUPS BETWEEN 1 PRECEDING AND 2 PRECEDING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a GROUPS BETWEEN 1 PRECEDING AND 2 PRECEDING EXCLUDE CURRENT ROW) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a,b GROUPS BETWEEN 1 PRECEDING AND 2 PRECEDING EXCLUDE CURRENT ROW) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b,
sum(c) OVER (ORDER BY a GROUPS BETWEEN 1 PRECEDING AND 2 PRECEDING EXCLUDE CURRENT ROW),
sum(c) OVER (ORDER BY a GROUPS BETWEEN 1 PRECEDING AND 2 PRECEDING ),
sum(c) OVER (ORDER BY a,b GROUPS BETWEEN 1 PRECEDING AND 2 PRECEDING EXCLUDE CURRENT ROW),
sum(c) OVER (ORDER BY a,b GROUPS BETWEEN 1 PRECEDING AND 2 PRECEDING )
FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a GROUPS BETWEEN 2 PRECEDING AND 1 PRECEDING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a,b GROUPS BETWEEN 2 PRECEDING AND 1 PRECEDING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, rank() OVER (ORDER BY a GROUPS BETWEEN 2 PRECEDING AND 1 PRECEDING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, max(c) OVER (ORDER BY a,b GROUPS BETWEEN 2 PRECEDING AND 1 PRECEDING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, min(c) OVER (ORDER BY a,b GROUPS BETWEEN 2 PRECEDING AND 1 PRECEDING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a GROUPS BETWEEN 2 PRECEDING AND 1 PRECEDING EXCLUDE CURRENT ROW) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a,b GROUPS BETWEEN 2 PRECEDING AND 1 PRECEDING EXCLUDE CURRENT ROW) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b,
sum(c) OVER (ORDER BY a GROUPS BETWEEN 2 PRECEDING AND 1 PRECEDING EXCLUDE CURRENT ROW),
sum(c) OVER (ORDER BY a GROUPS BETWEEN 2 PRECEDING AND 1 PRECEDING ),
sum(c) OVER (ORDER BY a,b GROUPS BETWEEN 2 PRECEDING AND 1 PRECEDING EXCLUDE CURRENT ROW),
sum(c) OVER (ORDER BY a,b GROUPS BETWEEN 2 PRECEDING AND 1 PRECEDING )
FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a GROUPS BETWEEN 3 PRECEDING AND 1 PRECEDING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a,b GROUPS BETWEEN 3 PRECEDING AND 1 PRECEDING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, rank() OVER (ORDER BY a GROUPS BETWEEN 3 PRECEDING AND 1 PRECEDING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, max(c) OVER (ORDER BY a,b GROUPS BETWEEN 3 PRECEDING AND 1 PRECEDING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, min(c) OVER (ORDER BY a,b GROUPS BETWEEN 3 PRECEDING AND 1 PRECEDING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a GROUPS BETWEEN 3 PRECEDING AND 1 PRECEDING EXCLUDE CURRENT ROW) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a,b GROUPS BETWEEN 3 PRECEDING AND 1 PRECEDING EXCLUDE CURRENT ROW) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b,
sum(c) OVER (ORDER BY a GROUPS BETWEEN 3 PRECEDING AND 1 PRECEDING EXCLUDE CURRENT ROW),
sum(c) OVER (ORDER BY a GROUPS BETWEEN 3 PRECEDING AND 1 PRECEDING ),
sum(c) OVER (ORDER BY a,b GROUPS BETWEEN 3 PRECEDING AND 1 PRECEDING EXCLUDE CURRENT ROW),
sum(c) OVER (ORDER BY a,b GROUPS BETWEEN 3 PRECEDING AND 1 PRECEDING )
FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a GROUPS BETWEEN 3 PRECEDING AND 0 PRECEDING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a,b GROUPS BETWEEN 3 PRECEDING AND 0 PRECEDING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, rank() OVER (ORDER BY a GROUPS BETWEEN 3 PRECEDING AND 0 PRECEDING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, max(c) OVER (ORDER BY a,b GROUPS BETWEEN 3 PRECEDING AND 0 PRECEDING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, min(c) OVER (ORDER BY a,b GROUPS BETWEEN 3 PRECEDING AND 0 PRECEDING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a GROUPS BETWEEN 3 PRECEDING AND 0 PRECEDING EXCLUDE CURRENT ROW) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a,b GROUPS BETWEEN 3 PRECEDING AND 0 PRECEDING EXCLUDE CURRENT ROW) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b,
sum(c) OVER (ORDER BY a GROUPS BETWEEN 3 PRECEDING AND 0 PRECEDING EXCLUDE CURRENT ROW),
sum(c) OVER (ORDER BY a GROUPS BETWEEN 3 PRECEDING AND 0 PRECEDING ),
sum(c) OVER (ORDER BY a,b GROUPS BETWEEN 3 PRECEDING AND 0 PRECEDING EXCLUDE CURRENT ROW),
sum(c) OVER (ORDER BY a,b GROUPS BETWEEN 3 PRECEDING AND 0 PRECEDING )
FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a GROUPS BETWEEN 2 PRECEDING AND CURRENT ROW ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a,b GROUPS BETWEEN 2 PRECEDING AND CURRENT ROW ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, rank() OVER (ORDER BY a GROUPS BETWEEN 2 PRECEDING AND CURRENT ROW ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, max(c) OVER (ORDER BY a,b GROUPS BETWEEN 2 PRECEDING AND CURRENT ROW ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, min(c) OVER (ORDER BY a,b GROUPS BETWEEN 2 PRECEDING AND CURRENT ROW ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a GROUPS BETWEEN 2 PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a,b GROUPS BETWEEN 2 PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b,
sum(c) OVER (ORDER BY a GROUPS BETWEEN 2 PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW),
sum(c) OVER (ORDER BY a GROUPS BETWEEN 2 PRECEDING AND CURRENT ROW ),
sum(c) OVER (ORDER BY a,b GROUPS BETWEEN 2 PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW),
sum(c) OVER (ORDER BY a,b GROUPS BETWEEN 2 PRECEDING AND CURRENT ROW )
FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a GROUPS BETWEEN 3 PRECEDING AND 0 FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a,b GROUPS BETWEEN 3 PRECEDING AND 0 FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, rank() OVER (ORDER BY a GROUPS BETWEEN 3 PRECEDING AND 0 FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, max(c) OVER (ORDER BY a,b GROUPS BETWEEN 3 PRECEDING AND 0 FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, min(c) OVER (ORDER BY a,b GROUPS BETWEEN 3 PRECEDING AND 0 FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a GROUPS BETWEEN 3 PRECEDING AND 0 FOLLOWING EXCLUDE CURRENT ROW) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a,b GROUPS BETWEEN 3 PRECEDING AND 0 FOLLOWING EXCLUDE CURRENT ROW) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b,
sum(c) OVER (ORDER BY a GROUPS BETWEEN 3 PRECEDING AND 0 FOLLOWING EXCLUDE CURRENT ROW),
sum(c) OVER (ORDER BY a GROUPS BETWEEN 3 PRECEDING AND 0 FOLLOWING ),
sum(c) OVER (ORDER BY a,b GROUPS BETWEEN 3 PRECEDING AND 0 FOLLOWING EXCLUDE CURRENT ROW),
sum(c) OVER (ORDER BY a,b GROUPS BETWEEN 3 PRECEDING AND 0 FOLLOWING )
FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a GROUPS BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a,b GROUPS BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, rank() OVER (ORDER BY a GROUPS BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, max(c) OVER (ORDER BY a,b GROUPS BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, min(c) OVER (ORDER BY a,b GROUPS BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a GROUPS BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a,b GROUPS BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b,
sum(c) OVER (ORDER BY a GROUPS BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW),
sum(c) OVER (ORDER BY a GROUPS BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING ),
sum(c) OVER (ORDER BY a,b GROUPS BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW),
sum(c) OVER (ORDER BY a,b GROUPS BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING )
FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a GROUPS BETWEEN CURRENT ROW AND 0 FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a,b GROUPS BETWEEN CURRENT ROW AND 0 FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, rank() OVER (ORDER BY a GROUPS BETWEEN CURRENT ROW AND 0 FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, max(c) OVER (ORDER BY a,b GROUPS BETWEEN CURRENT ROW AND 0 FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, min(c) OVER (ORDER BY a,b GROUPS BETWEEN CURRENT ROW AND 0 FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a GROUPS BETWEEN CURRENT ROW AND 0 FOLLOWING EXCLUDE CURRENT ROW) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a,b GROUPS BETWEEN CURRENT ROW AND 0 FOLLOWING EXCLUDE CURRENT ROW) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b,
sum(c) OVER (ORDER BY a GROUPS BETWEEN CURRENT ROW AND 0 FOLLOWING EXCLUDE CURRENT ROW),
sum(c) OVER (ORDER BY a GROUPS BETWEEN CURRENT ROW AND 0 FOLLOWING ),
sum(c) OVER (ORDER BY a,b GROUPS BETWEEN CURRENT ROW AND 0 FOLLOWING EXCLUDE CURRENT ROW),
sum(c) OVER (ORDER BY a,b GROUPS BETWEEN CURRENT ROW AND 0 FOLLOWING )
FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a GROUPS BETWEEN CURRENT ROW AND 1 FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a,b GROUPS BETWEEN CURRENT ROW AND 1 FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, rank() OVER (ORDER BY a GROUPS BETWEEN CURRENT ROW AND 1 FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, max(c) OVER (ORDER BY a,b GROUPS BETWEEN CURRENT ROW AND 1 FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, min(c) OVER (ORDER BY a,b GROUPS BETWEEN CURRENT ROW AND 1 FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a GROUPS BETWEEN CURRENT ROW AND 1 FOLLOWING EXCLUDE CURRENT ROW) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a,b GROUPS BETWEEN CURRENT ROW AND 1 FOLLOWING EXCLUDE CURRENT ROW) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b,
sum(c) OVER (ORDER BY a GROUPS BETWEEN CURRENT ROW AND 1 FOLLOWING EXCLUDE CURRENT ROW),
sum(c) OVER (ORDER BY a GROUPS BETWEEN CURRENT ROW AND 1 FOLLOWING ),
sum(c) OVER (ORDER BY a,b GROUPS BETWEEN CURRENT ROW AND 1 FOLLOWING EXCLUDE CURRENT ROW),
sum(c) OVER (ORDER BY a,b GROUPS BETWEEN CURRENT ROW AND 1 FOLLOWING )
FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a GROUPS BETWEEN CURRENT ROW AND 100 FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a,b GROUPS BETWEEN CURRENT ROW AND 100 FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, rank() OVER (ORDER BY a GROUPS BETWEEN CURRENT ROW AND 100 FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, max(c) OVER (ORDER BY a,b GROUPS BETWEEN CURRENT ROW AND 100 FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, min(c) OVER (ORDER BY a,b GROUPS BETWEEN CURRENT ROW AND 100 FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a GROUPS BETWEEN CURRENT ROW AND 100 FOLLOWING EXCLUDE CURRENT ROW) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a,b GROUPS BETWEEN CURRENT ROW AND 100 FOLLOWING EXCLUDE CURRENT ROW) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b,
sum(c) OVER (ORDER BY a GROUPS BETWEEN CURRENT ROW AND 100 FOLLOWING EXCLUDE CURRENT ROW),
sum(c) OVER (ORDER BY a GROUPS BETWEEN CURRENT ROW AND 100 FOLLOWING ),
sum(c) OVER (ORDER BY a,b GROUPS BETWEEN CURRENT ROW AND 100 FOLLOWING EXCLUDE CURRENT ROW),
sum(c) OVER (ORDER BY a,b GROUPS BETWEEN CURRENT ROW AND 100 FOLLOWING )
FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a GROUPS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a,b GROUPS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, rank() OVER (ORDER BY a GROUPS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, max(c) OVER (ORDER BY a,b GROUPS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, min(c) OVER (ORDER BY a,b GROUPS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a GROUPS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a,b GROUPS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b,
sum(c) OVER (ORDER BY a GROUPS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW),
sum(c) OVER (ORDER BY a GROUPS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ),
sum(c) OVER (ORDER BY a,b GROUPS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW),
sum(c) OVER (ORDER BY a,b GROUPS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING )
FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a GROUPS BETWEEN 0 FOLLOWING AND 0 FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a,b GROUPS BETWEEN 0 FOLLOWING AND 0 FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, rank() OVER (ORDER BY a GROUPS BETWEEN 0 FOLLOWING AND 0 FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, max(c) OVER (ORDER BY a,b GROUPS BETWEEN 0 FOLLOWING AND 0 FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, min(c) OVER (ORDER BY a,b GROUPS BETWEEN 0 FOLLOWING AND 0 FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a GROUPS BETWEEN 0 FOLLOWING AND 0 FOLLOWING EXCLUDE CURRENT ROW) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a,b GROUPS BETWEEN 0 FOLLOWING AND 0 FOLLOWING EXCLUDE CURRENT ROW) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b,
sum(c) OVER (ORDER BY a GROUPS BETWEEN 0 FOLLOWING AND 0 FOLLOWING EXCLUDE CURRENT ROW),
sum(c) OVER (ORDER BY a GROUPS BETWEEN 0 FOLLOWING AND 0 FOLLOWING ),
sum(c) OVER (ORDER BY a,b GROUPS BETWEEN 0 FOLLOWING AND 0 FOLLOWING EXCLUDE CURRENT ROW),
sum(c) OVER (ORDER BY a,b GROUPS BETWEEN 0 FOLLOWING AND 0 FOLLOWING )
FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a GROUPS BETWEEN 1 FOLLOWING AND 0 FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a,b GROUPS BETWEEN 1 FOLLOWING AND 0 FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, rank() OVER (ORDER BY a GROUPS BETWEEN 1 FOLLOWING AND 0 FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, max(c) OVER (ORDER BY a,b GROUPS BETWEEN 1 FOLLOWING AND 0 FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, min(c) OVER (ORDER BY a,b GROUPS BETWEEN 1 FOLLOWING AND 0 FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a GROUPS BETWEEN 1 FOLLOWING AND 0 FOLLOWING EXCLUDE CURRENT ROW) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a,b GROUPS BETWEEN 1 FOLLOWING AND 0 FOLLOWING EXCLUDE CURRENT ROW) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b,
sum(c) OVER (ORDER BY a GROUPS BETWEEN 1 FOLLOWING AND 0 FOLLOWING EXCLUDE CURRENT ROW),
sum(c) OVER (ORDER BY a GROUPS BETWEEN 1 FOLLOWING AND 0 FOLLOWING ),
sum(c) OVER (ORDER BY a,b GROUPS BETWEEN 1 FOLLOWING AND 0 FOLLOWING EXCLUDE CURRENT ROW),
sum(c) OVER (ORDER BY a,b GROUPS BETWEEN 1 FOLLOWING AND 0 FOLLOWING )
FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a GROUPS BETWEEN 1 FOLLOWING AND 5 FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a,b GROUPS BETWEEN 1 FOLLOWING AND 5 FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, rank() OVER (ORDER BY a GROUPS BETWEEN 1 FOLLOWING AND 5 FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, max(c) OVER (ORDER BY a,b GROUPS BETWEEN 1 FOLLOWING AND 5 FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, min(c) OVER (ORDER BY a,b GROUPS BETWEEN 1 FOLLOWING AND 5 FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a GROUPS BETWEEN 1 FOLLOWING AND 5 FOLLOWING EXCLUDE CURRENT ROW) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a,b GROUPS BETWEEN 1 FOLLOWING AND 5 FOLLOWING EXCLUDE CURRENT ROW) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b,
sum(c) OVER (ORDER BY a GROUPS BETWEEN 1 FOLLOWING AND 5 FOLLOWING EXCLUDE CURRENT ROW),
sum(c) OVER (ORDER BY a GROUPS BETWEEN 1 FOLLOWING AND 5 FOLLOWING ),
sum(c) OVER (ORDER BY a,b GROUPS BETWEEN 1 FOLLOWING AND 5 FOLLOWING EXCLUDE CURRENT ROW),
sum(c) OVER (ORDER BY a,b GROUPS BETWEEN 1 FOLLOWING AND 5 FOLLOWING )
FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a GROUPS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a,b GROUPS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, rank() OVER (ORDER BY a GROUPS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, max(c) OVER (ORDER BY a,b GROUPS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, min(c) OVER (ORDER BY a,b GROUPS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING ) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a GROUPS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b, sum(c) OVER (ORDER BY a,b GROUPS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW) FROM t3 ORDER BY 1, 2, 3;
SELECT a, b,
sum(c) OVER (ORDER BY a GROUPS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW),
sum(c) OVER (ORDER BY a GROUPS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING ),
sum(c) OVER (ORDER BY a,b GROUPS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW),
sum(c) OVER (ORDER BY a,b GROUPS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING )
FROM t3 ORDER BY 1, 2, 3;
SELECT row_number() OVER win
FROM t3
WINDOW win AS (
ORDER BY c, b, a
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE NO OTHERS
)
SELECT nth_value(c, 14) OVER win
FROM t3
WINDOW win AS (
ORDER BY c, b, a
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE NO OTHERS
)
SELECT min(c) OVER win, max(c) OVER win, sum(c) OVER win FROM t3
WINDOW win AS (
ORDER BY c, b, a
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE NO OTHERS
) ORDER BY a, b, c;
SELECT row_number() OVER win
FROM t3
WINDOW win AS (
ORDER BY c, b, a
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW
)
SELECT nth_value(c, 14) OVER win
FROM t3
WINDOW win AS (
ORDER BY c, b, a
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW
)
SELECT min(c) OVER win, max(c) OVER win, sum(c) OVER win FROM t3
WINDOW win AS (
ORDER BY c, b, a
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW
) ORDER BY a, b, c;
SELECT row_number() OVER win
FROM t3
WINDOW win AS (
ORDER BY c, b, a
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE GROUP
)
SELECT nth_value(c, 14) OVER win
FROM t3
WINDOW win AS (
ORDER BY c, b, a
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE GROUP
)
SELECT min(c) OVER win, max(c) OVER win, sum(c) OVER win FROM t3
WINDOW win AS (
ORDER BY c, b, a
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE GROUP
) ORDER BY a, b, c;
SELECT row_number() OVER win
FROM t3
WINDOW win AS (
ORDER BY c, b, a
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE TIES
)
SELECT nth_value(c, 14) OVER win
FROM t3
WINDOW win AS (
ORDER BY c, b, a
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE TIES
)
SELECT min(c) OVER win, max(c) OVER win, sum(c) OVER win FROM t3
WINDOW win AS (
ORDER BY c, b, a
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES
) ORDER BY a, b, c;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(a REAL, b INTEGER);
INSERT INTO t1 VALUES
(5, 10), (10, 20), (13, 26), (13, 26),
(15, 30), (20, 40), (22,80), (30, 90);
SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING )
SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 10 PRECEDING AND 5 PRECEDING )
SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 2 FOLLOWING AND 3 FOLLOWING )
SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING )
SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN 10 PRECEDING AND 5 PRECEDING )
SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN 2 FOLLOWING AND 3 FOLLOWING )
SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 5.1 PRECEDING AND 5.3 FOLLOWING )
SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 10.2 PRECEDING AND 5.4 PRECEDING )
SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE BETWEEN 2.6 FOLLOWING AND 3.5 FOLLOWING )
SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN 5.7 PRECEDING AND 5.8 FOLLOWING )
SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND 5.9 PRECEDING )
SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a DESC RANGE BETWEEN 2.1 FOLLOWING AND UNBOUNDED FOLLOWING )
SELECT CAST(a AS INTEGER), sum(b) OVER win FROM t1 WINDOW win AS ( ORDER BY a RANGE 5.1 PRECEDING )
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(a INTEGER, b INTEGER);
INSERT INTO t1 VALUES
(NULL, 1), (NULL, 2), (NULL, 3), (10, 4), (10, 5);
SELECT sum(b) OVER (
ORDER BY a RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING
) FROM t1 ORDER BY 1;
SELECT sum(b) OVER (
ORDER BY a DESC RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING
) FROM t1 ORDER BY 1;
SELECT sum(b) OVER (
ORDER BY a RANGE BETWEEN 5 FOLLOWING AND 10 FOLLOWING
) FROM t1 ORDER BY 1 NULLS FIRST;
SELECT sum(b) OVER (
ORDER BY a RANGE BETWEEN 5 FOLLOWING AND 10 FOLLOWING
) FROM t1 ORDER BY 1 NULLS LAST;
SELECT sum(b) OVER (
ORDER BY a DESC RANGE BETWEEN 5 FOLLOWING AND 10 FOLLOWING
) FROM t1 ORDER BY 1 NULLS FIRST;
SELECT sum(b) OVER (
ORDER BY a DESC RANGE BETWEEN 5 FOLLOWING AND 10 FOLLOWING
) FROM t1 ORDER BY 1 NULLS LAST;
SELECT sum(b) OVER (
ORDER BY a NULLS FIRST RANGE BETWEEN UNBOUNDED PRECEDING AND 10 FOLLOWING
) FROM t1 ORDER BY 1 NULLS FIRST;
SELECT sum(b) OVER (
ORDER BY a NULLS LAST RANGE BETWEEN UNBOUNDED PRECEDING AND 10 FOLLOWING
) FROM t1 ORDER BY 1 NULLS LAST;
SELECT sum(b) OVER (
ORDER BY a NULLS FIRST ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
) FROM t1 ORDER BY 1 NULLS FIRST;
SELECT sum(b) OVER (
ORDER BY a NULLS LAST ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
) FROM t1 ORDER BY 1 NULLS LAST;
SELECT sum(b) OVER (
ORDER BY a DESC NULLS LAST ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
) FROM t1 ORDER BY 1 NULLS FIRST;
SELECT sum(b) OVER (
ORDER BY a DESC NULLS LAST ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
) FROM t1 ORDER BY 1 NULLS LAST;
SELECT sum(b) OVER (
ORDER BY a ASC NULLS LAST RANGE BETWEEN UNBOUNDED PRECEDING AND 10 FOLLOWING
) FROM t1 ORDER BY 1 NULLS LAST;
SELECT sum(b) OVER (
ORDER BY a DESC NULLS FIRST RANGE
BETWEEN UNBOUNDED PRECEDING AND 10 FOLLOWING
) FROM t1 ORDER BY 1 NULLS LAST;
INSERT INTO t3 VALUES
(NULL, 'bb', 355), (NULL, 'cc', 158), (NULL, 'aa', 399),
('JJ', NULL, 839), ('FF', NULL, 618), ('BB', NULL, 393),
(NULL, 'bb', 629), (NULL, NULL, 667), (NULL, NULL, 870);
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE NO OTHERS )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE NO OTHERS )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( ORDER BY a NULLS FIRST
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE NO OTHERS )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( ORDER BY a NULLS FIRST
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE NO OTHERS )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( PARTITION BY coalesce(a, '')
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE NO OTHERS )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( PARTITION BY coalesce(a, '')
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE NO OTHERS )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( ORDER BY a NULLS FIRST GROUPS 6 PRECEDING EXCLUDE NO OTHERS )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( ORDER BY a NULLS FIRST GROUPS 6 PRECEDING EXCLUDE NO OTHERS )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( ORDER BY c NULLS FIRST RANGE BETWEEN 6 PRECEDING AND 7 FOLLOWING EXCLUDE NO OTHERS )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( ORDER BY c NULLS FIRST RANGE BETWEEN 6 PRECEDING AND 7 FOLLOWING EXCLUDE NO OTHERS )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( ORDER BY c NULLS FIRST RANGE BETWEEN 0 PRECEDING AND 0 FOLLOWING EXCLUDE NO OTHERS )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( ORDER BY c NULLS FIRST RANGE BETWEEN 0 PRECEDING AND 0 FOLLOWING EXCLUDE NO OTHERS )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( ORDER BY c NULLS FIRST, b NULLS FIRST, a NULLS FIRST
ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE NO OTHERS )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( ORDER BY c NULLS FIRST, b NULLS FIRST, a NULLS FIRST
ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE NO OTHERS )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE NO OTHERS )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE NO OTHERS )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( ORDER BY a NULLS LAST
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE NO OTHERS )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( ORDER BY a NULLS LAST
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE NO OTHERS )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( PARTITION BY coalesce(a, '')
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE NO OTHERS )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( PARTITION BY coalesce(a, '')
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE NO OTHERS )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( ORDER BY a NULLS LAST GROUPS 6 PRECEDING EXCLUDE NO OTHERS )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( ORDER BY a NULLS LAST GROUPS 6 PRECEDING EXCLUDE NO OTHERS )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( ORDER BY c NULLS LAST RANGE BETWEEN 6 PRECEDING AND 7 FOLLOWING EXCLUDE NO OTHERS )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( ORDER BY c NULLS LAST RANGE BETWEEN 6 PRECEDING AND 7 FOLLOWING EXCLUDE NO OTHERS )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( ORDER BY c NULLS LAST RANGE BETWEEN 0 PRECEDING AND 0 FOLLOWING EXCLUDE NO OTHERS )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( ORDER BY c NULLS LAST RANGE BETWEEN 0 PRECEDING AND 0 FOLLOWING EXCLUDE NO OTHERS )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( ORDER BY c NULLS LAST, b NULLS LAST, a NULLS LAST
ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE NO OTHERS )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( ORDER BY c NULLS LAST, b NULLS LAST, a NULLS LAST
ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE NO OTHERS )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( ORDER BY a NULLS FIRST
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( ORDER BY a NULLS FIRST
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( PARTITION BY coalesce(a, '')
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( PARTITION BY coalesce(a, '')
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( ORDER BY a NULLS FIRST GROUPS 6 PRECEDING EXCLUDE CURRENT ROW )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( ORDER BY a NULLS FIRST GROUPS 6 PRECEDING EXCLUDE CURRENT ROW )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( ORDER BY c NULLS FIRST RANGE BETWEEN 6 PRECEDING AND 7 FOLLOWING EXCLUDE CURRENT ROW )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( ORDER BY c NULLS FIRST RANGE BETWEEN 6 PRECEDING AND 7 FOLLOWING EXCLUDE CURRENT ROW )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( ORDER BY c NULLS FIRST RANGE BETWEEN 0 PRECEDING AND 0 FOLLOWING EXCLUDE CURRENT ROW )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( ORDER BY c NULLS FIRST RANGE BETWEEN 0 PRECEDING AND 0 FOLLOWING EXCLUDE CURRENT ROW )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( ORDER BY c NULLS FIRST, b NULLS FIRST, a NULLS FIRST
ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( ORDER BY c NULLS FIRST, b NULLS FIRST, a NULLS FIRST
ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( ORDER BY a NULLS LAST
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( ORDER BY a NULLS LAST
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( PARTITION BY coalesce(a, '')
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( PARTITION BY coalesce(a, '')
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( ORDER BY a NULLS LAST GROUPS 6 PRECEDING EXCLUDE CURRENT ROW )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( ORDER BY a NULLS LAST GROUPS 6 PRECEDING EXCLUDE CURRENT ROW )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( ORDER BY c NULLS LAST RANGE BETWEEN 6 PRECEDING AND 7 FOLLOWING EXCLUDE CURRENT ROW )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( ORDER BY c NULLS LAST RANGE BETWEEN 6 PRECEDING AND 7 FOLLOWING EXCLUDE CURRENT ROW )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( ORDER BY c NULLS LAST RANGE BETWEEN 0 PRECEDING AND 0 FOLLOWING EXCLUDE CURRENT ROW )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( ORDER BY c NULLS LAST RANGE BETWEEN 0 PRECEDING AND 0 FOLLOWING EXCLUDE CURRENT ROW )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( ORDER BY c NULLS LAST, b NULLS LAST, a NULLS LAST
ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( ORDER BY c NULLS LAST, b NULLS LAST, a NULLS LAST
ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE GROUP )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE GROUP )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( ORDER BY a NULLS FIRST
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE GROUP )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( ORDER BY a NULLS FIRST
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE GROUP )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( PARTITION BY coalesce(a, '')
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE GROUP )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( PARTITION BY coalesce(a, '')
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE GROUP )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( ORDER BY a NULLS FIRST GROUPS 6 PRECEDING EXCLUDE GROUP )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( ORDER BY a NULLS FIRST GROUPS 6 PRECEDING EXCLUDE GROUP )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( ORDER BY c NULLS FIRST RANGE BETWEEN 6 PRECEDING AND 7 FOLLOWING EXCLUDE GROUP )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( ORDER BY c NULLS FIRST RANGE BETWEEN 6 PRECEDING AND 7 FOLLOWING EXCLUDE GROUP )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( ORDER BY c NULLS FIRST RANGE BETWEEN 0 PRECEDING AND 0 FOLLOWING EXCLUDE GROUP )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( ORDER BY c NULLS FIRST RANGE BETWEEN 0 PRECEDING AND 0 FOLLOWING EXCLUDE GROUP )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( ORDER BY c NULLS FIRST, b NULLS FIRST, a NULLS FIRST
ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE GROUP )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( ORDER BY c NULLS FIRST, b NULLS FIRST, a NULLS FIRST
ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE GROUP )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE GROUP )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE GROUP )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( ORDER BY a NULLS LAST
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE GROUP )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( ORDER BY a NULLS LAST
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE GROUP )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( PARTITION BY coalesce(a, '')
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE GROUP )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( PARTITION BY coalesce(a, '')
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE GROUP )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( ORDER BY a NULLS LAST GROUPS 6 PRECEDING EXCLUDE GROUP )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( ORDER BY a NULLS LAST GROUPS 6 PRECEDING EXCLUDE GROUP )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( ORDER BY c NULLS LAST RANGE BETWEEN 6 PRECEDING AND 7 FOLLOWING EXCLUDE GROUP )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( ORDER BY c NULLS LAST RANGE BETWEEN 6 PRECEDING AND 7 FOLLOWING EXCLUDE GROUP )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( ORDER BY c NULLS LAST RANGE BETWEEN 0 PRECEDING AND 0 FOLLOWING EXCLUDE GROUP )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( ORDER BY c NULLS LAST RANGE BETWEEN 0 PRECEDING AND 0 FOLLOWING EXCLUDE GROUP )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( ORDER BY c NULLS LAST, b NULLS LAST, a NULLS LAST
ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE GROUP )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( ORDER BY c NULLS LAST, b NULLS LAST, a NULLS LAST
ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE GROUP )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE TIES )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE TIES )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( ORDER BY a NULLS FIRST
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE TIES )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( ORDER BY a NULLS FIRST
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE TIES )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( PARTITION BY coalesce(a, '')
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE TIES )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( PARTITION BY coalesce(a, '')
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE TIES )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( ORDER BY a NULLS FIRST GROUPS 6 PRECEDING EXCLUDE TIES )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( ORDER BY a NULLS FIRST GROUPS 6 PRECEDING EXCLUDE TIES )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( ORDER BY c NULLS FIRST RANGE BETWEEN 6 PRECEDING AND 7 FOLLOWING EXCLUDE TIES )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( ORDER BY c NULLS FIRST RANGE BETWEEN 6 PRECEDING AND 7 FOLLOWING EXCLUDE TIES )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( ORDER BY c NULLS FIRST RANGE BETWEEN 0 PRECEDING AND 0 FOLLOWING EXCLUDE TIES )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( ORDER BY c NULLS FIRST RANGE BETWEEN 0 PRECEDING AND 0 FOLLOWING EXCLUDE TIES )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( ORDER BY c NULLS FIRST, b NULLS FIRST, a NULLS FIRST
ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE TIES )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( ORDER BY c NULLS FIRST, b NULLS FIRST, a NULLS FIRST
ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE TIES )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE TIES )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE TIES )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( ORDER BY a NULLS LAST
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE TIES )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( ORDER BY a NULLS LAST
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE TIES )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( PARTITION BY coalesce(a, '')
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE TIES )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( PARTITION BY coalesce(a, '')
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE TIES )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( ORDER BY a NULLS LAST GROUPS 6 PRECEDING EXCLUDE TIES )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( ORDER BY a NULLS LAST GROUPS 6 PRECEDING EXCLUDE TIES )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( ORDER BY c NULLS LAST RANGE BETWEEN 6 PRECEDING AND 7 FOLLOWING EXCLUDE TIES )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( ORDER BY c NULLS LAST RANGE BETWEEN 6 PRECEDING AND 7 FOLLOWING EXCLUDE TIES )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( ORDER BY c NULLS LAST RANGE BETWEEN 0 PRECEDING AND 0 FOLLOWING EXCLUDE TIES )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( ORDER BY c NULLS LAST RANGE BETWEEN 0 PRECEDING AND 0 FOLLOWING EXCLUDE TIES )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT max(c) OVER win,
min(c) OVER win,
count(a) OVER win
FROM t3
WINDOW win AS ( ORDER BY c NULLS LAST, b NULLS LAST, a NULLS LAST
ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE TIES )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
SELECT sum(c) FILTER (WHERE (c%2)!=0) OVER win,
rank() OVER win,
dense_rank() OVER win
FROM t3
WINDOW win AS ( ORDER BY c NULLS LAST, b NULLS LAST, a NULLS LAST
ROWS BETWEEN 6 PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE TIES )
ORDER BY 1 NULLS FIRST, 2 NULLS FIRST, 3 NULLS FIRST
DROP TABLE IF EXISTS t2;
CREATE TABLE t2(a TEXT, b INTEGER);
INSERT INTO t2 VALUES('A', NULL);
INSERT INTO t2 VALUES('B', NULL);
INSERT INTO t2 VALUES('C', 1);
SELECT group_concat(a, '.') OVER (
ORDER BY b NULLS FIRST RANGE BETWEEN 7 PRECEDING AND 2 PRECEDING
)
FROM t2
SELECT group_concat(a, '.') OVER (
ORDER BY b DESC NULLS LAST RANGE BETWEEN 7 PRECEDING AND 2 PRECEDING
)
FROM t2
DROP TABLE IF EXISTS t2;
CREATE TABLE t2(a INTEGER, b INTEGER);
INSERT INTO t2 VALUES(1, 65);
INSERT INTO t2 VALUES(2, NULL);
INSERT INTO t2 VALUES(3, NULL);
INSERT INTO t2 VALUES(4, NULL);
INSERT INTO t2 VALUES(5, 66);
INSERT INTO t2 VALUES(6, 67);
SELECT sum (a) OVER win FROM t2
WINDOW win AS (
ORDER BY b NULLS LAST RANGE BETWEEN 6 FOLLOWING AND UNBOUNDED FOLLOWING
);
SELECT sum (a) OVER win FROM t2
WINDOW win AS (
ORDER BY b NULLS LAST RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING
);
SELECT sum (a) OVER win FROM t2
WINDOW win AS (
ORDER BY b NULLS LAST RANGE BETWEEN 2 FOLLOWING AND 1 FOLLOWING
);
SELECT sum (a) OVER win FROM t2
WINDOW win AS (
ORDER BY b NULLS FIRST RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING
);
SELECT sum (a) OVER win FROM t2
WINDOW win AS (
ORDER BY b NULLS FIRST RANGE BETWEEN 2 FOLLOWING AND 1 FOLLOWING
);
SELECT sum (a) OVER win FROM t2
WINDOW win AS (
ORDER BY b NULLS LAST RANGE BETWEEN 1000 PRECEDING AND 2 PRECEDING
);
SELECT sum (a) OVER win FROM t2
WINDOW win AS (
ORDER BY b NULLS LAST RANGE BETWEEN 2000 FOLLOWING AND 1000 FOLLOWING
);
SELECT sum (a) OVER win FROM t2
WINDOW win AS (
ORDER BY b NULLS FIRST RANGE BETWEEN 1000 PRECEDING AND 2000 PRECEDING
);
SELECT sum (a) OVER win FROM t2
WINDOW win AS (
ORDER BY b NULLS FIRST RANGE BETWEEN 2000 FOLLOWING AND 1000 FOLLOWING
);
SELECT min (a) OVER win FROM t2
WINDOW win AS (
ORDER BY b NULLS LAST RANGE BETWEEN 6 FOLLOWING AND UNBOUNDED FOLLOWING
);
SELECT min (a) OVER win FROM t2
WINDOW win AS (
ORDER BY b NULLS LAST RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING
);
SELECT min (a) OVER win FROM t2
WINDOW win AS (
ORDER BY b NULLS LAST RANGE BETWEEN 2 FOLLOWING AND 1 FOLLOWING
);
SELECT min (a) OVER win FROM t2
WINDOW win AS (
ORDER BY b NULLS FIRST RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING
);
SELECT min (a) OVER win FROM t2
WINDOW win AS (
ORDER BY b NULLS FIRST RANGE BETWEEN 2 FOLLOWING AND 1 FOLLOWING
);
SELECT min (a) OVER win FROM t2
WINDOW win AS (
ORDER BY b NULLS LAST RANGE BETWEEN 1000 PRECEDING AND 2 PRECEDING
);
SELECT min (a) OVER win FROM t2
WINDOW win AS (
ORDER BY b NULLS LAST RANGE BETWEEN 2000 FOLLOWING AND 1000 FOLLOWING
);
SELECT min (a) OVER win FROM t2
WINDOW win AS (
ORDER BY b NULLS FIRST RANGE BETWEEN 1000 PRECEDING AND 2000 PRECEDING
);
SELECT min (a) OVER win FROM t2
WINDOW win AS (
ORDER BY b NULLS FIRST RANGE BETWEEN 2000 FOLLOWING AND 1000 FOLLOWING
);
SELECT sum (a) OVER win FROM t2
WINDOW win AS (
ORDER BY b NULLS LAST RANGE BETWEEN 6 FOLLOWING AND UNBOUNDED FOLLOWING
);
SELECT sum (a) OVER win FROM t2
WINDOW win AS (
ORDER BY b NULLS LAST RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING
);
SELECT sum (a) OVER win FROM t2
WINDOW win AS (
ORDER BY b NULLS LAST RANGE BETWEEN 2 FOLLOWING AND 1 FOLLOWING
);
SELECT sum (a) OVER win FROM t2
WINDOW win AS (
ORDER BY b NULLS FIRST RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING
);
SELECT sum (a) OVER win FROM t2
WINDOW win AS (
ORDER BY b NULLS FIRST RANGE BETWEEN 2 FOLLOWING AND 1 FOLLOWING
);
SELECT sum (a) OVER win FROM t2
WINDOW win AS (
ORDER BY b NULLS LAST RANGE BETWEEN 1000 PRECEDING AND 2 PRECEDING
);
SELECT sum (a) OVER win FROM t2
WINDOW win AS (
ORDER BY b NULLS LAST RANGE BETWEEN 2000 FOLLOWING AND 1000 FOLLOWING
);
SELECT sum (a) OVER win FROM t2
WINDOW win AS (
ORDER BY b NULLS FIRST RANGE BETWEEN 1000 PRECEDING AND 2000 PRECEDING
);
SELECT sum (a) OVER win FROM t2
WINDOW win AS (
ORDER BY b NULLS FIRST RANGE BETWEEN 2000 FOLLOWING AND 1000 FOLLOWING
);
SELECT max (a) OVER win FROM t2
WINDOW win AS (
ORDER BY b NULLS LAST RANGE BETWEEN 6 FOLLOWING AND UNBOUNDED FOLLOWING
);
SELECT max (a) OVER win FROM t2
WINDOW win AS (
ORDER BY b NULLS LAST RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING
);
SELECT max (a) OVER win FROM t2
WINDOW win AS (
ORDER BY b NULLS LAST RANGE BETWEEN 2 FOLLOWING AND 1 FOLLOWING
);
SELECT max (a) OVER win FROM t2
WINDOW win AS (
ORDER BY b NULLS FIRST RANGE BETWEEN 1 PRECEDING AND 2 PRECEDING
);
SELECT max (a) OVER win FROM t2
WINDOW win AS (
ORDER BY b NULLS FIRST RANGE BETWEEN 2 FOLLOWING AND 1 FOLLOWING
);
SELECT max (a) OVER win FROM t2
WINDOW win AS (
ORDER BY b NULLS LAST RANGE BETWEEN 1000 PRECEDING AND 2 PRECEDING
);
SELECT max (a) OVER win FROM t2
WINDOW win AS (
ORDER BY b NULLS LAST RANGE BETWEEN 2000 FOLLOWING AND 1000 FOLLOWING
);
SELECT max (a) OVER win FROM t2
WINDOW win AS (
ORDER BY b NULLS FIRST RANGE BETWEEN 1000 PRECEDING AND 2000 PRECEDING
);
SELECT max (a) OVER win FROM t2
WINDOW win AS (
ORDER BY b NULLS FIRST RANGE BETWEEN 2000 FOLLOWING AND 1000 FOLLOWING
);
DROP TABLE IF EXISTS tx;
CREATE TABLE tx(a INTEGER PRIMARY KEY);
INSERT INTO tx VALUES(1), (2), (3), (4), (5), (6);
DROP TABLE IF EXISTS map;
CREATE TABLE map(v INTEGER PRIMARY KEY, t TEXT);
INSERT INTO map VALUES
(1, 'odd'), (2, 'even'), (3, 'odd'),
(4, 'even'), (5, 'odd'), (6, 'even');
SELECT sum(a) OVER (
PARTITION BY (
SELECT t FROM map WHERE v=a
) ORDER BY a
) FROM tx;
SELECT sum(a) OVER win FROM tx
WINDOW win AS (
PARTITION BY (
SELECT t FROM map WHERE v=a
) ORDER BY a
);
WITH map2 AS (
SELECT * FROM map
)
SELECT sum(a) OVER (
PARTITION BY (
SELECT t FROM map2 WHERE v=a
) ORDER BY a
) FROM tx;
WITH map2 AS (
SELECT * FROM map
)
SELECT sum(a) OVER win FROM tx
WINDOW win AS (
PARTITION BY (
SELECT t FROM map2 WHERE v=a
) ORDER BY a
);
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
CREATE TABLE t1(a INTEGER);
CREATE TABLE t2(y INTEGER);
SELECT (
SELECT max(a) OVER ( ORDER BY (SELECT sum(a) FROM t1) )
+ min(a) OVER()
)
FROM t1
View Git Blame Copy Permalink