1235 lines
33 KiB
Plaintext
1235 lines
33 KiB
Plaintext
# Test of SQL window functions LEAD/LAG
|
|
# ----------------------------------------------------------------------
|
|
SET NAMES utf8mb4;
|
|
SELECT LEAD(6, 0) OVER ();
|
|
LEAD(6, 0) OVER ()
|
|
6
|
|
SELECT LEAD(NULL, 0) OVER ();
|
|
LEAD(NULL, 0) OVER ()
|
|
NULL
|
|
SELECT LEAD(6, 0) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);
|
|
LEAD(6, 0) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
|
|
6
|
|
SELECT LEAD(NULL, 0) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);
|
|
LEAD(NULL, 0) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
|
|
NULL
|
|
SELECT LEAD(6, 1) OVER ();
|
|
LEAD(6, 1) OVER ()
|
|
NULL
|
|
SELECT LEAD(NULL, 1) OVER ();
|
|
LEAD(NULL, 1) OVER ()
|
|
NULL
|
|
SELECT LEAD(6, 1) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);
|
|
LEAD(6, 1) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
|
|
NULL
|
|
SELECT LEAD(NULL, 1) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);
|
|
LEAD(NULL, 1) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
|
|
NULL
|
|
SELECT LEAD(6, 1, 7) OVER ();
|
|
LEAD(6, 1, 7) OVER ()
|
|
7
|
|
SELECT LEAD(NULL, 1, 7) OVER ();
|
|
LEAD(NULL, 1, 7) OVER ()
|
|
7
|
|
SELECT LEAD(6, 1, 7) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);
|
|
LEAD(6, 1, 7) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
|
|
7
|
|
SELECT LEAD(NULL, 1, 7) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);
|
|
LEAD(NULL, 1, 7) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
|
|
7
|
|
SELECT LAG(6, 0) OVER ();
|
|
LAG(6, 0) OVER ()
|
|
6
|
|
SELECT LAG(NULL, 0) OVER ();
|
|
LAG(NULL, 0) OVER ()
|
|
NULL
|
|
SELECT LAG(6, 0) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);
|
|
LAG(6, 0) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
|
|
6
|
|
SELECT LAG(NULL, 0) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);
|
|
LAG(NULL, 0) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
|
|
NULL
|
|
SELECT LAG(6, 1) OVER ();
|
|
LAG(6, 1) OVER ()
|
|
NULL
|
|
SELECT LAG(NULL, 1) OVER ();
|
|
LAG(NULL, 1) OVER ()
|
|
NULL
|
|
SELECT LAG(6, 1) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);
|
|
LAG(6, 1) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
|
|
NULL
|
|
SELECT LAG(NULL, 1) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);
|
|
LAG(NULL, 1) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
|
|
NULL
|
|
SELECT LAG(6, 1, 7) OVER ();
|
|
LAG(6, 1, 7) OVER ()
|
|
7
|
|
SELECT LAG(NULL, 1, 7) OVER ();
|
|
LAG(NULL, 1, 7) OVER ()
|
|
7
|
|
SELECT LAG(6, 1, 7) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);
|
|
LAG(6, 1, 7) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
|
|
7
|
|
SELECT LAG(NULL, 1, 7) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);
|
|
LAG(NULL, 1, 7) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
|
|
7
|
|
CREATE TABLE t1 (d DOUBLE, id INT, sex CHAR(1), n INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(n));
|
|
INSERT INTO t1(d, id, sex) VALUES (1.0, 1, 'M'),
|
|
(2.0, 2, 'F'),
|
|
(3.0, 3, 'F'),
|
|
(4.0, 4, 'F'),
|
|
(5.0, 5, 'M'),
|
|
(NULL, NULL, 'M'),
|
|
(10.0, 10, NULL),
|
|
(10.0, 10, NULL),
|
|
(11.0, 11, NULL);
|
|
SELECT id, sex, LEAD(id, -1) RESPECT NULLS OVER () FROM t1;
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-1) RESPECT NULLS OVER () FROM t1' at line 1
|
|
SELECT id, sex, LEAD(id, 1.2) RESPECT NULLS OVER () FROM t1;
|
|
ERROR HY000: Incorrect arguments to lead
|
|
SELECT id, sex, LEAD(id, 'a') RESPECT NULLS OVER () FROM t1;
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''a') RESPECT NULLS OVER () FROM t1' at line 1
|
|
SELECT id, sex, LEAD(id, NULL) RESPECT NULLS OVER () FROM t1;
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NULL) RESPECT NULLS OVER () FROM t1' at line 1
|
|
PREPARE p FROM "SELECT id, sex, LEAD(id, ?) OVER () FROM t1";
|
|
SET @p1= 3;
|
|
EXECUTE p USING @p1;
|
|
id sex LEAD(id, ?) OVER ()
|
|
1 M 4
|
|
2 F 5
|
|
3 F NULL
|
|
4 F 10
|
|
5 M 10
|
|
NULL M 11
|
|
10 NULL NULL
|
|
10 NULL NULL
|
|
11 NULL NULL
|
|
SET @p1= -3;
|
|
EXECUTE p USING @p1;
|
|
id sex LEAD(id, ?) OVER ()
|
|
1 M NULL
|
|
2 F NULL
|
|
3 F NULL
|
|
4 F 1
|
|
5 M 2
|
|
NULL M 3
|
|
10 NULL 4
|
|
10 NULL 5
|
|
11 NULL NULL
|
|
SET @p1= 'a';
|
|
EXECUTE p USING @p1;
|
|
ERROR HY000: Incorrect arguments to lead
|
|
PREPARE p FROM "SELECT id, sex, LEAD(id+?, ?, ?) RESPECT NULLS OVER () FROM t1";
|
|
SET @p1= 8;
|
|
SET @p2= 3;
|
|
SET @p3= 7;
|
|
EXECUTE p USING @p1, @p2, @p3;
|
|
id sex LEAD(id+?, ?, ?) RESPECT NULLS OVER ()
|
|
1 M 12
|
|
2 F 13
|
|
3 F NULL
|
|
4 F 18
|
|
5 M 18
|
|
NULL M 19
|
|
10 NULL 7
|
|
10 NULL 7
|
|
11 NULL 7
|
|
EXECUTE p USING @p1, @p2, @p3;
|
|
id sex LEAD(id+?, ?, ?) RESPECT NULLS OVER ()
|
|
1 M 12
|
|
2 F 13
|
|
3 F NULL
|
|
4 F 18
|
|
5 M 18
|
|
NULL M 19
|
|
10 NULL 7
|
|
10 NULL 7
|
|
11 NULL 7
|
|
DROP PREPARE p;
|
|
SELECT id, sex, LEAD(id, 1, 10) IGNORE NULLS OVER () FROM t1;
|
|
ERROR 42000: This version of MySQL doesn't yet support 'IGNORE NULLS'
|
|
Ok, default semantics:
|
|
First without default:
|
|
SELECT id, sex, LEAD(id, 1) RESPECT NULLS OVER () FROM t1;
|
|
id sex LEAD(id, 1) RESPECT NULLS OVER ()
|
|
1 M 2
|
|
2 F 3
|
|
3 F 4
|
|
4 F 5
|
|
5 M NULL
|
|
NULL M 10
|
|
10 NULL 10
|
|
10 NULL 11
|
|
11 NULL NULL
|
|
SELECT id, sex, LAG(id, 1) RESPECT NULLS OVER () FROM t1;
|
|
id sex LAG(id, 1) RESPECT NULLS OVER ()
|
|
1 M NULL
|
|
2 F 1
|
|
3 F 2
|
|
4 F 3
|
|
5 M 4
|
|
NULL M 5
|
|
10 NULL NULL
|
|
10 NULL 10
|
|
11 NULL 10
|
|
SELECT id, sex, LEAD(id, 0) RESPECT NULLS OVER () FROM t1;
|
|
id sex LEAD(id, 0) RESPECT NULLS OVER ()
|
|
1 M 1
|
|
2 F 2
|
|
3 F 3
|
|
4 F 4
|
|
5 M 5
|
|
NULL M NULL
|
|
10 NULL 10
|
|
10 NULL 10
|
|
11 NULL 11
|
|
Now with default:
|
|
SELECT id, sex, LEAD(id, 1, id) RESPECT NULLS OVER () FROM t1;
|
|
id sex LEAD(id, 1, id) RESPECT NULLS OVER ()
|
|
1 M 2
|
|
2 F 3
|
|
3 F 4
|
|
4 F 5
|
|
5 M NULL
|
|
NULL M 10
|
|
10 NULL 10
|
|
10 NULL 11
|
|
11 NULL 11
|
|
SELECT id, sex, LAG(id, 1, id) RESPECT NULLS OVER () FROM t1;
|
|
id sex LAG(id, 1, id) RESPECT NULLS OVER ()
|
|
1 M 1
|
|
2 F 1
|
|
3 F 2
|
|
4 F 3
|
|
5 M 4
|
|
NULL M 5
|
|
10 NULL NULL
|
|
10 NULL 10
|
|
11 NULL 10
|
|
SELECT id, sex, LEAD(id, 0, 7) RESPECT NULLS OVER () FROM t1;
|
|
id sex LEAD(id, 0, 7) RESPECT NULLS OVER ()
|
|
1 M 1
|
|
2 F 2
|
|
3 F 3
|
|
4 F 4
|
|
5 M 5
|
|
NULL M NULL
|
|
10 NULL 10
|
|
10 NULL 10
|
|
11 NULL 11
|
|
SELECT n, id, LEAD(id, 1, 3) OVER
|
|
(ORDER BY id DESC, n ASC ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) L FROM t1;
|
|
n id L
|
|
9 11 10
|
|
7 10 10
|
|
8 10 5
|
|
5 5 4
|
|
4 4 3
|
|
3 3 2
|
|
2 2 1
|
|
1 1 NULL
|
|
6 NULL 3
|
|
SELECT n, id, LAG(id, 0, n*n) OVER
|
|
(ORDER BY id DESC, n ASC ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) L FROM t1;
|
|
n id L
|
|
9 11 11
|
|
7 10 10
|
|
8 10 10
|
|
5 5 5
|
|
4 4 4
|
|
3 3 3
|
|
2 2 2
|
|
1 1 1
|
|
6 NULL NULL
|
|
SELECT n, id, LAG(id, 1, n*n) OVER
|
|
(ORDER BY id DESC, n ASC ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) L FROM t1;
|
|
n id L
|
|
9 11 81
|
|
7 10 11
|
|
8 10 10
|
|
5 5 10
|
|
4 4 5
|
|
3 3 4
|
|
2 2 3
|
|
1 1 2
|
|
6 NULL 1
|
|
SELECT n, id, LEAD(id, 1, n*n) OVER
|
|
(ORDER BY id DESC, n ASC ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) L FROM t1;
|
|
n id L
|
|
9 11 10
|
|
7 10 10
|
|
8 10 5
|
|
5 5 4
|
|
4 4 3
|
|
3 3 2
|
|
2 2 1
|
|
1 1 NULL
|
|
6 NULL 36
|
|
Check imcompatible character sets
|
|
CREATE TABLE t (c1 CHAR(10) CHARACTER SET big5,
|
|
i INT,
|
|
c2 VARCHAR(10) CHARACTER SET euckr);
|
|
SELECT c1, c2, LEAD(c1, 0, c2) OVER () l0 FROM t;
|
|
ERROR HY000: Illegal mix of collations (big5_chinese_ci,IMPLICIT) and (euckr_korean_ci,IMPLICIT) for operation 'lead'
|
|
DROP TABLE t;
|
|
Check default char set & collation promotion to result
|
|
CREATE TABLE t (c1 CHAR(10) CHARACTER SET utf8mb4,
|
|
i INT,
|
|
c2 VARCHAR(10) CHARACTER SET latin1);
|
|
INSERT INTO t VALUES('A', 1, '1');
|
|
INSERT INTO t VALUES('A', 3, '3');
|
|
INSERT INTO t VALUES(x'F09F90AC' /* dolphin */, 5, null);
|
|
INSERT INTO t VALUES('A', 5, null);
|
|
INSERT INTO t VALUES(null, 10, '0');
|
|
Result sets should contain dolphin in columns c1 and l1.
|
|
SELECT c1, c2, LEAD(c1, 0, c2) OVER () l0 FROM t;
|
|
c1 c2 l0
|
|
A 1 A
|
|
A 3 A
|
|
🐬 NULL 🐬
|
|
A NULL A
|
|
NULL 0 NULL
|
|
SELECT c1, c2, LEAD(c1, 1, c2) OVER () l1 FROM t;
|
|
c1 c2 l1
|
|
A 1 A
|
|
A 3 🐬
|
|
🐬 NULL A
|
|
A NULL NULL
|
|
NULL 0 0
|
|
SELECT c1, c2, LEAD(c2, 1, c1) OVER () l1 FROM t;
|
|
c1 c2 l1
|
|
A 1 3
|
|
A 3 NULL
|
|
🐬 NULL NULL
|
|
A NULL 0
|
|
NULL 0 NULL
|
|
Use CREATE TABLE AS to show type of the resulting LEAD function
|
|
CREATE TABLE tt AS SELECT LEAD(c1, 0, c2) OVER () c FROM t;
|
|
CREATE TABLE ts AS SELECT LEAD(c2, 1, c1) OVER () c FROM t;
|
|
Both tables should have c as VARCHAR(10) CHARACTER SET utf8mb4
|
|
even though only c1 has that type.
|
|
SHOW CREATE TABLE tt;
|
|
Table Create Table
|
|
tt CREATE TABLE `tt` (
|
|
`c` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL
|
|
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
SHOW CREATE TABLE ts;
|
|
Table Create Table
|
|
ts CREATE TABLE `ts` (
|
|
`c` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL
|
|
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
DROP TABLE t, tt, ts;
|
|
Defaults: check non obvious type pairs, comparing with IFNULL
|
|
whose type reconciliation behavior we emulate when combining
|
|
types in LEAD/LAG with default value.
|
|
static wf
|
|
SELECT id, sex, COUNT(*) OVER w cnt, NTILE(3) OVER w `ntile`,
|
|
LEAD(id, 1) OVER w le1,
|
|
LAG(id, 1) OVER w la1,
|
|
LEAD(id, 100) OVER w le100,
|
|
LAG(id, 2, 777) OVER w la2 FROM t1
|
|
WINDOW w AS (ORDER BY id);
|
|
id sex cnt ntile le1 la1 le100 la2
|
|
NULL M 1 1 1 NULL NULL 777
|
|
1 M 2 1 2 NULL NULL 777
|
|
2 F 3 1 3 1 NULL NULL
|
|
3 F 4 2 4 2 NULL 1
|
|
4 F 5 2 5 3 NULL 2
|
|
5 M 6 2 10 4 NULL 3
|
|
10 NULL 8 3 10 5 NULL 4
|
|
10 NULL 8 3 11 10 NULL 5
|
|
11 NULL 9 3 NULL 10 NULL 10
|
|
SELECT id, sex, COUNT(*) OVER w cnt, NTH_VALUE(id, 2) OVER w nth,
|
|
LEAD(id, 1) OVER w le1,
|
|
LAG(id, 1) OVER w la1,
|
|
LEAD(id, 100) OVER w le100,
|
|
LAG(id, 2, 777) OVER w la2 FROM t1
|
|
WINDOW w as (PARTITION BY sex ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);
|
|
id sex cnt nth le1 la1 le100 la2
|
|
10 NULL 3 10 10 NULL NULL 777
|
|
10 NULL 3 10 11 10 NULL 777
|
|
11 NULL 3 10 NULL 10 NULL 10
|
|
2 F 3 3 3 NULL NULL 777
|
|
3 F 3 3 4 2 NULL 777
|
|
4 F 3 3 NULL 3 NULL 2
|
|
NULL M 3 1 1 NULL NULL 777
|
|
1 M 3 1 5 NULL NULL 777
|
|
5 M 3 1 NULL 1 NULL NULL
|
|
SELECT id, sex, COUNT(*) OVER w cnt, NTH_VALUE(id, 2) OVER w nth,
|
|
LEAD(id, 1) OVER w le1,
|
|
LAG(id, 1) OVER w la1,
|
|
LEAD(id, 100) OVER w le100,
|
|
LAG(id, 2, 777) OVER w la2 FROM t1
|
|
WINDOW w as (PARTITION BY id ORDER BY sex ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);
|
|
id sex cnt nth le1 la1 le100 la2
|
|
NULL M 1 NULL NULL NULL NULL 777
|
|
1 M 1 NULL NULL NULL NULL 777
|
|
2 F 1 NULL NULL NULL NULL 777
|
|
3 F 1 NULL NULL NULL NULL 777
|
|
4 F 1 NULL NULL NULL NULL 777
|
|
5 M 1 NULL NULL NULL NULL 777
|
|
10 NULL 2 10 10 NULL NULL 777
|
|
10 NULL 2 10 NULL 10 NULL 777
|
|
11 NULL 1 NULL NULL NULL NULL 777
|
|
unbuffered
|
|
SELECT id, sex, COUNT(*) OVER w cnt,
|
|
LEAD(id, 1) OVER w le1,
|
|
LAG(id, 1) OVER w la1,
|
|
LEAD(id, 100) OVER w le100,
|
|
LAG(id, 2, 777) OVER w la2 FROM t1
|
|
WINDOW w as (PARTITION BY SEX ORDER BY ID ROWS UNBOUNDED PRECEDING);
|
|
id sex cnt le1 la1 le100 la2
|
|
10 NULL 1 10 NULL NULL 777
|
|
10 NULL 2 11 10 NULL 777
|
|
11 NULL 3 NULL 10 NULL 10
|
|
2 F 1 3 NULL NULL 777
|
|
3 F 2 4 2 NULL 777
|
|
4 F 3 NULL 3 NULL 2
|
|
NULL M 1 1 NULL NULL 777
|
|
1 M 2 5 NULL NULL 777
|
|
5 M 3 NULL 1 NULL NULL
|
|
SELECT id, sex, COUNT(*) OVER w cnt, NTH_VALUE(id, 2) OVER w nth,
|
|
LEAD(id, 2) OVER w le2,
|
|
LAG(id, 2) OVER w la2 FROM t1
|
|
WINDOW w as (PARTITION BY SEX ORDER BY ID RANGE UNBOUNDED PRECEDING);
|
|
id sex cnt nth le2 la2
|
|
10 NULL 2 10 11 NULL
|
|
10 NULL 2 10 NULL NULL
|
|
11 NULL 3 10 NULL 10
|
|
2 F 1 NULL 4 NULL
|
|
3 F 2 3 NULL NULL
|
|
4 F 3 3 NULL 2
|
|
NULL M 1 NULL 5 NULL
|
|
1 M 2 1 NULL NULL
|
|
5 M 3 1 NULL NULL
|
|
test unoptimized path: trick: add DOUBLE type w/SUM which is unoptimized by default
|
|
ascending
|
|
SELECT d, SUM(d) OVER w `sum`, sex,
|
|
LEAD(d, 2) OVER w le2,
|
|
LAG(d, 2) OVER w la2 FROM t1
|
|
WINDOW w as (PARTITION BY SEX ORDER BY d ROWS 2 PRECEDING);
|
|
d sum sex le2 la2
|
|
10 10 NULL 11 NULL
|
|
10 20 NULL NULL NULL
|
|
11 31 NULL NULL 10
|
|
2 2 F 4 NULL
|
|
3 5 F NULL NULL
|
|
4 9 F NULL 2
|
|
NULL NULL M 5 NULL
|
|
1 1 M NULL NULL
|
|
5 6 M NULL NULL
|
|
SELECT d, SUM(d) OVER w `sum`, sex,
|
|
LEAD(d, 2) OVER w le2,
|
|
LAG(d, 2) OVER w la2 FROM t1
|
|
WINDOW w as (PARTITION BY SEX ORDER BY d RANGE 2 PRECEDING);
|
|
d sum sex le2 la2
|
|
10 20 NULL 11 NULL
|
|
10 20 NULL NULL NULL
|
|
11 31 NULL NULL 10
|
|
2 2 F 4 NULL
|
|
3 5 F NULL NULL
|
|
4 9 F NULL 2
|
|
NULL NULL M 5 NULL
|
|
1 1 M NULL NULL
|
|
5 5 M NULL NULL
|
|
SELECT d, SUM(d) OVER w `sum`, COUNT(*) OVER w cnt,
|
|
LEAD(d, 2) OVER w le2,
|
|
LAG(d, 2) OVER w la2 FROM t1
|
|
WINDOW w as (ORDER BY d ROWS 2 PRECEDING);
|
|
d sum cnt le2 la2
|
|
NULL NULL 1 2 NULL
|
|
1 1 2 3 NULL
|
|
2 3 3 4 NULL
|
|
3 6 3 5 1
|
|
4 9 3 10 2
|
|
5 12 3 10 3
|
|
10 19 3 11 4
|
|
10 25 3 NULL 5
|
|
11 31 3 NULL 10
|
|
SELECT d, SUM(d) OVER w `sum`, COUNT(*) OVER w cnt,
|
|
LEAD(d, 2) OVER w le2,
|
|
LAG(d, 2) OVER w la2 FROM t1
|
|
WINDOW w as (ORDER BY d ASC ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING);
|
|
d sum cnt le2 la2
|
|
NULL NULL 0 2 NULL
|
|
1 NULL 1 3 NULL
|
|
2 1 2 4 NULL
|
|
3 3 2 5 1
|
|
4 5 2 10 2
|
|
5 7 2 10 3
|
|
10 9 2 11 4
|
|
10 15 2 NULL 5
|
|
11 20 2 NULL 10
|
|
SELECT d, SUM(d) OVER w `sum`, COUNT(*) OVER w cnt,
|
|
LEAD(d, 2) OVER w le2,
|
|
LAG(d, 2) OVER w la2 FROM t1
|
|
WINDOW w as (ORDER BY d ASC ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING);
|
|
d sum cnt le2 la2
|
|
NULL 3 2 2 NULL
|
|
1 5 2 3 NULL
|
|
2 7 2 4 NULL
|
|
3 9 2 5 1
|
|
4 15 2 10 2
|
|
5 20 2 10 3
|
|
10 21 2 11 4
|
|
10 11 1 NULL 5
|
|
11 NULL 0 NULL 10
|
|
SELECT d, SUM(d) OVER w `sum`, COUNT(*) OVER w cnt,
|
|
LEAD(d, 2) OVER w le2,
|
|
LAG(d, 2) OVER w la2 FROM t1
|
|
WINDOW w as (ORDER BY d RANGE 2 PRECEDING);
|
|
d sum cnt le2 la2
|
|
NULL NULL 1 2 NULL
|
|
1 1 1 3 NULL
|
|
2 3 2 4 NULL
|
|
3 6 3 5 1
|
|
4 9 3 10 2
|
|
5 12 3 10 3
|
|
10 20 2 11 4
|
|
10 20 2 NULL 5
|
|
11 31 3 NULL 10
|
|
SELECT d, SUM(d) OVER w `sum`, COUNT(*) OVER w cnt,
|
|
LEAD(d, 2) OVER w le2,
|
|
LAG(d, 2) OVER w la2 FROM t1
|
|
WINDOW w as (ORDER BY d RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING);
|
|
d sum cnt le2 la2
|
|
NULL NULL 1 2 NULL
|
|
1 3 2 3 NULL
|
|
2 6 3 4 NULL
|
|
3 9 3 5 1
|
|
4 12 3 10 2
|
|
5 9 2 10 3
|
|
10 31 3 11 4
|
|
10 31 3 NULL 5
|
|
11 31 3 NULL 10
|
|
SELECT d, SUM(d) OVER w `sum`, COUNT(*) OVER w cnt,
|
|
LEAD(d, 2) OVER w le2,
|
|
LAG(d, 2) OVER w la2 FROM t1
|
|
WINDOW w as (ORDER BY d RANGE BETWEEN 1 FOLLOWING AND 2 FOLLOWING);
|
|
d sum cnt le2 la2
|
|
NULL NULL 1 2 NULL
|
|
1 5 2 3 NULL
|
|
2 7 2 4 NULL
|
|
3 9 2 5 1
|
|
4 5 1 10 2
|
|
5 NULL 0 10 3
|
|
10 11 1 11 4
|
|
10 11 1 NULL 5
|
|
11 NULL 0 NULL 10
|
|
descending
|
|
SELECT d, SUM(d) OVER w `sum`, COUNT(*) OVER w cnt, sex,
|
|
LEAD(d, 2) OVER w le2,
|
|
LAG(d, 2) OVER w la2 FROM t1
|
|
WINDOW w as (PARTITION BY SEX ORDER BY d DESC ROWS 2 PRECEDING);
|
|
d sum cnt sex le2 la2
|
|
11 11 1 NULL 10 NULL
|
|
10 21 2 NULL NULL NULL
|
|
10 31 3 NULL NULL 11
|
|
4 4 1 F 2 NULL
|
|
3 7 2 F NULL NULL
|
|
2 9 3 F NULL 4
|
|
5 5 1 M NULL NULL
|
|
1 6 2 M NULL NULL
|
|
NULL 6 3 M NULL 5
|
|
SELECT d, SUM(d) OVER w `sum`, COUNT(*) OVER w cnt, sex,
|
|
LEAD(d, 2) OVER w le2,
|
|
LAG(d, 2) OVER w la2 FROM t1
|
|
WINDOW w as (PARTITION BY SEX ORDER BY d DESC RANGE 2 PRECEDING);
|
|
d sum cnt sex le2 la2
|
|
11 11 1 NULL 10 NULL
|
|
10 31 3 NULL NULL NULL
|
|
10 31 3 NULL NULL 11
|
|
4 4 1 F 2 NULL
|
|
3 7 2 F NULL NULL
|
|
2 9 3 F NULL 4
|
|
5 5 1 M NULL NULL
|
|
1 1 1 M NULL NULL
|
|
NULL NULL 1 M NULL 5
|
|
SELECT d, SUM(d) OVER w `sum`, COUNT(*) OVER w cnt, sex,
|
|
LEAD(d, 2) OVER w le2,
|
|
LAG(d, 2) OVER w la2 FROM t1
|
|
WINDOW w as (ORDER BY d DESC ROWS 2 PRECEDING);
|
|
d sum cnt sex le2 la2
|
|
11 11 1 NULL 10 NULL
|
|
10 21 2 NULL 5 NULL
|
|
10 31 3 NULL 4 11
|
|
5 25 3 M 3 10
|
|
4 19 3 F 2 10
|
|
3 12 3 F 1 5
|
|
2 9 3 F NULL 4
|
|
1 6 3 M NULL 3
|
|
NULL 3 3 M NULL 2
|
|
SELECT d, SUM(d) OVER w `sum`, COUNT(*) OVER w cnt, sex,
|
|
LEAD(d, 2) OVER w le2,
|
|
LAG(d, 2) OVER w la2 FROM t1
|
|
WINDOW w as (ORDER BY d DESC ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING);
|
|
d sum cnt sex le2 la2
|
|
11 NULL 0 NULL 10 NULL
|
|
10 11 1 NULL 5 NULL
|
|
10 21 2 NULL 4 11
|
|
5 20 2 M 3 10
|
|
4 15 2 F 2 10
|
|
3 9 2 F 1 5
|
|
2 7 2 F NULL 4
|
|
1 5 2 M NULL 3
|
|
NULL 3 2 M NULL 2
|
|
SELECT d, SUM(d) OVER w `sum`, COUNT(*) OVER w cnt, sex,
|
|
LEAD(d, 2) OVER w le2,
|
|
LAG(d, 2) OVER w la2 FROM t1
|
|
WINDOW w as (ORDER BY d DESC ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING);
|
|
d sum cnt sex le2 la2
|
|
11 20 2 NULL 10 NULL
|
|
10 15 2 NULL 5 NULL
|
|
10 9 2 NULL 4 11
|
|
5 7 2 M 3 10
|
|
4 5 2 F 2 10
|
|
3 3 2 F 1 5
|
|
2 1 2 F NULL 4
|
|
1 NULL 1 M NULL 3
|
|
NULL NULL 0 M NULL 2
|
|
SELECT d, SUM(d) OVER w `sum`, COUNT(*) OVER w cnt, sex,
|
|
LEAD(d, 2) OVER w le2,
|
|
LAG(d, 2) OVER w la2 FROM t1
|
|
WINDOW w as (ORDER BY d DESC RANGE 2 PRECEDING);
|
|
d sum cnt sex le2 la2
|
|
11 11 1 NULL 10 NULL
|
|
10 31 3 NULL 5 NULL
|
|
10 31 3 NULL 4 11
|
|
5 5 1 M 3 10
|
|
4 9 2 F 2 10
|
|
3 12 3 F 1 5
|
|
2 9 3 F NULL 4
|
|
1 6 3 M NULL 3
|
|
NULL NULL 1 M NULL 2
|
|
SELECT d, SUM(d) OVER w `sum`, COUNT(*) OVER w cnt, sex,
|
|
LEAD(d, 2) OVER w le2,
|
|
LAG(d, 2) OVER w la2 FROM t1
|
|
WINDOW w as (ORDER BY d DESC RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING);
|
|
d sum cnt sex le2 la2
|
|
11 31 3 NULL 10 NULL
|
|
10 31 3 NULL 5 NULL
|
|
10 31 3 NULL 4 11
|
|
5 9 2 M 3 10
|
|
4 12 3 F 2 10
|
|
3 9 3 F 1 5
|
|
2 6 3 F NULL 4
|
|
1 3 2 M NULL 3
|
|
NULL NULL 1 M NULL 2
|
|
SELECT d, SUM(d) OVER w `sum`, COUNT(*) OVER w cnt, sex,
|
|
LEAD(d, 2) OVER w le2,
|
|
LAG(d, 2) OVER w la2 FROM t1
|
|
WINDOW w as (ORDER BY d DESC RANGE BETWEEN 1 FOLLOWING AND 2 FOLLOWING);
|
|
d sum cnt sex le2 la2
|
|
11 20 2 NULL 10 NULL
|
|
10 NULL 0 NULL 5 NULL
|
|
10 NULL 0 NULL 4 11
|
|
5 7 2 M 3 10
|
|
4 5 2 F 2 10
|
|
3 3 2 F 1 5
|
|
2 1 1 F NULL 4
|
|
1 NULL 0 M NULL 3
|
|
NULL NULL 1 M NULL 2
|
|
Dynamic upper
|
|
SELECT id, sex, COUNT(*) OVER w cnt, NTILE(3) OVER w `ntile`,
|
|
LEAD(id, 2) OVER w le2,
|
|
LAG(id, 2) OVER w la2 FROM t1 WINDOW w as (ORDER BY id);
|
|
id sex cnt ntile le2 la2
|
|
NULL M 1 1 2 NULL
|
|
1 M 2 1 3 NULL
|
|
2 F 3 1 4 NULL
|
|
3 F 4 2 5 1
|
|
4 F 5 2 10 2
|
|
5 M 6 2 10 3
|
|
10 NULL 8 3 11 4
|
|
10 NULL 8 3 NULL 5
|
|
11 NULL 9 3 NULL 10
|
|
optimized path
|
|
ascending
|
|
SELECT id, SUM(id) OVER w `sum`, sex,
|
|
LEAD(id, 2) OVER w le2,
|
|
LAG(id, 2) OVER w la2 FROM t1
|
|
WINDOW w as (PARTITION BY SEX ORDER BY id ROWS 2 PRECEDING);
|
|
id sum sex le2 la2
|
|
10 10 NULL 11 NULL
|
|
10 20 NULL NULL NULL
|
|
11 31 NULL NULL 10
|
|
2 2 F 4 NULL
|
|
3 5 F NULL NULL
|
|
4 9 F NULL 2
|
|
NULL NULL M 5 NULL
|
|
1 1 M NULL NULL
|
|
5 6 M NULL NULL
|
|
SELECT id, SUM(id) OVER w `sum`, sex,
|
|
LEAD(id, 2) OVER w le2,
|
|
LAG(id, 2) OVER w la2 FROM t1
|
|
WINDOW w as (PARTITION BY SEX ORDER BY id RANGE 2 PRECEDING);
|
|
id sum sex le2 la2
|
|
10 20 NULL 11 NULL
|
|
10 20 NULL NULL NULL
|
|
11 31 NULL NULL 10
|
|
2 2 F 4 NULL
|
|
3 5 F NULL NULL
|
|
4 9 F NULL 2
|
|
NULL NULL M 5 NULL
|
|
1 1 M NULL NULL
|
|
5 5 M NULL NULL
|
|
SELECT id, SUM(id) OVER w `sum`, COUNT(*) OVER w cnt,
|
|
LEAD(id, 2) OVER w le2,
|
|
LAG(id, 2) OVER w la2 FROM t1
|
|
WINDOW w as (ORDER BY id ROWS 2 PRECEDING);
|
|
id sum cnt le2 la2
|
|
NULL NULL 1 2 NULL
|
|
1 1 2 3 NULL
|
|
2 3 3 4 NULL
|
|
3 6 3 5 1
|
|
4 9 3 10 2
|
|
5 12 3 10 3
|
|
10 19 3 11 4
|
|
10 25 3 NULL 5
|
|
11 31 3 NULL 10
|
|
SELECT id, SUM(id) OVER w `sum`, COUNT(*) OVER w cnt,
|
|
LEAD(id, 2) OVER w le2,
|
|
LAG(id, 2) OVER w la2 FROM t1
|
|
WINDOW w as (ORDER BY id ASC ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING);
|
|
id sum cnt le2 la2
|
|
NULL NULL 0 2 NULL
|
|
1 NULL 1 3 NULL
|
|
2 1 2 4 NULL
|
|
3 3 2 5 1
|
|
4 5 2 10 2
|
|
5 7 2 10 3
|
|
10 9 2 11 4
|
|
10 15 2 NULL 5
|
|
11 20 2 NULL 10
|
|
SELECT id, SUM(id) OVER w `sum`, COUNT(*) OVER w cnt,
|
|
LEAD(id, 2) OVER w le2,
|
|
LAG(id, 2) OVER w la2 FROM t1
|
|
WINDOW w as (ORDER BY id ASC ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING);
|
|
id sum cnt le2 la2
|
|
NULL 3 2 2 NULL
|
|
1 5 2 3 NULL
|
|
2 7 2 4 NULL
|
|
3 9 2 5 1
|
|
4 15 2 10 2
|
|
5 20 2 10 3
|
|
10 21 2 11 4
|
|
10 11 1 NULL 5
|
|
11 NULL 0 NULL 10
|
|
SELECT id, SUM(id) OVER w `sum`, COUNT(*) OVER w cnt,
|
|
LEAD(id, 2) OVER w le2,
|
|
LAG(id, 2) OVER w la2 FROM t1
|
|
WINDOW w as (ORDER BY id RANGE 2 PRECEDING);
|
|
id sum cnt le2 la2
|
|
NULL NULL 1 2 NULL
|
|
1 1 1 3 NULL
|
|
2 3 2 4 NULL
|
|
3 6 3 5 1
|
|
4 9 3 10 2
|
|
5 12 3 10 3
|
|
10 20 2 11 4
|
|
10 20 2 NULL 5
|
|
11 31 3 NULL 10
|
|
SELECT id, SUM(id) OVER w `sum`, COUNT(*) OVER w cnt,
|
|
LEAD(id, 2) OVER w le2,
|
|
LAG(id, 2) OVER w la2 FROM t1
|
|
WINDOW w as (ORDER BY id RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING);
|
|
id sum cnt le2 la2
|
|
NULL NULL 1 2 NULL
|
|
1 3 2 3 NULL
|
|
2 6 3 4 NULL
|
|
3 9 3 5 1
|
|
4 12 3 10 2
|
|
5 9 2 10 3
|
|
10 31 3 11 4
|
|
10 31 3 NULL 5
|
|
11 31 3 NULL 10
|
|
SELECT id, SUM(id) OVER w `sum`, COUNT(*) OVER w cnt,
|
|
LEAD(id, 2) OVER w le2,
|
|
LAG(id, 2) OVER w la2 FROM t1
|
|
WINDOW w as (ORDER BY id RANGE BETWEEN 1 FOLLOWING AND 2 FOLLOWING);
|
|
id sum cnt le2 la2
|
|
NULL NULL 1 2 NULL
|
|
1 5 2 3 NULL
|
|
2 7 2 4 NULL
|
|
3 9 2 5 1
|
|
4 5 1 10 2
|
|
5 NULL 0 10 3
|
|
10 11 1 11 4
|
|
10 11 1 NULL 5
|
|
11 NULL 0 NULL 10
|
|
descending
|
|
SELECT id, SUM(id) OVER w `sum`, COUNT(*) OVER w cnt, sex,
|
|
LEAD(id, 2) OVER w le2,
|
|
LAG(id, 2) OVER w la2 FROM t1
|
|
WINDOW w as (PARTITION BY SEX ORDER BY id DESC ROWS 2 PRECEDING);
|
|
id sum cnt sex le2 la2
|
|
11 11 1 NULL 10 NULL
|
|
10 21 2 NULL NULL NULL
|
|
10 31 3 NULL NULL 11
|
|
4 4 1 F 2 NULL
|
|
3 7 2 F NULL NULL
|
|
2 9 3 F NULL 4
|
|
5 5 1 M NULL NULL
|
|
1 6 2 M NULL NULL
|
|
NULL 6 3 M NULL 5
|
|
SELECT id, SUM(id) OVER w `sum`, COUNT(*) OVER w cnt, sex,
|
|
LEAD(id, 2) OVER w le2,
|
|
LAG(id, 2) OVER w la2 FROM t1
|
|
WINDOW w as (PARTITION BY SEX ORDER BY id DESC RANGE 2 PRECEDING);
|
|
id sum cnt sex le2 la2
|
|
11 11 1 NULL 10 NULL
|
|
10 31 3 NULL NULL NULL
|
|
10 31 3 NULL NULL 11
|
|
4 4 1 F 2 NULL
|
|
3 7 2 F NULL NULL
|
|
2 9 3 F NULL 4
|
|
5 5 1 M NULL NULL
|
|
1 1 1 M NULL NULL
|
|
NULL NULL 1 M NULL 5
|
|
SELECT id, SUM(id) OVER w `sum`, COUNT(*) OVER w cnt, sex,
|
|
LEAD(id, 2) OVER w le2,
|
|
LAG(id, 2) OVER w la2 FROM t1
|
|
WINDOW w as (ORDER BY id DESC ROWS 2 PRECEDING);
|
|
id sum cnt sex le2 la2
|
|
11 11 1 NULL 10 NULL
|
|
10 21 2 NULL 5 NULL
|
|
10 31 3 NULL 4 11
|
|
5 25 3 M 3 10
|
|
4 19 3 F 2 10
|
|
3 12 3 F 1 5
|
|
2 9 3 F NULL 4
|
|
1 6 3 M NULL 3
|
|
NULL 3 3 M NULL 2
|
|
SELECT id, SUM(id) OVER w `sum`, COUNT(*) OVER w cnt, sex,
|
|
LEAD(id, 2) OVER w le2,
|
|
LAG(id, 2) OVER w la2 FROM t1
|
|
WINDOW w as (ORDER BY id DESC ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING);
|
|
id sum cnt sex le2 la2
|
|
11 NULL 0 NULL 10 NULL
|
|
10 11 1 NULL 5 NULL
|
|
10 21 2 NULL 4 11
|
|
5 20 2 M 3 10
|
|
4 15 2 F 2 10
|
|
3 9 2 F 1 5
|
|
2 7 2 F NULL 4
|
|
1 5 2 M NULL 3
|
|
NULL 3 2 M NULL 2
|
|
SELECT id, SUM(id) OVER w `sum`, COUNT(*) OVER w cnt, sex,
|
|
LEAD(id, 2) OVER w le2,
|
|
LAG(id, 2) OVER w la2 FROM t1
|
|
WINDOW w as (ORDER BY id DESC ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING);
|
|
id sum cnt sex le2 la2
|
|
11 20 2 NULL 10 NULL
|
|
10 15 2 NULL 5 NULL
|
|
10 9 2 NULL 4 11
|
|
5 7 2 M 3 10
|
|
4 5 2 F 2 10
|
|
3 3 2 F 1 5
|
|
2 1 2 F NULL 4
|
|
1 NULL 1 M NULL 3
|
|
NULL NULL 0 M NULL 2
|
|
SELECT id, SUM(id) OVER w `sum`, COUNT(*) OVER w cnt, sex,
|
|
LEAD(id, 2) OVER w le2,
|
|
LAG(id, 2) OVER w la2 FROM t1
|
|
WINDOW w as (ORDER BY id DESC RANGE 2 PRECEDING);
|
|
id sum cnt sex le2 la2
|
|
11 11 1 NULL 10 NULL
|
|
10 31 3 NULL 5 NULL
|
|
10 31 3 NULL 4 11
|
|
5 5 1 M 3 10
|
|
4 9 2 F 2 10
|
|
3 12 3 F 1 5
|
|
2 9 3 F NULL 4
|
|
1 6 3 M NULL 3
|
|
NULL NULL 1 M NULL 2
|
|
SELECT id, SUM(id) OVER w `sum`, COUNT(*) OVER w cnt, sex,
|
|
LEAD(id, 2) OVER w le2,
|
|
LAG(id, 2) OVER w la2 FROM t1
|
|
WINDOW w as (ORDER BY id DESC RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING);
|
|
id sum cnt sex le2 la2
|
|
11 31 3 NULL 10 NULL
|
|
10 31 3 NULL 5 NULL
|
|
10 31 3 NULL 4 11
|
|
5 9 2 M 3 10
|
|
4 12 3 F 2 10
|
|
3 9 3 F 1 5
|
|
2 6 3 F NULL 4
|
|
1 3 2 M NULL 3
|
|
NULL NULL 1 M NULL 2
|
|
SELECT id, SUM(id) OVER w `sum`, COUNT(*) OVER w cnt, sex,
|
|
LEAD(id, 2) OVER w le2,
|
|
LAG(id, 2) OVER w la2 FROM t1
|
|
WINDOW w as (ORDER BY id DESC RANGE BETWEEN 1 FOLLOWING AND 2 FOLLOWING);
|
|
id sum cnt sex le2 la2
|
|
11 20 2 NULL 10 NULL
|
|
10 NULL 0 NULL 5 NULL
|
|
10 NULL 0 NULL 4 11
|
|
5 7 2 M 3 10
|
|
4 5 2 F 2 10
|
|
3 3 2 F 1 5
|
|
2 1 1 F NULL 4
|
|
1 NULL 0 M NULL 3
|
|
NULL NULL 1 M NULL 2
|
|
many nth_value calls on one window, unoptimized path
|
|
SELECT d, SUM(d) OVER w `sum`, COUNT(*) OVER w cnt,
|
|
LEAD(d, 3) OVER w le3,
|
|
FIRST_VALUE(d) OVER w fv,
|
|
LEAD(d, 1) OVER w le1,
|
|
LEAD(d, 2) OVER w le2,
|
|
LAG(d, 2) OVER w la2 FROM t1
|
|
WINDOW w AS (ORDER BY d ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW);
|
|
d sum cnt le3 fv le1 le2 la2
|
|
NULL NULL 1 3 NULL 1 2 NULL
|
|
1 1 2 4 NULL 2 3 NULL
|
|
2 3 3 5 NULL 3 4 NULL
|
|
3 6 3 10 1 4 5 1
|
|
4 9 3 10 2 5 10 2
|
|
5 12 3 11 3 10 10 3
|
|
10 19 3 NULL 4 10 11 4
|
|
10 25 3 NULL 5 11 NULL 5
|
|
11 31 3 NULL 10 NULL NULL 10
|
|
many nth_value calls on one window, optimized path
|
|
SELECT id, SUM(d) OVER w `sum`, COUNT(*) OVER w cnt,
|
|
LEAD(id, 3) OVER w le3,
|
|
FIRST_VALUE(id) OVER w fv,
|
|
LEAD(id, 1) OVER w le1,
|
|
LEAD(id, 2) OVER w le2,
|
|
LAG(id, 2) OVER w la2 FROM t1
|
|
WINDOW w AS (ORDER BY id ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW);
|
|
id sum cnt le3 fv le1 le2 la2
|
|
NULL NULL 1 3 NULL 1 2 NULL
|
|
1 1 2 4 NULL 2 3 NULL
|
|
2 3 3 5 NULL 3 4 NULL
|
|
3 6 3 10 1 4 5 1
|
|
4 9 3 10 2 5 10 2
|
|
5 12 3 11 3 10 10 3
|
|
10 19 3 NULL 4 10 11 4
|
|
10 25 3 NULL 5 11 NULL 5
|
|
11 31 3 NULL 10 NULL NULL 10
|
|
DROP TABLE t1;
|
|
Check interference with other two pass window functions
|
|
CREATE TABLE t(i INT);
|
|
INSERT INTO t VALUES (NULL), (1), (2), (3), (3), (4), (5), (6), (6), (7), (8), (9), (10);
|
|
SELECT i, PERCENT_RANK() OVER w cd
|
|
FROM t WINDOW w AS (ORDER BY i);
|
|
i cd
|
|
NULL 0
|
|
1 0.08333333333333333
|
|
2 0.16666666666666666
|
|
3 0.25
|
|
3 0.25
|
|
4 0.4166666666666667
|
|
5 0.5
|
|
6 0.5833333333333334
|
|
6 0.5833333333333334
|
|
7 0.75
|
|
8 0.8333333333333334
|
|
9 0.9166666666666666
|
|
10 1
|
|
SELECT i, PERCENT_RANK() OVER w cd FROM t
|
|
WINDOW w AS (ORDER BY i ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING);
|
|
i cd
|
|
NULL 0
|
|
1 0.08333333333333333
|
|
2 0.16666666666666666
|
|
3 0.25
|
|
3 0.25
|
|
4 0.4166666666666667
|
|
5 0.5
|
|
6 0.5833333333333334
|
|
6 0.5833333333333334
|
|
7 0.75
|
|
8 0.8333333333333334
|
|
9 0.9166666666666666
|
|
10 1
|
|
SELECT i, PERCENT_RANK() OVER w cd, NTILE(3) OVER w `ntile`, COUNT(*) OVER w cnt, SUM(i) OVER W `sum` FROM t
|
|
WINDOW w AS (ORDER BY i ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING);
|
|
i cd ntile cnt sum
|
|
NULL 0 1 3 3
|
|
1 0.08333333333333333 1 4 6
|
|
2 0.16666666666666666 1 4 9
|
|
3 0.25 1 4 12
|
|
3 0.25 1 4 15
|
|
4 0.4166666666666667 2 4 18
|
|
5 0.5 2 4 21
|
|
6 0.5833333333333334 2 4 24
|
|
6 0.5833333333333334 2 4 27
|
|
7 0.75 3 4 30
|
|
8 0.8333333333333334 3 4 34
|
|
9 0.9166666666666666 3 3 27
|
|
10 1 3 2 19
|
|
SELECT i, PERCENT_RANK() OVER w cd, NTILE(3) OVER w `ntile`, COUNT(*) OVER w cnt, SUM(i) OVER W `sum`,
|
|
LEAD(i,2) OVER w le2,
|
|
LAG(i) OVER w la FROM t
|
|
WINDOW w AS (ORDER BY i ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING);
|
|
i cd ntile cnt sum le2 la
|
|
NULL 0 1 3 3 2 NULL
|
|
1 0.08333333333333333 1 4 6 3 NULL
|
|
2 0.16666666666666666 1 4 9 3 1
|
|
3 0.25 1 4 12 4 2
|
|
3 0.25 1 4 15 5 3
|
|
4 0.4166666666666667 2 4 18 6 3
|
|
5 0.5 2 4 21 6 4
|
|
6 0.5833333333333334 2 4 24 7 5
|
|
6 0.5833333333333334 2 4 27 8 6
|
|
7 0.75 3 4 30 9 6
|
|
8 0.8333333333333334 3 4 34 10 7
|
|
9 0.9166666666666666 3 3 27 NULL 8
|
|
10 1 3 2 19 NULL 9
|
|
DROP TABLE t;
|
|
Nullability bug fixed
|
|
CREATE TABLE t(i INT NOT NULL);
|
|
INSERT INTO t VALUES (1), (2), (3), (3), (4), (5), (6);
|
|
SELECT LEAD(i, 3) OVER () FROM t;
|
|
LEAD(i, 3) OVER ()
|
|
3
|
|
4
|
|
5
|
|
6
|
|
NULL
|
|
NULL
|
|
NULL
|
|
SELECT LAG(i, 3) OVER () FROM t;
|
|
LAG(i, 3) OVER ()
|
|
NULL
|
|
NULL
|
|
NULL
|
|
1
|
|
2
|
|
3
|
|
3
|
|
DROP TABLE t;
|
|
|
|
Bug with missing update of cached example after split_sum_func
|
|
for NTH_VALUE
|
|
|
|
CREATE TABLE t(a INT, b INT, c INT, d INT);
|
|
INSERT INTO t VALUES (1,1,1,1), (2,2,4,2), (3,3,9,3);
|
|
SELECT SUM(c/d), LEAD(SUM(c/d), 1) OVER (ORDER BY a) FROM t GROUP BY a,b;
|
|
SUM(c/d) LEAD(SUM(c/d), 1) OVER (ORDER BY a)
|
|
1.0000 2.0000
|
|
2.0000 3.0000
|
|
3.0000 NULL
|
|
SELECT SUM(c/d), LEAD(SUM(c/d), 1, SUM(c/d)) OVER (ORDER BY a) FROM t GROUP BY a,b;
|
|
SUM(c/d) LEAD(SUM(c/d), 1, SUM(c/d)) OVER (ORDER BY a)
|
|
1.0000 2.0000
|
|
2.0000 3.0000
|
|
3.0000 3.0000
|
|
SELECT SUM(c/d), LAG(SUM(c/d), 1) OVER (ORDER BY a) FROM t GROUP BY a,b;
|
|
SUM(c/d) LAG(SUM(c/d), 1) OVER (ORDER BY a)
|
|
1.0000 NULL
|
|
2.0000 1.0000
|
|
3.0000 2.0000
|
|
SELECT SUM(c/d), LAG(SUM(c/d), 1, SUM(c/d)) OVER (ORDER BY a) FROM t GROUP BY a,b;
|
|
SUM(c/d) LAG(SUM(c/d), 1, SUM(c/d)) OVER (ORDER BY a)
|
|
1.0000 1.0000
|
|
2.0000 1.0000
|
|
3.0000 2.0000
|
|
SELECT LEAD(SUM(c/d), 2) OVER (ORDER BY a) FROM t GROUP BY a,b;
|
|
LEAD(SUM(c/d), 2) OVER (ORDER BY a)
|
|
3.0000
|
|
NULL
|
|
NULL
|
|
SELECT 1+LEAD(SUM(c/d), 1) OVER (ORDER BY a) FROM t GROUP BY a,b;
|
|
1+LEAD(SUM(c/d), 1) OVER (ORDER BY a)
|
|
3.0000
|
|
4.0000
|
|
NULL
|
|
SELECT ROW_NUMBER() OVER () rn,
|
|
1 + LEAD(SUM(c/d), 1) OVER (ORDER BY a) le1,
|
|
1 + LAG(SUM(c/d), 1) OVER (ORDER BY a) la1,
|
|
1 + LEAD(SUM(c/d), 2) OVER (ORDER BY a) le2,
|
|
1 + LAG(SUM(c/d), 2) OVER (ORDER BY a) la2,
|
|
1 + LEAD(SUM(c/d), 1, SUM(c/d)) OVER (ORDER BY a) le1d,
|
|
1 + LAG(SUM(c/d), 1, SUM(c/d)) OVER (ORDER BY a) la1d,
|
|
1 + LEAD(SUM(c/d), 2, SUM(c/d)) OVER (ORDER BY a) le2d,
|
|
1 + LAG(SUM(c/d), 2, SUM(c/d)) OVER (ORDER BY a) la2d,
|
|
1 + LEAD(SUM(c/d), 1, 1 + SUM(c/d)) OVER (ORDER BY a) le1dp,
|
|
1 + LAG(SUM(c/d), 1, 1 + SUM(c/d)) OVER (ORDER BY a) la1dp,
|
|
1 + LEAD(SUM(c/d), 2, 1 + SUM(c/d)) OVER (ORDER BY a) le2dp,
|
|
1 + LAG(SUM(c/d), 2, 1 + SUM(c/d)) OVER (ORDER BY a) la2dp
|
|
FROM t GROUP BY a,b;
|
|
rn le1 la1 le2 la2 le1d la1d le2d la2d le1dp la1dp le2dp la2dp
|
|
1 3.0000 NULL 4.0000 NULL 3.0000 2.0000 4.0000 2.0000 3.0000 3.0000 4.0000 3.0000
|
|
2 4.0000 2.0000 NULL NULL 4.0000 2.0000 3.0000 3.0000 4.0000 2.0000 4.0000 4.0000
|
|
3 NULL 3.0000 NULL 2.0000 4.0000 3.0000 4.0000 2.0000 5.0000 3.0000 5.0000 2.0000
|
|
DROP TABLE t;
|
|
#
|
|
# Bug#25883997 : WL#9603: SIG11 AT STRING::LENGTH() IN INCLUDE/SQL_STRING.H
|
|
#
|
|
CREATE TABLE t1 (a int, b char(1), c varchar(1));
|
|
INSERT INTO t1 VALUES (1,'s','k'),(NULL,'e','t'),(NULL,'w','i'),(2,'i','k');
|
|
SELECT LEAD(a, 7,'abc') OVER w1, LAG(a) OVER w1 FROM t1 WINDOW w1 AS (PARTITION BY a);
|
|
LEAD(a, 7,'abc') OVER w1 LAG(a) OVER w1
|
|
abc NULL
|
|
abc NULL
|
|
abc NULL
|
|
abc NULL
|
|
SELECT a, LEAD(a, 7, 'abc') over w1,
|
|
LEAD(a, 2, 'abc') over w1
|
|
FROM t1 WINDOW w1 AS (ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);
|
|
a LEAD(a, 7, 'abc') over w1 LEAD(a, 2, 'abc') over w1
|
|
NULL abc 1
|
|
NULL abc 2
|
|
1 abc abc
|
|
2 abc abc
|
|
SELECT a, LEAD(a, 1, 'abc') OVER w1,
|
|
LEAD(a, 2, 'abc') over w1
|
|
FROM t1 WINDOW w1 AS (ORDER BY a ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);
|
|
a LEAD(a, 1, 'abc') OVER w1 LEAD(a, 2, 'abc') over w1
|
|
NULL NULL 1
|
|
NULL 1 2
|
|
1 2 abc
|
|
2 abc abc
|
|
DROP TABLE t1;
|
|
Coverage for LEAD/LAG ::get_time, get_date
|
|
SELECT ADDTIME(LEAD(time'18:00:00', 0) OVER (ORDER BY NULL), '01:00:00');
|
|
ADDTIME(LEAD(time'18:00:00', 0) OVER (ORDER BY NULL), '01:00:00')
|
|
19:00:00
|
|
SELECT ADDTIME(LEAD(NULL, 1, time'18:00:00') OVER (ORDER BY NULL), '01:00:00');
|
|
ADDTIME(LEAD(NULL, 1, time'18:00:00') OVER (ORDER BY NULL), '01:00:00')
|
|
19:00:00
|
|
SELECT ADDDATE(LEAD(NULL, 1, date'1955-05-15') OVER (ORDER BY NULL), 1);
|
|
ADDDATE(LEAD(NULL, 1, date'1955-05-15') OVER (ORDER BY NULL), 1)
|
|
1955-05-16
|
|
SELECT ADDDATE(LEAD(date'1955-05-15', 0) OVER (ORDER BY NULL), 1);
|
|
ADDDATE(LEAD(date'1955-05-15', 0) OVER (ORDER BY NULL), 1)
|
|
1955-05-16
|
|
#
|
|
# Bug#26100985 WL9603: LEAD IN VIEW GIVES WRONG RESULT
|
|
#
|
|
CREATE VIEW v AS
|
|
SELECT LEAD(d, 2) OVER () FROM
|
|
(SELECT 1 AS d UNION SELECT 2 UNION SELECT 3) dt;
|
|
SELECT * FROM v;
|
|
LEAD(d, 2) OVER ()
|
|
3
|
|
NULL
|
|
NULL
|
|
DROP VIEW v;
|
|
#
|
|
# Bug#26178042 WL#9603: LEAD(<>,0) RESET RESULTS OF OTHER WF'S ON SAME WINDOW
|
|
#
|
|
CREATE TABLE t1e(a int);
|
|
INSERT INTO t1e VALUES(1),(2),(3),(3),(NULL);
|
|
OK
|
|
SELECT LEAD(a,2) over w1, LEAD(a,1) OVER w1 FROM t1e WINDOW w1 as ();
|
|
LEAD(a,2) over w1 LEAD(a,1) OVER w1
|
|
3 2
|
|
3 3
|
|
NULL 3
|
|
NULL NULL
|
|
NULL NULL
|
|
Used to give wrong results for (a,2) and (a,1)
|
|
SELECT LEAD(a,2) over w1, LEAD(a,0) OVER w1, LEAD(a,1) OVER w1 FROM t1e WINDOW w1 as ();
|
|
LEAD(a,2) over w1 LEAD(a,0) OVER w1 LEAD(a,1) OVER w1
|
|
3 1 2
|
|
3 2 3
|
|
NULL 3 3
|
|
NULL 3 NULL
|
|
NULL NULL NULL
|
|
DROP TABLE t1e;
|
|
#
|
|
# Bug#26703246 ASSERTION FAILED: DELSUM+(INT) Y/4-TEMP >= 0
|
|
# Also solves Bug#26703156.
|
|
Assertion used to fail: delsum+(int) y/4-temp >= 0
|
|
DO TO_SECONDS(LAG(POINT(2.804466E+307,-2032),75) OVER());
|
|
DO IS_IPV4(TIMESTAMP(LAG(-19131 ,188) OVER(),@F));
|
|
DO WEEKOFYEAR(LAG(-16726 ,247)RESPECT NULLS OVER());
|
|
DO DAYOFYEAR(LEAD(-6653420797178186265 ,29)OVER());
|
|
DO YEARWEEK(LEAD(FOUND_ROWS(),250)OVER());
|
|
Warnings:
|
|
Warning 1287 FOUND_ROWS() is deprecated and will be removed in a future release. Consider using COUNT(*) instead.
|
|
DO DAYOFYEAR(LAG('] .| /= ',63) OVER());
|
|
DO IFNULL((DAYNAME(LEAD(STDDEV(@F),162) OVER())),(0xD73E));
|
|
DO TO_DAYS(LEAD(((LOCATE(0xA812,0xE8DE))OR(@G)),148)OVER());
|
|
DO WEEKOFYEAR(LEAD(('5183-10-18 06:15:35.076079')SOUNDS LIKE(0x9E335D89),68)OVER());
|
|
DO DAYOFYEAR(LEAD(ROW_COUNT(),177)OVER());
|
|
DO MONTHNAME(LAG(' =',74) OVER());
|
|
DO SHA(BIN(MONTHNAME(LEAD(UNCOMPRESSED_LENGTH(SHA1("")),224)OVER())));
|
|
DO (((NOT(1)))^(HOUR(MONTHNAME(LEAD(DATABASE(),89)OVER()))));
|
|
DO MONTHNAME(LEAD(CHARSET(CONVERT((MOD(CURTIME(4),STDDEV(NULL)))USING CP850)),12) OVER());
|
|
DO LAST_DAY(LAG(NULL,113) OVER());
|
|
DO LAST_DAY(LEAD(-2201 ,98)RESPECT NULLS OVER());
|
|
DO OCTET_LENGTH(((LAST_DAY(LEAD(-28178,163)OVER()))OR(COLLATION(@E))));
|
|
DO LAST_DAY(LAG('*',36)RESPECT NULLS OVER());
|
|
DO LAST_DAY(LEAD( _CP932 '',241)OVER(RANGE UNBOUNDED PRECEDING));
|
|
Assertion used to fail: !check_time_mmssff_range(ltime)
|
|
DO ((CAST((LEAD(NULL,152) OVER()) AS TIME))*(1));
|
|
DO CAST((LAG(SHA(STDDEV('-0E%_')),224) OVER()) AS TIME);
|
|
Warnings:
|
|
Warning 1292 Truncated incorrect DOUBLE value: '-0E%_'
|
|
DO CAST((LAG(_UJIS '8CA} ',144) OVER()) AS TIME);
|
|
DO CAST((LAG(-16520,156) OVER()) AS TIME);
|
|
DO CAST((LEAD(-6011,202) OVER()) AS TIME);
|
|
DO CAST((LEAD(0x45,104) OVER())AS TIME);
|
|
DO CAST((LAG(POLYGON( LINESTRING( POINT(4099,17421),
|
|
POINT(22259,-5875.0796),
|
|
POINT(30179,6542),
|
|
POINT(12331,-18840)),
|
|
LINESTRING( POINT( 8162.2539,-29332),
|
|
POINT(24157,-23393),
|
|
POINT(268435459,-26835),
|
|
POINT(3.933871E+307,-25808)),
|
|
LINESTRING( POINT(30360,-1049),
|
|
POINT(15405,5.816757E+307),
|
|
POINT( 3717.3555,1.599730E+308),
|
|
POINT(-23002,-19077))),
|
|
70)
|
|
OVER())
|
|
AS DATETIME );
|
|
DO UNIX_TIMESTAMP(
|
|
LAG(ROUND(((UTC_TIME()) OR
|
|
((NOT (INET6_ATON(IS_IPV4(((MAKETIME(-10966,
|
|
233,
|
|
2795.439453))
|
|
OR(MAKEDATE(-3030,19)))))))))),
|
|
140) OVER());
|
|
DO LAST_DAY(SQRT(CAST((LAG(REPEAT('B',64),91)OVER())AS DATETIME)));
|
|
We Must disable warning for next statement because we get two instead of
|
|
one with -ps protocol vs plain protocol.
|
|
DO CAST((LEAD(-197994311,60)OVER())AS DATETIME);
|
|
DO IFNULL((NULL ),(UNIX_TIMESTAMP(LEAD(NULL,12)RESPECT NULLS OVER())));
|
|
Assertion used to fail: !check_datetime_range(ltime)
|
|
DO ((CONVERT((VARIANCE(-18951))USING CP866)) <=
|
|
(JSON_ARRAY(LEAD(CAST((35184372088833)AS DATETIME),126)OVER())));
|
|
DO ((LAG(LAST_DAY("]<$*_#[DB!^+ : 3"),89) OVER (RANGE UNBOUNDED PRECEDING)) >
|
|
(CONVERT(("1985-10-19 03:36:29.304455" -
|
|
INTERVAL(0x1446C5A2627FB06D88DC63D66B36DF) DAY_MICROSECOND)
|
|
USING BIG5)));
|
|
#
|
|
# Bug#26740557: WINDOW FUNC + JSON: ASSERTION FAILED:
|
|
# FALSE IN ITEM::VAL_JSON
|
|
#
|
|
SELECT ((MAKETIME(((QUARTER('| !*c>*{/'))<=>
|
|
(LAG(JSON_OBJECTAGG('key4',0x067c13d0d0d7d8c8d768aef7)
|
|
,7)OVER())),'9236-05-27',0xe2a7d4))^(0x1109));
|
|
((MAKETIME(((QUARTER('| !*c>*{/'))<=>
|
|
(LAG(JSON_OBJECTAGG('key4',0x067c13d0d0d7d8c8d768aef7)
|
|
,7)OVER())),'9236-05-27',0xe2a7d4))^(0x1109))
|
|
NULL
|
|
Warnings:
|
|
Warning 1292 Incorrect datetime value: '| !*c>*{/'
|
|
Warning 1292 Incorrect datetime value: '| !*c>*{/'
|
|
Warning 1292 Truncated incorrect INTEGER value: '9236-05-27'
|
|
SELECT ((LAG(JSON_MERGE_PATCH(1.755913e+308,'{ }'),246)OVER())<=(1));
|
|
((LAG(JSON_MERGE_PATCH(1.755913e+308,'{ }'),246)OVER())<=(1))
|
|
NULL
|
|
SELECT ((LAG(JSON_MERGE_PATCH(1.755913e+308,'{ }'),0)OVER())<=(1));
|
|
ERROR 22032: Invalid data type for JSON data in argument 1 to function json_merge_patch; a JSON string or JSON type is required.
|
|
SELECT ((QUOTE(JSON_KEYS(LEAD(JSON_KEYS(EXP(-15676),ABS('d0')),
|
|
162)OVER())))>=(CONNECTION_ID()));
|
|
((QUOTE(JSON_KEYS(LEAD(JSON_KEYS(EXP(-15676),ABS('d0')),
|
|
162)OVER())))>=(CONNECTION_ID()))
|
|
0
|
|
SELECT ((QUOTE(JSON_KEYS(LEAD(JSON_KEYS(EXP(-15676),ABS('d0')),
|
|
0)OVER())))>=(CONNECTION_ID()));
|
|
ERROR 22032: Invalid data type for JSON data in argument 1 to function json_keys; a JSON string or JSON type is required.
|
|
SELECT JSON_LENGTH(LEAD(JSON_OBJECTAGG('key2','*B'),172)OVER());
|
|
JSON_LENGTH(LEAD(JSON_OBJECTAGG('key2','*B'),172)OVER())
|
|
NULL
|
|
SELECT JSON_LENGTH(LEAD(JSON_OBJECTAGG('key2','*B'),0)OVER());
|
|
JSON_LENGTH(LEAD(JSON_OBJECTAGG('key2','*B'),0)OVER())
|
|
1
|
|
# End of test for Bug#26740557
|