github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/window/window.result

drop table if exists t1;
create table t1 (a int, b datetime);
insert into t1 values(1, '2020-11-11'), (2, '2020-11-12'), (3, '2020-11-13');
select sum(a) over(partition by a order by b range between interval 1 day preceding and interval 2 day following) from t1;
sum(a) over (partition by a order by b range between interval(1, day) preceding and interval(2, day) following)
1
2
3
drop table t1;
drop table if exists t1;
create table t1 (a int, b date);
insert into t1 values(1, '2020-11-11'), (2, '2020-11-12'), (3, '2020-11-13'), (1, '2020-11-11'), (2, '2020-11-12'), (3, '2020-11-13'), (1, '2020-11-11'), (2, '2020-11-12'), (3, '2020-11-13');
select max(a) over(order by b range between interval 1 day preceding and interval 2 day following) from t1;
max(a) over (order by b range between interval(1, day) preceding and interval(2, day) following)
3
3
3
3
3
3
3
3
3
drop table t1;
drop table if exists t1;
create table t1 (a int, b time);
insert into t1 values(1, 112233), (2, 122233), (3, 132233), (1, 112233), (2, 122233), (3, 132233), (1, 112233), (2, 122233), (3, 132233);
select min(a) over(order by b range between interval 1 hour preceding and current row) from t1;
min(a) over (order by b range between interval(1, hour) preceding and current row)
1
1
1
1
1
1
2
2
2
drop table t1;
drop table if exists t1;
create table t1 (a int, b timestamp);
insert into t1 values(1, '2020-11-11'), (2, '2020-11-12'), (3, '2020-11-13'), (1, '2020-11-11'), (2, '2020-11-12'), (3, '2020-11-13'), (1, '2020-11-11'), (2, '2020-11-12'), (3, '2020-11-13');
select count(*) over(order by b range current row) from t1;
count(*) over (order by b range current row)
3
3
3
3
3
3
3
3
3
drop table t1;
drop table if exists t1;
create table t1 (a int, b int, c int);
insert into t1 values(1, 2, 1), (3, 4, 2), (5, 6, 3), (7, 8, 4), (3, 4, 5), (3, 4, 6), (3, 4, 7);
select a, rank() over (partition by a) from t1 group by a, c;
a    rank() over (partition by a)
1    1
3    1
3    1
3    1
3    1
5    1
7    1
select a, c, rank() over (partition by a order by c) from t1 group by a, c;
a    c    rank() over (partition by a order by c)
1    1    1
3    2    1
3    5    2
3    6    3
3    7    4
5    3    1
7    4    1
select a, c, rank() over (partition by a order by c) from t1 group by a, c;
a    c    rank() over (partition by a order by c)
1    1    1
3    2    1
3    5    2
3    6    3
3    7    4
5    3    1
7    4    1
select a, c, b, rank() over (partition by a, c, b) from t1;
a    c    b    rank() over (partition by a, c, b)
1    1    2    1
3    2    4    1
3    5    4    1
3    6    4    1
3    7    4    1
5    3    6    1
7    4    8    1
select a,  b, rank() over (partition by a, b) from t1;
a    b    rank() over (partition by a, b)
1    2    1
3    4    1
3    4    1
3    4    1
3    4    1
5    6    1
7    8    1
select a, c, sum(a) over (), sum(c) over () from t1;
a    c    sum(a) over ()    sum(c) over ()
1    1    25    28
3    2    25    28
5    3    25    28
7    4    25    28
3    5    25    28
3    6    25    28
3    7    25    28
select a, c, sum(a) over (order by c), sum(c) over (order by a) from t1;
a    c    sum(a) over (order by c)    sum(c) over (order by a)
1    1    1    1
3    2    4    21
3    5    19    21
3    6    22    21
3    7    25    21
5    3    9    24
7    4    16    28
select a, sum(b), sum(sum(b)) over (partition by a), sum(sum(b)) over (partition by c) from t1 group by a, c;
a    sum(b)    sum(sum(b)) over (partition by a)    sum(sum(b)) over (partition by c)
1    2    2    2
3    4    16    4
5    6    6    6
7    8    8    8
3    4    16    4
3    4    16    4
3    4    16    4
select a, sum(b), rank() over (partition by a +1), rank() over (partition by c), c from t1 group by a, c;
a    sum(b)    rank() over (partition by a + 1)    rank() over (partition by c)    c
1    2    1    1    1
3    4    1    1    2
5    6    1    1    3
7    8    1    1    4
3    4    1    1    5
3    4    1    1    6
3    4    1    1    7
select a, sum(b), sum(sum(b))  over (partition by a) as o from t1 group by a, c;
a    sum(b)    o
1    2    2
3    4    16
3    4    16
3    4    16
3    4    16
5    6    6
7    8    8
select a, sum(b), cast(sum(sum(b))  over (partition by a+1 order by a+1 rows between 2  preceding and CURRENT row) as float) as o from t1 group by a, c;
a    sum(b)    o
1    2    2.0
3    4    4.0
3    4    8.0
3    4    12.0
3    4    12.0
5    6    6.0
7    8    8.0
select a, sum(b), sum(sum(b)) over (partition by a rows BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) from
t1 group by a, c;
a    sum(b)    sum(sum(b)) over (partition by a rows between unbounded preceding and current row)
1    2    2
3    4    4
3    4    8
3    4    12
3    4    16
5    6    6
7    8    8
select a, sum(a) over (partition by c order by b range BETWEEN 3 preceding and 4 following), c, b from t1;
a    sum(a) over (partition by c order by b range between 3 preceding and 4 following)    c    b
1    1    1    2
3    3    2    4
5    5    3    6
7    7    4    8
3    3    5    4
3    3    6    4
3    3    7    4
select a, sum(a) over (order by a) from t1;
a    sum(a) over (order by a)
1    1
3    13
3    13
3    13
3    13
5    18
7    25
select a, rank() over (partition by a) from t1;
a    rank() over (partition by a)
1    1
3    1
3    1
3    1
3    1
5    1
7    1
select a, rank() over () from t1;
a    rank() over ()
1    1
3    1
5    1
7    1
3    1
3    1
3    1
select a, sum(a) over (partition by a rows current row) from t1;
a    sum(a) over (partition by a rows current row)
1    1
3    3
3    3
3    3
3    3
5    5
7    7
select c, sum(c) over (order by c range between 1 preceding and 1 following) from t1;
c    sum(c) over (order by c range between 1 preceding and 1 following)
1    3
2    6
3    9
4    12
5    15
6    18
7    13
select c, sum(100) over (order by c range between 1 preceding and 1 following), a, b from t1;
c    sum(100) over (order by c range between 1 preceding and 1 following)    a    b
1    200    1    2
2    300    3    4
3    300    5    6
4    300    7    8
5    300    3    4
6    300    3    4
7    200    3    4
select c, sum(null) over (order by c range between 1 preceding and 1 following), a, b from t1;
c    sum(null) over (order by c range between 1 preceding and 1 following)    a    b
1    null    1    2
2    null    3    4
3    null    5    6
4    null    7    8
5    null    3    4
6    null    3    4
7    null    3    4
select a, b, c, rank() over (partition by a, b order by c) from t1;
a    b    c    rank() over (partition by a, b order by c)
1    2    1    1
3    4    2    1
3    4    5    2
3    4    6    3
3    4    7    4
5    6    3    1
7    8    4    1
select a, c, rank() over(partition by a order by c rows current row) from t1;
a    c    rank() over (partition by a order by c rows current row)
1    1    1
3    2    1
3    5    2
3    6    3
3    7    4
5    3    1
7    4    1
select a, row_number() over (partition by a) from t1 group by a, c;
a    row_number() over (partition by a)
1    1
3    1
3    2
3    3
3    4
5    1
7    1
select a, c, row_number() over (partition by a order by c) from t1 group by a, c;
a    c    row_number() over (partition by a order by c)
1    1    1
3    2    1
3    5    2
3    6    3
3    7    4
5    3    1
7    4    1
select a, c, row_number() over (partition by a order by c) from t1 group by a, c;
a    c    row_number() over (partition by a order by c)
1    1    1
3    2    1
3    5    2
3    6    3
3    7    4
5    3    1
7    4    1
select a, c, b, row_number() over (partition by a, c, b) from t1;
a    c    b    row_number() over (partition by a, c, b)
1    1    2    1
3    2    4    1
3    5    4    1
3    6    4    1
3    7    4    1
5    3    6    1
7    4    8    1
select a,  b, row_number() over (partition by a, b) from t1;
a    b    row_number() over (partition by a, b)
1    2    1
3    4    1
3    4    2
3    4    3
3    4    4
5    6    1
7    8    1
select a, sum(b), row_number() over (partition by a +1), row_number() over (partition by c), c from t1 group by a, c;
a    sum(b)    row_number() over (partition by a + 1)    row_number() over (partition by c)    c
1    2    1    1    1
3    4    1    1    2
5    6    1    1    3
7    8    1    1    4
3    4    2    1    5
3    4    3    1    6
3    4    4    1    7
select a, row_number() over (partition by a) from t1;
a    row_number() over (partition by a)
1    1
3    1
3    2
3    3
3    4
5    1
7    1
select a, row_number() over () from t1;
a    row_number() over ()
1    1
3    2
5    3
7    4
3    5
3    6
3    7
select a, b, c, row_number() over (partition by a, b order by c) from t1;
a    b    c    row_number() over (partition by a, b order by c)
1    2    1    1
3    4    2    1
3    4    5    2
3    4    6    3
3    4    7    4
5    6    3    1
7    8    4    1
select a, c, row_number() over(partition by a order by c rows current row) from t1;
a    c    row_number() over (partition by a order by c rows current row)
1    1    1
3    2    1
3    5    2
3    6    3
3    7    4
5    3    1
7    4    1
select a, dense_rank() over (partition by a) from t1 group by a, c;
a    dense_rank() over (partition by a)
1    1
3    1
3    1
3    1
3    1
5    1
7    1
select a, c, dense_rank() over (partition by a order by c) from t1 group by a, c;
a    c    dense_rank() over (partition by a order by c)
1    1    1
3    2    1
3    5    2
3    6    3
3    7    4
5    3    1
7    4    1
select a, c, dense_rank() over (partition by a order by c) from t1 group by a, c;
a    c    dense_rank() over (partition by a order by c)
1    1    1
3    2    1
3    5    2
3    6    3
3    7    4
5    3    1
7    4    1
select a, c, b, dense_rank() over (partition by a, c, b) from t1;
a    c    b    dense_rank() over (partition by a, c, b)
1    1    2    1
3    2    4    1
3    5    4    1
3    6    4    1
3    7    4    1
5    3    6    1
7    4    8    1
select a,  b, dense_rank() over (partition by a, b) from t1;
a    b    dense_rank() over (partition by a, b)
1    2    1
3    4    1
3    4    1
3    4    1
3    4    1
5    6    1
7    8    1
select a, sum(b), dense_rank() over (partition by a +1), dense_rank() over (partition by c), c from t1 group by a, c;
a    sum(b)    dense_rank() over (partition by a + 1)    dense_rank() over (partition by c)    c
1    2    1    1    1
3    4    1    1    2
5    6    1    1    3
7    8    1    1    4
3    4    1    1    5
3    4    1    1    6
3    4    1    1    7
select a, dense_rank() over (partition by a) from t1;
a    dense_rank() over (partition by a)
1    1
3    1
3    1
3    1
3    1
5    1
7    1
select a, dense_rank() over () from t1;
a    dense_rank() over ()
1    1
3    1
5    1
7    1
3    1
3    1
3    1
select a, b, c, dense_rank() over (partition by a, b order by c) from t1;
a    b    c    dense_rank() over (partition by a, b order by c)
1    2    1    1
3    4    2    1
3    4    5    2
3    4    6    3
3    4    7    4
5    6    3    1
7    8    4    1
select a, c, dense_rank() over(partition by a order by c rows current row) from t1;
a    c    dense_rank() over (partition by a order by c rows current row)
1    1    1
3    2    1
3    5    2
3    6    3
3    7    4
5    3    1
7    4    1
select a, c, rank() over(order by a), row_number() over(order by a), dense_rank() over(order by a) from t1;
a    c    rank() over (order by a)    row_number() over (order by a)    dense_rank() over (order by a)
1    1    1    1    1
3    2    2    2    2
3    5    2    3    2
3    6    2    4    2
3    7    2    5    2
5    3    6    6    3
7    4    7    7    4
drop table t1;
drop table if exists t1;
create table t1 (a int, b decimal(7, 2));
insert into t1 values(1, 12.12), (2, 123.13), (3, 456.66), (4, 1111.34);
select a, sum(b) over (partition by a order by a) from t1;
a    sum(b) over (partition by a order by a)
1    12.12
2    123.13
3    456.66
4    1111.34
drop table t1;
drop table if exists wf01;
create table wf01(i int,j int);
insert into wf01 values(1,1);
insert into wf01 values(1,4);
insert into wf01 values(1,2);
insert into wf01 values(1,4);
select * from wf01;
i    j
1    1
1    4
1    2
1    4
select i, j, sum(i+j) over (order by j desc rows unbounded preceding) foo from wf01;
i    j    foo
1    4    5
1    4    10
1    2    13
1    1    15
select i, j, sum(i+j) over (order by j desc rows between 2 preceding and 2 following) as foo from wf01;
i    j    foo
1    4    13
1    4    15
1    2    15
1    1    10
select i, j, sum(i+j) over (order by j desc rows unbounded preceding) foo from wf01 order by foo;
i    j    foo
1    4    5
1    4    10
1    2    13
1    1    15
select i, j, sum(i+j) over (order by j desc rows unbounded preceding) foo from wf01 order by foo desc;
i    j    foo
1    1    15
1    2    13
1    4    10
1    4    5
drop table wf01;
drop table if exists wf08;
create table wf08(d decimal(10,2), date date);
insert into wf08 values (10.4, '2002-06-09');
insert into wf08 values (20.5, '2002-06-09');
insert into wf08 values (10.4, '2002-06-10');
insert into wf08 values (3,    '2002-06-09');
insert into wf08 values (40.2, '2015-08-01');
insert into wf08 values (40.2, '2002-06-09');
insert into wf08 values (5,    '2015-08-01');
select * from (select rank() over (order by d) as `rank`, d, date from wf08) alias order by `rank`, d, date;
rank    d    date
1    3.00    2002-06-09
2    5.00    2015-08-01
3    10.40    2002-06-09
3    10.40    2002-06-10
5    20.50    2002-06-09
6    40.20    2002-06-09
6    40.20    2015-08-01
select * from (select dense_rank() over (order by d) as `d_rank`, d, date from wf08) alias order by `d_rank`, d, date;
d_rank    d    date
1    3.00    2002-06-09
2    5.00    2015-08-01
3    10.40    2002-06-09
3    10.40    2002-06-10
4    20.50    2002-06-09
5    40.20    2002-06-09
5    40.20    2015-08-01
drop table wf08;
drop table if exists wf07;
create table wf07 (user_id integer not null, date date);
insert into wf07 values (1, '2002-06-09');
insert into wf07 values (2, '2002-06-09');
insert into wf07 values (1, '2002-06-09');
insert into wf07 values (3, '2002-06-09');
insert into wf07 values (4, '2002-06-09');
insert into wf07 values (4, '2002-06-09');
insert into wf07 values (5, '2002-06-09');
select rank() over () r from wf07;
r
1
1
1
1
1
1
1
select dense_rank() over () r from wf07;
r
1
1
1
1
1
1
1
drop table wf07;
drop table if exists wf12;
create table wf12(d double);
insert into wf12 values (1.7976931348623157e+307);
insert into wf12 values (1);
select d, sum(d) over (rows between current row and 1 following) from wf12;
d    sum(d) over (rows between current row and 1 following)
1.7976931348623158E307    1.7976931348623158E307
1.0    1.0
drop table wf12;
drop table if exists wf06;
create table wf06 (id integer, sex char(1));
insert into wf06 values (1, 'm');
insert into wf06 values (2, 'f');
insert into wf06 values (3, 'f');
insert into wf06 values (4, 'f');
insert into wf06 values (5, 'm');
drop table if exists wf07;
create table wf07 (user_id integer not null, date date);
insert into wf07 values (1, '2002-06-09');
insert into wf07 values (2, '2002-06-09');
insert into wf07 values (1, '2002-06-09');
insert into wf07 values (3, '2002-06-09');
insert into wf07 values (4, '2002-06-09');
insert into wf07 values (4, '2002-06-09');
insert into wf07 values (5, '2002-06-09');
select id value, sum(id) over (rows unbounded preceding) from wf06 inner join wf07 on wf07.user_id = wf06.id;
value    sum(id) over (rows unbounded preceding)
1    1
1    2
2    4
3    7
4    11
4    15
5    20
drop table wf06;
drop table wf07;
drop table if exists row01;
create table row01(i int,j int);
insert into row01 values(1,1);
insert into row01 values(1,4);
insert into row01 values(1,2);
insert into row01 values(1,4);
select i, j, sum(i+j) over (order by j rows between 2 preceding and 1 preceding) foo from row01 order by foo desc;
i    j    foo
1    4    8
1    4    5
1    2    2
1    1    null
select i, j, sum(i+j) over (order by j rows between 2 following and 1 following) foo from row01 order by foo desc;
i    j    foo
1    1    null
1    2    null
1    4    null
1    4    null
drop table row01;
drop table if exists test01;
create table test01(i int, j int);
insert into test01 values (1,null);
insert into test01 values (1,null);
insert into test01 values (1,1);
insert into test01 values (1,null);
insert into test01 values (1,2);
insert into test01 values (2,1);
insert into test01 values (2,2);
insert into test01 values (2,null);
insert into test01 values (2,null);
select i, j, min(j) over (partition by i order by j rows unbounded preceding) from test01;
i    j    min(j) over (partition by i order by j rows unbounded preceding)
1    null    null
1    null    null
1    null    null
1    1    1
1    2    1
2    null    null
2    null    null
2    1    1
2    2    1
drop table test01;
drop table if exists double01;
create table double01(d double);
insert into double01 values (2);
insert into double01 values (2);
insert into double01 values (3);
insert into double01 values (1);
insert into double01 values (1);
insert into double01 values (1.2);
insert into double01 values (null);
insert into double01 values (null);
select d, sum(d) over (partition by d order by d), avg(d) over (order by d rows between 1 preceding and 1 following) from double01;
d    sum(d) over (partition by d order by d)    avg(d) over (order by d rows between 1 preceding and 1 following)
null    null    null
null    null    1.0
1.0    2.0    1.0
1.0    2.0    1.0666666666666667
1.2    1.2    1.4000000000000001
2.0    4.0    1.7333333333333334
2.0    4.0    2.3333333333333335
3.0    3.0    2.5
select d, sum(d) over (partition by d order by d), avg(d) over (order by d rows between 2 preceding and 1 following) from double01;
d    sum(d) over (partition by d order by d)    avg(d) over (order by d rows between 2 preceding and 1 following)
null    null    null
null    null    1.0
1.0    2.0    1.0
1.0    2.0    1.0666666666666667
1.2    1.2    1.3
2.0    4.0    1.55
2.0    4.0    2.05
3.0    3.0    2.3333333333333335
drop table double01;
drop table if exists wf01;
create table wf01(d float);
insert into wf01 values (10);
insert into wf01 values (1);
insert into wf01 values (2);
insert into wf01 values (3);
insert into wf01 values (4);
insert into wf01 values (5);
insert into wf01 values (6);
insert into wf01 values (7);
insert into wf01 values (8);
insert into wf01 values (9);
select d, sum(d) over (order by d range between current row and 2 following), avg(d) over (order by d range between current row and 2 following) from wf01;
d    sum(d) over (order by d range between current row and 2 following)    avg(d) over (order by d range between current row and 2 following)
1.0    6.0    2.0
2.0    9.0    3.0
3.0    12.0    4.0
4.0    15.0    5.0
5.0    18.0    6.0
6.0    21.0    7.0
7.0    24.0    8.0
8.0    27.0    9.0
9.0    19.0    9.5
10.0    10.0    10.0
select d, sum(d) over (order by d range between 2 preceding and 2 following), avg(d) over (order by d range between current row and 2 following) from wf01;
d    sum(d) over (order by d range between 2 preceding and 2 following)    avg(d) over (order by d range between current row and 2 following)
1.0    6.0    2.0
2.0    10.0    3.0
3.0    15.0    4.0
4.0    20.0    5.0
5.0    25.0    6.0
6.0    30.0    7.0
7.0    35.0    8.0
8.0    40.0    9.0
9.0    34.0    9.5
10.0    27.0    10.0
drop table wf01;
drop table if exists dense_rank01;
create table dense_rank01 (id integer, sex char(1));
insert into dense_rank01 values (1, 'm');
insert into dense_rank01 values (2, 'f');
insert into dense_rank01 values (3, 'f');
insert into dense_rank01 values (4, 'f');
insert into dense_rank01 values (5, 'm');
select sex, id, rank() over (partition by sex order by id desc) from dense_rank01;
sex    id    rank() over (partition by sex order by id desc)
f    4    1
f    3    2
f    2    3
m    5    1
m    1    2
select sex, id, dense_rank() over (partition by sex order by id desc) from dense_rank01;
sex    id    dense_rank() over (partition by sex order by id desc)
f    4    1
f    3    2
f    2    3
m    5    1
m    1    2
drop table dense_rank01;
drop table if exists sales;
create table sales (customer_id varchar(1), order_date date, product_id integer);
insert into sales(customer_id, order_date, product_id) values ('a', '2021-01-01', '1'), ('a', '2021-01-01', '2'), ('a', '2021-01-07', '2'), ('a', '2021-01-10', '3'), ('a', '2021-01-11', '3'), ('a', '2021-01-11', '3'),('b', '2021-01-01', '2'),('b', '2021-01-02', '2'),('b', '2021-01-04', '1'),('b', '2021-01-11', '1'),('b', '2021-01-16', '3'),('b', '2021-02-01', '3'),('c', '2021-01-01', '3'),('c', '2021-01-01', '3'),('c', '2021-01-07', '3');
drop table if exists menu;
create table menu (product_id integer,product_name varchar(5),price integer);
insert into menu(product_id, product_name, price) values ('1', 'sushi', '10'),('2', 'curry', '15'),('3', 'ramen', '12');
with ordered_sales as (select sales.customer_id, sales.order_date, menu.product_name,dense_rank() over (partition by sales.customer_id order by sales.order_date) as `rank` from sales inner join menu on sales.product_id = menu.product_id) select customer_id, product_name from ordered_sales where `rank` = 1 group by customer_id, product_name;
customer_id    product_name
a    sushi
a    curry
b    curry
c    ramen
drop table sales;
drop table if exists test01;
create table test01(i int, j int);
insert into test01 values (1,null);
insert into test01 values (1,null);
insert into test01 values (1,1);
insert into test01 values (1,null);
insert into test01 values (1,2);
insert into test01 values (2,1);
insert into test01 values (2,2);
insert into test01 values (2,null);
insert into test01 values (2,null);
select rank() over (order by t0.a) as b from (select i as a from test01) as t0;
b
1
1
1
1
1
6
6
6
6
select rank() over(order by j) as col, j from test01;
col    j
1    null
1    null
1    null
1    null
1    null
6    1
6    1
8    2
8    2
drop table test01;
drop table if exists wf14;
create table wf14 (id integer, sex char(1));
insert into wf14 values (1, 'm');
insert into wf14 values (2, 'f');
insert into wf14 values (3, 'f');
insert into wf14 values (4, 'f');
insert into wf14 values (5, 'm');
insert into wf14 values (10, null);
insert into wf14 values (11, null);
insert into wf14 values (10, null);
select id, sex, sum(id) over (partition by sex order by id rows between 2 preceding and 1 following) as a from wf14;
id    sex    a
10    null    20
10    null    31
11    null    31
2    f    5
3    f    9
4    f    9
1    m    6
5    m    6
select id, sex, sum(id) over (partition by sex order by id rows between 2 preceding and 1 following) as a from wf14;
id    sex    a
10    null    20
10    null    31
11    null    31
2    f    5
3    f    9
4    f    9
1    m    6
5    m    6
select id, sex, sum(id) over (partition by sex order by id rows between 2 preceding and 1 following) as a from wf14;
id    sex    a
10    null    20
10    null    31
11    null    31
2    f    5
3    f    9
4    f    9
1    m    6
5    m    6
drop table wf14;
drop database if exists test;
create database test;
use test;
drop table if exists bool01;
create table bool01(col1 int,col2 bool,col3 datetime);
insert into bool01 values(1, true, '2023-05-16 00:12:12');
insert into bool01 values(2, false, '1997-01-13 12:12:00');
insert into bool01 values(3, true, '2000-10-10 11:11:11');
insert into bool01 values(4, false, '1020-10-01 01:01:01');
insert into bool01 values(5, null, null);
insert into bool01 values(6, null, '1997-11-10 10:10:10');
select * from bool01;
col1    col2    col3
1    true    2023-05-16 00:12:12
2    false    1997-01-13 12:12:00
3    true    2000-10-10 11:11:11
4    false    1020-10-01 01:01:01
5    null    null
6    null    1997-11-10 10:10:10
select rank() over (partition by col2 order by col1), sum(col1) over (partition by col2 order by col3) from bool01;
rank() over (partition by col2 order by col1)    sum(col1) over (partition by col2 order by col3)
1    5
2    11
2    4
1    6
2    3
1    4
select dense_rank() over (partition by col2 order by col1), sum(col1) over (partition by col2 order by col3) from bool01;
dense_rank() over (partition by col2 order by col1)    sum(col1) over (partition by col2 order by col3)
1    5
2    11
2    4
1    6
2    3
1    4
drop table bool01;
drop table varchar01 if exists;
SQL parser error: You have an error in your SQL syntax; check the manual that corresponds to your MatrixOne server version for the right syntax to use. syntax error at line 1 column 23 near " if exists;";
create table varchar01(col1 int, col2 varchar(12) primary key);
insert into varchar01 values(1, 'dhwenfewrfew');
insert into varchar01 values(2, 'wyeuijdew');
insert into varchar01 values(3, '数据库');
insert into varchar01 values(4, 'hejwkvrewvre');
insert into varchar01 values(5, '**&');
insert into varchar01 values(6, '12345');
insert into varchar01 values(7, 'database');
select *, rank() over (partition by col2 order by col1) as tmp from varchar01;
col1    col2    tmp
5    **&    1
6    12345    1
7    database    1
1    dhwenfewrfew    1
4    hejwkvrewvre    1
2    wyeuijdew    1
3    数据库    1
select dense_rank() over (partition by col2 order by col1) as tmp from varchar01;
tmp
1
1
1
1
1
1
1
drop table varchar01;
drop table if exists char01;
create table char01 (col1 integer, col2 char(1));
create table char01 (col1 integer, col2 char(1));
table char01 already exists
insert into char01 values (1, 'm');
insert into char01 values (2, 'f');
insert into char01 values (3, 'f');
insert into char01 values (4, 'f');
insert into char01 values (5, 'm');
select * from char01;
col1    col2
1    m
2    f
3    f
4    f
5    m
select *, rank() over (partition by col2 order by col1) as tmp from char01;
col1    col2    tmp
2    f    1
3    f    2
4    f    3
1    m    1
5    m    2
select dense_rank() over (partition by col2 order by col1) as tmp from char01;
tmp
1
2
3
1
2
drop table char01;
drop table if exists text01;
create table text01(col1 int, col2 text);
insert into text01 values(1, 'vdjnekwvrewvrjewkrmbew  bkejwkvmekrememwkvrewvrew re');
insert into text01 values(2, 'vdjnekwvrewvrjewkrmbew  bkejwkvmekrememwkvrewvrew re');
insert into text01 values(3, null);
insert into text01 values(4, '数据库，数据库，数据库，mo，mo，mo!');
insert into text01 values(5, null);
insert into text01 values(6, '数据库，数据库，数据库，mo，mo，mo!');
insert into text01 values(7, null);
select * from text01;
col1    col2
1    vdjnekwvrewvrjewkrmbew  bkejwkvmekrememwkvrewvrew re
2    vdjnekwvrewvrjewkrmbew  bkejwkvmekrememwkvrewvrew re
3    null
4    数据库，数据库，数据库，mo，mo，mo!
5    null
6    数据库，数据库，数据库，mo，mo，mo!
7    null
select *, rank() over (partition by col2 order by col1) as tmp from text01;
col1    col2    tmp
3    null    1
5    null    2
7    null    3
1    vdjnekwvrewvrjewkrmbew  bkejwkvmekrememwkvrewvrew re    1
2    vdjnekwvrewvrjewkrmbew  bkejwkvmekrememwkvrewvrew re    2
4    数据库，数据库，数据库，mo，mo，mo!    1
6    数据库，数据库，数据库，mo，mo，mo!    2
select dense_rank() over (partition by col2 order by col1) as tmp from text01;
tmp
1
2
3
1
2
1
2
drop table text01;
drop table if exists int01;
create table int01(col1 tinyint unsigned, col2 int, col3 timestamp);
insert into int01 values(100, 100, '2023-05-16 00:12:12');
insert into int01 values(98, -10, '2023-05-16 00:12:12');
insert into int01 values(100, null, '1997-05-16 00:12:12');
insert into int01 values(null, 100, '2023-05-16 00:12:12');
insert into int01 values(0, null, '1997-05-16 00:12:12');
insert into int01 values(null, null, null);
select * from int01;
col1    col2    col3
100    100    2023-05-16 00:12:12
98    -10    2023-05-16 00:12:12
100    null    1997-05-16 00:12:12
null    100    2023-05-16 00:12:12
0    null    1997-05-16 00:12:12
null    null    null
select col1, avg(col2) over (partition by col1 order by col2) as tmp from int01;
col1    tmp
null    null
null    100.0
0    null
98    -10.0
100    null
100    100.0
select col1, sum(col2) over (partition by col2 order by col1) as tmp from int01;
col1    tmp
null    null
0    null
100    null
98    -10
null    100
100    200
select col1, max(col1) over (partition by col1 rows between 1 preceding and 1 following) from int01;
col1    max(col1) over (partition by col1 rows between 1 preceding and 1 following)
null    null
null    null
0    0
98    98
100    100
100    100
select col1, min(col2) over (partition by col3 order by col2) from int01;
col1    min(col2) over (partition by col3 order by col2)
null    null
100    null
0    null
98    -10
null    -10
100    -10
drop table int01;
drop table if exists float01;
create table float01(col1 float, col2 date);
insert into float01 values(12434321313.213213,'2020-01-01');
insert into float01 values(null,'1997-01-13');
insert into float01 values(-12434321313.213213,'1000-10-10');
insert into float01 values(null,'2020-01-01');
insert into float01 values(null,null);
insert into float01 values(12434321313.213213,null);
insert into float01 values(0,'1997-01-13');
insert into float01 values(0,'1000-12-12');
insert into float01 values(12434321313.213213,null);
select * from float01;
col1    col2
1.2434321E10    2020-01-01
null    1997-01-13
-1.2434321E10    1000-10-10
null    2020-01-01
null    null
1.2434321E10    null
0.0    1997-01-13
0.0    1000-12-12
1.2434321E10    null
select col2, avg(col1) over (partition by col1 order by col2) as tmp from float01;
col2    tmp
null    null
1997-01-13    null
2020-01-01    null
1000-10-10    -1.2434321408E10
1000-12-12    0.0
1997-01-13    0.0
null    1.2434321408E10
null    1.2434321408E10
2020-01-01    1.2434321408E10
select col2, sum(col1) over (partition by col2 order by col1) as tmp from float01;
col2    tmp
null    null
null    2.4868642816E10
null    2.4868642816E10
1000-10-10    -1.2434321408E10
1000-12-12    0.0
1997-01-13    null
1997-01-13    0.0
2020-01-01    null
2020-01-01    1.2434321408E10
select col2, max(col1) over (partition by col1 rows between 1 preceding and 1 following) from float01;
col2    max(col1) over (partition by col1 rows between 1 preceding and 1 following)
1997-01-13    null
2020-01-01    null
null    null
1000-10-10    -1.2434321E10
1997-01-13    0.0
1000-12-12    0.0
2020-01-01    1.2434321E10
null    1.2434321E10
null    1.2434321E10
select col2, min(col1) over (partition by col2 order by col2) from float01;
col2    min(col1) over (partition by col2 order by col2)
null    null
null    1.2434321E10
null    1.2434321E10
1000-10-10    -1.2434321E10
1000-12-12    0.0
1997-01-13    0.0
1997-01-13    0.0
2020-01-01    1.2434321E10
2020-01-01    1.2434321E10
drop table float01;
drop table if exists double01;
create table double01(d double);
insert into double01 values (2);
insert into double01 values (2);
insert into double01 values (3);
insert into double01 values (1);
insert into double01 values (1);
insert into double01 values (1.2);
insert into double01 values (null);
insert into double01 values (null);
select * from double01;
d
2.0
2.0
3.0
1.0
1.0
1.2
null
null
select d, sum(d) over (partition by d order by d), avg(d) over (order by d) from double01;
d    sum(d) over (partition by d order by d)    avg(d) over (order by d)
null    null    null
null    null    null
1.0    2.0    1.0
1.0    2.0    1.0
1.2    1.2    1.0666666666666667
2.0    4.0    1.44
2.0    4.0    1.44
3.0    3.0    1.7
select d, sum(d) over (partition by d order by d), avg(d) over (order by d rows between 1 preceding and 1 following) from double01;
d    sum(d) over (partition by d order by d)    avg(d) over (order by d rows between 1 preceding and 1 following)
null    null    null
null    null    1.0
1.0    2.0    1.0
1.0    2.0    1.0666666666666667
1.2    1.2    1.4000000000000001
2.0    4.0    1.7333333333333334
2.0    4.0    2.3333333333333335
3.0    3.0    2.5
select d, max(d) over (partition by d) from double01;
d    max(d) over (partition by d)
null    null
null    null
1.0    1.0
1.0    1.0
1.2    1.2
2.0    2.0
2.0    2.0
3.0    3.0
select d, sum(d) over (partition by d order by d) from double01;
d    sum(d) over (partition by d order by d)
null    null
null    null
1.0    2.0
1.0    2.0
1.2    1.2
2.0    4.0
2.0    4.0
3.0    3.0
truncate double01;
select * from double01;
d
insert into double01 values (1.7976931348623157e+307);
insert into double01 values (1);
select * from double01;
d
1.7976931348623158E307
1.0
select d, sum(d) over (rows between current row and 1 following) from double01;
d    sum(d) over (rows between current row and 1 following)
1.7976931348623158E307    1.7976931348623158E307
1.0    1.0
drop table double01;
drop table if exists decimal01;
create table decimal01(d decimal(38,3));
insert into decimal01 values (28888888888888888888888888888888888.1234);
insert into decimal01 values (99999999999999999999999999999999999.83293323);
insert into decimal01 values (0);
insert into decimal01 values (-7841512312154312313158786541.342152121242143);
insert into decimal01 values (-7841512312154312313158786541.342152121242143);
insert into decimal01 values (99999999999999999999999999999999999.83293323);
insert into decimal01 values (null);
insert into decimal01 values (null);
select * from decimal01;
d
28888888888888888888888888888888888.123
99999999999999999999999999999999999.833
0.000
-7841512312154312313158786541.342
-7841512312154312313158786541.342
99999999999999999999999999999999999.833
null
null
select max(d) over (partition by d order by d) from decimal01;
max(d) over (partition by d order by d)
null
null
-7841512312154312313158786541.342
-7841512312154312313158786541.342
0.000
28888888888888888888888888888888888.123
99999999999999999999999999999999999.833
99999999999999999999999999999999999.833
select min(d) over (partition by d order by d) from decimal01;
min(d) over (partition by d order by d)
null
null
-7841512312154312313158786541.342
-7841512312154312313158786541.342
0.000
28888888888888888888888888888888888.123
99999999999999999999999999999999999.833
99999999999999999999999999999999999.833
select avg(d) over (partition by d) from decimal01;
invalid input: Decimal128 Div overflow: 28888888888888888888888888888888888123(Scale:3)/1(Scale:0)
select sum(d) over (partition by d order by d rows between 1 preceding and 1 following) from decimal01;
[unknown result because it is related to issue#10043]
drop table decimal01;
drop table if exists date01;
create table date01(id date);
insert into date01 values ('2002-06-09');
insert into date01 values ('2002-06-09');
insert into date01 values ('2002-06-10');
insert into date01 values ('2002-06-09');
insert into date01 values ('2015-08-01');
insert into date01 values ('2002-06-09');
insert into date01 values ('2015-08-01');
select id, rank() over () from date01;
id    rank() over ()
2002-06-09    1
2002-06-09    1
2002-06-10    1
2002-06-09    1
2015-08-01    1
2002-06-09    1
2015-08-01    1
select id, dense_rank() over (order by id) from date01;
id    dense_rank() over (order by id)
2002-06-09    1
2002-06-09    1
2002-06-09    1
2002-06-09    1
2002-06-10    2
2015-08-01    3
2015-08-01    3
select id, max(id) over (order by id rows 2 preceding) from date01;
id    max(id) over (order by id rows 2 preceding)
2002-06-09    2002-06-09
2002-06-09    2002-06-09
2002-06-09    2002-06-09
2002-06-09    2002-06-09
2002-06-10    2002-06-10
2015-08-01    2015-08-01
2015-08-01    2015-08-01
select min(id) over (partition by id order by id range interval 2 day preceding) from date01;
min(id) over (partition by id order by id range interval(2, day) preceding)
2002-06-09
2002-06-09
2002-06-09
2002-06-09
2002-06-10
2015-08-01
2015-08-01
select id, count(id) over (order by id rows between 2 preceding and 1 following) from date01;
id    count(id) over (order by id rows between 2 preceding and 1 following)
2002-06-09    2
2002-06-09    3
2002-06-09    4
2002-06-09    4
2002-06-10    4
2015-08-01    4
2015-08-01    3
select id, count(id) over (order by date_add(id,interval 3 day) rows between 2 preceding and 1 following) from date01;
id    count(id) over (order by date_add(id, interval(3, day)) rows between 2 preceding and 1 following)
2002-06-09    2
2002-06-09    3
2002-06-09    4
2002-06-09    4
2002-06-10    4
2015-08-01    4
2015-08-01    3
drop table date01;
drop table if exists test01;
create table test01(i int, j int);
insert into test01 values (1,null);
insert into test01 values (1,null);
insert into test01 values (1,1);
insert into test01 values (1,null);
insert into test01 values (1,2);
insert into test01 values (2,1);
insert into test01 values (2,2);
insert into test01 values (2,null);
insert into test01 values (2,null);
select * from test01;
i    j
1    null
1    null
1    1
1    null
1    2
2    1
2    2
2    null
2    null
select i, j, sum(j) over (partition by i order by j rows unbounded preceding) from test01;
i    j    sum(j) over (partition by i order by j rows unbounded preceding)
1    null    null
1    null    null
1    null    null
1    1    1
1    2    3
2    null    null
2    null    null
2    1    1
2    2    3
select i, j, avg(j) over (partition by i order by j rows unbounded preceding) from test01;
i    j    avg(j) over (partition by i order by j rows unbounded preceding)
1    null    null
1    null    null
1    null    null
1    1    1.0
1    2    1.5
2    null    null
2    null    null
2    1    1.0
2    2    1.5
select i, j, max(j) over (partition by i order by j rows unbounded preceding) from test01;
i    j    max(j) over (partition by i order by j rows unbounded preceding)
1    null    null
1    null    null
1    null    null
1    1    1
1    2    2
2    null    null
2    null    null
2    1    1
2    2    2
select i, j, min(j) over (partition by i order by j rows unbounded preceding) from test01;
i    j    min(j) over (partition by i order by j rows unbounded preceding)
1    null    null
1    null    null
1    null    null
1    1    1
1    2    1
2    null    null
2    null    null
2    1    1
2    2    1
drop table test01;
drop table if exists row01;
create table row01(i int,j int);
insert into row01 values(1,1);
insert into row01 values(1,4);
insert into row01 values(1,2);
insert into row01 values(1,4);
select * from row01;
i    j
1    1
1    4
1    2
1    4
select i, j, sum(i+j) over (rows unbounded preceding) foo from row01;
i    j    foo
1    1    2
1    4    7
1    2    10
1    4    15
select i, j, sum(i+j) over (rows between unbounded preceding and current row) foo from row01;
i    j    foo
1    1    2
1    4    7
1    2    10
1    4    15
select i, j, sum(i+j) over (rows unbounded preceding) foo from row01 order by foo;
i    j    foo
1    1    2
1    4    7
1    2    10
1    4    15
select i, j, sum(i+j) over (rows unbounded preceding) foo from row01 order by foo desc;
i    j    foo
1    4    15
1    2    10
1    4    7
1    1    2
select i, j, sum(i+j) over (order by j desc rows unbounded preceding) foo from row01;
i    j    foo
1    4    5
1    4    10
1    2    13
1    1    15
select i, j, sum(i+j) over (order by j desc rows between 2 preceding and 2 following) as foo from row01;
i    j    foo
1    4    13
1    4    15
1    2    15
1    1    10
select i, j, sum(i+j) over (order by j desc rows unbounded preceding) foo from row01 order by foo;
i    j    foo
1    4    5
1    4    10
1    2    13
1    1    15
select i, j, sum(i+j) over (order by j desc rows unbounded preceding) foo from row01 order by foo desc;
i    j    foo
1    1    15
1    2    13
1    4    10
1    4    5
select i, j, sum(i+j) over (rows unbounded preceding) foo from row01 order by foo desc limit 3;
i    j    foo
1    4    15
1    2    10
1    4    7
select i, j, sum(i+j) over (order by j rows unbounded preceding) foo from row01;
i    j    foo
1    1    2
1    2    5
1    4    10
1    4    15
select i, j, sum(i+j) over (order by j rows unbounded preceding) foo from row01 order by foo;
i    j    foo
1    1    2
1    2    5
1    4    10
1    4    15
select i, j, sum(i+j) over (order by j rows unbounded preceding) foo from row01 order by foo desc;
i    j    foo
1    4    15
1    4    10
1    2    5
1    1    2
select i, j, sum(i+j) over (order by j rows between 2 preceding and 1 preceding) foo from row01 order by foo desc;
i    j    foo
1    4    8
1    4    5
1    2    2
1    1    null
select i, j, sum(i+j) over (order by j rows between 2 following and 1 following) foo from row01 order by foo desc;
i    j    foo
1    1    null
1    2    null
1    4    null
1    4    null
select i, j, sum(i+j) over (order by j rows between -1 following and 1 following) foo from row01 order by foo desc;
SQL parser error: You have an error in your SQL syntax; check the manual that corresponds to your MatrixOne server version for the right syntax to use. syntax error at line 1 column 53 near " -1 following and 1 following) foo from row01 order by foo desc;";
select i, j, sum(i+j) over (order by j rows between 2 preceding and -10 following) foo from row01 order by foo desc;
SQL parser error: You have an error in your SQL syntax; check the manual that corresponds to your MatrixOne server version for the right syntax to use. syntax error at line 1 column 69 near " -10 following) foo from row01 order by foo desc;";
drop table row01;
drop table if exists wf02;
create table wf02 (i int) ;
insert into wf02 (i) values (1);
insert into wf02 (i) values (2);
insert into wf02 (i) values (3);
insert into wf02 (i) values (4);
insert into wf02 (i) values (5);
select * from wf02;
i
1
2
3
4
5
select i, sum(i) over (rows between 0 preceding and 2 following) from wf02;
i    sum(i) over (rows between 0 preceding and 2 following)
1    6
2    9
3    12
4    9
5    5
select i, sum(i) over (order by i rows between 2 preceding and 2 following) from wf02 limit 3;
i    sum(i) over (order by i rows between 2 preceding and 2 following)
1    6
2    10
3    15
select i, sum(i * 20) over (rows between 2 preceding and 2 following) from wf02 order by i desc limit 3;
i    sum(i * 20) over (rows between 2 preceding and 2 following)
5    240
4    280
3    300
select i, avg(i) over (rows between 2 preceding and 2 following) from wf02;
i    avg(i) over (rows between 2 preceding and 2 following)
1    2.0
2    2.5
3    3.0
4    3.5
5    4.0
select i, avg(i + 100) over (rows between 2 preceding and 2 following) from wf02;
i    avg(i + 100) over (rows between 2 preceding and 2 following)
1    102.0
2    102.5
3    103.0
4    103.5
5    104.0
select i, sum(i) over (rows between 1 preceding and 2 following) from wf02;
i    sum(i) over (rows between 1 preceding and 2 following)
1    6
2    10
3    14
4    12
5    9
drop table wf02;
drop table if exists og01;
create table og01(i int, j int, k int);
insert into og01 values (1,1,1);
insert into og01 values (1,4,1);
insert into og01 values (1,2,1);
insert into og01 values (1,4,1);
insert into og01 values (1,1,2);
insert into og01 values (1,4,2);
insert into og01 values (1,2,2);
insert into og01 values (1,4,2);
insert into og01 values (1,1,3);
insert into og01 values (1,4,3);
insert into og01 values (1,2,3);
insert into og01 values (1,4,3);
insert into og01 values (1,1,4);
insert into og01 values (1,4,4);
insert into og01 values (1,2,4);
insert into og01 values (1,4,4);
select * from og01;
i    j    k
1    1    1
1    4    1
1    2    1
1    4    1
1    1    2
1    4    2
1    2    2
1    4    2
1    1    3
1    4    3
1    2    3
1    4    3
1    1    4
1    4    4
1    2    4
1    4    4
select k, sum(k) over (rows unbounded preceding) wf from og01;
k    wf
1    1
1    2
1    3
1    4
2    6
2    8
2    10
2    12
3    15
3    18
3    21
3    24
4    28
4    32
4    36
4    40
select k, min(i), sum(j), sum(k) over (rows unbounded preceding) wf from og01 group by (k);
k    min(i)    sum(j)    wf
1    1    11    1
2    1    11    3
3    1    11    6
4    1    11    10
select k, min(i), sum(j), sum(k) over (rows unbounded preceding) wf from og01 group by (k) order by wf desc;
k    min(i)    sum(j)    wf
4    1    11    10
3    1    11    6
2    1    11    3
1    1    11    1
select k, sum(k) over (rows unbounded preceding) foo from og01 group by (k);
k    foo
1    1
2    3
3    6
4    10
select k, avg(distinct j), sum(k) over (rows unbounded preceding) foo from og01 group by (k);
k    avg(distinct j)    foo
1    2.3333333333333335    1
2    2.3333333333333335    3
3    2.3333333333333335    6
4    2.3333333333333335    10
select k, sum(k+1) over (rows unbounded preceding) foo from og01 group by (k);
k    foo
1    2
2    5
3    9
4    14
select k, sum(k+1) over (order by k desc rows unbounded preceding) foo from og01 group by (k);
k    foo
4    5
3    9
2    12
1    14
drop table og01;
drop table if exists og02;
create table og02 (id integer, sex char(1));
insert into og02 values (1, 'm');
insert into og02 values (2, 'f');
insert into og02 values (3, 'f');
insert into og02 values (4, 'f');
insert into og02 values (5, 'm');
insert into og02 values (10, null);
insert into og02 values (11, null);
select * from og02;
id    sex
1    m
2    f
3    f
4    f
5    m
10    null
11    null
drop table if exists og03;
create table og03(c char(1));
insert into og03 values ('m');
select * from og03;
c
m
select sex, avg(id), row_number() over (partition by sex) from og02
group by sex order by sex desc;
sex    avg(id)    row_number() over (partition by sex)
m    3.0    1
f    3.0    1
null    10.5    1
select sex, avg(id), row_number() over (partition by sex) from og02
group by sex order by sex desc;
sex    avg(id)    row_number() over (partition by sex)
m    3.0    1
f    3.0    1
null    10.5    1
select sex, avg(id), sum(avg(id) + 10) over (rows unbounded preceding) from og02
group by sex order by sex desc;
sex    avg(id)    sum(avg(id) + 10) over (rows unbounded preceding)
m    3.0    13.0
f    3.0    26.0
null    10.5    46.5
select sex, avg(id), row_number() over (partition by sex) from og02
group by sex having sex='m' or sex is null order by sex desc;
sex    avg(id)    row_number() over (partition by sex)
m    3.0    1
null    10.5    1
select sex, avg(id), sum(avg(id)) over (rows unbounded preceding) from og02
group by sex having sex='m' or sex='f' or sex is null
order by sex desc;
sex    avg(id)    sum(avg(id)) over (rows unbounded preceding)
m    3.0    3.0
f    3.0    6.0
null    10.5    16.5
select sex, avg(id), row_number() over (partition by sex) from og02
group by sex having sex=(select c from og03 limit 1) or sex is null
order by sex desc;
sex    avg(id)    row_number() over (partition by sex)
m    3.0    1
null    10.5    1
select sex, avg(id), sum(avg(id)) over (rows unbounded preceding) from og02
group by sex having sex=(select c from og03 limit 1) or sex='f' or sex is null
order by sex desc;
sex    avg(id)    sum(avg(id)) over (rows unbounded preceding)
m    3.0    3.0
f    3.0    6.0
null    10.5    16.5
select sex, avg(id), sum(avg(id)) over (order by sex rows unbounded preceding) from og02
group by sex
order by sex desc;
sex    avg(id)    sum(avg(id)) over (order by sex rows unbounded preceding)
m    3.0    16.5
f    3.0    13.5
null    10.5    10.5
select sex, avg(id), sum(avg(id)) over (order by sex rows unbounded preceding) from og02
group by sex having sex=(select c from og03 limit 1) or sex='f' or sex is null
order by sex desc;
sex    avg(id)    sum(avg(id)) over (order by sex rows unbounded preceding)
m    3.0    16.5
f    3.0    13.5
null    10.5    10.5
drop table og02;
drop table og03;
drop table if exists date02;
create table date02(col1 date,col2 datetime, col3 time, col4 timestamp);
insert into date02 values ('2002-06-09','1997-01-13 00:00:00','12:00:59','2023-05-16 00:12:12');
insert into date02 values ('2002-06-09','2020-02-20 00:00:00','11:12:12','2023-05-18 12:12:12');
insert into date02 values ('2002-06-10','1997-01-13 00:00:00','12:00:59','2023-05-16 00:12:12');
insert into date02 values ('2002-06-09','2020-02-20 00:00:00','11:12:12','2023-05-16 00:12:12');
insert into date02 values ('2015-08-01',null,null,'2023-05-18 12:12:12');
insert into date02 values ('2002-06-09',null,'01:01:01',null);
insert into date02 values ('2015-08-01','1990-01-01 01:02:03',null,null);
select * from date02;
col1    col2    col3    col4
2002-06-09    1997-01-13 00:00:00    12:00:59    2023-05-16 00:12:12
2002-06-09    2020-02-20 00:00:00    11:12:12    2023-05-18 12:12:12
2002-06-10    1997-01-13 00:00:00    12:00:59    2023-05-16 00:12:12
2002-06-09    2020-02-20 00:00:00    11:12:12    2023-05-16 00:12:12
2015-08-01    null    null    2023-05-18 12:12:12
2002-06-09    null    01:01:01    null
2015-08-01    1990-01-01 01:02:03    null    null
select dense_rank() over (partition by col1 order by date_format(col1,'%m-%d-%Y')) from date02;
SQL parser error: Window '<unnamed window>' with RANGE N PRECEDING/FOLLOWING frame requires exactly one ORDER BY expression, of numeric or temporal type
select max(col2) over (partition by col3 order by date(col2) desc) from date02;
max(col2) over (partition by col3 order by date(col2) desc)
1990-01-01 01:02:03
1990-01-01 01:02:03
null
2020-02-20 00:00:00
2020-02-20 00:00:00
1997-01-13 00:00:00
1997-01-13 00:00:00
select rank() over (order by col1 range interval 2 day preceding) from date02;
rank() over (order by col1 range interval(2, day) preceding)
1
1
1
1
5
6
6
select max(col3) over (order by date_add(col2,interval 2 minute) rows  between 2 preceding and 1 following) from date02;
max(col3) over (order by date_add(col2, interval(2, minute)) rows between 2 preceding and 1 following)
01:01:01
01:01:01
12:00:59
12:00:59
12:00:59
12:00:59
12:00:59
select min(col3) over (partition by col4 order by date_sub(col2,interval 2 minute) rows  between 2 preceding and 1 following) from date02;
min(col3) over (partition by col4 order by date_sub(col2, interval(2, minute)) rows between 2 preceding and 1 following)
01:01:01
01:01:01
12:00:59
11:12:12
11:12:12
11:12:12
11:12:12
select max(col3) over (order by year(col2) rows  between current row and unbounded following) from date02;
max(col3) over (order by year(col2) rows between current row and unbounded following)
12:00:59
12:00:59
12:00:59
12:00:59
12:00:59
11:12:12
11:12:12
select dense_rank() over (order by month(col3)) from date02;
dense_rank() over (order by month(col3))
1
1
2
2
2
2
2
drop table date02;
drop table if exists dense_rank01;
create table dense_rank01 (id integer, sex char(1));
insert into dense_rank01 values (1, 'm');
insert into dense_rank01 values (2, 'f');
insert into dense_rank01 values (3, 'f');
insert into dense_rank01 values (4, 'f');
insert into dense_rank01 values (5, 'm');
select * from dense_rank01;
id    sex
1    m
2    f
3    f
4    f
5    m
drop table if exists dense_rank02;
create table dense_rank02 (user_id integer not null, date date);
insert into dense_rank02 values (1, '2002-06-09');
insert into dense_rank02 values (2, '2002-06-09');
insert into dense_rank02 values (1, '2002-06-09');
insert into dense_rank02 values (3, '2002-06-09');
insert into dense_rank02 values (4, '2002-06-09');
insert into dense_rank02 values (4, '2002-06-09');
insert into dense_rank02 values (5, '2002-06-09');
select * from dense_rank02;
user_id    date
1    2002-06-09
2    2002-06-09
1    2002-06-09
3    2002-06-09
4    2002-06-09
4    2002-06-09
5    2002-06-09
select rank() over (order by user_id) r from dense_rank02;
r
1
1
3
4
5
5
7
select dense_rank() over (order by user_id) r from dense_rank02;
r
1
1
2
3
4
4
5
select rank() over () r from dense_rank02;
r
1
1
1
1
1
1
1
select dense_rank() over () r from dense_rank02;
r
1
1
1
1
1
1
1
select id, sex, rank() over (order by sex rows unbounded preceding) from dense_rank01 order by id;
id    sex    rank() over (order by sex rows unbounded preceding)
1    m    4
2    f    1
3    f    1
4    f    1
5    m    4
select id, sex, dense_rank() over (order by sex rows unbounded preceding) from dense_rank01 order by id;
id    sex    dense_rank() over (order by sex rows unbounded preceding)
1    m    2
2    f    1
3    f    1
4    f    1
5    m    2
select sex, rank() over (order by sex desc rows unbounded preceding) `rank`, avg(distinct id) as uids from dense_rank01 u, dense_rank02
where dense_rank02.user_id = u.id group by sex order by sex;
sex    rank    uids
f    2    3.0
m    1    3.0
select sex, dense_rank() over (order by sex desc rows unbounded preceding) `rank`, avg(distinct id) as uids from dense_rank01 u, dense_rank02
where dense_rank02.user_id = u.id group by sex order by sex;
sex    rank    uids
f    2    3.0
m    1    3.0
select  sex, avg(id) as uids, rank() over (order by avg(id)) `rank` from dense_rank01 u, dense_rank02
where dense_rank02.user_id = u.id group by sex;
sex    uids    rank
m    2.3333333333333335    1
f    3.25    2
select  sex, avg(id) as uids, dense_rank() over (order by avg(id)) `rank` from dense_rank01 u, dense_rank02
where dense_rank02.user_id = u.id group by sex;
sex    uids    rank
m    2.3333333333333335    1
f    3.25    2
select  sex, avg(distinct id) as uids, rank() over (order by avg(distinct id) desc) `rank` from dense_rank01 u, dense_rank02
where dense_rank02.user_id = u.id group by sex
order by sex;
sex    uids    rank
f    3.0    1
m    3.0    1
select  sex, avg(distinct id) as uids, dense_rank() over (order by avg(distinct id) desc) `p_rank` from dense_rank01 u, dense_rank02
where dense_rank02.user_id = u.id group by sex
order by sex;
sex    uids    p_rank
f    3.0    1
m    3.0    1
select  sex, avg(id) as uids, rank() over (order by avg(id) desc) `rank` from dense_rank01 u, dense_rank02
where dense_rank02.user_id = u.id group by sex
order by `rank` desc;
sex    uids    rank
m    2.3333333333333335    2
f    3.25    1
select  sex, avg(id) as uids, dense_rank() over (order by avg(id) desc) `p_rank`
from dense_rank01 u, dense_rank02
where dense_rank02.user_id = u.id group by sex
order by `p_rank` desc;
sex    uids    p_rank
m    2.3333333333333335    2
f    3.25    1
insert into dense_rank01 values (10, null);
insert into dense_rank01 values (11, null);
select id, sex, rank() over (order by sex rows unbounded preceding)from dense_rank01 order by id;
id    sex    rank() over (order by sex rows unbounded preceding)
1    m    6
2    f    3
3    f    3
4    f    3
5    m    6
10    null    1
11    null    1
select id, sex, dense_rank() over (order by sex rows unbounded preceding) from dense_rank01 order by id;
id    sex    dense_rank() over (order by sex rows unbounded preceding)
1    m    3
2    f    2
3    f    2
4    f    2
5    m    3
10    null    1
11    null    1
select id, sex, rank() over (order by sex desc rows unbounded preceding) from dense_rank01 order by id;
id    sex    rank() over (order by sex desc rows unbounded preceding)
1    m    1
2    f    3
3    f    3
4    f    3
5    m    1
10    null    6
11    null    6
select id value,
sum(id) over (rows unbounded preceding)
from dense_rank01 left join dense_rank02 on dense_rank02.user_id = dense_rank01.id;
value    sum(id) over (rows unbounded preceding)
1    1
1    2
2    4
3    7
4    11
4    15
5    20
10    30
11    41
select id value,
sum(id) over (rows unbounded preceding)
from dense_rank01 right join dense_rank02 on dense_rank02.user_id = dense_rank01.id;
value    sum(id) over (rows unbounded preceding)
1    1
2    3
1    4
3    7
4    11
4    15
5    20
select id value,
sum(id) over (rows unbounded preceding)
from dense_rank01 inner join dense_rank02 on dense_rank02.user_id = dense_rank01.id;
value    sum(id) over (rows unbounded preceding)
1    1
1    2
2    4
3    7
4    11
4    15
5    20
select id value,
sum(id) over (partition by id order by id rows unbounded preceding)
from dense_rank01 natural join dense_rank02;
value    sum(id) over (partition by id order by id rows unbounded preceding)
1    1
1    2
1    3
1    4
1    5
1    6
1    7
2    2
2    4
2    6
2    8
2    10
2    12
2    14
3    3
3    6
3    9
3    12
3    15
3    18
3    21
4    4
4    8
4    12
4    16
4    20
4    24
4    28
5    5
5    10
5    15
5    20
5    25
5    30
5    35
10    10
10    20
10    30
10    40
10    50
10    60
10    70
11    11
11    22
11    33
11    44
11    55
11    66
11    77
select id value,
sum(id) over (partition by id order by id rows unbounded preceding)
from dense_rank01 full join dense_rank02;
value    sum(id) over (partition by id order by id rows unbounded preceding)
1    1
1    2
1    3
1    4
1    5
1    6
1    7
2    2
2    4
2    6
2    8
2    10
2    12
2    14
3    3
3    6
3    9
3    12
3    15
3    18
3    21
4    4
4    8
4    12
4    16
4    20
4    24
4    28
5    5
5    10
5    15
5    20
5    25
5    30
5    35
10    10
10    20
10    30
10    40
10    50
10    60
10    70
11    11
11    22
11    33
11    44
11    55
11    66
11    77
select sex, avg(id), rank() over (order by avg(id) desc) from dense_rank01 group by sex order by sex;
sex    avg(id)    rank() over (order by avg(id) desc)
null    10.5    1
f    3.0    2
m    3.0    2
select sex, dense_rank() over (order by avg(id) desc) from dense_rank01 group by sex order by sex;
sex    dense_rank() over (order by avg(id) desc)
null    1
f    2
m    2
select sex, rank() over (order by avg(id) desc) from dense_rank01 group by sex order by sex;
sex    rank() over (order by avg(id) desc)
null    1
f    2
m    2
select rank() over (order by avg(id)) from dense_rank01;
rank() over (order by avg(id))
1
select dense_rank() over (order by avg(id)) from dense_rank01;
dense_rank() over (order by avg(id))
1
select avg(id), rank() over (order by avg(id)) from dense_rank01;
avg(id)    rank() over (order by avg(id))
5.142857142857143    1
select avg(id), dense_rank() over (order by avg(id)) from dense_rank01;
avg(id)    dense_rank() over (order by avg(id))
5.142857142857143    1
select avg(id), sum(avg(id)) over (order by avg(id) rows unbounded preceding) from dense_rank01;
avg(id)    sum(avg(id)) over (order by avg(id) rows unbounded preceding)
5.142857142857143    5.142857142857143
select sex, id, rank() over (partition by sex order by id desc) from dense_rank01;
sex    id    rank() over (partition by sex order by id desc)
null    11    1
null    10    2
f    4    1
f    3    2
f    2    3
m    5    1
m    1    2
select sex, id, dense_rank() over (partition by sex order by id desc) from dense_rank01;
sex    id    dense_rank() over (partition by sex order by id desc)
null    11    1
null    10    2
f    4    1
f    3    2
f    2    3
m    5    1
m    1    2
select sex, id, rank() over (partition by sex order by id asc) from dense_rank01;
sex    id    rank() over (partition by sex order by id asc)
null    10    1
null    11    2
f    2    1
f    3    2
f    4    3
m    1    1
m    5    2
select sex, id, dense_rank() over (partition by sex order by id asc) from dense_rank01;
sex    id    dense_rank() over (partition by sex order by id asc)
null    10    1
null    11    2
f    2    1
f    3    2
f    4    3
m    1    1
m    5    2
select sex, id, sum(id) over (partition by sex order by id asc rows unbounded preceding) summ,
rank() over (partition by sex order by id asc rows unbounded preceding) `rank` from dense_rank01;
sex    id    summ    rank
null    10    10    1
null    11    21    2
f    2    2    1
f    3    5    2
f    4    9    3
m    1    1    1
m    5    6    2
select sex, id, sum(id) over (partition by sex order by id asc rows unbounded preceding) summ,
dense_rank() over (partition by sex order by id asc rows unbounded preceding) `d_rank` from dense_rank01;
sex    id    summ    d_rank
null    10    10    1
null    11    21    2
f    2    2    1
f    3    5    2
f    4    9    3
m    1    1    1
m    5    6    2
select sex, id, sum(id) over (partition by sex order by id asc rows unbounded preceding) summ,
rank() over (partition by sex order by id asc rows unbounded preceding) `rank` from dense_rank01
order by summ;
sex    id    summ    rank
m    1    1    1
f    2    2    1
f    3    5    2
m    5    6    2
f    4    9    3
null    10    10    1
null    11    21    2
select sex, id, sum(id) over (partition by sex order by id asc rows unbounded preceding) summ,
dense_rank() over (partition by sex order by id asc rows unbounded preceding) `p_rank` from dense_rank01
order by summ;
sex    id    summ    p_rank
m    1    1    1
f    2    2    1
f    3    5    2
m    5    6    2
f    4    9    3
null    10    10    1
null    11    21    2
select sex, avg(distinct id),rank() over (order by uids desc) `uids`
from dense_rank01 u, dense_rank01 where dense_rank01.user_id = u.id group by sex
order by sex;
invalid input: ambiguouse column reference to 'sex'
select sex, avg(distinct id),rank() over (order by uids desc) `uids`
from dense_rank01 u, dense_rank02 where dense_rank02.user_id = u.id
group by sex order by sex;
invalid input: column uids does not exist
drop table dense_rank01;
drop table dense_rank02;
drop table if exists dense_rank03;
create table dense_rank03(d decimal(10,2), date date);
insert into dense_rank03 values (10.4, '2002-06-09');
insert into dense_rank03 values (20.5, '2002-06-09');
insert into dense_rank03 values (10.4, '2002-06-10');
insert into dense_rank03 values (3,    '2002-06-09');
insert into dense_rank03 values (40.2, '2015-08-01');
insert into dense_rank03 values (40.2, '2002-06-09');
insert into dense_rank03 values (5,    '2015-08-01');
select * from dense_rank03;
d    date
10.40    2002-06-09
20.50    2002-06-09
10.40    2002-06-10
3.00    2002-06-09
40.20    2015-08-01
40.20    2002-06-09
5.00    2015-08-01
select * from (select rank() over (order by d) as `rank`, d, date from dense_rank03) alias order by `rank`, d, date;
rank    d    date
1    3.00    2002-06-09
2    5.00    2015-08-01
3    10.40    2002-06-09
3    10.40    2002-06-10
5    20.50    2002-06-09
6    40.20    2002-06-09
6    40.20    2015-08-01
select * from (select dense_rank() over (order by d) as `d_rank`, d, date from dense_rank03) alias order by `d_rank`, d, date;
d_rank    d    date
1    3.00    2002-06-09
2    5.00    2015-08-01
3    10.40    2002-06-09
3    10.40    2002-06-10
4    20.50    2002-06-09
5    40.20    2002-06-09
5    40.20    2015-08-01
select * from (select rank() over (order by date) as `rank`, date, d from dense_rank03) alias order by `rank`, d desc;
rank    date    d
1    2002-06-09    40.20
1    2002-06-09    20.50
1    2002-06-09    10.40
1    2002-06-09    3.00
5    2002-06-10    10.40
6    2015-08-01    40.20
6    2015-08-01    5.00
select * from (select dense_rank() over (order by date) as `p_rank`, date, d from dense_rank03) alias order by `p_rank`, d desc;
p_rank    date    d
1    2002-06-09    40.20
1    2002-06-09    20.50
1    2002-06-09    10.40
1    2002-06-09    3.00
2    2002-06-10    10.40
3    2015-08-01    40.20
3    2015-08-01    5.00
drop table dense_rank03;
drop table if exists rank01;
create table rank01(i int, j int, k int);
insert into rank01 values (1,1,1);
insert into rank01 values (1,1,2);
insert into rank01 values (1,1,2);
insert into rank01 values (1,2,1);
insert into rank01 values (1,2,2);
insert into rank01 values (2,1,1);
insert into rank01 values (2,1,1);
insert into rank01 values (2,1,2);
insert into rank01 values (2,2,1);
insert into rank01 values (2,2,2);
select * from rank01;
i    j    k
1    1    1
1    1    2
1    1    2
1    2    1
1    2    2
2    1    1
2    1    1
2    1    2
2    2    1
2    2    2
select *, rank() over (order by i,j,k) as o_ijk,
rank() over (order by j) as o_j,
rank() over (order by k,j) as o_kj from rank01 order by i,j,k;
i    j    k    o_ijk    o_j    o_kj
1    1    1    1    1    1
1    1    2    2    1    6
1    1    2    2    1    6
1    2    1    4    7    4
1    2    2    5    7    9
2    1    1    6    1    1
2    1    1    6    1    1
2    1    2    8    1    6
2    2    1    9    7    4
2    2    2    10    7    9
drop table rank01;
drop table if exists row_number01;
create table row_number01 (id integer, sex char(1));
insert into row_number01 values (1, 'm');
insert into row_number01 values (2, 'f');
insert into row_number01 values (3, 'f');
insert into row_number01 values (4, 'f');
insert into row_number01 values (5, 'm');
select * from row_number01;
id    sex
1    m
2    f
3    f
4    f
5    m
drop table if exists row_number02;
create table row_number02 (user_id integer not null, date date);
insert into row_number02 values (1, '2002-06-09');
insert into row_number02 values (2, '2002-06-09');
insert into row_number02 values (1, '2002-06-09');
insert into row_number02 values (3, '2002-06-09');
insert into row_number02 values (4, '2002-06-09');
insert into row_number02 values (4, '2002-06-09');
insert into row_number02 values (5, '2002-06-09');
select * from row_number02;
user_id    date
1    2002-06-09
2    2002-06-09
1    2002-06-09
3    2002-06-09
4    2002-06-09
4    2002-06-09
5    2002-06-09
select user_id, row_number() over (partition by user_id) from row_number02 row_number01;
user_id    row_number() over (partition by user_id)
1    1
1    2
2    1
3    1
4    1
4    2
5    1
select sex, id, date, row_number() over (partition by date order by id) as row_no, rank() over (partition by date order by id) as `rank` from row_number01,row_number02
where row_number01.id=row_number02.user_id;
sex    id    date    row_no    rank
m    1    2002-06-09    1    1
m    1    2002-06-09    2    1
f    2    2002-06-09    3    3
f    3    2002-06-09    4    4
f    4    2002-06-09    5    5
f    4    2002-06-09    6    5
m    5    2002-06-09    7    7
select  date,id, rank() over (partition by date order by id) as `rank` from row_number01,row_number02;
date    id    rank
2002-06-09    1    1
2002-06-09    1    1
2002-06-09    1    1
2002-06-09    1    1
2002-06-09    1    1
2002-06-09    1    1
2002-06-09    1    1
2002-06-09    2    8
2002-06-09    2    8
2002-06-09    2    8
2002-06-09    2    8
2002-06-09    2    8
2002-06-09    2    8
2002-06-09    2    8
2002-06-09    3    15
2002-06-09    3    15
2002-06-09    3    15
2002-06-09    3    15
2002-06-09    3    15
2002-06-09    3    15
2002-06-09    3    15
2002-06-09    4    22
2002-06-09    4    22
2002-06-09    4    22
2002-06-09    4    22
2002-06-09    4    22
2002-06-09    4    22
2002-06-09    4    22
2002-06-09    5    29
2002-06-09    5    29
2002-06-09    5    29
2002-06-09    5    29
2002-06-09    5    29
2002-06-09    5    29
2002-06-09    5    29
select * from (select date,id, rank() over (partition by date order by id) as `rank` from row_number01,row_number02) alias;
date    id    rank
2002-06-09    1    1
2002-06-09    1    1
2002-06-09    1    1
2002-06-09    1    1
2002-06-09    1    1
2002-06-09    1    1
2002-06-09    1    1
2002-06-09    2    8
2002-06-09    2    8
2002-06-09    2    8
2002-06-09    2    8
2002-06-09    2    8
2002-06-09    2    8
2002-06-09    2    8
2002-06-09    3    15
2002-06-09    3    15
2002-06-09    3    15
2002-06-09    3    15
2002-06-09    3    15
2002-06-09    3    15
2002-06-09    3    15
2002-06-09    4    22
2002-06-09    4    22
2002-06-09    4    22
2002-06-09    4    22
2002-06-09    4    22
2002-06-09    4    22
2002-06-09    4    22
2002-06-09    5    29
2002-06-09    5    29
2002-06-09    5    29
2002-06-09    5    29
2002-06-09    5    29
2002-06-09    5    29
2002-06-09    5    29
select * from (select date,id, dense_rank() over (partition by date order by id) as `p_rank` from row_number01,row_number02) t;
date    id    p_rank
2002-06-09    1    1
2002-06-09    1    1
2002-06-09    1    1
2002-06-09    1    1
2002-06-09    1    1
2002-06-09    1    1
2002-06-09    1    1
2002-06-09    2    2
2002-06-09    2    2
2002-06-09    2    2
2002-06-09    2    2
2002-06-09    2    2
2002-06-09    2    2
2002-06-09    2    2
2002-06-09    3    3
2002-06-09    3    3
2002-06-09    3    3
2002-06-09    3    3
2002-06-09    3    3
2002-06-09    3    3
2002-06-09    3    3
2002-06-09    4    4
2002-06-09    4    4
2002-06-09    4    4
2002-06-09    4    4
2002-06-09    4    4
2002-06-09    4    4
2002-06-09    4    4
2002-06-09    5    5
2002-06-09    5    5
2002-06-09    5    5
2002-06-09    5    5
2002-06-09    5    5
2002-06-09    5    5
2002-06-09    5    5
select row_number01.*, rank() over (order by sex rows unbounded preceding), sum(id) over (order by sex,id rows unbounded preceding) from row_number01;
id    sex    rank() over (order by sex rows unbounded preceding)    sum(id) over (order by sex, id rows unbounded preceding)
2    f    1    2
3    f    1    5
4    f    1    9
1    m    4    10
5    m    4    15
select row_number01.*, dense_rank() over (order by sex rows unbounded preceding), sum(id) over (order by sex,id rows unbounded preceding) from row_number01;
id    sex    dense_rank() over (order by sex rows unbounded preceding)    sum(id) over (order by sex, id rows unbounded preceding)
2    f    1    2
3    f    1    5
4    f    1    9
1    m    2    10
5    m    2    15
select * from (select row_number01.*, sum(id) over (rows unbounded preceding), rank() over (order by sex rows unbounded preceding) from row_number01) alias order by id;
id    sex    sum(id) over (rows unbounded preceding)    rank() over (order by sex rows unbounded preceding)
1    m    1    4
2    f    3    1
3    f    6    1
4    f    10    1
5    m    15    4
select * from (select row_number01.*, sum(id) over (rows unbounded preceding), dense_rank() over (order by sex rows unbounded preceding) from row_number01) alias order by id;
id    sex    sum(id) over (rows unbounded preceding)    dense_rank() over (order by sex rows unbounded preceding)
1    m    1    2
2    f    3    1
3    f    6    1
4    f    10    1
5    m    15    2
select row_number01.*, sum(id) over (order by id rows unbounded preceding),
rank() over (order by sex,id rows between 1 preceding and 2 following),
row_number() over (order by sex,id rows unbounded preceding)
from row_number01;
id    sex    sum(id) over (order by id rows unbounded preceding)    rank() over (order by sex, id rows between 1 preceding and 2 following)    row_number() over (order by sex, id rows unbounded preceding)
2    f    3    1    1
3    f    6    2    2
4    f    10    3    3
1    m    1    4    4
5    m    15    5    5
select row_number01.*, sum(id) over (order by id rows unbounded preceding),
dense_rank() over (order by sex,id rows between 1 preceding and 2 following)
from row_number01;
id    sex    sum(id) over (order by id rows unbounded preceding)    dense_rank() over (order by sex, id rows between 1 preceding and 2 following)
2    f    3    1
3    f    6    2
4    f    10    3
1    m    1    4
5    m    15    5
select sum(id),avg(id) over (partition by sex), count(id) over (partition by sex) from row_number01;
SQL syntax error: column "row_number01.id" must appear in the GROUP BY clause or be used in an aggregate function
select * from (select id, sum(id) over (partition by sex), count(*) over (partition by sex), sex from row_number01 alias order by id) alias;
id    sum(id) over (partition by sex)    count(*) over (partition by sex)    sex
1    6    2    m
2    9    3    f
3    9    3    f
4    9    3    f
5    6    2    m
select sum(id) over (partition by sex) from row_number01;
sum(id) over (partition by sex)
9
9
9
6
6
select id, sum(id) over (partition by sex order by id
rows between 2 preceding and 1 following), sex from row_number01;
id    sum(id) over (partition by sex order by id rows between 2 preceding and 1 following)    sex
2    5    f
3    9    f
4    9    f
1    6    m
5    6    m
create view v as select id, sum(id) over (partition by sex order by id rows between 2 preceding and 1 following), sex from row_number01;
show create view v;
View    Create View    character_set_client    collation_connection
v    create view v as select id, sum(id) over (partition by sex order by id rows between 2 preceding and 1 following), sex from row_number01;    utf8mb4    utf8mb4_general_ci
select * from v;
id    sum(id) over (partition by sex order by id rows between 2 preceding and 1 following)    sex
2    5    f
3    9    f
4    9    f
1    6    m
5    6    m
drop view v;
drop table row_number01;
drop table row_number02;
drop table if exists wf01;
create table wf01(d float);
insert into wf01 values (10);
insert into wf01 values (1);
insert into wf01 values (2);
insert into wf01 values (3);
insert into wf01 values (4);
insert into wf01 values (5);
insert into wf01 values (6);
insert into wf01 values (7);
insert into wf01 values (8);
insert into wf01 values (9);
select * from wf01;
d
10.0
1.0
2.0
3.0
4.0
5.0
6.0
7.0
8.0
9.0
select d, sum(d) over (order by d range between 2 preceding and current row),
avg(d) over (order by d range between 2 preceding and current row) from wf01;
d    sum(d) over (order by d range between 2 preceding and current row)    avg(d) over (order by d range between 2 preceding and current row)
1.0    1.0    1.0
2.0    3.0    1.5
3.0    6.0    2.0
4.0    9.0    3.0
5.0    12.0    4.0
6.0    15.0    5.0
7.0    18.0    6.0
8.0    21.0    7.0
9.0    24.0    8.0
10.0    27.0    9.0
select d, sum(d) over (order by d range between 1 preceding and 2 following),
avg(d) over (order by d range between 2 preceding and 3 following) from wf01;
d    sum(d) over (order by d range between 1 preceding and 2 following)    avg(d) over (order by d range between 2 preceding and 3 following)
1.0    6.0    2.5
2.0    10.0    3.0
3.0    14.0    3.5
4.0    18.0    4.5
5.0    22.0    5.5
6.0    26.0    6.5
7.0    30.0    7.5
8.0    34.0    8.0
9.0    27.0    8.5
10.0    19.0    9.0
select d, sum(d) over (order by d range between 2 preceding and current row),
avg(d) over (order by d range between 1 preceding and current row) from wf01;
d    sum(d) over (order by d range between 2 preceding and current row)    avg(d) over (order by d range between 1 preceding and current row)
1.0    1.0    1.0
2.0    3.0    1.5
3.0    6.0    2.5
4.0    9.0    3.5
5.0    12.0    4.5
6.0    15.0    5.5
7.0    18.0    6.5
8.0    21.0    7.5
9.0    24.0    8.5
10.0    27.0    9.5
select d, sum(d) over (order by d range between 2 preceding and 2 following),
avg(d) over (order by d range between 1 preceding and 2 following) from wf01;
d    sum(d) over (order by d range between 2 preceding and 2 following)    avg(d) over (order by d range between 1 preceding and 2 following)
1.0    6.0    2.0
2.0    10.0    2.5
3.0    15.0    3.5
4.0    20.0    4.5
5.0    25.0    5.5
6.0    30.0    6.5
7.0    35.0    7.5
8.0    40.0    8.5
9.0    34.0    9.0
10.0    27.0    9.5
select d, sum(d) over (order by d range between current row and 0 following),
avg(d) over (order by d range between current row and 2 following) from wf01;
d    sum(d) over (order by d range between current row and 0 following)    avg(d) over (order by d range between current row and 2 following)
1.0    1.0    2.0
2.0    2.0    3.0
3.0    3.0    4.0
4.0    4.0    5.0
5.0    5.0    6.0
6.0    6.0    7.0
7.0    7.0    8.0
8.0    8.0    9.0
9.0    9.0    9.5
10.0    10.0    10.0
select d, sum(d) over (order by d range between 2 preceding and 2 following),
avg(d) over (order by d range between current row and 2 following) from wf01;
d    sum(d) over (order by d range between 2 preceding and 2 following)    avg(d) over (order by d range between current row and 2 following)
1.0    6.0    2.0
2.0    10.0    3.0
3.0    15.0    4.0
4.0    20.0    5.0
5.0    25.0    6.0
6.0    30.0    7.0
7.0    35.0    8.0
8.0    40.0    9.0
9.0    34.0    9.5
10.0    27.0    10.0
insert into wf01 select * from wf01;
select * from wf01;
d
10.0
1.0
2.0
3.0
4.0
5.0
6.0
7.0
8.0
9.0
10.0
1.0
2.0
3.0
4.0
5.0
6.0
7.0
8.0
9.0
select d, sum(d) over (order by d range between 2 preceding and current row),
avg(d) over (order by d range between 1 preceding and current row) from wf01;
d    sum(d) over (order by d range between 2 preceding and current row)    avg(d) over (order by d range between 1 preceding and current row)
1.0    2.0    1.0
1.0    2.0    1.0
2.0    6.0    1.5
2.0    6.0    1.5
3.0    12.0    2.5
3.0    12.0    2.5
4.0    18.0    3.5
4.0    18.0    3.5
5.0    24.0    4.5
5.0    24.0    4.5
6.0    30.0    5.5
6.0    30.0    5.5
7.0    36.0    6.5
7.0    36.0    6.5
8.0    42.0    7.5
8.0    42.0    7.5
9.0    48.0    8.5
9.0    48.0    8.5
10.0    54.0    9.5
10.0    54.0    9.5
select d, sum(d) over (order by d range between 2 preceding and 2 following),
avg(d) over (order by d range between 3 preceding and 2 following) from wf01;
d    sum(d) over (order by d range between 2 preceding and 2 following)    avg(d) over (order by d range between 3 preceding and 2 following)
1.0    12.0    2.0
1.0    12.0    2.0
2.0    20.0    2.5
2.0    20.0    2.5
3.0    30.0    3.0
3.0    30.0    3.0
4.0    40.0    3.5
4.0    40.0    3.5
5.0    50.0    4.5
5.0    50.0    4.5
6.0    60.0    5.5
6.0    60.0    5.5
7.0    70.0    6.5
7.0    70.0    6.5
8.0    80.0    7.5
8.0    80.0    7.5
9.0    68.0    8.0
9.0    68.0    8.0
10.0    54.0    8.5
10.0    54.0    8.5
select d, sum(d) over (order by d range between 2 preceding and current row),
avg(d) over (order by d range between 2 preceding and current row) from wf01;
d    sum(d) over (order by d range between 2 preceding and current row)    avg(d) over (order by d range between 2 preceding and current row)
1.0    2.0    1.0
1.0    2.0    1.0
2.0    6.0    1.5
2.0    6.0    1.5
3.0    12.0    2.0
3.0    12.0    2.0
4.0    18.0    3.0
4.0    18.0    3.0
5.0    24.0    4.0
5.0    24.0    4.0
6.0    30.0    5.0
6.0    30.0    5.0
7.0    36.0    6.0
7.0    36.0    6.0
8.0    42.0    7.0
8.0    42.0    7.0
9.0    48.0    8.0
9.0    48.0    8.0
10.0    54.0    9.0
10.0    54.0    9.0
select d, sum(d) over (order by d range between 1 preceding and 2 following),
avg(d) over (order by d range between 2 preceding and 2 following) from wf01;
d    sum(d) over (order by d range between 1 preceding and 2 following)    avg(d) over (order by d range between 2 preceding and 2 following)
1.0    12.0    2.0
1.0    12.0    2.0
2.0    20.0    2.5
2.0    20.0    2.5
3.0    28.0    3.0
3.0    28.0    3.0
4.0    36.0    4.0
4.0    36.0    4.0
5.0    44.0    5.0
5.0    44.0    5.0
6.0    52.0    6.0
6.0    52.0    6.0
7.0    60.0    7.0
7.0    60.0    7.0
8.0    68.0    8.0
8.0    68.0    8.0
9.0    54.0    8.5
9.0    54.0    8.5
10.0    38.0    9.0
10.0    38.0    9.0
select d, sum(d) over (order by d range between current row and 2 following),
avg(d) over (order by d range between current row and 2 following) from wf01;
d    sum(d) over (order by d range between current row and 2 following)    avg(d) over (order by d range between current row and 2 following)
1.0    12.0    2.0
1.0    12.0    2.0
2.0    18.0    3.0
2.0    18.0    3.0
3.0    24.0    4.0
3.0    24.0    4.0
4.0    30.0    5.0
4.0    30.0    5.0
5.0    36.0    6.0
5.0    36.0    6.0
6.0    42.0    7.0
6.0    42.0    7.0
7.0    48.0    8.0
7.0    48.0    8.0
8.0    54.0    9.0
8.0    54.0    9.0
9.0    38.0    9.5
9.0    38.0    9.5
10.0    20.0    10.0
10.0    20.0    10.0
select d, sum(d) over (order by d range between current row and 2 following),
avg(d) over (order by d range between current row and 2 following) from wf01;
d    sum(d) over (order by d range between current row and 2 following)    avg(d) over (order by d range between current row and 2 following)
1.0    12.0    2.0
1.0    12.0    2.0
2.0    18.0    3.0
2.0    18.0    3.0
3.0    24.0    4.0
3.0    24.0    4.0
4.0    30.0    5.0
4.0    30.0    5.0
5.0    36.0    6.0
5.0    36.0    6.0
6.0    42.0    7.0
6.0    42.0    7.0
7.0    48.0    8.0
7.0    48.0    8.0
8.0    54.0    9.0
8.0    54.0    9.0
9.0    38.0    9.5
9.0    38.0    9.5
10.0    20.0    10.0
10.0    20.0    10.0
drop table wf01;
drop table if exists wf02;
create table wf02 (id integer, sex varchar(10));
insert into wf02 values (1, 'moolol');
insert into wf02 values (2, 'fdhsajhd');
insert into wf02 values (3, 'fdhsajhd');
insert into wf02 values (4, 'fdhsajhd');
insert into wf02 values (5, 'moolol');
insert into wf02 values (10, null);
insert into wf02 values (11, null);
select * from wf02;
id    sex
1    moolol
2    fdhsajhd
3    fdhsajhd
4    fdhsajhd
5    moolol
10    null
11    null
select row_number() over (partition by sex order by id rows between unbounded preceding and unbounded following), id,
sum(id) over (partition by sex order by id rows between 1 following and 2 following), sex from wf02;
row_number() over (partition by sex order by id rows between unbounded preceding and unbounded following)    id    sum(id) over (partition by sex order by id rows between 1 following and 2 following)    sex
1    10    11    null
2    11    null    null
1    2    7    fdhsajhd
2    3    4    fdhsajhd
3    4    null    fdhsajhd
1    1    5    moolol
2    5    null    moolol
select row_number() over (partition by sex order by id rows between 1 following and 2 following), sum(id) over (partition by sex order by id
rows between 1 following and 2 following) from wf02;
row_number() over (partition by sex order by id rows between 1 following and 2 following)    sum(id) over (partition by sex order by id rows between 1 following and 2 following)
1    11
2    null
1    7
2    4
3    null
1    5
2    null
insert into wf02 values (10, null);
select rank() over (partition by sex order by id), id, sum(id) over (partition by sex order by id) as abc, sex from wf02;
rank() over (partition by sex order by id)    id    abc    sex
1    10    20    null
1    10    20    null
3    11    31    null
1    2    2    fdhsajhd
2    3    5    fdhsajhd
3    4    9    fdhsajhd
1    1    1    moolol
2    5    6    moolol
select id, sex, sum(id) over (partition by sex order by id rows between 2 preceding and 1 following) as a from wf02;
id    sex    a
10    null    20
10    null    31
11    null    31
2    fdhsajhd    5
3    fdhsajhd    9
4    fdhsajhd    9
1    moolol    6
5    moolol    6
select id, sex, sum(id) over (partition by sex order by id rows between 2 preceding and 1 following) as a,
row_number() over (partition by sex order by id rows between 2 preceding and 1 following) as b,
rank() over (partition by sex order by id rows between 2 preceding and 1 following) as c  from wf02;
id    sex    a    b    c
10    null    20    1    1
10    null    31    2    1
11    null    31    3    3
2    fdhsajhd    5    1    1
3    fdhsajhd    9    2    2
4    fdhsajhd    9    3    3
1    moolol    6    1    1
5    moolol    6    2    2
select id, sex, sum(id) over (partition by sex order by id rows between 2 preceding and 1 following) as a,
row_number() over (partition by sex order by id rows between 2 preceding and 1 following) as b from wf02;
id    sex    a    b
10    null    20    1
10    null    31    2
11    null    31    3
2    fdhsajhd    5    1
3    fdhsajhd    9    2
4    fdhsajhd    9    3
1    moolol    6    1
5    moolol    6    2
select row_number() over (partition by sex order by id rows between unbounded preceding and unbounded following), id,
sex from wf02;
row_number() over (partition by sex order by id rows between unbounded preceding and unbounded following)    id    sex
1    10    null
2    10    null
3    11    null
1    2    fdhsajhd
2    3    fdhsajhd
3    4    fdhsajhd
1    1    moolol
2    5    moolol
select row_number() over (partition by sex order by id rows between 1 preceding and 2 following), sum(id) over (partition by sex order by id
rows between 1 preceding and 2 following) from wf02;
row_number() over (partition by sex order by id rows between 1 preceding and 2 following)    sum(id) over (partition by sex order by id rows between 1 preceding and 2 following)
1    31
2    31
3    21
1    9
2    9
3    7
1    6
2    6
drop table wf02;
drop table if exists cte01;
drop table if exists cte02;
create table cte01 (
customer_id varchar(1),
order_date date,
product_id integer
);
insert into cte01 values('a', '2021-01-01', '1');
insert into cte01 values('a', '2021-01-01', '2');
insert into cte01 values('a', '2021-01-07', '2');
insert into cte01 values('a', '2021-01-10', '3');
insert into cte01 values('a', '2021-01-11', '3');
insert into cte01 values('a', '2021-01-11', '3');
insert into cte01 values('b', '2021-01-01', '2');
insert into cte01 values('b', '2021-01-02', '2');
insert into cte01 values('b', '2021-01-04', '1');
insert into cte01 values('b', '2021-01-11', '1');
insert into cte01 values('b', '2021-01-16', '3');
insert into cte01 values('b', '2021-02-01', '3');
insert into cte01 values('c', '2021-01-01', '3');
insert into cte01 values('c', '2021-01-01', '3');
insert into cte01 values('c', '2021-01-07', '3');
select * from cte01;
customer_id    order_date    product_id
a    2021-01-01    1
a    2021-01-01    2
a    2021-01-07    2
a    2021-01-10    3
a    2021-01-11    3
a    2021-01-11    3
b    2021-01-01    2
b    2021-01-02    2
b    2021-01-04    1
b    2021-01-11    1
b    2021-01-16    3
b    2021-02-01    3
c    2021-01-01    3
c    2021-01-01    3
c    2021-01-07    3
create table cte02 (
product_id integer,
product_name varchar(5),
price integer
);
insert into cte02 values('1', 'sushi', '10');
insert into cte02 values('2', 'curry', '15');
insert into cte02 values('3', 'ramen', '12');
select * from cte02;
product_id    product_name    price
1    sushi    10
2    curry    15
3    ramen    12
with test as (
select cte01.customer_id, cte01.order_date, cte02.product_name, dense_rank() over (partition by cte01.customer_id
order by cte01.order_date) as `rank` from cte01 inner join cte02 on cte01.product_id = cte02.product_id)
select customer_id, product_name from test where `rank` = 1 group by customer_id, product_name;
customer_id    product_name
a    sushi
a    curry
b    curry
c    ramen
with test as (
select cte01.customer_id, cte01.order_date, cte02.product_name, rank() over (partition by cte01.customer_id
order by cte01.order_date) as `rank` from cte01 inner join cte02 on cte01.product_id = cte02.product_id)
select customer_id, product_name from test where `rank` = 1 group by customer_id, product_name;
customer_id    product_name
a    sushi
a    curry
b    curry
c    ramen
with test as (
select cte01.customer_id, cte01.order_date, cte02.product_name, row_number() over (partition by cte01.customer_id
order by cte01.order_date) as `rank` from cte01 inner join cte02 on cte01.product_id = cte02.product_id)
select customer_id, product_name from test where `rank` = 1 group by customer_id, product_name;
customer_id    product_name
a    sushi
b    curry
c    ramen
drop table cte01;
drop table cte02;
drop table if exists td;
create table td(d int);
insert into td(d) values (10),(1),(2),(3),(4),(5),(6),(7),(8),(9);
insert into td(d) select d+10 from td;
insert into td(d) select d+20 from td;
insert into td(d) select d+30 from td;
insert into td(d) select d+40 from td;
insert into td(d) select d+50 from td;
insert into td(d) select d+60 from td;
insert into td(d) select d+70 from td;
insert into td(d) select d+80 from td;
insert into td(d) select d+90 from td;
insert into td(d) select d+100 from td;
insert into td(d) select d+200 from td;
insert into td(d) select d+300 from td;
insert into td(d) select d+400 from td;
insert into td(d) select d+500 from td;
insert into td(d) select d+600 from td;
insert into td(d) select d+700 from td;
insert into td(d) select d+800 from td;
insert into td(d) select d+900 from td;
insert into td(d) select d+1000 from td;
insert into td(d) select d+2000 from td;
insert into td(d) select d+3000 from td;
select count(*) from td;
count(*)
20971520
select avg(d) over (order by d range between 2 preceding and 2 following) from td limit 10;
[unknown result because it is related to issue#13008]
select sum(d) over (order by d rows between 10 preceding and 10 following) from td limit 10;
sum(d) over (order by d rows between 10 preceding and 10 following)
66
78
91
105
120
136
153
171
190
210
select d,min(d) over (partition by d%7 order by d rows  between 2 preceding and 1 following) from td limit 10;
d    min(d) over (partition by d % 7 order by d rows between 2 preceding and 1 following)
7    7
14    7
21    7
28    14
35    21
35    28
42    35
42    35
49    42
49    42
drop table td;
drop table if exists `c`;
create table `c` (
`pk` int(11) not null auto_increment,
`col_int` int(11) not null,
`col_date` date not null,
`col_datetime` datetime not null,
`col_time` time not null,
`col_varchar` varchar(15) not null,
primary key (`pk`),
unique key `col_date_key` (`col_date`),
unique key `col_date_key_2` (`col_date`,`col_datetime`),
key `col_int_key_1` (`col_int`,`col_date`),
key `col_int_key_2` (`col_int`,`col_time`),
key `col_int_key_3` (`col_int`,`col_datetime`)
);
insert into `c` (`pk`, `col_int`, `col_date`, `col_datetime`, `col_time`, `col_varchar`)
values (1, 9, '2009-11-04', '2006-10-12 19:52:02', '18:19:40', 'a'),
(2, 4, '2009-05-21', '2005-09-13 00:00:00', '07:45:25', 'tef'),
(3, 0, '1900-01-01', '2002-09-03 04:42:41', '13:17:14', 'efqsd'),
(4, 149, '2000-11-05', '2007-02-08 07:29:31', '10:38:21', 'fqsdk'),
(5, 8, '2001-06-12', '2000-11-07 15:28:31', '23:04:47', 'qsdksji'),
(6, 8, '2002-06-07', '2007-09-19 02:35:12', '07:33:31', 'sdks'),
(7, 5, '2008-06-02', '1900-01-01 00:00:00', '14:41:02', 'dksjij'),
(8, 7, '2000-07-26', '2007-11-27 00:19:33', '23:30:25', 'sjijcsz'),
(9, 8, '2008-09-16', '2004-12-17 11:22:46', '06:11:14', 'i'),
(10, 104, '2002-03-06', '2007-02-04 13:09:16', '22:24:50', 'jcszxw'),
(11, 1, '2004-01-10', '2008-03-19 08:36:41', '00:03:00', 'csz'),
(12, 4, '2002-02-21', '2008-03-27 03:09:30', '06:52:39', 'szxwbjj'),
(13, 8, '2004-07-01', '2001-10-20 06:42:39', '08:49:41', 'xwb'),
(14, 7, '2008-08-13', '2002-04-05 00:00:00', '05:52:03', 'wbjjvvk'),
(15, 8, '2008-12-18', '1900-01-01 00:00:00', '00:00:00', 'bj'),
(16, 6, '2002-08-03', '2008-04-14 09:20:36', '00:00:00', 'jjvvk'),
(17, 97, '2001-06-11', '2002-11-07 00:00:00', '13:30:55', 'j');
drop table if exists `dd`;
create table `dd` (
`pk` int(11) not null auto_increment,
`col_int` int(11) not null,
`col_date` date not null,
`col_datetime` datetime not null,
`col_time` time not null,
`col_varchar` varchar(15) not null,
primary key (`pk`),
unique key `col_date_key` (`col_date`),
unique key `col_date_key_1` (`col_date`,`col_time`,`col_datetime`),
key `col_int_key` (`col_int`),
key `col_time_key` (`col_time`),
key `col_datetime_key` (`col_datetime`),
key `col_int_key_5` (`col_int`),
key `col_int_key_6` (`col_int`),
key `col_int_key_7` (`col_int`,`col_date`),
key `col_int_key_8` (`col_int`,`col_time`),
key `col_int_key_9` (`col_int`,`col_datetime`));
insert into `dd` (`pk`, `col_int`, `col_date`, `col_datetime`, `col_time`, `col_varchar`)
values (10,7,'1992-01-01','2000-02-09 06:46:23','03:56:10','i'),
(11,5,'2008-12-11','2004-03-07 18:05:11','00:00:00','jrll'),
(12,7,'2005-11-18','2001-01-18 08:29:29','20:17:57','rllqunt'),
(13,9,'2009-02-08','2005-10-25 00:00:00','08:09:49','l'),
(14,3,'2002-05-26','2009-09-01 10:19:05','09:40:42','lq'),
(15,66,'2002-03-10','2002-09-06 04:43:02','08:28:55','quntp'),
(16,3,'2003-07-07','2006-04-07 00:00:00','20:12:00','untppi'),
(17,95,'2006-06-22','2004-05-08 00:00:00','18:50:24','ntppirz'),
(18,7,'2004-01-21','2000-01-23 03:34:04','17:01:57','tppirzd'),
(19,5,'2001-05-01','2005-12-26 20:42:01','15:11:27','pirzdp'),
(20,8,'2008-12-15','1900-01-01 00:00:00','05:49:51','irzd'),
(21,3,'2000-08-28','2003-02-28 16:30:52','14:58:44','zdphpdu'),
(22,96,'2008-06-08','2005-09-15 03:55:22','02:20:01','dp'),
(23,9,'2002-04-02','2001-01-08 10:44:10','19:03:57','p'),
(24,3,'2005-03-04','2001-03-23 00:00:00','00:27:13','h'),
(25,8,'2001-01-21','2004-03-02 00:00:00','13:39:32','pduhwq'),
(26,8,'2006-10-05','1900-01-01 00:00:00','08:06:08','uhwqh'),
(27,4,'2001-12-26','2006-10-24 05:59:20','16:15:34','hwqh'),
(28,7,'1900-01-01','2005-06-14 00:00:00','12:04:50','wqhnsm'),
(29,6,'2007-12-02','2001-08-25 03:00:31','00:00:00','qh'),
(30,4,'2009-02-06','2001-06-14 19:13:14','06:00:42','nsmu'),
(31,9,'2007-01-15','2006-12-18 07:54:16','11:18:35','smujjj'),
(32,5,'2004-11-07','2000-09-18 04:53:37','16:20:06','muj'),
(33,1,'2003-12-07','2002-08-18 04:47:11','01:41:35','jj'),
(34,1,'2008-09-07','2000-10-14 16:58:18','17:42:13','jbld'),
(35,5,'2005-03-08','2008-11-22 16:40:01','00:59:59','bldnki'),
(36,181,'2006-11-18','1900-01-01 00:00:00','00:00:00','nkiws'),
(37,5,'2007-01-26','2008-01-21 00:00:00','02:16:04','kiwsr'),
(38,1,'2003-08-24','1900-01-01 00:00:00','00:00:00','iwsrsx'),
(39,162,'2001-12-01','2008-05-17 00:00:00','14:34:36','srsxnd'),
(40,8,'2003-07-02','2000-06-07 00:00:00','23:02:05','r'),
(41,2,'2007-03-01','2009-01-03 12:22:04','00:00:00','sxndo'),
(42,7,'2009-08-04','2009-10-05 04:15:15','00:00:00','xndolp'),
(43,119,'2000-05-03','2002-02-17 23:12:12','23:23:35','olpujd'),
(44,3,'2001-05-18','2008-03-27 11:51:54','11:26:20','lp'),
(45,119,'2004-02-22','1900-01-01 00:00:00','00:00:00','pu'),
(46,8,'2002-07-15','2008-08-24 21:36:28','12:51:37','dnozrhh'),
(47,2,'2008-04-22','2005-01-12 08:50:22','20:55:45','no'),
(48,4,'2006-06-01','2000-04-20 00:00:00','13:02:05','ozrhhcx'),
(49,8,'2009-09-12','2000-02-16 03:57:05','17:04:35','zrhhcxs'),
(50,6,'2009-01-06','1900-01-01 00:00:00','05:15:45','rhhcxsx'),
(51,6,'2008-07-13','2002-04-27 14:13:27','00:00:00','hhcxsxw'),
(52,8,'2002-03-15','2008-01-17 20:30:57','07:09:22','hcxsxw'),
(53,6,'2007-10-14','2006-10-11 22:48:02','06:11:59','cxs'),
(54,1,'2008-07-23','2005-09-11 07:19:40','03:05:06','x'),
(55,1,'2007-05-22','2002-11-24 16:25:27','10:10:42','s'),
(56,6,'2008-01-08','2005-06-09 01:11:17','06:03:27','w'),
(57,9,'2006-10-18','1900-01-01 00:00:00','00:00:00','uju'),
(58,7,'2000-07-22','1900-01-01 00:00:00','00:00:00','ju'),
(59,6,'2004-07-21','2009-10-25 16:05:29','11:04:39','ul'),
(60,2,'2001-10-03','2002-06-13 11:41:55','10:20:49','lpjd'),
(61,8,'2002-08-17','1900-01-01 00:00:00','00:00:00','jdz'),
(62,0,'2009-11-10','2000-05-04 05:15:19','00:00:00','zvkpaij'),
(63,6,'2005-06-26','2002-08-19 00:00:00','09:21:09','vkpaij'),
(64,6,'2000-06-04','2002-03-22 04:37:00','00:00:00','kp'),
(65,9,'2005-10-02','2009-01-10 09:03:59','04:56:37','paiju'),
(66,0,'2009-11-13','1900-01-01 00:00:00','00:00:00','aij'),
(67,0,'2006-11-26','2001-09-21 00:00:00','08:16:28','ijurspr'),
(68,6,'2007-09-24','2003-08-27 05:11:09','19:55:11','j'),
(69,0,'2009-01-24','1900-01-01 00:00:00','11:25:58','urspr'),
(70,5,'2001-06-22','2005-07-07 00:00:00','14:38:03','rsprn'),
(71,4,'2006-07-18','2000-07-16 06:17:20','15:32:00','sprnw'),
(72,5,'2009-05-12','2007-07-26 00:00:00','09:25:59','rnwgrp');
drop table if exists `e`;
create table `e` (
`pk` int(11) not null auto_increment,
`col_int` int(11) not null,
`col_date` date not null,
`col_datetime` datetime not null,
`col_time` time not null,
`col_varchar` varchar(15) not null,
primary key (`pk`),
unique key `col_date` (`col_date`,`col_time`,`col_datetime`),
unique key `col_varchar_key_2` (`col_varchar`(5)),
unique key `col_int_key_1` (`col_int`,`col_varchar`(5)),
unique key `col_int_key_2` (`col_int`,`col_varchar`(5),`col_date`,`col_time`,`col_datetime`),
key `col_int_key` (`col_int`),
key `col_time_key` (`col_time`),
key `col_datetime_key` (`col_datetime`),
key `col_int_key_7` (`col_int`,`col_date`),
key `col_int_key_8` (`col_int`,`col_time`),
key `col_int_key_9` (`col_int`,`col_datetime`));
insert into `e` (`pk`, `col_int`, `col_date`, `col_datetime`, `col_time`, `col_varchar`)
values (1, 202, '1997-01-13', '2008-11-25 09:14:26', '07:23:12', 'en'),
(2, 4, '2005-07-10', '2005-03-15 22:48:25', '23:28:02', 'nchyhu'),
(3, 7, '2005-06-09', '2006-11-22 00:00:00', '10:51:23', 'chy'),
(4, 2, '2007-12-08', '2007-11-01 09:02:50', '01:12:13', 'hyhu'),
(5, 7, '2007-12-22', '2001-04-08 00:00:00', '06:34:46', 'yhuoo'),
(6, 1, '1900-01-01', '2001-11-27 19:47:15', '10:16:53', 'huoo'),
(7, 7, '2002-10-07', '2009-09-15 04:42:26', '07:07:58', 'uoowit'),
(8, 7, '2005-01-09', '2001-08-12 02:07:43', '06:15:07', 'oo'),
(9, 3, '2007-10-12', '2009-05-09 17:06:27', '00:00:00', 'ow'),
(10, 3, '2004-01-22', '1900-01-01 00:00:00', '06:41:21', 'wityzg'),
(11, 5, '2007-10-11', '2000-03-03 23:40:04', '22:28:00', 'ityzg'),
(12, 8, '2001-08-19', '2005-10-18 17:41:54', '04:47:49', 'tyz'),
(13, 9, '2001-02-12', '2000-03-23 23:22:54', '03:24:01', 'gktbkjr'),
(14, 0, '2000-07-14', '2007-01-25 11:00:51', '14:37:06', 'ktbkjrk'),
(15, 4, '2007-11-14', '2003-12-21 10:46:23', '05:53:49', 'tbkjrkm'),
(16, 9, '2004-01-25', '2003-09-02 01:45:27', '00:00:00', 'k'),
(17, 2, '2003-12-15', '2009-05-28 08:03:38', '23:41:09', 'j'),
(18, 4, '2002-01-25', '2003-10-23 18:22:15', '09:26:45', 'kmqm'),
(19, 0, '2009-09-08', '2001-12-28 00:00:00', '17:04:03', 'mq'),
(20, 7, '2008-03-15', '2005-05-06 19:42:18', '02:15:17', 'mkn'),
(21, 0, '2005-11-10', '2003-03-05 00:00:00', '00:00:00', 'knbtoe'),
(22, 1, '2008-11-12', '2001-12-26 16:47:05', '19:09:36', 'n'),
(23, 2, '2007-11-22', '2003-02-09 00:00:00', '07:55:11', 'btoer'),
(24, 4, '2002-04-25', '2008-10-13 00:00:00', '11:24:50', 'toe'),
(25, 4, '2004-02-14', '2001-07-16 16:05:48', '08:46:01', 'oervq'),
(26, 4, '2004-04-21', '2004-04-23 14:00:22', '20:16:19', 'rvqlzs'),
(27, 3, '2003-03-26', '2002-11-10 08:15:17', '13:03:14', 'vqlzs'),
(28, 0, '2007-06-18', '2006-06-24 03:59:58', '06:11:33', 'qlzsva'),
(29, 5, '2006-12-09', '2008-04-08 18:06:18', '09:40:31', 'lzsvasu'),
(30, 8, '2001-10-01', '2000-10-12 16:32:35', '03:34:01', 'zsvasu'),
(31, 6, '2001-01-07', '2005-09-11 10:09:54', '00:00:00', 'svas'),
(32, 0, '2007-11-02', '2009-09-10 01:44:18', '12:23:27', 'v'),
(33, 9, '2005-07-23', '2002-10-20 21:55:02', '05:12:10', 'surqdhu'),
(34, 4, '2003-09-13', '2009-11-03 09:54:42', '20:54:06', 'urqdh'),
(35, 165, '2001-05-14', '2002-10-19 00:00:00', '00:00:00', 'rqd'),
(36, 2, '2006-07-04', '2008-10-26 00:00:00', '00:59:06', 'qdhu'),
(37, 6, '2001-08-15', '2002-08-14 14:52:08', '07:22:34', 'dhu'),
(38, 5, '2000-04-27', '2007-06-10 00:00:00', '11:27:19', 'hu4332cjx'),
(39, 9, '2007-10-13', '2002-07-07 04:10:43', '10:03:09', 'uc'),
(40, 214, '2004-02-06', '2007-08-15 13:56:29', '23:00:35', 'cjxd'),
(41, 194, '2008-12-27', '1900-01-01 00:00:00', '11:59:05', 'jx'),
(42, 1, '2002-08-16', '2000-08-11 11:34:38', '21:39:43', 'xdo'),
(43, 220, '2001-06-17', '1900-01-01 00:00:00', '00:00:00', 'oyg'),
(44, 9, '2002-10-16', '2008-12-07 23:41:33', '00:00:00', 'gx'),
(45, 248, '2008-04-06', '1900-01-01 00:00:00', '12:32:24', 'x'),
(46, 0, '2000-07-08', '2001-12-27 19:38:22', '00:00:00', 'vgqmw'),
(47, 0, '2005-03-16', '1900-01-01 00:00:00', '06:22:01', 'qmwcid'),
(48, 4, '2002-06-19', '2007-03-08 02:43:50', '07:00:21', 'mwc'),
(49, 3, '2005-11-25', '2001-11-14 17:21:32', '17:59:20', 'wcidtu'),
(50, 7, '2007-07-08', '1900-01-01 00:00:00', '01:58:05', 'cidtum'),
(51, 7, '2000-06-20', '2004-07-20 11:05:12', '22:24:24', 'dtumxwc'),
(52, 5, '2006-03-28', '2008-08-15 08:28:18', '04:22:26', 'tumxwc'),
(53, 1, '2004-03-05', '1900-01-01 00:00:00', '00:00:00', 'umxwcf'),
(54, 0, '2009-05-10', '2004-01-28 15:16:19', '11:46:32', 'mxwcft'),
(55, 67, '2004-04-18', '2001-06-23 00:00:00', '20:12:09', 'xwcfted'),
(56, 204, '2008-01-10', '2009-02-12 07:59:52', '13:58:17', 'wc'),
(57, 9, '2000-07-12', '2004-12-10 07:32:31', '04:04:48', 'ftedx'),
(58, 5, '2001-06-16', '2006-09-06 12:15:44', '10:14:16', 't'),
(59, 6, '2000-02-20', '2003-09-13 14:23:06', '21:22:20', 'dx'),
(60, 6, '2001-02-07', '2004-01-18 00:00:00', '10:15:21', 'xqyciak'),
(61, 1, '2008-12-24', '2004-04-02 07:16:01', '16:30:10', 'qy'),
(62, 1, '2009-12-14', '2000-01-04 14:51:24', '03:57:54', 'y'),
(63, 5, '2008-03-07', '2001-06-24 00:00:00', '06:41:05', 'ciak'),
(64, 4, '2005-01-19', '2001-06-02 03:41:12', '00:00:00', 'iakh'),
(65, 4, '2003-02-10', '1900-01-01 00:00:00', '08:51:25', 'ak'),
(66, 9, '2005-12-25', '2007-07-13 14:26:05', '14:32:55', 'hxptz'),
(67, 4, '2003-10-13', '2008-03-20 21:14:50', '00:21:31', 'xptzfp'),
(68, 3, '2001-08-03', '1900-01-01 00:00:00', '00:00:00', 'ptzfpjw'),
(69, 0, '2006-04-01', '1900-01-01 00:00:00', '11:26:05', 'tzfpjwr'),
(70, 2, '2003-12-27', '2002-05-09 18:39:28', '05:28:11', 'wrgeo'),
(71, 100, '2001-10-25', '2006-01-13 00:00:00', '04:35:51', 'r'),
(72, 37, '2006-09-12', '2003-12-04 05:20:00', '06:10:43', 'geo'),
(73, 5, '2003-06-04', '2003-07-21 11:43:03', '17:26:47', 'eozxnby'),
(74, 6, '2009-11-13', '2006-12-24 00:00:00', '22:34:54', 'oz'),
(75, 1, '2006-08-13', '2005-08-25 00:00:00', '21:27:38', 'zxnbyc'),
(76, 7, '2007-07-09', '2003-10-16 01:16:30', '03:14:14', 'xnbycjz'),
(77, 6, '2000-01-07', '2001-06-22 00:00:00', '00:00:00', 'nby'),
(78, 5, '2004-12-21', '2004-09-01 18:53:04', '16:06:30', 'bycj'),
(79, 0, '2003-10-14', '2000-04-13 05:21:03', '19:04:51', 'ycjzxie');
with test01 as (
select `e`.col_int, `c`.col_varchar, row_number() over (partition by `e`.col_int
order by `e`.col_date) as `rank` from `e` inner join `c` on `c`.col_int = `e`.col_int)
select col_int as a from test where `rank` = 1 group by col_int;
SQL parser error: table "test" does not exist
with test02 as (
select `dd`.col_int, `c`.col_datetime, rank() over (partition by `dd`.col_int
order by `dd`.col_date) as `rank` from `dd` left join `c` on `c`.col_int = `dd`.col_int)
select col_int as a from test02 where `rank` = 1 group by col_int;
a
0
1
2
3
4
5
6
7
8
9
66
95
96
119
162
181
with test03 as (
select `dd`.col_int, `e`.col_varchar, dense_rank() over (partition by `dd`.col_int
order by `dd`.col_datetime) as `rank` from `dd` left join `e` on `e`.col_int = `dd`.col_int)
select col_int as a from test03 where `rank` = 1 group by col_int;
a
0
1
2
3
4
5
6
7
8
9
66
95
96
119
162
181
select `c`.col_int,`c`.col_datetime, `dd`.col_time, row_number() over (partition by `c`.col_int
order by `dd`.col_time) as `rank` from `dd` left join `c` on `c`.col_int = `dd`.col_int;
col_int    col_datetime    col_time    rank
null    null    00:00:00    1
null    null    00:00:00    2
null    null    00:00:00    3
null    null    00:27:13    4
null    null    02:20:01    5
null    null    08:28:55    6
null    null    09:40:42    7
null    null    10:20:49    8
null    null    11:26:20    9
null    null    14:34:36    10
null    null    14:58:44    11
null    null    18:50:24    12
null    null    20:12:00    13
null    null    20:55:45    14
null    null    23:23:35    15
0    2002-09-03 04:42:41    00:00:00    1
0    2002-09-03 04:42:41    00:00:00    2
0    2002-09-03 04:42:41    08:16:28    3
0    2002-09-03 04:42:41    11:25:58    4
1    2008-03-19 08:36:41    00:00:00    1
1    2008-03-19 08:36:41    01:41:35    2
1    2008-03-19 08:36:41    03:05:06    3
1    2008-03-19 08:36:41    10:10:42    4
1    2008-03-19 08:36:41    17:42:13    5
4    2008-03-27 03:09:30    06:00:42    1
4    2005-09-13 00:00:00    06:00:42    2
4    2005-09-13 00:00:00    13:02:05    3
4    2008-03-27 03:09:30    13:02:05    4
4    2008-03-27 03:09:30    15:32:00    5
4    2005-09-13 00:00:00    15:32:00    6
4    2005-09-13 00:00:00    16:15:34    7
4    2008-03-27 03:09:30    16:15:34    8
5    1900-01-01 00:00:00    00:00:00    1
5    1900-01-01 00:00:00    00:59:59    2
5    1900-01-01 00:00:00    02:16:04    3
5    1900-01-01 00:00:00    09:25:59    4
5    1900-01-01 00:00:00    14:38:03    5
5    1900-01-01 00:00:00    15:11:27    6
5    1900-01-01 00:00:00    16:20:06    7
6    2008-04-14 09:20:36    00:00:00    1
6    2008-04-14 09:20:36    00:00:00    2
6    2008-04-14 09:20:36    00:00:00    3
6    2008-04-14 09:20:36    05:15:45    4
6    2008-04-14 09:20:36    06:03:27    5
6    2008-04-14 09:20:36    06:11:59    6
6    2008-04-14 09:20:36    09:21:09    7
6    2008-04-14 09:20:36    11:04:39    8
6    2008-04-14 09:20:36    19:55:11    9
7    2007-11-27 00:19:33    00:00:00    1
7    2007-11-27 00:19:33    00:00:00    2
7    2002-04-05 00:00:00    00:00:00    3
7    2002-04-05 00:00:00    00:00:00    4
7    2007-11-27 00:19:33    03:56:10    5
7    2002-04-05 00:00:00    03:56:10    6
7    2007-11-27 00:19:33    12:04:50    7
7    2002-04-05 00:00:00    12:04:50    8
7    2007-11-27 00:19:33    17:01:57    9
7    2002-04-05 00:00:00    17:01:57    10
7    2002-04-05 00:00:00    20:17:57    11
7    2007-11-27 00:19:33    20:17:57    12
8    1900-01-01 00:00:00    00:00:00    1
8    2000-11-07 15:28:31    00:00:00    2
8    2007-09-19 02:35:12    00:00:00    3
8    2004-12-17 11:22:46    00:00:00    4
8    2001-10-20 06:42:39    00:00:00    5
8    2000-11-07 15:28:31    05:49:51    6
8    2004-12-17 11:22:46    05:49:51    7
8    2007-09-19 02:35:12    05:49:51    8
8    1900-01-01 00:00:00    05:49:51    9
8    2001-10-20 06:42:39    05:49:51    10
8    1900-01-01 00:00:00    07:09:22    11
8    2001-10-20 06:42:39    07:09:22    12
8    2004-12-17 11:22:46    07:09:22    13
8    2007-09-19 02:35:12    07:09:22    14
8    2000-11-07 15:28:31    07:09:22    15
8    1900-01-01 00:00:00    08:06:08    16
8    2004-12-17 11:22:46    08:06:08    17
8    2000-11-07 15:28:31    08:06:08    18
8    2007-09-19 02:35:12    08:06:08    19
8    2001-10-20 06:42:39    08:06:08    20
8    2004-12-17 11:22:46    12:51:37    21
8    2000-11-07 15:28:31    12:51:37    22
8    2007-09-19 02:35:12    12:51:37    23
8    2001-10-20 06:42:39    12:51:37    24
8    1900-01-01 00:00:00    12:51:37    25
8    2000-11-07 15:28:31    13:39:32    26
8    2007-09-19 02:35:12    13:39:32    27
8    1900-01-01 00:00:00    13:39:32    28
8    2001-10-20 06:42:39    13:39:32    29
8    2004-12-17 11:22:46    13:39:32    30
8    1900-01-01 00:00:00    17:04:35    31
8    2001-10-20 06:42:39    17:04:35    32
8    2004-12-17 11:22:46    17:04:35    33
8    2007-09-19 02:35:12    17:04:35    34
8    2000-11-07 15:28:31    17:04:35    35
8    2007-09-19 02:35:12    23:02:05    36
8    1900-01-01 00:00:00    23:02:05    37
8    2004-12-17 11:22:46    23:02:05    38
8    2000-11-07 15:28:31    23:02:05    39
8    2001-10-20 06:42:39    23:02:05    40
9    2006-10-12 19:52:02    00:00:00    1
9    2006-10-12 19:52:02    04:56:37    2
9    2006-10-12 19:52:02    08:09:49    3
9    2006-10-12 19:52:02    11:18:35    4
9    2006-10-12 19:52:02    19:03:57    5
select `c`.col_int,`c`.col_datetime, `dd`.col_time, sum(`c`.col_int) over (partition by `c`.col_int
order by `dd`.col_time) as `rank` from `dd` left join `c` on `c`.col_int = `dd`.col_int;
col_int    col_datetime    col_time    rank
null    null    00:00:00    null
null    null    00:00:00    null
null    null    00:00:00    null
null    null    00:27:13    null
null    null    02:20:01    null
null    null    08:28:55    null
null    null    09:40:42    null
null    null    10:20:49    null
null    null    11:26:20    null
null    null    14:34:36    null
null    null    14:58:44    null
null    null    18:50:24    null
null    null    20:12:00    null
null    null    20:55:45    null
null    null    23:23:35    null
0    2002-09-03 04:42:41    00:00:00    0
0    2002-09-03 04:42:41    00:00:00    0
0    2002-09-03 04:42:41    08:16:28    0
0    2002-09-03 04:42:41    11:25:58    0
1    2008-03-19 08:36:41    00:00:00    1
1    2008-03-19 08:36:41    01:41:35    2
1    2008-03-19 08:36:41    03:05:06    3
1    2008-03-19 08:36:41    10:10:42    4
1    2008-03-19 08:36:41    17:42:13    5
4    2008-03-27 03:09:30    06:00:42    8
4    2005-09-13 00:00:00    06:00:42    8
4    2005-09-13 00:00:00    13:02:05    16
4    2008-03-27 03:09:30    13:02:05    16
4    2008-03-27 03:09:30    15:32:00    24
4    2005-09-13 00:00:00    15:32:00    24
4    2005-09-13 00:00:00    16:15:34    32
4    2008-03-27 03:09:30    16:15:34    32
5    1900-01-01 00:00:00    00:00:00    5
5    1900-01-01 00:00:00    00:59:59    10
5    1900-01-01 00:00:00    02:16:04    15
5    1900-01-01 00:00:00    09:25:59    20
5    1900-01-01 00:00:00    14:38:03    25
5    1900-01-01 00:00:00    15:11:27    30
5    1900-01-01 00:00:00    16:20:06    35
6    2008-04-14 09:20:36    00:00:00    18
6    2008-04-14 09:20:36    00:00:00    18
6    2008-04-14 09:20:36    00:00:00    18
6    2008-04-14 09:20:36    05:15:45    24
6    2008-04-14 09:20:36    06:03:27    30
6    2008-04-14 09:20:36    06:11:59    36
6    2008-04-14 09:20:36    09:21:09    42
6    2008-04-14 09:20:36    11:04:39    48
6    2008-04-14 09:20:36    19:55:11    54
7    2007-11-27 00:19:33    00:00:00    28
7    2007-11-27 00:19:33    00:00:00    28
7    2002-04-05 00:00:00    00:00:00    28
7    2002-04-05 00:00:00    00:00:00    28
7    2007-11-27 00:19:33    03:56:10    42
7    2002-04-05 00:00:00    03:56:10    42
7    2007-11-27 00:19:33    12:04:50    56
7    2002-04-05 00:00:00    12:04:50    56
7    2007-11-27 00:19:33    17:01:57    70
7    2002-04-05 00:00:00    17:01:57    70
7    2002-04-05 00:00:00    20:17:57    84
7    2007-11-27 00:19:33    20:17:57    84
8    1900-01-01 00:00:00    00:00:00    40
8    2000-11-07 15:28:31    00:00:00    40
8    2007-09-19 02:35:12    00:00:00    40
8    2004-12-17 11:22:46    00:00:00    40
8    2001-10-20 06:42:39    00:00:00    40
8    2000-11-07 15:28:31    05:49:51    80
8    2004-12-17 11:22:46    05:49:51    80
8    2007-09-19 02:35:12    05:49:51    80
8    1900-01-01 00:00:00    05:49:51    80
8    2001-10-20 06:42:39    05:49:51    80
8    1900-01-01 00:00:00    07:09:22    120
8    2001-10-20 06:42:39    07:09:22    120
8    2004-12-17 11:22:46    07:09:22    120
8    2007-09-19 02:35:12    07:09:22    120
8    2000-11-07 15:28:31    07:09:22    120
8    1900-01-01 00:00:00    08:06:08    160
8    2004-12-17 11:22:46    08:06:08    160
8    2000-11-07 15:28:31    08:06:08    160
8    2007-09-19 02:35:12    08:06:08    160
8    2001-10-20 06:42:39    08:06:08    160
8    2004-12-17 11:22:46    12:51:37    200
8    2000-11-07 15:28:31    12:51:37    200
8    2007-09-19 02:35:12    12:51:37    200
8    2001-10-20 06:42:39    12:51:37    200
8    1900-01-01 00:00:00    12:51:37    200
8    2000-11-07 15:28:31    13:39:32    240
8    2007-09-19 02:35:12    13:39:32    240
8    1900-01-01 00:00:00    13:39:32    240
8    2001-10-20 06:42:39    13:39:32    240
8    2004-12-17 11:22:46    13:39:32    240
8    1900-01-01 00:00:00    17:04:35    280
8    2001-10-20 06:42:39    17:04:35    280
8    2004-12-17 11:22:46    17:04:35    280
8    2007-09-19 02:35:12    17:04:35    280
8    2000-11-07 15:28:31    17:04:35    280
8    2007-09-19 02:35:12    23:02:05    320
8    1900-01-01 00:00:00    23:02:05    320
8    2004-12-17 11:22:46    23:02:05    320
8    2000-11-07 15:28:31    23:02:05    320
8    2001-10-20 06:42:39    23:02:05    320
9    2006-10-12 19:52:02    00:00:00    9
9    2006-10-12 19:52:02    04:56:37    18
9    2006-10-12 19:52:02    08:09:49    27
9    2006-10-12 19:52:02    11:18:35    36
9    2006-10-12 19:52:02    19:03:57    45
select `c`.col_int,`c`.col_datetime, `dd`.col_time, avg(`dd`.col_int) over (partition by `c`.col_int
order by `dd`.col_time) as `rank` from `dd` left join `c` on `c`.col_int = `dd`.col_int;
col_int    col_datetime    col_time    rank
null    null    00:00:00    100.66666666666667
null    null    00:00:00    100.66666666666667
null    null    00:00:00    100.66666666666667
null    null    00:27:13    76.25
null    null    02:20:01    80.2
null    null    08:28:55    77.83333333333333
null    null    09:40:42    67.14285714285714
null    null    10:20:49    59.0
null    null    11:26:20    52.77777777777778
null    null    14:34:36    63.7
null    null    14:58:44    58.18181818181818
null    null    18:50:24    61.25
null    null    20:12:00    56.76923076923077
null    null    20:55:45    52.857142857142854
null    null    23:23:35    57.266666666666666
0    2002-09-03 04:42:41    00:00:00    0.0
0    2002-09-03 04:42:41    00:00:00    0.0
0    2002-09-03 04:42:41    08:16:28    0.0
0    2002-09-03 04:42:41    11:25:58    0.0
1    2008-03-19 08:36:41    00:00:00    1.0
1    2008-03-19 08:36:41    01:41:35    1.0
1    2008-03-19 08:36:41    03:05:06    1.0
1    2008-03-19 08:36:41    10:10:42    1.0
1    2008-03-19 08:36:41    17:42:13    1.0
4    2008-03-27 03:09:30    06:00:42    4.0
4    2005-09-13 00:00:00    06:00:42    4.0
4    2005-09-13 00:00:00    13:02:05    4.0
4    2008-03-27 03:09:30    13:02:05    4.0
4    2008-03-27 03:09:30    15:32:00    4.0
4    2005-09-13 00:00:00    15:32:00    4.0
4    2005-09-13 00:00:00    16:15:34    4.0
4    2008-03-27 03:09:30    16:15:34    4.0
5    1900-01-01 00:00:00    00:00:00    5.0
5    1900-01-01 00:00:00    00:59:59    5.0
5    1900-01-01 00:00:00    02:16:04    5.0
5    1900-01-01 00:00:00    09:25:59    5.0
5    1900-01-01 00:00:00    14:38:03    5.0
5    1900-01-01 00:00:00    15:11:27    5.0
5    1900-01-01 00:00:00    16:20:06    5.0
6    2008-04-14 09:20:36    00:00:00    6.0
6    2008-04-14 09:20:36    00:00:00    6.0
6    2008-04-14 09:20:36    00:00:00    6.0
6    2008-04-14 09:20:36    05:15:45    6.0
6    2008-04-14 09:20:36    06:03:27    6.0
6    2008-04-14 09:20:36    06:11:59    6.0
6    2008-04-14 09:20:36    09:21:09    6.0
6    2008-04-14 09:20:36    11:04:39    6.0
6    2008-04-14 09:20:36    19:55:11    6.0
7    2007-11-27 00:19:33    00:00:00    7.0
7    2007-11-27 00:19:33    00:00:00    7.0
7    2002-04-05 00:00:00    00:00:00    7.0
7    2002-04-05 00:00:00    00:00:00    7.0
7    2007-11-27 00:19:33    03:56:10    7.0
7    2002-04-05 00:00:00    03:56:10    7.0
7    2007-11-27 00:19:33    12:04:50    7.0
7    2002-04-05 00:00:00    12:04:50    7.0
7    2007-11-27 00:19:33    17:01:57    7.0
7    2002-04-05 00:00:00    17:01:57    7.0
7    2002-04-05 00:00:00    20:17:57    7.0
7    2007-11-27 00:19:33    20:17:57    7.0
8    1900-01-01 00:00:00    00:00:00    8.0
8    2000-11-07 15:28:31    00:00:00    8.0
8    2007-09-19 02:35:12    00:00:00    8.0
8    2004-12-17 11:22:46    00:00:00    8.0
8    2001-10-20 06:42:39    00:00:00    8.0
8    2000-11-07 15:28:31    05:49:51    8.0
8    2004-12-17 11:22:46    05:49:51    8.0
8    2007-09-19 02:35:12    05:49:51    8.0
8    1900-01-01 00:00:00    05:49:51    8.0
8    2001-10-20 06:42:39    05:49:51    8.0
8    1900-01-01 00:00:00    07:09:22    8.0
8    2001-10-20 06:42:39    07:09:22    8.0
8    2004-12-17 11:22:46    07:09:22    8.0
8    2007-09-19 02:35:12    07:09:22    8.0
8    2000-11-07 15:28:31    07:09:22    8.0
8    1900-01-01 00:00:00    08:06:08    8.0
8    2004-12-17 11:22:46    08:06:08    8.0
8    2000-11-07 15:28:31    08:06:08    8.0
8    2007-09-19 02:35:12    08:06:08    8.0
8    2001-10-20 06:42:39    08:06:08    8.0
8    2004-12-17 11:22:46    12:51:37    8.0
8    2000-11-07 15:28:31    12:51:37    8.0
8    2007-09-19 02:35:12    12:51:37    8.0
8    2001-10-20 06:42:39    12:51:37    8.0
8    1900-01-01 00:00:00    12:51:37    8.0
8    2000-11-07 15:28:31    13:39:32    8.0
8    2007-09-19 02:35:12    13:39:32    8.0
8    1900-01-01 00:00:00    13:39:32    8.0
8    2001-10-20 06:42:39    13:39:32    8.0
8    2004-12-17 11:22:46    13:39:32    8.0
8    1900-01-01 00:00:00    17:04:35    8.0
8    2001-10-20 06:42:39    17:04:35    8.0
8    2004-12-17 11:22:46    17:04:35    8.0
8    2007-09-19 02:35:12    17:04:35    8.0
8    2000-11-07 15:28:31    17:04:35    8.0
8    2007-09-19 02:35:12    23:02:05    8.0
8    1900-01-01 00:00:00    23:02:05    8.0
8    2004-12-17 11:22:46    23:02:05    8.0
8    2000-11-07 15:28:31    23:02:05    8.0
8    2001-10-20 06:42:39    23:02:05    8.0
9    2006-10-12 19:52:02    00:00:00    9.0
9    2006-10-12 19:52:02    04:56:37    9.0
9    2006-10-12 19:52:02    08:09:49    9.0
9    2006-10-12 19:52:02    11:18:35    9.0
9    2006-10-12 19:52:02    19:03:57    9.0
select `c`.col_int,`dd`.col_time, min(`dd`.col_int) over (partition by `c`.col_int
order by `dd`.col_time) as `rank` from `dd` left join `c` on `c`.col_int = `dd`.col_int;
col_int    col_time    rank
null    00:00:00    2
null    00:00:00    2
null    00:00:00    2
null    00:27:13    2
null    02:20:01    2
null    08:28:55    2
null    09:40:42    2
null    10:20:49    2
null    11:26:20    2
null    14:34:36    2
null    14:58:44    2
null    18:50:24    2
null    20:12:00    2
null    20:55:45    2
null    23:23:35    2
0    00:00:00    0
0    00:00:00    0
0    08:16:28    0
0    11:25:58    0
1    00:00:00    1
1    01:41:35    1
1    03:05:06    1
1    10:10:42    1
1    17:42:13    1
4    06:00:42    4
4    06:00:42    4
4    13:02:05    4
4    13:02:05    4
4    15:32:00    4
4    15:32:00    4
4    16:15:34    4
4    16:15:34    4
5    00:00:00    5
5    00:59:59    5
5    02:16:04    5
5    09:25:59    5
5    14:38:03    5
5    15:11:27    5
5    16:20:06    5
6    00:00:00    6
6    00:00:00    6
6    00:00:00    6
6    05:15:45    6
6    06:03:27    6
6    06:11:59    6
6    09:21:09    6
6    11:04:39    6
6    19:55:11    6
7    00:00:00    7
7    00:00:00    7
7    00:00:00    7
7    00:00:00    7
7    03:56:10    7
7    03:56:10    7
7    12:04:50    7
7    12:04:50    7
7    17:01:57    7
7    17:01:57    7
7    20:17:57    7
7    20:17:57    7
8    00:00:00    8
8    00:00:00    8
8    00:00:00    8
8    00:00:00    8
8    00:00:00    8
8    05:49:51    8
8    05:49:51    8
8    05:49:51    8
8    05:49:51    8
8    05:49:51    8
8    07:09:22    8
8    07:09:22    8
8    07:09:22    8
8    07:09:22    8
8    07:09:22    8
8    08:06:08    8
8    08:06:08    8
8    08:06:08    8
8    08:06:08    8
8    08:06:08    8
8    12:51:37    8
8    12:51:37    8
8    12:51:37    8
8    12:51:37    8
8    12:51:37    8
8    13:39:32    8
8    13:39:32    8
8    13:39:32    8
8    13:39:32    8
8    13:39:32    8
8    17:04:35    8
8    17:04:35    8
8    17:04:35    8
8    17:04:35    8
8    17:04:35    8
8    23:02:05    8
8    23:02:05    8
8    23:02:05    8
8    23:02:05    8
8    23:02:05    8
9    00:00:00    9
9    04:56:37    9
9    08:09:49    9
9    11:18:35    9
9    19:03:57    9
drop table `c`;
drop table `dd`;
drop table `e`;
drop table if exists t2;
CREATE TABLE t2 (a int, b int);
insert into t2 values(1,1);
insert into t2 values(2,2);
insert into t2 values(3,3);
insert into t2 values(4,4);
insert into t2 values(1,1);
insert into t2 values(5,5);
insert into t2 values(6,6);
select a,min(a) over (partition by a) ,sum(a+1) over (partition by a) from t2;
a    min(a) over (partition by a)    sum(a + 1) over (partition by a)
1    1    4
1    1    4
2    2    3
3    3    4
4    4    5
5    5    6
6    6    7
drop table t2;
drop table if exists int_8;
create table int_8 (id tinyint, sex varchar(10));
insert into int_8 values (-1, 'moolol');
insert into int_8 values (-128, 'fdhsajhd');
insert into int_8 values (32, 'fdhsajhd');
insert into int_8 values (-1, 'fdhsajhd');
insert into int_8 values (23, 'moolol');
insert into int_8 values (127, null);
insert into int_8 values (-128, null);
select * from int_8;
id    sex
-1    moolol
-128    fdhsajhd
32    fdhsajhd
-1    fdhsajhd
23    moolol
127    null
-128    null
select row_number() over (partition by sex order by id rows between unbounded preceding and unbounded following) as a, id,
sum(id) over (partition by sex order by id rows between 1 following and 2 following) as b from int_8;
a    id    b
1    -128    127
2    127    null
1    -128    31
2    -1    32
3    32    null
1    -1    23
2    23    null
select row_number() over (partition by sex order by id rows between 1 following and 2 following) as a, sum(id) over (partition by sex order by id
rows between 1 following and 2 following) as b from int_8;
a    b
1    127
2    null
1    31
2    32
3    null
1    23
2    null
drop table int_8;
drop table if exists int_16;
create table int_16(col1 smallint,col2 bool,col3 datetime);
insert into int_16 values(-32768, true, '2023-05-16 00:12:12');
insert into int_16 values(22201, false, '1997-01-13 12:12:00');
insert into int_16 values(-32768, true, '2000-10-10 11:11:11');
insert into int_16 values(4, false, '1020-10-01 01:01:01');
insert into int_16 values(32767, null, null);
select max(col1) over (partition by col2 order by col1 rows between 2 preceding and 3 following) as col1 from int_16;
col1
32767
22201
22201
-32768
-32768
select dense_rank() over (partition by col2 order by col1 rows between 2 preceding and 3 following) as col1 from int_16;
col1
1
1
2
1
1
drop table int_16;
drop table if exists int_32;
create table int_32(i int, j int, k int);
insert into int_32 values (-2147483648, 1, 1);
insert into int_32 values (-2147483648, 1, 2);
insert into int_32 values (2147483647, 1, 2);
insert into int_32 values (2147483647, 2, 1);
insert into int_32 values (13289392, 2, 2);
insert into int_32 values (23289483, 1, 1);
insert into int_32 values (-3824, 1, 1);
insert into int_32 values (2438294, 1, 2);
insert into int_32 values (-3824, 2, 1);
select * from int_32;
i    j    k
-2147483648    1    1
-2147483648    1    2
2147483647    1    2
2147483647    2    1
13289392    2    2
23289483    1    1
-3824    1    1
2438294    1    2
-3824    2    1
select *, rank() over (order by i,j,k rows between 2 preceding and 3 following) as o_ijk,
min(i) over (order by j rows between 4 preceding and 5 following) as o_j,
rank() over (order by k,j rows between 1 preceding and 1 following) as o_kj from int_32 order by i,j,k;
i    j    k    o_ijk    o_j    o_kj
-2147483648    1    1    1    -2147483648    1
-2147483648    1    2    2    -2147483648    6
-3824    1    1    3    -2147483648    1
-3824    2    1    4    -3824    4
2438294    1    2    5    -2147483648    6
13289392    2    2    6    -3824    9
23289483    1    1    7    -2147483648    1
2147483647    1    2    8    -2147483648    6
2147483647    2    1    9    -3824    4
drop table int_32;
drop table if exists int_64;
create table int_64(i bigint unsigned, j int, k int);
insert into int_64 values (18446744073709551614, 1, 1);
insert into int_64 values (18446744073709551614, 1, 2);
insert into int_64 values (2147483647, 1, 2);
insert into int_64 values (2147483647, 2, 1);
insert into int_64 values (0, 2, 2);
insert into int_64 values (0, 1, 1);
select * from int_64;
i    j    k
18446744073709551614    1    1
18446744073709551614    1    2
2147483647    1    2
2147483647    2    1
0    2    2
0    1    1
select *, rank() over (order by i,j,k rows between 2 preceding and 3 following) as o_ijk,
min(i) over (order by j rows between 4 preceding and 5 following) as o_j,
rank() over (order by k,j rows between 1 preceding and 1 following) as o_kj from int_64 order by i,j,k;
i    j    k    o_ijk    o_j    o_kj
0    1    1    1    0    1
0    2    2    2    0    6
2147483647    1    2    3    0    4
2147483647    2    1    4    0    3
18446744073709551614    1    1    5    0    1
18446744073709551614    1    2    6    0    4
drop table int_64;
drop table if exists uint_8;
create table uint_8 (col1 tinyint unsigned, col2 varchar(10));
insert into uint_8 values (1, 'moolol');
insert into uint_8 values (128, 'fdhsajhd');
insert into uint_8 values (32, 'fdhsajhd');
insert into uint_8 values (1, 'fdhsajhd');
insert into uint_8 values (23, 'moolol');
insert into uint_8 values (255, null);
insert into uint_8 values (128, null);
select * from uint_8;
col1    col2
1    moolol
128    fdhsajhd
32    fdhsajhd
1    fdhsajhd
23    moolol
255    null
128    null
select row_number() over (partition by col2 order by col1 rows between unbounded preceding and unbounded following) as a,
sum(col1) over (partition by col2 order by col1 rows between 1 following and 2 following) as b from uint_8;
a    b
1    255
2    null
1    160
2    128
3    null
1    23
2    null
select dense_rank() over (partition by col2 order by col1) as col1, sum(col1) over (partition by col2 order by col1) as col2 from uint_8;
col1    col2
1    128
2    383
1    1
2    33
3    161
1    1
2    24
drop table uint_8;
drop table if exists uint_16;
create table uint_16(col1 smallint unsigned,col2 bool,col3 datetime);
insert into uint_16 values(0, true, '2023-05-16 00:12:12');
insert into uint_16 values(0, false, '1997-01-13 12:12:00');
insert into uint_16 values(65535, true, '2000-10-10 11:11:11');
insert into uint_16 values(4, false, '1020-10-01 01:01:01');
insert into uint_16 values(null, null, null);
insert into uint_16 values(65535, null, '1997-11-10 10:10:10');
select * from uint_16;
col1    col2    col3
0    true    2023-05-16 00:12:12
0    false    1997-01-13 12:12:00
65535    true    2000-10-10 11:11:11
4    false    1020-10-01 01:01:01
null    null    null
65535    null    1997-11-10 10:10:10
select max(col1) over (partition by col2 order by col1 rows between unbounded preceding and unbounded following) as col1 from uint_16;
col1
65535
65535
4
4
65535
65535
select dense_rank() over (partition by col2 order by col1 rows between unbounded preceding and unbounded following) as col1 from uint_16;
col1
1
2
1
2
1
2
drop table uint_16;
drop table if exists uint_32;
create table uint_32(i int unsigned, j int, k int);
insert into uint_32 values (4294967295, 1, 1);
insert into uint_32 values (4294967295, 1, 2);
insert into uint_32 values (2147483647, 1, 2);
insert into uint_32 values (2147483647, 2, 1);
insert into uint_32 values (13289392, 2, 2);
insert into uint_32 values (23289483, 1, 1);
insert into uint_32 values (3824, 1, 1);
insert into uint_32 values (2438294, 1, 2);
insert into uint_32 values (3824, 2, 1);
select * from uint_32;
i    j    k
4294967295    1    1
4294967295    1    2
2147483647    1    2
2147483647    2    1
13289392    2    2
23289483    1    1
3824    1    1
2438294    1    2
3824    2    1
select  max(i) over (order by i,j,k rows between 1 preceding and 2 following) as o_ijk,
dense_rank() over (order by i rows between unbounded preceding and unbounded following) as o_j,
rank() over (order by k,j rows between unbounded preceding and unbounded following) as o_kj from uint_32 order by i,j,k;
o_ijk    o_j    o_kj
2438294    1    1
13289392    1    4
23289483    2    6
2147483647    3    9
2147483647    4    1
4294967295    5    6
4294967295    5    4
4294967295    6    1
4294967295    6    6
drop table uint_32;
drop table if exists uint_64;
create table uint_64(i bigint unsigned, j bigint unsigned, k decimal);
insert into uint_64 values (18446744073709551615, 2147483647, 123213.99898);
insert into uint_64 values (4294967295, 2147483647, 2);
insert into uint_64 values (18446744073709551615, 1, 2);
insert into uint_64 values (2147483647, 23289483, 123213.99898);
insert into uint_64 values (13289392, 2, 2);
insert into uint_64 values (18446744073709551615, 23289483, 1);
insert into uint_64 values (3824, 13289392, 123213.99898);
insert into uint_64 values (2438294, 1, 2);
insert into uint_64 values (3824, 13289392, 1);
select * from uint_64;
i    j    k
18446744073709551615    2147483647    123214
4294967295    2147483647    2
18446744073709551615    1    2
2147483647    23289483    123214
13289392    2    2
18446744073709551615    23289483    1
3824    13289392    123214
2438294    1    2
3824    13289392    1
select rank() over (order by i,j,k rows between unbounded preceding and unbounded following) as o_ijk,
max(i) over (order by i rows between 10 preceding and 2 following) as o_j,
rank() over (order by k,j rows between unbounded preceding and unbounded following) as o_kj from uint_64 order by i,j,k;
o_ijk    o_j    o_kj
1    2438294    1
2    13289392    7
3    2147483647    3
4    4294967295    5
5    18446744073709551615    8
6    18446744073709551615    6
7    18446744073709551615    3
8    18446744073709551615    2
9    18446744073709551615    9
drop table uint_64;
drop table if exists decimal_64;
create table decimal_64(col1 decimal(18,10), col2 char(10));
insert into decimal_64 values (23189723.2314892238902, 'male');
insert into decimal_64 values (23189723.2314892238902, 'female');
insert into decimal_64 values (3278.3243214124242, 'male');
insert into decimal_64 (col1, col2) values (32134243.2143243242142, 'male');
insert into decimal_64 values (-23189723.2314892238902, 'male');
insert into decimal_64 values (-3278.3243214124242, 'female');
insert into decimal_64 (col1, col2) values (32134243.2143243242142, 'male');
select * from decimal_64;
col1    col2
23189723.2314892239    male
23189723.2314892239    female
3278.3243214124    male
32134243.2143243242    male
-23189723.2314892239    male
-3278.3243214124    female
32134243.2143243242    male
select max(col1) over (order by col1 rows between 1 preceding and 0 following) as newcol1 from decimal_64;
newcol1
-23189723.2314892239
-3278.3243214124
3278.3243214124
23189723.2314892239
23189723.2314892239
32134243.2143243242
32134243.2143243242
select min(col1) over (partition by col2 order by col1 rows between unbounded preceding and unbounded following) as newcol from decimal_64 limit 4;
newcol
-3278.3243214124
-3278.3243214124
-23189723.2314892239
-23189723.2314892239
drop table decimal_64;
drop table if exists decimal_128;
create table decimal_128(col1 decimal(38,10), col2 char(10));
insert into decimal_128 values (2318972338274832748378887878.2314892238902, 'male');
insert into decimal_128 values (2318972338274832748378887878.23148922389, 'female');
insert into decimal_128 values (-3278234242342349090943024982.3243214124242, 'male');
insert into decimal_128 (col1, col2) values (32134243.2143243242142, 'male');
insert into decimal_128 values (-23189723.2314892238902, 'male');
insert into decimal_128 values (-3278234242342349090943024982.3243214124242, 'female');
insert into decimal_128 (col1, col2) values (32134243.2143243242142, 'male');
select * from decimal_128;
col1    col2
2318972338274832748378887878.2314892239    male
2318972338274832748378887878.2314892239    female
-3278234242342349090943024982.3243214124    male
32134243.2143243242    male
-23189723.2314892239    male
-3278234242342349090943024982.3243214124    female
32134243.2143243242    male
select min(col1) over (order by col1 rows between 1 preceding and 0 following) as newcol1 from decimal_128;
newcol1
-3278234242342349090943024982.3243214124
-3278234242342349090943024982.3243214124
-3278234242342349090943024982.3243214124
-23189723.2314892239
32134243.2143243242
32134243.2143243242
2318972338274832748378887878.2314892239
select dense_rank() over (order by col1 rows between 1 preceding and 2 following) as newcol2 from decimal_128;
newcol2
1
1
2
3
3
4
4
select row_number() over (partition by col2 order by col1 rows between unbounded preceding and unbounded following) as newcol from decimal_128;
newcol
1
2
1
2
3
4
5
drop table decimal_128;
drop table if exists time01;
create table time01 (col1 time, col2 timestamp);
insert into time01 values ('12:1b2:12', '2023-05-16 00:12:12');
invalid input: invalid time value 12:1b2:12
insert into time01 values ('23:23:59', '2019-05-16 23:23:59');
insert into time01 values ('12:12:12', '1997-01-13 01:02:03');
insert into time01 values ('23:23:59', '2023-05-16 00:12:12');
insert into time01 values ('01:02:03', '2019-05-16 23:23:59');
insert into time01 values (null, null);
select * from time01;
col1    col2
23:23:59    2019-05-16 23:23:59
12:12:12    1997-01-13 01:02:03
23:23:59    2023-05-16 00:12:12
01:02:03    2019-05-16 23:23:59
null    null
select rank() over (order by col1 rows between 1 preceding and 0 following) as newcol1 from time01;
newcol1
1
2
3
4
4
select dense_rank() over (order by col1 rows between 1 preceding and 2 following) as newcol2 from time01;
newcol2
1
2
3
4
4
select row_number() over (partition by col2 order by col1 rows between unbounded preceding and unbounded following) as newcol from time01;
newcol
1
1
1
2
1
select rank() over (partition by col1 order by col2 rows between 0 preceding and 0 following) as newcol from time01;
newcol
1
1
1
1
2
select max(col2) over (partition by col1 order by col2 rows between 3 preceding and 3 following) as newcol from time01;
newcol
null
2019-05-16 23:23:59
1997-01-13 01:02:03
2023-05-16 00:12:12
2023-05-16 00:12:12
drop table time01;
drop table if exists window01;
create table window01(col1 int, col2 varchar(20));
insert into window01 values(1,'老师');
insert into window01 values(2,'医生');
insert into window01 values(3,'工人');
insert into window01 values(10,'学生');
insert into window01 values(20,'学生');
insert into window01 values(12,'学生');
insert into window01 values(21,'老师');
insert into window01 values(100,'老师');
insert into window01 values(200,'工人');
select col2, avg(col1) over (partition by col2 order by col1 desc) from window01;
col2    avg(col1) over (partition by col2 order by col1 desc)
医生    2.0
学生    20.0
学生    16.0
学生    14.0
工人    200.0
工人    101.5
老师    100.0
老师    60.5
老师    40.666666666666664
select col2, col1, sum(col1) over (partition by col2 order by col1 desc) from window01;
col2    col1    sum(col1) over (partition by col2 order by col1 desc)
医生    2    2
学生    20    20
学生    12    32
学生    10    42
工人    200    200
工人    3    203
老师    100    100
老师    21    121
老师    1    122
select col2, col1, min(col1) over (partition by col2 order by col1 desc) from window01;
col2    col1    min(col1) over (partition by col2 order by col1 desc)
医生    2    2
学生    20    20
学生    12    12
学生    10    10
工人    200    200
工人    3    3
老师    100    100
老师    21    21
老师    1    1
select col2, col1, max(col1) over (partition by col2 order by col1 desc) from window01;
col2    col1    max(col1) over (partition by col2 order by col1 desc)
医生    2    2
学生    20    20
学生    12    20
学生    10    20
工人    200    200
工人    3    200
老师    100    100
老师    21    100
老师    1    100
select col2, col1, count(col1) over (partition by col2 order by col1 desc) from window01;
col2    col1    count(col1) over (partition by col2 order by col1 desc)
医生    2    1
学生    20    1
学生    12    2
学生    10    3
工人    200    1
工人    3    2
老师    100    1
老师    21    2
老师    1    3
drop database test;