105 lines
6.9 KiB
Plaintext
105 lines
6.9 KiB
Plaintext
--echo # Test of SQL window functions NTH_VALUE
|
|
--echo # ----------------------------------------------------------------------
|
|
|
|
SET NAMES utf8;
|
|
|
|
CREATE TABLE t1 (d DOUBLE, id INT, sex CHAR(1));
|
|
INSERT INTO t1 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);
|
|
ANALYZE TABLE t1;
|
|
|
|
--echo Ok, default semantics:
|
|
EXPLAIN FORMAT=JSON SELECT id, sex, NTH_VALUE(id, 1) RESPECT NULLS OVER () FROM t1;
|
|
|
|
--echo static wf
|
|
EXPLAIN FORMAT=JSON SELECT id, sex, NTH_VALUE(id, 1) OVER () FROM t1;
|
|
EXPLAIN FORMAT=JSON SELECT id, sex, NTH_VALUE(id, 3) OVER () FROM t1;
|
|
|
|
EXPLAIN FORMAT=JSON SELECT id, sex, NTH_VALUE(id, 2) OVER w FROM t1 WINDOW w as (PARTITION BY sex);
|
|
EXPLAIN FORMAT=JSON SELECT id, sex, NTH_VALUE(id, 2) OVER w FROM t1 WINDOW w as (PARTITION BY id);
|
|
|
|
--echo unbuffered
|
|
EXPLAIN FORMAT=JSON SELECT id, sex, NTH_VALUE(id, 2) OVER w FROM t1 WINDOW w as (PARTITION BY SEX ORDER BY ID ROWS UNBOUNDED PRECEDING);
|
|
EXPLAIN FORMAT=JSON SELECT id, sex, NTH_VALUE(id, 2) OVER w FROM t1 WINDOW w as (PARTITION BY SEX ORDER BY ID RANGE UNBOUNDED PRECEDING);
|
|
|
|
--echo test unoptimized path: trick: add DOUBLE type w/SUM which is unoptimized by default
|
|
--echo ascending
|
|
EXPLAIN FORMAT=JSON SELECT d, SUM(d) OVER w, sex, NTH_VALUE(d, 2) OVER w FROM t1 WINDOW w as (PARTITION BY SEX ORDER BY D ROWS 2 PRECEDING);
|
|
EXPLAIN FORMAT=JSON SELECT d, SUM(d) OVER w, sex, NTH_VALUE(d, 2) OVER w FROM t1 WINDOW w as (PARTITION BY SEX ORDER BY D RANGE 2 PRECEDING);
|
|
|
|
EXPLAIN FORMAT=JSON SELECT d, SUM(d) OVER w, COUNT(*) OVER w, NTH_VALUE(d, 2) OVER w FROM t1 WINDOW w as (ORDER BY D ROWS 2 PRECEDING);
|
|
EXPLAIN FORMAT=JSON SELECT d, SUM(d) OVER w, COUNT(*) OVER w, NTH_VALUE(d, 2) OVER w FROM t1 WINDOW w as (ORDER BY D ASC ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING);
|
|
EXPLAIN FORMAT=JSON SELECT d, SUM(d) OVER w, COUNT(*) OVER w, NTH_VALUE(d, 2) OVER w FROM t1 WINDOW w as (ORDER BY D ASC ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING);
|
|
|
|
EXPLAIN FORMAT=JSON SELECT d, SUM(d) OVER w, COUNT(*) OVER w, NTH_VALUE(d, 2) OVER w FROM t1 WINDOW w as (ORDER BY D RANGE 2 PRECEDING);
|
|
EXPLAIN FORMAT=JSON SELECT d, SUM(d) OVER w, COUNT(*) OVER w, NTH_VALUE(d, 2) OVER w FROM t1 WINDOW w as (ORDER BY D RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING);
|
|
EXPLAIN FORMAT=JSON SELECT d, SUM(d) OVER w, COUNT(*) OVER w, NTH_VALUE(d, 2) OVER w FROM t1 WINDOW w as (ORDER BY D RANGE BETWEEN 1 FOLLOWING AND 2 FOLLOWING);
|
|
|
|
--echo descending
|
|
EXPLAIN FORMAT=JSON SELECT d, SUM(d) OVER w, sex, NTH_VALUE(d, 2) OVER w FROM t1 WINDOW w as (PARTITION BY SEX ORDER BY D DESC ROWS 2 PRECEDING);
|
|
EXPLAIN FORMAT=JSON SELECT d, SUM(d) OVER w, sex, NTH_VALUE(d, 2) OVER w FROM t1 WINDOW w as (PARTITION BY SEX ORDER BY D DESC RANGE 2 PRECEDING);
|
|
|
|
EXPLAIN FORMAT=JSON SELECT d, SUM(d) OVER w, COUNT(*) OVER w, NTH_VALUE(d, 2) OVER w FROM t1 WINDOW w as (ORDER BY D DESC ROWS 2 PRECEDING);
|
|
EXPLAIN FORMAT=JSON SELECT d, SUM(d) OVER w, COUNT(*) OVER w, NTH_VALUE(d, 2) OVER w FROM t1 WINDOW w as (ORDER BY D DESC ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING);
|
|
EXPLAIN FORMAT=JSON SELECT d, SUM(d) OVER w, COUNT(*) OVER w, NTH_VALUE(d, 2) OVER w FROM t1 WINDOW w as (ORDER BY D DESC ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING);
|
|
|
|
EXPLAIN FORMAT=JSON SELECT d, SUM(d) OVER w, COUNT(*) OVER w, NTH_VALUE(d, 2) OVER w FROM t1 WINDOW w as (ORDER BY D DESC RANGE 2 PRECEDING);
|
|
EXPLAIN FORMAT=JSON SELECT d, SUM(d) OVER w, COUNT(*) OVER w, NTH_VALUE(d, 2) OVER w FROM t1 WINDOW w as (ORDER BY D DESC RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING);
|
|
EXPLAIN FORMAT=JSON SELECT d, SUM(d) OVER w, COUNT(*) OVER w, NTH_VALUE(d, 2) OVER w FROM t1 WINDOW w as (ORDER BY D DESC RANGE BETWEEN 1 FOLLOWING AND 2 FOLLOWING);
|
|
|
|
--echo Dynamic upper
|
|
#SELECT id, sex, NTH_VALUE(d, 2) OVER w FROM t1 WINDOW w as (ORDER BY id);
|
|
EXPLAIN FORMAT=JSON SELECT id, sex, NTH_VALUE(id, 2) OVER w FROM t1 WINDOW w as (ORDER BY id);
|
|
|
|
|
|
--echo optimized path
|
|
--echo ascending
|
|
EXPLAIN FORMAT=JSON SELECT id, sex, NTH_VALUE(id, 2) OVER w FROM t1 WINDOW w as (PARTITION BY SEX ORDER BY ID ROWS 2 PRECEDING);
|
|
EXPLAIN FORMAT=JSON SELECT id, sex, NTH_VALUE(id, 2) OVER w FROM t1 WINDOW w as (PARTITION BY SEX ORDER BY ID RANGE 2 PRECEDING);
|
|
|
|
EXPLAIN FORMAT=JSON SELECT id, COUNT(*) OVER w, NTH_VALUE(id, 2) OVER w FROM t1 WINDOW w as (ORDER BY ID ROWS 2 PRECEDING);
|
|
EXPLAIN FORMAT=JSON SELECT id, COUNT(*) OVER w, NTH_VALUE(id, 2) OVER w FROM t1 WINDOW w as (ORDER BY ID ASC ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING);
|
|
EXPLAIN FORMAT=JSON SELECT id, COUNT(*) OVER w, NTH_VALUE(id, 2) OVER w FROM t1 WINDOW w as (ORDER BY ID ASC ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING);
|
|
|
|
EXPLAIN FORMAT=JSON SELECT id, COUNT(*) OVER w, NTH_VALUE(id, 2) OVER w FROM t1 WINDOW w as (ORDER BY ID RANGE 2 PRECEDING);
|
|
EXPLAIN FORMAT=JSON SELECT id, COUNT(*) OVER w, NTH_VALUE(id, 2) OVER w FROM t1 WINDOW w as (ORDER BY ID RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING);
|
|
EXPLAIN FORMAT=JSON SELECT id, COUNT(*) OVER w, NTH_VALUE(id, 2) OVER w FROM t1 WINDOW w as (ORDER BY ID RANGE BETWEEN 1 FOLLOWING AND 2 FOLLOWING);
|
|
|
|
--echo descending
|
|
EXPLAIN FORMAT=JSON SELECT id, sex, NTH_VALUE(id, 2) OVER w FROM t1 WINDOW w as (PARTITION BY SEX ORDER BY ID DESC ROWS 2 PRECEDING);
|
|
EXPLAIN FORMAT=JSON SELECT id, sex, NTH_VALUE(id, 2) OVER w FROM t1 WINDOW w as (PARTITION BY SEX ORDER BY ID DESC RANGE 2 PRECEDING);
|
|
|
|
EXPLAIN FORMAT=JSON SELECT id, COUNT(*) OVER w, NTH_VALUE(id, 2) OVER w FROM t1 WINDOW w as (ORDER BY ID DESC ROWS 2 PRECEDING);
|
|
EXPLAIN FORMAT=JSON SELECT id, COUNT(*) OVER w, NTH_VALUE(id, 2) OVER w FROM t1 WINDOW w as (ORDER BY ID DESC ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING);
|
|
EXPLAIN FORMAT=JSON SELECT id, COUNT(*) OVER w, NTH_VALUE(id, 2) OVER w FROM t1 WINDOW w as (ORDER BY ID DESC ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING);
|
|
|
|
EXPLAIN FORMAT=JSON SELECT id, COUNT(*) OVER w, NTH_VALUE(id, 2) OVER w FROM t1 WINDOW w as (ORDER BY ID DESC RANGE 2 PRECEDING);
|
|
EXPLAIN FORMAT=JSON SELECT id, COUNT(*) OVER w, NTH_VALUE(id, 2) OVER w FROM t1 WINDOW w as (ORDER BY ID DESC RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING);
|
|
EXPLAIN FORMAT=JSON SELECT id, COUNT(*) OVER w, NTH_VALUE(id, 2) OVER w FROM t1 WINDOW w as (ORDER BY ID DESC RANGE BETWEEN 1 FOLLOWING AND 2 FOLLOWING);
|
|
|
|
--echo many nth_value calls on one window, optimized path
|
|
EXPLAIN FORMAT=JSON SELECT id, COUNT(*) OVER w,
|
|
NTH_VALUE(id, 3) OVER w,
|
|
FIRST_VALUE(id) OVER w,
|
|
NTH_VALUE(id, 1) OVER w,
|
|
NTH_VALUE(id, 2) OVER w FROM t1
|
|
WINDOW w AS (ORDER BY id ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW);
|
|
|
|
--echo many nth_value calls on one window, unoptimized path
|
|
EXPLAIN FORMAT=JSON SELECT id, SUM(d) OVER w, COUNT(*) OVER w,
|
|
NTH_VALUE(id, 3) OVER w,
|
|
FIRST_VALUE(id) OVER w,
|
|
NTH_VALUE(id, 1) OVER w,
|
|
NTH_VALUE(id, 2) OVER w FROM t1
|
|
WINDOW w AS (ORDER BY id ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW);
|
|
|
|
DROP TABLE t1;
|
|
|