mirror of
https://github.com/mariadb-corporation/mariadb-columnstore-engine.git
synced 2025-04-18 21:44:02 +03:00
4c9d6e39ac
* Restructured test suites and added autopilot and extended suites * Updated autopilot with correct branch - develop * Moved setup test case to a 'setup' directory, for consistency * Fixed a path issue * Updated some tests cases to keep up with development Co-authored-by: root <root@rocky8.localdomain>
272 lines
4.6 KiB
Plaintext
272 lines
4.6 KiB
Plaintext
USE tpch1;
|
|
select n_nationkey, avg(n_nationkey) over(order by n_nationkey range between unbounded preceding and 15 following) a from nation;
|
|
n_nationkey a
|
|
0 7.5000
|
|
1 8.0000
|
|
2 8.5000
|
|
3 9.0000
|
|
4 9.5000
|
|
5 10.0000
|
|
6 10.5000
|
|
7 11.0000
|
|
8 11.5000
|
|
9 12.0000
|
|
10 12.0000
|
|
11 12.0000
|
|
12 12.0000
|
|
13 12.0000
|
|
14 12.0000
|
|
15 12.0000
|
|
16 12.0000
|
|
17 12.0000
|
|
18 12.0000
|
|
19 12.0000
|
|
20 12.0000
|
|
21 12.0000
|
|
22 12.0000
|
|
23 12.0000
|
|
24 12.0000
|
|
select n_nationkey, avg(n_nationkey) over(order by n_nationkey range between 3 following and 15 following) b from nation;
|
|
n_nationkey b
|
|
0 9.0000
|
|
1 10.0000
|
|
2 11.0000
|
|
3 12.0000
|
|
4 13.0000
|
|
5 14.0000
|
|
6 15.0000
|
|
7 16.0000
|
|
8 17.0000
|
|
9 18.0000
|
|
10 18.5000
|
|
11 19.0000
|
|
12 19.5000
|
|
13 20.0000
|
|
14 20.5000
|
|
15 21.0000
|
|
16 21.5000
|
|
17 22.0000
|
|
18 22.5000
|
|
19 23.0000
|
|
20 23.5000
|
|
21 24.0000
|
|
22 NULL
|
|
23 NULL
|
|
24 NULL
|
|
select n_nationkey, n_regionkey, avg(n_nationkey) over (partition by n_regionkey order by n_nationkey rows between 2 following and 2 following) c from nation order by n_regionkey, n_nationkey;
|
|
n_nationkey n_regionkey c
|
|
0 0 14.0000
|
|
5 0 15.0000
|
|
14 0 16.0000
|
|
15 0 NULL
|
|
16 0 NULL
|
|
1 1 3.0000
|
|
2 1 17.0000
|
|
3 1 24.0000
|
|
17 1 NULL
|
|
24 1 NULL
|
|
8 2 12.0000
|
|
9 2 18.0000
|
|
12 2 21.0000
|
|
18 2 NULL
|
|
21 2 NULL
|
|
6 3 19.0000
|
|
7 3 22.0000
|
|
19 3 23.0000
|
|
22 3 NULL
|
|
23 3 NULL
|
|
4 4 11.0000
|
|
10 4 13.0000
|
|
11 4 20.0000
|
|
13 4 NULL
|
|
20 4 NULL
|
|
select n_nationkey, n_regionkey, avg(n_nationkey) over (partition by n_regionkey order by n_nationkey rows between 20 following and 20 following) d from nation order by n_regionkey, n_nationkey;
|
|
n_nationkey n_regionkey d
|
|
0 0 NULL
|
|
5 0 NULL
|
|
14 0 NULL
|
|
15 0 NULL
|
|
16 0 NULL
|
|
1 1 NULL
|
|
2 1 NULL
|
|
3 1 NULL
|
|
17 1 NULL
|
|
24 1 NULL
|
|
8 2 NULL
|
|
9 2 NULL
|
|
12 2 NULL
|
|
18 2 NULL
|
|
21 2 NULL
|
|
6 3 NULL
|
|
7 3 NULL
|
|
19 3 NULL
|
|
22 3 NULL
|
|
23 3 NULL
|
|
4 4 NULL
|
|
10 4 NULL
|
|
11 4 NULL
|
|
13 4 NULL
|
|
20 4 NULL
|
|
select n_nationkey, n_regionkey, avg(n_nationkey) over (partition by n_regionkey order by n_nationkey rows between 20 following and 1 following) e from nation order by n_regionkey, n_nationkey;
|
|
n_nationkey n_regionkey e
|
|
0 0 NULL
|
|
5 0 NULL
|
|
14 0 NULL
|
|
15 0 NULL
|
|
16 0 NULL
|
|
1 1 NULL
|
|
2 1 NULL
|
|
3 1 NULL
|
|
17 1 NULL
|
|
24 1 NULL
|
|
8 2 NULL
|
|
9 2 NULL
|
|
12 2 NULL
|
|
18 2 NULL
|
|
21 2 NULL
|
|
6 3 NULL
|
|
7 3 NULL
|
|
19 3 NULL
|
|
22 3 NULL
|
|
23 3 NULL
|
|
4 4 NULL
|
|
10 4 NULL
|
|
11 4 NULL
|
|
13 4 NULL
|
|
20 4 NULL
|
|
select n_nationkey, n_regionkey, avg(n_nationkey) over (partition by n_regionkey order by n_nationkey rows between 1 following and 20 following) f from nation order by n_regionkey, n_nationkey;
|
|
n_nationkey n_regionkey f
|
|
0 0 12.5000
|
|
5 0 15.0000
|
|
14 0 15.5000
|
|
15 0 16.0000
|
|
16 0 NULL
|
|
1 1 11.5000
|
|
2 1 14.6667
|
|
3 1 20.5000
|
|
17 1 24.0000
|
|
24 1 NULL
|
|
8 2 15.0000
|
|
9 2 17.0000
|
|
12 2 19.5000
|
|
18 2 21.0000
|
|
21 2 NULL
|
|
6 3 17.7500
|
|
7 3 21.3333
|
|
19 3 22.5000
|
|
22 3 23.0000
|
|
23 3 NULL
|
|
4 4 13.5000
|
|
10 4 14.6667
|
|
11 4 16.5000
|
|
13 4 20.0000
|
|
20 4 NULL
|
|
select n_nationkey, n_regionkey, avg(n_nationkey) over (partition by n_regionkey order by n_nationkey rows between 2 preceding and 2 preceding) g from nation order by n_regionkey, n_nationkey;
|
|
n_nationkey n_regionkey g
|
|
0 0 NULL
|
|
5 0 NULL
|
|
14 0 0.0000
|
|
15 0 5.0000
|
|
16 0 14.0000
|
|
1 1 NULL
|
|
2 1 NULL
|
|
3 1 1.0000
|
|
17 1 2.0000
|
|
24 1 3.0000
|
|
8 2 NULL
|
|
9 2 NULL
|
|
12 2 8.0000
|
|
18 2 9.0000
|
|
21 2 12.0000
|
|
6 3 NULL
|
|
7 3 NULL
|
|
19 3 6.0000
|
|
22 3 7.0000
|
|
23 3 19.0000
|
|
4 4 NULL
|
|
10 4 NULL
|
|
11 4 4.0000
|
|
13 4 10.0000
|
|
20 4 11.0000
|
|
select n_nationkey, n_regionkey, avg(n_nationkey) over (partition by n_regionkey order by n_nationkey rows between 20 preceding and 20 preceding) h from nation order by n_regionkey, n_nationkey;
|
|
n_nationkey n_regionkey h
|
|
0 0 NULL
|
|
5 0 NULL
|
|
14 0 NULL
|
|
15 0 NULL
|
|
16 0 NULL
|
|
1 1 NULL
|
|
2 1 NULL
|
|
3 1 NULL
|
|
17 1 NULL
|
|
24 1 NULL
|
|
8 2 NULL
|
|
9 2 NULL
|
|
12 2 NULL
|
|
18 2 NULL
|
|
21 2 NULL
|
|
6 3 NULL
|
|
7 3 NULL
|
|
19 3 NULL
|
|
22 3 NULL
|
|
23 3 NULL
|
|
4 4 NULL
|
|
10 4 NULL
|
|
11 4 NULL
|
|
13 4 NULL
|
|
20 4 NULL
|
|
select n_nationkey, n_regionkey, avg(n_nationkey) over (partition by n_regionkey order by n_nationkey rows between 20 preceding and 1 preceding) k from nation order by n_regionkey, n_nationkey;
|
|
n_nationkey n_regionkey k
|
|
0 0 NULL
|
|
5 0 0.0000
|
|
14 0 2.5000
|
|
15 0 6.3333
|
|
16 0 8.5000
|
|
1 1 NULL
|
|
2 1 1.0000
|
|
3 1 1.5000
|
|
17 1 2.0000
|
|
24 1 5.7500
|
|
8 2 NULL
|
|
9 2 8.0000
|
|
12 2 8.5000
|
|
18 2 9.6667
|
|
21 2 11.7500
|
|
6 3 NULL
|
|
7 3 6.0000
|
|
19 3 6.5000
|
|
22 3 10.6667
|
|
23 3 13.5000
|
|
4 4 NULL
|
|
10 4 4.0000
|
|
11 4 7.0000
|
|
13 4 8.3333
|
|
20 4 9.5000
|
|
select n_nationkey, n_regionkey, avg(n_nationkey) over (partition by n_regionkey order by n_nationkey rows between 1 preceding and 20 preceding) m from nation order by n_regionkey, n_nationkey;
|
|
n_nationkey n_regionkey m
|
|
0 0 NULL
|
|
5 0 NULL
|
|
14 0 NULL
|
|
15 0 NULL
|
|
16 0 NULL
|
|
1 1 NULL
|
|
2 1 NULL
|
|
3 1 NULL
|
|
17 1 NULL
|
|
24 1 NULL
|
|
8 2 NULL
|
|
9 2 NULL
|
|
12 2 NULL
|
|
18 2 NULL
|
|
21 2 NULL
|
|
6 3 NULL
|
|
7 3 NULL
|
|
19 3 NULL
|
|
22 3 NULL
|
|
23 3 NULL
|
|
4 4 NULL
|
|
10 4 NULL
|
|
11 4 NULL
|
|
13 4 NULL
|
|
20 4 NULL
|