FOR_MIN
This article will show you how to get some value based on the minimum metric from a set of values.
Overview
FOR_MIN() function takes two arguments: metric and value. Returns a value corresponding to the minimal metric in the same row from a set of values.
The metric argument can be one of the following types:
- INT
- LONG
- FLOAT
- DOUBLE
- DATE
- TIMESTAMP
The value argument can be of any type except STRING.
π‘Special cases:
- Returns NULL if:
- there are no input rows
- the metric column has only NULL values
- the value corresponding to the minimum metric is NULL.
- Returns NaN if the input contains a NaN.
Examples
We have a payments table that stores the records of payments by customers, along with discounts applied during the payment:
It will create a table as shown below:
#Case 1: FOR_MIN() on the whole table
For example, let's check the price for the lowest discount applied to it:
It will return the following output:
#Case 2: FOR_MIN() with GROUP BY clause
For this example, we use a GROUP BY clause to group the customers, then use FOR_MIN() to get a discount for the lowest price paid by each customer.
Which will give the following result:
ο»Ώ