create table t1 (
pk int primary key,
a int,
b int
);
create table t2 (
pk int primary key,
a int,
b int,
c char(10)
);
insert into t2 values
( 1, 0, 1, 'one'),
( 2, 0, 2, 'two'),
( 3, 0, 3, 'three'),
( 4, 1, 1, 'one'),
( 5, 1, 1, 'two'),
( 6, 1, 2, 'three');
# First try some invalid queries.
select std(c) over (order by a)
from t2;
std(c) over (order by a)
0
0
0
0
0
0
# Empty frame.
select std(b) over (order by a rows between 2 following and 1 following)
from t2;
std(b) over (order by a rows between 2 following and 1 following)
NULL
NULL
NULL
NULL
NULL
NULL
select std(b) over (order by a range between 2 following and 1 following)
from t2;
std(b) over (order by a range between 2 following and 1 following)
NULL
NULL
NULL
NULL
NULL
NULL
select std(b) over (order by a rows between 1 preceding and 2 preceding)
from t2;
std(b) over (order by a rows between 1 preceding and 2 preceding)
NULL
NULL
NULL
NULL
NULL
NULL
select std(b) over (order by a range between 1 preceding and 2 preceding)
from t2;
std(b) over (order by a range between 1 preceding and 2 preceding)
NULL
NULL
NULL
NULL
NULL
NULL
select std(b) over (order by a rows between 1 following and 0 following)
from t2;
std(b) over (order by a rows between 1 following and 0 following)
NULL
NULL
NULL
NULL
NULL
NULL
select std(b) over (order by a range between 1 following and 0 following)
from t2;
std(b) over (order by a range between 1 following and 0 following)
NULL
NULL
NULL
NULL
NULL
NULL
select std(b) over (order by a rows between 1 following and 0 preceding)
from t2;
std(b) over (order by a rows between 1 following and 0 preceding)
NULL
NULL
NULL
NULL
NULL
NULL
select std(b) over (order by a range between 1 following and 0 preceding)
from t2;
std(b) over (order by a range between 1 following and 0 preceding)
NULL
NULL
NULL
NULL
NULL
NULL
select std(b) over (order by a rows between 0 following and 1 preceding)
from t2;
std(b) over (order by a rows between 0 following and 1 preceding)
NULL
NULL
NULL
NULL
NULL
NULL
select std(b) over (order by a range between 0 following and 1 preceding)
from t2;
std(b) over (order by a range between 0 following and 1 preceding)
NULL
NULL
NULL
NULL
NULL
NULL
# 1 row frame.
select std(b) over (order by a rows between current row and current row)
from t2;
std(b) over (order by a rows between current row and current row)
0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
select std(b) over (order by a rows between 0 preceding and current row)
from t2;
std(b) over (order by a rows between 0 preceding and current row)
0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
select std(b) over (order by a rows between 0 preceding and 0 preceding)
from t2;
std(b) over (order by a rows between 0 preceding and 0 preceding)
0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
select std(b) over (order by a rows between 0 preceding and 0 following)
from t2;
std(b) over (order by a rows between 0 preceding and 0 following)
0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
select std(b) over (order by a rows between 0 following and 0 preceding)
from t2;
std(b) over (order by a rows between 0 following and 0 preceding)
0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
select std(b) over (order by a rows between 0 following and current row)
from t2;
ERROR HY000: Unacceptable combination of window frame bound specifications
select std(b) over (order by a rows between current row and 0 following)
from t2;
std(b) over (order by a rows between current row and 0 following)
0.0000
0.0000
0.0000
0.0000
0.0000
0.0000
# Only peers frame.
select a, b, std(b) over (order by a range between 0 preceding and 0 preceding)
from t2;
a	b	std(b) over (order by a range between 0 preceding and 0 preceding)
0	1	0.8165
0	2	0.8165
0	3	0.8165
1	1	0.4714
1	1	0.4714
1	2	0.4714
select a, b, std(b) over (order by a range between 0 preceding and current row)
from t2;
a	b	std(b) over (order by a range between 0 preceding and current row)
0	1	0.8165
0	2	0.8165
0	3	0.8165
1	1	0.4714
1	1	0.4714
1	2	0.4714
select a, b, std(b) over (order by a range between current row and 0 preceding)
from t2;
ERROR HY000: Unacceptable combination of window frame bound specifications
select a, b, std(b) over (order by a range between current row and 0 following)
from t2;
a	b	std(b) over (order by a range between current row and 0 following)
0	1	0.8165
0	2	0.8165
0	3	0.8165
1	1	0.4714
1	1	0.4714
1	2	0.4714
select a, b, std(b) over (order by a range between 0 following and 0 following)
from t2;
a	b	std(b) over (order by a range between 0 following and 0 following)
0	1	0.8165
0	2	0.8165
0	3	0.8165
1	1	0.4714
1	1	0.4714
1	2	0.4714
# 2 rows frame.
select pk, a, b, std(b) over (order by a, b, pk rows between 1 preceding and current row)
from t2;
pk	a	b	std(b) over (order by a, b, pk rows between 1 preceding and current row)
1	0	1	0.0000
2	0	2	0.5000
3	0	3	0.5000
4	1	1	1.0000
5	1	1	0.0000
6	1	2	0.5000
select pk, a, b, std(b) over (order by a, b, pk rows between 1 preceding and 0 preceding)
from t2;
pk	a	b	std(b) over (order by a, b, pk rows between 1 preceding and 0 preceding)
1	0	1	0.0000
2	0	2	0.5000
3	0	3	0.5000
4	1	1	1.0000
5	1	1	0.0000
6	1	2	0.5000
select pk, a, b, std(b) over (order by a, b, pk rows between current row and 1 following)
from t2;
pk	a	b	std(b) over (order by a, b, pk rows between current row and 1 following)
1	0	1	0.5000
2	0	2	0.5000
3	0	3	1.0000
4	1	1	0.0000
5	1	1	0.5000
6	1	2	0.0000
select pk, a, b, std(b) over (order by a, b, pk rows between 0 following and 1 following)
from t2;
pk	a	b	std(b) over (order by a, b, pk rows between 0 following and 1 following)
1	0	1	0.5000
2	0	2	0.5000
3	0	3	1.0000
4	1	1	0.0000
5	1	1	0.5000
6	1	2	0.0000
# 2 peers frame.
select pk, a, b, std(b) over (order by a range between 1 preceding and current row)
from t2;
pk	a	b	std(b) over (order by a range between 1 preceding and current row)
1	0	1	0.8165
2	0	2	0.8165
3	0	3	0.8165
4	1	1	0.7454
5	1	1	0.7454
6	1	2	0.7454
select pk, a, b, std(b) over (order by a range between 1 preceding and 0 preceding)
from t2;
pk	a	b	std(b) over (order by a range between 1 preceding and 0 preceding)
1	0	1	0.8165
2	0	2	0.8165
3	0	3	0.8165
4	1	1	0.7454
5	1	1	0.7454
6	1	2	0.7454
select pk, a, b, std(b) over (order by a range between current row and 1 following)
from t2;
pk	a	b	std(b) over (order by a range between current row and 1 following)
1	0	1	0.7454
2	0	2	0.7454
3	0	3	0.7454
4	1	1	0.4714
5	1	1	0.4714
6	1	2	0.4714
select pk, a, b, std(b) over (order by a range between 0 following and 1 following)
from t2;
pk	a	b	std(b) over (order by a range between 0 following and 1 following)
1	0	1	0.7454
2	0	2	0.7454
3	0	3	0.7454
4	1	1	0.4714
5	1	1	0.4714
6	1	2	0.4714
drop table t1;
drop table t2;