SET windowing_use_high_precision= OFF;
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.3726779962	0.1388888889
2	0.4517539515	0.2040816327
2	0.4841229183	0.234375
2	0.496903995	0.2469135802
3	0.6633249581	0.44
3	0.7496555683	0.5619834711
3	0.7993052539	0.6388888889
3	0.8284868934	0.6863905325
4	0.9609731462	0.9234693878
4	1.0456258094	1.0933333333
4	1.1021995055	1.21484375
4	1.1406305547	1.3010380623
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
1	1,2,4,4,4	1.118033988749895
2	1,2,4,4	0.816496580927726
3	1,2,4,4	0.5
4	1,2,4,4	0
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
1	1,2,4,4,4	1.25
2	1,2,4,4	0.6666666666666666
3	1,2,4,4	0.25
4	1,2,4,4	0
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 (ORDER BY k DESC 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.09999999999999998
1.2	0.43204937989385733
2	0.4555216789572149
2	0.6383572667401852
3	0.4714045207910318
SELECT d, VARIANCE(d) OVER (ORDER BY d ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING) variance FROM td;
d	variance
NULL	0
1	0.009999999999999995
1.2	0.18666666666666668
2	0.20749999999999996
2	0.4075
3	0.22222222222222232
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.6666666666666666	0.816496580927726
6	30	0.6666666666666666	0.816496580927726
7	36	0.6666666666666666	0.816496580927726
7	36	0.6666666666666666	0.816496580927726
8	42	0.6666666666666666	0.816496580927726
8	42	0.6666666666666666	0.816496580927726
9	48	0.6666666666666666	0.816496580927726
9	48	0.6666666666666666	0.816496580927726
10	54	0.6666666666666666	0.816496580927726
10	54	0.6666666666666666	0.816496580927726
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.25	1.118033988749895
9	68	1.25	1.118033988749895
10	54	0.6666666666666666	0.816496580927726
10	54	0.6666666666666666	0.816496580927726
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.6666666666666666	0.816496580927726
4	30	0.6666666666666666	0.816496580927726
5	36	0.6666666666666666	0.816496580927726
5	36	0.6666666666666666	0.816496580927726
6	42	0.6666666666666666	0.816496580927726
6	42	0.6666666666666666	0.816496580927726
7	48	0.6666666666666666	0.816496580927726
7	48	0.6666666666666666	0.816496580927726
8	54	0.6666666666666666	0.816496580927726
8	54	0.6666666666666666	0.816496580927726
9	38	0.25	0.5
9	38	0.25	0.5
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.4	0.16	2
1.9	0.4	0.16	2
4	1.05	1.1025	2
8.3	0	0	1
16	0	0	1
20.1	0	0	1
22	1.2027745702	1.4466666667	3
23	0.8164965809	0.6666666667	3
24	0.8164965809	0.6666666667	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.5969205059
1.5969205059
1.5969205059
1.5969205059
1.5969205059
1.5969205059
1.5969205059
1.5969205059
1.5969205059
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.8164965809
3.14	DOUBLE	0.8623659316
4	INTEGER	0.7097006411
5	INTEGER	0.7991714459
[1, 2, 3]	ARRAY	1.8718106208
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.6666666667
3.14	DOUBLE	0.743675
4	INTEGER	0.503675
5	INTEGER	0.638675
[1, 2, 3]	ARRAY	3.503675
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.8164965809
3.14	DOUBLE	0.8623659316
3.14	DOUBLE	0.4768647607
4	INTEGER	0.3967366885
5	INTEGER	0.7664202503
[1, 2, 3]	ARRAY	1.4743388349
[1, 2, 3]	ARRAY	1.7853571071
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)
0	0.816496580927726	0
1	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)
0	0	0	0	0
1	0	0.25	0	0
2	0	0.6666666666666666	0	0
NULL	0	NULL	NULL	NULL
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)
10	1.1248582677159726
10	1.1248582677159726
10	1.1248582677159726
10	1.1248582677159726
10	1.1248582677159726
10	1.1248582677159726
11	1.2686114456365272
11	1.2686114456365272
12	1.4989708403591155
12	1.4989708403591155
7	0
7	0
8	0.4714045207910316
8	0.4714045207910316
8	0.4714045207910316
8	0.4714045207910316
9	0.7071067811865475
9	0.7071067811865475
NULL	NULL
NULL	NULL
NULL	NULL
NULL	NULL
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.71153580246914
NULL	NULL	2.71153580246914
1	0	2.71153580246914
[1, 2, 3]	0	2.71153580246914
[1, 2, 3]	0	2.71153580246914
2	0	2.71153580246914
3	0.06599663291074	2.71153580246914
3.14	0.06599663291074	2.71153580246914
3.14	0.06599663291074	2.71153580246914
4	0	2.71153580246914
5	0	2.71153580246914
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
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.3845
1567250109.3925
913658168.5479
0
0
434276491.9013
562298939.1038
562298939.1038
557831940.035
410388168.4135
877910435.52
576795027.784
576786920.9937
65868305.8552
34480887.7531
325834504.81
593204159.5097
741720976.4735
1129021912.5071
DROP TABLE t1;
SET windowing_use_high_precision= default;