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
|
# name: test/sql/window/test_rank.test
# description: Test RANK state computations
# group: [window]
statement ok
PRAGMA enable_verification
# Multiple chunks, single partition
query IIIII
WITH t AS (
SELECT i, RANK() OVER (ORDER BY i % 50) AS d
FROM range(3000) tbl(i)
), w AS (
SELECT d, COUNT(*) as c
FROM t
GROUP BY ALL
)
SELECT COUNT(*), MIN(d), MAX(d), MIN(c), MAX(c)
FROM w
----
50 1 2941 60 60
# Multiple chunks, multiple partitions
query IIIII
WITH t AS (
SELECT i, RANK() OVER (PARTITION BY i // 3000 ORDER BY i % 50) AS d
FROM range(9000) tbl(i)
), w AS (
SELECT d, COUNT(*) as c
FROM t
GROUP BY ALL
)
SELECT COUNT(*), MIN(d), MAX(d), MIN(c), MAX(c)
FROM w
----
50 1 2941 180 180
# Different null ordering
query III
SELECT
*,
RANK() OVER (ORDER BY x NULLS FIRST) rank_nulls_first,
RANK() OVER (ORDER BY x NULLS LAST) rank_nulls_last,
FROM VALUES (1), (1), (1), (NULL) as issue8315(x)
ORDER BY x
----
1.0 2 1
1.0 2 1
1.0 2 1
NULL 1 4
|
