rank_desc()#

relationalai.std.aggregates
#rank_desc(*args: Producer, per: list[Producer] = []) -> Expression

Sorts distinct values produced by one or more Producer objects in descending order and returns an Expression that produces the sort order for each value. When multiple producers are provided, the rank is performed over rows (v1, v2, ... vn) representing distinct combinations of values produced by the arguments, and the rank is computed by sorting the rows in descending lexicographic order. Pass a list of Producer objects to the optional per parameter to group values and compute the rank per group. Must be called in a rule or query context.

Parameters#

NameTypeDescription
*argsProducerOne or more Producer objects.
perList[Producer]An optional list of Producer objects used to group arguments. (Default: [])

Returns#

An Expression object.

Example#

Use rank_desc() to rank distinct values produced by a Producer object in descending order:

#import relationalai as rai
from relationalai.std import aggregates, alias


# =====
# SETUP
# =====

model = rai.Model("MyModel")
Person = model.Type("Person")

with model.rule():
    Person.add(id=1).set(name="Joe", age=20)
    Person.add(id=2).set(name="Jane", age=20)
    Person.add(id=3).set(name="John", age=30)


# =======
# EXAMPLE
# =======

with model.query() as select:
    person = Person()
    # Rank distinct age values in descending order.
    rank = aggregates.rank_desc(person.age)
    response = select(alias(rank, "rank"), person.name, person.age)

print(response.results)
#    rank  name  age
# 0     1  John   30
# 1     2  Jane   20  <-- Since Jane and Joe are the same age, they share the same rank.
# 2     2   Joe   20

When you pass multiple Producer objects to rank_desc(), the ranking is performed over the distinct rows of the form (v1, v2, ..., vn) where v1, v2, …, vn are the values produced by the Producer objects. The rank is computed by sorting the rows in descending lexicographic order.

You can use this, for example, to generate a unique rank for each person object in the preceding example and order results by age in descending order:

## Get the names and ages of people and order them in descending order by age.
with model.query() as select:
    person = Person()
    rank = aggregates.rank_desc(person.age, person)
    response = select(alias(rank, "rank"), person.name, person.age)

print(response.results)
#    rank  name  age
# 0     1  John   30
# 1     2   Joe   20
# 2     3  Jane   20

To group and rank values for each group, pass a list of one or more Producer objects to the optional per keyword argument. In the following example, the person object is passed to per to rank each person’s friends by age:

## Set a multi-valued friend property for each person.
with model.rule():
    joe = Person(name="Joe")
    jane = Person(name="Jane")
    john = Person(name="John")
    joe.friends.extend([jane, john])
    jane.friends.extend([joe])
    john.friends.extend([joe])

with model.query() as select:
    person = Person()
    # Rank each person's friends by age in descending order.
    friend_rank = aggregates.rank_desc(person.friends.age, person.friends, per=[person])
    response = select(
        person.name,
        alias(friend_rank, "friend_rank"),
        alias(person.friends.name, "friend_name"),
        alias(person.friends.age, "friend_age"),
    )

print(response.results)
#    name  friend_rank friend_name  friend_age
# 0  Jane            1         Joe          20
# 1   Joe            1        John          30
# 2   Joe            2        Jane          20
# 3  John            1         Joe          20

See Also#