7.16.3. window_rank

7.16.3.1. Summary

New 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.

7.16.3.6. See also