1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
|
# name: test/sql/window/test_rank_orderby.test
# description: Test argument ordering for RANK and PERCENT_RANK
# group: [window]
statement ok
PRAGMA enable_verification
query IIII
SELECT
i,
(i * 29) % 11 AS outside,
rank(ORDER BY (i // 2) DESC) OVER w,
percent_rank(ORDER BY (i // 2) DESC) OVER w,
FROM range(10) tbl(i)
WINDOW w AS (
ORDER BY (i * 29) % 11
)
ORDER BY 2
----
0 0 1 0.0
8 1 1 0.0
5 2 2 0.5
2 3 3 0.6666666666666666
7 5 2 0.25
4 6 3 0.4
1 7 6 0.8333333333333334
9 8 1 0.0
6 9 3 0.25
3 10 7 0.6666666666666666
# Test parallel token construction (uses 4 threads)
query IIIII
WITH ranked AS (
SELECT
i,
i // 100 AS p,
i % 50 AS o,
100 - 2 * (i % 50) - 1 AS expected,
rank(ORDER BY i % 50 DESC) OVER w AS actual,
FROM range(100_000) tbl(i)
WINDOW w AS (
PARTITION BY i // 100
ORDER BY i
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
)
SELECT *
FROM ranked
WHERE expected <> actual
ORDER BY p, o DESC
LIMIT 20
----
# Test duplicate ORDER BY optimisation
query IIII
SELECT
i,
i // 2 AS outside,
rank(ORDER BY i // 2) OVER w,
percent_rank(ORDER BY i // 2) OVER w,
FROM range(10) tbl(i)
WINDOW w AS (
ORDER BY i // 2
ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING
)
ORDER BY 1
----
0 0 1 0.0
1 0 1 0.0
2 1 3 0.4
3 1 3 0.3333333333333333
4 2 4 0.5
5 2 3 0.3333333333333333
6 3 4 0.5
7 3 3 0.4
8 4 4 0.75
9 4 3 0.6666666666666666
|
