1446 lines
47 KiB
Plaintext
1446 lines
47 KiB
Plaintext
SET windowing_use_high_precision= ON;
|
|
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, STD(i) OVER (ROWS UNBOUNDED PRECEDING) std,
|
|
VARIANCE(j) OVER (ROWS UNBOUNDED PRECEDING) variance FROM t;
|
|
i j std variance
|
|
1 1 0 0
|
|
1 4 0 2.25
|
|
1 2 0 1.5555555555555556
|
|
1 4 0 1.6875
|
|
SELECT i, j, STD(i) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) std,
|
|
VARIANCE(j) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) variance FROM t;
|
|
i j std variance
|
|
1 1 0 0
|
|
1 4 0 2.25
|
|
1 2 0 1.5555555555555556
|
|
1 4 0 1.6875
|
|
SELECT i, j, STD(i+j) OVER (ROWS UNBOUNDED PRECEDING) std FROM t ORDER BY std;
|
|
i j std
|
|
1 1 0
|
|
1 2 1.247219128924647
|
|
1 4 1.299038105676658
|
|
1 4 1.5
|
|
SELECT i, j, VAR_POP(i+j) OVER (ROWS UNBOUNDED PRECEDING) variance FROM t ORDER BY variance;
|
|
i j variance
|
|
1 1 0
|
|
1 2 1.5555555555555556
|
|
1 4 1.6875
|
|
1 4 2.25
|
|
SELECT i, j, STD(i+j) OVER (ROWS UNBOUNDED PRECEDING) std FROM t ORDER BY std DESC;
|
|
i j std
|
|
1 4 1.5
|
|
1 4 1.299038105676658
|
|
1 2 1.247219128924647
|
|
1 1 0
|
|
SELECT i, j, VARIANCE(i+j) OVER (ROWS UNBOUNDED PRECEDING) variance FROM t ORDER BY variance DESC;
|
|
i j variance
|
|
1 4 2.25
|
|
1 4 1.6875
|
|
1 2 1.5555555555555556
|
|
1 1 0
|
|
Single ordered partition
|
|
SELECT i, j, STD(i) OVER (ORDER BY j ROWS UNBOUNDED PRECEDING) std,
|
|
VARIANCE(j) OVER (ORDER BY j ROWS UNBOUNDED PRECEDING) variance FROM t;
|
|
i j std variance
|
|
1 1 0 0
|
|
1 2 0 0.25
|
|
1 4 0 1.5555555555555554
|
|
1 4 0 1.6874999999999998
|
|
SELECT i, j, STDDEV_SAMP(j) OVER (ORDER BY j ROWS UNBOUNDED PRECEDING) std,
|
|
VARIANCE(i) OVER (ORDER BY i ROWS UNBOUNDED PRECEDING) variance FROM t ORDER BY std ;
|
|
i j std variance
|
|
1 1 NULL 0
|
|
1 2 0.7071067811865476 0
|
|
1 4 1.4999999999999998 0
|
|
1 4 1.5275252316519465 0
|
|
SELECT i, j, STD(i+j) OVER (ORDER BY j ROWS UNBOUNDED PRECEDING) std FROM t ORDER BY std DESC;
|
|
i j std
|
|
1 4 1.2990381056766578
|
|
1 4 1.247219128924647
|
|
1 2 0.5
|
|
1 1 0
|
|
SELECT i, j, VAR_POP(i+j) OVER (ORDER BY j ROWS UNBOUNDED PRECEDING) variance FROM t ORDER BY variance DESC;
|
|
i j variance
|
|
1 4 1.6874999999999998
|
|
1 4 1.5555555555555554
|
|
1 2 0.25
|
|
1 1 0
|
|
SELECT i, j, STDDEV_SAMP(i+j) OVER (ORDER BY j DESC ROWS UNBOUNDED PRECEDING) std FROM t ORDER BY std DESC;
|
|
i j std
|
|
1 1 1.4999999999999998
|
|
1 2 1.1547005383792515
|
|
1 4 0
|
|
1 4 NULL
|
|
SELECT i, j, VARIANCE(i+j) OVER (ORDER BY j DESC ROWS UNBOUNDED PRECEDING) variance FROM t ORDER BY variance DESC;
|
|
i j variance
|
|
1 1 1.6874999999999998
|
|
1 2 0.8888888888888887
|
|
1 4 0
|
|
1 4 0
|
|
Check that we eliminate redundant sorting in ORDER BY even with wfs
|
|
SELECT i, j, STD(i+j) OVER (ROWS UNBOUNDED PRECEDING) std FROM t ORDER BY NULL DESC;
|
|
i j std
|
|
1 1 0
|
|
1 4 1.5
|
|
1 2 1.247219128924647
|
|
1 4 1.299038105676658
|
|
EXPLAIN FORMAT=JSON SELECT i, j, std(i+j) OVER (ROWS UNBOUNDED PRECEDING) STD FROM t ORDER BY NULL DESC;
|
|
EXPLAIN
|
|
{
|
|
"query_block": {
|
|
"select_id": 1,
|
|
"cost_info": {
|
|
"query_cost": "0.65"
|
|
},
|
|
"ordering_operation": {
|
|
"using_filesort": false,
|
|
"windowing": {
|
|
"windows": [
|
|
{
|
|
"name": "<unnamed window>",
|
|
"functions": [
|
|
"std"
|
|
]
|
|
}
|
|
],
|
|
"table": {
|
|
"table_name": "t",
|
|
"access_type": "ALL",
|
|
"rows_examined_per_scan": 4,
|
|
"rows_produced_per_join": 4,
|
|
"filtered": "100.00",
|
|
"cost_info": {
|
|
"read_cost": "0.25",
|
|
"eval_cost": "0.40",
|
|
"prefix_cost": "0.65",
|
|
"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`,std((`test`.`t`.`i` + `test`.`t`.`j`)) OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS `STD` from `test`.`t` order by NULL desc
|
|
With LIMIT
|
|
SELECT i, j, STD(i+j) OVER (ROWS UNBOUNDED PRECEDING) STD FROM t ORDER BY std DESC LIMIT 3;
|
|
i j STD
|
|
1 4 1.5
|
|
1 4 1.299038105676658
|
|
1 2 1.247219128924647
|
|
STD/VARIANCE with View
|
|
CREATE VIEW v AS
|
|
SELECT i, j, STD(i+j) OVER (ORDER BY j DESC ROWS UNBOUNDED PRECEDING) STD,
|
|
VARIANCE(i+j) OVER (ORDER BY j DESC ROWS UNBOUNDED PRECEDING) variance 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`,std((`t`.`i` + `t`.`j`)) OVER (ORDER BY `t`.`j` desc ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS `STD`,variance((`t`.`i` + `t`.`j`)) OVER (ORDER BY `t`.`j` desc ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS `variance` from `t` utf8mb4 utf8mb4_0900_ai_ci
|
|
SELECT * FROM v;
|
|
i j STD variance
|
|
1 4 0 0
|
|
1 4 0 0
|
|
1 2 0.9428090415820632 0.8888888888888887
|
|
1 1 1.2990381056766578 1.6874999999999998
|
|
DROP VIEW v;
|
|
TRUNCATE TABLE t;
|
|
INSERT INTO t VALUES (999961560, DEFAULT);
|
|
INSERT INTO t VALUES (44721, DEFAULT);
|
|
SELECT STD(i) OVER () FROM t;
|
|
STD(i) OVER ()
|
|
499958419.5
|
|
499958419.5
|
|
SELECT VARIANCE(i) OVER () FROM t;
|
|
VARIANCE(i) OVER ()
|
|
2.49958421228938e17
|
|
2.49958421228938e17
|
|
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, STD(k) OVER (ROWS UNBOUNDED PRECEDING) std,
|
|
VARIANCE(k) OVER (ROWS UNBOUNDED PRECEDING) variance FROM t;
|
|
k std variance
|
|
1 0 0
|
|
1 0 0
|
|
1 0 0
|
|
1 0 0
|
|
1 0 0
|
|
2 0.3726779962499649 0.13888888888888887
|
|
2 0.4517539514526256 0.2040816326530612
|
|
2 0.48412291827592707 0.23437499999999997
|
|
2 0.49690399499995325 0.24691358024691354
|
|
3 0.6633249580710799 0.43999999999999995
|
|
3 0.7496555682941201 0.5619834710743802
|
|
3 0.7993052538854533 0.6388888888888888
|
|
3 0.8284868934053083 0.6863905325443788
|
|
4 0.9609731462195509 0.9234693877551022
|
|
4 1.0456258094238748 1.0933333333333335
|
|
4 1.102199505534275 1.2148437500000002
|
|
4 1.1406305546861952 1.3010380622837374
|
|
SELECT k, STD(i), SUM(j), STD(k) OVER (ROWS UNBOUNDED PRECEDING) std_wf FROM t GROUP BY (k);
|
|
k STD(i) SUM(j) std_wf
|
|
1 0 15 0
|
|
2 0 11 0.5
|
|
3 0 11 0.816496580927726
|
|
4 0 11 1.118033988749895
|
|
SELECT k, STD(i), SUM(j), VAR_POP(k) OVER (ROWS UNBOUNDED PRECEDING) variance_wf FROM t GROUP BY (k);
|
|
k STD(i) SUM(j) variance_wf
|
|
1 0 15 0
|
|
2 0 11 0.25
|
|
3 0 11 0.6666666666666666
|
|
4 0 11 1.25
|
|
SELECT k, STD(i), SUM(j), STD(k) OVER (ROWS UNBOUNDED PRECEDING) std_wf FROM t GROUP BY (k) ORDER BY std_wf DESC;
|
|
k STD(i) SUM(j) std_wf
|
|
1 0 15 0
|
|
2 0 11 0.5
|
|
3 0 11 0.816496580927726
|
|
4 0 11 1.118033988749895
|
|
SELECT k, STD(i), SUM(j), STDDEV(k) OVER (ROWS UNBOUNDED PRECEDING) std_wf FROM t GROUP BY (k) ORDER BY std_wf DESC;
|
|
k STD(i) SUM(j) std_wf
|
|
4 0 11 1.118033988749895
|
|
3 0 11 0.816496580927726
|
|
2 0 11 0.5
|
|
1 0 15 0
|
|
SELECT k, STD(i), SUM(j), STDDEV_POP(k) OVER (ROWS UNBOUNDED PRECEDING) std_wf FROM t GROUP BY (k) ORDER BY std_wf DESC;
|
|
k STD(i) SUM(j) std_wf
|
|
4 0 11 1.118033988749895
|
|
3 0 11 0.816496580927726
|
|
2 0 11 0.5
|
|
1 0 15 0
|
|
SELECT k, STD(i), SUM(j), STDDEV_SAMP(k) OVER (ROWS UNBOUNDED PRECEDING) std_wf FROM t GROUP BY (k) ORDER BY std_wf DESC;
|
|
k STD(i) SUM(j) std_wf
|
|
4 0 11 1.2909944487358056
|
|
3 0 11 1
|
|
2 0 11 0.7071067811865476
|
|
1 0 15 NULL
|
|
SELECT k, STD(i), SUM(j), VARIANCE(k) OVER (ROWS UNBOUNDED PRECEDING) variance_wf FROM t GROUP BY (k) ORDER BY variance_wf DESC;
|
|
k STD(i) SUM(j) variance_wf
|
|
4 0 11 1.25
|
|
3 0 11 0.6666666666666666
|
|
2 0 11 0.25
|
|
1 0 15 0
|
|
SELECT k, STD(i), SUM(j), VAR_POP(k) OVER (ROWS UNBOUNDED PRECEDING) variance_wf FROM t GROUP BY (k) ORDER BY variance_wf DESC;
|
|
k STD(i) SUM(j) variance_wf
|
|
4 0 11 1.25
|
|
3 0 11 0.6666666666666666
|
|
2 0 11 0.25
|
|
1 0 15 0
|
|
SELECT k, STD(i), SUM(j), VAR_SAMP(k) OVER (ROWS UNBOUNDED PRECEDING) variance_wf FROM t GROUP BY (k) ORDER BY variance_wf DESC;
|
|
k STD(i) SUM(j) variance_wf
|
|
4 0 11 1.6666666666666667
|
|
3 0 11 1
|
|
2 0 11 0.5
|
|
1 0 15 NULL
|
|
SELECT k, GROUP_CONCAT(j ORDER BY j), STD(k) OVER (ROWS UNBOUNDED PRECEDING) std,
|
|
VAR_SAMP(k) OVER (ROWS UNBOUNDED PRECEDING) variance FROM t GROUP BY (k);
|
|
k GROUP_CONCAT(j ORDER BY j) std variance
|
|
1 1,2,4,4,4 0 NULL
|
|
2 1,2,4,4 0.5 0.5
|
|
3 1,2,4,4 0.816496580927726 1
|
|
4 1,2,4,4 1.118033988749895 1.6666666666666667
|
|
SELECT k, AVG(DISTINCT j), STD(k) OVER (ROWS UNBOUNDED PRECEDING) std,
|
|
VARIANCE(k) OVER (ROWS UNBOUNDED PRECEDING) variance FROM t GROUP BY (k);
|
|
k AVG(DISTINCT j) std variance
|
|
1 2.3333 0 0
|
|
2 2.3333 0.5 0.25
|
|
3 2.3333 0.816496580927726 0.6666666666666666
|
|
4 2.3333 1.118033988749895 1.25
|
|
SELECT k, GROUP_CONCAT(j ORDER BY j), STD(k+1) OVER (ROWS UNBOUNDED PRECEDING) std FROM t GROUP BY (k);
|
|
k GROUP_CONCAT(j ORDER BY j) std
|
|
1 1,2,4,4,4 0
|
|
2 1,2,4,4 0.5
|
|
3 1,2,4,4 0.816496580927726
|
|
4 1,2,4,4 1.118033988749895
|
|
SELECT k, GROUP_CONCAT(j ORDER BY j), VARIANCE(k+1) OVER (ROWS UNBOUNDED PRECEDING) variance FROM t GROUP BY (k);
|
|
k GROUP_CONCAT(j ORDER BY j) variance
|
|
1 1,2,4,4,4 0
|
|
2 1,2,4,4 0.25
|
|
3 1,2,4,4 0.6666666666666666
|
|
4 1,2,4,4 1.25
|
|
SELECT k, GROUP_CONCAT(j ORDER BY j), STD(k+1) OVER (ORDER BY k DESC ROWS UNBOUNDED PRECEDING) std FROM t GROUP BY (k);
|
|
k GROUP_CONCAT(j ORDER BY j) std
|
|
4 1,2,4,4 0
|
|
3 1,2,4,4 0.5
|
|
2 1,2,4,4 0.816496580927726
|
|
1 1,2,4,4,4 1.118033988749895
|
|
SELECT k, GROUP_CONCAT(j ORDER BY j), VAR_POP(k+1) OVER (ORDER BY k DESC ROWS UNBOUNDED PRECEDING) variance FROM t GROUP BY (k);
|
|
k GROUP_CONCAT(j ORDER BY j) variance
|
|
4 1,2,4,4 0
|
|
3 1,2,4,4 0.25
|
|
2 1,2,4,4 0.6666666666666666
|
|
1 1,2,4,4,4 1.25
|
|
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), std(AVG(id)) OVER w std, VARIANCE(AVG(id)) OVER w variance 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) std variance
|
|
M 3.0000 0 0
|
|
F 3.0000 0 0
|
|
NULL 10.5000 3.5355339059327378 12.5
|
|
Ditto, but HAVING using subquery
|
|
SELECT sex, AVG(id), STD(AVG(id)) OVER w std, VARIANCE(AVG(id)) OVER w variance 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) std variance
|
|
M 3.0000 0 0
|
|
F 3.0000 0 0
|
|
NULL 10.5000 3.5355339059327378 12.5
|
|
This is a grouped aggregate in conjunction with a buffered wf
|
|
which generates an extra tmp file step
|
|
SELECT sex, AVG(id), STDDEV(AVG(id)) OVER w std, VAR_POP(AVG(id)) OVER w variance, 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) std variance NTILE(2) OVER w
|
|
M 3.0000 3.5355339059327378 12.5 2
|
|
F 3.0000 3.75 14.0625 1
|
|
NULL 10.5000 0 0 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), STD(AVG(id)) OVER w std, VARIANCE(AVG(id)) OVER w variance, 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) std variance NTILE(2) OVER w
|
|
M 3.0000 3.5355339059327378 12.5 2
|
|
F 3.0000 3.75 14.0625 1
|
|
NULL 10.5000 0 0 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 , STDDEV_POP(ASCII(sex)) OVER w std,
|
|
VARIANCE(ASCII(sex)) OVER w variance FROM t1
|
|
HAVING sex=(SELECT c FROM ss LIMIT 1)
|
|
WINDOW w AS (ORDER BY sex ROWS UNBOUNDED PRECEDING);
|
|
sex NTILE(2) OVER w std variance
|
|
M 1 0 0
|
|
M 2 0 0
|
|
Bug fix for prepared statements
|
|
PREPARE p FROM "SELECT sex, AVG(id), STD(AVG(id)) OVER w std,
|
|
VARIANCE(AVG(id)) OVER w variance, 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) std variance NTILE(2) OVER w
|
|
M 3.0000 3.5355339059327378 12.5 2
|
|
F 3.0000 3.75 14.0625 1
|
|
NULL 10.5000 0 0 1
|
|
EXECUTE p;
|
|
sex AVG(id) std variance NTILE(2) OVER w
|
|
M 3.0000 3.5355339059327378 12.5 2
|
|
F 3.0000 3.75 14.0625 1
|
|
NULL 10.5000 0 0 1
|
|
DROP PREPARE p;
|
|
DROP TABLE t1,ss;
|
|
Tests with ROLLUP
|
|
SELECT k, STD(i), SUM(j), STDDEV_SAMP(k) OVER (ROWS UNBOUNDED PRECEDING) std_wf FROM t
|
|
GROUP BY (k) WITH ROLLUP;
|
|
k STD(i) SUM(j) std_wf
|
|
1 0 15 NULL
|
|
2 0 11 0.7071067811865476
|
|
3 0 11 1
|
|
4 0 11 1.2909944487358056
|
|
NULL 0 48 1.2909944487358056
|
|
SELECT k, STD(i), SUM(j), VAR_SAMP(k) OVER (ROWS UNBOUNDED PRECEDING) variance_wf FROM t
|
|
GROUP BY (k) WITH ROLLUP;
|
|
k STD(i) SUM(j) variance_wf
|
|
1 0 15 NULL
|
|
2 0 11 0.5
|
|
3 0 11 1
|
|
4 0 11 1.6666666666666667
|
|
NULL 0 48 1.6666666666666667
|
|
SELECT k, STD(i), SUM(j), STD(k) OVER (ROWS UNBOUNDED PRECEDING) std_wf FROM t
|
|
GROUP BY (k) WITH ROLLUP ORDER BY k DESC;
|
|
k STD(i) SUM(j) std_wf
|
|
4 0 11 0
|
|
3 0 11 0.5
|
|
2 0 11 0.816496580927726
|
|
1 0 15 1.118033988749895
|
|
NULL 0 48 1.118033988749895
|
|
SELECT k, STD(i), SUM(j), STDDEV(k) OVER (ROWS UNBOUNDED PRECEDING) std_wf FROM t
|
|
GROUP BY k,j WITH ROLLUP;
|
|
k STD(i) SUM(j) std_wf
|
|
1 0 1 0
|
|
1 0 2 0
|
|
1 0 12 0
|
|
1 0 15 0
|
|
2 0 1 0.4
|
|
2 0 2 0.4714045207910317
|
|
2 0 8 0.4948716593053935
|
|
2 0 11 0.5
|
|
3 0 1 0.6666666666666666
|
|
3 0 2 0.7483314773547882
|
|
3 0 8 0.7925270806437588
|
|
3 0 11 0.816496580927726
|
|
4 0 1 0.9483713850721502
|
|
4 0 2 1.0301575072754257
|
|
4 0 8 1.0832051206181281
|
|
4 0 11 1.118033988749895
|
|
NULL 0 48 1.118033988749895
|
|
DROP TABLE t;
|
|
----------------------------------------------------------------------
|
|
- STD/VARIANCE 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, STD(t3_id) OVER w std,
|
|
VARIANCE(t3_id) OVER w variance,
|
|
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 std variance c_dist lead2 nth k
|
|
0 0 0 0.2 2 NULL 0
|
|
0 0 0 0.2 2 NULL 0
|
|
2 1 1 0.4 4 2 0
|
|
2 1 1 0.4 4 2 0
|
|
4 1.632993161855452 2.6666666666666665 0.6 6 2 0
|
|
4 1.632993161855452 2.6666666666666665 0.6 6 2 0
|
|
6 2.23606797749979 5 0.8 8 2 0
|
|
6 2.23606797749979 5 0.8 8 2 0
|
|
8 2.8284271247461903 8 1 NULL 2 0
|
|
8 2.8284271247461903 8 1 NULL 2 0
|
|
1 0 0 0.2 3 NULL 1
|
|
1 0 0 0.2 3 NULL 1
|
|
3 1 1 0.4 5 3 1
|
|
3 1 1 0.4 5 3 1
|
|
5 1.632993161855452 2.6666666666666665 0.6 7 3 1
|
|
5 1.632993161855452 2.6666666666666665 0.6 7 3 1
|
|
7 2.23606797749979 5 0.8 9 3 1
|
|
7 2.23606797749979 5 0.8 9 3 1
|
|
9 2.8284271247461903 8 1 NULL 3 1
|
|
9 2.8284271247461903 8 1 NULL 3 1
|
|
SELECT t3_id, STDDEV_SAMP(t3_id) OVER w std,
|
|
VARIANCE(t3_id) OVER w variance,
|
|
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 std variance c_dist lead2 nth k
|
|
0 0 0 0.2 2 NULL 0
|
|
0 0 0 0.2 2 NULL 0
|
|
2 1.1547005383792515 1 0.4 4 2 0
|
|
2 1.1547005383792515 1 0.4 4 2 0
|
|
4 1.7888543819998317 2.6666666666666665 0.6 6 2 0
|
|
4 1.7888543819998317 2.6666666666666665 0.6 6 2 0
|
|
6 2.390457218668787 5 0.8 8 2 0
|
|
6 2.390457218668787 5 0.8 8 2 0
|
|
8 2.9814239699997196 8 1 NULL 2 0
|
|
8 2.9814239699997196 8 1 NULL 2 0
|
|
1 0 0 0.2 3 NULL 1
|
|
1 0 0 0.2 3 NULL 1
|
|
3 1.1547005383792515 1 0.4 5 3 1
|
|
3 1.1547005383792515 1 0.4 5 3 1
|
|
5 1.7888543819998317 2.6666666666666665 0.6 7 3 1
|
|
5 1.7888543819998317 2.6666666666666665 0.6 7 3 1
|
|
7 2.390457218668787 5 0.8 9 3 1
|
|
7 2.390457218668787 5 0.8 9 3 1
|
|
9 2.9814239699997196 8 1 NULL 3 1
|
|
9 2.9814239699997196 8 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.*, STDDEV_POP(t.rank) OVER (ROWS UNBOUNDED PRECEDING) std,
|
|
VARIANCE(t.rank) OVER (ROWS UNBOUNDED PRECEDING) variance 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 std variance
|
|
M 1 2002-06-09 1 1 0 0
|
|
M 1 2002-06-09 2 1 0 0
|
|
F 2 2002-06-09 3 3 0.9428090415820634 0.888888888888889
|
|
F 3 2002-06-09 4 4 1.299038105676658 1.6875000000000002
|
|
F 4 2002-06-09 5 5 1.6 2.56
|
|
F 4 2002-06-09 6 5 1.6749792701868151 2.805555555555556
|
|
M 5 2002-06-09 7 7 2.050385727772475 4.204081632653062
|
|
----------------------------------------------------------------------
|
|
- Multiple windows
|
|
----------------------------------------------------------------------
|
|
SELECT t1.*, RANK() OVER (ORDER BY sex) `rank`, STD(id) OVER (ORDER BY sex,id ROWS UNBOUNDED PRECEDING) std FROM t1;
|
|
id sex rank std
|
|
2 F 1 0
|
|
3 F 1 0.5
|
|
4 F 1 0.816496580927726
|
|
1 M 4 1.118033988749895
|
|
5 M 4 1.4142135623730951
|
|
SELECT t1.*, PERCENT_RANK() OVER (ORDER BY sex) p_rank, VARIANCE(id) OVER (ORDER BY sex,id ROWS UNBOUNDED PRECEDING) variance FROM t1;
|
|
id sex p_rank variance
|
|
2 F 0 0
|
|
3 F 0 0.25
|
|
4 F 0 0.6666666666666666
|
|
1 M 0.75 1.25
|
|
5 M 0.75 2
|
|
SELECT t1.*, CUME_DIST() OVER (ORDER BY sex) c_dist, STDDEV_POP(id) OVER (ORDER BY sex,id ROWS UNBOUNDED PRECEDING) std FROM t1;
|
|
id sex c_dist std
|
|
2 F 0.6 0
|
|
3 F 0.6 0.5
|
|
4 F 0.6 0.816496580927726
|
|
1 M 1 1.118033988749895
|
|
5 M 1 1.4142135623730951
|
|
SELECT * from (SELECT t1.*, STDDEV(id) OVER (ROWS UNBOUNDED PRECEDING) std, RANK() OVER (ORDER BY sex) `rank` FROM t1) alias ORDER BY id;
|
|
id sex std rank
|
|
1 M 0 4
|
|
2 F 0.5 1
|
|
3 F 0.816496580927726 1
|
|
4 F 1.118033988749895 1
|
|
5 M 1.4142135623730951 4
|
|
SELECT * from (SELECT t1.*, VAR_SAMP(id) OVER (ROWS UNBOUNDED PRECEDING) variance, PERCENT_RANK() OVER (ORDER BY sex) p_rank FROM t1) alias ORDER BY id;
|
|
id sex variance p_rank
|
|
1 M NULL 0.75
|
|
2 F 0.5 0
|
|
3 F 1 0
|
|
4 F 1.6666666666666667 0
|
|
5 M 2.5 0.75
|
|
SELECT * from (SELECT t1.*, VARIANCE(id) OVER (ROWS UNBOUNDED PRECEDING) variance, CUME_DIST() OVER (ORDER BY sex) c_dist FROM t1) alias ORDER BY id;
|
|
id sex variance c_dist
|
|
1 M 0 1
|
|
2 F 0.25 0.6
|
|
3 F 0.6666666666666666 0.6
|
|
4 F 1.25 0.6
|
|
5 M 2 1
|
|
SELECT t1.*, STD(id) OVER (ORDER BY id ROWS UNBOUNDED PRECEDING) std,
|
|
variance(id) OVER (ORDER BY id ROWS UNBOUNDED PRECEDING) VARIANCE,
|
|
RANK() OVER (ORDER BY sex,id) `rank`,
|
|
ROW_NUMBER() OVER (ORDER BY sex,id) row_num
|
|
FROM t1;
|
|
id sex std VARIANCE rank row_num
|
|
2 F 0.5 0.25 1 1
|
|
3 F 0.816496580927726 0.6666666666666666 2 2
|
|
4 F 1.118033988749895 1.25 3 3
|
|
1 M 0 0 4 4
|
|
5 M 1.4142135623730951 2 5 5
|
|
a little more windows + subquery
|
|
SELECT t.*, STDDEV(id + r00 + r01) OVER (ORDER BY id ROWS UNBOUNDED PRECEDING) AS std,
|
|
variance(id + r00 + r01) OVER (ORDER BY id ROWS UNBOUNDED PRECEDING) AS VARIANCE 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 std VARIANCE
|
|
1 M 4 5 5 1 4 4 4 4 4 4 4 4 4 4 4 0 0
|
|
2 F 1 3 3 1 1 1 1 1 1 1 1 1 1 1 1 2 4
|
|
3 F 2 2 2 1 2 2 2 2 2 2 2 2 2 2 2 1.699673171197595 2.8888888888888893
|
|
4 F 3 1 1 1 3 3 3 3 3 3 3 3 3 3 3 1.4790199457749043 2.1875000000000004
|
|
5 M 5 4 4 1 5 5 5 5 5 5 5 5 5 5 5 2.8284271247461903 8
|
|
DROP TABLE t;
|
|
----------------------------------------------------------------------
|
|
- STD/VARIANCE with frames
|
|
----------------------------------------------------------------------
|
|
SELECT AVG(id) OVER w, STD(id) OVER w std, VARIANCE(id) OVER w variance FROM t1
|
|
WINDOW w AS (PARTITION BY sex);
|
|
AVG(id) OVER w std variance
|
|
3.0000 0.816496580927726 0.6666666666666666
|
|
3.0000 0.816496580927726 0.6666666666666666
|
|
3.0000 0.816496580927726 0.6666666666666666
|
|
3.0000 2 4
|
|
3.0000 2 4
|
|
SELECT * FROM (
|
|
SELECT id, SUM(id) OVER w, STD(id) OVER w std, VARIANCE(id) OVER w variance, sex FROM t1
|
|
WINDOW w AS (PARTITION BY sex)
|
|
) alias ORDER BY id;
|
|
id SUM(id) OVER w std variance sex
|
|
1 6 2 4 M
|
|
2 9 0.816496580927726 0.6666666666666666 F
|
|
3 9 0.816496580927726 0.6666666666666666 F
|
|
4 9 0.816496580927726 0.6666666666666666 F
|
|
5 6 2 4 M
|
|
SELECT STD(id) OVER w std FROM t1 WINDOW w AS (PARTITION BY sex);
|
|
std
|
|
0.816496580927726
|
|
0.816496580927726
|
|
0.816496580927726
|
|
2
|
|
2
|
|
SELECT VARIANCE(id) OVER w variance FROM t1 WINDOW w AS (PARTITION BY sex);
|
|
variance
|
|
0.6666666666666666
|
|
0.6666666666666666
|
|
0.6666666666666666
|
|
4
|
|
4
|
|
SELECT id, STD(id) OVER w std, VARIANCE(id) OVER w variance, sex FROM t1
|
|
WINDOW w AS (PARTITION BY sex ORDER BY id
|
|
ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING);
|
|
id std variance sex
|
|
2 NULL NULL F
|
|
3 0 0 F
|
|
4 0.5 0.25 F
|
|
1 NULL NULL M
|
|
5 0 0 M
|
|
try the same as a view
|
|
CREATE VIEW v AS
|
|
SELECT id, SUM(id) OVER w, STD(id) OVER w std, VARIANCE(id) OVER w variance, 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`,std(`t1`.`id`) OVER `w` AS `std`,variance(`t1`.`id`) OVER `w` AS `variance`,`t1`.`sex` AS `sex` from `t1` window `w` AS (PARTITION BY `t1`.`sex` ORDER BY `t1`.`id` ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING) utf8mb4 utf8mb4_0900_ai_ci
|
|
SELECT * FROM v;
|
|
id SUM(id) OVER w std variance sex
|
|
2 NULL NULL NULL F
|
|
3 2 0 0 F
|
|
4 5 0.5 0.25 F
|
|
1 NULL NULL NULL M
|
|
5 1 0 0 M
|
|
DROP VIEW v;
|
|
SELECT SUM(id) OVER w, STDDEV(id) OVER w std,
|
|
VARIANCE(id) OVER w variance FROM t1
|
|
WINDOW w AS (PARTITION BY sex ORDER BY id
|
|
ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING);
|
|
SUM(id) OVER w std variance
|
|
NULL NULL NULL
|
|
2 0 0
|
|
5 0.5 0.25
|
|
NULL NULL NULL
|
|
1 0 0
|
|
SELECT id, SUM(id) OVER w, STD(id) OVER w std,
|
|
VARIANCE(id) OVER w variance, sex FROM t1
|
|
WINDOW w AS (PARTITION BY sex ORDER BY id
|
|
ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING);
|
|
id SUM(id) OVER w std variance sex
|
|
2 7 0.5 0.25 F
|
|
3 4 0 0 F
|
|
4 NULL NULL NULL F
|
|
1 5 0 0 M
|
|
5 NULL NULL NULL M
|
|
SELECT SUM(id) OVER w, COUNT(*) OVER w, STD(id) OVER w std,
|
|
VARIANCE(id) OVER w variance 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 std variance
|
|
7 2 0.5 0.25
|
|
4 1 0 0
|
|
NULL 0 NULL NULL
|
|
5 1 0 0
|
|
NULL 0 NULL NULL
|
|
SELECT id, AVG(id) OVER (ROWS UNBOUNDED PRECEDING) avg,
|
|
STD(id) OVER (ROWS UNBOUNDED PRECEDING) std FROM t1;
|
|
id avg std
|
|
1 1.0000 0
|
|
2 1.5000 0.5
|
|
3 2.0000 0.816496580927726
|
|
4 2.5000 1.118033988749895
|
|
5 3.0000 1.4142135623730951
|
|
SELECT id, AVG(id) OVER (ROWS UNBOUNDED PRECEDING),
|
|
VARIANCE(id) OVER (ROWS UNBOUNDED PRECEDING) variance FROM t1;
|
|
id AVG(id) OVER (ROWS UNBOUNDED PRECEDING) variance
|
|
1 1.0000 0
|
|
2 1.5000 0.25
|
|
3 2.0000 0.6666666666666666
|
|
4 2.5000 1.25
|
|
5 3.0000 2
|
|
SELECT id, AVG(id) OVER w avg, COUNT(id) OVER w count, STD(id) OVER w std,
|
|
VARIANCE(id) OVER w variance FROM t1
|
|
WINDOW w AS (ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING);
|
|
id avg count std variance
|
|
1 1.5000 2 0.5 0.25
|
|
2 2.0000 3 0.816496580927726 0.6666666666666666
|
|
3 3.0000 3 0.816496580927726 0.6666666666666666
|
|
4 4.0000 3 0.816496580927726 0.6666666666666666
|
|
5 4.5000 2 0.5 0.25
|
|
STD/VARIANCE 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, STD(d) OVER (ORDER BY d) std, VARIANCE(d) OVER (ORDER BY d) variance FROM td;
|
|
d std variance
|
|
NULL NULL NULL
|
|
1 0 0
|
|
1.2 0.09999999999999992 0.009999999999999985
|
|
2 0.45552167895721485 0.20749999999999993
|
|
2 0.45552167895721485 0.20749999999999993
|
|
3 0.708801805866774 0.5024
|
|
SELECT d, STD(d) OVER () std, VARIANCE(d) OVER () variance FROM td;
|
|
d std variance
|
|
2 0.708801805866774 0.5024
|
|
2 0.708801805866774 0.5024
|
|
3 0.708801805866774 0.5024
|
|
1 0.708801805866774 0.5024
|
|
1.2 0.708801805866774 0.5024
|
|
NULL 0.708801805866774 0.5024
|
|
SELECT d, STD(d) OVER (ORDER BY d ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING) std FROM td;
|
|
d std
|
|
NULL 0
|
|
1 0.09999999999999992
|
|
1.2 0.4320493798938573
|
|
2 0.45552167895721485
|
|
2 0.6383572667401852
|
|
3 0.4714045207910316
|
|
SELECT d, VARIANCE(d) OVER (ORDER BY d ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING) variance FROM td;
|
|
d variance
|
|
NULL 0
|
|
1 0.009999999999999985
|
|
1.2 0.18666666666666662
|
|
2 0.20749999999999993
|
|
2 0.40750000000000003
|
|
3 0.22222222222222218
|
|
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, STD(d) OVER w std, VARIANCE(d) OVER w variance FROM td
|
|
WINDOW w AS (ORDER BY d RANGE BETWEEN 2 PRECEDING AND CURRENT ROW);
|
|
d std variance
|
|
1 0 0
|
|
2 0.5 0.25
|
|
3 0.816496580927726 0.6666666666666666
|
|
4 0.816496580927726 0.6666666666666666
|
|
5 0.816496580927726 0.6666666666666666
|
|
6 0.816496580927726 0.6666666666666666
|
|
7 0.816496580927726 0.6666666666666666
|
|
8 0.816496580927726 0.6666666666666666
|
|
9 0.816496580927726 0.6666666666666666
|
|
10 0.816496580927726 0.6666666666666666
|
|
SELECT d, SUM(d) OVER w sum, STD(d) OVER w std, VARIANCE(d) OVER w variance FROM td
|
|
WINDOW w AS (ORDER BY d RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING);
|
|
d sum std variance
|
|
1 6 0.816496580927726 0.6666666666666666
|
|
2 10 1.118033988749895 1.25
|
|
3 15 1.4142135623730951 2
|
|
4 20 1.4142135623730951 2
|
|
5 25 1.4142135623730951 2
|
|
6 30 1.4142135623730951 2
|
|
7 35 1.4142135623730951 2
|
|
8 40 1.4142135623730951 2
|
|
9 34 1.118033988749895 1.25
|
|
10 27 0.816496580927726 0.6666666666666666
|
|
SELECT d, SUM(d) OVER w sum, STD(d) OVER w std, VARIANCE(d) OVER w variance FROM td
|
|
WINDOW w AS (ORDER BY d RANGE BETWEEN CURRENT ROW AND 2 FOLLOWING);
|
|
d sum std variance
|
|
1 6 0.816496580927726 0.6666666666666666
|
|
2 9 0.816496580927726 0.6666666666666666
|
|
3 12 0.816496580927726 0.6666666666666666
|
|
4 15 0.816496580927726 0.6666666666666666
|
|
5 18 0.816496580927726 0.6666666666666666
|
|
6 21 0.816496580927726 0.6666666666666666
|
|
7 24 0.816496580927726 0.6666666666666666
|
|
8 27 0.816496580927726 0.6666666666666666
|
|
9 19 0.5 0.25
|
|
10 10 0 0
|
|
INSERT INTO td SELECT * FROM td;
|
|
SELECT d, SUM(d) OVER w sum, VARIANCE(d) OVER w variance, STD(d) OVER w std FROM td
|
|
WINDOW w AS (ORDER BY d RANGE BETWEEN 2 PRECEDING AND CURRENT ROW);
|
|
d sum variance std
|
|
1 2 0 0
|
|
1 2 0 0
|
|
2 6 0.25 0.5
|
|
2 6 0.25 0.5
|
|
3 12 0.6666666666666666 0.816496580927726
|
|
3 12 0.6666666666666666 0.816496580927726
|
|
4 18 0.6666666666666666 0.816496580927726
|
|
4 18 0.6666666666666666 0.816496580927726
|
|
5 24 0.6666666666666666 0.816496580927726
|
|
5 24 0.6666666666666666 0.816496580927726
|
|
6 30 0.6666666666666669 0.8164965809277261
|
|
6 30 0.6666666666666669 0.8164965809277261
|
|
7 36 0.6666666666666669 0.8164965809277261
|
|
7 36 0.6666666666666669 0.8164965809277261
|
|
8 42 0.6666666666666669 0.8164965809277261
|
|
8 42 0.6666666666666669 0.8164965809277261
|
|
9 48 0.6666666666666669 0.8164965809277261
|
|
9 48 0.6666666666666669 0.8164965809277261
|
|
10 54 0.6666666666666662 0.8164965809277257
|
|
10 54 0.6666666666666662 0.8164965809277257
|
|
SELECT d, SUM(d) OVER w sum, VARIANCE(d) OVER w variance, STD(d) OVER w std FROM td
|
|
WINDOW w AS (ORDER BY d RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING);
|
|
d sum variance std
|
|
1 12 0.6666666666666666 0.816496580927726
|
|
1 12 0.6666666666666666 0.816496580927726
|
|
2 20 1.25 1.118033988749895
|
|
2 20 1.25 1.118033988749895
|
|
3 30 2 1.4142135623730951
|
|
3 30 2 1.4142135623730951
|
|
4 40 2 1.4142135623730951
|
|
4 40 2 1.4142135623730951
|
|
5 50 2 1.4142135623730951
|
|
5 50 2 1.4142135623730951
|
|
6 60 2 1.4142135623730951
|
|
6 60 2 1.4142135623730951
|
|
7 70 2 1.4142135623730951
|
|
7 70 2 1.4142135623730951
|
|
8 80 2 1.4142135623730951
|
|
8 80 2 1.4142135623730951
|
|
9 68 1.2499999999999998 1.1180339887498947
|
|
9 68 1.2499999999999998 1.1180339887498947
|
|
10 54 0.6666666666666662 0.8164965809277257
|
|
10 54 0.6666666666666662 0.8164965809277257
|
|
SELECT d, SUM(d) OVER w sum, VARIANCE(d) OVER w variance, STD(d) OVER w std FROM td
|
|
WINDOW w AS (ORDER BY d RANGE BETWEEN CURRENT ROW AND 2 FOLLOWING);
|
|
d sum variance std
|
|
1 12 0.6666666666666666 0.816496580927726
|
|
1 12 0.6666666666666666 0.816496580927726
|
|
2 18 0.6666666666666666 0.816496580927726
|
|
2 18 0.6666666666666666 0.816496580927726
|
|
3 24 0.6666666666666666 0.816496580927726
|
|
3 24 0.6666666666666666 0.816496580927726
|
|
4 30 0.6666666666666669 0.8164965809277261
|
|
4 30 0.6666666666666669 0.8164965809277261
|
|
5 36 0.6666666666666669 0.8164965809277261
|
|
5 36 0.6666666666666669 0.8164965809277261
|
|
6 42 0.6666666666666669 0.8164965809277261
|
|
6 42 0.6666666666666669 0.8164965809277261
|
|
7 48 0.6666666666666669 0.8164965809277261
|
|
7 48 0.6666666666666669 0.8164965809277261
|
|
8 54 0.6666666666666662 0.8164965809277257
|
|
8 54 0.6666666666666662 0.8164965809277257
|
|
9 38 0.24999999999999978 0.4999999999999998
|
|
9 38 0.24999999999999978 0.4999999999999998
|
|
10 20 0 0
|
|
10 20 0 0
|
|
DROP TABLE td;
|
|
----------------------------------------------------------------------
|
|
- STD/VARIANCE with frames in combination with non-framing window functions
|
|
- ROW_NUMBER and RANK
|
|
----------------------------------------------------------------------
|
|
SELECT ROW_NUMBER() OVER w `row_number`, id, STD(id) OVER w std, VARIANCE(id) OVER w variance, sex FROM t1
|
|
WINDOW w AS (PARTITION BY sex ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING);
|
|
row_number id std variance sex
|
|
1 2 0.816496580927726 0.6666666666666666 F
|
|
2 3 0.816496580927726 0.6666666666666666 F
|
|
3 4 0.816496580927726 0.6666666666666666 F
|
|
1 1 2 4 M
|
|
2 5 2 4 M
|
|
SELECT ROW_NUMBER() OVER w `row_number`, STD(id) OVER w std,
|
|
VARIANCE(id) OVER w variance FROM t1
|
|
WINDOW w AS (PARTITION BY sex ORDER BY id
|
|
ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING);
|
|
row_number std variance
|
|
1 0.5 0.25
|
|
2 0 0
|
|
3 NULL NULL
|
|
1 0 0
|
|
2 NULL NULL
|
|
INSERT INTO t1 VALUES (10, NULL);
|
|
SELECT RANK() OVER w `rank`, id, STD(id) OVER w std, VARIANCE(id) OVER w variance, sex FROM t1
|
|
WINDOW w AS (PARTITION BY sex ORDER BY id);
|
|
rank id std variance sex
|
|
1 10 0 0 NULL
|
|
1 2 0 0 F
|
|
2 3 0.5 0.25 F
|
|
3 4 0.816496580927726 0.6666666666666666 F
|
|
1 1 0 0 M
|
|
2 5 2 4 M
|
|
SELECT RANK() OVER w `rank`, STD(id) OVER w std,
|
|
VARIANCE(id) OVER w variance FROM t1
|
|
WINDOW w AS (PARTITION BY sex ORDER BY id
|
|
ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING);
|
|
rank std variance
|
|
1 NULL NULL
|
|
1 0.5 0.25
|
|
2 0 0
|
|
3 NULL NULL
|
|
1 0 0
|
|
2 NULL NULL
|
|
SELECT id, sex, STD(id) OVER w std,
|
|
VARIANCE(id) OVER w variance,
|
|
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 std variance row_number rank
|
|
10 NULL NULL NULL 1 1
|
|
2 F NULL NULL 1 1
|
|
3 F 0 0 2 2
|
|
4 F 0.5 0.25 3 3
|
|
1 M NULL NULL 1 1
|
|
5 M 0 0 2 2
|
|
SELECT id, sex, STD(id) OVER w std,
|
|
VARIANCE(id) OVER w variance,
|
|
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 std variance row_number cume_dist
|
|
10 NULL NULL NULL 1 1
|
|
2 F NULL NULL 1 0.3333333333333333
|
|
3 F 0 0 2 0.6666666666666666
|
|
4 F 0.5 0.25 3 1
|
|
1 M NULL NULL 1 0.5
|
|
5 M 0 0 2 1
|
|
----------------------------------------------------------------------
|
|
- STD/VARIANCE 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, STD(id) OVER (ORDER BY id RANGE 2 PRECEDING) std FROM t1 ORDER BY id;
|
|
id std
|
|
1 0
|
|
2 0.5
|
|
3 0.816496580927726
|
|
4 0.816496580927726
|
|
5 0.816496580927726
|
|
10 0
|
|
SELECT id, STD(id) OVER (ORDER BY id RANGE 2 PRECEDING) std FROM t1 ORDER BY id;
|
|
id std
|
|
1 0
|
|
2 0.5
|
|
3 0.816496580927726
|
|
4 0.816496580927726
|
|
5 0.816496580927726
|
|
10 0
|
|
SELECT id, STD(id) OVER (ORDER BY id RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) std FROM t1 ORDER BY id;
|
|
id std
|
|
1 0.5
|
|
2 0.816496580927726
|
|
3 0.816496580927726
|
|
4 0.816496580927726
|
|
5 0.5
|
|
10 0
|
|
SELECT id, VARIANCE(id) OVER (ORDER BY id RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) variance FROM t1 ORDER BY id;
|
|
id variance
|
|
1 0.25
|
|
2 0.6666666666666666
|
|
3 0.6666666666666666
|
|
4 0.6666666666666666
|
|
5 0.25
|
|
10 0
|
|
SELECT id, STD(id) OVER (ORDER BY id RANGE UNBOUNDED PRECEDING) std FROM t1 ORDER BY id;
|
|
id std
|
|
1 0
|
|
2 0.5
|
|
3 0.816496580927726
|
|
4 1.118033988749895
|
|
5 1.4142135623730951
|
|
10 2.91070819942883
|
|
SELECT id, VARIANCE(id) OVER (ORDER BY id RANGE UNBOUNDED PRECEDING) variance FROM t1 ORDER BY id;
|
|
id variance
|
|
1 0
|
|
2 0.25
|
|
3 0.6666666666666666
|
|
4 1.25
|
|
5 2
|
|
10 8.472222222222221
|
|
SELECT p, id, STD(id) OVER (PARTITION BY p ORDER BY id RANGE 2 PRECEDING) std FROM t11 ORDER BY p,id;
|
|
p id std
|
|
1 1 0
|
|
1 2 0.5
|
|
1 3 0.816496580927726
|
|
1 4 0.816496580927726
|
|
1 5 0.816496580927726
|
|
1 10 0
|
|
2 1 0
|
|
2 2 0.5
|
|
2 3 0.816496580927726
|
|
2 4 0.816496580927726
|
|
2 5 0.816496580927726
|
|
2 10 0
|
|
SELECT p, id, VARIANCE(id) OVER (PARTITION BY p ORDER BY id RANGE 2 PRECEDING) variance FROM t11 ORDER BY p,id;
|
|
p id variance
|
|
1 1 0
|
|
1 2 0.25
|
|
1 3 0.6666666666666666
|
|
1 4 0.6666666666666666
|
|
1 5 0.6666666666666666
|
|
1 10 0
|
|
2 1 0
|
|
2 2 0.25
|
|
2 3 0.6666666666666666
|
|
2 4 0.6666666666666666
|
|
2 5 0.6666666666666666
|
|
2 10 0
|
|
SELECT p, id, STD(id) OVER (PARTITION BY p ORDER BY id RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) std FROM t11 ORDER BY p,id;
|
|
p id std
|
|
1 1 0.5
|
|
1 2 0.816496580927726
|
|
1 3 0.816496580927726
|
|
1 4 0.816496580927726
|
|
1 5 0.5
|
|
1 10 0
|
|
2 1 0.5
|
|
2 2 0.816496580927726
|
|
2 3 0.816496580927726
|
|
2 4 0.816496580927726
|
|
2 5 0.5
|
|
2 10 0
|
|
SELECT p, id, VARIANCE(id) OVER (PARTITION BY p ORDER BY id RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING) variance FROM t11 ORDER BY p,id;
|
|
p id variance
|
|
1 1 0.25
|
|
1 2 0.6666666666666666
|
|
1 3 0.6666666666666666
|
|
1 4 0.6666666666666666
|
|
1 5 0.25
|
|
1 10 0
|
|
2 1 0.25
|
|
2 2 0.6666666666666666
|
|
2 3 0.6666666666666666
|
|
2 4 0.6666666666666666
|
|
2 5 0.25
|
|
2 10 0
|
|
SELECT p, id, STD(id) OVER (PARTITION BY p ORDER BY id RANGE UNBOUNDED PRECEDING) std FROM t11 ORDER BY p,id;
|
|
p id std
|
|
1 1 0
|
|
1 2 0.5
|
|
1 3 0.816496580927726
|
|
1 4 1.118033988749895
|
|
1 5 1.4142135623730951
|
|
1 10 2.91070819942883
|
|
2 1 0
|
|
2 2 0.5
|
|
2 3 0.816496580927726
|
|
2 4 1.118033988749895
|
|
2 5 1.4142135623730951
|
|
2 10 2.91070819942883
|
|
SELECT p, id, VARIANCE(id) OVER (PARTITION BY p ORDER BY id RANGE UNBOUNDED PRECEDING) variance FROM t11 ORDER BY p,id;
|
|
p id variance
|
|
1 1 0
|
|
1 2 0.25
|
|
1 3 0.6666666666666666
|
|
1 4 1.25
|
|
1 5 2
|
|
1 10 8.472222222222221
|
|
2 1 0
|
|
2 2 0.25
|
|
2 3 0.6666666666666666
|
|
2 4 1.25
|
|
2 5 2
|
|
2 10 8.472222222222221
|
|
Implicit frame due to ORDER BY, with last in peer group as upper bound
|
|
SELECT user_id, STD(user_id) OVER w, VARIANCE(user_id) OVER w FROM t2 WINDOW w AS (ORDER BY user_id);
|
|
user_id STD(user_id) OVER w VARIANCE(user_id) OVER w
|
|
1 0 0
|
|
1 0 0
|
|
2 0.4714045207910317 0.22222222222222224
|
|
3 0.82915619758885 0.6875
|
|
4 1.2583057392117916 1.5833333333333333
|
|
4 1.2583057392117916 1.5833333333333333
|
|
5 1.4568627181693672 2.122448979591837
|
|
SELECT p, user_id, STD(user_id) OVER w, VARIANCE(user_id) OVER w FROM t22 WINDOW w AS (PARTITION BY p ORDER BY user_id) ORDER BY p;
|
|
p user_id STD(user_id) OVER w VARIANCE(user_id) OVER w
|
|
1 1 0 0
|
|
1 1 0 0
|
|
1 2 0.4714045207910317 0.22222222222222224
|
|
1 3 0.82915619758885 0.6875
|
|
1 4 1.2583057392117916 1.5833333333333333
|
|
1 4 1.2583057392117916 1.5833333333333333
|
|
1 5 1.4568627181693672 2.122448979591837
|
|
2 1 0 0
|
|
2 1 0 0
|
|
2 2 0.4714045207910317 0.22222222222222224
|
|
2 3 0.82915619758885 0.6875
|
|
2 4 1.2583057392117916 1.5833333333333333
|
|
2 4 1.2583057392117916 1.5833333333333333
|
|
2 5 1.4568627181693672 2.122448979591837
|
|
SELECT user_id, STD(user_id) OVER w, VARIANCE(user_id) OVER w1 FROM t2
|
|
WINDOW w AS (ORDER BY user_id), w1 AS (ORDER BY user_id);
|
|
user_id STD(user_id) OVER w VARIANCE(user_id) OVER w1
|
|
1 0 0
|
|
1 0 0
|
|
2 0.4714045207910317 0.22222222222222224
|
|
3 0.82915619758885 0.6875
|
|
4 1.2583057392117916 1.5833333333333333
|
|
4 1.2583057392117916 1.5833333333333333
|
|
5 1.4568627181693672 2.122448979591837
|
|
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, STD(id) OVER w FROM t1
|
|
WINDOW w AS (ORDER BY id DESC RANGE 2 PRECEDING);
|
|
NTILE(5) OVER w ROW_NUMBER() OVER w id STD(id) OVER w
|
|
1 1 10 0
|
|
1 2 5 0
|
|
2 3 4 0.5
|
|
3 4 3 0.816496580927726
|
|
4 5 2 0.816496580927726
|
|
5 6 1 0.816496580927726
|
|
SELECT p, NTILE(5) OVER w, ROW_NUMBER() OVER w, id, VARIANCE(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 VARIANCE(id) OVER w
|
|
1 1 1 10 0
|
|
1 1 2 5 0
|
|
1 2 3 4 0.25
|
|
1 3 4 3 0.6666666666666666
|
|
1 4 5 2 0.6666666666666666
|
|
1 5 6 1 0.6666666666666666
|
|
2 1 1 10 0
|
|
2 1 2 5 0
|
|
2 2 3 4 0.25
|
|
2 3 4 3 0.6666666666666666
|
|
2 4 5 2 0.6666666666666666
|
|
2 5 6 1 0.6666666666666666
|
|
SELECT NTILE(5) OVER w, ROW_NUMBER() OVER w, id, STD(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, STD(d) OVER w std, VARIANCE(d) OVER w variance, COUNT(*) OVER w
|
|
FROM t3 WINDOW w AS (ORDER BY d RANGE BETWEEN 2.1 PRECEDING AND 1.1 FOLLOWING);
|
|
d std variance COUNT(*) OVER w
|
|
1.1 0.3999999999999999 0.15999999999999992 2
|
|
1.9 0.3999999999999999 0.15999999999999992 2
|
|
4 1.0499999999999998 1.1024999999999998 2
|
|
8.3 0 0 1
|
|
16 0 0 1
|
|
20.1 0 0 1
|
|
22 1.2027745701779142 1.446666666666666 3
|
|
23 0.816496580927726 0.6666666666666666 3
|
|
24 0.816496580927726 0.6666666666666666 3
|
|
DROP TABLE t3;
|
|
----------------------------------------------------------------------
|
|
- STD/VARIANCE 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 STD(j) OVER () AS JSON FROM tj;
|
|
JSON
|
|
1.5969205058614584
|
|
1.5969205058614584
|
|
1.5969205058614584
|
|
1.5969205058614584
|
|
1.5969205058614584
|
|
1.5969205058614584
|
|
1.5969205058614584
|
|
1.5969205058614584
|
|
1.5969205058614584
|
|
Warnings:
|
|
Warning 3156 Invalid JSON value for CAST to DOUBLE from column j at row 1
|
|
SELECT j, JSON_TYPE(j), STD(j) OVER (ORDER BY j ROWS 3 PRECEDING) FROM tj;
|
|
j JSON_TYPE(j) STD(j) OVER (ORDER BY j ROWS 3 PRECEDING)
|
|
NULL NULL NULL
|
|
NULL NULL NULL
|
|
1 INTEGER 0
|
|
2 INTEGER 0.5
|
|
3 INTEGER 0.816496580927726
|
|
3.14 DOUBLE 0.8623659316090821
|
|
4 INTEGER 0.7097006411156749
|
|
5 INTEGER 0.799171445936352
|
|
[1, 2, 3] ARRAY 1.8718106207626881
|
|
Warnings:
|
|
Warning 1235 This version of MySQL doesn't yet support 'sorting of non-scalar JSON values'
|
|
Warning 3156 Invalid JSON value for CAST to DOUBLE from column j at row 9
|
|
SELECT j, JSON_TYPE(j), VARIANCE(j) OVER (ORDER BY j ROWS 3 PRECEDING) FROM tj;
|
|
j JSON_TYPE(j) VARIANCE(j) OVER (ORDER BY j ROWS 3 PRECEDING)
|
|
NULL NULL NULL
|
|
NULL NULL NULL
|
|
1 INTEGER 0
|
|
2 INTEGER 0.25
|
|
3 INTEGER 0.6666666666666666
|
|
3.14 DOUBLE 0.7436750000000001
|
|
4 INTEGER 0.503675
|
|
5 INTEGER 0.6386749999999997
|
|
[1, 2, 3] ARRAY 3.503675
|
|
Warnings:
|
|
Warning 1235 This version of MySQL doesn't yet support 'sorting of non-scalar JSON values'
|
|
Warning 3156 Invalid JSON value for CAST to DOUBLE from column j at row 9
|
|
SELECT j, JSON_TYPE(j), JSON_TYPE(STD(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');
|
|
INSERT INTO tj(j) VALUES ('[1,2,3]');
|
|
SELECT j,
|
|
JSON_TYPE(j),
|
|
STD(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) STD(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 0
|
|
2 INTEGER 0.5
|
|
3 INTEGER 0.816496580927726
|
|
3.14 DOUBLE 0.8623659316090821
|
|
3.14 DOUBLE 0.4768647607026546
|
|
4 INTEGER 0.3967366884975473
|
|
5 INTEGER 0.7664202502543888
|
|
[1, 2, 3] ARRAY 1.4743388348680233
|
|
[1, 2, 3] ARRAY 1.7853571071357126
|
|
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), VARIANCE(i) OVER (), COUNT(*) OVER () FROM tj ORDER BY NTILE(3) OVER (ORDER BY i);
|
|
i NTILE(3) OVER (ORDER BY i) VARIANCE(i) OVER () COUNT(*) OVER ()
|
|
7 1 0 11
|
|
7 2 0 11
|
|
7 3 0 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(STD(i),2) FROM tj2 GROUP BY i ;
|
|
COUNT(*) OVER () MOD(STD(i),2)
|
|
4 0
|
|
4 0
|
|
4 0
|
|
4 NULL
|
|
SELECT DISTINCT COUNT(*) OVER (), MOD(VARIANCE(i),2) FROM tj2 GROUP BY i ;
|
|
COUNT(*) OVER () MOD(VARIANCE(i),2)
|
|
4 0
|
|
4 NULL
|
|
SELECT DISTINCT COUNT(*) OVER (), MOD(STD(i),2) as a FROM tj2 GROUP BY i ORDER BY MOD(STD(i),2);
|
|
COUNT(*) OVER () a
|
|
4 NULL
|
|
4 0
|
|
SELECT DISTINCT COUNT(*) OVER (), MOD(STD(i),2) as a FROM tj2 GROUP BY i ORDER BY MOD(STD(i),2) DESC;
|
|
COUNT(*) OVER () a
|
|
4 0
|
|
4 NULL
|
|
Bug fix GROUP BY with window function referring column used in grouping expression
|
|
SELECT i, STD(i) OVER (), MOD(STD(i),2) FROM tj2 GROUP BY i ;
|
|
i STD(i) OVER () MOD(STD(i),2)
|
|
1 0.816496580927726 0
|
|
0 0.816496580927726 0
|
|
2 0.816496580927726 0
|
|
NULL 0.816496580927726 NULL
|
|
SELECT i, VARIANCE(VARIANCE(i)) OVER (), VARIANCE(i) OVER (ORDER BY i), MOD(VARIANCE(i),2), VARIANCE(i) FROM tj2 GROUP BY i ;
|
|
i VARIANCE(VARIANCE(i)) OVER () VARIANCE(i) OVER (ORDER BY i) MOD(VARIANCE(i),2) VARIANCE(i)
|
|
NULL 0 NULL NULL NULL
|
|
0 0 0 0 0
|
|
1 0 0.25 0 0
|
|
2 0 0.6666666666666666 0 0
|
|
SELECT i, STD(i) OVER (ORDER BY i) FROM tj UNION ALL SELECT i, STD(i) OVER (ORDER BY i) FROM tj;
|
|
i STD(i) OVER (ORDER BY i)
|
|
NULL NULL
|
|
NULL NULL
|
|
7 0
|
|
8 0.4714045207910316
|
|
8 0.4714045207910316
|
|
9 0.7071067811865475
|
|
10 1.1248582677159726
|
|
10 1.1248582677159726
|
|
10 1.1248582677159726
|
|
11 1.2686114456365272
|
|
12 1.4989708403591155
|
|
NULL NULL
|
|
NULL NULL
|
|
7 0
|
|
8 0.4714045207910316
|
|
8 0.4714045207910316
|
|
9 0.7071067811865475
|
|
10 1.1248582677159726
|
|
10 1.1248582677159726
|
|
10 1.1248582677159726
|
|
11 1.2686114456365272
|
|
12 1.4989708403591155
|
|
SELECT j,CAST(STD(j) OVER (PARTITION BY i) AS JSON), CAST(VARIANCE(j) OVER () AS JSON) FROM tj;
|
|
j CAST(STD(j) OVER (PARTITION BY i) AS JSON) CAST(VARIANCE(j) OVER () AS JSON)
|
|
NULL NULL 2.711535802469135
|
|
NULL NULL 2.711535802469135
|
|
1 0.0 2.711535802469135
|
|
[1, 2, 3] 0.0 2.711535802469135
|
|
[1, 2, 3] 0.0 2.711535802469135
|
|
2 0.0 2.711535802469135
|
|
3 0.06599663291074434 2.711535802469135
|
|
3.14 0.06599663291074434 2.711535802469135
|
|
3.14 0.06599663291074434 2.711535802469135
|
|
4 0.0 2.711535802469135
|
|
5 0.0 2.711535802469135
|
|
Warnings:
|
|
Warning 3156 Invalid JSON value for CAST to DOUBLE from column j at row 1
|
|
Warning 3156 Invalid JSON value for CAST to DOUBLE from column j at row 1
|
|
Warning 3156 Invalid JSON value for CAST to DOUBLE from column j at row 1
|
|
Warning 3156 Invalid JSON value for CAST to DOUBLE from column j at row 1
|
|
SELECT j,CAST(STD(j) OVER (PARTITION BY i ROWS UNBOUNDED PRECEDING) AS JSON), CAST(VARIANCE(j) OVER (PARTITION BY i ROWS UNBOUNDED PRECEDING) AS JSON) FROM tj;
|
|
j CAST(STD(j) OVER (PARTITION BY i ROWS UNBOUNDED PRECEDING) AS JSON) CAST(VARIANCE(j) OVER (PARTITION BY i ROWS UNBOUNDED PRECEDING) AS JSON)
|
|
NULL NULL NULL
|
|
NULL NULL NULL
|
|
1 0.0 0.0
|
|
[1, 2, 3] 0.0 0.0
|
|
[1, 2, 3] 0.0 0.0
|
|
2 0.0 0.0
|
|
3 0.0 0.0
|
|
3.14 0.06999999999999995 0.004899999999999993
|
|
3.14 0.06599663291074434 0.004355555555555542
|
|
4 0.0 0.0
|
|
5 0.0 0.0
|
|
Warnings:
|
|
Warning 3156 Invalid JSON value for CAST to DOUBLE from column j at row 1
|
|
Warning 3156 Invalid JSON value for CAST to DOUBLE from column j at row 1
|
|
Warning 3156 Invalid JSON value for CAST to DOUBLE from column j at row 4
|
|
Warning 3156 Invalid JSON value for CAST to DOUBLE from column j at row 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, STD(i) OVER (PARTITION BY j ORDER BY i) FROM t1;
|
|
i j STD(i) OVER (PARTITION BY j ORDER BY i)
|
|
NULL 3 NULL
|
|
NULL 3 NULL
|
|
4 3 0
|
|
2 5 0
|
|
3 7 0
|
|
1 10 0
|
|
5 20 0
|
|
NULL 30 NULL
|
|
NULL 30 NULL
|
|
3 30 0
|
|
DROP TABLE t1;
|
|
#
|
|
# Bug#26125954: PROBLEM WITH ITEM_SUM_STD::VAL_REAL
|
|
#
|
|
CREATE TABLE t1(c1 INT,c2 INT);
|
|
INSERT INTO t1 VALUES
|
|
(1,5),(2,-1900740608),(3,1938161664),(4,2),(5,6),(6,5),(7,5),
|
|
(8,-921239552),(9,-1347354624),(10,5),(11,-870580224),(12,-30913),
|
|
(13,1267597312),(14,93454336),(15,6),(16,-67174400),(17,10751180),
|
|
(18,659685376),(19,-790691840),(20,882114560),(21,1952907264);
|
|
SELECT STDDEV(c2) OVER ( ROWS 2 PRECEDING ) AS std_deviation FROM t1;
|
|
std_deviation
|
|
0
|
|
950370306.5
|
|
1567250109.3845165
|
|
1567250109.3924754
|
|
913658168.5478505
|
|
1.699673171197595
|
|
0.4714045207910316
|
|
434276491.9013273
|
|
562298939.1037524
|
|
562298939.1037524
|
|
557831940.0349873
|
|
410388168.4135142
|
|
877910435.5200007
|
|
576795027.7840261
|
|
576786920.9936816
|
|
65868305.855243735
|
|
34480887.75308482
|
|
325834504.8100463
|
|
593204159.5097291
|
|
741720976.4735416
|
|
1129021912.5070748
|
|
DROP TABLE t1;
|
|
SET windowing_use_high_precision= default;
|