ARGMIN [analytic]

This function is patterned after the mathematical function , which returns the value of x that minimizes.

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

Behavior type

Immutable

Syntax

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

Arguments

target, arg
Columns in the queried dataset.
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, ARGMIN 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.

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

ARGMIN returns the value in the product_name column that minimizes the value in the users column. In this case, ARGMIN returns totp, which indicates that the totp service has the smallest user base:


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

The next query partitions the data on dev_group to identify the least popular service created by each development group. ARGMIN 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, ARGMIN(users, product_name) OVER (PARTITION BY dev_group ORDER BY product_name ASC) FROM service_info;
 dev_group |   product_name   |  users  |   ARGMIN
-----------+------------------+---------+------------
 iris      | chat             |   48193 | video call
 iris      | forum            |   48193 | video call
 iris      | video call       |   10203 | video call
 orchid    | cloud            |  990322 | cloud
 aspen     | trading          |    3000 | trading
 daffodil  | clip share       |    3000 | clip share
 daffodil  | streaming        |   44123 | clip share
 rose      | crypto           |         |
 hydrangea | e2e sms          |         | totp
 hydrangea | password manager |         | totp
 hydrangea | totp             | 1837363 | totp
(11 rows)

See also

ARGMAX [analytic]