1594 lines
40 KiB
Plaintext
1594 lines
40 KiB
Plaintext
SET NAMES utf8;
|
|
Warnings:
|
|
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
|
|
Single window function (plus ORDER BY).
|
|
CREATE TABLE t(i INT, j INT);
|
|
INSERT INTO t VALUES (1,1);
|
|
INSERT INTO t VALUES (1,4);
|
|
INSERT INTO t VALUES (1,2);
|
|
INSERT INTO t VALUES (1,4);
|
|
ANALYZE TABLE t;
|
|
Table Op Msg_type Msg_text
|
|
test.t analyze status OK
|
|
Single partition, no sorting
|
|
SELECT i, j, MIN(i) OVER (ROWS UNBOUNDED PRECEDING) min,
|
|
MAX(j) OVER (ROWS UNBOUNDED PRECEDING) max FROM t;
|
|
i j min max
|
|
1 1 1 1
|
|
1 4 1 4
|
|
1 2 1 4
|
|
1 4 1 4
|
|
SELECT i, j, MIN(i) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) min,
|
|
MAX(j) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) max FROM t;
|
|
i j min max
|
|
1 1 1 1
|
|
1 4 1 4
|
|
1 2 1 4
|
|
1 4 1 4
|
|
SELECT i, j, MIN(i+j) OVER (ROWS UNBOUNDED PRECEDING) min FROM t ORDER BY j;
|
|
i j min
|
|
1 1 2
|
|
1 2 2
|
|
1 4 2
|
|
1 4 2
|
|
SELECT i, j, MAX(i+j) OVER (ROWS UNBOUNDED PRECEDING) max FROM t ORDER BY j;
|
|
i j max
|
|
1 1 2
|
|
1 2 3
|
|
1 4 5
|
|
1 4 5
|
|
SELECT i, j, MIN(i+j) OVER (ROWS UNBOUNDED PRECEDING) min FROM t ORDER BY j DESC;
|
|
i j min
|
|
1 4 5
|
|
1 4 5
|
|
1 2 3
|
|
1 1 2
|
|
SELECT i, j, MAX(i+j) OVER (ROWS UNBOUNDED PRECEDING) max FROM t ORDER BY j DESC;
|
|
i j max
|
|
1 4 5
|
|
1 4 5
|
|
1 2 5
|
|
1 1 5
|
|
Single ordered partition
|
|
SELECT i, j, MIN(i) OVER (ORDER BY j ROWS UNBOUNDED PRECEDING) min,
|
|
MAX(j) OVER (ORDER BY j ROWS UNBOUNDED PRECEDING) max FROM t;
|
|
i j min max
|
|
1 1 1 1
|
|
1 2 1 2
|
|
1 4 1 4
|
|
1 4 1 4
|
|
SELECT i, j, MIN(j) OVER (ORDER BY j ROWS UNBOUNDED PRECEDING) min,
|
|
MAX(i) OVER (ORDER BY i ROWS UNBOUNDED PRECEDING) max FROM t ORDER BY j ;
|
|
i j min max
|
|
1 1 1 1
|
|
1 2 1 1
|
|
1 4 1 1
|
|
1 4 1 1
|
|
SELECT i, j, MIN(i+j) OVER (ORDER BY j ROWS UNBOUNDED PRECEDING) min FROM t ORDER BY j DESC;
|
|
i j min
|
|
1 4 2
|
|
1 4 2
|
|
1 2 2
|
|
1 1 2
|
|
SELECT i, j, MAX(i+j) OVER (ORDER BY j ROWS UNBOUNDED PRECEDING) max FROM t ORDER BY j DESC;
|
|
i j max
|
|
1 4 5
|
|
1 4 5
|
|
1 2 3
|
|
1 1 2
|
|
SELECT i, j, MIN(i+j) OVER (ORDER BY j DESC ROWS UNBOUNDED PRECEDING) min FROM t ORDER BY j DESC;
|
|
i j min
|
|
1 4 5
|
|
1 4 5
|
|
1 2 3
|
|
1 1 2
|
|
SELECT i, j, MAX(i+j) OVER (ORDER BY j DESC ROWS UNBOUNDED PRECEDING) max FROM t ORDER BY j DESC;
|
|
i j max
|
|
1 4 5
|
|
1 4 5
|
|
1 2 5
|
|
1 1 5
|
|
Check that we eliminate redundant sorting in ORDER BY even with wfs
|
|
SELECT i, j, MIN(i+j) OVER (ROWS UNBOUNDED PRECEDING) min FROM t ORDER BY NULL DESC;
|
|
i j min
|
|
1 1 2
|
|
1 4 2
|
|
1 2 2
|
|
1 4 2
|
|
EXPLAIN FORMAT=JSON SELECT i, j, MIN(i+j) OVER (ROWS UNBOUNDED PRECEDING) min FROM t ORDER BY NULL DESC;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "2.95"
|
|
},
|
|
"ordering_operation": {
|
|
"using_filesort": false,
|
|
"windowing": {
|
|
"windows": [
|
|
{
|
|
"name": "<unnamed window>",
|
|
"functions": [
|
|
"min"
|
|
]
|
|
}
|
|
],
|
|
"table": {
|
|
"table_name": "t",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 4,
|
|
"rows_produced_per_join": 4,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "2.55",
|
|
"eval_cost": "0.40",
|
|
"prefix_cost": "2.95",
|
|
"data_read_per_join": "64"
|
|
},
|
|
"used_columns": [
|
|
"i",
|
|
"j"
|
|
]
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t`.`i` AS `i`,`test`.`t`.`j` AS `j`,min((`test`.`t`.`i` + `test`.`t`.`j`)) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS `min` from `test`.`t` order by NULL desc
|
|
With LIMIT
|
|
SELECT i, j, MIN(i+j) OVER (ROWS UNBOUNDED PRECEDING) min FROM t ORDER BY j DESC LIMIT 3;
|
|
i j min
|
|
1 4 5
|
|
1 4 5
|
|
1 2 3
|
|
Min/Max with View
|
|
CREATE VIEW v AS
|
|
SELECT i, j, MIN(i+j) OVER (ORDER BY j DESC ROWS UNBOUNDED PRECEDING) min,
|
|
MAX(i+j) OVER (ORDER BY j DESC ROWS UNBOUNDED PRECEDING) max FROM t;
|
|
SHOW CREATE VIEW v;
|
|
View Create View character_set_client collation_connection
|
|
v CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select `t`.`i` AS `i`,`t`.`j` AS `j`,min((`t`.`i` + `t`.`j`)) OVER (ORDER BY `t`.`j` desc ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS `min`,max((`t`.`i` + `t`.`j`)) OVER (ORDER BY `t`.`j` desc ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS `max` from `t` utf8 utf8_general_ci
|
|
SELECT * FROM v;
|
|
i j min max
|
|
1 4 5 5
|
|
1 4 5 5
|
|
1 2 3 5
|
|
1 1 2 5
|
|
DROP VIEW v;
|
|
TRUNCATE TABLE t;
|
|
INSERT INTO t VALUES (999961560, DEFAULT);
|
|
INSERT INTO t VALUES (44721, DEFAULT);
|
|
SELECT MIN(i) OVER () FROM t;
|
|
MIN(i) OVER ()
|
|
44721
|
|
44721
|
|
SELECT MAX(i) OVER () FROM t;
|
|
MAX(i) OVER ()
|
|
999961560
|
|
999961560
|
|
DROP TABLE t;
|
|
CREATE TABLE t(i INT, j INT, k INT);
|
|
INSERT INTO t VALUES (1,1,1);
|
|
INSERT INTO t VALUES (1,4,1);
|
|
INSERT INTO t VALUES (1,2,1);
|
|
INSERT INTO t VALUES (1,4,1);
|
|
INSERT INTO t VALUES (1,4,1);
|
|
INSERT INTO t VALUES (1,1,2);
|
|
INSERT INTO t VALUES (1,4,2);
|
|
INSERT INTO t VALUES (1,2,2);
|
|
INSERT INTO t VALUES (1,4,2);
|
|
INSERT INTO t VALUES (1,1,3);
|
|
INSERT INTO t VALUES (1,4,3);
|
|
INSERT INTO t VALUES (1,2,3);
|
|
INSERT INTO t VALUES (1,4,3);
|
|
INSERT INTO t VALUES (1,1,4);
|
|
INSERT INTO t VALUES (1,4,4);
|
|
INSERT INTO t VALUES (1,2,4);
|
|
INSERT INTO t VALUES (1,4,4);
|
|
----------------------------------------------------------------------
|
|
- Combination with GROUP BY
|
|
----------------------------------------------------------------------
|
|
SELECT k, MIN(k) OVER (ROWS UNBOUNDED PRECEDING) min,
|
|
MAX(k) OVER (ROWS UNBOUNDED PRECEDING) max FROM t;
|
|
k min max
|
|
1 1 1
|
|
1 1 1
|
|
1 1 1
|
|
1 1 1
|
|
1 1 1
|
|
2 1 2
|
|
2 1 2
|
|
2 1 2
|
|
2 1 2
|
|
3 1 3
|
|
3 1 3
|
|
3 1 3
|
|
3 1 3
|
|
4 1 4
|
|
4 1 4
|
|
4 1 4
|
|
4 1 4
|
|
SELECT k, MIN(i), SUM(j), MIN(k) OVER (ROWS UNBOUNDED PRECEDING) min_wf FROM t GROUP BY (k);
|
|
k MIN(i) SUM(j) min_wf
|
|
1 1 15 1
|
|
2 1 11 1
|
|
3 1 11 1
|
|
4 1 11 1
|
|
SELECT k, MIN(i), SUM(j), MAX(k) OVER (ROWS UNBOUNDED PRECEDING) max_wf FROM t GROUP BY (k);
|
|
k MIN(i) SUM(j) max_wf
|
|
1 1 15 1
|
|
2 1 11 2
|
|
3 1 11 3
|
|
4 1 11 4
|
|
SELECT k, MIN(i), SUM(j), MIN(k) OVER (ROWS UNBOUNDED PRECEDING) min_wf FROM t GROUP BY (k) ORDER BY min_wf DESC;
|
|
k MIN(i) SUM(j) min_wf
|
|
1 1 15 1
|
|
2 1 11 1
|
|
3 1 11 1
|
|
4 1 11 1
|
|
SELECT k, MIN(i), SUM(j), MAX(k) OVER (ROWS UNBOUNDED PRECEDING) max_wf FROM t GROUP BY (k) ORDER BY max_wf DESC;
|
|
k MIN(i) SUM(j) max_wf
|
|
1 1 15 1
|
|
2 1 11 2
|
|
3 1 11 3
|
|
4 1 11 4
|
|
SELECT k, GROUP_CONCAT(j ORDER BY j), MIN(k) OVER (ROWS UNBOUNDED PRECEDING) min,
|
|
MAX(k) OVER (ROWS UNBOUNDED PRECEDING) max FROM t GROUP BY (k);
|
|
k GROUP_CONCAT(j ORDER BY j) min max
|
|
1 1,2,4,4,4 1 1
|
|
2 1,2,4,4 1 2
|
|
3 1,2,4,4 1 3
|
|
4 1,2,4,4 1 4
|
|
SELECT k, AVG(DISTINCT j), MIN(k) OVER (ROWS UNBOUNDED PRECEDING) min,
|
|
MAX(k) OVER (ROWS UNBOUNDED PRECEDING) max FROM t GROUP BY (k);
|
|
k AVG(DISTINCT j) min max
|
|
1 2.3333 1 1
|
|
2 2.3333 1 2
|
|
3 2.3333 1 3
|
|
4 2.3333 1 4
|
|
SELECT k, GROUP_CONCAT(j ORDER BY j), MIN(k+1) OVER (ROWS UNBOUNDED PRECEDING) min FROM t GROUP BY (k);
|
|
k GROUP_CONCAT(j ORDER BY j) min
|
|
1 1,2,4,4,4 2
|
|
2 1,2,4,4 2
|
|
3 1,2,4,4 2
|
|
4 1,2,4,4 2
|
|
SELECT k, GROUP_CONCAT(j ORDER BY j), MAX(k+1) OVER (ROWS UNBOUNDED PRECEDING) max FROM t GROUP BY (k);
|
|
k GROUP_CONCAT(j ORDER BY j) max
|
|
1 1,2,4,4,4 2
|
|
2 1,2,4,4 3
|
|
3 1,2,4,4 4
|
|
4 1,2,4,4 5
|
|
SELECT k, GROUP_CONCAT(j ORDER BY j), MIN(k+1) OVER (ORDER BY k DESC ROWS UNBOUNDED PRECEDING) min FROM t GROUP BY (k);
|
|
k GROUP_CONCAT(j ORDER BY j) min
|
|
4 1,2,4,4 5
|
|
3 1,2,4,4 4
|
|
2 1,2,4,4 3
|
|
1 1,2,4,4,4 2
|
|
SELECT k, GROUP_CONCAT(j ORDER BY j), MAX(k+1) OVER (ORDER BY k DESC ROWS UNBOUNDED PRECEDING) max FROM t GROUP BY (k);
|
|
k GROUP_CONCAT(j ORDER BY j) max
|
|
4 1,2,4,4 5
|
|
3 1,2,4,4 5
|
|
2 1,2,4,4 5
|
|
1 1,2,4,4,4 5
|
|
CREATE TABLE t1 (id INTEGER, sex CHAR(1));
|
|
INSERT INTO t1 VALUES (1, 'M');
|
|
INSERT INTO t1 VALUES (2, 'F');
|
|
INSERT INTO t1 VALUES (3, 'F');
|
|
INSERT INTO t1 VALUES (4, 'F');
|
|
INSERT INTO t1 VALUES (5, 'M');
|
|
INSERT INTO t1 VALUES (10, NULL);
|
|
INSERT INTO t1 VALUES (11, NULL);
|
|
CREATE TABLE ss(c CHAR(1));
|
|
INSERT INTO ss VALUES ('M');
|
|
This is grouped aggregate with HAVING in conjunction with a streaming wf
|
|
SELECT sex, AVG(id), MIN(AVG(id)) OVER w min, MAX(AVG(id)) OVER w max FROM t1
|
|
GROUP BY sex HAVING sex='M' OR sex='F' OR sex IS NULL
|
|
WINDOW w AS (ROWS UNBOUNDED PRECEDING) ORDER BY sex DESC;
|
|
sex AVG(id) min max
|
|
M 3.0000 3.0000 3.0000
|
|
F 3.0000 3.0000 3.0000
|
|
NULL 10.5000 3.0000 10.5000
|
|
Ditto, but HAVING using subquery
|
|
SELECT sex, AVG(id), MIN(AVG(id)) OVER w min, MAX(AVG(id)) OVER w max FROM t1
|
|
GROUP BY sex HAVING sex=(SELECT c FROM ss LIMIT 1) OR sex='F' OR sex IS NULL
|
|
WINDOW w AS (ROWS UNBOUNDED PRECEDING) ORDER BY sex DESC;
|
|
sex AVG(id) min max
|
|
M 3.0000 3.0000 3.0000
|
|
F 3.0000 3.0000 3.0000
|
|
NULL 10.5000 3.0000 10.5000
|
|
This is a grouped aggregate in conjunction with a buffered wf
|
|
which generates an extra tmp file step
|
|
SELECT sex, AVG(id), MIN(AVG(id)) OVER w min, MAX(AVG(id)) OVER w max, NTILE(2) OVER w FROM t1
|
|
GROUP BY sex
|
|
WINDOW w AS (ORDER BY sex ROWS UNBOUNDED PRECEDING) ORDER BY sex DESC;
|
|
sex AVG(id) min max NTILE(2) OVER w
|
|
M 3.0000 3.0000 10.5000 2
|
|
F 3.0000 3.0000 10.5000 1
|
|
NULL 10.5000 10.5000 10.5000 1
|
|
This is a grouped aggregate with HAVING in conjunction with a buffered wf
|
|
which generates an extra tmp file step
|
|
SELECT sex, AVG(id), MIN(AVG(id)) OVER w min, MAX(AVG(id)) OVER w max, NTILE(2) OVER w FROM t1
|
|
GROUP BY sex HAVING sex=(SELECT c FROM ss LIMIT 1) OR sex='F' OR sex IS NULL
|
|
WINDOW w AS (ORDER BY sex ROWS UNBOUNDED PRECEDING) ORDER BY sex DESC;
|
|
sex AVG(id) min max NTILE(2) OVER w
|
|
M 3.0000 3.0000 10.5000 2
|
|
F 3.0000 3.0000 10.5000 1
|
|
NULL 10.5000 10.5000 10.5000 1
|
|
Pure HAVING: In absence of filtering in the grouping sort step,
|
|
make sure we filter before windowing steps
|
|
SELECT sex, NTILE(2) OVER w , MIN(ASCII(sex)) OVER w min,
|
|
MAX(ASCII(sex)) OVER w max FROM t1
|
|
HAVING sex=(SELECT c FROM ss LIMIT 1)
|
|
WINDOW w AS (ORDER BY sex ROWS UNBOUNDED PRECEDING);
|
|
sex NTILE(2) OVER w min max
|
|
M 1 77 77
|
|
M 2 77 77
|
|
Bug fix for prepared statements
|
|
PREPARE p FROM "SELECT sex, AVG(id), MIN(AVG(id)) OVER w min,
|
|
MAX(AVG(id)) OVER w max, NTILE(2) OVER w FROM t1
|
|
GROUP BY sex HAVING sex=(SELECT c FROM ss LIMIT 1) OR sex='F' OR sex IS NULL
|
|
WINDOW w AS (ORDER BY sex ROWS UNBOUNDED PRECEDING) ORDER BY sex DESC";
|
|
EXECUTE p;
|
|
sex AVG(id) min max NTILE(2) OVER w
|
|
M 3.0000 3.0000 10.5000 2
|
|
F 3.0000 3.0000 10.5000 1
|
|
NULL 10.5000 10.5000 10.5000 1
|
|
EXECUTE p;
|
|
sex AVG(id) min max NTILE(2) OVER w
|
|
M 3.0000 3.0000 10.5000 2
|
|
F 3.0000 3.0000 10.5000 1
|
|
NULL 10.5000 10.5000 10.5000 1
|
|
DROP PREPARE p;
|
|
DROP TABLE t1,ss;
|
|
Tests with ROLLUP
|
|
SELECT k, MIN(i), SUM(j), MIN(k) OVER (ROWS UNBOUNDED PRECEDING) min_wf FROM t
|
|
GROUP BY (k) WITH ROLLUP;
|
|
k MIN(i) SUM(j) min_wf
|
|
1 1 15 1
|
|
2 1 11 1
|
|
3 1 11 1
|
|
4 1 11 1
|
|
NULL 1 48 1
|
|
SELECT k, MIN(i), SUM(j), MAX(k) OVER (ROWS UNBOUNDED PRECEDING) max_wf FROM t
|
|
GROUP BY (k) WITH ROLLUP;
|
|
k MIN(i) SUM(j) max_wf
|
|
1 1 15 1
|
|
2 1 11 2
|
|
3 1 11 3
|
|
4 1 11 4
|
|
NULL 1 48 4
|
|
SELECT k, MIN(i), SUM(j), MIN(k) OVER (ROWS UNBOUNDED PRECEDING) min_wf FROM t
|
|
GROUP BY (k) WITH ROLLUP ORDER BY k DESC;
|
|
k MIN(i) SUM(j) min_wf
|
|
4 1 11 4
|
|
3 1 11 3
|
|
2 1 11 2
|
|
1 1 15 1
|
|
NULL 1 48 1
|
|
SELECT k, MIN(i), SUM(j), MIN(k) OVER (ROWS UNBOUNDED PRECEDING) min_wf FROM t
|
|
GROUP BY k,j WITH ROLLUP;
|
|
k MIN(i) SUM(j) min_wf
|
|
1 1 1 1
|
|
1 1 2 1
|
|
1 1 12 1
|
|
1 1 15 1
|
|
2 1 1 1
|
|
2 1 2 1
|
|
2 1 8 1
|
|
2 1 11 1
|
|
3 1 1 1
|
|
3 1 2 1
|
|
3 1 8 1
|
|
3 1 11 1
|
|
4 1 1 1
|
|
4 1 2 1
|
|
4 1 8 1
|
|
4 1 11 1
|
|
NULL 1 48 1
|
|
DROP TABLE t;
|
|
----------------------------------------------------------------------
|
|
- Min/Max with NTH VALUE/LEAD/LAG
|
|
----------------------------------------------------------------------
|
|
CREATE TABLE t3(t3_id INT, k INT);
|
|
INSERT INTO t3 VALUES (0, 0);
|
|
INSERT INTO t3 VALUES (0, 0);
|
|
INSERT INTO t3 VALUES (2, 0);
|
|
INSERT INTO t3 VALUES (2, 0);
|
|
INSERT INTO t3 VALUES (4, 0);
|
|
INSERT INTO t3 VALUES (4, 0);
|
|
INSERT INTO t3 VALUES (6, 0);
|
|
INSERT INTO t3 VALUES (6, 0);
|
|
INSERT INTO t3 VALUES (8, 0);
|
|
INSERT INTO t3 VALUES (8, 0);
|
|
INSERT INTO t3 VALUES (1, 1);
|
|
INSERT INTO t3 VALUES (1, 1);
|
|
INSERT INTO t3 VALUES (3, 1);
|
|
INSERT INTO t3 VALUES (3, 1);
|
|
INSERT INTO t3 VALUES (5, 1);
|
|
INSERT INTO t3 VALUES (5, 1);
|
|
INSERT INTO t3 VALUES (7, 1);
|
|
INSERT INTO t3 VALUES (7, 1);
|
|
INSERT INTO t3 VALUES (9, 1);
|
|
INSERT INTO t3 VALUES (9, 1);
|
|
SELECT t3_id, MIN(t3_id) OVER w min,
|
|
MAX(t3_id) OVER w max,
|
|
CUME_DIST() OVER w c_dist,
|
|
LEAD(t3_id, 2) OVER w lead2,
|
|
NTH_VALUE(t3_id, 3) OVER w nth,
|
|
k FROM t3
|
|
WINDOW w AS (PARTITION BY k ORDER BY t3_id);
|
|
t3_id min max c_dist lead2 nth k
|
|
0 0 0 0.2 2 NULL 0
|
|
0 0 0 0.2 2 NULL 0
|
|
2 0 2 0.4 4 2 0
|
|
2 0 2 0.4 4 2 0
|
|
4 0 4 0.6 6 2 0
|
|
4 0 4 0.6 6 2 0
|
|
6 0 6 0.8 8 2 0
|
|
6 0 6 0.8 8 2 0
|
|
8 0 8 1 NULL 2 0
|
|
8 0 8 1 NULL 2 0
|
|
1 1 1 0.2 3 NULL 1
|
|
1 1 1 0.2 3 NULL 1
|
|
3 1 3 0.4 5 3 1
|
|
3 1 3 0.4 5 3 1
|
|
5 1 5 0.6 7 3 1
|
|
5 1 5 0.6 7 3 1
|
|
7 1 7 0.8 9 3 1
|
|
7 1 7 0.8 9 3 1
|
|
9 1 9 1 NULL 3 1
|
|
9 1 9 1 NULL 3 1
|
|
SELECT t3_id, MIN(t3_id) OVER w min,
|
|
MAX(t3_id) OVER w max,
|
|
CUME_DIST() OVER w c_dist,
|
|
LEAD(t3_id, 2) OVER w lead2,
|
|
NTH_VALUE(t3_id, 3) OVER w nth,
|
|
k FROM t3
|
|
WINDOW w AS (PARTITION BY k ORDER BY t3_id RANGE UNBOUNDED PRECEDING);
|
|
t3_id min max c_dist lead2 nth k
|
|
0 0 0 0.2 2 NULL 0
|
|
0 0 0 0.2 2 NULL 0
|
|
2 0 2 0.4 4 2 0
|
|
2 0 2 0.4 4 2 0
|
|
4 0 4 0.6 6 2 0
|
|
4 0 4 0.6 6 2 0
|
|
6 0 6 0.8 8 2 0
|
|
6 0 6 0.8 8 2 0
|
|
8 0 8 1 NULL 2 0
|
|
8 0 8 1 NULL 2 0
|
|
1 1 1 0.2 3 NULL 1
|
|
1 1 1 0.2 3 NULL 1
|
|
3 1 3 0.4 5 3 1
|
|
3 1 3 0.4 5 3 1
|
|
5 1 5 0.6 7 3 1
|
|
5 1 5 0.6 7 3 1
|
|
7 1 7 0.8 9 3 1
|
|
7 1 7 0.8 9 3 1
|
|
9 1 9 1 NULL 3 1
|
|
9 1 9 1 NULL 3 1
|
|
DROP TABLE t3;
|
|
----------------------------------------------------------------------
|
|
- Window function in parent and subquery
|
|
----------------------------------------------------------------------
|
|
CREATE TABLE t(i INT, j INT);
|
|
INSERT INTO t VALUES (1,NULL);
|
|
INSERT INTO t VALUES (1,NULL);
|
|
INSERT INTO t VALUES (1,1);
|
|
INSERT INTO t VALUES (1,NULL);
|
|
INSERT INTO t VALUES (1,2);
|
|
INSERT INTO t VALUES (2,1);
|
|
INSERT INTO t VALUES (2,2);
|
|
INSERT INTO t VALUES (2,NULL);
|
|
INSERT INTO t VALUES (2,NULL);
|
|
CREATE TABLE t1 (id INTEGER, sex CHAR(1));
|
|
INSERT INTO t1 VALUES (1, 'M');
|
|
INSERT INTO t1 VALUES (2, 'F');
|
|
INSERT INTO t1 VALUES (3, 'F');
|
|
INSERT INTO t1 VALUES (4, 'F');
|
|
INSERT INTO t1 VALUES (5, 'M');
|
|
CREATE TABLE t2 (user_id INTEGER NOT NULL, date DATE);
|
|
INSERT INTO t2 VALUES (1, '2002-06-09');
|
|
INSERT INTO t2 VALUES (2, '2002-06-09');
|
|
INSERT INTO t2 VALUES (1, '2002-06-09');
|
|
INSERT INTO t2 VALUES (3, '2002-06-09');
|
|
INSERT INTO t2 VALUES (4, '2002-06-09');
|
|
INSERT INTO t2 VALUES (4, '2002-06-09');
|
|
INSERT INTO t2 VALUES (5, '2002-06-09');
|
|
SELECT t.*, MIN(t.rank) OVER (ROWS UNBOUNDED PRECEDING) min,
|
|
MAX(t.rank) OVER (ROWS UNBOUNDED PRECEDING) max FROM
|
|
(SELECT sex, id, date, ROW_NUMBER() OVER w AS row_no, RANK() OVER w AS `rank` FROM t1,t2
|
|
WHERE t1.id=t2.user_id
|
|
WINDOW w AS (PARTITION BY date ORDER BY id)
|
|
) AS t;
|
|
sex id date row_no rank min max
|
|
M 1 2002-06-09 1 1 1 1
|
|
M 1 2002-06-09 2 1 1 1
|
|
F 2 2002-06-09 3 3 1 3
|
|
F 3 2002-06-09 4 4 1 4
|
|
F 4 2002-06-09 5 5 1 5
|
|
F 4 2002-06-09 6 5 1 5
|
|
M 5 2002-06-09 7 7 1 7
|
|
----------------------------------------------------------------------
|
|
- Multiple windows
|
|
----------------------------------------------------------------------
|
|
SELECT t1.*, RANK() OVER (ORDER BY sex) `rank`, MIN(id) OVER (ORDER BY sex,id ROWS UNBOUNDED PRECEDING) min FROM t1;
|
|
id sex rank min
|
|
2 F 1 2
|
|
3 F 1 2
|
|
4 F 1 2
|
|
1 M 4 1
|
|
5 M 4 1
|
|
SELECT t1.*, PERCENT_RANK() OVER (ORDER BY sex) p_rank, MAX(id) OVER (ORDER BY sex,id ROWS UNBOUNDED PRECEDING) max FROM t1;
|
|
id sex p_rank max
|
|
2 F 0 2
|
|
3 F 0 3
|
|
4 F 0 4
|
|
1 M 0.75 4
|
|
5 M 0.75 5
|
|
SELECT t1.*, CUME_DIST() OVER (ORDER BY sex) c_dist, MIN(id) OVER (ORDER BY sex,id ROWS UNBOUNDED PRECEDING) min FROM t1;
|
|
id sex c_dist min
|
|
2 F 0.6 2
|
|
3 F 0.6 2
|
|
4 F 0.6 2
|
|
1 M 1 1
|
|
5 M 1 1
|
|
SELECT * from (SELECT t1.*, MIN(id) OVER (ROWS UNBOUNDED PRECEDING) min, RANK() OVER (ORDER BY sex) `rank` FROM t1) alias ORDER BY id;
|
|
id sex min rank
|
|
1 M 1 4
|
|
2 F 1 1
|
|
3 F 1 1
|
|
4 F 1 1
|
|
5 M 1 4
|
|
SELECT * from (SELECT t1.*, MAX(id) OVER (ROWS UNBOUNDED PRECEDING) max, PERCENT_RANK() OVER (ORDER BY sex) p_rank FROM t1) alias ORDER BY id;
|
|
id sex max p_rank
|
|
1 M 1 0.75
|
|
2 F 2 0
|
|
3 F 3 0
|
|
4 F 4 0
|
|
5 M 5 0.75
|
|
SELECT * from (SELECT t1.*, MAX(id) OVER (ROWS UNBOUNDED PRECEDING) max, CUME_DIST() OVER (ORDER BY sex) c_dist FROM t1) alias ORDER BY id;
|
|
id sex max c_dist
|
|
1 M 1 1
|
|
2 F 2 0.6
|
|
3 F 3 0.6
|
|
4 F 4 0.6
|
|
5 M 5 1
|
|
SELECT t1.*, MIN(id) OVER (ORDER BY id ROWS UNBOUNDED PRECEDING) min,
|
|
MAX(id) OVER (ORDER BY id ROWS UNBOUNDED PRECEDING) max,
|
|
RANK() OVER (ORDER BY sex,id) `rank`,
|
|
ROW_NUMBER() OVER (ORDER BY sex,id) row_num
|
|
FROM t1;
|
|
id sex min max rank row_num
|
|
2 F 1 2 1 1
|
|
3 F 1 3 2 2
|
|
4 F 1 4 3 3
|
|
1 M 1 1 4 4
|
|
5 M 1 5 5 5
|
|
a little more windows + subquery
|
|
SELECT t.*, MIN(id + r00 + r01) OVER (ORDER BY id ROWS UNBOUNDED PRECEDING) AS min,
|
|
MAX(id + r00 + r01) OVER (ORDER BY id ROWS UNBOUNDED PRECEDING) AS max FROM (
|
|
SELECT t1.*,
|
|
RANK() OVER (ORDER BY sex, id) AS r00,
|
|
RANK() OVER (ORDER BY sex, id DESC) AS r01,
|
|
RANK() OVER (ORDER BY sex, id DESC) AS r02,
|
|
RANK() OVER (PARTITION BY id ORDER BY sex) AS r03,
|
|
RANK() OVER (ORDER BY sex,id) AS r04,
|
|
RANK() OVER (ORDER BY sex,id) AS r05,
|
|
RANK() OVER (ORDER BY sex, id) AS r06,
|
|
RANK() OVER (ORDER BY sex, id) AS r07,
|
|
RANK() OVER (ORDER BY sex, id) AS r08,
|
|
RANK() OVER (ORDER BY sex, id) AS r09,
|
|
RANK() OVER (ORDER BY sex, id) AS r10,
|
|
RANK() OVER (ORDER BY sex, id) AS r11,
|
|
RANK() OVER (ORDER BY sex, id) AS r12,
|
|
RANK() OVER (ORDER BY sex, id) AS r13,
|
|
RANK() OVER (ORDER BY sex, id) AS r14
|
|
FROM t1) t;
|
|
id sex r00 r01 r02 r03 r04 r05 r06 r07 r08 r09 r10 r11 r12 r13 r14 min max
|
|
1 M 4 5 5 1 4 4 4 4 4 4 4 4 4 4 4 10 10
|
|
2 F 1 3 3 1 1 1 1 1 1 1 1 1 1 1 1 6 10
|
|
3 F 2 2 2 1 2 2 2 2 2 2 2 2 2 2 2 6 10
|
|
4 F 3 1 1 1 3 3 3 3 3 3 3 3 3 3 3 6 10
|
|
5 M 5 4 4 1 5 5 5 5 5 5 5 5 5 5 5 6 14
|
|
DROP TABLE t;
|
|
----------------------------------------------------------------------
|
|
- MIN/MAX with frames
|
|
----------------------------------------------------------------------
|
|
SELECT AVG(id) OVER w, MIN(id) OVER w min, MAX(id) OVER w max FROM t1
|
|
WINDOW w AS (PARTITION BY sex);
|
|
AVG(id) OVER w min max
|
|
3.0000 2 4
|
|
3.0000 2 4
|
|
3.0000 2 4
|
|
3.0000 1 5
|
|
3.0000 1 5
|
|
SELECT * FROM (
|
|
SELECT id, SUM(id) OVER w, MIN(id) OVER w min, MAX(id) OVER w max, sex FROM t1
|
|
WINDOW w AS (PARTITION BY sex)
|
|
) alias ORDER BY id;
|
|
id SUM(id) OVER w min max sex
|
|
1 6 1 5 M
|
|
2 9 2 4 F
|
|
3 9 2 4 F
|
|
4 9 2 4 F
|
|
5 6 1 5 M
|
|
SELECT MIN(id) OVER w min FROM t1 WINDOW w AS (PARTITION BY sex);
|
|
min
|
|
2
|
|
2
|
|
2
|
|
1
|
|
1
|
|
SELECT MAX(id) OVER w max FROM t1 WINDOW w AS (PARTITION BY sex);
|
|
max
|
|
4
|
|
4
|
|
4
|
|
5
|
|
5
|
|
SELECT id, MIN(id) OVER w min, MAX(id) OVER w max, sex FROM t1
|
|
WINDOW w AS (PARTITION BY sex ORDER BY id
|
|
ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING);
|
|
id min max sex
|
|
2 NULL NULL F
|
|
3 2 2 F
|
|
4 2 3 F
|
|
1 NULL NULL M
|
|
5 1 1 M
|
|
try the same as a view
|
|
CREATE VIEW v AS
|
|
SELECT id, SUM(id) OVER w, MIN(id) OVER w min, MAX(id) OVER w max, sex FROM t1
|
|
WINDOW w AS (PARTITION BY sex ORDER BY id
|
|
ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING);
|
|
SHOW CREATE VIEW v;
|
|
View Create View character_set_client collation_connection
|
|
v CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select `t1`.`id` AS `id`,sum(`t1`.`id`) OVER `w` AS `SUM(id) OVER w`,min(`t1`.`id`) OVER `w` AS `min`,max(`t1`.`id`) OVER `w` AS `max`,`t1`.`sex` AS `sex` from `t1` window `w` AS (PARTITION BY `t1`.`sex` ORDER BY `t1`.`id` ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING) utf8 utf8_general_ci
|
|
SELECT * FROM v;
|
|
id SUM(id) OVER w min max sex
|
|
2 NULL NULL NULL F
|
|
3 2 2 2 F
|
|
4 5 2 3 F
|
|
1 NULL NULL NULL M
|
|
5 1 1 1 M
|
|
DROP VIEW v;
|
|
SELECT SUM(id) OVER w, MIN(id) OVER w min,
|
|
MAX(id) OVER w max FROM t1
|
|
WINDOW w AS (PARTITION BY sex ORDER BY id
|
|
ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING);
|
|
SUM(id) OVER w min max
|
|
NULL NULL NULL
|
|
2 2 2
|
|
5 2 3
|
|
NULL NULL NULL
|
|
1 1 1
|
|
SELECT id, SUM(id) OVER w, MIN(id) OVER w min,
|
|
MAX(id) OVER w max, sex FROM t1
|
|
WINDOW w AS (PARTITION BY sex ORDER BY id
|
|
ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING);
|
|
id SUM(id) OVER w min max sex
|
|
2 7 3 4 F
|
|
3 4 4 4 F
|
|
4 NULL NULL NULL F
|
|
1 5 5 5 M
|
|
5 NULL NULL NULL M
|
|
SELECT SUM(id) OVER w, COUNT(*) OVER w, MIN(id) OVER w min,
|
|
MAX(id) OVER w max FROM t1
|
|
WINDOW w AS (PARTITION BY sex ORDER BY id
|
|
ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING);
|
|
SUM(id) OVER w COUNT(*) OVER w min max
|
|
7 2 3 4
|
|
4 1 4 4
|
|
NULL 0 NULL NULL
|
|
5 1 5 5
|
|
NULL 0 NULL NULL
|
|
SELECT id, AVG(id) OVER (ROWS UNBOUNDED PRECEDING) avg,
|
|
MIN(id) OVER (ROWS UNBOUNDED PRECEDING) min FROM t1;
|
|
id avg min
|
|
1 1.0000 1
|
|
2 1.5000 1
|
|
3 2.0000 1
|
|
4 2.5000 1
|
|
5 3.0000 1
|
|
SELECT id, AVG(id) OVER (ROWS UNBOUNDED PRECEDING),
|
|
MAX(id) OVER (ROWS UNBOUNDED PRECEDING) max FROM t1;
|
|
id AVG(id) OVER (ROWS UNBOUNDED PRECEDING) max
|
|
1 1.0000 1
|
|
2 1.5000 2
|
|
3 2.0000 3
|
|
4 2.5000 4
|
|
5 3.0000 5
|
|
SELECT id, AVG(id) OVER w avg, COUNT(id) OVER w count, MIN(id) OVER w min,
|
|
MAX(id) OVER w max FROM t1
|
|
WINDOW w AS (ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING);
|
|
id avg count min max
|
|
1 1.5000 2 1 2
|
|
2 2.0000 3 1 3
|
|
3 3.0000 3 2 4
|
|
4 4.0000 3 3 5
|
|
5 4.5000 2 4 5
|
|
MIN/MAX with double type
|
|
CREATE TABLE td(d DOUBLE);
|
|
INSERT INTO td VALUES (2);
|
|
INSERT INTO td VALUES (2);
|
|
INSERT INTO td VALUES (3);
|
|
INSERT INTO td VALUES (1);
|
|
INSERT INTO td VALUES (1.2);
|
|
INSERT INTO td VALUES (NULL);
|
|
SELECT d, MIN(d) OVER (ORDER BY d) min, MAX(d) OVER (ORDER BY d) max FROM td;
|
|
d min max
|
|
NULL NULL NULL
|
|
1 1 1
|
|
1.2 1 1.2
|
|
2 1 2
|
|
2 1 2
|
|
3 1 3
|
|
SELECT d, MIN(d) OVER () min, MAX(d) OVER () max FROM td;
|
|
d min max
|
|
2 1 3
|
|
2 1 3
|
|
3 1 3
|
|
1 1 3
|
|
1.2 1 3
|
|
NULL 1 3
|
|
SELECT d, MIN(d) OVER (ORDER BY d ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING) min FROM td;
|
|
d min
|
|
NULL 1
|
|
1 1
|
|
1.2 1
|
|
2 1
|
|
2 1.2
|
|
3 2
|
|
SELECT d, MAX(d) OVER (ORDER BY d ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING) max FROM td;
|
|
d max
|
|
NULL 1
|
|
1 1.2
|
|
1.2 2
|
|
2 2
|
|
2 3
|
|
3 3
|
|
TRUNCATE td;
|
|
INSERT INTO td VALUES (10);
|
|
INSERT INTO td VALUES (1);
|
|
INSERT INTO td VALUES (2);
|
|
INSERT INTO td VALUES (3);
|
|
INSERT INTO td VALUES (4);
|
|
INSERT INTO td VALUES (5);
|
|
INSERT INTO td VALUES (6);
|
|
INSERT INTO td VALUES (7);
|
|
INSERT INTO td VALUES (8);
|
|
INSERT INTO td VALUES (9);
|
|
SELECT d, MIN(d) OVER w min, MAX(d) OVER w max FROM td
|
|
WINDOW w AS (ORDER BY d RANGE BETWEEN 2 PRECEDING AND CURRENT ROW);
|
|
d min max
|
|
1 1 1
|
|
2 1 2
|
|
3 1 3
|
|
4 2 4
|
|
5 3 5
|
|
6 4 6
|
|
7 5 7
|
|
8 6 8
|
|
9 7 9
|
|
10 8 10
|
|
SELECT d, SUM(d) OVER w sum, MIN(d) OVER w min, MAX(d) OVER w max FROM td
|
|
WINDOW w AS (ORDER BY d RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING);
|
|
d sum min max
|
|
1 6 1 3
|
|
2 10 1 4
|
|
3 15 1 5
|
|
4 20 2 6
|
|
5 25 3 7
|
|
6 30 4 8
|
|
7 35 5 9
|
|
8 40 6 10
|
|
9 34 7 10
|
|
10 27 8 10
|
|
SELECT d, SUM(d) OVER w sum, MIN(d) OVER w min, MAX(d) OVER w max FROM td
|
|
WINDOW w AS (ORDER BY d RANGE BETWEEN CURRENT ROW AND 2 FOLLOWING);
|
|
d sum min max
|
|
1 6 1 3
|
|
2 9 2 4
|
|
3 12 3 5
|
|
4 15 4 6
|
|
5 18 5 7
|
|
6 21 6 8
|
|
7 24 7 9
|
|
8 27 8 10
|
|
9 19 9 10
|
|
10 10 10 10
|
|
INSERT INTO td SELECT * FROM td;
|
|
SELECT d, SUM(d) OVER w sum, MAX(d) OVER w max, MIN(d) OVER w min FROM td
|
|
WINDOW w AS (ORDER BY d RANGE BETWEEN 2 PRECEDING AND CURRENT ROW);
|
|
d sum max min
|
|
1 2 1 1
|
|
1 2 1 1
|
|
2 6 2 1
|
|
2 6 2 1
|
|
3 12 3 1
|
|
3 12 3 1
|
|
4 18 4 2
|
|
4 18 4 2
|
|
5 24 5 3
|
|
5 24 5 3
|
|
6 30 6 4
|
|
6 30 6 4
|
|
7 36 7 5
|
|
7 36 7 5
|
|
8 42 8 6
|
|
8 42 8 6
|
|
9 48 9 7
|
|
9 48 9 7
|
|
10 54 10 8
|
|
10 54 10 8
|
|
SELECT d, SUM(d) OVER w sum, MAX(d) OVER w max, MIN(d) OVER w min FROM td
|
|
WINDOW w AS (ORDER BY d RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING);
|
|
d sum max min
|
|
1 12 3 1
|
|
1 12 3 1
|
|
2 20 4 1
|
|
2 20 4 1
|
|
3 30 5 1
|
|
3 30 5 1
|
|
4 40 6 2
|
|
4 40 6 2
|
|
5 50 7 3
|
|
5 50 7 3
|
|
6 60 8 4
|
|
6 60 8 4
|
|
7 70 9 5
|
|
7 70 9 5
|
|
8 80 10 6
|
|
8 80 10 6
|
|
9 68 10 7
|
|
9 68 10 7
|
|
10 54 10 8
|
|
10 54 10 8
|
|
SELECT d, SUM(d) OVER w sum, MAX(d) OVER w max, MIN(d) OVER w min FROM td
|
|
WINDOW w AS (ORDER BY d RANGE BETWEEN CURRENT ROW AND 2 FOLLOWING);
|
|
d sum max min
|
|
1 12 3 1
|
|
1 12 3 1
|
|
2 18 4 2
|
|
2 18 4 2
|
|
3 24 5 3
|
|
3 24 5 3
|
|
4 30 6 4
|
|
4 30 6 4
|
|
5 36 7 5
|
|
5 36 7 5
|
|
6 42 8 6
|
|
6 42 8 6
|
|
7 48 9 7
|
|
7 48 9 7
|
|
8 54 10 8
|
|
8 54 10 8
|
|
9 38 10 9
|
|
9 38 10 9
|
|
10 20 10 10
|
|
10 20 10 10
|
|
DROP TABLE td;
|
|
----------------------------------------------------------------------
|
|
- Min/Max with frames in combination with non-framing window functions
|
|
- ROW_NUMBER and RANK
|
|
----------------------------------------------------------------------
|
|
SELECT ROW_NUMBER() OVER w `row_number`, id, MIN(id) OVER w min, MAX(id) OVER w max, sex FROM t1
|
|
WINDOW w AS (PARTITION BY sex ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);
|
|
row_number id min max sex
|
|
1 2 2 4 F
|
|
2 3 2 4 F
|
|
3 4 2 4 F
|
|
1 1 1 5 M
|
|
2 5 1 5 M
|
|
SELECT ROW_NUMBER() OVER w `row_number`, MIN(id) OVER w min,
|
|
MAX(id) OVER w max FROM t1
|
|
WINDOW w AS (PARTITION BY sex ORDER BY id
|
|
ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING);
|
|
row_number min max
|
|
1 3 4
|
|
2 4 4
|
|
3 NULL NULL
|
|
1 5 5
|
|
2 NULL NULL
|
|
INSERT INTO t1 VALUES (10, NULL);
|
|
SELECT RANK() OVER w `rank`, id, MIN(id) OVER w min, MAX(id) OVER w max, sex FROM t1
|
|
WINDOW w AS (PARTITION BY sex ORDER BY id);
|
|
rank id min max sex
|
|
1 10 10 10 NULL
|
|
1 2 2 2 F
|
|
2 3 2 3 F
|
|
3 4 2 4 F
|
|
1 1 1 1 M
|
|
2 5 1 5 M
|
|
SELECT RANK() OVER w `rank`, MIN(id) OVER w min,
|
|
MAX(id) OVER w max FROM t1
|
|
WINDOW w AS (PARTITION BY sex ORDER BY id
|
|
ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING);
|
|
rank min max
|
|
1 NULL NULL
|
|
1 3 4
|
|
2 4 4
|
|
3 NULL NULL
|
|
1 5 5
|
|
2 NULL NULL
|
|
SELECT id, sex, MIN(id) OVER w min,
|
|
MAX(id) OVER w max,
|
|
ROW_NUMBER() OVER w `row_number`,
|
|
RANK() OVER w `rank` FROM t1
|
|
WINDOW w AS (PARTITION BY sex ORDER BY id ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING);
|
|
id sex min max row_number rank
|
|
10 NULL NULL NULL 1 1
|
|
2 F NULL NULL 1 1
|
|
3 F 2 2 2 2
|
|
4 F 2 3 3 3
|
|
1 M NULL NULL 1 1
|
|
5 M 1 1 2 2
|
|
SELECT id, sex, MIN(id) OVER w min,
|
|
MAX(id) OVER w max,
|
|
ROW_NUMBER() OVER w `row_number`,
|
|
CUME_DIST() OVER w `cume_dist` FROM t1
|
|
WINDOW w AS (PARTITION BY sex ORDER BY id ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING);
|
|
id sex min max row_number cume_dist
|
|
10 NULL NULL NULL 1 1
|
|
2 F NULL NULL 1 0.3333333333333333
|
|
3 F 2 2 2 0.6666666666666666
|
|
4 F 2 3 3 1
|
|
1 M NULL NULL 1 0.5
|
|
5 M 1 1 2 1
|
|
----------------------------------------------------------------------
|
|
- Min/Max with RANGE frame specification
|
|
----------------------------------------------------------------------
|
|
Make t11 a clone of t1 but with an extra partitioning column, but other values
|
|
repeated, so we can test it the same frames work on more than one partition
|
|
CREATE TABLE t11 (id INTEGER, sex CHAR(1), p INTEGER DEFAULT 1);
|
|
INSERT INTO t11(id, sex) SELECT * FROM t1;
|
|
UPDATE t11 SET p=p+1;
|
|
INSERT INTO t11(id, sex) SELECT * FROM t1;
|
|
Make t22 a clone of t2 but with an extra partitioning column, but other values
|
|
repeated, so we can test it the same frames work on more than one partition
|
|
CREATE TABLE t22 (user_id INTEGER NOT NULL, date DATE, p INTEGER DEFAULT 1);
|
|
INSERT INTO t22(user_id, date) SELECT * FROM t2;
|
|
UPDATE t22 SET p=p+1;
|
|
INSERT INTO t22(user_id, date) SELECT * FROM t2;
|
|
SELECT id, MIN(id) OVER (ORDER BY id RANGE 2 PRECEDING) min FROM t1 ORDER BY id;
|
|
id min
|
|
1 1
|
|
2 1
|
|
3 1
|
|
4 2
|
|
5 3
|
|
10 10
|
|
SELECT id, MIN(id) OVER (ORDER BY id RANGE 2 PRECEDING) max FROM t1 ORDER BY id;
|
|
id max
|
|
1 1
|
|
2 1
|
|
3 1
|
|
4 2
|
|
5 3
|
|
10 10
|
|
SELECT id, MIN(id) OVER (ORDER BY id RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) min FROM t1 ORDER BY id;
|
|
id min
|
|
1 1
|
|
2 1
|
|
3 2
|
|
4 3
|
|
5 4
|
|
10 10
|
|
SELECT id, MAX(id) OVER (ORDER BY id RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) max FROM t1 ORDER BY id;
|
|
id max
|
|
1 2
|
|
2 3
|
|
3 4
|
|
4 5
|
|
5 5
|
|
10 10
|
|
SELECT id, MIN(id) OVER (ORDER BY id RANGE UNBOUNDED PRECEDING) min FROM t1 ORDER BY id;
|
|
id min
|
|
1 1
|
|
2 1
|
|
3 1
|
|
4 1
|
|
5 1
|
|
10 1
|
|
SELECT id, MAX(id) OVER (ORDER BY id RANGE UNBOUNDED PRECEDING) max FROM t1 ORDER BY id;
|
|
id max
|
|
1 1
|
|
2 2
|
|
3 3
|
|
4 4
|
|
5 5
|
|
10 10
|
|
SELECT p, id, MIN(id) OVER (PARTITION BY p ORDER BY id RANGE 2 PRECEDING) min FROM t11 ORDER BY p,id;
|
|
p id min
|
|
1 1 1
|
|
1 2 1
|
|
1 3 1
|
|
1 4 2
|
|
1 5 3
|
|
1 10 10
|
|
2 1 1
|
|
2 2 1
|
|
2 3 1
|
|
2 4 2
|
|
2 5 3
|
|
2 10 10
|
|
SELECT p, id, MAX(id) OVER (PARTITION BY p ORDER BY id RANGE 2 PRECEDING) max FROM t11 ORDER BY p,id;
|
|
p id max
|
|
1 1 1
|
|
1 2 2
|
|
1 3 3
|
|
1 4 4
|
|
1 5 5
|
|
1 10 10
|
|
2 1 1
|
|
2 2 2
|
|
2 3 3
|
|
2 4 4
|
|
2 5 5
|
|
2 10 10
|
|
SELECT p, id, MIN(id) OVER (PARTITION BY p ORDER BY id RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) min FROM t11 ORDER BY p,id;
|
|
p id min
|
|
1 1 1
|
|
1 2 1
|
|
1 3 2
|
|
1 4 3
|
|
1 5 4
|
|
1 10 10
|
|
2 1 1
|
|
2 2 1
|
|
2 3 2
|
|
2 4 3
|
|
2 5 4
|
|
2 10 10
|
|
SELECT p, id, MAX(id) OVER (PARTITION BY p ORDER BY id RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) max FROM t11 ORDER BY p,id;
|
|
p id max
|
|
1 1 2
|
|
1 2 3
|
|
1 3 4
|
|
1 4 5
|
|
1 5 5
|
|
1 10 10
|
|
2 1 2
|
|
2 2 3
|
|
2 3 4
|
|
2 4 5
|
|
2 5 5
|
|
2 10 10
|
|
SELECT p, id, MIN(id) OVER (PARTITION BY p ORDER BY id RANGE UNBOUNDED PRECEDING) min FROM t11 ORDER BY p,id;
|
|
p id min
|
|
1 1 1
|
|
1 2 1
|
|
1 3 1
|
|
1 4 1
|
|
1 5 1
|
|
1 10 1
|
|
2 1 1
|
|
2 2 1
|
|
2 3 1
|
|
2 4 1
|
|
2 5 1
|
|
2 10 1
|
|
SELECT p, id, MAX(id) OVER (PARTITION BY p ORDER BY id RANGE UNBOUNDED PRECEDING) max FROM t11 ORDER BY p,id;
|
|
p id max
|
|
1 1 1
|
|
1 2 2
|
|
1 3 3
|
|
1 4 4
|
|
1 5 5
|
|
1 10 10
|
|
2 1 1
|
|
2 2 2
|
|
2 3 3
|
|
2 4 4
|
|
2 5 5
|
|
2 10 10
|
|
Implicit frame due to ORDER BY, with last in peer group as upper bound
|
|
SELECT user_id, MIN(user_id) OVER w, MAX(user_id) OVER w FROM t2 WINDOW w AS (ORDER BY user_id);
|
|
user_id MIN(user_id) OVER w MAX(user_id) OVER w
|
|
1 1 1
|
|
1 1 1
|
|
2 1 2
|
|
3 1 3
|
|
4 1 4
|
|
4 1 4
|
|
5 1 5
|
|
SELECT p, user_id, MIN(user_id) OVER w, MAX(user_id) OVER w FROM t22 WINDOW w AS (PARTITION BY p ORDER BY user_id) ORDER BY p;
|
|
p user_id MIN(user_id) OVER w MAX(user_id) OVER w
|
|
1 1 1 1
|
|
1 1 1 1
|
|
1 2 1 2
|
|
1 3 1 3
|
|
1 4 1 4
|
|
1 4 1 4
|
|
1 5 1 5
|
|
2 1 1 1
|
|
2 1 1 1
|
|
2 2 1 2
|
|
2 3 1 3
|
|
2 4 1 4
|
|
2 4 1 4
|
|
2 5 1 5
|
|
SELECT user_id, MIN(user_id) OVER w, MAX(user_id) OVER w1 FROM t2
|
|
WINDOW w AS (ORDER BY user_id), w1 AS (ORDER BY user_id);
|
|
user_id MIN(user_id) OVER w MAX(user_id) OVER w1
|
|
1 1 1
|
|
1 1 1
|
|
2 1 2
|
|
3 1 3
|
|
4 1 4
|
|
4 1 4
|
|
5 1 5
|
|
Check descending order by with RANGE: 2 PRECEDING in this case means larger than
|
|
current row.
|
|
SELECT NTILE(5) OVER w, ROW_NUMBER() OVER w, id, MIN(id) OVER w FROM t1
|
|
WINDOW w AS (ORDER BY id DESC RANGE 2 PRECEDING);
|
|
NTILE(5) OVER w ROW_NUMBER() OVER w id MIN(id) OVER w
|
|
1 1 10 10
|
|
1 2 5 5
|
|
2 3 4 4
|
|
3 4 3 3
|
|
4 5 2 2
|
|
5 6 1 1
|
|
SELECT p, NTILE(5) OVER w, ROW_NUMBER() OVER w, id, MAX(id) OVER w FROM t11
|
|
WINDOW w AS (PARTITION BY p ORDER BY id DESC RANGE 2 PRECEDING);
|
|
p NTILE(5) OVER w ROW_NUMBER() OVER w id MAX(id) OVER w
|
|
1 1 1 10 10
|
|
1 1 2 5 5
|
|
1 2 3 4 5
|
|
1 3 4 3 5
|
|
1 4 5 2 4
|
|
1 5 6 1 3
|
|
2 1 1 10 10
|
|
2 1 2 5 5
|
|
2 2 3 4 5
|
|
2 3 4 3 5
|
|
2 4 5 2 4
|
|
2 5 6 1 3
|
|
SELECT NTILE(5) OVER w, ROW_NUMBER() OVER w, id, MIN(id) OVER w FROM t1
|
|
WINDOW w AS (ORDER BY id DESC RANGE INTERVAL 2 MONTH PRECEDING);
|
|
ERROR HY000: Window 'w' with RANGE frame has ORDER BY expression of numeric type, INTERVAL bound value not allowed.
|
|
DROP TABLE t11,t22,t1,t2;
|
|
CREATE TABLE t3(d DOUBLE);
|
|
INSERT INTO t3 VALUES (1.1);
|
|
INSERT INTO t3 VALUES (1.9);
|
|
INSERT INTO t3 VALUES (4.0);
|
|
INSERT INTO t3 VALUES (8.3);
|
|
INSERT INTO t3 VALUES (16.0);
|
|
INSERT INTO t3 VALUES (24.0);
|
|
INSERT INTO t3 VALUES (20.1);
|
|
INSERT INTO t3 VALUES (22.0);
|
|
INSERT INTO t3 VALUES (23.0);
|
|
SELECT d, MIN(d) OVER w min, MAX(d) OVER w max, COUNT(*) OVER w
|
|
FROM t3 WINDOW w AS (ORDER BY d RANGE BETWEEN 2.1 PRECEDING AND 1.1 FOLLOWING);
|
|
d min max COUNT(*) OVER w
|
|
1.1 1.1 1.9 2
|
|
1.9 1.1 1.9 2
|
|
4 1.9 4 2
|
|
8.3 8.3 8.3 1
|
|
16 16 16 1
|
|
20.1 20.1 20.1 1
|
|
22 20.1 23 3
|
|
23 22 24 3
|
|
24 22 24 3
|
|
DROP TABLE t3;
|
|
----------------------------------------------------------------------
|
|
- Min/Max over JSON
|
|
----------------------------------------------------------------------
|
|
CREATE TABLE tj(j JSON, i INT DEFAULT 7);
|
|
INSERT INTO tj(j) VALUES ('1');
|
|
INSERT INTO tj(j) VALUES ('2');
|
|
INSERT INTO tj(j) VALUES ('3');
|
|
INSERT INTO tj(j) VALUES ('4');
|
|
INSERT INTO tj(j) VALUES ('5');
|
|
INSERT INTO tj(j) VALUES (NULL);
|
|
INSERT INTO tj(j) VALUES ('3.14');
|
|
INSERT INTO tj(j) VALUES ('[1,2,3]');
|
|
INSERT INTO tj(j) VALUES (NULL);
|
|
SELECT MIN(j) OVER () AS JSON FROM tj;
|
|
JSON
|
|
1
|
|
1
|
|
1
|
|
1
|
|
1
|
|
1
|
|
1
|
|
1
|
|
1
|
|
SELECT j, JSON_TYPE(j), MIN(j) OVER (ORDER BY j ROWS 3 PRECEDING) FROM tj;
|
|
j JSON_TYPE(j) MIN(j) OVER (ORDER BY j ROWS 3 PRECEDING)
|
|
NULL NULL NULL
|
|
NULL NULL NULL
|
|
1 INTEGER 1
|
|
2 INTEGER 1
|
|
3 INTEGER 1
|
|
3.14 DOUBLE 1
|
|
4 INTEGER 2
|
|
5 INTEGER 3
|
|
[1, 2, 3] ARRAY 3.14
|
|
Warnings:
|
|
Warning 1235 This version of MySQL doesn't yet support 'sorting of non-scalar JSON values'
|
|
SELECT j, JSON_TYPE(j), MAX(j) OVER (ORDER BY j ROWS 3 PRECEDING) FROM tj;
|
|
j JSON_TYPE(j) MAX(j) OVER (ORDER BY j ROWS 3 PRECEDING)
|
|
NULL NULL NULL
|
|
NULL NULL NULL
|
|
1 INTEGER 1
|
|
2 INTEGER 2
|
|
3 INTEGER 3
|
|
3.14 DOUBLE 3.14
|
|
4 INTEGER 4
|
|
5 INTEGER 5
|
|
[1, 2, 3] ARRAY [1, 2, 3]
|
|
Warnings:
|
|
Warning 1235 This version of MySQL doesn't yet support 'sorting of non-scalar JSON values'
|
|
SELECT j, JSON_TYPE(j), JSON_TYPE(MIN(j) OVER (ORDER BY j RANGE 3 PRECEDING)) FROM tj;
|
|
ERROR HY000: Window '<unnamed window>' with RANGE N PRECEDING/FOLLOWING frame requires exactly one ORDER BY expression, of numeric or temporal type
|
|
INSERT INTO tj(j) VALUES ('3.14');
|
|
SELECT j, JSON_TYPE(j), JSON_TYPE(MAX(j) OVER (ORDER BY j ROWS 3 PRECEDING)) FROM tj;
|
|
j JSON_TYPE(j) JSON_TYPE(MAX(j) OVER (ORDER BY j ROWS 3 PRECEDING))
|
|
NULL NULL NULL
|
|
NULL NULL NULL
|
|
1 INTEGER INTEGER
|
|
2 INTEGER INTEGER
|
|
3 INTEGER INTEGER
|
|
3.14 DOUBLE DOUBLE
|
|
3.14 DOUBLE DOUBLE
|
|
4 INTEGER INTEGER
|
|
5 INTEGER INTEGER
|
|
[1, 2, 3] ARRAY ARRAY
|
|
Warnings:
|
|
Warning 1235 This version of MySQL doesn't yet support 'sorting of non-scalar JSON values'
|
|
INSERT INTO tj(j) VALUES ('[1,2,3]');
|
|
SELECT j,
|
|
JSON_TYPE(j),
|
|
MIN(CASE WHEN JSON_TYPE(j) = 'ARRAY' THEN j->"$[0]" ELSE j END)
|
|
OVER (ORDER BY j ROWS 3 PRECEDING)
|
|
FROM tj;
|
|
j JSON_TYPE(j) MIN(CASE WHEN JSON_TYPE(j) = 'ARRAY' THEN j->"$[0]" ELSE j END)
|
|
OVER (ORDER BY j ROWS 3 PRECEDING)
|
|
NULL NULL NULL
|
|
NULL NULL NULL
|
|
1 INTEGER 1
|
|
2 INTEGER 1
|
|
3 INTEGER 1
|
|
3.14 DOUBLE 1
|
|
3.14 DOUBLE 2
|
|
4 INTEGER 3
|
|
5 INTEGER 3.14
|
|
[1, 2, 3] ARRAY 1
|
|
[1, 2, 3] ARRAY 1
|
|
Warnings:
|
|
Warning 1235 This version of MySQL doesn't yet support 'sorting of non-scalar JSON values'
|
|
SELECT DISTINCT i,NTILE(3) OVER (ORDER BY i), MAX(i) OVER (), COUNT(*) OVER () FROM tj ORDER BY NTILE(3) OVER (ORDER BY i);
|
|
i NTILE(3) OVER (ORDER BY i) MAX(i) OVER () COUNT(*) OVER ()
|
|
7 1 7 11
|
|
7 2 7 11
|
|
7 3 7 11
|
|
UPDATE tj SET i=i+CASE WHEN JSON_TYPE(j) = 'ARRAY' THEN 1 ELSE j END;
|
|
UPDATE tj SET i=7 where i=8 AND JSON_TYPE(j) != 'ARRAY';
|
|
CREATE TABLE tj2 AS SELECT * FROM tj;
|
|
UPDATE tj2 SET i=MOD(i,3);
|
|
SELECT * FROM tj2;
|
|
j i
|
|
1 1
|
|
2 0
|
|
3 1
|
|
4 2
|
|
5 0
|
|
NULL NULL
|
|
3.14 1
|
|
[1, 2, 3] 2
|
|
NULL NULL
|
|
3.14 1
|
|
[1, 2, 3] 2
|
|
With GROUP BY
|
|
SELECT COUNT(*) OVER (), MOD(MIN(i),2) FROM tj2 GROUP BY i ;
|
|
COUNT(*) OVER () MOD(MIN(i),2)
|
|
4 1
|
|
4 0
|
|
4 0
|
|
4 NULL
|
|
SELECT DISTINCT COUNT(*) OVER (), MOD(MAX(i),2) FROM tj2 GROUP BY i ;
|
|
COUNT(*) OVER () MOD(MAX(i),2)
|
|
4 1
|
|
4 0
|
|
4 NULL
|
|
Bug fix GROUP BY with window function referring column used in grouping expression
|
|
SELECT i, MIN(i) OVER (), MOD(MIN(i),2) FROM tj2 GROUP BY i ;
|
|
i MIN(i) OVER () MOD(MIN(i),2)
|
|
1 0 1
|
|
0 0 0
|
|
2 0 0
|
|
NULL 0 NULL
|
|
SELECT i, MAX(MAX(i)) OVER (), MAX(i) OVER (ORDER BY i), MOD(MAX(i),2), MAX(i) FROM tj2 GROUP BY i ;
|
|
i MAX(MAX(i)) OVER () MAX(i) OVER (ORDER BY i) MOD(MAX(i),2) MAX(i)
|
|
NULL 2 NULL NULL NULL
|
|
0 2 0 0 0
|
|
1 2 1 1 1
|
|
2 2 2 0 2
|
|
SELECT i, MIN(i) OVER (ORDER BY i) FROM tj UNION ALL SELECT i, MIN(i) OVER (ORDER BY i) FROM tj;
|
|
i MIN(i) OVER (ORDER BY i)
|
|
NULL NULL
|
|
NULL NULL
|
|
7 7
|
|
8 7
|
|
8 7
|
|
9 7
|
|
10 7
|
|
10 7
|
|
10 7
|
|
11 7
|
|
12 7
|
|
NULL NULL
|
|
NULL NULL
|
|
7 7
|
|
8 7
|
|
8 7
|
|
9 7
|
|
10 7
|
|
10 7
|
|
10 7
|
|
11 7
|
|
12 7
|
|
SELECT j,CAST(MIN(j) OVER (PARTITION BY i) AS JSON), CAST(MAX(j) OVER () AS JSON) FROM tj;
|
|
j CAST(MIN(j) OVER (PARTITION BY i) AS JSON) CAST(MAX(j) OVER () AS JSON)
|
|
NULL NULL [1, 2, 3]
|
|
NULL NULL [1, 2, 3]
|
|
1 1 [1, 2, 3]
|
|
[1, 2, 3] [1, 2, 3] [1, 2, 3]
|
|
[1, 2, 3] [1, 2, 3] [1, 2, 3]
|
|
2 2 [1, 2, 3]
|
|
3 3 [1, 2, 3]
|
|
3.14 3 [1, 2, 3]
|
|
3.14 3 [1, 2, 3]
|
|
4 4 [1, 2, 3]
|
|
5 5 [1, 2, 3]
|
|
SELECT j,CAST(MIN(j) OVER (PARTITION BY i ROWS UNBOUNDED PRECEDING) AS JSON), CAST(MAX(j) OVER (PARTITION BY i ROWS UNBOUNDED PRECEDING) AS JSON) FROM tj;
|
|
j CAST(MIN(j) OVER (PARTITION BY i ROWS UNBOUNDED PRECEDING) AS JSON) CAST(MAX(j) OVER (PARTITION BY i ROWS UNBOUNDED PRECEDING) AS JSON)
|
|
NULL NULL NULL
|
|
NULL NULL NULL
|
|
1 1 1
|
|
[1, 2, 3] [1, 2, 3] [1, 2, 3]
|
|
[1, 2, 3] [1, 2, 3] [1, 2, 3]
|
|
2 2 2
|
|
3 3 3
|
|
3.14 3 3.14
|
|
3.14 3 3.14
|
|
4 4 4
|
|
5 5 5
|
|
DROP TABLE tj,tj2;
|
|
CREATE TABLE t1 (i INTEGER, j INTEGER);
|
|
INSERT INTO t1 VALUES (NULL,3),(NULL,3),(NULL,30),(NULL,30),(4,3),
|
|
(2,5),(3,7),(1,10),(5,20),(3,30);
|
|
SELECT i, j, MIN(i) OVER (PARTITION BY j ORDER BY i) FROM t1;
|
|
i j MIN(i) OVER (PARTITION BY j ORDER BY i)
|
|
NULL 3 NULL
|
|
NULL 3 NULL
|
|
4 3 4
|
|
2 5 2
|
|
3 7 3
|
|
1 10 1
|
|
5 20 5
|
|
NULL 30 NULL
|
|
NULL 30 NULL
|
|
3 30 3
|
|
DROP TABLE t1;
|
|
CREATE TABLE t1(a INT);
|
|
INSERT INTO t1 VALUES(1),(1),(1),(1),(NULL),(NULL),(NULL),(NULL);
|
|
SELECT a, MAX(a) OVER (ORDER BY a DESC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) FROM t1;
|
|
a MAX(a) OVER (ORDER BY a DESC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
|
|
1 1
|
|
1 1
|
|
1 1
|
|
1 1
|
|
NULL 1
|
|
NULL NULL
|
|
NULL NULL
|
|
NULL NULL
|
|
SELECT a, MAX(a) OVER (ORDER BY a DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM t1;
|
|
a MAX(a) OVER (ORDER BY a DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
|
|
1 1
|
|
1 1
|
|
1 1
|
|
1 1
|
|
NULL NULL
|
|
NULL NULL
|
|
NULL NULL
|
|
NULL NULL
|
|
SELECT a, MIN(a) OVER (ORDER BY a DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) FROM t1;
|
|
a MIN(a) OVER (ORDER BY a DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
|
|
1 1
|
|
1 1
|
|
1 1
|
|
1 1
|
|
NULL NULL
|
|
NULL NULL
|
|
NULL NULL
|
|
NULL NULL
|
|
TRUNCATE t1;
|
|
INSERT INTO t1 VALUES (2), (1), (4), (3), (NULL), (NULL), (NULL), (NULL);
|
|
SELECT a, MIN(a) OVER w FROM t1 WINDOW w AS (ORDER BY a DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING);
|
|
a MIN(a) OVER w
|
|
4 1
|
|
3 1
|
|
2 1
|
|
1 1
|
|
NULL NULL
|
|
NULL NULL
|
|
NULL NULL
|
|
NULL NULL
|
|
SELECT a, MAX(a) OVER w, MIN(a) OVER w FROM t1 WINDOW w AS (ORDER BY a DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING);
|
|
a MAX(a) OVER w MIN(a) OVER w
|
|
4 4 1
|
|
3 3 1
|
|
2 2 1
|
|
1 1 1
|
|
NULL NULL NULL
|
|
NULL NULL NULL
|
|
NULL NULL NULL
|
|
NULL NULL NULL
|
|
SELECT a, COUNT(a) OVER w, MAX(a) OVER w, MIN(a) OVER w FROM t1 WINDOW w AS (ORDER BY a DESC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW);
|
|
a COUNT(a) OVER w MAX(a) OVER w MIN(a) OVER w
|
|
4 1 4 4
|
|
3 2 4 3
|
|
2 2 3 2
|
|
1 2 2 1
|
|
NULL 1 1 1
|
|
NULL 0 NULL NULL
|
|
NULL 0 NULL NULL
|
|
NULL 0 NULL NULL
|
|
SELECT a, COUNT(a) OVER w, MIN(a) OVER w FROM t1 WINDOW w AS (ORDER BY a DESC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW);
|
|
a COUNT(a) OVER w MIN(a) OVER w
|
|
4 1 4
|
|
3 2 3
|
|
2 2 2
|
|
1 2 1
|
|
NULL 1 1
|
|
NULL 0 NULL
|
|
NULL 0 NULL
|
|
NULL 0 NULL
|
|
SELECT a, MIN(a) OVER w FROM t1 WINDOW w AS (ORDER BY a DESC RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING);
|
|
a MIN(a) OVER w
|
|
4 1
|
|
3 1
|
|
2 1
|
|
1 1
|
|
NULL NULL
|
|
NULL NULL
|
|
NULL NULL
|
|
NULL NULL
|
|
SELECT a, MAX(a) OVER w, MIN(a) OVER w FROM t1 WINDOW w AS (ORDER BY a DESC RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING);
|
|
a MAX(a) OVER w MIN(a) OVER w
|
|
4 4 1
|
|
3 3 1
|
|
2 2 1
|
|
1 1 1
|
|
NULL NULL NULL
|
|
NULL NULL NULL
|
|
NULL NULL NULL
|
|
NULL NULL NULL
|
|
SELECT a, COUNT(a) OVER w, MAX(a) OVER w, MIN(a) OVER w FROM t1 WINDOW w AS (ORDER BY a DESC RANGE BETWEEN 1 PRECEDING AND CURRENT ROW);
|
|
a COUNT(a) OVER w MAX(a) OVER w MIN(a) OVER w
|
|
4 1 4 4
|
|
3 2 4 3
|
|
2 2 3 2
|
|
1 2 2 1
|
|
NULL 0 NULL NULL
|
|
NULL 0 NULL NULL
|
|
NULL 0 NULL NULL
|
|
NULL 0 NULL NULL
|
|
SELECT a, COUNT(a) OVER w, MIN(a) OVER w FROM t1 WINDOW w AS (ORDER BY a DESC RANGE BETWEEN 1 PRECEDING AND CURRENT ROW);
|
|
a COUNT(a) OVER w MIN(a) OVER w
|
|
4 1 4
|
|
3 2 3
|
|
2 2 2
|
|
1 2 1
|
|
NULL 0 NULL
|
|
NULL 0 NULL
|
|
NULL 0 NULL
|
|
NULL 0 NULL
|
|
SELECT a, MIN(a) OVER w min, MAX(a) OVER w max FROM t1 WINDOW w AS (ORDER BY a DESC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);
|
|
a min max
|
|
4 4 4
|
|
3 3 4
|
|
2 2 4
|
|
1 1 4
|
|
NULL 1 4
|
|
NULL 1 4
|
|
NULL 1 4
|
|
NULL 1 4
|
|
SELECT a, MIN(a) OVER w min, MAX(a) OVER w max FROM t1 WINDOW w AS (ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING);
|
|
a min max
|
|
NULL 1 4
|
|
NULL 1 4
|
|
NULL 1 4
|
|
NULL 1 4
|
|
1 1 4
|
|
2 2 4
|
|
3 3 4
|
|
4 4 4
|
|
TRUNCATE t1;
|
|
INSERT INTO t1 VALUES (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL);
|
|
SELECT a, MIN(a) OVER w FROM t1 WINDOW w AS (ORDER BY a DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING);
|
|
a MIN(a) OVER w
|
|
NULL NULL
|
|
NULL NULL
|
|
NULL NULL
|
|
NULL NULL
|
|
NULL NULL
|
|
NULL NULL
|
|
NULL NULL
|
|
NULL NULL
|
|
SELECT a, MAX(a) OVER w, MIN(a) OVER w FROM t1 WINDOW w AS (ORDER BY a DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING);
|
|
a MAX(a) OVER w MIN(a) OVER w
|
|
NULL NULL NULL
|
|
NULL NULL NULL
|
|
NULL NULL NULL
|
|
NULL NULL NULL
|
|
NULL NULL NULL
|
|
NULL NULL NULL
|
|
NULL NULL NULL
|
|
NULL NULL NULL
|
|
SELECT a, COUNT(a) OVER w, MAX(a) OVER w, MIN(a) OVER w FROM t1 WINDOW w AS (ORDER BY a DESC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW);
|
|
a COUNT(a) OVER w MAX(a) OVER w MIN(a) OVER w
|
|
NULL 0 NULL NULL
|
|
NULL 0 NULL NULL
|
|
NULL 0 NULL NULL
|
|
NULL 0 NULL NULL
|
|
NULL 0 NULL NULL
|
|
NULL 0 NULL NULL
|
|
NULL 0 NULL NULL
|
|
NULL 0 NULL NULL
|
|
SELECT a, COUNT(a) OVER w, MIN(a) OVER w FROM t1 WINDOW w AS (ORDER BY a DESC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW);
|
|
a COUNT(a) OVER w MIN(a) OVER w
|
|
NULL 0 NULL
|
|
NULL 0 NULL
|
|
NULL 0 NULL
|
|
NULL 0 NULL
|
|
NULL 0 NULL
|
|
NULL 0 NULL
|
|
NULL 0 NULL
|
|
NULL 0 NULL
|
|
SELECT a, MIN(a) OVER w FROM t1 WINDOW w AS (ORDER BY a DESC RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING);
|
|
a MIN(a) OVER w
|
|
NULL NULL
|
|
NULL NULL
|
|
NULL NULL
|
|
NULL NULL
|
|
NULL NULL
|
|
NULL NULL
|
|
NULL NULL
|
|
NULL NULL
|
|
SELECT a, MAX(a) OVER w, MIN(a) OVER w FROM t1 WINDOW w AS (ORDER BY a DESC RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING);
|
|
a MAX(a) OVER w MIN(a) OVER w
|
|
NULL NULL NULL
|
|
NULL NULL NULL
|
|
NULL NULL NULL
|
|
NULL NULL NULL
|
|
NULL NULL NULL
|
|
NULL NULL NULL
|
|
NULL NULL NULL
|
|
NULL NULL NULL
|
|
SELECT a, COUNT(a) OVER w, MAX(a) OVER w, MIN(a) OVER w FROM t1 WINDOW w AS (ORDER BY a DESC RANGE BETWEEN 1 PRECEDING AND CURRENT ROW);
|
|
a COUNT(a) OVER w MAX(a) OVER w MIN(a) OVER w
|
|
NULL 0 NULL NULL
|
|
NULL 0 NULL NULL
|
|
NULL 0 NULL NULL
|
|
NULL 0 NULL NULL
|
|
NULL 0 NULL NULL
|
|
NULL 0 NULL NULL
|
|
NULL 0 NULL NULL
|
|
NULL 0 NULL NULL
|
|
SELECT a, COUNT(a) OVER w, MIN(a) OVER w FROM t1 WINDOW w AS (ORDER BY a DESC RANGE BETWEEN 1 PRECEDING AND CURRENT ROW);
|
|
a COUNT(a) OVER w MIN(a) OVER w
|
|
NULL 0 NULL
|
|
NULL 0 NULL
|
|
NULL 0 NULL
|
|
NULL 0 NULL
|
|
NULL 0 NULL
|
|
NULL 0 NULL
|
|
NULL 0 NULL
|
|
NULL 0 NULL
|
|
DROP TABLE t1;
|