ARGMAX [analytic]

This function is patterned after the mathematical function argmax(f(x)), which returns the value of x that maximizes f(x). Similarly, ARGMAX takes two arguments target and arg, where both are columns or column expressions in the queried dataset. ARGMAX finds the row with the largest non-null value in target and returns the value of arg in that row. If multiple rows contain the largest target value, ARGMAX returns arg from the first row that it finds.

Behavior type

Syntax

ARGMAX ( target, arg )  OVER ( [ PARTITION BY expression[,...] ] [ window-order-clause ] )

Arguments

target, arg

Columns in the queried dataset.

Note

ARGMAX does not support spatial data types: GEOMETRY and GEOGRAPHY.

OVER()

Specifies the following window clauses:

• PARTITION BY expression: Groups (partitions) input rows according to the values in expression, which resolves to one or more columns in the queried dataset. If you omit this clause, ARGMAX processes all input rows as a single partition.

• window-order-clause: Specifies how to sort input rows. If the OVER clause also includes a partition clause, rows are sorted separately within each partition.

  Important

  To ensure consistent results when multiple rows contain the largest target value, include a window order clause that sorts on arg.

For details, see Analytic Functions.

Examples

Create and populate table service_info, which contains information on various services, their respective development groups, and their userbase. A NULL in the users column indicates that the service has not been released, and so it cannot have users.

=> CREATE TABLE service_info(dev_group VARCHAR(10), product_name VARCHAR(30), users INT);
=> COPY t FROM stdin NULL AS 'null';
>> iris|chat|48193
>> aspen|trading|3000
>> orchid|cloud|990322
>> iris|video call| 10203
>> daffodil|streaming|44123
>> hydrangea|password manager|null
>> hydrangea|totp|1837363
>> daffodil|clip share|3000
>> hydrangea|e2e sms|null
>> rose|crypto|null
>> iris|forum|48193
>> \.

ARGMAX returns the value in the product_name column that maximizes the value in the users column. In this case, ARGMAX returns totp, which indicates that the totp service has the largest user base:

=> SELECT dev_group, product_name, users, ARGMAX(users, product_name) OVER (ORDER BY dev_group ASC) FROM service_info;
 dev_group |   product_name   |  users  | ARGMAX
-----------+------------------+---------+--------
 aspen     | trading          |    3000 | totp
 daffodil  | clip share       |    3000 | totp
 daffodil  | streaming        |   44123 | totp
 hydrangea | e2e sms          |         | totp
 hydrangea | password manager |         | totp
 hydrangea | totp             | 1837363 | totp
 iris      | chat             |   48193 | totp
 iris      | forum            |   48193 | totp
 iris      | video call       |   10203 | totp
 orchid    | cloud            |  990322 | totp
 rose      | crypto           |         | totp
(11 rows)

The next query partitions the data on dev_group to identify the most popular service created by each development group. ARGMAX returns NULL if the partition's users column contains only NULL values and breaks ties using the first value in product_name from the top of the partition.

=> SELECT dev_group, product_name, users, ARGMAX(users, product_name) OVER (PARTITION BY dev_group ORDER BY product_name ASC) FROM service_info;
 dev_group |   product_name   |  users  |  ARGMAX
-----------+------------------+---------+-----------
 iris      | chat             |   48193 | chat
 iris      | forum            |   48193 | chat
 iris      | video call       |   10203 | chat
 orchid    | cloud            |  990322 | cloud
 aspen     | trading          |    3000 | trading
 daffodil  | clip share       |    3000 | streaming
 daffodil  | streaming        |   44123 | streaming
 rose      | crypto           |         |
 hydrangea | e2e sms          |         | totp
 hydrangea | password manager |         | totp
 hydrangea | totp             | 1837363 | totp
(11 rows)

See also