7.16.3. window_rank#
7.16.3.1. Summary#
Added in version 11.0.9.
This window function computes the rank of each record with gaps. This
is similar to window_record_number. window_record_number
computes the number of each record. The number is always incremented
but the rank isn’t incremented when multiple records that are the same
order. The next rank after multiple records that are the same order
has gap. If values of sort keys are 100, 100, 200 then the ranks
of them are 1, 1, 3. The rank of the last record is 3 not
2 because there are two 1 rank records.
7.16.3.2. Syntax#
This window function doesn’t require any parameters:
window_rank()
7.16.3.3. Usage#
Here are a schema definition and sample data to show usage.
Execution example:
table_create Points TABLE_NO_KEY
# [[0,1337566253.89858,0.000355720520019531],true]
column_create Points game COLUMN_SCALAR ShortText
# [[0,1337566253.89858,0.000355720520019531],true]
column_create Points score COLUMN_SCALAR UInt32
# [[0,1337566253.89858,0.000355720520019531],true]
load --table Points
[
["game", "score"],
["game1", 100],
["game1", 200],
["game1", 100],
["game1", 400],
["game2", 150],
["game2", 200],
["game2", 200],
["game2", 200]
]
# [[0,1337566253.89858,0.000355720520019531],8]
Here is an example that specifies only sort keys:
Execution example:
select Points \
--columns[rank].stage filtered \
--columns[rank].value 'window_rank()' \
--columns[rank].type UInt32 \
--columns[rank].window.sort_keys score \
--output_columns 'game, score, rank' \
--sort_keys score
# [
# [
# 0,
# 1337566253.89858,
# 0.000355720520019531
# ],
# [
# [
# [
# 8
# ],
# [
# [
# "game",
# "ShortText"
# ],
# [
# "score",
# "UInt32"
# ],
# [
# "rank",
# "UInt32"
# ]
# ],
# [
# "game1",
# 100,
# 1
# ],
# [
# "game1",
# 100,
# 1
# ],
# [
# "game2",
# 150,
# 3
# ],
# [
# "game2",
# 200,
# 4
# ],
# [
# "game2",
# 200,
# 4
# ],
# [
# "game1",
# 200,
# 4
# ],
# [
# "game2",
# 200,
# 4
# ],
# [
# "game1",
# 400,
# 8
# ]
# ]
# ]
# ]
Here is an example that computes ranks for each game:
Execution example:
select Points \
--columns[rank].stage filtered \
--columns[rank].value 'window_rank()' \
--columns[rank].type UInt32 \
--columns[rank].window.group_keys game \
--columns[rank].window.sort_keys score \
--output_columns 'game, score, rank' \
--sort_keys game,score
# [
# [
# 0,
# 1337566253.89858,
# 0.000355720520019531
# ],
# [
# [
# [
# 8
# ],
# [
# [
# "game",
# "ShortText"
# ],
# [
# "score",
# "UInt32"
# ],
# [
# "rank",
# "UInt32"
# ]
# ],
# [
# "game1",
# 100,
# 1
# ],
# [
# "game1",
# 100,
# 1
# ],
# [
# "game1",
# 200,
# 3
# ],
# [
# "game1",
# 400,
# 4
# ],
# [
# "game2",
# 150,
# 1
# ],
# [
# "game2",
# 200,
# 2
# ],
# [
# "game2",
# 200,
# 2
# ],
# [
# "game2",
# 200,
# 2
# ]
# ]
# ]
# ]
Here is an example that uses descending order:
Execution example:
select Points \
--columns[rank].stage filtered \
--columns[rank].value 'window_rank()' \
--columns[rank].type UInt32 \
--columns[rank].window.group_keys game \
--columns[rank].window.sort_keys -score \
--output_columns 'game, score, rank' \
--sort_keys game,-score
# [
# [
# 0,
# 1337566253.89858,
# 0.000355720520019531
# ],
# [
# [
# [
# 8
# ],
# [
# [
# "game",
# "ShortText"
# ],
# [
# "score",
# "UInt32"
# ],
# [
# "rank",
# "UInt32"
# ]
# ],
# [
# "game1",
# 400,
# 1
# ],
# [
# "game1",
# 200,
# 2
# ],
# [
# "game1",
# 100,
# 3
# ],
# [
# "game1",
# 100,
# 3
# ],
# [
# "game2",
# 200,
# 1
# ],
# [
# "game2",
# 200,
# 1
# ],
# [
# "game2",
# 200,
# 1
# ],
# [
# "game2",
# 150,
# 4
# ]
# ]
# ]
# ]
7.16.3.4. Parameters#
Nothing.
7.16.3.5. Return value#
The rank as UInt32 value.